Databases and SQL: Question 2

Worked solution

Part (a): Candidate key

A candidate key is a field, or the smallest possible combination of fields, whose values are unique for every record in a table. Meaning it could have been chosen as the primary key, even though a different field ends up being used instead.

Email fits this description: like CoachID, no two rows of COACH share the same Email value, so Email alone could identify any coach uniquely. It was simply not the field that was actually chosen as the primary key. CoachID was chosen instead, leaving Email as a candidate key.

[2 marks]: [1] for correctly defining a candidate key as a unique field that could have been the primary key, [1] for correctly identifying that Email is unique for every coach and so satisfies this.

Part (b): The relationship between COACH and SESSIONS

The relationship is one-to-many.

CoachID is the primary key of COACH, so every CoachID value appears in exactly one row of COACH. In SESSIONS, however, CoachID is a foreign key, and the same CoachID value is free to appear in many different rows (once for every session that coach runs). This is exactly what a one-to-many relationship looks like at the table level: one row on the “one” side (COACH) can correspond to many rows on the “many” side (SESSIONS). In an entity-relationship diagram, this would be documented as COACH 1 - M SESSIONS.

[2 marks]: [1] for stating the relationship is one-to-many, [1] for correctly explaining this from the way CoachID behaves as primary key in COACH versus foreign key in SESSIONS.

Part (c): Referential integrity

Referential integrity is the rule that a foreign key value must always match a value that actually exists as the primary key of the table it refers to (or be left empty, if the field allows this), this keeps every link between related tables valid and prevents “orphaned” records that point nowhere.

Using CoachID: the database should reject any attempt to insert a new SESSIONS row (or update an existing one) whose CoachID does not already exist as a CoachID value in COACH. (Referential integrity would equally require the database to prevent deleting a COACH row while SESSIONS rows still reference its CoachID, but only one rule was required here.)

[2 marks]: [1] for correctly explaining that a foreign key value must match an existing primary key value in the referenced table, [1] for a correct, specific rule involving CoachID linking the two tables.

Part (d): Secondary key

SportName is an example of a secondary key. A field that is not unique, but that is still useful for searching or sorting records.

It cannot be the primary key of SESSIONS because its values are not unique: more than one session can share the same SportName (for example, two different Swimming sessions run on different dates by different coaches). A primary key must uniquely identify a single record on its own, and SportName cannot do this.

[2 marks]: [1] for naming a secondary key, [1] for correctly explaining that SportName is not unique across sessions and so cannot serve as the primary key.

Final answers

• (a) Candidate key: a unique field that could have been the primary key; Email qualifies since it is unique per coach.
• (b) One-to-many, shown by CoachID being unique in COACH but repeatable as a foreign key in SESSIONS.
• (c) Referential integrity: every CoachID in SESSIONS must match an existing CoachID in COACH; invalid inserts/updates should be rejected.
• (d) Secondary key; SportName is not unique across sessions, so it cannot be the primary key.