Inclusion Compatibilities | Database Design | Teradata Vantage - 17.10 - Inclusion Compatibilities - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)

Inclusion compatibilities are a generalization of referential constraints. As such, they provide the foundation upon which referential integrity is based. In common with functional compatibilities (see Functional, Transitive, and Multivalued Compatibilities), inclusion compatibilities represent one-to-many relationships (see One-to-Many Relationships); however, inclusion compatibilities typically represent relationships between relations (see Database-Level Constraints), while functional compatibilities always represent relationships between the primary key of a relation variable and its attributes.

Suppose you have the following table definitions:

Using the notation R.A, where R is the name of a relation variable and A is the name of one of its attributes, you can write the following inclusion compatibility:

     supplier_parts.part_num  parts.part_num

parts
 
PK part_name color weight city
part_num
supplier_parts
 
PK quantity
FK FK
supp_num part_num

This inclusion compatibility states that the set of values appearing in the attribute part_num of relation variable supplier_parts must be a subset of the values appearing in the attribute part_num of relation variable parts. Notice that this defines a simple foreign key-primary key relationship, though it is only necessary, in order to write a proper referential integrity relationship, that the right hand side (RHS) indicates any candidate key of the specified relation variable, not necessarily its primary key (see Foreign Key Constraints for more information about this).

The left hand side (LHS) and RHS of a compatibility relationship are not required to be a foreign key and a candidate key, respectively. This is merely required to write a correct inclusion compatibility expression of a referential integrity relationship.

Inference Axioms for Inclusion Compatibilities

Interference axioms for inclusion compatibilities are described in the following table:

Axiom Formal Expression
Reflexive rule A   A
Projection and Permutation rule IF AB →   CD THEN A   C AND B →   D
Transitivity rule IF A →   B AND B →   C THEN A   C