AS · Practice questions · Relational databases and keys

One fact, one place.

Six original Cambridge-style questions on file-based limitations, relational database terminology and keys, with worked mark points.

Original questions All questions on this page are original work, written in the Cambridge AS & A Level style. They are not from past papers. They test the same concepts and skills the syllabus rewards.

Keep these straight

Name it, then explain it.

File-based problems: redundancy, inconsistency, data dependency, poor integrity, security and sharing.
Keys: primary (chosen, unique), candidate (could be primary), secondary (candidate not chosen, indexed), foreign (links to another table's primary key).
Referential integrity: every foreign key value matches an existing primary key value.
Use the scenario: name the actual tables and fields in your answer.

Explain

[4 marks]

A sports centre stores member details in a separate file for each activity: swimming, tennis and gym. Explain two limitations of this file-based approach.

Data redundancy: a member's details are stored in several files, wasting storage. ✓
This can lead to data inconsistency: if a member's phone number changes in one file only, the files disagree. ✓
Data dependency: each program is written for its own file structure, so changing a file structure means changing the programs. ✓
Any two limitations, each identified and explained (2 marks each). ✓

Recall

[4 marks]

Define the terms (a) tuple, (b) attribute, (c) primary key, (d) foreign key.

(a) A row in a table: the data about one instance of an entity. ✓
(b) A column in a table: one property of the entity. ✓
(c) An attribute (or combination) that uniquely identifies each tuple in a table. ✓
(d) An attribute in one table that is the primary key in another table, used to link the tables. ✓

Apply

[3 marks]

A table holds one record per car: CAR(RegNumber, ChassisNumber, Make, Colour, OwnerID). Every car has a unique registration number and a unique chassis number. Identify the candidate keys, suggest a primary key and state what OwnerID is likely to be.

Candidate keys: RegNumber and ChassisNumber (both unique for every car). ✓
Primary key: RegNumber (or ChassisNumber, with a reason); the other is a secondary key. ✓
OwnerID is a foreign key linking to the primary key of an OWNER table. ✓

Explain

[3 marks]

Explain what is meant by referential integrity and give an example of a change that the DBMS would reject for the MEMBER and LOAN tables in this lesson.

Every foreign key value must match an existing primary key value in the linked table. ✓
Example: adding a LOAN record with MemberID M09 when there is no member M09. ✓
Or: deleting member M01 from MEMBER while loans still refer to M01. ✓

Explain

[2 marks]

Explain how indexing a field such as Surname helps the users of a database.

An index holds the Surname values in order with a pointer to each record. ✓
Searches and sorts on Surname use the index instead of reading every record, so they are much faster. ✓

Analysis

[3 marks]

A student says: "A relational database avoids redundancy, so no data value is ever stored twice." Explain why this is not quite correct.

Foreign keys repeat values on purpose: MemberID M01 appears in MEMBER and also in LOAN. ✓
This is controlled redundancy, needed to link the tables. ✓
What is removed is the uncontrolled duplication of other data, such as a member's name and form group being stored with every loan. ✓

Mark this once you have attempted all six and checked your working. It records a Practiced badge on the topic and adds a one-time bonus. Revealing the solutions alone does not count.