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,
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?
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?
BCNF means that the determinant of every non-trivial 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?
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?
You think A should be the only key? BC is a determinant too.
What is a non-trivial FD exactly?
Trivial FDs are ones where the determinant includes the dependent attributes and so the FD is always satisfied. So AD->D would be trivial (how could D not determine itself?) but AD->DE is non-trivial. It's the non-trivial ones that matter.
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?
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.
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.