IB Computer Science HL · Year 2 · Lesson 43

Relational databases and database schemas

Explain relational-database strengths and limits, then describe the same data at conceptual, logical and physical schema levels.

Paper 150 minutesA3.1.1 · A3.2.1

Retrieve first, attempt the task, then reveal.

Course home IA hub 2027 case study

Targets

What must you be able to do

2 / 10

explain features: tables, relationships, primary keys, foreign keys and composite keys;
explain benefits: community support, concurrency control, data consistency, data integrity, data retrieval, reduced data duplication, reduced redundancy, reliable transactions, scalability and security;
explain limitations: big-data scalability, design complexity, hierarchical/unstructured data, rigid schema and object-relational impedance mismatch;
describe conceptual, logical and physical schemas.

A3.1.1A3.2.1

Page 2 of 10

Learn + retrieve

Rapid Recall Deck

3 / 10

Attempt each card first. If a term is new, flip and study it, then answer it again without looking. Mark only secure recall as “Know it”.

Recall deck answers

What is a relational database? A database that organizes data into related tables whose rows are records and columns are attributes.
What is a primary key? An attribute or set of attributes that uniquely identifies each record.
What is a foreign key? An attribute that references a key in another table to represent a relationship.
What is a composite key? A key made from multiple attributes whose combined values uniquely identify a record.
What does data integrity mean? Data remains valid, accurate and consistent according to database rules.
What does concurrency control address? Safe coordination when multiple users or transactions access/change data at the same time.
What is a conceptual schema? A high-level description of entities/data and relationships, independent of implementation.
What is a logical schema? The tables, attributes, keys and relationships for the chosen database model.
What is a physical schema? How the logical database is stored/accessed through files, pages, indexes or other structures.
What is one benefit of a relational database? Examples include consistency, integrity, reduced redundancy, reliable transactions, retrieval, concurrency control, scalability, security or mature support.
What is one limitation of a relational database? Examples include rigid schema, design complexity, big-data scalability issues, awkward unstructured/hierarchical data or object-relational mismatch.

Page 3 of 10

A3.2.1

Three schema levels

4 / 10

Describe the progression

Conceptual: Student — enrolls in — Course
Logical: STUDENT / COURSE / ENROLMENT tables
Physical: files/pages + indexes + storage structures

What becomes more specific as you move conceptual → logical → physical?

Conceptual identifies real-world entities/relationships without implementation detail. Logical converts them into relational tables, attributes and keys. Physical describes storage/access details such as files/pages and indexes.

Page 4 of 10

A3.1.1

Keys protect relationships

5 / 10

Composite + foreign keys

ENROLMENT(StudentID, CourseID) links STUDENT and COURSE. Explain why both fields are foreign keys and why the pair can be a composite primary key.

Each field references one parent table. Together the pair can uniquely identify one student-course enrolment, preserving the many-to-many relationship without duplicating student/course facts.

Integrity failure

What problem occurs if ENROLMENT contains a StudentID that does not exist in STUDENT?

The row contains an orphaned/invalid relationship. A foreign-key constraint can prevent it and protect referential/data integrity.

Page 5 of 10

Benefits

Why databases beat disconnected files

6 / 10

Benefit reference: relational database systems can provide community/vendor support, concurrency control, data consistency, data integrity, data retrieval, reduced data duplication/redundancy, reliable transaction processing, scalability and security. The task below applies a subset; the remaining benefits still belong to the examinable scope.

Apply database benefits to hospital problems

A hospital replaces separate departmental spreadsheets with one managed relational database. Explain how the database can address these four problems:
A. two staff members edit related records at the same time;
B. patient details are copied into many files and become inconsistent;
C. staff need reliable searches/transactions over shared data;
D. sensitive records must be available only to authorized users.

For A–D: name the database benefit that addresses the problem.

Mechanism: explain what the database does that helps.

Consequence: link the mechanism back to the hospital problem.

A relational DB can coordinate concurrent access, use constraints/keys for consistent valid data, store shared facts once to reduce duplication/redundancy, support complex retrieval and reliable transactions, centralize security and scale beyond isolated spreadsheets. Mature systems also have strong community/vendor support and tooling.

Page 6 of 10

Limitations

When relational design becomes awkward

7 / 10

Rigid/unstructured/hierarchical

A social platform stores changing nested post data. Explain two relational-model difficulties.

A rigid schema makes frequent structural changes more complex, and irregular/nested/unstructured or deeply hierarchical data may require many tables/joins or transformations instead of mapping naturally to rows and columns.

Scale + object mismatch

Explain big-data scalability issues and object-relational impedance mismatch.

Very large volume/concurrency/query load can require complex scaling/partitioning beyond a simple relational server. Object-relational mismatch occurs when application objects/relationships do not map directly to rows/tables, requiring extra mapping logic.

Page 7 of 10

Transfer

Benefit or limitation?

8 / 10

Choose from context

A small school needs consistent student records, secure access and concurrent staff use. A social platform instead stores rapidly changing irregular documents at huge scale. Explain why relational storage is a stronger default fit for one than the other.

The school benefits directly from stable schema, keys/relationships, integrity, security and concurrency. The social platform may face rigid-schema, unstructured-data and horizontal-scale challenges, so an alternative model may be worth considering.

Page 8 of 10

Exam lens

Specimen-calibrated mini-set

9 / 10

Paper 1 practice

Answer: (1) explain one way a relational database provides integrity; (2) explain one way it reduces redundancy; (3) distinguish conceptual and physical schemas.

Keys/constraints prevent invalid/orphan relationships.
Facts are stored once in appropriate tables and referenced by keys rather than repeated.
Conceptual = high-level entities/relationships; physical = implementation/storage/access structures.

Page 9 of 10

Homework

Finish the learning cycle

10 / 10

IA — main task

Continue Criterion E/final-submission work. Tie evaluation statements directly to success criteria and observed evidence.

Task complete

Paper 1 — short practice

Recall every relational-database feature, benefit and limitation, then explain the three schema levels.

Practice complete

Designed by David Xu

Educators and students are welcome to use these materials for non-commercial teaching and learning with attribution. Please share the original link when possible. Reposting, redistributing modified copies, removing attribution, or commercial use requires prior permission.

End of Lesson 43.