Keys, ERDs & Building the Database

Step 1 of 5

Watch the explainer video

5½ min · narrated walkthrough: every key type, 1:1, 1:M and M:M, and a full ERD for a steelpan academy · captions available

Class activity: Mas Camp Database — printable student sheets for a 40-minute group lesson.

TRY IT · OBJECTIVE 13
Book Swap: Your First Database Website →
Build a real database in WAMP with phpMyAdmin, then connect a simple website to it, in one lesson.

EXPLORE IN 3D

Keys and relationships in 3D

See how foreign keys link rows in 1:1, 1:M and M:M relationships, and how a junction table resolves a M:M.

REVISION · PAST-PAPER PRACTICE

Test yourself

Multiple-choice items in Paper 1 style and structured questions in Paper 2 style, adapted from CAPE Information Technology Unit 2 past papers. Your score and answers are saved on this device. When you have finished, submit them to your teacher at the end of this section.

MCQ best –Structured –

Paper 1 practice · 12 multiple-choice items

Choose one answer for each item, then check. Aim for 80% or better.

Adapted from CAPE U2 P1 2019 Item 1; 2020 Item 13; 2022 Item 13

1. A foreign key is an attribute

(A) that uniquely identifies each row in its own table (B) in one table whose values must match the primary key of another table or be null (C) used only to speed up searching (D) that stores an encryption key

Answer: B. A foreign key links tables: each value must match an existing primary key in the related table (or be empty).

Adapted from CAPE U2 P1 2020 Item 5

2. In the notation CUSTOMER (CustID, Name, Phone), the underlined attribute is the

(A) foreign key (B) secondary key (C) primary key (D) calculated field

Answer: C. By convention the primary key is underlined; foreign keys are often marked with an asterisk.

Adapted from CAPE U2 P1 2019 Item 14

3. Which feature must be enabled so that the DBMS blocks deleting a customer who still has orders?

(A) Activating macros (B) Writing SQL comments (C) Providing multiple views (D) Enforcing referential integrity

Answer: D. Referential integrity stops changes that would leave foreign keys pointing at records that do not exist.

Adapted from CAPE U2 P1 2019 Item 15; 2020 Item 18

4. STUDENT (StudentID, Name) and COURSE (CourseID, Title) are linked by REGISTRATION (StudentID, CourseID, Mark). The relationship between STUDENT and COURSE is

(A) many-to-many (M:M), resolved by REGISTRATION (B) one-to-one (C) one-to-many (D) not a relationship

Answer: A. A student takes many courses and a course has many students. The REGISTRATION junction table resolves this M:M.

Adapted from CAPE U2 P1 2022 Item 15; 2024 Item 14

5. At a secondary school each teacher teaches many students and each student is taught by many teachers. The ERD should show

(A) Teacher 1 — 1 Student (B) Teacher M — M Student (C) Teacher 1 — M Student (D) Teacher M — 1 Student

Answer: B. Many on both sides: a many-to-many (M:M) relationship, implemented with a junction table.

Adapted from CAPE U2 P1 2019 Item 9; 2022 Item 11; 2024 Item 11

6. ACCOUNTS table:

Name	Country	Year	Owed
R. Ali	Guyana	2023	18 000
S. Best	Barbados	2024	9 000
T. King	Guyana	2024	21 000
U. Lee	Guyana	2024	7 500
V. Moe	Jamaica	2024	12 000

SELECT COUNT(*) FROM ACCOUNTS WHERE Country = 'Guyana' AND Year = 2024 AND Owed < 20000;
What value is returned?

(A) 2 (B) 3 (C) 1 (D) 4

Answer: C. Guyana and 2024 gives T. King and U. Lee; of these only U. Lee owes less than 20 000, so the count is 1.

Adapted from CAPE U2 P1 2020 Item 6

7. Which field would MOST likely be validated using a range check?

(A) Student name (B) Student address (C) Student ID (D) Student age

Answer: D. A range check tests that a number lies between limits, e.g. age 11–20. Names and addresses have no numeric range.

Adapted from CAPE U2 P1 2019 Item 12; 2020 Item 14

8. Which of the following are MOST appropriate for making queries in a DBMS?
I. Structured Query Language (SQL)
II. Data Definition Language (DDL)
III. Query by Example (QBE)

