All subjects · Information Technology · WIT11 2 hour written exam on IT systems, networks and data
Relational databases: tables, records, fields and keys
Edexcel IAL Information Technology · WIT11 2 hour written exam on IT systems, networks and data
Spec points 5.2.1 and 5.2.2: why databases are used to structure data, and relational database structure (tables, primary keys, foreign keys, composite keys, records, fields). June 2024 asked candidates to name keys in given tables; the composite key was the most missed part.
Why use a database
A database stores structured data so it can be searched, sorted, updated and shared efficiently. A relational database splits data into related tables to avoid storing the same data many times (data redundancy), which also avoids inconsistency (one copy updated but not another). Other benefits: many users at once, validation and data types, security through access rights, queries with SQL, backup and recovery.
Structure and keys
- Table: data about one type of thing (an entity), for example tbl_employee.
- Record: one row, all the data about one item (one employee).
- Field: one column, one attribute (postCode), with a data type (text/varchar, integer, decimal, date, boolean).
- Primary key: a field that uniquely identifies each record (empNum).
- Foreign key: a field in one table that is the primary key of another table, creating the link (employee in tbl_shift refers to empNum).
- Composite key: a primary key made of two or more fields that together are unique (in tbl_shift, store + date + start identifies a shift when no single field does).
Convention: underline primary key fields and mark foreign keys with an asterisk; a field that is part of a composite primary key and also a foreign key gets both (as the June 2025 examiner report expected).
Choosing data types
Choose the type that fits the data and the operations needed. An ID such as 062833 must be text, not integer: an integer would drop the leading zero (storing 62833) and the value is never used in calculations. Phone numbers are text for the same reason. Prices: decimal/currency. Dates: date type, so they can be compared and sorted correctly.
Worked example
Tables: tbl_employee(empNum, address, postCode, rate), tbl_store(storeNum, address, postCode), tbl_shift(store, date, start, end, employee). Identify the keys.
- tbl_employee primary key: empNum.
- tbl_store primary key: storeNum.
- tbl_shift foreign keys: store (links to storeNum) and employee (links to empNum).
- tbl_shift composite key: store, date and start (three fields together identify one shift).
Answer: PKs empNum and storeNum; FKs store and employee; composite key store + date + start.
Check yourself
- What is a primary key?
- The first field typed into a table
- A field that links to another table only
- A field (or fields) that uniquely identifies each record
- A password for the database
Show answer
A field (or fields) that uniquely identifies each record. Unique identifier.
- In tbl_shift(store, date, start, end, employee), what is 'employee' if it holds values of empNum from tbl_employee?
- A foreign key
- A composite key on its own
- A record
- A table
Show answer
A foreign key. It refers to another table's primary key.
- What is a composite key?
- A key made of letters and numbers
- A foreign key that is also encrypted
- A primary key made up of two or more fields
- A field that stores images
Show answer
A primary key made up of two or more fields. Fields together are unique.
- In a table of students, what is one row called?
- A field
- A record
- A key
- A query
Show answer
A record. Rows are records; columns are fields.
- An employee number such as 062833 must be stored. Explain why it should not be an integer data type.
Show answer
An integer would remove the leading zero, storing 62833, so the employee number would be wrong; it should be stored as text because it is never used in calculations.. Leading zero lost (the question already says it cannot be an integer; give the reason).
- Explain one reason for storing data in several related tables instead of one large table.
Show answer
It reduces data redundancy, because details such as a store's address are stored once, so there is less risk of inconsistent copies when they are updated.. Redundancy and consistency.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (2018), Unit 1
- Pearson Edexcel IAL Information Technology WIT11/01 question paper, June 2024
- Pearson Edexcel IAL Information Technology WIT11/01 examiner report, June 2024
- Pearson Edexcel IAL Information Technology WIT11/01 examiner report, June 2025
Last updated 8 November 2026. Spotted a mistake? Tell us.