Databases: tables, fields, records, data types and primary keys
The syllabus uses single-table databases. You need to be able to describe one, choose a data type for each field, and pick the field that makes a good primary key.
Tables, records and fields
A table holds data about one kind of thing, such as books or members. Each row is a record: all the data about one book. Each column is a field: one piece of data, such as the title, stored for every record.
Data types
- Text / alphanumeric: letters, digits and symbols, such as a name or a postcode.
- Character: a single character, such as a size code M.
- Boolean: one of two values, such as TRUE for “in stock”.
- Integer: a whole number, such as the number of copies.
- Real: a number with decimal places, such as a price.
- Date/time: a date or time, such as the date a book was borrowed.
Primary keys
A primary key is a field whose value is different for every record, so each record can be found on its own. A made-up ID such as MemberID works well. A name doesn’t, because two people can share one.
Where marks go
- Mixing up record (a row) and field (a column).
- Choosing integer for a phone number or ID that could start with 0 or contain letters.
- Picking a primary key that could repeat, such as a surname or a date.
- Saying a primary key must be a number: it only has to be unique.
- Choosing text for a yes/no field that should be Boolean.
Try a question
4 MARKS · MARKED ON THIS PAGEA hockey club stores data about its members in a table called MEMBER, shown above. Complete the grid by answering each question about the table. Give field names exactly as they appear in the table.
MEMBER
| MemberID | Surname | Team | AgeGroup | FeesPaid |
|---|---|---|---|---|
| M201 | Okafor | Hawks | U14 | TRUE |
| M202 | Lindqvist | Falcons | U16 | FALSE |
| M203 | Okafor | Falcons | U16 | TRUE |
| M204 | Patel | Hawks | U14 | TRUE |
| M205 | Moreau | Eagles | U18 | FALSE |
| M206 | Tanaka | Eagles | U18 | TRUE |
| M207 | Silva | Hawks | U16 | TRUE |
Fill in your answer first.