Database basics
Databases
- A database is an organised store of data, easy to search, sort and update.
- At IGCSE you use a single-table database — all data in one table.
- A table is made of records and fields, with a primary key.
Records and fields
- A record is one row — all the data about one thing (one student).
- A field is one column — one piece of data every record has ("First name").
- So a table is a grid: each row is a record, each column is a field.
Practice
In a database table, a record is:
A record is a row (all data about one item); a field is a column (one piece of data).
Data types for fields
- Each field stores one data type, chosen to fit the data:
- text/alphanumeric (letters/digits/symbols), character (one character), Boolean (TRUE/FALSE), integer (whole number), real (decimal), date/time.
Practice
Which data type best stores 14/03/2009?
A calendar date is stored as a date/time field.
The primary key and validation
- A primary key is a field with a unique value for every record, so it picks out exactly one record (e.g.
StudentID). - A field like
FormClassis a bad primary key — many students share a class. - Validation checks data is sensible as it goes in (a range check on age, a presence check so a field isn't blank).
Practice
A primary key is a field that:
A primary key is unique, so it identifies exactly one record.
Practice
Why is FormClass a poor choice of primary key?
A primary key must be unique; many records share a FormClass, so it cannot identify one record.
Practice
A presence check on a database field ensures that:
A presence check requires data to be entered; a range check limits the value.
You've got it
Key idea
- a database organises data for easy search/sort/update; IGCSE uses a single table
- a record = a row; a field = a column
- choose a data type per field (text, Boolean, integer, real, date/time)
- a primary key is unique for every record; validation checks data is sensible