The big idea: A schema is a description of how the data is structured.
The same database is described three times, at three levels of detail — because three different audiences need three different amounts.
Think of a building: A sketch of the rooms, a technical drawing with dimensions, and the builder's specification of materials.
All three describe one building. None is wrong; each is aimed at someone different.
Free preview
This is the free notes preview
You're reading the free notes. Aimnova Pro unlocks the full study experience — and you can try it with your first topic free to keep:
- FlashcardsLock in vocabulary and key terms with spaced repetition.
- Practice questionsAnswer exam-style questions and get instant AI marking.
- Mock exams & past-paper vaultSit full mocks and see exactly how examiners award marks.
- Personalised study planA daily plan built around your exam date and weak areas.
Conceptual schema — what the data is
The things the organisation cares about and how they relate: students, clubs, teachers.
No keys, no data types, no software. It is written with the people who run the organisation, and it should still make sense to them.
Logical schema — how it is organised
The conceptual model turned into tables, columns, keys and relationships.
Data types appear here, and so do primary and foreign keys. Still independent of which database system you will use.
Physical schema — how it is stored
How the chosen system actually stores it: files on disk, indexes, partitions, storage settings.
This is where performance decisions live, and it changes if you move to a different database system.
What changes between them: Going down the levels, each step adds detail and ties you to a decision.
Conceptual survives any technology. Logical survives any particular product. Physical is specific to what you actually installed.
Learn what examiners really want
See exactly what to write to score full marks. Our AI shows you model answers and the key phrases examiners look for.
What the separation buys
- You can agree the meaning with non-technical people before any technical choices
- Changing how data is stored does not change how programs query it
- The same logical design can be built on different database systems
- Performance can be tuned at the physical level without redesigning anything above
What happens without it
- Technical detail is decided before anyone agrees what the data means
- A storage change breaks every program that reads the database
- The design is welded to one product from the start
- Tuning performance means redesigning the tables
The phrase for this: Data independence — the levels are insulated from each other, so a change at one does not force a change at another.
That is the whole reason three schemas exist rather than one.
How this is tested — you must say what each level contains and what it deliberately leaves out. It comes up two ways:
Paper 1 Section A
- Describe the three schemas, 3-4 marks
- Distinguish two of them
- Say which level a stated decision belongs to
Paper 1 Section B — case study
- Identify the levels in the case study's design
- Say which level a proposed change affects
The classic trap: Describing the levels as "more and more detailed" without saying what detail. The marks come from naming the contents: entities and relationships, then tables and keys, then files and indexes.
Describe the three levels of database schema, using a school's club membership system as your example.
Model answer plan
See the answer broken into clear points, with guidance on how each point earns marks.