Cambridge O Level Computer Science · Syllabus 2210 · Databases
Record
What is 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.
This definition is part of the Databases chapter in Cambridge O Level Computer Science.
Record in context
A database is an organised collection of data that can be stored, searched and processed. Topic 9 works entirely with a database made of one table: data arranged in rows and columns, where a field is a column holding one category of data and a record is a row holding all the field values for one item. Every field is given a data type (text/alphanumeric, character, Boolean, integer, real or date/time) and, where a rule can be stated, one or more validation checks. One field is chosen as the primary key because its value is different in every record. SQL is then used to ask questions of that table — and the answer to “what does this query output?” is found by working through the clauses in a fixed order, never in the order they are written.
A field is one category or attribute of data. It is shown as a column, it has a field name printed as the column heading, it normally has a defined data type, and it may have validation rules. A record holds all the field values for one item, and it is shown as a row. The number of fields is therefore the number of columns, and the number of records is the number of data rows — the row of field headings names the columns and is not a record.
A primary key is a field whose value uniquely identifies each record in the table. Its purpose is to make every record findable and distinguishable: given one primary-key value, the database can return exactly one record, and no two records can ever be confused with one another. To be suitable, a field must have a different value in every record, must never be left empty, and should stay stable over time. That is the whole test — and it is a test about uniqueness, not about importance.
Every SQL example, activity and answer from here to the end of the chapter is worked against the single table below. It never changes: the same ten records, the same eight fields, the same values, from the first SELECT to the final mastery question. Keep this page in view while you work — every result printed later can be checked against it by hand, and you should check some of them.
Structured Query Language (SQL) is the language used to ask questions of a database. A Topic 9 query is built from at most four clauses, always written in the order SELECT, FROM, WHERE, ORDER BY, and finished with a semicolon. That written order is not the order the query is worked out in. The table is fetched first, the records are filtered next, the chosen fields are taken after that, any total or count is calculated, and the sort happens last. Getting those two orders straight is the single most useful thing in this half of the chapter.
SELECT chooses which fields appear in the output, and the fields appear in the order you write them. FROM names which table the data comes from. Together they are the minimum a query needs. On their own they do not filter anything: a query with no WHERE clause returns every record in the table — just fewer columns of each.
WHERE filters records according to a condition. The database takes every record in turn, tests the condition against that record's field values, and keeps it if the condition is true and discards it if it is not. Records that fail never reach the output at all. WHERE decides which records; SELECT decides which fields of those records are displayed. The two jobs never overlap.
AND keeps a record only when both connected conditions are true of that record. OR keeps a record when at least one is true — which includes the case where both are true. Because AND can only ever remove records that OR would keep, an AND result is always the same size or smaller than the matching OR result, never larger. Test every record against every condition separately, then combine; do not try to hold both conditions in your head at once.
ORDER BY sorts the query result on one named field, either ASCENDING or DESCENDING. It is the last thing that happens: WHERE has already thrown records away and SELECT has already chosen the fields, so ORDER BY is only rearranging what is left. It never adds a record, never removes one, and never changes which fields are displayed. And you can sort on a field whether or not that field appears in the output.
SUM adds up the values held in a numeric field. COUNT counts how many records or values there are. They answer completely different questions, and their outputs are completely different kinds of thing: SUM(Places) over the school bookings gives 82 — a number of people — while COUNT(BookingRef) over the same bookings gives 3 — a number of bookings. Both produce a single value, not a list, and WHERE is applied first if it is present.
Common mistakes with Record
- 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.”
- 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.”
- 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.”
- 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.”
- 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.”
Examiner tips on Record
- 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.
Questions students ask about Record
Do I have to count the heading row when counting records?
No. The heading row holds field names, not field values, so it is not a record. The number of records is the number of data rows. Counting the heading row is the single most common slip in this part of the topic, and it makes every records answer exactly one too many.
Can a primary key be text rather than a number?
Yes. Nothing in the definition of a primary key mentions numbers. B1041, A12 and T104 are all perfectly good primary keys and are all stored as text/alphanumeric. The only requirement is that the value is different in every record.
If a query has no ORDER BY, what order do the records come out in?
SQL makes no guarantee. In an examination, write the qualifying records in the order they appear in the printed table — that is what is expected and it will be marked correct. What you should not do is claim that a database must return them in that order, or assume that an unsorted query gives you a sorted result.
How much should I write for a “show the output” question?
Exactly the result table and nothing else: the selected fields as headings, in the SELECT order, and the qualifying records in the required sort order. No extra columns, no BookingRef unless it was selected, no explanation of why each record qualified. For a SUM or COUNT query, write the single value.