← QCE Digital Solutions Unit 2

Reading a relational schema

A relational schema lists each table and its fields, marks the primary key (underlined) and foreign keys (FK), and shows how tables link.

Part of: QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 97

Understanding Reading a relational schema in QCE Digital Solutions Unit 2

What it does: ProductID is the underlined primary key of the Product table, and Name and Price are ordinary fields. StudentID FK in Order matches the primary key StudentID in Student, so each order links to one student. FK means foreign key. A foreign key holds a value that matches the primary key of another table, which creates the link between the two tables. Count every name inside the brackets: OrderID, CustomerID and OrderDate. The foreign key still counts as a field of this table. The FK sits in the table on the many side and refers to the primary key of the other table.

Common mistakes: StuID in Result is a foreign key to the Student table, so the link must be Result.StuID -> Student.StuID, not to Subject.SubID. Title is not the primary key. BookID uniquely identifies each book, so it should be marked PK: Book(BookID PK, Title).

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Reading a relational schema to a class →

This module's key terms are also in a printable crossword (Modules 234–285) and word search (Modules 234–409).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
In a relational schema, how is the primary key usually marked?
It is underlined, e.g. Student(StudentID, Name)It is written in brackets with a question mark, e.g. Student((StudentID?), Name)
Matches
Order(OrderID, StudentID FK, Qty) Student(StudentID, Name) Reading: Student has the FK
↓
The foreign key is in Order, not Student. Student holds the primary key that StudentID FK in Order points to.
Applying
Meanings
Term
Table(PK, field, FK)
Meaning
A relational schema lists each table and its fields, marks the primary key (underlined) and foreign keys (FK), and shows how tables link.
Practice
Student(StudentID PK, FirstName, LastName)
Testing
Gaps
Student(StudentID , Name, House)
Check Answer
Spots
3Result(ResID PK, StuID FK)
4Result.StuID -> Subject.SubID

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Scorpion' badge — Venom Debugger, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. In a relational schema, how is the primary key usually marked?
A. It is written in brackets with a question mark, e.g. Student((StudentID?), Name)
B. It is underlined, e.g. Student(StudentID, Name) ✓
The primary key field is underlined in a schema so you can spot it quickly. It uniquely identifies each record in the table.
2. A schema reads: Enrolment(EnrolID, StudentID (FK), CourseID (FK)). What does FK tell you?
A. The field links to the primary key of another table ✓
B. The field is the primary key of this table
FK means foreign key. A foreign key holds a value that matches the primary key of another table, which creates the link between the two tables.
3. Which line is a correctly written schema entry for a table?
A. Book(BookID, Title, AuthorID (FK)) ✓
B. Book = BookID + Title + AuthorID (FK)
A schema uses the format Table(field, field, field), with the table name first and its fields listed inside the brackets.
4. Schema: Author(AuthorID, Name) and Book(BookID, Title, AuthorID (FK)). Which table does Book link to through AuthorID?
A. Author, because AuthorID is its primary key ✓
B. Nothing, because AuthorID appears in both tables by accident
To trace a link, find the FK field and look for the table where that same field is the primary key. Here that is Author.
5. Schema: Student(StudentID, Name) and Result(ResultID, StudentID (FK), Mark). Which field would you use to join Result to Student?
A. ResultID
B. StudentID ✓
The foreign key StudentID in Result matches the primary key StudentID in Student. ResultID only identifies rows within Result.

Match the Pairs

Student(StudentID, FirstName, House) → A table called Student with three fields; the underlined StudentID is its primary key, which identifies each record
Result(StudentID FK, SubjectID FK, Mark) → A table whose StudentID and SubjectID fields are foreign keys linking each result to a student and a subject
The primary key is underlined in a schema so it stands out as the field that uniquely identifies each record → Product(ProductID, Name, Price)
To trace a link between tables, find the foreign key in one table and match it to the primary key of another table → Order(OrderID, StudentID FK) -> Student(StudentID)
Order(OrderID, StudentID FK, Qty) Student(StudentID, Name) Reading: Student has the FK → The foreign key is in Order, not Student. Student holds the primary key that StudentID FK in Order points to.
Teacher(TeacherID, Name, Room) Reading: Room is the primary key → The primary key is the underlined TeacherID, not Room. Room is an ordinary field that may repeat across teachers.

Key Term

Table(PK, field, FK)
A relational schema lists each table and its fields, marks the primary key (underlined) and foreign keys (FK), and shows how tables link.

Fill in the Blanks

1. Student(StudentID PK, Name, House)
Hint: Type the short label that marks the primary key field in a schema.
2. Result(ResultID PK, StudentID FK, Mark)
Hint: Type the short label for a field that points to another table's primary key.
3. Subject(SubjectID PK, Name) Result(ResultID PK, SubjectID FK, Mark)
Hint: Type the foreign key field name that matches the primary key of the Subject table.
4. Student(StudentID PK, Name) Orders(OrderID PK, StudentID FK, Total) Link: Orders.StudentID -> Student.StudentID
Hint: Type the table whose primary key the foreign key points to.
5. Author(AuthorID PK, Name) Book(BookID PK, AuthorID FK, Title) Book.AuthorID -> Author.AuthorID
Hint: Type the symbol that shows the link running from the foreign key to the primary key.

Spot the Error

Question 1
A Student(StuID PK, Name)
B Subject(SubID PK, Title)
C Result(ResID PK, StuID FK)
D Result.StuID -> Subject.SubID
Line D — StuID in Result is a foreign key to the Student table, so the link must be Result.StuID -> Student.StuID, not to Subject.SubID.
Question 2
A Book(BookID, Title PK)
B Loan(LoanID PK, BookID FK)
C Loan.BookID -> Book.BookID
Line A — Title is not the primary key. BookID uniquely identifies each book, so it should be marked PK: Book(BookID PK, Title).
Question 3
A Team(TeamID PK, Name)
B Player(PlayID PK, TeamID FK)
C Player.TeamID -> Team.Name
Line C — A foreign key links to the primary key of the other table. It must point to Team.TeamID, not Team.Name.
Question 4
A Client(CliID PK, Name)
B Order(OrdID PK, CliID FK)
C Item(ItemID FK, Price)
D Order.CliID -> Client.CliID
Line C — ItemID identifies each row in Item, so it is the table's own primary key and should be marked PK, not FK.
Question 5
A Booking(BookID PK, RoomID)
B Room(RoomID PK, Floor)
C Booking.RoomID -> Room.RoomID
Line A — RoomID in Booking links to the Room table, so it must be marked FK: Booking(BookID PK, RoomID FK).

Student with a class code? Join your class →