Abstract
Inclusion dependencies support essential semantic aspects of the standard relational data model. An inclusion dependency is defined as the existence of attributes in a table whose values must be a subset of the values of the corresponding attributes in another table (Codd, 1990; Abiteboul, Hull, & Vianu, 1995; Connolly & Begg, 2004). Formally, it can be expressed as R[X]?S[Z]. R and S are relation names. With X and Z as compatible attributes, R[X] and S[Z] are the inclusion dependency’s left and right sides respectively. When Z is the primary key of S or it is restricted by a unique clause, the inclusion dependency is key-based (also named referential integrity restriction, rir). In this case, X is a foreign key (FK) for R. On the contrary, if Z does not constitute the key of the relation, the inclusion dependency is non-key-based (simply, an inclusion dependency, id). Both rirs and ids are referential constraints. Rirs are important because they contain basic local semantic characteristics, which have been elicited from the Universe of Discourse (UofD). They are sufficient to symbolize many natural semantic links such as the relationships and hierarchies that are captured by semantic models (Abiteboul et al., 1995). Conversely, ids do not appear as a product of the translation ‘conceptual schema?logical schema’, but because of ad-hoc changes made in the phase of detail design, some denormalization degree, or the presence of complex n-ary relationship constructs. In this scenario, ids frequently misrepresent objects and their corresponding inter-object relationships.
TopIntroduction
Inclusion dependencies support essential semantic aspects of the standard relational data model. An inclusion dependency is defined as the existence of attributes in a table whose values must be a subset of the values of the corresponding attributes in another table (Codd, 1990; Abiteboul, Hull, & Vianu, 1995; Connolly & Begg, 2004). Formally, it can be expressed as R[X]⊆S[Z]. R and S are relation names. With X and Z as compatible attributes, R[X] and S[Z] are the inclusion dependency’s left and right sides respectively. When Z is the primary key of S or it is restricted by a unique clause, the inclusion dependency is key-based (also named referential integrity restriction, rir). In this case, X is a foreign key (FK) for R. On the contrary, if Z does not constitute the key of the relation, the inclusion dependency is non-key-based (simply, an inclusion dependency, id). Both rirs and ids are referential constraints.
Rirs are important because they contain basic local semantic characteristics, which have been elicited from the Universe of Discourse (UofD). They are sufficient to symbolize many natural semantic links such as the relationships and hierarchies that are captured by semantic models (Abiteboul et al., 1995). Conversely, ids do not appear as a product of the translation ‘conceptual schema→logical schema’, but because of ad-hoc changes made in the phase of detail design, some denormalization degree, or the presence of complex n-ary relationship constructs. In this scenario, ids frequently misrepresent objects and their corresponding inter-object relationships.
Rirs can be declaratively defined via the SQL foreign key clause (SQL:1999-2, 1999) and are enforced by most current database systems:
FOREIGN KEY (<referencing column list>) REFERENCES <referenced table name> [(<referenced column list>)]
If <referenced column list> is omitted, the foreign key refers to the primary key of <referenced table name>.
The rirs can be specified with respect to different match types: simple (implicit if no match option is declared), partial, and full. As stated in the SQL:1999 standard document: If <match type> is not specified, then for each row in the referencing table, either the referencing column has at least one null value or its value matches the value of a corresponding row in the referenced table. If PARTIAL is specified, then for each row in the referencing table the value of each foreign key column is null, or it has at least one non-null value that equals the corresponding referenced column value. Finally, FULL means that, for each row in the referencing table, either all foreign key values are null or they equal the value of the corresponding referenced column (Türker & Gertz, 2001; SQL:1999-2).
When an integrity restriction is violated, the usual response of the system is the rollback of the data manipulation intended by the user. In the case of rirs, some other alternative actions are possible. These actions, named referential actions or referential rules, specify the behavior of the left and right relations under the deletion or the updating of a referenced row (a row in the right table), or the insertion of a row in the referencing (left) table. Possible actions are: cascade, restrict, no action, set null, set default (Markowitz, 1994; SQL:1999_2, 1999; Türker & Gertz, 2001). With the cascade option, the referencing rows will be deleted (updated) together with the referenced row. With the set null (set default) option, all references to the deleted (updated) row will be set to null (default) values. The deletion (update) of a referenced row is disallowed by restrict and no action rules, whenever at least a row in the left table is pointing to it. The unique referential rule for insertions is restrict: inserting a row into the referencing table is possible only if the referenced tuple already exists in the right term.
Key Terms in this Chapter
Equality Constraint: Shorthand for two inverse inclusion dependencies, R[X]?S[Z] and S[Z]?R[X]. It can be specified as R[X]=S[Z] (the populations of the attributes R.X and S.Z must always be equal).
Axiom System for Inclusion Dependencies: Set of inference rules that axiomatize id s. Let ID be a set of id s over a database schema R = {R 1 , R 2 , …, R k }, FD a set of functional dependencies over R , and sch(R i ) the set of attributes of R i - (id-1) Reflexivity -- If X?sch(R i ), then R i [X]?R i [X]? ID .(id-2) Projection and Permutation: if R 1 [X]?R 2 [Y]? ID , with X=< A 1 , A 2 , ..., A m >?sch(R 1 ), Y = < B 1 , B 2 , ..., B m >?sch(R 2 ), and i 1 , i 2 , ..., i k is a sequence of distinct natural numbers over {1, ..., m}, then R 1 [A i 1 , A i 2 , ..., A i k ]?R 2 [B i 1 , B i 2 , ..., B i k ]? ID . (id-3) Transitivity: If R 1 [X]?R 2 [Y], R 2 [Y]?R 3 [Z]? ID , then R 1 [X]?R 3 [Z]? ID .
Axiom System for the Interaction Between Inclusion and Functional Dependencies: Comprises the following three inference rules, (if-1) Pullback -- If R 1 [X,Y]?R 2 [U,V], R 2 -- U?V? FD ? ID , with X,Y?sch(R 1 ) and U,V?sch(R 2 ), then R 1 -- X?Y? FD ? ID , where |X|=|U|. (if-2) Collection -- If R 1 [X,Y]?R 2 [U,V], R 1 [X,Z]?R 2 [U,W], R 2 -- U?V? FD ? ID , then R 1 [X,Y,Z]?R 2 [U,V,W]? FD ? ID , where |X|=|U|. (if-3) Attribute Introduction -- If R 1 [X]?R 2 [U], R 2 -- U?W? FD ? ID , then R 1 [X,Z]?R 2 [U,W]? FD ? ID , with Z an attribute newly added to sch(R 1 ).
Inclusion Dependency (id): The existence of attributes in a relation whose values must be a subset of the values of the corresponding (compatible) attributes in another (or the same) relation -- R[X]?S[Z]. R and S are relation names (possibly the same), R[X] and S[Z] are the left and right sides, respectively.
Business Rules: Statements that model the reaction to events that occur in the real world, having tangible side effects on the database content.
Referential Integrity Restriction (rir): Particular case of an inclusion dependency, when Z is the primary key K of S -- R[FK]<<S[K]. X constitutes a foreign key FK for R.