Doubt regarding foreign key and referential integrity

Question

I am studying database from Database System Concepts.

The author was explaining foreign keys and referential integrity with the help of following schemas:

$$ instructor(\underline{ID},\space name,\space dept\text_name,\space salary)$$ $$ course(\underline{course\text_id},\space title, \space dept\text_name,\space credits) $$ $$ department(\underline{dept\text_name}, \space building,\space budget)$$ $$ section(\underline{course\text_id,\space sec\text_id,\space semester,\space year},\space building,\space room\text_no,\space time\text_slot\text_id) $$ $$ teaches(\underline{ID,\space course\text_id, \space sec\text_id,\space semester, \space year})$$

The primary keys are underlined.

The relation $section$ is used so that each course could be offered multiple times, across different semesters, or even within a semester.

Foreign key was explained with the help of schemas $instructor$ and $department$. The attribute $dept\text_name$ in in $instructor$ is a foreign key from $instructor$ referencing $department$.

It need not be the case that all $dept\text_name$ values are referenced by $instructor$ relation.

Referential integrity constraint was explained with the help of $section$ and $teaches$ schema.

If a section exists for a course it must be taught by at least one instructor(it could be taught by more than one instructor)

So all the combinations of $(course\text_id,\space sec\text_id,\space semester, \space year)$ that appear in $section$ relation must also appear in $teaches$ relation.

But we cannot declare a foreign key from $section$ to $teaches$ since multiple instructors could teach a single section.

We could define a foreign key from $teaches$ to $section$.

Since we could form a referential integrity constraint from $instructor$ to $department$ by using foreign key but without foreign key we have referential integrity constraint from $section$ to $teaches$.

So how is referential integrity different from foreign key constraints?

Also if you know some free resources that could clear database concepts please share.

Answer 1

In general the “referential integrity constraint” is a constraint that states that a foreign key must always be equal to some primary key of a tuple in the referred relation (so establishing a correspondence between a tuple in the first relation and another (and only one) in the other relation. If the constraint is violated, that is if the value of the foreign key of a tuple is not equal to the value of the primary key of another tuple, this means that the foreign key has lost its integrity, since it “does not refer anything”, like a “dangling reference” in a programming language.

It is a duty of the Database Management System to enforce this constraint, and prevent any violation of it through some policy, that can be specified by the database administrator (“on delete cascade, no action”, etc.)

In the book that you cited, it is in fact said that:

a referential integrity constraint requires that the values appearing in specified attributes of any tuple in the referencing relation also appear in specified attributes of at least one tuple in the referenced relation.

The comment related to the example explains that, even if, according to the rules of the university, each section should be taught by at least one instructor, it is not possible to express such constraint in the database designed, since it is not possible to define the attributes of section as foreign key for the relation teaches (since they do not form a primary key in such relation).

Note that this depends on the fact that the only way to represent a 1-n relationship between two sets of data in the Relational Data Model is to use a foreign key in the relation where there is only one element (at maximum) in the other relation, not vice-versa. So the foreign key is from teaches to section, since a tuple in teaches can be related only to one tuple in section.