A3.2.1 · Database design
Database schemas
By the end you can
Describe the conceptual, logical and physical schemas and what each one shows.
Before anyone builds a database, they design it. The design is the : a blueprint of the entities (tables), their attributes (fields), the relationships between them and the constraints on the data. Like an architect's plan, it shows the structure but not the people living in it: a schema holds no actual data.
Different people need different amounts of detail, so the schema is described at three levels. Use the buttons below to move through them for one database.
The big picture, for everyone: which things the database holds and how they relate. No attributes, no keys.
The three levels#
- Conceptual schema: the big picture. It names the entities and shows how they are related (a team has players; a manager manages a team). No attributes, no keys. It is for everyone, including people who will never touch the database, so the client can check the designer has understood the business.
- Logical schema: adds the attributes of each entity and the primary and foreign keys that create the relationships. It is worked out from the conceptual schema, is not tied to any particular database product, and is used by designers, programmers and administrators.
- Physical schema: how the logical design is built in one real database system: the data types of the attributes, which columns are indexed, how the data is stored and partitioned. It is used by the programmer who sets the database up.
A good schema improves organisation (a clear place for everything), integrity (keys and constraints), security (who can see which table or view), performance (sensible indexes) and scalability (the design can change without breaking the programs that use it).
A diagram of boxes for Customer, Order and Product, joined by lines, shown to the shop owner.
Customer(CustomerID PK, Name, Email, City) and Order(OrderID PK, CustomerID FK, Date).
CustomerID INT PRIMARY KEY AUTO_INCREMENT, Email VARCHAR(100) UNIQUE, an index on City.
0 of 3 matched
Check yourself
Database schemas
Quick questions, made fresh every time and marked here. Do a few until they feel easy.
Exam practice
Paper 1-style questions
4 questions · 13 marks
Write your answer as you would in the exam, then show the mark points and tick each one your answer made. These are our own questions in the style of the exam, not past-paper questions.
1.Describe what a database schema is.
[3]Describe: A detailed account: say what happens, step by step if it is a process.
2.Explain what is contained in a logical schema.
[3]Explain: Give reasons: say what happens AND why, or how one thing causes another.
3.Distinguish between the conceptual, logical and physical schemas.
[3]Distinguish: Make the differences between two things clear.
4.Outline why a database designer draws a schema before building the database.
[4]Outline: A brief account: the main points, without much detail.
Past papers
Old exam questions on this topic
From HL Paper 1, May 2014 to November 2025. These papers were set on the previous syllabus, so the wording may differ from today's exam, but the topic is the same. Find the questions and markschemes in your school's past-paper pack.
| Paper | Question | Marks | About |
|---|---|---|---|
| May 2025 TZ2 | 2(a) | 2 | Paper 2 Option A: database schemas (same in TZ3) |
| May 2025 TZ2 | 2(b) | 2 | Paper 2 Option A: database schemas (same in TZ3) |
| November 2018 | 2(b) | 3 | Paper 2 Option A: database schemas |
| November 2016 | 2(a) | 6 | Paper 2 Option A: database schemas |
| November 2015 | 3(a) | 4 | Paper 2 Option A: database schemas |
| May 2014 | 3(f) | 3 | Paper 2 Option A: database schemas |
In a nutshell
- A schema is the blueprint of a database: its entities, attributes, relationships and constraints. It holds no data.
- Conceptual schema: entities and relationships only, for everyone. Logical schema: adds attributes and the primary and foreign keys, independent of any product.
- Physical schema: how it is built in one database system, with data types, indexes and storage; used by the programmer who sets it up.
- An entity-relationship diagram (ERD) is the standard way to draw a schema.
- Good schemas improve organisation, integrity, security and performance, and make change safer.