1

This explains how to do Gen Spec when we have disjoint Specialization classes, although it uses SQL Server. Note : MySQL now allows CHECK so we don't have to worry about that anymore.

I have a couple of queries though.

  1. How can I update both the Super and Subclasses ? Here is what I have read till now : Create a view by doing an inner join of the Super Class with the Sub Class. Next I CANNOT update both the Super and the Sub Class with ONE insert. I need to insert the relevant parts of the tuple into the Super / Sub Classes SEPERATELY. So ONE tuple insert into the View which modifies BOTH the Super and the Sub Classes is NOT possible. For example, I would need to break down the tuple so that I insert first into the Super Class and then insert into the Sub Class. Is there not any other better way?

  2. How do I enforce total participation of the Super Class with respect to the Sub Classes?

As suggested in the referred link, it says "That can be fixed by adding a constraint that the superclass's ids exist in a union of the ids in the subclass tables." How can I convert this into a trigger? After what operation should this trigger activate ? It is not clear to me.

  1. I am curious, how do I convert this into a Overlapping IsA Relationship. By this I mean ONE Superclass may be belonging to multiple Subclasses at the same time.
user2338823
  • 501
  • 1
  • 3
  • 16
  • *MySQL now allows CHECK* Too restricted - within current record only, subqueries not allowed in it. See [CHECK Constraints](https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html), starting from "CHECK condition expressions must adhere to the following rules". – Akina Jan 21 '20 at 11:12
  • @Akina Which DBMS permits subqueries in a CHECK constraint? – Paul Spiegel Jan 21 '20 at 11:15
  • @PaulSpiegel SQL server allows. Another - must be seen, I was interested never... – Akina Jan 21 '20 at 11:16
  • Dave Markle's answer in the quoted link uses CHECK in a fashion that does'nt need subqueries I think. Will work, no ? – user2338823 Jan 21 '20 at 11:16
  • *Dave Markle's answer* He uses single FK check + CHECK constraint within the record. And I do not see the reason to use this CHECK constraint at all (moreover, I think `SubClass` field is excess and unusable with conjunction of the constraint - what is the point of having a field in the structure if its value is the same for absolutely all records in the table?). I should remove both the constraint and the field. – Akina Jan 21 '20 at 11:21
  • @Akina, the point having that common field with the same entry is that it allows us to test that the SAME type is there in the SuperClass, Sub Class. – user2338823 Jan 21 '20 at 11:27
  • So if somebody is both Musician and Athlet we must have 2 separate records in Students table which differs in `SubClass` field only for him. Do you really think this is good? Where is normalization? – Akina Jan 21 '20 at 11:32
  • In the answer it is disjoint specialization. We won't have both musician and athlete. – user2338823 Jan 21 '20 at 11:34
  • I predict that the logic needed will exceed what `CHECK` can provide. Instead, depend more on app code and less on SQL. – Rick James Jan 23 '20 at 06:37
  • @RickJames I wish to see some examples of this. What should I be reading? – user2338823 Jan 29 '20 at 05:30

0 Answers0