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.

Practise this in the app: instant marking and a fix for every mistake. Free, no email needed to start.

Practise this lesson

From the real world to tables

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.

CustomerIDFirstNameSurnameTownEmail
1AmiraHassanPortviewamira.h@example.com
2BenOkaforMillbrookNULL
3ChenLiPortviewchen.li@example.com
4DanaKowalskiEastfielddana.k@example.com
5EllisBrownMillbrookellis.b@example.com
6FarahAhmedEastfieldNULL

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:

ALTER TABLE tblCustomer ADD COLUMN Phone TEXT;

Result (2 rows):

CustomerID | Surname | Phone
-----------+---------+------
1          | Hassan  | NULL
2          | Okafor  | NULL

The 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.

  1. Two entities appear: a class and a teacher (a teacher's phone belongs to the teacher, not the class).
  2. tblTeacher: TeacherID, TeacherName, TeacherPhone.
  3. 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.

Key idea: Entity = table, attribute = field, instance = record. Use a naming convention (tblName, no spaces). CREATE TABLE defines fields, data types and rules; ALTER TABLE ADD COLUMN amends a structure; new fields start as NULL.

Check yourself

  1. 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).

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

Practise with instant feedback

Sources

Last updated 8 November 2026. Spotted a mistake? Tell us.