Relational database fundamentals, schemas, ERDs and keys – IB Computer Science exam questions with mark scheme

1Core3 marks

Define the terms primary key and foreign key, and outline one reason for using a foreign key in a relational database. [3]

Primary key: a field (or combination of fields) whose value uniquely identifies each record in a table (must say 'unique' or 'uniquely')(1)
Foreign key: a field in one table that refers to / matches the primary key of another table (accept 'is the primary key of another table')(1)
Reason: it creates a relationship / link between the two tables; accept: enforces referential integrity (a record cannot refer to a record that does not exist); accept: data is stored once and referenced, reducing redundancy(1)
Answer: A primary key uniquely identifies each record; a foreign key is a field that refers to the primary key of another table, used to link the tables (and enforce referential integrity).
Examiner tip: Say 'uniquely' for the primary key and 'primary key of another table' for the foreign key.

2Standard3 marks

Students can join many clubs and each club has many students. Explain how this many-to-many relationship is implemented in a relational database. [3]

A many-to-many relationship cannot be implemented directly: a single foreign key in either table would need repeated values / repeating groups(1)
Create a link table (accept junction / associative / bridge table), e.g. Membership(StudentID, ClubID)(1)
The link table holds two foreign keys, StudentID referencing Student and ClubID referencing Club, turning the many-to-many into two one-to-many relationships (together they usually form a composite primary key; allow a separate surrogate primary key, e.g. MembershipID)(1)
Answer: Link table Membership(StudentID FK, ClubID FK), usually with (StudentID, ClubID) as a composite primary key, giving two one-to-many relationships.
Examiner tip: Say the link table holds two foreign keys; just writing 'a third table' earns only one mark.

3Core6 marks

A gym stores details of its members in a table MEMBER. Some example values are shown.

Field	Example value
PhoneNumber	07700 900123
JoinDate	15 January 2026
MonthlyFee	24.99
IsStudent	Yes / No

(a) State the most appropriate data type for each of the four fields. [4]

(b) The database designer stores PhoneNumber as an INTEGER. Outline one effect of this choice. [1]

(c) A second designer stores JoinDate as text in the form dd/mm/yyyy. Outline one effect of this choice when members are listed in order of joining. [1]

(a) PhoneNumber: text / string / VARCHAR / CHAR (do not accept integer or any numeric type)(1)
(a) JoinDate: DATE (accept DATE/TIME, DATETIME)(1)
(a) MonthlyFee: DECIMAL / NUMERIC / currency (accept real / float / double; do not accept integer)(1)
(a) IsStudent: BOOLEAN (accept bit / Yes/No / TRUE/FALSE type)(1)
(b) The leading zero is lost (07700900123 is stored as 7700900123) OR spaces / '+' / brackets cannot be stored OR numbers longer than the integer range overflow (accept any one valid effect; ignore 'arithmetic could be done on it' unless the effect is stated)(1)
(c) Dates are compared character by character as text, so they sort by day number rather than chronologically (e.g. 28/02/2025 appears after 15/01/2026) (accept: date calculations / validation of impossible dates such as 31/02 are not possible)(1)
Answer: (a) Text/VARCHAR; DATE; DECIMAL (or real/currency); BOOLEAN. (b) Leading zero dropped (7700900123) or spaces/+ rejected. (c) Text dates sort by day first, not chronologically.
Examiner tip: Phone numbers are identifiers, not quantities: if you would never add two of them, they are text.

Stuck on Relational database fundamentals, schemas, ERDs and keys?

Get it sorted in a free 30-minute session

We work through your weakest questions on this topic on Zoom. I reply the same day.

Free 30-min session on this topic Send me 5 questions a day

4Core4 marks

A school is designing a database for its library. Three design documents have been written.

Document P: Members borrow books. A book can be borrowed many times, but only by one member at a time. Each loan has a due date.

Document Q: BOOK(ISBN, Title, Author, YearPublished); MEMBER(MemberID, Name, TutorGroup); LOAN(LoanID, ISBN, MemberID, DateOut, DateDue). ISBN is a 13-character string; YearPublished is an integer.

Document R: The tables are stored in the file library.db on the school's server SSD. An index on Title is held as a B-tree, and the LOAN table is backed up nightly.

(a) State which level of database schema (conceptual, logical or physical) each document describes. [3]

(b) Outline one benefit of keeping the logical schema separate from the physical schema. [1]

