Boyce–Codd normal form: Difference between revisions
m +de |
|||
| Line 52: | Line 52: | ||
[[Category:Database normalization|BCNF]] |
[[Category:Database normalization|BCNF]] |
||
[[de:Normalisierung (Datenbank)#Boyce-Codd-Normalform (BCNF)]] |
|||
[[es:Forma normal Boyce-Codd]] |
[[es:Forma normal Boyce-Codd]] |
||
Revision as of 14:21, 29 November 2007
Boyce-Codd normal form (or BCNF) is a normal form used in database normalization. It is a slightly stronger version of the third normal form (3NF). A table is in Boyce-Codd normal form if and only if, for every one of its non-trivial functional dependencies X → Y, X is a superkey—that is, X is either a candidate key or a superset thereof.
BCNF was developed in 1974 by Raymond F. Boyce and Edgar F. Codd[1] to address certain types of anomaly not dealt with by 3NF as originally defined. Chris Date has pointed out that a definition of what we now know as BCNF appeared in a paper[2] by Ian Heath in 1971. "Since that definition predated Boyce and Codd's own definition by some three years," writes Date, "it seems to me that BCNF ought by rights to be called Heath normal form. But it isn't."[3]
Example
Only in rare cases does a 3NF table not meet the requirements of BCNF. An example of such a table is:
| Tutor ID | Tutor Soc. Security Num. | Student ID |
|---|---|---|
| 1078 | 088-51-0074 | 31850 |
| 1078 | 088-51-0074 | 37921 |
| 1293 | 096-77-4146 | 46224 |
| 1480 | 072-21-2223 | 31850 |
The purpose of the table is to show which tutors are assigned to which students. The table's candidate keys are:
- {Tutor ID, Student ID}
- {Tutor Social Security Number, Student ID}
Therefore all three attributes of the table are prime attributes: that is, all three attributes belong to candidate keys.
Recall that 2NF prohibits partial functional dependencies of non-prime attributes on candidate keys, and that 3NF prohibits transitive functional dependencies of non-prime attributes on candidate keys. Since the table above lacks any non-prime attributes, it adheres to both 2NF and 3NF.
BCNF is more stringent than 3NF in that it does not permit any functional dependency in which the determining set of attributes is not a candidate key (or superset thereof). The dependency of Tutor ID on Tutor Social Security Number is such a dependency. Accordingly, the table above is not in BCNF.
Any table that falls short of BCNF will be vulnerable to logical inconsistencies. In the table above, an inconsistent combination of Tutor ID and Tutor Social Security Number could be represented.
Correcting the problem in this case would be a simple matter of using only one scheme of identifiers for Tutors: either IDs or Social Security Numbers, but not both.
References
- ^ Codd, E. F. "Recent Investigations into Relational Data Base Systems." IBM Research Report RJ1385 (April 23rd, 1974). Republished in Proc. 1974 Congress (Stockholm, Sweden, 1974). New York, N.Y.: North-Holland (1974).
- ^ Heath, I. "Unacceptable File Operations in a Relational Database." Proc. 1971 ACM SIGFIDET Workshop on Data Description, Access, and Control, San Diego, Calif. (November 11th-12th, 1971).
- ^ Date, C.J. Database in Depth: Relational Theory for Practitioners. O'Reilly (2005), p. 142.
Further reading
- Date, C. J. (1999), An Introduction to Database Systems (8th ed.). Addison-Wesley Longman. ISBN 0-321-19784-4.
External links
- Rules Of Data Normalization
- Date, C.J., & Darwen, H., & Pascal, F. Database Debunkings
- Advanced Normalization by ITS, University of Texas.
 | This database-related article is a stub. You can help Wikipedia by expanding it. |