169
Chapter 7: Relational Database Design and Creating the Physical Database Schema
7
Best Practice
If the entity has a good primary key and every attribute is scalar and fully dependent on the primary
key, the logical design is in the third normal form. Most database designs stop at the third normal form.The additional forms prevent problems with more complex logical designs. If you tend to work with
mind-bending modeling problems and develop creative solutions, understanding the advanced forms
can prove useful.The Boyce-Codd Normal Form (BCNF)
The Boyce-Codd normal form occurs between the third and fourth normal forms, and it
handles a problem with an entity that has multiple candidate keys. One of the candidate
keys is chosen as the primary key, and the others become alternative keys. For example, a
person might be uniquely identifi ed by his or her Social Security number (ssn), employee
number, and driver’s license number. If the ssn is the primary key, the employee number
and driver’s license number are the alternative keys.The Boyce-Codd normal form simply stipulates that in such a case every attribute must
describe every candidate key. If an attribute describes one of the candidate keys but not
another candidate key, the entity violates BCNF.Fourth Normal Form (4NF)
The fourth normal form deals with problems created by complex composite primary keys. If
two independent attributes are brought together to form a primary key along with a third
attribute, but the two attributes don’t uniquely identify the entity without the third attri-
bute, the design violates the 4NF. For example, assume the following conditions:- Regional Offi ce and the regional offi ce’s Manager were used as a composite primary
key. - A Store and the Manager were brought together as a primary key.
- Because both used a manager all three were combined into a single entity.
The preceding example violates the fourth normal form.
The fourth normal form is used to help identify entities that should be split into separate
entities. Usually this is only an issue if large composite primary keys have brought too
many disparate objects into a single entity.Fifth Normal Form (5NF)
The fi fth normal form provides the method for designing complex relationships that involve
multiple (three or more) entities. A three-way or ternary relationship, if properly designed,
is in the fi fth normal form. The cardinality of any of the relationships could be one or
many. What makes it a ternary relationship is the number of related entities.c07.indd 169c07.indd 169 7/30/2012 4:18:17 PM7/30/2012 4:18:17 PM
http://www.it-ebooks.info