Foreign Keys and Relational Models

See also slides

A relation is a table

Relations are unordered ⇒ relations are sets

tuple and domain constraints

• tuple: expresses conditions on the values of each tuple
• domain constraint: tuple constrain that involves a single attributes

(GPA <= 4.0) AND (GPA >= 0.0)

unique identifier

A superkey is a set of attributes for a relation $r$ if $r$ cannot contain two distinct tuples $t_1$ and $t_2$ such that $t_1[K]=t_2[K]$

A (candidate) key for $r$ if $K$ is a minimal superkey

ex: superkey of RegNum

primary value

handles null value

Presence of nulls in keys

definition

Each relation must have a primary key on which nulls are not allowed.

notation: the attributes of the primary keys are underlined

⇒ references between relations are realised through primary keys

Remark

A set of fields is a key for a relation if:

1. No two distinct tuples can have same values in all key fields
2. This is not true for any subset of the key (minimal)

If #2 is false, then a superkey

If there’s > 1 key for a relation, one of the keys is chosen to be primary key

Example:

requirements:

• For a given student and course, there is a single grade.

CREATE TABLE Enrolled (
  sid INTEGER,
  cid INTEGER,
  grade INTEGER,
  PRIMARY KEY (sid, cid),
  UNIQUE (cid, grade)
);

• Students can take only one course, and received a single grade for that courses; further, no two students in a course receive the grade

CREATE TABLE Enrolled (
  sid INTEGER,
  cid INTEGER,
  grade INTEGER,
  PRIMARY KEY sid,
  KEY (cid, grade)
);

IC are validated when data is updated

interpolation constraints (foreign keys)

Referential integrity constraints are imposed in order to guarantee values refer to existing tuples

Definition

A foreign key requires that the values on a set $X$ of attributes of a relation $R_1$ must appear as values for the primary key of another relation $R_2$

Ex: sid is a foreign key referring to Students

If al foreign key constraints are enforced ⇒ referential integrity is enforced

enforcing referential integrity

See also source