FD, 3NF and BCNF

Let’s get a good yet simple definition or grasp on FD or functional dependency:

The definition of a functional dependency looks like A->B. In this case B is a single attribute but it can be as many attributes as required (for instance, X->J,K,L,M). In the functional dependency, the determinant (the left hand side of the -> sign) can determine the set of attributes on the right hand side of the -> sign. This basically means that A selects a particular value for B, and that A is unique. In the second example X is unique and selects a particular set of values for J,K,L, and M. It can also be said that B is functionally dependent on A. In addition, a particular value of A ALWAYS gives you a particular value for B, but not vice-versa.

ref: http://db.grussell.org/section008.html

This same reference above gives us example of 3NF:

3NF is an even stricter normal form and removes virtually all the redundant data :

• A relation is in 3NF if, and only if, it is in 2NF and there are no transitive functional dependencies
• Transitive functional dependencies arise:
• when one non-key attribute is functionally dependent on another non-key attribute:
• FD: non-key attribute -> non-key attribute
• and when there is redundancy in the database

Let’s go to another section from Grussell for Boyce-Codd definition:

http://db.grussell.org/section009.html

• When a relation has more than one candidate key, anomalies may result even though the relation is in 3NF.
• 3NF does not deal satisfactorily with the case of a relation with overlapping candidate keys
• i.e. composite candidate keys with at least one attribute in common.
• BCNF is based on the concept of a determinant.
• A determinant is any attribute (simple or composite) on which some other attribute is fully functionally dependent.
• A relation is in BCNF is, and only if, every determinant is a candidate key.

OK, let’s review what we have learned so far (from Grussell or other basic knowledge):

1. BCNF is based on determinant.
2. Determinant is a single or composite attribute where another attribute is dependent.
3. BCNF is where every determinant is a Candidate Key.  In other words, we can’t remove any attribute from the determinant or it will lose its uniqueness.  It already is decomposed down as far as possible.