(a) P: conceptual (high-level entities and relationships, no fields/data types)(1)
(a) Q: logical (tables, attributes/fields, keys and data types, independent of storage)(1)
(a) R: physical (how/where data is actually stored: files, indexes, storage devices)(1)
(b) The physical storage can be changed (e.g. moved to a new server, index added) without changing the logical design or the applications that use it (data independence) (accept: designers can focus on structure without worrying about storage details)(1)
Answer: (a) P conceptual, Q logical, R physical. (b) Storage can change without affecting the logical design/applications (physical data independence).
Examiner tip: Data types and keys belong to the logical schema; files, indexes and hardware belong to the physical schema.

5Standard6 marks

The entity-relationship diagram (ERD), drawn in crow's foot notation, shows part of the design of a database for a veterinary surgery.

(a) State the cardinality of the relationship between OWNER and PET. [1]

(b) With reference to the symbols at both ends of the relationship between PET and APPOINTMENT, explain the modality of this relationship. [2]

(d) Explain why the foreign key for the OWNER–PET relationship is placed in the PET table rather than in the OWNER table. [1]

(a) One-to-many (1:M) (accept: one owner to one or more pets; do not accept many-to-one unless the direction PET to OWNER is clearly stated)(1)
(b) The circle at the APPOINTMENT end shows the relationship is optional: a pet may have zero appointments (zero or many)(1)
(b) The double bar at the PET end shows it is mandatory: every appointment must be for exactly one pet(1)
(c) PET contains a foreign key (e.g. OwnerID) referring to the primary key of OWNER(1)
(c) APPOINTMENT contains two foreign keys, one referring to the primary key of PET (e.g. PetID) and one to the primary key of VET (e.g. VetID) (both needed for the mark)(1)
(d) Each pet has only one owner, so PET can hold a single owner ID; an owner can have many pets, so OWNER would need a repeating group / multiple values in one field (breaking atomicity) (accept: the foreign key goes on the 'many' side)(1)
Answer: (a) One-to-many. (b) Pet: zero or many appointments (optional); appointment: exactly one pet (mandatory). (c) PET (FK to OWNER); APPOINTMENT (FKs to PET and VET). (d) FK goes on the 'many' side; OWNER would need several pet IDs in one field.
Examiner tip: Cardinality is the maximum (one/many); modality is the minimum (zero = optional, one = mandatory). Say which end each symbol is at.

6Standard6 marks

A swimming club records race results in a table RESULT. A swimmer may race in several events, and in each event may swim in a heat and, if fast enough, a final. Details of swimmers are held in a table SWIMMER and details of events in a table EVENT.

SwimmerID	EventCode	Round	Lane	TimeSec
S014	FR50	Heat	3	28.41
S014	FR50	Final	4	27.95
S022	FR50	Heat	5	29.10
S022	BK100	Heat	5	71.36
S031	FR50	Heat	4	28.02
S031	FR50	Final	4	28.20
S014	BK100	Heat	3	69.84
S040	FR50	Heat	3	30.55

(a) With reference to the data, explain why SwimmerID cannot be the primary key of RESULT. [1]

(b) A student suggests the composite key (SwimmerID, EventCode). Using the data, explain why this is not suitable. [1]

(d) Identify two foreign keys in RESULT, stating the table each one links to. [2]

(e) Explain why TimeSec should not form part of the primary key. [1]

(a) SwimmerID values repeat (e.g. S014 appears in three records), so it does not uniquely identify each record(1)
(b) The same swimmer/event pair occurs more than once, e.g. S014 / FR50 (or S031 / FR50) appears for both the heat and the final, so the combination is not unique(1)
(c) (SwimmerID, EventCode, Round) (all three fields required; ignore order)(1)
(d) SwimmerID, linking to (the primary key of) SWIMMER(1)
(d) EventCode, linking to (the primary key of) EVENT(1)
(e) TimeSec is not needed to make records unique: (SwimmerID, EventCode, Round) already identifies each record, and a primary key should be minimal (accept: a time is not known until the race is swum, so the record could not be created in advance / a measured value may be corrected later, which would change the key; do not accept 'two races could have the same time' alone, since a composite-key field need not be unique on its own)(1)
Answer: (a) S014 repeats. (b) S014/FR50 appears for heat and final. (c) (SwimmerID, EventCode, Round). (d) SwimmerID to SWIMMER; EventCode to EVENT. (e) Not needed for uniqueness (key should be minimal); time unknown until swum / may be corrected.
Examiner tip: Always quote a specific repeated value from the table when asked to use the data; a general definition of a primary key scores nothing.

