Lesson 4.10.3.1

4.10.3.1 Database design and normalisation Quiz: AQA Computer Science, Unit 10

20 questions

Written by Revision Ninja · Last reviewed 11 October 2026

In partnership with Revision Ninja

Lesson 4.10.3.1, Database design and normalisation: 20 multiple choice questions for the AQA Computer Science (7517), Unit 10: Fundamentals of databases, written with Revision Ninja.

Host it live on the board and students join with a game code on their own devices, or revise alone with Free Play. The answers are revealed in the game.

Host this setFree Play

The 20 questions

  1. What is the main purpose of normalising a database?

    • To make every attribute a primary key so that searches can be made faster.
    • To store all data in a single table so that joins are never needed in queries.
    • To increase the number of tables so that each one stores only one single value.
    • To reduce data redundancy and avoid update, insertion and deletion anomalies.

  2. Which condition must a relation satisfy to be in first normal form?

    • Every attribute holds atomic values, and there are no repeating groups within a row.
    • Every attribute is stored as a formatted string with a fixed length of characters.
    • Every attribute is a primary key, and no attribute depends on any other attribute, so each row can be found from any single column.
    • Every table has a foreign key that refers to a table in another database system.

  3. Which conditions must hold for a relation to be in third normal form?

    • It must contain three tables that are joined together by a foreign key relationship.
    • It must have exactly three attributes, and each of those attributes must be a primary key.
    • It must already be in second normal form, and no non-key attribute may depend on another non-key attribute.
    • It must be in first normal form, and every attribute must be stored as a number.

  4. What is a transitive dependency?

    • A key attribute that depends on a non-key attribute within the same relation.
    • A relation in which every attribute depends on the entire composite primary key.
    • A dependency between two attributes stored in two separate databases on one network, which is kept in sync by the network server.
    • A non-key attribute depends on another non-key attribute, which in turn depends on the primary key.

  5. What is a partial dependency?

    • A non-key attribute that depends on none of the attributes in the relation at all.
    • A dependency between a primary key and a foreign key in two linked tables.
    • A non-key attribute depends on only part of a composite primary key.
    • A relation in which some rows are missing a value for the primary key field.

  6. Why are databases normalised?

    • To allow every table to store personal data without any restrictions on its use.
    • To ensure data is stored consistently with minimal duplication, so that each update happens in one place.
    • To guarantee that the database never needs backups or restoring after a failure.
    • To make the database run faster on all hardware by removing its indexes entirely.

  7. Which anomaly occurs when deleting a row removes data that is still needed elsewhere?

    • An update anomaly, where a changed value appears differently in several rows.
    • A deletion anomaly.
    • An insertion anomaly, where a row cannot be added until unrelated data exists.
    • A foreign key anomaly, where a foreign key refers to a value that has been changed.

  8. A table Order (OrderID, CustomerID, CustomerName) holds CustomerName, which depends on CustomerID rather than OrderID. Which problem does this create?

    • A partial dependency only, so the relation is already in third normal form.
    • Repeating groups, so the relation fails first normal form and must be split.
    • A transitive dependency, so the relation is not in third normal form.
    • No problem, because CustomerName depends on the primary key of the table.

  9. A row in Order has a column Items containing the values 'pen, ruler, book'. Which normal form is violated?

    • First normal form, because the attribute does not hold atomic values.
    • None, since text values are always allowed in first normal form.
    • Third normal form, because a transitive dependency exists between two attributes.
    • Second normal form only, because the items depend on the OrderID of the row.

  10. A composite key is (StudentID, CourseID), and CourseName depends only on CourseID. Which problem exists?

    • No problem, because the composite key already includes CourseID as one of its attributes.
    • A transitive dependency, so the relation fails third normal form only and not second normal form.
    • A repeating group, so the relation fails first normal form and no other normal form.
    • A partial dependency, so the relation is not in second normal form.

  11. Order (OrderID, CustomerID, CustomerName) has CustomerName depending on CustomerID. Which change removes the problem?

    • Move CustomerName into a Customer table keyed on CustomerID, and keep CustomerID as a foreign key in Order.
    • Copy CustomerName into every order row so that the name is always available for querying.
    • Make CustomerName a second primary key of the Order table, alongside OrderID.
    • Remove CustomerID so that Order depends only on OrderID and the customer name.

  12. A table Staff (StaffID, DeptID, DeptName) is not in third normal form. Which change achieves third normal form?

    • Add DeptName as a second foreign key in the Staff table alongside DeptID.
    • Create Department (DeptID, DeptName) and keep DeptID as a foreign key in Staff.
    • Store DeptName as a repeating group inside each Staff row to keep it together, so that every staff member carries their department name.
    • Remove StaffID so that DeptID becomes the only attribute left in the table.

  13. A new student cannot be recorded in a table until they are enrolled in a course. Which anomaly does this describe?

    • A normalisation anomaly caused by a missing index on the student table.
    • An insertion anomaly.
    • A deletion anomaly, where removing a row loses information that should be kept.
    • An update anomaly, where changing one value requires many rows to be altered.

  14. A customer's address is stored in many rows, and one row is changed. What is the risk?

    • An update anomaly, where some rows show the old address and others show the new one.
    • A foreign key failure, where the customer is unable to place any orders afterwards.
    • No risk, because addresses are always stored only once in a well-designed database.
    • A deletion anomaly, where all rows for the customer disappear when one row is removed.

  15. Which property must a relation in third normal form have?

    • Every attribute is a foreign key that refers to a different relation in the database.
    • Every attribute holds only numeric values, and no text values are stored at all.
    • Every attribute depends on at least one other attribute in the same relation.
    • Every non-key attribute depends on the key, the whole key and nothing but the key.

  16. Which of these is not a requirement of third normal form?

    • Every non-key attribute must depend on the primary key of the relation.
    • The relation must already satisfy first and second normal form before 3NF applies.
    • Every attribute must have a numeric data type.
    • There must be no transitive dependencies between non-key attributes in the relation.

  17. Evaluate: why might a designer choose not to normalise a database fully?

    • Normalisation is not possible in a relational database and is never recommended by designers.
    • Normalisation always makes queries slower because it always creates many extra joins.
    • Full normalisation can require many joins, which may slow some queries in certain systems.
    • Normalised tables cannot hold any foreign key values at all, so they cannot be linked.

  18. Which decomposition is an example of normalising Sale (SaleID, ProductID, ProductName, Qty)?

    • SaleID, ProductID and Qty combined into a single text field inside one column.
    • Product (ProductID) and Sale (SaleID, ProductID, ProductName, Qty) with copies of the same names.
    • Product (ProductID, ProductName) and Sale (SaleID, ProductID, Qty).
    • Sale (SaleID, ProductName) and Product (ProductID, Qty) as two separate tables.

  19. Why does a transitive dependency cause problems in a database?

    • The table can then hold more than one primary key, which SQL does not allow to exist.
    • The primary key is then replaced by a foreign key, so that queries are unable to run.
    • Data about one entity is repeated in many rows, so changing it in one place can leave rows inconsistent.
    • The attributes are then stored as binary values, which takes more disk space than text.

  20. A table is in second normal form but contains a non-key attribute that depends on another non-key attribute. What should be done?

    • Add the non-key attribute to the composite primary key so that it is no longer a dependency.
    • Store the attribute as a repeating group within the same table to keep the data together.
    • Move the dependent attributes into a new table keyed by the attribute that determines them.
    • Delete the non-key attribute, because it cannot be stored in any table of a database.

All AQA Computer Science quizzes