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?
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.
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.
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?