# Thread: Decomposition theorem (properties, BCNF, DP)

1. Registered 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,

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?

Thanks!

2. Registered User
Join Date
Dec 2007
Location
London, UK
Posts
741
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?

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

4. Registered User
Join Date
Dec 2007
Location
London, UK
Posts
741
Originally Posted by temp_tsun
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.

5. Registered 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?

6. Registered User
Join Date
Dec 2007
Location
London, UK
Posts
741
Originally Posted by temp_tsun
BC->D
Originally Posted by temp_tsun
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.
Last edited by dportas; 03-13-10 at 17:50.

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

8. Registered 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.

9. Registered 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?

10. Registered 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.

11. Registered 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?

12. Registered 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.

13. Registered 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)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•