SQL3 (Cont.)
Subtables (Cont.)
A row identifier may be used either implicitly for identification or explicitly, for example as a foreign-key value.
Row identifiers are specific data types with system-wide values.
(A subtable example)
A table may be declared as a subtable of other tables:
1 | CREATE TABLE employee UNDER person ( |
2 | qualification VARCHAR(10), |
4 | familymembers SET( person IDENTITY ) ); |
|
The following rules are applied for using subtable and row identifiers:
- If a tuple is inserted into a table which is a subtable of another table, then a tuple with the same row identifier needs to be inserted into the supertable.
- If a tuple in a table which has subtable is modified, all inherited attribute values in the subtable are modified accordingly.
- If a tuple in a table which is a subtable is modified, the corresponding tuples in the supertables will be modified as well.
- If a tuple is deleted from a table which is part of a subtable lattice, all corresponding tuples will be deleted as well.
Show the names of all persons living in Boston:
1 | SELECT name FROM person WHERE domicile.city = 'Boston'; |
|
Show those employees whose family members include a person named “Peter Smith:”
1 | SELECT * FROM employee a WHERE 'Peter Smith' IN |
2 | ( SELECT b.name FROM ( a.familymembers ) b ); |
|
Two fishes are in a tank.
One turns to the other and says...
How do you drive this thing?
|