Editing Database normalization (section)

=== Satisfying 2NF ===
Recall that the '''Book''' table below has a [[composite key]] of '''{Title, Format}''', which will not satisfy 2NF if some subset of that key is a determinant.  At this point in our design the '''key''' is not finalized as the [[primary key]], so it is called a [[candidate key]]. Consider the following table: 
{| class="wikitable"
|+Book
!<u>Title</u>
!<u>Format</u>
!Author
!Author Nationality
!Price
!Pages
!Thickness
!Publisher
!Publisher Country
!Genre ID
!Genre Name
|-
|Beginning MySQL Database Design and Optimization
|Hardcover
|Chad Russell
|American
|49.99
|520
|Thick
|Apress
|USA
|1
|Tutorial
|-
|Beginning MySQL Database Design and Optimization
|E-book
|Chad Russell
|American
|22.34
|520
|Thick
|Apress
|USA
|1
|Tutorial
|-
|The Relational Model for Database Management: Version 2
|E-book
|E.F.Codd
|British
|13.88
|538
|Thick
|Addison-Wesley
|USA
|2
|Popular science
|-
|The Relational Model for Database Management: Version 2
|Paperback
|E.F.Codd
|British
|39.99
|538
|Thick
|Addison-Wesley
|USA
|2
|Popular science
|}

All of the attributes that are not part of the candidate key depend on ''Title'', but only ''Price'' also depends on ''Format''. To conform to [[Second normal form|2NF]] and remove duplicates, every non-candidate-key attribute must depend on the whole candidate key, not just part of it.

To normalize this table, make '''{Title}''' a (simple) candidate key (the primary key) so that every non-candidate-key attribute depends on the whole candidate key, and remove ''Price'' into a separate table so that its dependency on ''Format'' can be preserved:

{| class="wikitable"
|+Book
!<u>Title</u>
!Author
!Author Nationality
!Pages
!Thickness
!Publisher
!Publisher Country
!Genre ID
!Genre Name
|-
|Beginning MySQL Database Design and Optimization
|Chad Russell
|American
|520
|Thick
|Apress
|USA
|1
|Tutorial
|-
|The Relational Model for Database Management: Version 2
|E.F.Codd
|British
|538
|Thick
|Addison-Wesley
|USA
|2
|Popular science
|}

{| class="wikitable"
|+Price
!<u>Title</u>
!<u>Format</u>
!Price
|-
|Beginning MySQL Database Design and Optimization
|Hardcover
|49.99
|-
|Beginning MySQL Database Design and Optimization
|E-book
|22.34
|-
|The Relational Model for Database Management: Version 2
|E-book
|13.88
|-
|The Relational Model for Database Management: Version 2
|Paperback
|39.99
|}

Now, both the '''Book''' and '''Price''' tables conform to [[Second normal form|2NF]].