(A) I and II only (B) I and III only (C) II and III only (D) I, II and III

Answer: B. SQL SELECT statements and QBE grids retrieve data. DDL (CREATE/ALTER) defines structure rather than querying.

Adapted from CAPE U2 P1 2019 Items 6–7; 2024 Item 13

9. An EMPLOYEE table stores Salary as Text. The MOST likely problem is that

(A) calculations such as totals or averages of salary cannot be done (B) salaries cannot be printed (C) the ID field will contain duplicates (D) dates will be in the wrong format

Answer: A. Numbers stored as text cannot be summed or averaged. Salary should use a Currency/Number data type.

EduTech practice item · ERD notations (Chen symbols as taught in the CAPE IT textbook, pp. 58–59)

10. In Chen notation for entity-relationship diagrams, a diamond represents

(A) an entity (B) an attribute (C) a relationship (D) a data store

Answer: C. Chen notation: rectangles = entities, diamonds = relationships, ovals = attributes. A data store belongs to DFDs, not ERDs.

EduTech practice item · ERD notations

11. In a Chen ERD, the attribute CustID is drawn in an oval with its name underlined. The underline shows that CustID is

(A) a foreign key (B) the primary key (identifier) (C) a calculated field (D) a multivalued attribute

Answer: B. An underlined attribute is the entity’s key — the same convention as underlining the primary key in table notation.

EduTech practice item · ERD notations (CXC 2020 specimen P2 Q1(e) mark scheme uses 1/M labels)

12. An ERD shows Customer joined to Order by a line ending in a single bar at Customer and a three-pronged fork at Order. This notation, and the relationship it shows, are

(A) Chen notation; one-to-one (B) crow’s foot notation; many-to-many (C) DFD notation; a data flow (D) crow’s foot notation; one-to-many (one customer, many orders)

Answer: D. In crow’s foot notation the bar means one and the fork (crow’s foot) means many, so this is 1:M. CXC’s specimen mark scheme shows the same fact with 1 and M written on a plain line.

Paper 2 practice · structured questions (21 marks)

Write a full answer to each part, then show the mark scheme and tick every point you earned. Marks for a part are capped at its maximum. Mark schemes are EduTech’s own; CXC does not publish them.

Based on CAPE U2 P2 May/June 2025, Question 2(a)–(b) and 2024, Question 1(c)

Question 1 [11 marks]

Harbour Community Library uses these files:
BOOK

BookID	Title	Available
B10	Caribbean Cooking	Yes
B11	Solar Homes	No
B12	Reef Life	Yes
B13	Sign Language Basics	No

RESERVATION

MemberID	BookID	RequestDate
M4	B11	2 May
M9	B12	3 May
M4	B10	5 May

(a) Identify the foreign key and the file in which it is found. [2]

MARK SCHEME (a) · max 2

BookID [1]In the RESERVATION file [1]

(b) State the purpose of the RESERVATION (transaction) file. [1]

MARK SCHEME (b) · max 1

It records recent events/requests (reservations) used to update or act on the BOOK master file [1]

(c) List the titles of the reserved books that are available for lending. [2]

MARK SCHEME (c) · max 2

Reef Life [1]Caribbean Cooking [1]

(d) A member can reserve many books and a book can be reserved by many members. Draw an ERD for reserving books. Show the cardinality and the primary key of each entity. (Use any standard notation — Chen, crow’s foot or 1/M labels — consistently.) [6]

Upload a photo of your drawing (optional · JPG or PNG · flat, well-lit, whole diagram in view)

MARK SCHEME (d) · max 6

MEMBER entity with PK MemberID [1]BOOK entity with PK BookID [1]RESERVATION junction entity [1]RESERVATION PK: MemberID + BookID (+ RequestDate), with both as FKs [1]MEMBER 1 — M RESERVATION [1]BOOK 1 — M RESERVATION (M:M resolved) [1]

Submit to your teacher

Send your multiple-choice choices and written answers to your teacher for marking. Your teacher will also see whether you opened the mark scheme or checked your answers before submitting. You can submit again if you improve your answers.

Full name Class / group

Items are adapted from CAPE® Information Technology Unit 2 Papers 01 and 02 (2019–2026) for practice. © Caribbean Examinations Council for the original papers.