7Standard6 marks

An online shop keeps all its data in a single flat-file table ORDERS. Every one of its 1200 order records stores the customer's name (30 bytes) and full address (60 bytes) as well as the order details. The orders were placed by 150 different customers. Part of the file is shown; all three orders were placed by the same customer.

OrderNo	CustName	CustAddress	Item
5011	R. Mensah	12 Mill Lane, Leeds	Desk lamp
5034	R. Mensah	12 Mill Ln, Leeds	Bulbs x4
5102	R. Mensah	3 Park Road, York	Shelf

The shop plans to move to a relational database with a CUSTOMER table and an ORDER table. Each customer will be given a 4-byte CustomerID, which is also stored in each order record as a foreign key.

(a) Calculate the number of bytes saved by this change, considering only the customer name, address and CustomerID data. Show your working. [3]

(b) Identify the data integrity problem shown in the extract. [1]

(a) Flat file: 1200 × (30 + 60) = 108 000 bytes(1)
(a) Relational: 150 × (4 + 30 + 60) + 1200 × 4 = 14 100 + 4800 = 18 900 bytes (method mark: both tables counted)(1)
(a) Saving = 89 100 bytes (accept 89.1 kB / 87.0 KiB; allow follow-through from one arithmetic slip)(1)
(b) Inconsistent data: the same customer's address is stored differently in different records (12 Mill Lane / 12 Mill Ln / 3 Park Road), so it is unclear which is correct (accept: update anomaly - address changed in one record only)(1)
(c) Each customer's name and address is stored only once, in one CUSTOMER record (reduced redundancy)(1)
(c) Orders refer to the customer via the CustomerID foreign key, so an address change is made in one place and every order automatically uses the updated value (consistency)(1)
Answer: (a) 108 000 - 18 900 = 89 100 bytes saved. (b) Inconsistent / out-of-date addresses for the same customer. (c) Address stored once in CUSTOMER; orders link by CustomerID, so one update keeps all orders consistent.
Examiner tip: Remember that the relational design still costs something: add the CustomerID in both tables before subtracting.

8Hard6 marks

A start-up is building a social media app. Posts can contain text, images, polls or video, and new post types are added every few months. Comments can be replied to, giving threads nested to any depth. The developers write the app in an object-oriented language, with a Post class whose objects hold lists of Comment objects. The company expects billions of posts within a few years. User accounts and paid subscriptions must also be stored.

Discuss whether a relational database is a suitable choice for storing this app's data. [6]

Benefit: for structured data such as user accounts and subscriptions, keys and constraints (primary/foreign keys) enforce integrity and consistency, e.g. no payment for a non-existent user(1)
Benefit: concurrency control (e.g. locking/transactions) lets many users update data at the same time safely, important for payments/subscriptions (accept reduced redundancy through linked tables)(1)
Limitation: rigid schema - each new post type with different fields needs the table structure altered (or many NULL columns), which is slow/risky on a large live database(1)
Limitation: hierarchical/unstructured data - comment threads of any depth need self-referencing tables and repeated joins/recursive queries; images/video do not fit naturally in tables(1)
Limitation: big-data scalability - relational databases mainly scale vertically, and joins across billions of rows are hard to distribute across many servers (horizontal scaling) (accept: object-relational impedance mismatch - a Post object holding a list of nested Comment objects must be split across several tables and reassembled, needing extra mapping code/ORM)(1)
Justified conclusion, e.g. relational suitable for accounts/subscriptions but not ideal for the post content, so a hybrid (relational + NoSQL/document store) is best (must be linked to the scenario; a conclusion without justification scores 0)(1)
Answer: Relational suits structured, integrity-critical data (accounts, payments: keys, constraints, concurrency control), but posts suffer from a rigid schema, nested/unstructured data, impedance mismatch with nested objects and limited horizontal scaling for billions of rows. Best: hybrid.
Examiner tip: A 'discuss' needs both sides and a conclusion tied to the scenario; listing limitations only caps you at about 4 marks.

9Stretch8 marks

A driving school is designing a relational database.

Each instructor has a unique staff number, a name and a phone number. A newly hired instructor may not yet have any learners.
Each learner has a learner ID, a name and a date of birth, and is assigned to exactly one instructor.
A learner books lessons. Each lesson has a date and a start time, is for one learner, and takes place in one car. A learner who has just registered may not yet have booked a lesson.
Each car has a registration number and a model. A car can be used for many lessons; a new car may not yet have been used.

