Article From:

Relational databaseParadigm (NF)Concept: A set of relational models that conform to a certain level, representing the rationalization of the relationships among the attributes within a relationship.

It’s hard to understand, isn’t it? In general, paradigm refers to the level of a data table.

One Norm (1NF)It’s the basic level; everyone needs it. The concept is: every item in the database table is inseparable; in a simple word, all attributes are single, you can’t write two attributes in a cell, and you can’t write them anywhere. Illustration:

Full nameStudent IDAgeEssential information
byebye disco010117League memberMonitor
Po pole rush010218League memberLearning commissary

This (name, student number, age, basic information) does not meet the basic level (1NF), basic information can be re-divided, because in the data table, one grid can not write multiple data at the same time.

Two Norms (2NF)It is based on a paradigm. The concept is that instances or attributes of a data table must be uniquely distinguished. Similarly, to put it simply, you can’t put all the information together. You have to separate the tables according to the key information. Otherwise, it would be a lot of trouble to find out. Like a student information sheet, the monitor takes a student.Information sheet, the phone QQ Wechat ID card class number written in it, plus the school-related point is almost enough. But students have too much information, interests, personality, family members, and so on. It is impossible to put them in the same table. Illustration:

Student Information Table
Full nameStudent IDclassIDNative placeDate of birthGenderHome phoneHome addressHobbymajorAn associationNationdormitory… …
king of Wu in the Three Kingdoms Era01011class0101123WuThe end of Eastern Han Dynastymale123Eastern WuOne hundred thousand Northern Expedition Wood handworkEastern WuHan nationality


… …

 Liu Bei01022Class0102233 ShuThe end of Eastern Han DynastyMale233 BashuVegetable growing matPerformanceBashuHan nationality2Number… …
 Cao Cao0103 3Class0103155 WeiLate Eastern Han DynastyMale1551 Northern Wei Dynasty The AssassinsPretend to sleepNorthern Wei DynastyHan nationality3Number… …

 We can see that this table already satisfies a paradigm. His attributes were clearly defined.

Different information, their ID number is different, and their school number is different. We can say that the ID card and school number are the codes of this form.

Information such as date of birth, place of origin and so on is mainly distinguished by identity cards. Class information is basically distinguished by school number. That is to say, if I want to check the class information, I can get the school number. The ID number is not necessary for the class information I want to check. We call this table incomplete dependency.Lai. The way to get rid of this incomplete dependence is to divide a large table above into two pieces, one with the school number and one with the ID card. In this way, we can get rid of the incomplete dependence and conform to the second paradigm. After disassembly, it should be as follows:

Personal Information Sheet
IDHome addressDate of birthNation
0101123Eastern WuThe end of Eastern Han DynastyHan nationality
0102233BashuThe end of Eastern Han DynastyHan nationality
0103155Northern Wei DynastyThe end of Eastern Han DynastyHan nationality


Student Information Table
Student IDFull nameclassmajorProfessional teacherProfessional achievement
0101king of Wu in the Three Kingdoms Era1classWood handworkZhou Yu100branch
0102Liu Bei2classperformZi Long100branch
0103Cao Cao3classPretend to sleepYang Xiu100branch


Three Norms (3NF))It is based on the two paradigms. The concept is: a data table can not contain non-critical information already in other tables, attributes do not depend on other non-primary attributes. That is to say, all attributes in a table are directly determined by its code, and can not rely on other intermediate attributes, if there are such intermediate attributes.Sex, then, should be split into new tables. We can understand that the students’information sheet above, professional achievements are given by professional teachers. According to the student number, it is impossible to get professional results directly. If we need to satisfy the three paradigms, we must divide the major, the professional teacher and the professional achievement into a new table.Information sheet), the information of the original student information sheet can be directly determined by the student number, so we can say that the third paradigm is satisfied. The new table should look like this:


Student Information Table
Student IDFull nameclassmajor
0101king of Wu in the Three Kingdoms Era1classWood handwork
0102Liu Bei2classperform
0103Cao Cao3classPretend to sleep
Professional Information Table
Wood handworkZhou Yu
performZi Long
Pretend to sleepYang Xiu

Now you have to ask, how do you think of your professional performance? Professional achievement sheet, this table is based on (student number, major) as a code, performance as a non-primary attribute, then it should be an additional table:

Professional transcript
Student IDmajorachievement
0101Wood handwork100branch
0103Pretend to sleep100branch

Only in this way can the data relationship conform to the third paradigm.

BCParadigm (BCNF)It is called the revised version of the third paradigm. The concept is that there can be no dependence of non-primary attributes on the primary key subset. Let me give another example. If a school wants to count the number of competition certificates in physics, chemistry, biology and other disciplines, the school has designed such a table.

classHeadmasterCompetition disciplineNumber of certificates
1classTeacher LiuPhysics10
1classTeacher LiuChemistry15
2classTeacher WangBiology20
2classTeacher WangPhysics10
… …… …… …… …

The code of this table is (class, head teacher, competition subject). The number of certificates is the non-principal attribute. The number of certificates is determined by (class, competition subject) or (class teacher, competition subject), because class and class teacher can deduce from each other. This table conforms to the 1NF element inseparability. Conform to 2NF, because the number of certificates is determined by (class, competition subject), but (class teacher, competition subject) can also decide. For 3NF, it is also consistent, because there is only one non-principal attribute, which must be directly determined by the code. So what is the BCNF specification? That is, there can be no principal attributes of each other.Deduction. That is to say, if there is such a relationship (class – & gt; head teacher) (class teacher – & gt; class), it is not in line with BCNF. If you want to conform to BCNF, you need to split the tables above; this is the case below.

classCompetition disciplineNumber of certificates
… …… …… …

1classTeacher Liu
2classTeacher Wang
… …… …

In this way, there is no dependence of the number of certificates on the subset of codes, which conforms to BCNF. In practical application, BCNF should have low data redundancy. In fact, all the tables in my work at present satisfy the third paradigm, and some data relations only satisfy the second paradigm.

If there are any mistakes, readers are welcome to criticize and correct them. Thank you for reading and look forward to your comments.

Leave a Reply

Your email address will not be published. Required fields are marked *