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!

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?

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 non-trivial FD exactly? Cause I know the "formal" definition, but it's really hard to understand for me =(.

You think A should be the only key? BC is a determinant too.

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 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?

Otherwise how can you know whether every non-trivial 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.

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?

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.

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?

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.

But BC determines every attribute in relation (BCD) so it's a key, and therefor still not violating the BCNF right?

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.

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)