Results 1 to 13 of 13

031310, 12:55 #1Registered User
 Join Date
 Feb 2009
 Posts
 13
Decomposition theorem (properties, BCNF, DP)
Hello,
I've done my homework about databases. It is even corrected, however at some points I disagree with the answers, can someone help me to get this cleared up?
Let R(XYZ) relation with attributes XYZ, if there is a X>Y (functional dependency), then the decomposition R(XY) and R(XZ) is lossless. (this is what it said in my assignment, not the point to argue about )
Given R(ABCDE) and the functional dependencies:
A > BCD,
BC> D,
AD>DE.
Give the properties of the following decompositions:
 Is the following decomposition in BCNF?
 Is the following decomposition dependency preserving?
I had a really tough time to see wheter it was in BCNF, can someone show me how you can "see" whether it is in BCNF? And whether it is dependency preserving?
R(ABCD) R(ADE),
this one I thought it was not in BCNF and did preserve the functional dependencies. And I was correct, but can someone show me how it is suppose to be done? (checking for those properties?
Thanks!

031310, 15:48 #2Registered User
 Join Date
 Dec 2007
 Location
 London, UK
 Posts
 741
BCNF means that the determinant of every nontrivial FD is a superkey. You haven't stated the candidate keys so it's not possible to say if either relation is in BCNF. I guess you are expected to work out what the keys should be. Have you done that?

031310, 17:01 #3Registered User
 Join Date
 Feb 2009
 Posts
 13
Ehm no, because this is the data I only got. How can I work out the keys? Can't I work out the keys based on the functional dependecies?
Because it seems natural to me that A is the key? Because if I know A, I know ABCD and if I know AD then I get to know DE. So I would know everything. Am I right?
What is a nontrivial FD exactly? Cause I know the "formal" definition, but it's really hard to understand for me =(.

031310, 17:27 #4Registered User
 Join Date
 Dec 2007
 Location
 London, UK
 Posts
 741
You think A should be the only key? BC is a determinant too.
What is a nontrivial FD exactly?

031310, 17:31 #5Registered User
 Join Date
 Feb 2009
 Posts
 13
I am sorry, but I cannot see why BC is determinant? As I cannot get back the A? (I am sorry, if I am wrong)
So first I need to find all the candidate keys right?

031310, 17:38 #6Registered User
 Join Date
 Dec 2007
 Location
 London, UK
 Posts
 741
Otherwise how can you know whether every nontrivial determinant is a superkey? A determinant like BC for example.
Put another way: You are right about A being the key implied by those dependencies. I just wondered if you knew why you were right and what that would then mean if BC was not a superkey.Last edited by dportas; 031310 at 17:50.

031310, 17:50 #7Registered User
 Join Date
 Feb 2009
 Posts
 13
I think I'm kind of stupid, but is BC a candidate key? I would say no because it does not determine A right? So all the candidate keys are A? It determinates D but that's not enough, it needs to determine everything right?

031310, 17:53 #8Registered User
 Join Date
 Dec 2007
 Location
 London, UK
 Posts
 741
Correct and that's why it is not in BCNF. BC is a determinant that is not a candidate key or a superkey. In fact the R(ABCD) is not in 3NF either.

031310, 17:58 #9Registered User
 Join Date
 Feb 2009
 Posts
 13
Wait a second, then why can other relations be in BCNF? If it all uses the same functional dependencies? :
Like this one: (ABC), (BCD), (ADE). My tutor says that when it is decomposed like this, it is in BCNF? How can this be?

031310, 18:07 #10Registered User
 Join Date
 Dec 2007
 Location
 London, UK
 Posts
 741
Remember that Normal Form is a property of each relation individually, based on what dependencies are being satisfied by that relation. So in the relation (ABC) the attributes BC are not determinant for any attribute in that relation. Therefore BCNF is not violated. If every relation by itself is in BCNF then the whole schema is said to be in BCNF.

031310, 18:12 #11Registered User
 Join Date
 Feb 2009
 Posts
 13
But BC determines every attribute in relation (BCD) so it's a key, and therefor still not violating the BCNF right?

031310, 18:15 #12Registered User
 Join Date
 Dec 2007
 Location
 London, UK
 Posts
 741
Yes, you've got it. That's why it's important to identify candidate keys when specifying a schema. It's poor practice to leave them out and just list the attribute names  I say so even having just done that myself.

031310, 18:18 #13Registered User
 Join Date
 Feb 2009
 Posts
 13
Ah I got it, so now to the next subject:
How can I effectively search whether a relation scheme is depenency preserving?
Like in my first example: R(ABCD) R(ADE)