Nothing but the key 属性全部依赖于主键 third norm form

全依赖

Designs that Violate 1NF

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

Designs that Comply with 1NF

Customer Name
Customer ID First Name Surname
123 Pooja Singh
456 San Zhang
789 John Doe

Customer Telephone Number
Customer ID Telephone Number
123 555-861-2025
123 192-122-1111
456 (555) 403-1659 Ext. 53
456 182-929-2929
789 555-808-9633

NOT 2NF

Electric Toothbrush Models
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
Kobayashi	ST-60	Kobayashi ST-60	Japan
Hoch	Toothmaster	Hoch Toothmaster	Germany
Hoch	X-Prime	Hoch X-Prime	Germany

Even if the designer has specified the primary key as {Model Full Name}, the relation is not in 2NF because of the other candidate keys. {Manufacturer, Model} is also a candidate key, and Manufacturer Country is dependent on a proper subset of it: Manufacturer. To make the design conform to 2NF, it is necessary to have two relations:

Electric Toothbrush Manufacturers

Manufacturer Manufacturer Country
Forte Italy
Dent-o-Fresh USA
Kobayashi Japan
Hoch Germany

Electric Toothbrush Models

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

1-值具有原子性，比如客户的电话号码单行至多一个号码；

2-“不能既有主键又有复合主键”：行仅有一种唯一性体现方式；

3-An approximation of Codd's definition of 3NF, paralleling the traditional pledge to give true evidence in a court of law, was given by Bill Kent: "[Every] non-key [attribute] must provide a fact about the key, the whole key, and nothing but the key."^[7] A common variation supplements this definition with the oath: "so help me Codd".^[8]

https://en.wikipedia.org/wiki/Third_normal_form

巴特西

Nothing but the key 属性全部依赖于主键 third norm form

Designs that Violate 1NF

Designs that Comply with 1NF

最新文章

热门文章