Sixth normal form

Sixth normal form (6NF) is a term in relational database theory, used in two different ways.

## Contents

* 1 6NF (C. Date's definition)
* 2 Domain-key normal form
* 3 Usage
* 4 Examples
* 5 References
* 6 Bibliography
* 7 Further reading

## 6NF (C. Date's definition)

Christopher J. Date and others have defined sixth normal form as a normal form, based on an extension of the relational algebra.[1][2][3]

Relational operators, such as join, are generalized to support a natural treatment of interval data, such as sequences of dates or moments in time, for instance in temporal databases.[4][2][3] Sixth normal form is then based on this generalized join, as follows:

> 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.[5]

Date et al. have also given the following definition:

> 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.[6]

Any relation in 6NF is also in 5NF.

Sixth normal form is intended to decompose relation variables to 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.[7] For a different approach, see TSQL2.[8]

## Domain-key normal form

Some authors have used the term sixth normal form differently: as a synonym for domain-key normal form (DKNF). This usage predates Date et al.'s work. [9]

## Usage

The sixth normal form is currently being used in some data warehouses where the benefits outweigh the drawbacks,[10] 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') where they are not required and thus speed up queries that only access several attributes.

## Examples

This section's factual accuracy is disputed. Please help to ensure that disputed statements are reliably sourced. (June 2017) (Learn how and when to remove this template message)

|

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[11] with a table already in 5NF: Here, in the users table, every attribute is non null and the primary key is the username:

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:

Users

Username | Status

|

Users_dept

Username | Department

|

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

Following is another example:

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:

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

TABLE 2.2

Medic ID | Occupation

|

1 | orthopedic
2 | orthopedic
3 | neurologist
4 | orthopedic
5 | neurologist
6 | ophthalmologist
7 | ophthalmologist
8 | neurologist
9 | ophthalmologist

TABLE 2.3

Medic ID | Practice in years

|

1 | 23
2 | 4
3 | 5
4 | 1
5 | 12
6 | 3
7 | 7
8 | 1
9 | 2

TABLE 2.4

Medic ID | Type

|

1 | specialist
2 | probationer
3 | probationer
4 | resident
5 | specialist
6 | probationer
7 | specialist
8 | resident
9 | resident

## References

1. ↑ Date, Darwen & Lorentzos 2003.
2. ↑ 2.0 2.1 Date, Darwen & Lorentzos 2014.
3. ↑ 3.0 3.1 Harrington 2009, pp. 125–126.
4. ↑ Date, Darwen & Lorentzos 2003, pp. 141–160.
5. ↑ Date, Darwen & Lorentzos 2003, p. 176.
6. ↑ Date, Darwen & Lorentzos 2014, p. 213.
7. ↑ Zimanyi 2006.
8. ↑ Snodgrass.
9. ↑ dbdebunk.
10. ↑ See the Anchor Modeling website for a website that describes a data warehouse modelling method based on the sixth normal form
11. ↑ Example provided by: http://www.anattatechnologies.com/q/2011/07/normalization-6nf/

## Bibliography

* {{cite book | ref =
* Date, Chris J.; Darwen, Hugh; Lorentzos, Nikos A. (12 August 2014). Time and relational theory - Temporal databases in the relational model and SQL. Elsevier-Morgan Kaufmann. ISBN 9780128006313.
* Snodgrass, Richard T.. "TSQL2 Temporal Query Language". http://www.cs.arizona.edu/~rts/tsql2.html.
* Zimanyi, E. (June 2006). "Temporal Aggregates and Temporal Universal Quantification in Standard SQL". ACM SIGMOD Record, volume 35, number 2, page 16. ACM. http://www.sigmod.org/publications/sigmod-record/0606/sigmod-record.june2006.pdf.
* "Error: no `|title=` specified when using {{Cite web}}".Error:+no+`|title=`+specified when+using+{{[[Template:Cite+web|Cite+web]]}}[[Category:Articles+with+incorrect+citation+syntax|Sixth+normal+form]]&rft.atitle=&rfr_id=info:sid/en.wikibooks.org:Sixth_normal_form">
* Harrington, Jan L. (2009). Relational Database Design and Implementation: Clearly Explained. Elsevier-Morgan Kaufmann. ISBN 9780123747303.

## Further reading

* Date, C.J. (2006). The relational database dictionary: a comprehensive glossary of relational terms and concepts, with illustrative examples. O'Reilly Series Pocket references. O'Reilly Media, Inc.. p. 90. ISBN 978-0-596-52798-3. https://archive.org/details/relationaldataba0000date_a8w3/page/90.

* v
* t
* e

Database normalization

* Unnormalized form (UNF/NF2)
* First normal form (1NF)
* Second normal form (2NF)
* Third normal form (3NF)
* Elementary key normal form (EKNF)
* Boyce–Codd normal form (3.5NF / BCNF)
* Fourth normal form (4NF)
* Fifth normal form (5NF / PJNF)
* Domain-key normal form (DKNF)
* Sixth normal form (6NF)

Denormalization

* v
* t
* e

Data warehouses

Creating a data warehouse

| Concepts|

* Database
* Dimension
* Dimensional modeling
* Fact
* OLAP
* Star schema
* Snowflake schema
* Reverse star schema
* Aggregate

|

Variants|

* Anchor modeling
* Column-oriented DBMS
* Data vault modeling
* HOLAP
* MOLAP
* ROLAP
* Operational data store

Elements|

* Data dictionary/Metadata
* Data mart
* Sixth normal form
* Surrogate key

Fact|

* Fact table
* Early-arriving fact
* Measure

Dimension|

* Dimension table
* Degenerate
* Slowly changing

Filling|

* Extract-Transform-Load (ETL)
* Extract
* Transform
* Load

Using a data warehouse

| Concepts|

* Business intelligence
* Dashboard
* Data mining
* Decision support system (DSS)
* OLAP cube
* Data warehouse automation

|

Languages|

* Data Mining Extensions (DMX)
* MultiDimensional eXpressions (MDX)
* XML for Analysis (XMLA)

Tools|

* Business intelligence software
* Reporting software
* Spreadsheet

Related

| People|

* Bill Inmon
* Ralph Kimball

|

Products|

* Comparison of OLAP servers
* Data warehousing products and their producers

nl:Zesde normaalvorm

0.00

(0 votes)

Original source: https://en.wikipedia.org/wiki/Sixth normal form. Read more

|

Retrieved from "https://handwiki.org/wiki/index.php?title=Sixth_normal_form&oldid=3378157"
*[v]: View this template
*[t]: Discuss this template
*[e]: Edit this template