Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2006
    Posts
    56

    Composite primary key question

    With this senario, I am thinking that only D is incorrect. Because A alone cannot define the key. C may be wrong, but I think it depends on the value of B. I assume if a FK can be null, but it's a composite, that it would be invalid if one part of the key is null and the other is not. What do you think?

    Which of the following statement(s) is/are not True __d______:
    In relation R ( A, B, C, D, E ) , A and B together are defined as primary key, S ( X, A, B, Y, Z) is another relation, and the A and B in relation S are the same attributes A, B correspondently in R.
    a. As A and B are defined as primary key in R, so the values of A and B in R cannot be null.
    b. Attribute A in R itself cannot be null.
    c. Attribute A in S may be null.
    d. A in S can be defined as foreign key reference to A in R.
    e. A and B in S together can be defined as foreign key reference to A and B in R.

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    In C think about the fact that every time a record is generated for S it refers to R. Otherwise the database would be corrupted.

    In D think about the fact that references even to composite keys deal with each attribute individually in the child table.

  3. #3
    Join Date
    Aug 2006
    Posts
    56
    I am a bit confused. I assume a composite key elements are thought of as a whole. So if one key has a value and the other is null, then the reference is invalid. ...?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    composite key elements are indeed thought of as a whole

    statement "e" is not necessarily true because the values of A and B in S might not be the same values as in R
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2006
    Posts
    56
    So it sounds like a reference back to R can be made using just one of the FK's A or B, rather than A and B?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, that's not it at all

    which school is this? is there a book recommended for this course?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2006
    Posts
    56
    So then my original premise that D is wrong is correct?

    As far as E goes, it can be correct if the sequence matches a componation in R?

    The book is Database Design Using Entity-Relationship Diagrams, Sikha Bagui & Richard Earp

    It does a poor job explaining composite keys. Do you know of a better source?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    > So then my original premise that D is wrong is correct?
    that would've been my answer too

    > As far as E goes, it can be correct if the sequence matches a componation in R?
    i think so (but i do not know what a componation is)


    > The book is Database Design Using Entity-Relationship Diagrams, Sikha Bagui & Richard Earp
    can't say i've heard of it, but there are so many books out there...

    > It does a poor job explaining composite keys. Do you know of a better source?
    i learned from experience -- you get a real visceral understanding of how foreign keys work when you start working with them and getting the "ah ah, you can't do that" error messages when you screw up, as you will if you have composite keys and nulls...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2006
    Posts
    56
    Sorry, I meant 'Combination'


    I did play around with FK's in MySQL. I was able to make invalid key combinations in the table that had the composite FK. I assume one would use a trigger to fix that in the real world.

  10. #10
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    The catch word is "defined". Is it defined logically, physically or in the DDL?

Posting Permissions

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