Cambridge IGCSE · 0478

Computer Science

Databases — Paper 2

Name: ____________________Date: October 4, 2026

1.
Explain one advantage of a relational database over a single flat-file database for storing a school's student and course records.
[2 marks] · no calculator
Marking points
- Explains that a relational database stores data in separate, linked tables instead of one large repeated table.
- Explains that this avoids duplicating the same student details for every course they take, reducing storage space and the risk of inconsistent data.
Examiner tip: A flat file repeats data whenever a relationship is one-to-many; splitting into linked tables removes that repetition.
2.
In a database table storing student records, which field would be most suitable as a primary key? A Student name B Date of birth C Student ID number D Class name
[1 mark] · no calculator
Marking points
- Selects C: a student ID number is unique to each student, unlike the other fields which can repeat.
Examiner tip: A primary key must be unique for every record; names, birth dates and class names can all be shared by more than one student.
3.
A 'Students' table and a 'Courses' table are linked using a StudentID field present in both. In the Courses table, identify what this field is called, and explain its purpose.
[2 marks] · no calculator
Marking points
- Identifies it as a foreign key.
- Explains that it references the primary key of the Students table, linking each course record to the correct student.
Examiner tip: A foreign key in one table points to the primary key in another, which is how relational databases model relationships between tables.
4.
Write an SQL statement to select the Name and Age fields from a table called Students, for records where Age is greater than 16.
[3 marks] · no calculator
Marking points
- Uses SELECT followed by the correct field names, Name and Age.
- Uses FROM Students to specify the correct table.
- Uses WHERE Age > 16 to apply the correct condition.
Examiner tip: The clause order in SQL is always SELECT ... FROM ... WHERE ...; mixing up this order causes a syntax error.
5.
Write an SQL statement to select all fields from a table called Books, ordered by PublishedYear in descending order.
[3 marks] · no calculator
Marking points
- Uses SELECT * to select all fields.
- Uses FROM Books to specify the correct table.
- Uses ORDER BY PublishedYear DESC to sort by the correct field in descending order.
Examiner tip: DESC sorts from highest to lowest; leaving it out, or using ASC, sorts from lowest to highest instead.
6.
Explain one problem that can occur if a single flat-file table stores both a student's personal details and a separate repeated row for every course they take.
[2 marks] · no calculator
Marking points
- Explains that the student's personal details (such as name and address) are repeated in every row for that student.
- Explains that this wastes storage space and risks inconsistency if the details are updated in some rows but not others.
Examiner tip: This repetition and its risks are exactly what splitting data into separate, linked tables (normalisation) is designed to remove.
7.
Which data type would be most suitable for storing a student's exact date of birth in a database? A Text B Boolean C Date D Integer
[1 mark] · no calculator
Marking points
- Selects C: a Date data type correctly validates and stores day, month and year values.
Examiner tip: A dedicated Date type allows sorting and date calculations (such as finding someone's age) that a plain Text field cannot reliably support.
8.
Explain why a database field storing a student's 'Year Group' might be restricted to a predefined list of values rather than allowing free text entry.
[2 marks] · no calculator
Marking points
- Explains that restricting entry to a predefined list avoids inconsistent values, such as typos or different spellings of the same year group.
- Explains that consistent values make the field easier and more reliable to search, filter and analyse.
Examiner tip: A predefined list is a form of validation that prevents invalid or inconsistent data from being entered in the first place.
9.
Write an SQL statement to insert a new record into a table called Students, with StudentID 101, Name 'Amira', and Age 15.
[3 marks] · no calculator
Marking points
- Uses INSERT INTO Students to specify the correct table.
- Lists the field names in parentheses: (StudentID, Name, Age).
- Uses VALUES (101, 'Amira', 15) with text in quotes and numbers without.
Examiner tip: Text values in SQL must be enclosed in quotes; numeric values must not be, or the statement will cause an error.
10.
Write an SQL statement to update the Age field to 16 for the student with StudentID 101 in the Students table.
[3 marks] · no calculator
Marking points
- Uses UPDATE Students to specify the correct table.
- Uses SET Age = 16 to specify the new value.
- Uses WHERE StudentID = 101 to target only the correct record.
Examiner tip: Always include a WHERE clause with UPDATE — without one, every record in the table would be updated, not just the one intended.
11.
Write an SQL statement to delete the record for the student with StudentID 101 from the Students table.
[2 marks] · no calculator
Marking points
- Uses DELETE FROM Students to specify the correct table.
- Uses WHERE StudentID = 101 to target only the correct record.
Examiner tip: As with UPDATE, forgetting the WHERE clause on a DELETE statement would delete every record in the table.
12.
Write an SQL statement to select the Name field from a table called Students, for records where Age is greater than 14 AND the Grade field equals 'A'.
[2 marks] · no calculator
Marking points
- Uses SELECT Name FROM Students to specify the correct field and table.
- Uses WHERE Age > 14 AND Grade = 'A' to combine both conditions correctly.
Examiner tip: AND requires both conditions to be true for a record to be included; OR would include a record if either condition is true.
13.
Write an SQL statement to count how many records are in a table called Students.
[2 marks] · no calculator
Marking points
- Uses the COUNT aggregate function.
- Writes a complete, correct statement: SELECT COUNT(*) FROM Students.
Examiner tip: COUNT(*) counts every row in the result, regardless of which specific field values they contain.
14.
Explain what is meant by referential integrity in a relational database, using the link between a Students table and a Courses table as an example.
[2 marks] · no calculator
Marking points
- Explains that referential integrity ensures a foreign key value in one table must match an existing primary key value in the related table.
- Applies this to the example: a Courses record cannot reference a StudentID that does not actually exist in the Students table.
Examiner tip: Referential integrity prevents 'orphan' records that reference something which does not exist, keeping relationships between tables valid.
15.
Explain what it means for a database field to contain a NULL value.
[1 mark] · no calculator
Marking points
- Explains that a NULL value means the field has no data stored in it at all — it is not the same as zero or an empty text string.
Examiner tip: NULL represents 'unknown' or 'not entered', which is different in meaning from a recorded value of zero or an empty string.
16.
Explain, using students and clubs as an example, what is meant by a many-to-many relationship in a database.
[2 marks] · no calculator
Marking points
- Explains that in a many-to-many relationship, one record in the first table can be linked to many records in the second table, and vice versa.
- Applies this to the example: one student can join many clubs, and one club can have many students, so neither table alone can represent the relationship directly.
Examiner tip: A many-to-many relationship usually needs a third, linking table to record each individual student-club combination.
17.
Explain one function performed by a Database Management System (DBMS).
[2 marks] · no calculator
Marking points
- Identifies a valid function, such as controlling access and permissions, maintaining data integrity, or providing tools to query and update data.
- Explains briefly how this function benefits users of the database.
Examiner tip: A DBMS sits between the user and the raw stored data, managing how it is created, accessed, updated and protected.
18.
Explain the purpose of a data dictionary when designing a database.
[2 marks] · no calculator
Marking points
- Explains that a data dictionary records details about every field in the database, such as its name, data type, and length.
- Explains that this provides a single, consistent reference for anyone designing, building or maintaining the database.
Examiner tip: A data dictionary documents the structure of a database itself, separate from the actual data records stored in it.
19.
Explain how adding an index to a frequently searched field can improve a database's performance.
[2 marks] · no calculator
Marking points
- Explains that an index creates a separate, organised structure that allows the database to find matching records more quickly, without scanning every row.
- States a trade-off, such as an index using extra storage space or slightly slowing down data insertion.
Examiner tip: An index works much like a book's index — it lets you jump straight to relevant information instead of reading every page in order.
20.
Explain, in simple terms, what an SQL JOIN is used for when working with two related tables.
[2 marks] · no calculator
Marking points
- Explains that a JOIN combines rows from two or more related tables into a single result, based on a common field (such as a matching key).
- Gives a plausible example, such as joining a Students table and a Courses table to list each student alongside the course they are enrolled in.
Examiner tip: A JOIN lets you query data that is spread across multiple linked tables as if it were a single combined table.
21.
Explain the purpose of drawing an entity-relationship diagram before building a database.
[2 marks] · no calculator
Marking points
- Explains that an entity-relationship diagram shows the different entities (such as Students, Courses) a database will store and how they relate to each other.
- Explains that planning this out before building the database helps avoid structural mistakes and design flaws later.
Examiner tip: An entity-relationship diagram is a planning tool used before building a database, much like a blueprint is used before constructing a building.
22.
Explain why a field recording whether a student has paid a school trip deposit would be best stored as a Boolean data type.
[2 marks] · no calculator
Marking points
- Explains that the field can only have one of two possible states: paid or not paid.
- Explains that a Boolean data type (true/false) matches this exactly, while using text would allow inconsistent entries such as 'Yes', 'yes' or 'paid'.
Examiner tip: A Boolean field is the most appropriate choice whenever data genuinely has only two possible states, since it prevents inconsistent text entries.
23.
Explain why a school should regularly back up its student records database.
[2 marks] · no calculator
Marking points
- Explains that a backup is a separate copy of the data, stored safely in case the original is lost, corrupted, or damaged.
- Explains that without a recent backup, important student records could be permanently lost, which could be very difficult or impossible to recreate.
Examiner tip: A backup should be stored in a different location from the original data, so a single disaster (such as a fire or hardware failure) cannot destroy both.
24.
Write an SQL statement to select all fields from a table called Employees, ordered first by Department and then by Surname, both in ascending order.
[2 marks] · no calculator
Marking points
- Uses SELECT * FROM Employees to select all fields from the correct table.
- Uses ORDER BY Department, Surname to sort by both fields in the correct priority order.
Examiner tip: Listing fields in order after ORDER BY sorts by the first field first, only using the second field to break ties within the same first-field value.