- Understand what tables, records and fields are
- Explain why a primary key uniquely identifies each record
- Recognise how related tables can be linked together
- I can label the parts of a database table correctly
- I can choose a sensible primary key for a table
- I can describe why duplicate or unclear data causes problems
Think about a school library system. It stores books, borrowers and loans.
Which item would be best as a unique identifier for one book?
Key vocabulary
price.Tables, Records, Fields and Keys
Why databases use tables
primary key
field
foreign key
field
Columns are fields. Rows are records. A primary key uniquely identifies one record; a foreign key links to another table.
A database stores information in a structured way. Instead of putting everything in one long document, a database separates information into tables. Each table describes one kind of thing. A bike shop database might have a bikes table, a customers table and an orders table.
Inside a table, fields are the headings and records are the entries. In a bikes table, fields could include bike_id, model, type and price. One record would hold the details for one bike.
Primary keys
A primary key is the field that uniquely identifies each record. Names and titles usually make poor primary keys because they can repeat. A generated ID, such as bike_id, is normally safer.
Links between tables
National 4 pupils must create a structure with links. In a relational database, a link is normally made by storing one table's primary key inside another table as a foreign key. For example, a bike_id in an orders table tells us which bike was ordered.
| Term | Bike shop example | Why it matters |
|---|---|---|
| Field | model | Stores one category of data |
| Record | One row for "Trail 200" | Stores one complete item |
| Primary key | bike_id | Identifies a bike uniquely |
| Foreign key | type_id | Links the bike to its type |
Worked examples
In a table of mountain bikes, the whole row for "Trail 200" is one record. The model name alone is only one field value.
bike_id is better than model because two suppliers might sell bikes with the same model name. The key must not repeat.
- Calling a column a record. A column is a field; a row is a record.
- Using a name as a primary key when it could repeat.
- Putting several pieces of information into one field, such as "red mountain bike" instead of separate colour and type fields.
When you describe a database, use the exact vocabulary. "The table has fields for model, type and price" is much clearer evidence than "the database has information in it."
Task Set
Questions 1-3 are auto-checked. Questions 4-5 are self-marked evidence practice.
1. In a table, what is a field? TYPE 1
2. What is the main job of a primary key? TYPE 1
3. Which field is most suitable as a primary key for customers? TYPE 1
4. Explain the difference between a record and a field. TYPE 2
5. Draw a simple design for a bikes table with four fields. Mark the primary key. TYPE 2
bike_id (primary key), model, bike_type, price.Background lesson for N3 Information Solutions and N4 Information System Design and Development. It prepares pupils for N3 O1.1/O1.2 and N4 O1.1 but is not itself an assessed task.