Referential integrity, entity relationship diagrams and database management systems

Overview

Recall questions on referential integrity and what it prevents, one-to-one, one-to-many and many-to-many relationships in entity relationship diagrams, resolving many-to-many relationships with a linking table, and what a database management system does.

What you need to know

Answer each one before you reveal it.

Questions from this subtopic

Each one with the model answer underneath.

1. 1

   What is referential integrity?

   Model answer

   Referential integrity is the rule that every foreign key value must match an existing primary key value in the related table, or be left empty where that is permitted. It ensures the links between tables always point at something real.

2. 2

   A library database has Member and Loan tables, with MemberID as a foreign key in Loan. What does referential integrity prevent, and what happens if someone tries to delete a member who still has loans?

   Model answer

   It prevents a loan being created for a MemberID that does not exist in the Member table, and it prevents a member being deleted while loans still refer to them, which would otherwise leave orphaned loan records pointing at a member who is not there. The database management system will refuse the deletion, or, if the relationship is configured to cascade, will delete the related loan records along with the member, so that no dangling reference is ever left behind.

3. 3

   Describe how a one-to-one relationship is represented in an entity relationship diagram, and give an example.

   Model answer

   Each entity is drawn as a labelled rectangle and the relationship as a line joining them, with the 'one' symbol at each end: in crow's foot notation a single stroke across the line at both ends, or in the simpler arrow notation a plain line with no arrowheads, since an arrowhead marks a 'many' end. An example is a pupil and their locker, where each pupil has exactly one locker and each locker belongs to exactly one pupil. In a database this is often implemented by holding both sets of attributes in one table, or by giving one table a unique foreign key to the other.

4. 4

   Describe how a one-to-many relationship is drawn and how it is implemented in the tables.

   Model answer

   The line is drawn with the 'one' symbol at the single end, a single stroke across the line, and a crow's foot at the many end, which is a three-pronged fork opening out towards the entity; the crow’s foot is the notation to know, since the older arrow forms are drawn inconsistently between sources. An example is one customer to many orders. It is implemented by placing the primary key of the 'one' table into the 'many' table as a foreign key, so CustomerID appears in the Order table.

5. 5

   What does a many-to-many relationship look like in an entity relationship diagram, and why must it be resolved?

   Model answer

   It is drawn as a line with a crow's foot at both ends, or arrowheads at both ends in the simpler notation, for example students and courses where each student takes many courses and each course has many students. It must be resolved because there is no way to store it with a single foreign key: putting a course into the Student table would need a repeating group of courses, and putting a student into the Course table would need a repeating group of students, neither of which is allowed.

6. 6

   How is a many-to-many relationship resolved?

   Model answer

   It is broken down into two one-to-many relationships using a linking table, also called a junction or link table. For students and courses, an Enrolment table is created containing StudentID and CourseID as foreign keys, usually forming a composite primary key together. Student to Enrolment is one-to-many, and Course to Enrolment is one-to-many, and any attribute belonging to the pairing rather than to either entity, such as the enrolment date or the grade, is stored in the linking table.

Other ways to work this

The same content, four other ways.

What is inside Levo

Six ways to work the same material.