The Boyce – Codd normal form (abbreviated as BCNF from the English Boyce – Codd normal form ) is one of the possible normal relationship forms in a relational data model .
Sometimes the normal Boyce – Codd form is called the reinforced third normal form , since it is stronger (stricter) in all respects compared to the previously determined 3NF [1] .
It is named after Ray Boyce and Edgar Codd , although Christopher Date indicates that in fact a strict definition of the “third” normal form, equivalent to the definition of the normal Boyce-Codd form, was first given by Ian Heath in 1971, therefore this form should be called the "normal Hit form" [1] .
Content
Definition
A relationship variable is in BCNF if and only if each of its non-trivial and left-irreducible functional dependencies has a potential key as its determinant [1] .
Less formally, the relationship variable is in the normal Boyce-Codd form if and only if the determinants of all its functional dependencies are potential keys.
To determine BCNF, the concept of the functional dependence of relationship attributes should be understood.
Let R be a relation variable, and X and Y be arbitrary subsets of the set of attributes of the variable relation R. Y is functionally dependent on X if and only if, for any admissible value of the variable of the relation R , if two tuples of the variable of the relation R coincide in the value of X , they also coincide in the value of Y. The subset X is called the determinant , and Y is the dependent part .
A functional dependence is trivial if and only if its right (dependent) part is a subset of its left part (determinant).
A functional dependence is called irreducible on the left if no attribute can be omitted from its determinant without breaking the dependency (in other words, the determinant is not redundant).
The situation when the relation will be in 3NF, but not in BCNF, arises, for example, provided that the relation has two (or more) potential keys that are composite, and there is a functional dependence between the individual attributes of such keys. Since the described dependence is not transitive, such a situation does not fall under the definition of 3NF. In practice, such relationships are quite rare; for all other relationships, 3NF and BCNF are equivalent.
Example
Suppose you are considering a relationship that represents data on booking tennis courts for the day:
| Court number | Start time | End time | Rate |
|---|---|---|---|
| one | 09:30 | 10:30 | "Court 1 for club members" |
| one | 11:00 | 12:00 | "Court 1 for club members" |
| one | 2 p.m. | 15:30 | "Court 1 for non-club members" |
| 2 | 10 a.m. | 11:30 | "Court 2 for non-club members" |
| 2 | 11:30 | 13:30 | "Court 2 for non-club members" |
| 2 | 3 p.m. | 16:30 | "Court 2 for club members" |
Thus, the following compound potential keys are possible: { Court number , Start time }, { Court number , End time }, { Tariff , Start time }, { Tariff , End time }.
The ratio corresponds to the second ( 2NF ) and third ( 3NF ) normal form. The requirements of the second normal form are satisfied, since all attributes are included in one of the potential keys, and there are no non-key attributes in relation. There are also no transitive dependencies, which corresponds to the requirements of the third normal form. Nevertheless, there is a functional dependence Tariff → Court number , in which the left part (determinant) is not a potential key of the relation, that is, the relation is not in the normal Boyce – Codd form .
The disadvantage of this structure is that, for example, by mistake, you can attribute the rate “Court 1 for club members” to the reservation of the second court, although it can apply only to the first court.
You can improve the structure by decomposing the relationship into two, obtaining relations satisfying the BCNF (the attributes included in the primary key are emphasized). For greater clarity, the attribute For club members has been added to the tariff information:
| Rate | Court number | For club members |
|---|---|---|
| "Court 1 for club members" | one | Yes |
| "Court 1 for non-club members" | one | Not |
| "Court 2 for club members" | 2 | Yes |
| "Court 2 for non-club members" | 2 | Not |
| Rate | Start time | End time |
|---|---|---|
| "Court 1 for club members" | 09:30 | 10:30 |
| "Court 1 for club members" | 11:00 | 12:00 |
| "Court 1 for non-club members" | 2 p.m. | 15:30 |
| "Court 2 for non-club members" | 10 a.m. | 11:30 |
| "Court 2 for non-club members" | 11:30 | 13:00 |
| "Court 2 for club members" | 3 p.m. | 16:30 |
Notes
- ↑ 1 2 3 Date C. J. Introduction to Database Systems. - 8th ed. - M.: "Williams", 2006
Literature
Russian
- Kogalovsky M.R. Encyclopedia of database technologies. - M .: Finance and statistics , 2002. - 800 p. - ISBN 5-279-02276-4 .
- Kuznetsov S. D. Fundamentals of databases. - 2nd ed. - M .: Internet University of Information Technology; BINOMIAL. Laboratory of Knowledge, 2007. - 484 p. - ISBN 978-5-94774-736-2 .
Transferable
- Date C. J. Introduction to Database Systems = Introduction to Database Systems. - 8th ed. - M .: Williams , 2005 .-- 1328 p. - ISBN 5-8459-0788-8 (Russian) 0-321-19784-4 (English).
- Connolly T., Begg K. Databases. Design, implementation and maintenance. Theory and Practice = Database Systems: A Practical Approach to Design, Implementation, and Management. - 3rd ed. - M .: Williams , 2003 .-- 1436 p. - ISBN 0-201-70857-4 .
- Garcia-Molina G., Ulman J. , Widom J. Database Systems. Full Course = Database Systems: The Complete Book. - Williams , 2003 .-- 1088 p. - ISBN 5-8459-0384-X .
Foreign
- CJ Date . Date on Database: Writings 2000–2006. - Apress , 2006 .-- 566 p. - ISBN 978-1-59059-746-0 , 1-59059-746-X.