Editing Database normalization (section)

=== Satisfying 4NF ===
Assume the database is owned by a book retailer franchise that has several franchisees that own shops in different locations. And therefore the retailer decided to add a table that contains data about availability of the books at different locations:
{| class="wikitable"
|+ align="top" |'''Franchisee - Book - Location'''
!<u>Franchisee ID</u>
!<u>Title</u>
!<u>Location</u>
|-
|1
|Beginning MySQL Database Design and Optimization
|California
|-
|1
|Beginning MySQL Database Design and Optimization
|Florida
|-
|1
|Beginning MySQL Database Design and Optimization
|Texas
|-
|1
|The Relational Model for Database Management: Version 2
|California
|-
|1
|The Relational Model for Database Management: Version 2
|Florida
|-
|1
|The Relational Model for Database Management: Version 2
|Texas
|-
|2
|Beginning MySQL Database Design and Optimization
|California
|-
|2
|Beginning MySQL Database Design and Optimization
|Florida
|-
|2
|Beginning MySQL Database Design and Optimization
|Texas
|-
|2
|The Relational Model for Database Management: Version 2
|California
|-
|2
|The Relational Model for Database Management: Version 2
|Florida
|-
|2
|The Relational Model for Database Management: Version 2
|Texas
|-
|3
|Beginning MySQL Database Design and Optimization
|Texas
|-
|}

As this table structure consists of a [[Compound key|compound primary key]], it doesn't contain any non-key attributes and it's already in [[Boyce–Codd normal form|BCNF]] (and therefore also satisfies all the previous [[Database normalization#Normal forms|normal forms]]). However, assuming that all available books are offered in each area, the '''Title''' is not unambiguously bound to a certain '''Location''' and therefore the table doesn't satisfy [[Fourth normal form|4NF]].

That means that, to satisfy the [[fourth normal form]], this table needs to be decomposed as well:

{|
|
{| class="wikitable"
|+ align="top" |'''Franchisee - Book'''
!<u>Franchisee ID</u>
!<u>Title</u>
|-
|1
|Beginning MySQL Database Design and Optimization
|-
|1
|The Relational Model for Database Management: Version 2
|-
|2
|Beginning MySQL Database Design and Optimization
|-
|2
|The Relational Model for Database Management: Version 2
|-
|3
|Beginning MySQL Database Design and Optimization
|-
|}
|
{| class="wikitable"
|+ align="top" |Franchisee - Location
!<u>Franchisee ID</u>
!<u>Location</u>
|-
|1
|California
|-
|1
|Florida
|-
|1
|Texas
|-
|2
|California
|-
|2
|Florida
|-
|2
|Texas
|-
|3
|Texas
|-
|}
|}

Now, every record is unambiguously identified by a [[superkey]], therefore [[4NF]] is satisfied.