Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    14

    Question Q: Supertype/Subtype relationship

    Hi all,

    I am reading J. Hoffer’s Database Management book and need your opinions on the following supertype/subtype relationship concept.

    This book suggested using the different modified forms of Supertype’s Primary Key for Subtypes as foreign key. For example, if the supertype PK is ABC_ID, we should use
    A_ABC_ID for subtype A
    B_ABC_ID for subtype B
    C_ABC_ID for subtype C

    I made and uploaded a low-resolution image of related paragraphs and diagrams at the following address:

    http://www.geocities.com/fellowreade...stionimage.htm
    or
    geocities.com/fellowreaderz/questionimage.htm

    My question is:

    1) When the subtypes have no overlap (like the example in the book), why can’t we use the unchanged supertype’s PK (ABC_ID) (without adding the discriminators) in all subtypes? Like ordinary one-to-one relationships?

    Any help and comment would be appreciated.

    Thanks
    Sam
    .

  2. #2
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43
    1) When the subtypes have no overlap (like the example in the book), why can’t we use the unchanged supertype’s PK (ABC_ID) (without adding the discriminators) in all subtypes? Like ordinary one-to-one relationships?
    I would like to know this also. I just checked out the passage, and I cant see a reason why you would bother adding a discriminator to the PK for the subtype, as the subtype table is seperation enough.
    Last edited by jwab; 08-09-04 at 09:12.

  3. #3
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43
    After a little thought I might have the answer.

    his SQL included the line:

    WHERE employee_number = s_employee_number;

    This is apposed to:

    WHERE employee.employee_number = salaried_employee.employee_number;

    I think the design approach maybe to simplify SQL queries.

  4. #4
    Join Date
    Dec 2003
    Posts
    14
    Quote Originally Posted by jwab
    After a little thought I might have the answer.

    his SQL included the line:

    WHERE employee_number = s_employee_number;

    This is apposed to:

    WHERE employee.employee_number = salaried_employee.employee_number;

    I think the design approach maybe to simplify SQL queries.
    Thanks jwab. You are right the SQL codes is shorter. But if this is the main reason, at least for me, using:

    "WHERE employee.employee_number = salaried_employee.employee_number;"

    is much easier than creating 3 new incremented keys with specific structure (Stringe+Supertype's PK)

    I wonder if ignoring the book recommendation and using the ordinary one-to-one relationships (supertype's PK in subtypes as foreign key + their own PK) causes any problem.

    By the way, I missed a paragraphy while creating the book image. I uploeded a corrected one at:

    http://www.geocities.com/fellowreade...stionimage.htm

    direct link to image
    www.geocities.com/fellowreaderz/myquestion.gif

    I look forward further ideas?

    Thanks again
    Sam
    .

  5. #5
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    The confusion lies in his imprecise nomenclature. He merely is referring to adding a prefix to the attribute name of the sub table primary key (which refers to the pk of the supertype table) for, as was noted, a slight change in the SQL.

    For example, he proposes (SQL shorthand):
    CREATE TABLE subtype( s_item_id REFERS supertype( item_id ) )

    instead of
    CREATE TABLE subtype( item_id REFERS supertype( item_id ) )

    You could just as easily do:
    CREATE TABLE subtype( subtype_id REFERS supertype( item_id ) )

    or anything else.

    He's also incorrect in saying that the relational model does not "support" entity sub/supertypes. That is not surprising.
    Thanks,

    Matt

  6. #6
    Join Date
    Dec 2003
    Posts
    14
    Thanks a lot Matt.
    Now, I am more comfortable to try other ways.

    Cheers
    Sam

Posting Permissions

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