Databases

• The one sentence to carry into the exam: a field is a column, a record is a row, a primary key is the column with no repeats — and an SQL query is answered table → filter → fields → total → sort, whatever order the clauses appear in.
• The four sentences that neutralise most of these at once: a field is a column and a record is a data row · the primary key is the column with no repeats · validation checks the rule, never the truth · WHERE chooses records, SELECT chooses fields, ORDER BY only rearranges.
• If you can say R3, R10, R12, R15 and R16 without hesitating, you have the load-bearing sentences of Topic 9. Everything else in this chapter is those five sentences applied to a table you have not seen before.
• Six sentences for the last minute before the exam: a field is a column, a record is a data row · a code with digits is still text · the primary key is the column with no repeats · validation checks the rule, never the truth · AND needs both, OR needs one · SUM adds values, COUNT counts records.

• Define a database, a table, a field and a record, and explain how a field differs from a record.
• Count the number of fields and the number of records in a printed table without counting the heading row.
• Take a written set of data storage requirements and define a suitable single table: field names, data types, validation and a primary key.
• Choose the most suitable basic data type from text/alphanumeric, character, Boolean, integer, real and date/time, and justify the choice.
• Explain why an identifier that contains digits is usually stored as text/alphanumeric and not as an integer.
• Select an appropriate validation check for a field and state honestly what that check does not prove.
• Explain the purpose of a primary key and identify a suitable one for a given table, including recognising when no displayed field is suitable.
• Read an SQL script built from SELECT, FROM, WHERE, ORDER BY ASCENDING, ORDER BY DESCENDING, SUM, COUNT, AND and OR, and say in words what it asks for.
• Complete a partly written SQL script so that it answers a stated question.
• Work out the exact output an SQL statement produces from a displayed table — the right records, the right fields, in the right order.
• Explain the difference between SUM and COUNT, and between AND and OR, using a worked case rather than a definition.

The scenario. The Riverside Observatory runs public stargazing and solar-viewing sessions and takes group bookings for them. It stores one record per booking. The session codes are E for an evening session, L for a late-night session and D for a daytime solar session. The observatory, the groups and every value below are fictional and were invented for this chapter; no real person or organisation is represented.

Database

An organised collection of data that can be stored, searched and processed; it holds its data in one or more tables, and Cambridge 2210 Topic 9 works only with databases containing a single table.

Table

A structure that holds data organised into rows and columns; each column is a field holding one category of data and each data row is a record holding all the field values for one item.

Record

All the field values belonging to one item or entity in a database table, shown as one data row; the row of field headings is not a record.

Field

One category or attribute of data in a database table, shown as a column, identified by a field name, and normally given a data type and any validation rules that apply to it.

Data Type

A statement of what kind of value a field is allowed to hold; the Cambridge 2210 Topic 9 list is text/alphanumeric, character, Boolean, integer, real and date/time.

Structured Query Language

The language used to query data held in a database; at Cambridge O Level 2210 a query is limited to SELECT, FROM, WHERE, ORDER BY ASCENDING or DESCENDING, SUM, COUNT, AND and OR, applied to a single table.

Primary Key

A field whose value uniquely identifies each record in a database table, so that one key value always picks out exactly one record; it must have no duplicate values and must not be empty.

