Data Type

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.

Topic 9 asks five kinds of question over and over: count something, choose a data type, choose a validation check, choose a primary key, and complete or interpret a query. Each has a shape. Learn the shape and you will never face a Topic 9 question with nothing to write — you fill the frame with the table in front of you.

Structure, data types, validation, primary keys, SQL completion and output interpretation, mixed together the way an examination mixes them. Every question refers to the BOOKING table earlier on this page — scroll back to it whenever you need to; that is exactly what you would do with a printed table in the exam. Answers are marked in this page only: nothing is stored and nothing is sent anywhere.