Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    Join Date
    Aug 2008
    Location
    Australia
    Posts
    23

    Referential relationship

    Take a relvar DOG with the attributes DOG_ID and DOG_NAME. Take another relvar DOG_TYPE containing attributes DOG_BREED and DOG_COLOR. Is it possible for these two relvars to have a referential relationship even though they have no attributes in common? I don't think so, but am unsure.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    What's a "relvar"? I'm guesing its either the aliens that appear in South Park episodes, or perhaps a form of male enhancement medication?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    The answer is yes, if "referential relationship" just means a multi-relvar constraint.

    If "referential relationship" actually means "foreign key" then I'd say the answer is "perhaps". The term "foreign key" rarely seems to be precisely defined in textbooks. Most examples tend to assume the referencing and referenced attributes have the same name but there's no obvious reason why that MUST be so. Presumably they must at least be of the same type or have a common supertype. And then there is the somewhat arbitrary restriction that a foreign key must only reference a candidate key...

  4. #4
    Join Date
    Aug 2008
    Location
    Australia
    Posts
    23
    Thanks, very helpful. The thing that really threw me was that I have now learnt from you (and via other reading) is that attributes in foreign keys do not have to have the same name that the attributes in the associated primary key, they only need be of the same type. Almost every example I have seen shows the attribute names being the same so I assumed that this was necessary. However it seems you can reference keys regardless of the attribute names involved. Most books I have read failed to make this clear early on.

    Take this example;
    Relvar PLAYER contains attributes ID and TEAM_ID.
    Relvar PLAYER_HISTORY contains attributes PLAYER_ID and SURNAME.

    Now I previously thought that there was no way that these two relvars could have a referencing relatoinship as they had no common attributes. BUT it now appears that they can. ID and PLAYER_ID could have such a relationship if they are of the same type (which they are). It seems you can select keys and reference them however you want as long as the types are the same. The attribute names are immaterial. Do I have this correct?

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Correct. The attribute names are unimportant in principle. In practice it may depend on the syntax of the language used to define a foreign key. A syntax that absolutely required names to be the same would not be very useful I think, but most examples and many database designers tend to use consistent names for attributes wherever they occur.

    The standard Tutorial D doesn't have any special syntax for a referential constraint. It just has general-purpose relvar constraints.

    SQL on the other hand has a syntax for a so-called "FOREIGN KEY" constraint that is not the same as what is usually understood to be a foreign key in the relational model: the SQL one can reference a super key whereas RM conventionally uses the term "foreign key" to mean a constraint that references a candidate key. This is a potential cause of confusion.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I should learn this stuff. Sometimes, I just don't know how I manage to do my job.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Aug 2008
    Location
    Australia
    Posts
    23
    Thanks again, I am stumbling through this now. Here is another aspect that you may so kind as to clear up, forgive me if my question is nonsensical.

    If we were to add another relvar to my database described above so we now have;
    Relvar PLAYER contains attributes ID and TEAM_ID.
    Relvar PLAYER_HISTORY contains attributes PLAYER_ID and SURNAME.
    Relvar SCORING contains attributes PLAYER_ID and GOALS.

    {ID}/{PLAYER_ID} is the glue (technical term) that holds this database together. How would the referencing of these 3 relvars be implented? Here are some ideas;

    * SCORING{PLAYER_ID} references PLAYER_HISTORY{PLAYER_ID} which in turn references PLAYER{ID} - ie a chain.
    * SCORING{PLAYER_ID} references PLAYER{ID} and PLAYER_HISTORY{PLAYER_ID} also references PLAYER{ID} - ie a Y shape.
    * SCORING{PLAYER_ID} references PLAYER_HISTORY{PLAYER_ID} which in turn references PLAYER{ID} which in turn refernces SCORING{PLAYER_ID} - ie a ring.

    Which of these relationship schemes would be used?

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    It depends on what these relvars mean. What is a PLAYER_HISTORY and how is it different from a PLAYER given that all it contains is an ID and a surname? Is it to cope with players who change their surnames over time? What are the keys of the PLAYER and PLAYER_HISTORY relvars?

    Looking at it another way, who scored the SCORING: the PLAYER or the PLAYER_HISTORY?

  9. #9
    Join Date
    Aug 2008
    Location
    Australia
    Posts
    23
    OK I have been doing some more thinking and found some Webpages that clearly explain the basics about referencing. So here goes an attempt at structuring at my 3 relvar database above using keys that reference each other;

    I would make PLAYER{ID} a primary key
    I would make SCORING{PLAYER_ID} a foreign key that references PLAYER{ID}
    I would also make PLAYER_HISTORY{PLAYER_ID} a foreign key that references PLAYER{ID}

    Does this make sense?

    If I am correct I can't believe my textbook made such a simple concept so incredibly complex. If I am wrong it is back to the drawing board.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by dportas
    Correct. The attribute names are unimportant in principle. In practice it may depend on the syntax of the language used to define a foreign key. A syntax that absolutely required names to be the same would not be very useful I think, but most examples and many database designers tend to use consistent names for attributes wherever they occur......
    however it good practice to make them sort of similar, but the overriding concern must be that the column name must be immediately obvious. As a general rule I wouldn't repeat the table namein the column defintion

    eg
    table: contacts
    id
    title
    forename
    ...etc

    rather than

    table: contacts
    contacts_id
    contacts_title
    contacts_forename
    ...etc

    some people recommend the plural of the entity as the table name (eg contacts, containing details of multiple contact). the primary key (especially of autogenerated columns is ID for numeric), Code for alphanumeric

    when used as a foreign key in the 'child' table it can become contact_id, as it immediately identifies that this column is the ID from table contacts.

    but what is fundamenatl is whatever naming convention you use you are consistent. theres little point in referring to tel_no in one table and telephone_number in another, like wise be consistent with you underscores and camel casing.. some people like telno, some like tel_no, some like TelNo... its often personal (or site preference)....


    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by enuenu
    I would make PLAYER{ID} a primary key
    I would make SCORING{PLAYER_ID} a foreign key that references PLAYER{ID}
    I would also make PLAYER_HISTORY{PLAYER_ID} a foreign key that references PLAYER{ID}

    Does this make sense?
    yes, and don't you agree that it is actually rather simple

    of course, all my experience is with tables and not "relvars," using SQL (ptui!!!) and not a relational database

    i are deprived
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by r937
    of course, all my experience is with tables and not "relvars," using SQL (ptui!!!) and not a relational database

    i are deprived
    Where did you learn databases: School of Hard Knocks, or University of Life?

    ;-)

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    on the job training -- started using SQL on DB2 in 1987

    there were no courses in relational concepts back then
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Aug 2008
    Location
    Australia
    Posts
    23
    To sound like a broken record, I can't believe Date made such a simple concept so difficult to understand. I found one or two Websites that conveyed the basic concept of how referential relationships work in about 2 paragraphs that I understood in about 2 minutes. Now I have a basic understanding I can re-read Date and try to gain an appreciation of the finer details of the theory.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    In theory, theory and practice are identical. In practice, theory and practice are incompatible.

    -PatP

Posting Permissions

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