• M1 — “A database always contains just one table.” Why it is wrongReal databases routinely contain many tables that reference one another. The single table is a restriction of this syllabus, not a fact about databases. Correct“A database is an organised collection of data held in one or more tables. Cambridge 2210 Topic 9 works only with a database containing a single table.”
• M2 — “A field is a row.” Why it is wrongIt swaps the two structural terms, and every later answer built on it — counting, data types, primary keys — goes wrong too. Correct“A field is a column: one category of data, with a field name and a data type.”
• M3 — “A record is a column.” Why it is wrongSame swap, the other way round. A column holds one kind of value about many items; a record holds many kinds of value about one item. Correct“A record is a row: all the field values belonging to one item.”
• M4 — “There are 11 records, because I counted 11 rows.” Why it is wrongThe heading row was counted. It holds field names, not field values, and there is no item in the database called “BookingRef”. Correct“The number of records is the number of data rows. The BOOKING table has 10.”
• M5 — “Every field must have unique values.” Why it is wrongOnly the primary key must be unique. Repetition in other fields is normal and useful — it is what makes filtering worth doing. Three bookings share GroupType of School, and that is the point. Correct“Only the primary key must have a different value in every record.”
• M6 — “AmountPaid is the primary key because it is the most important field.” Why it is wrongImportance is not the test. The test is uniqueness — and two bookings can easily pay the same amount. In BOOKING, 30.00 and 120.00 each appear twice. Correct“A primary key is the field whose value is different in every record, whether or not it is the most interesting field.”
• M7 — “GroupName would make a good primary key.” Why it is wrongNames repeat. Ashwood High School booked twice, so the value “Ashwood High School” identifies two records rather than one. Names also change, which breaks a key. Correct“Name fields are poor keys because two records can share a name and names can change. Use an issued identifier.”
• M8 — “A primary key has to be a number.” Why it is wrongNothing in the definition mentions numbers. B1041, A12 and PROD-0091 are all perfectly good keys, stored as text/alphanumeric. Correct“A primary key can be of any data type, as long as its value is unique in every record.”
• M9 — “BookingRef contains digits, so it should be an integer.” Why it is wrongDigits are characters. What decides the type is whether arithmetic would mean anything and whether leading zeros must survive. B1041 also contains a letter, and totalling booking references is meaningless. Correct“A code or identifier is text/alphanumeric, however many digits it contains.”
• M10 — “Real is for very large numbers.” Why it is wrongSize has nothing to do with it. Real means a fractional part is permitted. 0.5 is real and 1000000 is an integer. Correct“Real stores a number that may have a fractional part. Integer stores a whole number.”
• M11 — “168.00 ends in zeros, so the field is an integer.” Why it is wrongThe type describes what the field is allowed to hold, not what today's data happens to look like. A pounds-and-pence field must permit 168.50. Correct“Money in pounds and pence is real, even when every stored value currently ends in .00.”
• M12 — “Validation makes sure the data is correct.” Why it is wrongNo validation check can know the truth. A range check of 1 to 40 accepts 4 places for a group of fourteen. Validation tests the form of an entry, not its truth. Correct“Validation checks that an entry satisfies a stated rule. It cannot check that the entry is factually correct.”
• M13 — “A primary key is a type of validation check.” Why it is wrongThey answer different questions. Validation asks “is this entry reasonable?” A primary key answers “which record is this?” The named checks are range, length, type, presence, format and check digit. Correct“A primary key is a field that uniquely identifies each record; it is not one of the validation checks.”
• M14 — “Double entry is a validation check.” Why it is wrongDouble entry checks that data was copied accurately, which is verification. Offering it as a validation check answers the wrong question. Correct“Validation is an automatic rule check. Verification checks that data was transferred accurately.”
• M15 — “SELECT picks the records I want.” Why it is wrongThe English meaning of “select” misleads. In SQL, SELECT picks columns. SELECT GroupName FROM BOOKING; returns all ten records. Correct“SELECT chooses which fields appear, and in what order. WHERE chooses which records.”
• M16 — “WHERE GroupName shows me the group names.” Why it is wrongThat is not a condition and it does not display anything. WHERE always compares something with something. Correct“WHERE takes a condition and filters records. To display a field, list it after SELECT.”
• M17 — “ORDER BY Places DESCENDING shows only the biggest bookings.” Why it is wrongSorting is not filtering. Every qualifying record is still there; the biggest is simply printed first. Correct“ORDER BY rearranges the result. It never adds or removes a record.”
• M18 — “ASCENDING means newest first.” Why it is wrongAscending always runs from smallest to largest, and an earlier date is the smaller value. 03/03/2026 comes before 25/03/2026. Correct“For dates, ASCENDING is earliest first and DESCENDING is latest first.”
• M19 — “Without ORDER BY, the records come out in table order.” Why it is wrongSQL guarantees no order unless you ask for one. Writing them in printed-table order in an exam is sensible; claiming the database must do so is not true. Correct“If a particular order is required, an ORDER BY clause has to say so.”
• M20 — “AND means either condition can be true.” Why it is wrongThat is OR. AND requires both. Confusing them produces results that are far too large. Correct“AND keeps a record only if every connected condition is true of it.”
• M21 — “OR means exactly one of the two must be true.” Why it is wrongPlain OR is inclusive: it also keeps records where both conditions are true, and each such record appears once, not twice. The exclusive version is XOR, which belongs to Topic 10. Correct“OR keeps a record if at least one connected condition is true of it.”
• M22 — “SUM tells you how many records there are.” Why it is wrongSUM adds the values in a numeric field. Over the whole BOOKING table SUM(Places) is 165, while the number of records is 10. Correct“SUM adds values. COUNT counts records.”
• M23 — “COUNT(Places) gives the total number of places.” Why it is wrongCOUNT never looks at the size of a value, only at whether a value is there. Over the school bookings it gives 3, not 82. Correct“To total the places, use SUM(Places). To count the bookings, use COUNT on the primary key.”
• M24 — “Topic 9 SQL includes adding, changing and deleting records.” Why it is wrongThe syllabus limits SQL here to nine keywords, all of which read data. INSERT, UPDATE, DELETE and CREATE TABLE are not in the list. Correct“Topic 9 SQL queries a table. It does not modify it.”
• M25 — “I should write ASC and DESC, and I need joins for a proper answer.” Why it is wrongThe syllabus lists the sorting keywords written out in full, and there is only ever one table, so a join has nothing to join. Normalisation, foreign keys and entity-relationship diagrams are outside the topic as well. Correct“Write ASCENDING and DESCENDING, and answer every Topic 9 question from the single table given.”

• No calculator matters here. Every SUM in a Topic 9 question is arithmetic you must do in your head or on the paper. That is exactly why the numbers in real database questions are kept small and round — and why an answer of 361.00 where 360.00 was correct usually means a copying slip, not a misunderstanding. Write the values you are adding in a column first, then add them.
• How to count without slipping. Put your pen at the top-left of the printed table. Move it along the heading row, tapping each heading, and write the total — that is the number of fields. Then move it down the left-hand column, tapping each data row, and write that total — that is the number of records. Two counts, two numbers, in two directions. The commonest slip is counting the heading row as a record, which makes every records answer exactly one too many.
• Match the check to the field, and stop. A question asking for “two validation checks on the BookingRef field” wants two checks that make sense on that field — presence, length and format all do. A range check does not, because a reference has no numeric range. Listing every check you can remember is a common way to lose marks: the wrong ones are marked as wrong, they do not sit harmlessly beside the right ones.
• The two repeats are deliberate. Ashwood High School appears twice and 07/03/2026 appears twice. Those repeats are what disqualify GroupName and SessionDate as primary keys, and they are also what makes sorting on SessionDate produce a genuine tie. Real exam tables contain repeats like these for exactly the same reason.
• Command words, one more time. State and give want short factual answers — do not justify unless asked. Identify wants one thing chosen from what is in front of you. Explain and describe the purpose of want a reason as well as a fact. Complete means fill the printed gaps in the printed style. Show the output means write the result table, nothing more. Matching the answer to the command word is worth more marks in Topic 9 than any extra database knowledge.