©2020-2024 ioDraw All rights reserved
<> What is paradigm ?
Normal form is a kind of criterion that database design follows , Different specifications require different paradigms .
<> The three most commonly used paradigms
First paradigm (1NF)： Attribute indivisible , That is, each attribute is an indivisible atomic item .( The attributes of an entity are the columns in the table )
Second normal form (2NF)： Satisfy the first normal form ; And there is no partial dependency , That is, the non primary attribute must be completely dependent on the primary attribute .( Primary attribute is primary key ; Full dependency is for federated primary keys , A non primary key column cannot depend on only part of the primary key )
The third paradigm (3NF)： Satisfy the second normal form ; And there is no delivery dependency , That is, non primary attributes cannot have dependencies with non primary attributes , Non primary attribute must be directly dependent on primary attribute , Cannot indirectly depend on the primary attribute .（A ->
B, B ->C, A -> C）
<> Examples 3NF：
<>1NF
Attribute cannot be subdivided , That is, each column in the table can no longer be split .
The following student information sheet (student)：
id,name( full name ),sex_code( Gender code ),sex_desc( Gender description ),contact( contact information )
primary key(id)
idnamesex_codesex_desccontact
001 Zhang San 0 male 17835201234_ Yuncheng City, Shanxi Province xx village
002 Lisi 0 male 17735204567_ Luliang City, Shanxi Province yy village
003 Wang Wu 1 female 18835207890_ Taiyuan City, Shanxi Province zz village
If the student's phone number is often used when querying the student table , Contact information (contact) This column is divided into telephone numbers (phone) And address (address) Two columns , This is the first paradigm .
Modify to make the table meet 1NF after ：
idnamesex_codesex_descphoneaddress
001 Zhang San 0 male 17835201234 Yuncheng City, Shanxi Province xx village
002 Lisi 0 male 17735204567 Luliang City, Shanxi Province yy village
003 Wang Wu 1 female 18835207890 Taiyuan City, Shanxi Province zz village
Judge whether the table conforms to the first normal form , Whether the column can be subdivided , It depends on the demand , If the telephone number and address are separated, the query and other requirements can be met
Hour , The previous table design was not satisfied 1NF of , If the telephone number and address are spliced as a field, it can also meet the query , Storage and other requirements , Then it will be satisfied 1NF.
<>2NF
In meeting 1NF Under the premise of , There are no partial dependencies in the table , Non primary key columns are completely dependent on primary keys .( Mainly in the case of Federated primary keys , A non primary key column cannot depend on only part of the primary key )
Student transcript below (score)：
stu_id( student id),kc_id( curriculum id),score( fraction ),kc_name( Course name )
primary key(stu_id, kc_id)
stu_idkc_idscorekc_name
001101185 High number 3-1
001102279 Principle of computer composition
002101159.9 High number 3-1
The primary key in the table is stu_id and kc_id Combined primary key . satisfy 1NF; Non primary key column score Completely dependent on primary key ,stu_id and kc_id Two values to determine score Value of ; and kc_name Rely only on kc_id, And stu_id No dependencies , It does not depend entirely on the primary key , Depends on only part of the primary key , Non conformance 2NF.
Modify to make the table meet 2NF after ：
Transcript (score) primary key(stu_id)
stu_idkc_idscore
001101185
001102279
002101159.9
Class Schedule Card (kc) primary key(kc_id)
kc_idkc_name
1011 High number 3-1
1022 Principle of computer composition
Change the original score sheet (score) Split into grades (score) And Curriculum (kc), And both tables match 2NF.
<>3NF：
In meeting 2NF Under the premise of , No delivery dependency exists .(A -> B, B -> C, A->C)
The following student information sheet (student)：
primary key(id)
idnamesex_codesex_descphoneaddress
001 Zhang San 0 male 17835201234 Yuncheng City, Shanxi Province xx village
002 Lisi 0 male 17735204567 Luliang City, Shanxi Province yy village
003 Wang Wu 1 female 18835207890 Taiyuan City, Shanxi Province zz village
In the table sex_desc Dependent on sex_code, and sex_code Dependent on id( Primary key ), So that sex_desc Dependent on id( Primary key );sex_desc Not directly dependent on primary key , Instead, rely on the primary key by relying on non primary key columns , Is a transitive dependency , Non conformance 3NF.
Modify table to meet 3NF after ：
Student form (student) primary key(id)
idnamesex_codephoneaddress
001 Zhang San 017835201234 Yuncheng City, Shanxi Province xx village
002 Lisi 017735204567 Luliang City, Shanxi Province yy village
003 Wang Wu 118835207890 Taiyuan City, Shanxi Province zz village
Gender code table (sexcode) primary key(sex_code)
sex_codesex_desc
0 male
1 female
Change the original student After splitting the table , Both tables are satisfied 3NF.
<> What kind of table is easier to match 3NF?
Tables with fewer non primary key columns .(1NF Emphasize that the column cannot be subdivided ;2NF and 3NF Emphasize the relationship between non primary attribute columns and primary attribute columns )
Such as code table (sexcode), There is only one non primary key column sex_desc;
Or design the primary key of the student table as primary key(id,name,sex_code,phone), In this way, non primary key columns only have address, Easier to comply 3NF.
Technology