Page 1 of 4 123 ... LastLast
Results 1 to 15 of 46
  1. #1
    Join Date
    May 2012
    Posts
    17

    Supertype/subtype how to enforce relationship

    I have Persons. It's a supertype. The 3, so far, subtypes are Students, Parents and Teachers.

    Most Persons will be only one. But some 2, and one case all three.

    In an ER, what is the notation to indicate that each instance of a supertype must also be at least one subtype. A Person cannot just be a person, they must be a Student, a Parent or a Teacher, and maybe more than one.

    Is there a Constraint at the Logical level that will indicate that?

    And at the physical level, I'm guessing this has to be done via a transaction?

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Why would you want that constraint?
    It seems to be a poor model.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    May 2012
    Posts
    17
    Thanks for your reply.

    I'm sure you're right. What would you suggest as a better model?

    Many thanks.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mikeymo View Post
    I'm sure you're right.
    i'm sure i disagree

    your model is just fine

    see also http://consultingblogs.emc.com/david...-Subtypes.aspx

    Last edited by r937; 05-29-12 at 10:49.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2012
    Posts
    17
    Thanks. The ER notation in that link is fine. But...

    The requirement is that the super type ('SKU' in the example) needs to be related to AT LEAST one subtype (Books, CDs, DVDs in the example). But can be related to more.

    I can't find any notation for that.

    Or how to enforce it. though I've not looked to hard at that, I'm guessing it's going to be from a Transaction fired by a trigger. Can't see any way of doing it in the DDL.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Requiring that a person be a Teacher, Student, or Parent is unnecessary.

    Your database application will work just fine without this constraint. Leave it off.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    A Person cannot just be a person, they must be a Student, a Parent or a Teacher, and maybe more than one.
    Requiring that a person be a Teacher, Student, or Parent is unnecessary.
    Your database application will work just fine without this constraint. Leave it off.
    Why does someone believe this constraint is needed?

    Is this a homework exercise or a business requirement?

  8. #8
    Join Date
    May 2012
    Posts
    17
    Looks like I've misunderstood this database lark.

    I thought the idea was that the database should be set up so incorrect data can't get stored in it. As the only sort of people we're interested in are Parents, Students and Teachers, I kinda thought that I ought to have some way that it's impossible to store a person without them being defined as one of those 3 (at the moment) type of of person.

    So do you think I should just forget this Supertype/subtype thing and have 3 separate tables? Or just allow entries in Persons without any corresponding entries in either Parents, Students or Teachers.

    Thanks for your help.

  9. #9
    Join Date
    May 2012
    Posts
    17
    Quote Originally Posted by papadi View Post
    Why does someone believe this constraint is needed?

    Is this a homework exercise or a business requirement?
    It's a timetabling system for a school.

  10. #10
    Join Date
    May 2012
    Posts
    17
    Quote Originally Posted by papadi View Post
    Why does someone believe this constraint is needed?

    Is this a homework exercise or a business requirement?
    Well that's 3 questions.

    1. 'Why does someone believe this constraint is needed?' Answer - to prevent useless data getting into the database, namely, a Person record with no clue as to what that Person is. The 'business requirement', as you put it, is that we keep records of Persons we are dealing with, not just random names.

    2. 'Is this a homework exercise...' Answer - No. Does that help?

    3. '...or a business requirement?' See 1. So yes, I suppose it is a business requirement.

    Thanks for your questions, you've really given me a lot to think about there.
    Last edited by mikeymo; 05-29-12 at 21:21.

  11. #11
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Well, NO incorrect/invalid data should be allowed to be inserted into the table. . .

    For the persons with more than one value, how are these being stored (how is/are the tables defined)?

    I asked about whether this was for a class assignment because often there are no options and the directions must be followed.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if it's a real requirement, and not a homework assignment, then you don't need to bother with the ER diagram

    but you do have to enforce it in the database

    you want to prevent adding a person (in parent table) without also adding a subtype row (in one of three child tables)

    a trigger would do that

    but a transaction would also do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    May 2012
    Posts
    17
    Quote Originally Posted by papadi View Post
    Well, NO incorrect/invalid data should be allowed to be inserted into the table. . .

    For the persons with more than one value, how are these being stored (how is/are the tables defined)?

    I asked about whether this was for a class assignment because often there are no options and the directions must be followed.
    I see. I'm glad I was right about incorrect/invalid data not being allowed into the table.

    I'm thinking of this:

    Persons table:
    Person ID (PK)
    First Name
    Last Name
    etc.

    Students table:
    StudentID (PK)
    PersonID (FK)
    Student Details....

    Parents table:
    ParentID (PK)
    PersonID (FK)
    Parent Details...

    Teachers table:
    TeacherID (PK)
    PersonID (FK)
    Teacher Details...

  14. #14
    Join Date
    May 2012
    Posts
    17
    Quote Originally Posted by r937 View Post
    if it's a real requirement, and not a homework assignment, then you don't need to bother with the ER diagram

    but you do have to enforce it in the database

    you want to prevent adding a person (in parent table) without also adding a subtype row (in one of three child tables)

    a trigger would do that

    but a transaction would also do it
    I see. So ER diagrams are only used for homework assignments are they? Good news for me if I don't have to bother with one of those then.

    I'm imagining a transaction which is started by a trigger.

    Something like ON INSERT of the Persons table checks for a matching record in one of the subtype tables and if not rolls back the whole thing. With a deferred constraint on the FK check in the subtype table. Or something else that has the same effect. Am I on the right lines with that?

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mikeymo View Post
    Or something else that has the same effect. Am I on the right lines with that?
    yeah, something else that has the same effect

    you would add the person row first, and the trigger would add the student or parent or teacher row

    note that the student and parent and teacher tables do ~not~ have their own id columns as PKs -- they use personid as both FK and PK
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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