Several standards in the data warehouse

Several standards in the data warehouse

 

 

1. Database naming rules
All Database programmers in a project team should abide by the unified database naming rules ". In appendix B of this book, we provide an "database naming convention" instance for your reference.
2. database design paradigm
When designing relational databases, you must follow certain rules. In particular, the database design paradigm. Next we will briefly introduce 1nf (first paradigm), 2nf (second paradigm), 3nf (third paradigm), and bcnf, as well as the fourth and fifth paradigms. When designing a database, if you can meet these paradigms, you are a master of database design.
(1) 1nf)
Fields in the database table are single attributes and cannot be divided. This single attribute is composed of basic types, including integer, real number, complex type, logical type, and date type.
For example, the phone field may not conform to the first normalization, because each person may have more than one phone number to record. In this case, the simplest way is to add the field and do not use the phone number as the field name, use the home phone number, mobile phone number, office phone number, extension number, and dormitory phone number.

(2) 2nf)
The rule conforms to the first paradigm, and there is no primary key function that determines other attributes, that is, other attributes outside the primary key are completely dependent on the primary key.
Assume that the course selection relation table is selectcourse (student ID, name, age, course name, score, and credits), and the keywords are combined keywords (student ID, course name), because the following deciding relation exists: (student ID, course name) → (name, age, score, credits ).
This database table does not meet the second paradigm because of the following decision relationships:
(Course name) → (credits), that is, you know the credits of the course.
(Student ID) → (name, age), that is, you can know the student's name and age.
① Data redundancy
The same course is selected by N students, and the "Credits" are repeated for N1 times. The same student takes m courses, and the names and ages are repeated for M1 times.
② Update exception
If the credits of a course are adjusted, the "Credits" value of all rows in the data table must be updated. Otherwise, different credits may occur for the same course.
③ Insertion exception
For example, if you want to open a new course, no one will take the course. In this way, because there is no "student ID" keyword, the course name and credits cannot be recorded in the database.
④ Deletion exception
Assuming that a group of students have completed their electives, These electives should be deleted from the database table. However, the course name and credit information are also deleted. Obviously, this will also cause insertion exceptions.
Change the selectcourse table to the following three tables.
Student: Student (student ID, name, age)
Course: Course (Course name, credits)
Course Selection relationship: selectcourse (student ID, course name, score)
Such database tables conform to the second paradigm, eliminating data redundancy, update exceptions, insertion exceptions, and deletion exceptions.
(3) third paradigm (3nf)
On the basis of the second paradigm, if the data table does not have a function dependency on any candidate keyword segment without a non-Keyword segment, it complies with the third paradigm.
The so-called pass function dependency means that if there is a "A → B → C" decision relationship, the C transfer function depends on. Therefore, database tables that meet the third paradigm should not have the following dependencies: keyword segment → non-Keyword segment X → non-Keyword segment Y.
Assume that the student relationship table is student (student ID, name, age, school, school location, school phone number), and the keyword is a single keyword "student ID", because the following decision relationship exists:
(Student ID) → (name, age, school, school location, school phone number)
This database complies with 2nf but does not comply with 3nf because of the following decision relationships:
(Student ID) → (school location, school phone number)
The student relationship table is divided into the following two tables.
Student (student ID, name, age, school );
School: (school, location, phone number ).
(4) bcnf paradigm
The bcnf Paradigm (Boyce/codd normal form) is composed of R. f. boycy and e.f. codd proposed by codd can be regarded as a supplement to the third regularization. The rule conforms to the third normalization principle and does not have a non-primary key attribute to function to determine some primary keys.
Assume that there is a table R, where the attributes include a, B, c, d, e. Take A and B as the composite primary keys, r = {A, B, C, D, e}, if there is a non-primary key attribute, for example, C can function to determine B, C → B, and B is a part of the primary key, at this time, the third normalization method cannot identify this error. Therefore, there are bcnf regularization rules to check whether the original table is split, create a new associated table R1 to hold C → B, R1 = {C, B}, but the original table r still uses (A, B) as the composite primary key, associate B as the foreign key to the new table to retain the original information.
R = {a, B, d, e}, R1 = {C, B}, R. B = r1. B