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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s