(a) Construct an entity-relationship diagram (ERD) for this database, showing the cardinality and modality of each relationship. [4]

(b) Construct the table designs, in the form TABLE(Field1, Field2, …), clearly identifying every primary key and foreign key. [4]

(a) Four entities named: INSTRUCTOR, LEARNER, LESSON, CAR (and no extra entities for attributes)(1)
(a) INSTRUCTOR to LEARNER one-to-many, with exactly one (mandatory) at the INSTRUCTOR end and zero or many (optional) at the LEARNER end(1)
(a) LEARNER to LESSON one-to-many, with exactly one at the LEARNER end and zero or many at the LESSON end(1)
(a) CAR to LESSON one-to-many, with exactly one at the CAR end and zero or many at the LESSON end (accept any clear notation, e.g. crow's foot or 1 / 0..*; cardinality and modality both required for each relationship mark)(1)
(b) INSTRUCTOR(StaffNo, Name, Phone) and CAR(RegNo, Model) with the primary keys identified(1)
(b) LEARNER(LearnerID, Name, DateOfBirth, StaffNo) with StaffNo identified as a foreign key to INSTRUCTOR(1)
(b) LESSON contains LearnerID as a foreign key to LEARNER and RegNo as a foreign key to CAR, plus LessonDate and StartTime(1)
(b) A suitable primary key for LESSON identified: LessonID (surrogate) or a composite key such as (LearnerID, LessonDate, StartTime) or (RegNo, LessonDate, StartTime) (do not accept LearnerID or RegNo alone; ignore StaffNo also placed in LESSON)(1)
Answer: ERD: INSTRUCTOR ||--o< LEARNER ||--o< LESSON >o--|| CAR. Tables: INSTRUCTOR(StaffNo, Name, Phone); LEARNER(LearnerID, Name, DateOfBirth, StaffNo*); CAR(RegNo, Model); LESSON(LessonID, LearnerID*, RegNo*, LessonDate, StartTime) (* = foreign key).
Examiner tip: Read each sentence for the word 'may' (zero = optional) versus 'exactly one' (mandatory) - that is where the modality marks are.

10Hard6 marks

A company database contains two tables. DeptCode in EMPLOYEE is a foreign key referring to DEPARTMENT. Referential integrity is enforced and no cascading rules are set.

DEPARTMENT

DeptCode	DeptName
FIN	Finance
HR	Human Resources
LEG	Legal
OPS	Operations

EMPLOYEE

EmpID	Name	DeptCode
101	Ama Owusu	FIN
102	Ben Clarke	OPS
103	Chen Wei	HR
104	Dina Patel	OPS

(a) For each operation, state whether the database management system will accept or reject it, giving a reason. [4]

Add employee 105, Eli Brook, in department MKT.
Delete the department FIN.
Add employee 103, Fay Lund, in department HR.
Delete the department LEG.

(b) Two HR clerks open employee 102's record at the same time. One changes the department to HR; the other changes the name and saves a moment later. Explain how concurrency control prevents one change from being lost. [2]

(a)(i) Rejected: MKT does not exist in DEPARTMENT, so the foreign key would refer to a non-existent record (referential integrity)(1)
(a)(ii) Rejected: employee 101 still refers to FIN, so deleting it would leave an orphan record(1)
(a)(iii) Rejected: EmpID 103 already exists, and primary key values must be unique (entity integrity) (do not accept a referential integrity reason)(1)
(a)(iv) Accepted: no employee record refers to LEG, so no orphan is created (accept/reject with no reason scores 0 in every part)(1)
(b) The first clerk's transaction locks the record (or detects the conflict) so the second clerk cannot write it until the first update is committed(1)
(b) The second clerk then works with / re-reads the updated record, so both changes are kept instead of the second save overwriting the first (lost update avoided) (accept: optimistic control - the second save is rejected because the record changed, and must be redone)(1)
Answer: (a) (i) reject - no MKT department; (ii) reject - employee 101 would be orphaned; (iii) reject - duplicate primary key 103; (iv) accept - no employee in LEG. (b) Locking serialises the two updates so the second works on the committed first change; no lost update.
Examiner tip: Part (iii) is about the primary key, not the foreign key: name the right kind of integrity.

Stuck on this topic?

Book a free 30-minute session on WhatsApp and we'll find exactly which step is costing you the marks. Or build a timed paper on it.

Free 30-minute session