数据库范式

数据库范式

范式保证数据库操作的原子化，即Insert，Update，Delete，减少数据冗余

1. Define

  1. First normal form (1NF) is a property of a relation in a relational 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

    • 属性具备原子性且唯一值

  2. Second normal form (2NF) - (1) It is in first normal form (1NF) - (2) It does not have any non-prime attribute that is functionally dependent on any proper subset of any candidate key of the relation.A non-prime attribute of a relation is an attribute that is not a part of any candidate key of the relation

    • 符合第一范式的基础上，非主键的属性不是任意其他候选键的组成部分

  3. Third normal form (3NF) - (1) the entity is in second normal form - (2) no non-prime (non-key) attribute is transitively dependent of any key i.e. no non-prime attribute depends on other non-prime attributes. All the non-prime attributes must depend on the primary key only.

    • Each attribute must represent a fact about the key, the whole key, and nothing but the key

2. 1NF Example

Customer ID     First Name     Surname     Telephone Number
123     Pooja     Singh     555-861-2025
123     Pooja     Singh     192-122-1111
456     San     Zhang     182-929-2929
456     San     Zhang     (555) 403-1659 Ext. 53
789     John     Doe     555-808-9633 
Customer ID     First Name     Surname
123     Pooja     Singh
456     San     Zhang
789     John     Doe
    
Customer Telephone Number Id     
Customer ID     Telephone Number
1     123     555-861-2025
2     123     192-122-1111
3     456     (555) 403-1659 Ext. 53
4     456     182-929-2929
5     789     555-808-9633

3. 2NF Example

Manufacturer     Model     Model Full Name     Manufacturer Country
Forte     X-Prime     Forte X-Prime     Italy
Forte     Ultraclean     Forte Ultraclean     Italy
Dent-o-Fresh     EZbrush     Dent-o-Fresh EZbrush     USA
Brushmaster     SuperBrush     Brushmaster SuperBrush     USA
Kobayashi     ST-60     Kobayashi ST-60     Japan
Hoch     Toothmaster     Hoch Toothmaster     Germany
Hoch     X-Prime     Hoch X-Prime     Germany 
Manufacturer     Manufacturer Country
Forte     Italy
Dent-o-Fresh     USA
Brushmaster     USA
Kobayashi     Japan
Hoch     Germany 

Manufacturer     Model     Model Full Name
Forte     X-Prime     Forte X-Prime
Forte     Ultraclean     Forte Ultraclean
Dent-o-Fresh     EZbrush     Dent-o-Fresh EZbrush
Brushmaster     SuperBrush     Brushmaster SuperBrush
Kobayashi     ST-60     Kobayashi ST-60
Hoch     Toothmaster     Hoch Toothmaster
Hoch     X-Prime     Hoch X-Prime

4. 3NF Example

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 
Tournament     Year     Winner
Indiana Invitational     1998     Al Fredrickson
Cleveland Open     1999     Bob Albertson
Des Moines Masters     1999     Al Fredrickson
Indiana Invitational     1999     Chip Masterson 

Winner     Date of Birth
Chip Masterson     14 March 1977
Al Fredrickson     21 July 1975
Bob Albertson     28 September 1968

Reference

  1. 如何解释关系数据库的第一第二第三范式？

  2. First normal form

  3. Second normal form

  4. Third normal form

  5. Boyce–Codd normal form

标签: none

添加新评论