Three-paradigm understanding of database design

The third paradigm of database design

Relationships in a relational database must meet certain requirements. Meet different levels of requirements for different paradigms. The design paradigm of database is the specification that the database design needs to meet. Only by understanding the design paradigm of the database can design a high-efficiency, elegant database, otherwise the wrong database may be designed.

At present, there are six main paradigms: the first paradigm, the second paradigm, the third paradigm, the BC Paradigm, the fourth normal form and the fifth paradigm. Meet the minimum requirements called the first normal form, referred to as 1NF. On the basis of the first paradigm to further meet some requirements for the second paradigm, referred to as 2NF. The rest and so forth.

Paradigm avoids data redundancy, reduces database space, and reduces the hassle of maintaining data integrity, but it is difficult to operate because multiple tables need to be contacted to get the data you need, and the higher the paradigm, the worse it will be. It is troublesome to weigh whether the higher paradigm is used, generally in the project, the third paradigm that is most used, and I think the use of the third paradigm is sufficient, good performance and easy to manage data.

function dependency, if the value of a field y in a table is determined by the value of another field or a set of field x, it is called the Y function dependent on X.

First Paradigm (1NF)
Definition: If the properties of each relationship R of the relationship mode R are non-divided data items, then it is called R is the pattern of the first paradigm.
Simply put, each attribute is an atomic term, indivisible.
1NF is the minimum condition that a relational pattern should have, and if the database design does not meet the first paradigm, it is not called a relational database. The relationship normalization of relational database design is performed on the 1NF.

For example (Student information sheet):
Student Number name Gender contact
20080901 Three men Email:[email protected],phone:88886666
20080902 li si female email:[email protected],phone:66668888

The above table does not conform to the first paradigm: the Contact field can be re-divided, so the change to correct is:

Student number name Sex email phone
20080901 three men [email protected] 88886666
20080902 li si female [email protected] 66668888

second paradigm (2NF)
Definition: If the relational mode R is 1NF, and each non-primary attribute full function depends on the candidate key, then it is called R is the second normal.
To put it simply, the second paradigm satisfies the following conditions: first to satisfy the first paradigm, and then for each non-primary attribute to be fully functional dependent on the candidate key or the primary key. That is, each non-primary property is determined by the entire primary key function, not part of the primary key.

For example (students choose a timetable):
Student curriculum teacher teacher Title textbook class time
John Doe Spring Zhang teacher Java lecturer "Spring in the" 301 08:00
Zhang San struts yang teacher Java lecturer "Struts in Action" 302 13:30

Here (students, courses) can determine teacher, teacher title, textbook, Classroom and class time, so you can put (students, courses) as the primary key. However, the textbook is not entirely dependent on (students, courses), only take out the course to determine the teaching material, because a course, must have designated a teaching material. This is called incomplete dependency, or partial dependency. In this case, the second paradigm is not satisfied.

After the change, select the timetable:
Student Curriculum teacher Teacher Title class time
John Doe Spring Zhang teacher Java Lecturer 301 08:00
Zhang San Struts yang teacher Java Lecturer 302 13:30

Curriculum:
Course Materials
Spring "Spring in the Bible"
Struts in Action

Therefore, the second paradigm can be said to eliminate partial dependence. The second paradigm can reduce insert exceptions, delete exceptions, and modify exceptions.

Third paradigm (3NF)
Definition: If the relationship mode R is 2NF and all the non-primary attributes in the relational mode R (u,f) have no transitive dependencies on any candidate keywords, then the relationship R is the third paradigm.
Simply put, the third paradigm satisfies the following conditions: First, the second paradigm is satisfied, and then there is no function dependency between the non-principal attributes. Because the second normal form is satisfied, each non-primary attribute is dependent on the primary key. If there is a function dependency between the non-primary attributes, there is a transitive dependency, which does not satisfy the third paradigm.

In the above example, in the selected timetable, a teacher can determine a teacher's title. In this way, teachers rely on (students, courses), and the teacher's title is dependent on teachers, which is called transmission dependence. The third paradigm is to eliminate transitive dependencies.

After the change, select the timetable:

Student Course Teacher Classroom time
John Doe Spring Zhang 301 08:00
Zhang San Struts yang teacher 302 13:30

Teacher Table:
Teachers ' titles
Teacher Zhang, Java lecturer
Teacher Yang, Java lecturer

In this way, the title of the new teacher is not selected in the course of time also have a place to save, no candidate this teacher's class when the teacher's title is not deleted, modify the teacher title when the teacher can only modify the table.

To put it simply,
The first paradigm is atomicity, and the field cannot be separated.
The second paradigm is full dependence, no partial dependence;
The third paradigm is that there is no transitive dependency.

Three-paradigm understanding of database design