View source for Sixth normal form

{{Short description|Form in relational database normalization}}
{{confused|Domain-key normal form}}

'''Sixth normal form''' ('''6NF''') is a [[Database normalization#Normal forms|normal form]] used in [[relational database]] [[database normalization|normalization]] which extends the relational algebra and generalizes relational operators (such as [[Join (SQL)|join]]) to support interval data, which can be useful in [[temporal database]]s.

The term 6NF has historically also been used to refer to another normalization degree, which today is more commonly known as [[domain-key normal form]] (DKNF) (see [[Sixth normal form#Other meanings|Other meanings]]).

== Definition ==
[[Christopher J. Date]] and others have defined sixth normal form as a [[Database normalization#Normal forms|normal form]], based on an extension of the relational algebra.{{sfn|Date |Darwen |Lorentzos |2003}}{{sfn |Date |Darwen |Lorentzos|2014}}{{sfn|Harrington|2009|pp=125–126}}

Relational operators, such as ''join'', are generalized to support a natural treatment of interval data, such as [[Sequence number|sequences]] of dates or moments in time, for instance in [[temporal database]]s.{{sfn|Date|Darwen|Lorentzos|2003|pp=141–160}}{{sfn |Date |Darwen |Lorentzos|2014}}{{sfn|Harrington|2009|pp=125–126}} Sixth normal form is then based on this generalized join, as follows:

<blockquote>A [[relvar]] R [table] is in sixth normal form (abbreviated 6NF) if and only if it satisfies no nontrivial join dependencies at all — where, as before, a [[join dependency]] is trivial if and only if at least one of the projections (possibly U_projections) involved is taken over the set of all attributes of the relvar [table] concerned.{{sfn|Date|Darwen|Lorentzos|2003|p=176}}
</blockquote>

Date et al. have also given the following definition:
<blockquote>Relvar R is in sixth normal form (6NF) if and only if every JD [Join Dependency] of R is trivial — where a JD is trivial if and only if one of its components is equal to the pertinent heading in its entirety.{{sfn|Date|Darwen|Lorentzos|2014|p=213}}</blockquote>

Any relation in 6NF is also in [[fifth normal form|5NF]].

Sixth normal form is intended to decompose relation variables to [[Irreducibility (mathematics)|irreducible]] components. Though this may be relatively unimportant for non-temporal relation variables, it can be important when dealing with temporal variables or other interval data. For instance, if a relation comprises a supplier's name, status, and city, we may also want to add temporal data, such as the time during which these values are, or were, valid (e.g., for historical data) but the three values may vary independently of each other and at different rates. We may, for instance, wish to trace the history of changes to Status; a review of production costs may reveal that a change was caused by a supplier changing city and hence what they charged for delivery.

For further discussion on Temporal Aggregation in SQL, see also Zimanyi.{{sfn| Zimanyi|2006 }} For a different approach, see [[TSQL2]].{{sfn|Snodgrass}}

==Usage==
The sixth normal form is currently as of 2009 being used in some [[data warehouse]]s where the benefits outweigh the drawbacks,<ref>See [http://www.anchormodeling.com the Anchor Modeling website] for a website that describes a data warehouse modelling method based on the sixth normal form</ref> for example using [[anchor modeling]]. Although using 6NF leads to an explosion of tables, modern databases can prune the tables from select queries (using a process called '[[table elimination]]' - so that a query can be solved without even reading some of the tables that the query refers to<ref>[https://mariadb.com/kb/en/what-is-table-elimination/ What is Table Elimination? - MariaDB Knowledge Base]</ref>) where they are not required<!--". Queries that only access several attributes will then be faster than similar queries in databases modelled in the [[Third normal form]]." Note: this is not what the original text nor the citation say, so I am reverting it for now.-->  and thus speed up queries that only access several attributes.

==Examples==
{{Disputed section|talk=6NF Examples|date=June 2017}}
In order for a table to be in sixth normal form, it has to be in [[fifth normal form]] first and then it requires that each table satisfies only trivial join dependencies.
Let's take a simple example<ref>Example provided by: http://www.anattatechnologies.com/q/2011/07/normalization-6nf/</ref> with a table already in 5NF:
Here, in the users table, every attribute is non null and the primary key is the username:

{| class="wikitable"
|+Users_table
|-
! Username !! Department !! Status
|}

This table is in 5NF because each join dependency is implied by the unique [[candidate key]] of the table (Username). More specifically, the only possible join dependencies are: {username, status}, {username, department}.

The 6NF version would look like this:

{| class="wikitable"
|+Users
|-
! Username !! Status
|}

{| class="wikitable"
|+Users_dept
|-
! Username !! Department
|}

So, from one table in 5NF, 6NF produces two tables.

Following is another example:

{| class="wikitable"
|+TABLE 1
|-
!Medic ID
! Medic Name !! Occupation !! Type !! Practice in years
|-
|1
| Smith James || Orthopedic || Specialist || 23
|-
|2
| Miller Michael || Orthopedic || Probationer || 4
|-
|3
| Thomas Linda || Neurologist || Probationer || 5
|-
|4
| Scott Nancy || Orthopedic || Resident || 1
|-
|5
| Allen Brian || Neurologist || Specialist || 12
|-
|6
| Turner Steven || Ophthalmologist || Probationer || 3
|-
|7
| Collins Kevin || Ophthalmologist || Specialist || 7
|-
|8
| King Donald || Neurologist || Resident || 1
|-
|9
| Harris Sarah || Ophthalmologist || Resident || 2
|}

The join dependencies of the table are {medic name, occupation}, {medic name, practice in years} and {medic name, type}. Hence we could see that such table is 2NF (due to the appearance of transitive dependency). The following tables try to bring it to 6NF:

{| class="wikitable"
|+TABLE 2.1
|-
!Medic ID
! Medic Name 
|-
|1
| Smith James 
|-
|2
| Miller Michael 
|-
|3
| Thomas Linda 
|-
|4
| Scott Nancy 
|-
|5
| Allen Brian 
|-
|6
| Turner Steven 
|-
|7
| Collins Kevin 
|-
|8
| King Donald 
|-
|9
| Harris Sarah 
|}

{| class="wikitable"
|+TABLE 2.2
|-
! Medic ID !! Occupation
|-
| 1 || Orthopedic
|-
| 2 || Orthopedic
|-
| 3 || Neurologist
|-
| 4 || Orthopedic
|-
| 5 || Neurologist
|-
| 6 || Ophthalmologist
|-
| 7 || Ophthalmologist
|-
| 8 || Neurologist
|-
| 9 || Ophthalmologist
|}

{| class="wikitable"
|+TABLE 2.3
|-
! Medic ID !! Type
|-
| 1 || Specialist
|-
| 2 || Probationer
|-
| 3 || Probationer
|-
| 4 || Resident
|-
| 5 || Specialist
|-
| 6 || Probationer
|-
| 7 || Specialist
|-
| 8 || Resident
|-
| 9 || Resident
|}

{| class="wikitable"
|+TABLE 2.4
|-
! Medic ID !! Practice in years
|-
| 1 || 23
|-
| 2 || 4
|-
| 3 || 5
|-
| 4 || 1
|-
| 5 || 12
|-
| 6 || 3
|-
| 7 || 7
|-
| 8 || 1
|-
| 9 || 2
|}

== Other meanings ==
Sixth normal form (6NF) is sometimes instead used as a synonym for [[domain-key normal form]] (DKNF).<!-- {{citation needed|date=December 2009}} Note, succeeding reference actually does confirm this. --> This usage predates Date et al.'s work. {{sfn|dbdebunk}}

== See also ==
* [[Anchor modeling]]
* [[Data vault modeling]]
* [[Database theory]] 
* [[Temporal database]]
*[[First normal form]] (1NF)
*[[Second normal form]] (2NF)
*[[Third normal form]] (3NF)
*[[Fourth normal form]] (4NF)
*[[Fifth normal form]] (5NF)

==References==
{{reflist|2}}

==Bibliography ==
{{Refbegin}}
<!--{{sfn|Date|Darwen|Lorentzos|2003}}-->
*{{cite book | ref =<!-- da da lo-->
| last1 = Date | first1 = Chris J. | author-link1 = Christopher J. Date|first2=Hugh|last2= Darwen |author-link2= Hugh Darwen | first3=Nikos A. |last3= Lorentzos  |author-link3= Nikos Lorentzos| title = Temporal Data and the Relational Model: A Detailed Investigation into the Application of Interval and Relation Theory to the Problem of Temporal Database Management |date=January 2003 | publisher = Elsevier LTD | location = Oxford | isbn = 1-55860-855-9 }}
<!--{{sfn|Date|Darwen| Lorentzos|2014}}-->
*{{cite book | ref =<!-- date-darwin-Lorentzos-->| last1 = Date | first1 = Chris J. | author-link1 = Christopher J. Date|first2=Hugh|last2= Darwen |author-link2= Hugh Darwen | first3=Nikos A. |last3= Lorentzos  |author-link3= Nikos Lorentzos| title = Time and relational theory - Temporal databases in the relational model and SQL | date= 12 August 2014 | publisher = Elsevier-Morgan Kaufmann | isbn = 9780128006313 }}
*{{cite web | last = Snodgrass | first = Richard T. | title = TSQL2 Temporal Query Language | url = http://www.cs.arizona.edu/~rts/tsql2.html }}
<!--{{sfn| Zimanyi }}-->
*{{cite web
| ref =<!--  zimanyi-->
| url = http://www.sigmod.org/publications/sigmod-record/0606/sigmod-record.june2006.pdf | title = Temporal Aggregates and Temporal Universal Quantification in Standard SQL | work = ACM SIGMOD Record, volume 35, number 2, page 16 | publisher = [[Association for Computing Machinery|ACM]] | last = Zimanyi|first= E.|date=June 2006}}
<!--{{sfn| dbdebunk}}-->
*{{cite web
| ref ={{sfnref|dbdebunk}}
| last = Date | first = Chris J. | author-link = Christopher J. Date | title = ON DK/NF NORMAL FORM | url = http://www.dbdebunk.com/page/page/621935.htm
|archive-url= https://web.archive.org/web/20120406123712/http://www.dbdebunk.com/page/page/621935.htm |archive-date= 6 April 2012}}
<!--{{sfn|Harrington|2009|pp=125–126}}-->
*{{cite book | date= 2009| last1 = Harrington | first1 = Jan L.| title = Relational Database Design and Implementation: Clearly Explained  | publisher = Elsevier-Morgan Kaufmann | isbn = 9780123747303}}
{{Refend}}

==Further reading ==
*{{cite book | last = Date | first = C.J. | author-link1 = Christopher J. Date | title = The relational database dictionary: a comprehensive glossary of relational terms and concepts, with illustrative examples | series = O'Reilly Series Pocket references | year = 2006 | publisher = O'Reilly Media, Inc. | isbn = 978-0-596-52798-3 | page = [https://archive.org/details/relationaldataba0000date_a8w3/page/90 90] | url = https://archive.org/details/relationaldataba0000date_a8w3/page/90 }}

{{Database normalization}}
{{Data warehouse}}
{{Use dmy dates|date=December 2018}}

[[Category:Database normalization|6NF]]

[[nl:Zesde normaalvorm]]