A vet surgery, a school library and a games shop all keep the same kind of data again and again: one set of details for each pet, book or game. A database table stores it neatly in rows and columns, so it can be searched and sorted in seconds.
A database is a structured collection of data that allows people to extract information. In a single-table database all the data is in one table. Each field (column) holds one item of data, such as Name or Weight. Each record (row) holds all the data about one person or thing. Every field is given a data type: text/alphanumeric, character, Boolean, integer, real or date/time. Validation rules check that data entered into a field is sensible.
In the lab, click a column heading to highlight a field and click a row to highlight a record. Count how many of each the table has.
Do not mix the two words up. A field is a column (one kind of data); a record is a row (one item). Swapping them loses the mark.
Pick the data type that matches what the field will hold and what you will do with it. Ask: is it a whole number, a decimal, yes/no, a date, one character, or a mix of letters and digits?
| Data type | Holds | Example field and value |
|---|---|---|
| Text / alphanumeric | any letters, digits and symbols | Name: Bella; Postcode: LS1 4AB |
| Character | exactly one letter, digit or symbol | Size: M |
| Boolean | one of two values, TRUE or FALSE | Vaccinated: TRUE |
| Integer | a whole number | NumberOfVisits: 7 |
| Real | a number with a fractional (decimal) part | Weight: 12.5 |
| Date/time | a date or a time | DateOfBirth: 14/03/2022 |
"Number" and "string" are not accepted as answers. Give the exact type: integer or real, and text/alphanumeric.
Four quick checks. Each correct answer earns XP and records this skill on your profile.
What is a record in a database table?
In the lab's table, each column such as Title or Price is called what?
Which data type is most suitable for the number of goals a player has scored?
Which data type is most suitable for whether a library book is on loan?
To define a single-table database from given data, list each field with a sensible name, a data type and, where useful, a validation check. Validation does not prove data is correct, only that it is reasonable.
| Field name | Data type | Validation |
|---|---|---|
| PetID | Text | Format check: letter P then two digits; presence check |
| Name | Text | Presence check; length check (1 to 20 characters) |
| Species | Text | Lookup check: Dog, Cat, Rabbit or Bird |
| Weight | Real | Range check: greater than 0 and no more than 100 |
| Vaccinated | Boolean | Type check: TRUE or FALSE only |
| DateOfBirth | Date/time | Type check: a real date, not in the future |
A range check on Weight would still accept 21.5 when the dog really weighs 12.5. Validation checks that data is sensible, not that it is accurate.
Unlocks once the four checks above are done. Worth more XP, written to IGCSE Paper 1 standard.
A table has field names in the top row and 12 rows of data about 12 students, with 6 columns. How many records and fields does it have?
Which data type should be used for a product price such as 4.99?
Why should a mobile phone number be stored as text rather than integer?
Which validation check is best for a field holding a student's age, from 11 to 18?
This skill is now recorded on your profile. Keep the chain going.