Database-三大范式

数据库的三大范式。

第一范式

A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain.

简而言之,就是每一个属性都满足不可分割的原子性。

举例:

Customer ID Telephone Number
1 555-861-2025, 192-122-1111
2 (555) 403-1659 Ext. 53; 182-929-2929

应该修改为:

Customer ID Telephone Number
1 555-861-2025
1 192-122-1111
2 (555) 403-1659 Ext. 53
2 182-929-2929

第二范式

A relation is in 2NF if it is in 1NF and every non-prime attribute of the relation is dependent on the whole of every candidate key.

简而言之,在满足第一范式的基础上,表中的字段必须完全依赖于全部主键(主要针对复合主键)。

举例:

Manufacturer Model Model Full Name Manufacturer Country
Forte X-Prime Forte X-Prime Italy
Forte Ultraclean Forte Ultraclean Italy
Hoch Toothmaster Hoch Toothmaster Germany
Hoch X-Prime Hoch X-Prime Germany

其中,Model Full Name 依赖复合主键 Manufacturer & Model,而 Manufacturer Country 只依赖于 Manufacturer,存在非完全依赖。

应该修改为:

Manufacturer Manufacturer Country
Forte Italy
Hoch Germany
Manufacturer Model Model Full Name
Forte X-Prime Forte X-Prime
Forte Ultraclean Forte Ultraclean
Hoch Toothmaster Hoch Toothmaster
Hoch X-Prime Hoch X-Prime

第三范式

A relation is in 3NF if (1) the entity is in 2NF, and (2) all the attributes are determined only by the candidate keys of that relation and not by any non-prime attributes.

简而言之,在满足第二范式的基础上,表中的字段必须完全依赖于主键,不存在传递依赖。

举例:

Tournament Year Winner Winner Date of Birth
Indiana Invitational 1998 Al Fredrickson 21 July 1975
Cleveland Open 1999 Bob Albertson 28 September 1968
Des Moines Masters 1999 Al Fredrickson 21 July 1975
Indiana Invitational 1999 Chip Masterson 14 March 1977

其中,Winner 依赖复合主键 Tournament & Year,而 Winner Date of Birth 只依赖于 Winner,存在传递依赖: Winner Date of Birth -> Winner -> Tournament & Year。

应该修改为:

Winner Date of Birth
Chip Masterson 14 March 1977
Al Fredrickson 21 July 1975
Bob Albertson 28 September 1968
Tournament Year Winner
Indiana Invitational 1998 Al Fredrickson
Cleveland Open 1999 Bob Albertson
Des Moines Masters 1999 Al Fredrickson
Indiana Invitational 1999 Chip Masterson