(Continued from Part I)
To recall, adherence to the POOD means independent base relations--no base facts are derivable from the other base facts--that have unique relation predicates (RP) i.e., relations are uniquely constrained.
Going back to the employees example in Part 1, because salary and commission are mutually exclusive properties of employees, salaried and commissioned employees are subtypes of the employee supertype. Entity subtypes inherit the properties and constraints of the supertype.
Entity Supertype-subtype RelationshipsThe three base relations
EMPS (EMP#,ENAME,HIREDATE)represent the entity supertype and two subtypes. This design avoid NULLs, but note that aside from redundancy, the relations are not independent--supertype facts are derivable from subtype facts--in violation of the POOD.
First, with a DBMS that is not relational and does not support ESS relationships and constraint inheritance (e.g., a SQL DBMS), application/users:
- Must formulate disjunctive integrity constraints on SAL_EMPS and COMM_EMPS relations to ensure their mutual exclusivity;
- Must ensure that candidate tuples are inserted into both EMPS and one of the subtype relations;
Some data professionals contend that the RPs are unique by virtue of the distinct relation and column names (an example will be presented in Part III). Thus, for the tuple
BEGIN TRANSACTIONBut consider what happens if SALARY and COMMISSION are defined on the same domain and have overlapping value ranges and one application/user specifies the wrong name: because the relations are not uniquely constrained, the update will render the database inconsistent (without anybody being aware of it!).
INSERT INTO emps (EMP#="emp#",ENAME="ename",HIREDATE=date)
INSERT INTO sal_emps (EMP#="emp#",ENAME="ename",HIREDATE=date,SALARY=salary}
This is, of course, always the likely consequence of application/user-enforced integrity. For DBMS-enforced integrity, relation and attribute names are just references to the corresponding RPs, namely the conjunctions of
- Domain constraints
- Attribute constraints
- Tuple constraints
- Multi-tuple constraints
Note: After-the-insert relations must also satisfy, of course, any database (multi-relation) constraints to which it is subject.
POOD, ESS and View UpdatabilityLet's now comply with POOD by making EMPS a union view (for simplicity, assume the data of the two subtype base relations are stored). ESS relationships generally result in POOD designs where views represent particular entity supertypes or subtypes, so applications will be built on top of views, consistent with the general recommendation that database access should be via views to benefit from logical data independence (LI)--the insulation of applications from logical database reorganizations. For this to work, views must be updatable: the DBMS must know how to propagate view updates to the base relations from which they are--directly or indirectly--derived.
As we explained in Part I, a true RDBMS supports constraint inheritance and it can trace back the base RPs from the cataloged derived view RPs, which, courtesy of the POOD-compliant design, are unique: the base relations are uniquely constrained. When tuples are presented for insertion into the view, a RDBMS aware of the ESS relationship (i.e., it is expressed in the data language as part of the RP), it can:
- Generate the EMPS union view on its own;
- Generate and enforce the disjunctive constraints on the base relations;
- Properly propagate the view update to the (only possible) base relation;
Unfortunately, SQL DBMSs are not relational. Stay tuned for Part III.
Do you like this post? Please link back to this article by copying one of the codes below.URL: HTML link code: BB (forum) link code: