Sixth normal form

Form in relational database normalization From Wikipedia, the free encyclopedia

Sixth normal form (6NF) is a normal form used in relational database normalization which extends the relational algebra and generalizes relational operators (such as join) to support interval data, which can be useful in temporal databases.

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 Other meanings).

Definition

Summarize
Perspective

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]

Usage

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

Examples

Summarize
Perspective

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:

More information Username, Department ...
Users_table
UsernameDepartmentStatus
Close

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:

More information Username, Status ...
Users
UsernameStatus
Close
More information Username, Department ...
Users_dept
UsernameDepartment
Close

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

Following is another example:

More information Medic ID, Medic Name ...
TABLE 1
Medic ID Medic NameOccupationTypePractice in years
1 Smith JamesOrthopedicSpecialist23
2 Miller MichaelOrthopedicProbationer4
3 Thomas LindaNeurologistProbationer5
4 Scott NancyOrthopedicResident1
5 Allen BrianNeurologistSpecialist12
6 Turner StevenOphthalmologistProbationer3
7 Collins KevinOphthalmologistSpecialist7
8 King DonaldNeurologistResident1
9 Harris SarahOphthalmologistResident2
Close

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:

More information Medic ID, Medic Name ...
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
Close
More information Medic ID, Occupation ...
TABLE 2.2
Medic IDOccupation
1Orthopedic
2Orthopedic
3Neurologist
4Orthopedic
5Neurologist
6Ophthalmologist
7Ophthalmologist
8Neurologist
9Ophthalmologist
Close
More information Medic ID, Type ...
TABLE 2.3
Medic IDType
1Specialist
2Probationer
3Probationer
4Resident
5Specialist
6Probationer
7Specialist
8Resident
9Resident
Close
More information Medic ID, Practice in years ...
TABLE 2.4
Medic IDPractice in years
123
24
35
41
512
63
77
81
92
Close

Other meanings

Sixth normal form (6NF) is sometimes instead used as a synonym for domain-key normal form (DKNF). This usage predates Date et al.'s work. [12]

See also

References

Bibliography

Further reading

Loading related searches...

Wikiwand - on

Seamless Wikipedia browsing. On steroids.