Pondering 3NF to BCNF

The table’s superkeys are:

  • S1 = {Court, Start Time}
  • S2 = {Court, End Time}
  • S3 = {Rate Type, Start Time}
  • S4 = {Rate Type, End Time}
  • S5 = {Court, Start Time, End Time}
  • S6 = {Rate Type, Start Time, End Time}
  • S7 = {Court, Rate Type, Start Time}
  • S8 = {Court, Rate Type, End Time}
  • ST = {Court, Rate Type, Start Time, End Time}, the trivial superkey

Good examples and all here from Wikipedia.  Allow me to ramble a bit on my findings.

AB means A is a subset of B.

I’d like to add just a little bit more from the example:

“However, only S1 to S4 are candidate keys (that is, minimal Superkeys for that relation) because e.g. S1 ⊂ S5, so S5 cannot be a candidate key.

The table does not adhere to BCNF. This is because of the dependency Rate Type → Court, in which the determining attribute (Rate Type) is neither a candidate key nor a superset of a candidate key.”

Now, I hope I am getting this right from this example.  I just want to break this down a bit more, and forgive if this is old news.  It just has helped me when I started to see these…   

Start Time by itself, or End Time by itself can’t be considered Superkeys for this table.  The reason is as cited, that they are not unique. There are 2 courts, so there can be more than one start time or end time.  S1-S8 and the trivial Superkey are all Superkeys since they are unique.

Only S1-S4 are considered Candidate Keys because you can’t decompose these any further without breaking uniqueness.  For example, S3 is comprised of Rate Type and Start Time which can be used to discover what has been scheduled (since there is a different rate for either of the courts), but if you remove any part of this Candidate Key it will lose that qualification as well as being a Superkey.  It no longer will be unique.

What I’ve been leading up to is BCNF.   We will find that BCNF is based on the concept of a determinant.  I don’t really think this is the most important definition of BCNF, but I wanted to define what I found on it.  If you look up the definition of determinant you will find it simply means an attribute where another attribute is dependent. 

Definition: A determinant in a database table is any attribute that you can use to determine the values assigned to other attribute(s) in the same row.

ref: http://databases.about.com/cs/specificproducts/g/determinant.htm

All examples above from S1-S8 are in this category since each one of these can be used to determine the values in question. 

The most important definition to me on BCNF is that every determinant for BCNF has to be a Candidate Key.   The point here is that the table from our example is not in BCNF because it uses Court, Start Time, End Time, and Rate Type in this table.  As shown above, this can’t be a Candidate Key because it would not qualify to be a minimal Superkey.  In other words, we can remove an attribute or two and still have it be a determinant and a Superkey. 
What this means is that we would have to re-design our database in order to achieve BCNF.  If the design was decomposed down to Rate Types and Today’s Bookings (as shown in Wikipedia’s example), it would include the decomposed Candidate Keys and would achieve BCNF.  This would require additional Joins, and in some situations it might mean the loss of some dependencies.  I would put 3NF in my toolbox if I could only have one.  Having both BCNF and 3NF to choose from is of course the blessing, or perhaps the curse if I don’t keep my eye on all these issues (smile).  I don’t know if this helps or not, but I wanted to share the things I was looking at and pondering.

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