Cambridge O Level Computer Science · Syllabus 2210 · Databases
Field
What is 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.
This definition is part of the Databases chapter in Cambridge O Level Computer Science.
Field 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 design question gives you a paragraph describing what must be stored and asks you to define a table. The paragraph is not decoration — it is a checklist. Each separate item of data that the requirement says must be stored becomes exactly one field. You then give each field a meaningful name and a suitable data type, attach validation wherever the requirement states a rule, and choose a primary key. A field belongs in your design because a stated requirement puts it there, never because it would look realistic.
Every field is given a data type: a statement of what kind of value it is allowed to hold. Topic 9 uses six, and only six — text/alphanumeric, character, Boolean, integer, real and date/time. Choosing between them is not a matter of what the value looks like. A booking reference such as B1041 and a room code such as 0044 both contain digits and are both text, because no arithmetic will ever be done on them and because 0044 would lose its leading zeros if stored as a number.
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.
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 Field
- 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.”
- 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.”
- 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.”
- 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.”
Examiner tips on Field
- 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.
Questions students ask about Field
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.
Why is a code made of digits stored as text and not as a number?
Because a code is a label, not a quantity. No arithmetic is ever performed on it — totalling a column of booking references or telephone numbers is meaningless — and a code such as 0044 stored as a number would become 44, losing its leading zeros permanently. If arithmetic on a field would never mean anything, the field is text.
What if no field in the table is unique?
Say so, and say what should be done: a new identifier field must be created and used as the primary key. That is a complete answer, and it is the expected answer whenever a scenario describes data with no natural identifier. Combining two fields into a single key is outside this syllabus.
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.