Editing Database normalization (section)

=== Satisfying 5NF ===
To spot a table not satisfying the [[Fifth normal form|5NF]], it is usually necessary to examine the data thoroughly. Suppose the table from [[Database normalization#Satisfying 4NF|4NF example]] with a little modification in data and let's examine if it satisfies [[Fifth normal form|5NF]]: 
{| 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
|Learning SQL
|California
|-
|1
|The Relational Model for Database Management: Version 2
|Texas
|-
|2
|The Relational Model for Database Management: Version 2
|California
|-
|}
Decomposing this table lowers redundancies, resulting in the following two tables:
{|
|
{| class="wikitable"
|+ align="top" |'''Franchisee - Book''' 
!<u>Franchisee ID</u>
!<u>Title</u>
|-
|1
|Beginning MySQL Database Design and Optimization
|-
|1
|Learning SQL
|-
|1
|The Relational Model for Database Management: Version 2
|-
|2
|The Relational Model for Database Management: Version 2
|-
|}
|
{| class="wikitable"
|+ align="top" |'''Franchisee - Location'''
!<u>Franchisee ID</u>
!<u>Location</u>
|-
|1
|California
|-
|1
|Texas
|-
|2
|California
|-
|}
|}
The query joining these tables would return the following data:
{| class="wikitable"
|+ align="top" |'''Franchisee - Book - Location JOINed'''
!<u>Franchisee ID</u>
!<u>Title</u>
!<u>Location</u>
|-
|1
|Beginning MySQL Database Design and Optimization
|California
|-
|1
|Learning SQL
|California
|-
|<span style="color:red">1</span>
|<span style="color:red">The Relational Model for Database Management: Version 2</span>
|<span style="color:red">California</span>
|-
|1
|The Relational Model for Database Management: Version 2
|Texas
|-
|<span style="color:red">1</span>
|<span style="color:red">Learning SQL</span>
|<span style="color:red">Texas</span>
|-
|<span style="color:red">1</span>
|<span style="color:red">Beginning MySQL Database Design and Optimization</span>
|<span style="color:red">Texas</span>
|-
|2
|The Relational Model for Database Management: Version 2
|California
|-
|}
The JOIN returns three more rows than it should; adding another table to clarify the relation results in three separate tables:
<br />
{|
|
{| class="wikitable"
|+ align="top" |'''Franchisee - Book'''
!<u>Franchisee ID</u>
!<u>Title</u>
|-
|1
|Beginning MySQL Database Design and Optimization
|-
|1
|Learning SQL
|-
|1
|The Relational Model for Database Management: Version 2
|-
|2
|The Relational Model for Database Management: Version 2
|-
|}
|
{| class="wikitable"
|+ align="top" |'''Franchisee - Location'''
!<u>Franchisee ID</u>
!<u>Location</u>
|-
|1
|California
|-
|1
|Texas
|-
|2
|California
|-
|}
|
{| class="wikitable"
|+ align="top" |'''Location - Book'''
!<u>Location</u>
!<u>Title</u>
|-
|California
|Beginning MySQL Database Design and Optimization
|-
|California
|Learning SQL
|-
|California
|The Relational Model for Database Management: Version 2
|-
|Texas
|The Relational Model for Database Management: Version 2
|-
|}
|}

What will the JOIN return now? It actually is not possible to join these three tables. That means it wasn't possible to decompose the '''Franchisee - Book - Location''' without data loss, therefore the table already satisfies [[5NF]].

'''Disclaimer''' - the data used demonstrates the principal, but fails to remain true.
In this case the data would best be decomposed into the following, with a surrogate key which we will call 'Store ID':
{|
|
{| class="wikitable"
|+ align="top" |'''Store - Book'''
!<u>Store ID</u>
!<u>Title</u>
|-
|1
|Beginning MySQL Database Design and Optimization
|-
|1
|Learning SQL
|-
|2
|The Relational Model for Database Management: Version 2
|-
|3
|The Relational Model for Database Management: Version 2
|-
|}
|
{| class="wikitable"
|+ align="top" |'''Store - Franchisee - Location'''
!<u>Store ID</u>
!Franchisee ID
!Location
|-
|1
|1
|California
|-
|2
|1
|Texas
|-
|3
|2
|California
|-
|}
|
|}

The JOIN will now return the expected result:
{| class="wikitable"
|+ align="top" |'''Store - Book - Franchisee - Location JOINed'''
!<u>Store ID</u>
!<u>Title</u>
!<u>Franchisee ID</u>
!<u>Location</u>
|-
|1
|Beginning MySQL Database Design and Optimization
|1
|California
|-
|1
|Learning SQL
|1
|California
|-
|2
|The Relational Model for Database Management: Version 2
|1
|Texas
|-
|3
|The Relational Model for Database Management: Version 2
|2
|California
|-
|}


[[Christopher J. Date|C.J. Date]] has argued that only a database in 5NF is truly "normalized".<ref>{{Cite book|url=https://books.google.com/books?id=Jx5UCwAAQBAJ&q=etnf%20normalization&pg=PT163|title=The New Relational Database Dictionary: Terms, Concepts, and Examples|last=Date|first=C. J.|date=December 21, 2015|publisher="O'Reilly Media, Inc."|isbn=9781491951699|pages=163|language=en}}</ref>