reset password
Author Message
Vanquish39
Posts: 134
Posted 03:57 Apr 14, 2012 |

Hi Professor, I'm having some confusion between a key and a super key.

This question is referring to pg 19/23 on the normalization powerpoint.

I noticed and wanted to clarify what the titles are to the following relations...

{A,C,F}+ ------> {A,B,C,D,E,F}.  Is this a key or a superkey?  My answer is:  It has a potential to be a super key.

Taking the subset of {A,C,F}+, I came up with {A,C}+, {A,F}+, and {C,F}+

AC+ is nothing

AF+ is nothing

but....

CF+ produces {A,B,C,D,E,F}

 

So what's confusing me is the title of {A,C,F}+ and {C,F}+.

Is CF+ a key and ACF+ a superkey? 


Thanks.

cysun
Posts: 2935
Posted 12:03 Apr 14, 2012 |

Given the definitions of key (Slide #9) and super key (Slide #10), we can say that something is a super key if it meets the first condition of the key definition (i.e. functionally determines all attributes); and if it also meets the second condition (i.e. no proper subset of it functionally determines all attributes), we can say it is a key (i.e. not just a super key).

So in the {A,B,C,D,E,F} example, {A,C,F} is definitely a super key; however, it is not a key because {C,F}  - a proper subset of {A,C,F}, also determines all attributes.

Because {C,F} determines all attributes, we know for sure that {C,F} is at least a super key, but to decide whether {C,F} is a key, we have to check if {C,F} meets the second condition of the key definition - whether {C,F} has a proper subset that determines all attributes. Because {C} or {F} cannot determine all attributes, we know that {C,F} is a key.

Also note that {C,F} and {C,F}+ mean different things. {C,F}+ means the closure of {C,F}, i.e. {C,F}+ = {A,B,C,D,E,F}, so you can't say {C,F}+ is a key.

Vanquish39
Posts: 134
Posted 14:04 Apr 14, 2012 |

So to put the super-key vs key in a more condensed fashion, can I say the following....


If ACF+ determines all attributes of R and we find a subset of ACF+ that also determines all attributes of R, we can say that ACF+ is a super-key?

If ACF+ determines all attributes of R and we cannot find a subset of ACF+ that determines all attributes of R, we can say that ACF+ is a key?

Thank you.

cysun
Posts: 2935
Posted 15:48 Apr 14, 2012 |
Vanquish39 wrote:

So to put the super-key vs key in a more condensed fashion, can I say the following....


If ACF+ determines all attributes of R and we find a subset of ACF+ that also determines all attributes of R, we can say that ACF+ is a super-key?

If ACF+ determines all attributes of R and we cannot find a subset of ACF+ that determines all attributes of R, we can say that ACF+ is a key?

Thank you.

Yes and yes, except that we say ACF is a key/super key, not ACF+.

Vanquish39
Posts: 134
Posted 16:01 Apr 14, 2012 |

Ah I see, also professor I wanted to clarify something on the decomposition part of BCNF.  Still a little confused on how to decompose it further.  Tried the video from 2 years ago but the whiteboard doesn't show on the camera.


So if we have

{id} --> {name,address}

{assignment} --> {due}

{id,assignment} --> {grade}

 

We say {id} is a BCNF violation because the LHS {id} is not a super-key.  So we make 2 different tables R1 and R2.

R1 is {id}+ and R2 is everything else including id

So...

R1 = {id,name,address}

R2 = {assignment, grade, due, id}

What I'm confused on is decomposing this further....

Why is R1 in BCNF?  What is the exact reason for this? 

Why is R2 not in BCNF?  What is the exact reason for this?

 

________________

 

Also for this question.

R(A,B,C,D,E,F)

FD's....

AB --> C

BC --> AD

D --> E

CF --> B

Superkey = ACF

They are all violations..

So for the first one AB+ = ABCDE

R1 = {A,B,C,D,E}

R2 = {A,B,F}

Why is R1 not in BCNF or is it?

And if it's not in BCNF, you take the {A,B,C,D,E}+?

 

ORRRRR......

Can i just ask u during office hours lol?

 

Thanks.

 

 

Last edited by Vanquish39 at 21:29 Apr 14, 2012.
cysun
Posts: 2935
Posted 11:00 Apr 15, 2012 |

Here's the definition of BCNF:

A table R is in BCNF if for every nontrivial FD A -> B in R, A is a super key of R.

So is {id,name,address} BCNF?

Step 1: Find all the FD(s) of the table:

{id} -> {name, address}

Step 2: Find all the key(s) of the table (note that a table may have more than one key):

{id}

Step 3: Check the LHS of each FD and see if the LHS is a super key:

{id} is a super key because {id} is a superset of {id}

So yes, {id, name, address} is BCNF.

You can use the same procedure to check other tables.