SQL and Data Handling

Tick each line you got. Your score builds from the marks on the scheme.

• correct SELECT and FROM clauses, e.g. SELECT name, attendancePercent FROM pupils1 mark
• correct WHERE clause combining both conditions with AND, e.g. WHERE tutorGroup = '9C' AND attendancePercent < 901 mark
• final answer: correct ORDER BY clause and full statement ending in a semicolon, e.g. SELECT name, attendancePercent FROM pupils WHERE tutorGroup = '9C' AND attendancePercent < 90 ORDER BY attendancePercent ASC;1 mark

Nothing ticked yet - 3 available

Tick each line you got. Your score builds from the marks on the scheme.

• (a) valid problem identified, e.g. the member's name, phone number and membership type are repeated (duplicated) on every row for that member's bookings1 mark
• (a) developed, e.g. this wastes storage space and risks data inconsistency if the phone number is updated on some rows but not others1 mark
• (b) explains that each member's details would be stored only once, as a single record in the members table, identified by a unique memberId (primary key)1 mark
• (b) final answer: each row in the bookings table would only need to store the memberId as a foreign key linking to the correct member, removing the repeated data and the risk of inconsistency1 mark

Nothing ticked yet - 4 available

Tick each line you got. Your score builds from the marks on the scheme.

• a well-developed, coherent discussion that identifies more than one genuine problem with the flat-file design (e.g. data redundancy, inconsistency risk, wasted storage) and clearly explains how a relational design with a customerId foreign key solves each one, reaching a justified evaluation of whether the change is worthwhile as the school grows.2 marks
• clear points made about the flat-file problems and the relational solution, with some explanation, but with less detail linking cause to effect, or a weaker/less justified evaluation.1 mark
• basic or list-like statements (e.g. 'flat-file repeats data' or 'a database is better') with little explanation of why, and no real evaluation of whether the change is worthwhile.1 mark
• no relevant content, or the response does not address the scenario.1 mark
• every booking row repeats the customer's full name, phone number and membership type, wasting storage as the number of bookings grows; if a customer's phone number changes, every row for that customer must be updated, and missing even one row leaves inconsistent data in the table; a relational design would store each customer once, in a customers table with a primary key customerId, and each booking would store only that customerId as a foreign key; this removes the repeated data and the risk of inconsistency, since a customer's details only ever need updating in one place; the initial effort of designing two linked tables and rewriting queries to join them is a genuine cost, especially while the business is still small; however, as the school grows and takes many more bookings, the storage and consistency problems of the flat-file design get worse, so the relational design is likely to be worth the setup effort in the medium to long term.1 mark

Nothing ticked yet - 6 available