Elementary key normal form
{{Short description|Enhancement on third normal form when there is more than one unique compound key and they overlap}}
{{multiple issues|
{{technical|date=December 2012}}
{{more footnotes|date=May 2018}}
}}
Elementary key normal form (EKNF) is a subtle enhancement on third normal form, thus EKNF tables are in 3NF by definition. This happens when there is more than one unique compound key and they overlap. Such cases can cause redundant information in the overlapping column(s).
History
Definition
A table is in EKNF if and only if all its elementary functional dependencies begin at whole keys or end at elementary key attributes. For every full non-trivial functional dependency of the form X→Y, either X is a key or Y is (a part of) an elementary key.{{cite journal |title=A New Normal Form for the Design of Relational Database Schemata |last=Zaniolo |first=Carlo |url=http://www.cs.ucla.edu/~zaniolo/papers/tods82b.pdf |journal=ACM Transactions on Database Systems |volume=7 |issue=3 |date=September 1982|pages=489–499 |doi=10.1145/319732.319749 |s2cid=15790238 }}{{page needed|date=May 2018}}
In this definition, an elementary functional dependency is a full functional dependency (a non-trivial functional dependency X → A such that there is no functional dependency X' → A that also holds with X' being a strict subset of X), and an elementary key is a key X for which there exists an attribute A such that X → A is an elementary functional dependency.
Example
{{Expand section|date=March 2024}}
For an example of a table whose highest normal form is EKNF, see Boyce–Codd normal form#Achievability of BCNF.
Notes
{{reflist}}
References
- {{cite book |title=Information Modeling and Relational Databases |last1=Halpin |first1=T. A. |last2=Morgan |first2=Antony J. |last3=Morgan |first3=Tony}}
- {{cite book |title=Database Design and Relational Theory |last=Date |first=C. J. |chapter=Elementary key normal form (EKNF)}}
- {{cite book |title=SQL for Smarties: Advanced SQL Programming |edition=3rd |last=Celko |first=Joe}}
{{Database normalization}}