How to implement common disjoint specialization in a database?

Question

Let's say there is a database for students and teachers (very simple), and a relational database:

GradStudent (_id_, name, gradStuff) UndergradStudent (_id_, name, underGradStuff) Professor (_id_, name) Teaches(_prof_id_, _stud_id_) 

Given that the relational database above is intended to represent general disjoint specialization, i.e. there is no Student table, but rather two completely separate, when you write this in SQL to implement the database, how would you get the student id for the Teaches table? I cannot figure out how to make a foreign key from two different tables.

I am writing this question assuming that the SQL language is not different across platforms. If clarification is required: I am working on Oracle SQL Developer.

Edit :: additional information / Clarification:

For a more graphical, simplified view of what I'm trying to achieve:

I want to write the following in SQL code (however, I do not know how this is possible and therefore I do not know how to do this)

My apologies if the image is too simplistic, if necessary I can add more attributes and details, just let me know.

Answer 1

I cannot figure out how to make a foreign key from two different tables.

You mean a foreign key for references to two different tables. But there is no such foreign key in this design.

We declare an SQL FOREIGN KEY for the table to say that (that is, to tell the DBMS) that the values ​​for the list of columns are also the values ​​of the list of corresponding columns (possibly the same list) that are unique to the table (maybe , same table). You do not have this. You have different restrictions on your tables.

If you need these base tables, you need to use triggers in SQL to enforce your constraints.

You can also have a design with:

• base table Student with the identifier NOT NULL UNIQUE or PRIMARY KEY
• FOREIGN KEYS from GradStudent (id), UndergradStudent (id) and Teaches (stud_id) LINKS Student (id)
• restriction that the projection of Student on id is a disjoint union of the projections of GradStudent and UndergradStudent on id

You can express part of the last restriction with a trigger. Endless way to express fragmentation (but not association):

• student_type type / tag discriminator column (say) in GradStudent, UndergradStudent and Student with optional FOREIGN (super) (id, student_type) KEYs from GradStudent and UndergradStudent to NOT NULL UNIQUE (id, student_type) in Student
• GradStudent CHECK (student_type = 'grad') and UndergradStudent CHECK (student_type = 'undergrad')

The rows in each of the two basic tables of the student subtype are the same (redundancy), and the rows in Student are determined by their identifier (redundancy), but the cost in this case does not have triggers. The student_type column may be a computed column.

There is actually no nice SQL method to ensure that every parent id is a child. The presence of only LEFT JOIN from the above child tables instead of the parent and child tables provides that each parent is a child, but requires NULL columns and additional restrictions. Triggers are needed for a reasonable limitation of SQL databases. One uses idioms to obtain any declarative restrictions.

For more on subtyping idioms, see this answer and its links . The "Sql table" of the stackoverflow database, plus child / parent, super / subtitles, super / subtypes, inheritance and / or polymorphism. There are also several / many / two FKs / relations / associations / links / links (although usually, as in this question, the required restriction is not FK, and the design should use subtypes instead). I googled "stackoverflow two foreign keys" and got this one .

Answer 2

If you want Teaches.stud_id to have an FK that refers to “GradStudent or Undergradstudent, as it is,” when selecting the student ID for the Teachers table, you cannot. The target of the FK must be the key of a table that is not a view You do not have such a table, you also do not have such a key.

The only way I can see is to encode a trigger that performs check for inserts / updates in Teaches.