Singapore H2 Computing Wiki

Paper 1 Databases Drills

3 min read

Paper 1 Databases Drills

These are original Paper 1-style practice questions. They use concrete schemas, sample data, and scenarios so the answers can be checked exactly.

Detailed answers are in Paper 1 Databases Answers.

Revise the topic hub first:

Questions

Question 1: Primary Key

The table below stores students.

student_idnameclass_code
S01Asha24C1
S02Ben24C1
S03Chen24C2

Define primary key and state the primary key in this table. [2]

Question 2: Foreign Key

Two tables are shown below.

Class(class_code PK, tutor)
Student(student_id PK, name, class_code FK)

Explain how Student.class_code links the two tables. [2]

Question 3: ER Diagram

A music school stores teachers and lessons.

  • Each teacher has teacher_id and teacher_name.
  • Each lesson has lesson_id, lesson_date, and instrument.
  • One teacher can teach many lessons.
  • Each lesson is taught by exactly one teacher.

Draw or describe an ER diagram for this scenario, including primary keys, foreign key, relationship, and cardinality. [5]

Question 4: Normalisation

The unnormalised table below stores repeated phone numbers.

customer_idcustomer_namephone1phone2
C01Asha9123456792345678
C02Ben93456789

Explain one problem caused by the repeated phone fields and suggest a better table design with key roles. [3]

Question 5: 3NF

Consider the table:

Booking(booking_id, room_id, room_name, booking_date)

Assume booking_id is the primary key and room_id determines room_name.

  1. Identify the transitive dependency. [1]
  2. Explain why this is not in 3NF. [1]
  3. Suggest a better 3NF table design. [2]

Question 6: SQL SELECT

Use this table:

Student(student_id, name, mark)

Write an SQL query to display name and mark for students with marks at least 50. [3]

Question 7: SQL JOIN

Use these tables:

Student(student_id, name, class_code)
Class(class_code, tutor)

Write an SQL query to display each student’s name and their class tutor. [4]

Question 8: Aggregate

Use this table:

Result(student_id, subject, mark)

Write an SQL query to display each subject and the number of results recorded for that subject. [4]

Question 9: SQL vs NoSQL

A school stores student records with fixed fields: student_id, name, class_code, and date_of_birth.

Another system stores event feedback where different events may have different optional fields, nested rating sections, and free-text comments.

Compare relational storage and document-style NoSQL storage for these two situations. [4]

Question 10: Backup and Archive

A library keeps a database of current loans. It also keeps records of loans from more than five years ago for audit purposes.

Explain the difference between a backup and an archive in this scenario. [2]

Question 11: Privacy and Integrity

A school stores student contact details and exam marks in a database.

Scenario A: A list of student phone numbers is accidentally sent to a group of people who should not see it.

Scenario B: A student’s mark is accidentally changed from 78 to 87.

For each scenario, state whether it is mainly a privacy problem or an integrity problem, and explain why. [4]

Review Checklist

After attempting these questions, check whether you can:

  • identify primary and foreign keys from a schema;
  • describe ER relationships and cardinality;
  • spot repeated groups and transitive dependencies;
  • write SQL with correct table and field names;
  • distinguish relational and document-style storage;
  • distinguish backup from archive;
  • distinguish privacy from integrity;
  • match a data-management problem to the correct concept.

Graph View

Backlinks