Cambridge IGCSE · 0478

Computer Science

Databases — Paper 2

Name: ____________________Date: November 19, 2026
  1. 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

    Course scope: Optional enrichment

    Worked explanation

    1. Put each student's details once in Students(StudentID,Name,Address) and each student-course pairing in Enrolments(StudentID,CourseID). A student taking three courses no longer needs three copies of their address.
    2. Changing one Students address updates the single authoritative fact; a query can link enrolments to it. Keys and a sound schema are necessary: merely selecting relational software does not automatically remove repeated facts.

    Common mistake: One giant relational table can still repeat the same facts and suffer the flat-file problem.

    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: Well-designed linked tables can reduce repeated facts and update inconsistencies. Merely using a relational database does not automatically eliminate duplication; suitable keys and schema design are needed.

  2. 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

    Worked explanation

    1. Select C, Student ID number, provided it is assigned uniquely and never left missing for a record.
    2. Two students can share a name, birth date or class. A unique ID lets a query identify one record even when those descriptive fields match; uniqueness must be enforced, not assumed from the word 'number'.

    Common mistake: Numeric fields such as year group are not automatically unique keys.

    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. 3.

    A Students table has primary key StudentID. An Enrolments table records each student-course pairing and contains StudentID referencing Students.StudentID. Identify the role of StudentID in Enrolments and explain its purpose.

    [2 marks] · no calculator

    Course scope: Optional enrichment

    Worked explanation

    1. StudentID in Enrolments is a foreign key referencing the primary key Students.StudentID.
    2. Example: student 101 can have enrolments (101,'Physics') and (101,'Computing'), both linked to one student row. The foreign key may repeat across enrolments; it need not itself identify each enrolment uniquely.

    Common mistake: Requiring a foreign key to be unique would prevent one student enrolling in several courses.

    Marking points

    • Identifies it as a foreign key.
    • Explains that it references Students.StudentID and links each enrolment to the corresponding student.

    Examiner tip: StudentID is a foreign key in Enrolments. A separate Courses table normally stores one row per course; Enrolments models students taking courses.

  4. 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

    Worked explanation

    1. SQL: SELECT Name, Age FROM Students WHERE Age > 16;
    2. For illustrative rows ('Amira',16),('Ben',17),('Cara',19), only Ben and Cara with their ages are returned. Age 16 is excluded because the predicate is strict; SELECT lists only the requested output fields.

    Common mistake: Age >= 16 would include the boundary value the prompt excludes.

    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. 5.

    Write an SQL statement to select all fields from a table called Books, ordered by PublishedYear in descending order.

    [3 marks] · no calculator

    Worked explanation

    1. SQL: SELECT * FROM Books ORDER BY PublishedYear DESC;
    2. The star returns every field, and DESC places an illustrative 2024 book before 2020 and 1998 books. Rows sharing a year have no guaranteed relative order without another ordering field.

    Common mistake: Sorting by title instead of PublishedYear changes the requested ordering criterion.

    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. 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

    Course scope: Optional enrichment

    Worked explanation

    1. If a student takes Physics and Computing, their address occurs in both course rows. Moving house requires updating both copies.
    2. Updating only one leaves contradictory addresses for the same person, an update anomaly; repetition also costs storage. Keeping student details once and linking course enrolments avoids that repeated fact when designed correctly.

    Common mistake: Sorting the repeated rows does not remove their duplication or inconsistent-update risk.

    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. 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

    Worked explanation

    1. Select C, Date, because the value represents a calendar day, month and year, not merely an arbitrary string.
    2. A date-aware system can reject invalid calendar dates and sort chronologically. For example, 2009-12-31 precedes 2010-01-01; arbitrary text formats such as day/month strings can sort differently if treated as text.

    Common mistake: An integer year alone does not preserve the exact date of birth.

    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. 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

    Worked explanation

    1. Restrict YearGroup to authorised values such as 7,8,9,10,11 rather than accepting spellings like 'Year Ten', '10' and 'Y10' as unrelated free text.
    2. The same year then has one representation, so filtering YearGroup=10 retrieves the intended group consistently. Membership of the list checks validity, not whether the selected valid year is factually correct for this student.

    Common mistake: A dropdown reduces invalid formats but cannot prove the user chose the true year group.

    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. 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

    Course scope: Optional enrichment

    Worked explanation

    1. SQL: INSERT INTO Students (StudentID, Name, Age) VALUES (101, 'Amira', 15);
    2. Explicit columns map 101 to StudentID, the quoted text to Name and 15 to Age. Running SELECT Name, Age FROM Students WHERE StudentID=101; afterward should return Amira,15, assuming no conflicting key and valid schema.

    Common mistake: Leaving the column order implicit makes the insertion depend on the table's physical column definition.

    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: Use single quotes for the text literal and ordinary unquoted numerals for numeric values in this answer. Some databases accept quoted numerals through type conversion, so quoting a numeral is not universally a syntax error.

  10. 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

    Course scope: Optional enrichment

    Worked explanation

    1. SQL: UPDATE Students SET Age = 16 WHERE StudentID = 101;
    2. The predicate selects the key, then SET changes only Age. If 101 is Amira and 102 is Ben, a check query should show Amira's new age 16 with Ben's age unchanged; no matching row means zero rows updated.

    Common mistake: Omitting WHERE changes every student's age to 16.

    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. 11.

    Write an SQL statement to delete the record for the student with StudentID 101 from the Students table.

    [2 marks] · no calculator

    Course scope: Optional enrichment

    Worked explanation

    1. SQL: DELETE FROM Students WHERE StudentID = 101;
    2. This removes the matching row, not the table structure or just the ID field. SELECT COUNT(*) FROM Students WHERE StudentID=101; then returns 0 if deletion is allowed; related foreign-key constraints may forbid it.

    Common mistake: DELETE FROM Students without a predicate removes all rows, not just student 101.

    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. 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

    Worked explanation

    1. SQL: SELECT Name FROM Students WHERE Age > 14 AND Grade = 'A';
    2. Illustrative cases: age15/gradeA passes; age14/gradeA fails; age16/gradeB fails. AND requires both conditions, and quotes mark A as a text value rather than a field name.

    Common mistake: OR would return older non-A students and younger A students, widening the result incorrectly.

    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. 13.

    Write an SQL statement to count how many records are in a table called Students.

    [2 marks] · no calculator

    Worked explanation

    1. SQL: SELECT COUNT(*) FROM Students;
    2. With three student rows it returns one result row containing 3, even if a non-key field is NULL. An empty Students table returns 0; COUNT(Name) could omit rows whose Name is NULL, so COUNT(*) matches total records.

    Common mistake: SELECT * lists records rather than returning their count.

    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. 14.

    Explain referential integrity using a Students table with primary key StudentID and an Enrolments table whose non-NULL StudentID foreign key references it.

    [2 marks] · no calculator

    Course scope: Optional enrichment

    Worked explanation

    1. A non-NULL Enrolments.StudentID must match an existing Students.StudentID. If Students contains only 101, an enrolment for 101 is valid but one for 999 violates the reference.
    2. An enforcing DBMS rejects the invalid insertion or an incompatible parent deletion, preventing orphan enrolments. The prompt specifies non-NULL; generally foreign-key validity and a NOT NULL requirement are separate constraints.

    Common mistake: Referential integrity is not a universal ban on NULL foreign keys unless another constraint imposes it.

    Marking points

    • Explains that each non-NULL foreign key value must match an existing referenced key value in the related table.
    • Applies this to the example: an enrolment cannot refer to a StudentID absent from Students.

    Examiner tip: A nullable foreign key can allow NULL unless another constraint forbids it. Referential integrity concerns valid references, not a universal ban on missing values.

  15. 15.

    Explain what it means for a database field to contain a NULL value.

    [1 mark] · no calculator

    Course scope: Optional enrichment

    Worked explanation

    1. NULL marks a missing, unknown or inapplicable value rather than a recorded zero or empty string. An unknown deposit amount differs from a known amount of 0.
    2. SQL tests it with IS NULL, not = NULL. Example SELECT StudentID FROM Students WHERE Age IS NULL; finds missing ages, while Age=0 asks for a recorded numeric zero.

    Common mistake: Treating NULL as zero hides the difference between unknown and known data.

    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. 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

    Course scope: Optional enrichment

    Worked explanation

    1. Student 101 may join Chess and Coding, while Chess may contain students 101 and 102: many students per club and many clubs per student.
    2. Use Membership(StudentID,ClubID) rows (101,Chess),(101,Coding),(102,Chess), normally with the pair unique. Separate student and club tables keep details once while the linking rows represent each membership.

    Common mistake: A single ClubID field per student cannot represent several clubs without repeated or packed values.

    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. 17.

    Explain one function performed by a Database Management System (DBMS).

    [2 marks] · no calculator

    Course scope: Optional enrichment

    Worked explanation

    1. A DBMS can enforce access permissions: a teacher account may read class records while only authorised office staff can update them.
    2. Checking each operation against those permissions reduces accidental or unauthorised modification at the data layer. An application hiding an edit button alone would not enforce that rule for other access routes.

    Common mistake: A database interface is not itself the same as centrally enforced data permissions.

    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. 18.

    Explain the purpose of a data dictionary when designing a database.

    [2 marks] · no calculator

    Course scope: Optional enrichment

    Worked explanation

    1. A data dictionary documents schema metadata, for example StudentID: integer, primary key, non-NULL; Name: text, maximum 60 characters.
    2. Developers can use that reference to build compatible forms and queries rather than guessing field names or allowed sizes. It describes fields, not a second copy of every student's actual record.

    Common mistake: A list of current student names is data, not a dictionary of field definitions.

    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. 19.

    Explain how an index on a frequently searched database field can speed up suitable lookups, and state one trade-off.

    [2 marks] · no calculator

    Course scope: Optional enrichment

    Worked explanation

    1. An index on StudentID stores a search structure linking keys to records. A suitable WHERE StudentID=101 lookup can locate a record without scanning all student rows.
    2. The index needs extra storage and must be maintained on relevant inserts, deletes and updates. It need not help a query using an unrelated expression or returning most of the table, so 'indexed' does not guarantee faster every time.

    Common mistake: Adding indexes to every field ignores write cost and whether queries can use them.

    Marking points

    • Explains that an index provides a search structure that can locate matching records without scanning every row when the query and index are suitable.
    • 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. 20.

    Explain, in simple terms, what an SQL JOIN is used for when working with two related tables.

    [2 marks] · no calculator

    Course scope: Optional enrichment

    Worked explanation

    1. A JOIN combines matching related rows in a result without permanently merging the underlying tables. Example: SELECT Students.Name, Enrolments.CourseID FROM Students JOIN Enrolments ON Students.StudentID = Enrolments.StudentID;
    2. If student101 is Amira and enrolments are (101,Physics),(101,Computing), the result has Amira paired with each course. A many-to-many course design needs Enrolments; joining Students to Courses without a valid relationship invents associations.

    Common mistake: Leaving out the matching condition can create all pairings rather than the intended related ones.

    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. 21.

    Explain the purpose of drawing an entity-relationship diagram before building a database.

    [2 marks] · no calculator

    Course scope: Optional enrichment

    Worked explanation

    1. An entity-relationship diagram can show Students and Courses linked through Enrolments, with one student and one course each having many enrolment rows.
    2. Drawing those cardinalities first exposes that students can take several courses and courses contain several students. This avoids an inadequate single Course field per student before tables and forms are built; the diagram is a design model, not the actual records.

    Common mistake: A diagram of screen navigation does not specify database entities, keys or relationships.

    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. 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

    Worked explanation

    1. For the specified two-state rule, DepositPaid is true for paid and false for not paid, matching a Boolean field.
    2. Filtering WHERE DepositPaid = TRUE then consistently retrieves paid students instead of managing text variants 'Yes','yes','paid'. If 'unknown' is a required third state, that would need a separate explicit design rather than silently calling it false.

    Common mistake: Two-state storage is suitable only when the business meaning genuinely distinguishes exactly those states.

    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. 23.

    Explain why a school should regularly back up its student records database.

    [2 marks] · no calculator

    Course scope: Optional enrichment

    Worked explanation

    1. A backup is a separately recoverable copy of student records, useful after accidental deletion, corruption or equipment failure when the live database cannot reconstruct itself.
    2. Keep protected copies separately from the original and test restoration. A daily backup can still lose changes since the last backup; regular frequency reduces that potential lost interval, while a sync that copies deletions is not necessarily a recoverable backup.

    Common mistake: A copy on the same failed disk or an untested unusable archive does not provide dependable recovery.

    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. 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

    Worked explanation

    1. SQL: SELECT * FROM Employees ORDER BY Department ASC, Surname ASC;
    2. For rows (Maths,Zed),(English,Yang),(Maths,Ali), the order is English/Yang, Maths/Ali, Maths/Zed. Department is the primary sort; Surname only orders ties within each department.

    Common mistake: ORDER BY Surname, Department reverses the requested priority rather than giving the same result.

    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.