All subjects · Information Technology · WIT14 3 hour practical exam on database solutions
Entities, tables, records and fields
Edexcel IAL Information Technology · WIT14 3 hour practical exam on database solutions
Specification 18.1.1 asks you to construct and amend tables, records, fields and relationships. This lesson builds the Harbour Bike Hire tables, first as a design and then in SQL, so you can see exactly what database software creates behind its design view.
From the real world to tables
- An entity is a thing the organisation stores data about: a customer, a bike, a hire.
- Each entity becomes a table.
- An attribute is a property of the entity (a customer's surname, a bike's daily rate). Each attribute becomes a field (column).
- One instance of the entity (one particular customer) is a record (row).
- Name things consistently. Pearson's mark schemes credit a naming convention such as a tbl prefix for tables (tblCustomer) and clear field names without spaces (FirstName, DailyRate).
Most SQL examples in this unit use one small database for a fictional business, Harbour Bike Hire. It has four tables: tblCustomer, tblBike, tblHire (one row per hire booking) and tblHireItem (which bikes were on each hire). A hire can include several bikes and a bike is hired many times, so tblHireItem links the two with a composite key.
| CustomerID | FirstName | Surname | Town | |
|---|---|---|---|---|
| 1 | Amira | Hassan | Portview | amira.h@example.com |
| 2 | Ben | Okafor | Millbrook | NULL |
| 3 | Chen | Li | Portview | chen.li@example.com |
| 4 | Dana | Kowalski | Eastfield | dana.k@example.com |
| 5 | Ellis | Brown | Millbrook | ellis.b@example.com |
| 6 | Farah | Ahmed | Eastfield | NULL |
tblCustomer in the Harbour Bike Hire database
Creating a table in SQL
In design view you type field names and pick data types. The SQL that does the same job is CREATE TABLE (its syntax was introduced in Unit 1):
CREATE TABLE tblBikeTest (
BikeID TEXT PRIMARY KEY,
Model TEXT NOT NULL,
BikeType TEXT NOT NULL,
DailyRate REAL NOT NULL
);Each line names a field, gives its data type and any rules (PRIMARY KEY, NOT NULL). The full script for the four Harbour Bike Hire tables is:
CREATE TABLE tblCustomer (
CustomerID INTEGER PRIMARY KEY,
FirstName TEXT NOT NULL,
Surname TEXT NOT NULL,
Town TEXT,
Email TEXT
);
CREATE TABLE tblBike (
BikeID TEXT PRIMARY KEY CHECK (BikeID LIKE 'B__' AND length(BikeID) = 3),
Model TEXT NOT NULL,
BikeType TEXT NOT NULL CHECK (BikeType IN ('Road', 'Mountain', 'Hybrid', 'Electric')),
DailyRate REAL NOT NULL CHECK (DailyRate BETWEEN 5 AND 60)
);
CREATE TABLE tblHire (
HireID INTEGER PRIMARY KEY,
CustomerID INTEGER NOT NULL REFERENCES tblCustomer (CustomerID),
HireDate TEXT NOT NULL,
Days INTEGER NOT NULL CHECK (Days >= 1)
);
CREATE TABLE tblHireItem (
HireID INTEGER NOT NULL REFERENCES tblHire (HireID),
BikeID TEXT NOT NULL REFERENCES tblBike (BikeID),
PRIMARY KEY (HireID, BikeID)
);Amending a table
Requirements change, so you must be able to amend structures:
- Add a field: in design view type a new row; in SQL use ALTER TABLE.
- Rename or change a field's data type or properties in design view (database software may warn that data could be lost if values do not fit the new type).
- Add or edit records in datasheet view, through a form, or with INSERT and UPDATE.
ALTER TABLE tblCustomer ADD COLUMN Phone TEXT;Result (2 rows):
CustomerID | Surname | Phone
-----------+---------+------
1 | Hassan | NULL
2 | Okafor | NULLThe new field exists for every record but is empty (NULL) until data is entered.
Worked example
A dance school stores, for each class, its name, day, start time, teacher's name and teacher's phone. Identify the entities and propose tables with fields.
- Two entities appear: a class and a teacher (a teacher's phone belongs to the teacher, not the class).
- tblTeacher: TeacherID, TeacherName, TeacherPhone.
- tblClass: ClassID, ClassName, ClassDay, StartTime, TeacherID (to link each class to its teacher).
Answer: Two tables, so each teacher's phone number is stored once.
Check yourself
- In a library database, which is an entity rather than an attribute?
- ISBN
- Book
- Title
- Publication year
Show answer
Book. Book is a thing data is stored about (a table); the others are properties of a book (fields).
- What is one row of tblCustomer called?
- A field
- An entity type
- A key
- A record
Show answer
A record. Each row holds the data for one customer: a record.
- How many rows does this query return on the Harbour Bike Hire database?
```sql
SELECT * FROM tblCustomer
```
Show answer
6. tblCustomer holds 6 records, one per customer.
- How many fields does tblBike have? This SQLite query counts them:
```sql
SELECT COUNT(*) FROM pragma_table_info('tblBike')
```
Show answer
4. tblBike has 4 fields: BikeID, Model, BikeType and DailyRate. pragma_table_info lists one row per field.
- Which table and field names follow a good naming convention?
- tblBooking with fields BookingID, BookingDate
- Booking Table with fields Booking ID, date of booking
- t1 with fields f1, f2
- BOOKINGS!! with fields ID#, Date?
Show answer
tblBooking with fields BookingID, BookingDate. Consistent prefix, no spaces or symbols, meaningful names.
- Which SQL statement adds a Postcode field to tblCustomer without losing existing data?
- CREATE TABLE tblCustomer (Postcode TEXT);
- DROP TABLE tblCustomer;
- ALTER TABLE tblCustomer ADD COLUMN Postcode TEXT;
- INSERT INTO tblCustomer (Postcode) VALUES ('');
Show answer
ALTER TABLE tblCustomer ADD COLUMN Postcode TEXT;. ALTER TABLE changes the structure of an existing table. CREATE would fail as the table exists; DROP deletes it; INSERT adds a record, not a field.
- A vet stores the owner's name and phone number on every pet record. Explain why the owner should be a separate entity.
Show answer
One owner can have several pets, so storing the owner's details in a separate table means they are entered once and only need updating in one place.. Owners can own many pets; a separate Owner table avoids repeating their details and the errors that come with updating many copies.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (Issue 2, February 2021): Unit 4, Topics 17 to 19
- Pearson: WIT14/01 mark scheme, June 2024
Last updated 8 November 2026. Spotted a mistake? Tell us.