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:
CREATE TABLE employee UNDER person (
qualification VARCHAR(10),
salary INT,
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:
SELECT name FROM person WHERE domicile.city = 'Boston';
|
Show those employees whose family members include a person named “Peter Smith:”
SELECT * FROM employee a WHERE 'Peter Smith' IN
( 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?
|