Database schema
Describe database schema
In this lesson
- What a database schema is, and why it is a description of the data rather than the data itself
- The conceptual, logical and physical schemas, and what each one defines
- Who uses each level, and why the levels are kept separate
LearnWhat a schema is
A database schema is the blueprint of a database: an abstract definition of how the data is structured and organised. It describes the entities, their attributes, the relationships between them and the rules (constraints) the data must follow. It contains no actual data.
LabOne library, three schemas
Switch between the levels and notice what each one adds.
Shows: Entities and the relationships between them. No attributes, keys or data types.
Mainly used by: Users, managers and designers agreeing what the database must hold.
- Member — makes (one member, many loans) — Loan
- Book — is lent in (one book, many loans) — Loan
Look at all three levels to complete this activity.
CheckWhich level?
A school is designing a database of students and clubs. Which schema level does each piece of the design belong to?
- Students join clubs; each club has one teacher in charge
- Club has the attributes ClubID (primary key), ClubName and TeacherID (foreign key)
- ClubName is VARCHAR(40)
- The Membership table is stored on the school's solid-state drive
LearnWhy keep three levels?
ExamWorked exam answer
Describe a logical schema.
[2]ReviewMistakes examiners see
A schema is not the database: it describes the structure and contains no records.
The logical schema is not the same as the physical one: data types and sizes, indexes and storage belong to the physical schema.
In schema questions, say which level you mean, what it contains and who uses it.
Exam practiceExam-style questions
5 questions · 12 marks. Answer each one first, then mark it yourself against the markscheme. Your answers are saved in this browser only.
A hospital is designing a new database for patients, doctors and appointments. Doctors and receptionists are on the project team, together with the hospital's developers and database administrator.
Describe the conceptual schema for the hospital's database, and how the doctors and receptionists on the team would use it.
Construct the logical schema for the hospital's Appointment entity, in the form Entity(attribute, …), identifying the primary key and any foreign keys.
Explain why the developers complete the logical schema before the physical schema.
After the database goes live, the administrator adds an index on the appointment date to speed up searches. Explain which schema changes and why the appointment-booking programs do not need to change.
The hospital decides to record an emergency contact phone number for every patient. Outline how this affects the three schemas.
0 of 5 questions marked. Mark every question to see your total.
Exam-style questions written for Praxis IB in the format of the 2027 specimen papers. They are not IB past-paper questions.