Databases Exam Practice

This lesson brings together all the database concepts covered in this course with exam-style questions, model answers, and strategies for tackling each question type. Use this lesson to consolidate your knowledge and develop your exam technique.

---

Question Type 1: Short-Answer Definitions (2–4 marks)

Example Question

Define the following database terms: (a) primary key, (b) foreign key, (c) referential integrity. [6 marks]

Model Answer

(a) Primary key: A field (or combination of fields) that uniquely identifies each record in a table. The primary key value must be unique and cannot be NULL. [2 marks]

(b) Foreign key: A field in one table that references the primary key of another table, creating a link between the two tables. It is used to enforce relationships between entities. [2 marks]

(c) Referential integrity: The principle that every foreign key value must correspond to an existing primary key value in the referenced table, or be NULL if the field allows it. This ensures that relationships between tables remain consistent and that no orphan records exist. [2 marks]

---

Question Type 2: Normalisation (6–12 marks)

Example Question

The following data is stored about student club memberships. Normalise the data to Third Normal Form (3NF). Show each stage of normalisation clearly.

StudentID	StudentName	FormGroup	FormTutor	ClubName	MeetingDay
101	Alice	10A	Mrs Jones	Chess, Coding	Tue, Thu
102	Bob	10B	Mr Smith	Chess	Tue
103	Carol	10A	Mrs Jones	Coding, Drama, Chess	Thu, Fri, Tue

Model Answer

UNF → 1NF: Remove repeating groups. Make all values atomic.

StudentID	StudentName	FormGroup	FormTutor	ClubName	MeetingDay
101	Alice	10A	Mrs Jones	Chess	Tue
101	Alice	10A	Mrs Jones	Coding	Thu
102	Bob	10B	Mr Smith	Chess	Tue
103	Carol	10A	Mrs Jones	Coding	Thu
103	Carol	10A	Mrs Jones	Drama	Fri
103	Carol	10A	Mrs Jones	Chess	Tue

Composite PK: (StudentID, ClubName).

1NF → 2NF: Remove partial dependencies. StudentName, FormGroup, and FormTutor depend only on StudentID (part of the key). MeetingDay depends only on ClubName (part of the key).

Student(StudentID PK, StudentName, FormGroup, FormTutor) Club(ClubName PK, MeetingDay) StudentClub(StudentID FK, ClubName FK) — composite PK

2NF → 3NF: Remove transitive dependencies. FormTutor depends on FormGroup (not directly on StudentID).

Student(StudentID PK, StudentName, FormGroup FK) FormGroup(FormGroup PK, FormTutor) Club(ClubName PK, MeetingDay) StudentClub(StudentID FK, ClubName FK) — composite PK

---

Question Type 3: SQL Queries (4–8 marks)

Example Questions

Using the following tables, write SQL to answer each question:

Student(StudentID, FirstName, Surname, FormGroup) Grade(GradeID, StudentID, Subject, Score)

(a) List the first name and surname of all students in form group 10A, ordered by surname. [3 marks]

SELECT FirstName, Surname
FROM Student
WHERE FormGroup = '10A'
ORDER BY Surname ASC;

(b) Find the average score for each subject. [3 marks]

SELECT Subject, AVG(Score) AS AverageScore
FROM Grade
GROUP BY Subject;

(c) List the names of students who scored above 90 in Maths. [4 marks]

SELECT s.FirstName, s.Surname
FROM Student s
INNER JOIN Grade g ON s.StudentID = g.StudentID
WHERE g.Subject = 'Maths' AND g.Score > 90;

(d) Find form groups where the average score across all subjects is above 80. [4 marks]

SELECT s.FormGroup, AVG(g.Score) AS AvgScore
FROM Student s
INNER JOIN Grade g ON s.StudentID = g.StudentID
GROUP BY s.FormGroup
HAVING AVG(g.Score) > 80;

---

Question Type 4: E-R Diagrams (4–8 marks)

Example Question