Paper 1 Databases Answers

These answers correspond to Paper 1 Databases Drills.

Answer 1: Primary Key

A primary key is a field, or minimal set of fields, that uniquely identifies each record in a table.

In the Student table, the primary key is:

student_id

Mark points:

• 1 mark for defining primary key as a unique record identifier;
• 1 mark for identifying student_id.

Common weak answer:

• choosing name; different students may have the same name.

Answer 2: Foreign Key

Class.class_code is the primary key of the Class table.

Student.class_code is a foreign key because it refers to Class.class_code.

This links each student to the class record that stores the tutor for that class.

Mark points:

• identifies Student.class_code as a foreign key that refers to the primary key Class.class_code;
• explains that the foreign key links each student to the matching class record.

Common weak answer:

• saying the tables are linked because they both have “class” in the name, without identifying the matching key field.

Answer 3: ER Diagram

One valid text description:

Teacher(teacher_id PK, teacher_name)
Lesson(lesson_id PK, lesson_date, instrument, teacher_id FK)
 
Teacher 1 ---- teaches ---- many Lesson

The foreign key is Lesson.teacher_id, which references Teacher.teacher_id.

Mark points:

• identifies Teacher entity with teacher_id primary key;
• identifies Lesson entity with lesson_id primary key;
• includes teacher_id as a foreign key in Lesson;
• shows one-to-many cardinality from teacher to lesson;
• states that each lesson has exactly one teacher.

Common weak answer:

• putting lesson_id inside the teacher table as repeated fields such as lesson1, lesson2.

Answer 4: Normalisation

Problem:

• Repeated fields such as phone1 and phone2 make the table inflexible. A customer with three phone numbers would need another field, and queries over phone numbers become awkward.

Better design:

Customer(customer_id PK, customer_name)
CustomerPhone(customer_id FK, phone_number, PRIMARY KEY(customer_id, phone_number))

Each phone number becomes a separate record in CustomerPhone, so the design can handle zero, one, two, or more phone numbers without adding phone3, phone4, and so on. The composite primary key prevents the same phone number from being repeated for the same customer.

Mark points:

• identifies repeated phone fields as inflexible or not atomic as a repeating group;
• explains that a separate table stores each phone number as one row;
• gives clear key roles, especially customer_id as a foreign key and (customer_id, phone_number) as a composite primary key.

Common weak answer:

• adding phone3, which repeats the same design problem.

Answer 5: 3NF

1. Transitive dependency:

booking_id -> room_id -> room_name

or:

room_id -> room_name

2. This is not in 3NF because a non-key attribute, room_name, depends on another non-key attribute, room_id.

3. Better 3NF design:

Booking(booking_id PK, room_id FK, booking_date)
Room(room_id PK, room_name)

room_name should be stored in Room because it depends on room_id, not directly on booking_id.

Mark points:

• identifies room_id -> room_name;
• explains the 3NF violation using key/non-key dependency language;
• gives separated Booking and Room tables;
• keeps room_id as a foreign key in Booking.

Common weak answer:

• saying the table is not in 3NF only because it has four fields. The issue is dependency, not number of fields.

Answer 6: SQL SELECT

SELECT name, mark
FROM Student
WHERE mark >= 50;

Mark points:

• selects name and mark;
• uses table Student;
• includes condition mark >= 50.

Common weak answer:

• writing mark > 50, which wrongly excludes a mark of exactly 50.

Answer 7: SQL JOIN

SELECT Student.name, Class.tutor
FROM Student
INNER JOIN Class
ON Student.class_code = Class.class_code;

An alias version is also valid:

SELECT s.name, c.tutor
FROM Student AS s
INNER JOIN Class AS c
ON s.class_code = c.class_code;

Mark points:

• selects student name;
• selects class tutor;
• joins Student and Class;
• uses matching class_code fields as the join condition.

Common weak answer:

• omitting the ON condition, causing an invalid or unintended join.

Answer 8: Aggregate

SELECT subject, COUNT(*) AS number_of_results
FROM Result
GROUP BY subject;

Mark points:

• selects subject;
• uses COUNT(*);
• uses table Result;
• groups by subject.

Common weak answer:

• using COUNT(*) without GROUP BY subject, which gives one total rather than one count per subject.

Answer 9: SQL vs NoSQL

Relational storage is suitable for the fixed student records because each record has the same fields, can be stored in a table, and can use keys such as student_id.

Document-style NoSQL storage can suit event feedback because different events may store different optional fields and nested sections inside each document.

Paired comparison:

• Relational storage uses fixed tables with defined fields, whereas document-style NoSQL storage can use flexible and nested document structures.
• Relational storage is strong when keys and relationships are regular, whereas document-style NoSQL storage can be convenient for nested or variable data.

Mark points:

• links relational storage to the fixed student fields;
• links NoSQL documents to variable/nested feedback;
• gives a paired comparison about schema/table versus flexible documents;
• gives a paired comparison about keys/relationships versus nested data.

Common weak answer:

• saying NoSQL means “no structure”. Document data still has structure, but it is usually more flexible.

Answer 10: Backup and Archive

A backup is a copy of the current loans database used to restore service after data loss, corruption, or system failure.

An archive is long-term storage of old loan records, such as loans more than five years old, kept for audit or historical purposes even though they are not part of daily operations.

Mark points:

• backup linked to recovery after loss or damage;
• archive linked to long-term retention of old/inactive records.

Common weak answer:

• saying backup and archive are the same because both are copies. Their purposes are different.

Answer 11: Privacy and Integrity

Scenario A is mainly a privacy problem because student phone numbers are disclosed to unauthorised people.

Scenario B is mainly an integrity problem because the stored mark has been incorrectly altered, so the data is no longer accurate or trustworthy.

Mark points:

• identifies Scenario A as privacy;
• explains unauthorised disclosure or access;
• identifies Scenario B as integrity;
• explains incorrect alteration or loss of accuracy.

Common weak answer:

• saying both are just “security problems” without distinguishing access/disclosure from correctness.