A conceptual schema describes what data an organization needs, a logical schema defines how that data is organized, and a physical schema specifies how it is implemented and stored. Together, they move from requirements to technical implementation.
The Three Schema Levels
In IB Computer Science topic A3.2 Database design, each schema serves a different purpose and audience.
| Schema | Purpose and detail | School database example |
|---|---|---|
| Conceptual schema | Provides a high-level, technology-independent view of required entities and relationships; it excludes data types and storage methods. | STUDENT, TEACHER, and CLASS, with students enrolling in classes. |
| Logical schema | Converts the conceptual design into a data model. It defines attributes, tables, primary keys, foreign keys, relationships, and constraints, usually independently of a particular database platform. | STUDENT uses StudentID as its primary key; ENROLMENT contains StudentID and ClassID as foreign keys. |
| Physical schema | Defines implementation in a specific database management system (DBMS), including data types, indexes, storage structures, access methods, and performance decisions. | StudentID is an integer; an index is created on StudentSurname. |
The process moves from what data is needed, to how it is logically structured, and finally to how it is stored and accessed. Separating these levels supports data independence: physical storage can change, for example by adding an index, without redesigning the conceptual model.
A common misconception is that a schema is the actual data. A schema is the database’s structure or blueprint; the current records form the database instance.
Exam Technique
For the command term distinguish, state one clear difference among all three levels and apply each to one consistent example. Do not call the physical schema merely “more detailed”; identify DBMS-specific features such as data types, indexes, and storage decisions. Examiners reward precise terminology and a progression from requirements to implementation. Make this sequence explicit in answers.
