Learn
A3.2.1 | DATABASE SCHEMAS
01 | THREE QUESTIONS ABOUT THE SAME DATABASE
A school wants a database for students and clubs. Before building it, different people ask different questions. A teacher asks what the school needs to record. A designer asks which attributes and relationships represent those facts. An implementer asks how a chosen DBMS will store and access them.
A database schema describes the structure and organisation of data and its relevant rules. This lesson distinguishes conceptual, logical and physical schemas by their level of abstraction. They are views of the same intended system, not three unrelated databases.
Abstraction means concentrating on relevant details while leaving other details aside. A high-level plan can discuss student memberships without deciding an index type or storage setting.
We will keep one club example throughout so you can see what is added as the description becomes more detailed.
02 | SCHEMA IS THE STRUCTURE; DATA IS THE CURRENT CONTENT
A schema might define a Students table with a StudentID identifier, StudentName and Email. The current rows might include S01, Maya and a fictional email address. Those values are the database’s current state or instance.
Adding a new student using the same columns changes the data, not necessarily the schema. Adding a new DateOfBirth attribute changes the structure and therefore requires a schema change.
A schema does not normally list every actual student who will ever exist. It defines how permitted student records are represented. The same schema can describe an empty database and a database containing thousands of rows.
The schema can also specify constraints, such as a unique non-null primary key. Changing one email value is different from changing the rule that every student must have an email.
| Description | Structure or current data? |
|---|---|
| StudentID identifies a student | Schema rule |
| StudentName is text | Schema attribute/type requirement |
| S01 is named Maya | Current data |
| There are three student records today | Current state |
| Enrolments references existing students | Schema relationship/constraint |
03 | SEE THE THREE LEVELS TOGETHER
CONCEPTUAL
WHAT DOES THE DOMAIN NEED TO REPRESENT?Students participate in clubs; a membership has a role.
High-level meaning and business rules.
LOGICAL
HOW ARE THOSE FACTS STRUCTURED?Student, Club and Enrolment attributes, identifiers and relationships.
Detailed organisation without committing to a product’s storage implementation.
PHYSICAL
HOW WILL THE CHOSEN SYSTEM IMPLEMENT IT?Specific types, constraints, indexes and relevant storage choices.
DBMS-specific implementation and access details.
The levels become more implementation-specific as you move down. They should remain consistent: a physical implementation must not silently contradict the intended logical relationship or conceptual business rule.
Modelling conventions vary in how much detail appears at each stage. For this course, use the main distinction consistently: domain meaning, detailed data organisation, and implementation/storage. A key can appear in a logical design and then be enforced physically; concepts need not appear at only one stage.
04 | CONCEPTUAL SCHEMA: THE DOMAIN STORY
A conceptual schema presents the important kinds of things and their relationships at a high level. An entity is a kind of thing about which the system records data, such as Student or Club. A relationship describes an association, such as a student participating in a club.
For our school, a student can join several clubs and a club can have several students. A membership records a student’s role in that club. These statements describe the information need before choosing SQL syntax or a DBMS product.
Conceptual discussion is useful with teachers and other stakeholders because they can check whether the model represents their work. A club can exist before anyone joins it; a student can exist without a membership. Those rules affect later design.
Do not fill the first conversation with disk page sizes or vendor data types. The point is to agree what the data means and which real-world associations matter.
05 | A CONCEPTUAL PICTURE OF CLUB MEMBERSHIP
A person the school needs to record
Connects a student and a club
Has a membership role
An activity that students can join
One student can have many memberships; one club can have many memberships. Each membership belongs to one student and one club. This is a high-level association picture, not a complete ERD with a particular cardinality notation.
The rule “at most one membership per student per club in this example” is a domain requirement. It helps determine a later identifier. If the school instead needs separate memberships every term, that requirement must be stated before the design is finalised.
An entity is not the same as one row: Student is the kind of thing, while Maya S01 is an individual instance.
06 | LOGICAL SCHEMA: MAKE THE ORGANISATION PRECISE
A logical schema develops the conceptual ideas into a detailed data structure. In a relational design, it identifies tables or relations, attributes, keys, relationships and relevant constraints in a way that is not tied to a particular product’s storage implementation.
StudentID identifies a student; ClubID identifies a club. Enrolment records the association. Instead of putting a changing list of club names in each Student row, a linking relation stores the memberships separately.
Logical type requirements can say “identifier text”, “name text” or “date”. A physical design then maps these requirements to supported DBMS types and their parameters. This is not a universal rule that no type information can appear until the last stage.
A logical description should be specific enough to reveal ambiguity. Which facts describe the student, which describe the club, and which describe the particular membership?
07 | THE LOGICAL CLUB SCHEMA
| Relation | Attributes and key rules |
|---|---|
| Student | StudentID (PK), StudentName, Email |
| Club | ClubID (PK), ClubName, Room |
| Enrolment | StudentID (PK part, FK), ClubID (PK part, FK), Role |
StudentID and ClubID together form the Enrolment primary key under the one-membership-per-pair rule. StudentID alone can repeat because one student can join more than one club. ClubID alone can repeat because one club can have more than one student.
The Enrolment foreign keys reference the appropriate identifiers in Student and Club. With enforced non-null key/reference rules, a membership must identify existing parents. These are logical requirements that the physical implementation must enforce.
This is one composite primary key with two parts, not two independent primary keys. A relationship’s meaning and its key implementation are related details at different abstraction levels.
08 | PHYSICAL SCHEMA: IMPLEMENT THE DESIGN
A physical schema describes how the logical organisation is implemented in a specific database environment. It can include concrete tables/columns, supported types and lengths, declared constraints, indexes and appropriate storage or partitioning choices.
For example, a chosen SQL system might implement StudentID as VARCHAR(8), StudentName as VARCHAR(80), and the Enrolment identifier as a declared two-column primary key. These are implementation choices for the requirements, not a claim that all identifiers in every system must be eight characters.
An index is an access structure that can speed suitable queries at a storage and update cost. A physical design might add one to support finding all memberships for a club. Whether it helps depends on existing indexes, data and workload.
Physical schema does not simply mean a photograph of the server, its room or a hardware shopping list. Its focus is the database’s implemented representation and relevant storage/access structures.
09 | LOGICAL REQUIREMENT TO PHYSICAL CHOICE
| Logical requirement | Illustrative physical implementation | Reason to check the choice |
|---|---|---|
| A student has an identifier | StudentID VARCHAR(8) PRIMARY KEY | Length and type must accommodate valid identifiers |
| A student has a name | StudentName VARCHAR(80) NOT NULL | Maximum length and mandatory status must match requirements |
| An enrolment identifies one student and club | PRIMARY KEY (StudentID, ClubID) with matching foreign keys | Enforces the stated association rules |
| Queries frequently list a club’s members | An appropriate index beginning with ClubID, if needed | Avoid redundant indexes; measure relevant queries |
| Data grows enough to justify storage planning | Suitable supported partition/storage arrangements | Need depends on volume, access and operational constraints |
The type names and constraints illustrate a possible SQL implementation. They are not instructions to copy blindly into every DBMS. Physical design needs compatibility and testing.
A logical primary key is still the same identification requirement when implemented as a database constraint. It has not become a different business rule merely because SQL now enforces it.
10 | SWITCH BETWEEN VIEWS OF THE SAME SYSTEM
Use the selector to change the detail shown for the club system. The underlying information need stays the same. Notice what each view emphasises and what it deliberately leaves out.
This is an explanatory model, not a schema editor. It does not create a database or execute SQL.
11 | ONE REQUIREMENT CAN AFFECT ALL THREE LEVELS
The school changes its rule: a student can join the same club in different terms, with a different role each term. At the conceptual level, membership is now tied to a term rather than being a single timeless association.
The logical design may add Term and identify each Enrolment by (StudentID, ClubID, TermID), with suitable references and rules. Other valid designs are possible; the key must identify the intended fact under the new requirements.
The physical implementation must then create suitable columns and constraints, adjust dependent queries and migrate existing memberships. It cannot invent the correct historical term for every old row without evidence or an agreed transformation rule.
The stages are linked, not isolated paperwork. A changed domain rule can propagate downwards, while an implementation problem may prompt reconsideration of a design choice.
12 | SCHEMA CHANGE OR DATA CHANGE?
Changing S01’s email value is a data update if it obeys the existing structure. Adding S04 is a new instance. Adding a required DateOfBirth field changes the schema and may require existing records to be completed under a migration plan.
A migration is a managed change to structure and often existing data and applications. It needs appropriate tests, recovery arrangements and coordination rather than simply editing a diagram and hoping the live system follows.
Adding an access index is primarily a physical/access-design change, though the implementation’s catalog records it too. Changing the permitted membership rule begins at the domain level and affects subsequent structure and enforcement.
Choose a proposed change to see the primary effect. The explanation identifies linked consequences rather than pretending every change belongs exclusively to one isolated level.
13 | WHY SEPARATING LEVELS HELPS
Stakeholders can check the conceptual meaning without needing to know database syntax. Designers can inspect logical organisation and rules before committing to storage-specific details. Implementers can then tune supported structures without mixing every decision into the initial requirements discussion.
This can expose mistakes early. If staff need attendance at individual meetings rather than just membership, the domain model needs that distinction. An index cannot fix a schema that never records the required fact.
Separation also supports appropriate data independence. A suitable physical change, such as an index, can leave the application’s logical data meaning unchanged. This does not mean all schema changes are invisible to every application.
Changes to attributes, rules or interfaces may still require application updates. The benefit is managing dependencies explicitly, not guaranteeing that every layer can change freely without consequences.
14 | SCHEMAS ARE NOT JUST DIAGRAM STYLES
A diagram can document conceptual ideas or detailed logical structure, depending on its content. An ERD is therefore not automatically only conceptual, and a table-shaped drawing is not automatically a complete physical schema.
SQL definitions can implement structural rules, while a data dictionary can document attribute meanings, types and permitted values. The artefact’s purpose and detail determine what it communicates.
Some DBMSs also use the word schema for a named container or namespace holding objects. That product-specific usage is valid, but it is not the whole meaning of the three abstraction levels required here.
Use the course context to answer: conceptual meaning, logical organisation and physical implementation. Avoid confusing this modelling progression with other architecture frameworks that use some of the same words differently.
15 | CHECK THE DESIGN THROUGH A REAL QUESTION
A teacher asks, “Which clubs does S01 attend?” The conceptual model establishes what membership means. The logical schema shows that Student and Club connect through Enrolment. The physical system supplies the actual tables, constraints and supported query access.
The current rows determine the answer today. The schema determines how the question can be expressed and which relationships the system maintains. With the same schema, tomorrow’s result may change because S01 joins another club.
This helps distinguish four statements: “students join clubs” is high-level meaning; “Enrolment has a composite identifier” is detailed logical organisation; “an index supports ClubID lookup” is an implementation/access choice; “S01 attends Robotics today” is current data.
A strong description links the levels without replacing them with the data itself.
16 | CLASSIFY THE PURPOSE OF THE DESCRIPTION
Each situation asks for the most direct level or category. Details can be carried forward into later stages, so classify the emphasis rather than assuming every word is forbidden elsewhere.
17 | COMMON MISCONCEPTIONS
| Claim | More accurate description |
|---|---|
| A schema is the current set of student names | It defines organisation and rules; names are current data |
| Physical schema means the server is in a cupboard | It concerns implemented structures and relevant storage/access details |
| Logical design must already select a DBMS | It can specify relational organisation independently of product storage choices |
| A conceptual schema is too vague to have rules | It can express important domain relationships and business constraints |
| Each key only appears at one level | An identification requirement can be designed logically and enforced physically |
| A schema change never affects applications | Changes may require migration, query/interface updates and testing |
To avoid confusion, ask what question the description answers. What must be represented? How is it organised? How will it be implemented? Or what values exist right now?
18 | DESCRIBE EACH LEVEL IN A CONNECTED ANSWER
For describe, give the main characteristics and use a concrete example. Merely writing “high, middle, low” does not show what the levels contain.
A useful answer is: “A conceptual schema describes the domain’s entities, relationships and rules, such as students joining clubs. A logical schema develops that into attributes, identifiers and relationships, such as Student, Club and Enrolment with a composite membership key. A physical schema implements the design in a particular DBMS using supported types, constraints and relevant indexes or storage choices.”
Add the distinction from data: “The schema defines permitted organisation, whereas today’s rows are the current state. Adding another student normally changes data without changing that structure.”
Use the same example throughout so the difference is visible. The next lesson uses ERDs to express entities and relationships more formally.