Results 1 to 13 of 13
  1. #1
    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!

  2. #2
    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. #3
    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. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by temp_tsun View Post
    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. #5
    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. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by temp_tsun View Post
    BC->D
    Quote Originally Posted by temp_tsun View Post
    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 18:50.

  7. #7
    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. #8
    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. #9
    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. #10
    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. #11
    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. #12
    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. #13
    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
  •