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 name||Student ID||Age||Essential information|
|byebye disco||0101||17||League member||Monitor|
|Po pole rush||0102||18||League member||Learning 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:
|Full name||Student ID||class||ID||Native place||Date of birth||Gender||Home phone||Home address||Hobby||major||An association||Nation||dormitory||… …|
|king of Wu in the Three Kingdoms Era||0101||1class||0101123||Wu||The end of Eastern Han Dynasty||male||123||Eastern Wu||One hundred thousand Northern Expedition||Wood handwork||Eastern Wu||Han nationality|
|Liu Bei||0102||2Class||0102233||Shu||The end of Eastern Han Dynasty||Male||233||Bashu||Vegetable growing mat||Performance||Bashu||Han nationality||2Number||… …|
|Cao Cao||0103||3Class||0103155||Wei||Late Eastern Han Dynasty||Male||1551||Northern Wei Dynasty||The Assassins||Pretend to sleep||Northern Wei Dynasty||Han nationality||3Number||… …|
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:
|ID||Home address||Date of birth||Nation|
|0101123||Eastern Wu||The end of Eastern Han Dynasty||Han nationality|
|0102233||Bashu||The end of Eastern Han Dynasty||Han nationality|
|0103155||Northern Wei Dynasty||The end of Eastern Han Dynasty||Han nationality|
|Student ID||Full name||class||major||Professional teacher||Professional achievement|
|0101||king of Wu in the Three Kingdoms Era||1class||Wood handwork||Zhou Yu||100branch|
|0102||Liu Bei||2class||perform||Zi Long||100branch|
|0103||Cao Cao||3class||Pretend to sleep||Yang Xiu||100branch|
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 ID||Full name||class||major|
|0101||king of Wu in the Three Kingdoms Era||1class||Wood handwork|
|0103||Cao Cao||3class||Pretend to sleep|
|Wood handwork||Zhou Yu|
|Pretend to sleep||Yang 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:
|0103||Pretend to sleep||100branch|
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.
|class||Headmaster||Competition discipline||Number of certificates|
|… …||… …||… …||… …|
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.
|class||Competition discipline||Number of certificates|
|… …||… …||… …|
|… …||… …|
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.