Logical and Physical Data Independence
The reason a database survives ten years while applications are rewritten is that it can change at one level without breaking the others. This lesson explains the two kinds of independence and how they are actually achieved.
Beginner
Learning objectives
- Describe the three levels of data description and what each one contains
- Define logical and physical data independence precisely
- Explain which mechanism provides each kind of independence
- Recognise the schema changes that are safe and those that break applications
- Explain why independence is a design obligation, not a DBMS feature
Prerequisites
- Introduction to Databases
- Database Management Systems: Definition and Functions
Introduction
A library installs a database in 2015. In 2018 the loans table is split in two. In 2021 the storage engine changes from MyISAM to InnoDB. In 2024 a column is renamed. Through all of this, the desk application written in 2015 keeps working — with only the 2024 rename requiring a coordinated release.
This is not luck. It is the most economically important property of database technology, and it has a name: data independence. The idea is that a database is described at more than one level, and each level can change without forcing a change in the others.
The three levels of description
The three levels
- The physical level (internal level) describes how the data are stored: files, pages, indexes, compression, partitioning, encryption.
- The logical level (conceptual level) describes what data are stored: entities, attributes, types, keys, constraints, relationships — independently of any storage decision.
- The external level (view level) describes what each group of users sees: a subset of the logical schema, possibly reorganised, possibly with derived values.
A single library database, described at the three levels:
| Level | Description | Example |
|---|---|---|
| Physical | Storage structures | prestito stored in a clustered B+ tree on (socio, dataInizio); secondary index on copia; 16 KB pages; InnoDB |
| Logical | The complete model | socio(codice, cognome, nome, ...), prestito(id, socio, copia, dataInizio, dataScadenza, dataRestituzione) with keys and constraints |
| External | Per-role views | The desk sees v_prestiti_banco (no fiscal code); the director sees v_statistiche_mensili; the member sees v_miei_prestiti |
external level v_banco v_direzione v_socio ← one per user group
\ | /
\ | /
logical level ───── the complete conceptual schema ─────
|
physical level ───── files, pages, indexes, engine ─────
Logical data independence
Logical data independence
Logical data independence is the ability to change the logical schema — add a table, add a column, split a table, introduce a relationship — without changing the applications that use the database, provided they access the data through the external level.
Concretely: the library decides to record the member's e-mail address. It runs ALTER TABLE socio ADD COLUMN email VARCHAR(120). The desk application, which selects codice, cognome, nome, telefono, is unaffected: it never asked for email, and SQL's SELECT names its columns.
This works because of a property that is easy to overlook: adding is safe, removing is not.
| Change to the logical schema | Effect on existing applications |
|---|---|
| Add a nullable column | Safe — nobody asked for it |
Add a column with a DEFAULT |
Safe — existing inserts still work |
| Add a new table | Safe |
| Add a new index | Safe (physical) |
| Rename a column | Breaks every SELECT that names it |
| Remove a column | Breaks every SELECT that names it |
| Change a column's type | Breaks if the new type is incompatible |
| Split a table in two | Breaks unless a view preserves the old shape |
Add a NOT NULL without default |
Breaks existing inserts |
The last three are where logical independence is achieved rather than merely hoped for: the answer is a view.
Views as the mechanism of logical independence
Suppose the library splits socio into socio (identity and contact) and socio_abbonamento (subscription history). Every application that did SELECT codice, cognome, abbonamento FROM socio now fails.
The disciplined solution: before the split, create a view with the old shape, and point the applications at it.
-- before the split, applications use this view
CREATE VIEW v_socio AS
SELECT codice, cognome, nome, telefono, abbonamento, dataIscrizione
FROM socio;
-- after the split, the view is redefined; applications are untouched
CREATE OR REPLACE VIEW v_socio AS
SELECT s.codice, s.cognome, s.nome, s.telefono,
a.tipo AS abbonamento, s.dataIscrizione
FROM socio s
LEFT JOIN socio_abbonamento a ON a.socio = s.codice AND a.corrente = 1;
The applications never noticed. This is logical independence in action: the external schema stayed stable while the logical schema changed underneath it.
Physical data independence
Physical data independence
Physical data independence is the ability to change the physical schema — indexes, storage engine, file organisation, partitioning, compression — without changing the logical schema or the applications.
Concretely: the report on loans by copy is slow. The DBA creates an index:
CREATE INDEX idx_prestito_copia ON prestito (copia);
No application changes. No SQL changes. The query text is identical; the plan changes. That is physical independence: the logical schema is the same, the applications are the same, only the storage decisions changed.
| Physical change | Visible to applications? |
|---|---|
| Create or drop a secondary index | No |
| Change the storage engine (MyISAM → InnoDB) | No, if the features used are the same |
| Partition a large table | No |
| Compress a table | No |
| Move a table to another tablespace | No |
| Change the page size | No |
| Change the cluster key | No (but it may change performance dramatically) |
Physical independence is nearly absolute in relational DBMSs, and it is why the same SQL runs on a laptop and on a cluster. The exceptions are performance, not correctness: a physical change can make a query a hundred times faster or slower, and the application will not know why.
How the DBMS provides independence
Independence is not automatic; it is implemented by two mappings that the DBMS maintains.
external schema(s) ──[ external/logical mapping ]──▶ logical schema
logical schema ──[ logical/physical mapping ]──▶ physical schema
- The external/logical mapping translates a view definition into a query over the logical schema. It is what lets the logical schema change while views stay stable.
- The logical/physical mapping translates tables and columns into files, pages and index accesses. It is what lets storage change while the logical schema stays stable.
Both mappings live in the catalogue. When you write SELECT * FROM v_socio, the query processor expands the view through the first mapping, then plans access through the second. You never see either.
| Kind of independence | Changes at this level | Does not affect | Provided by |
|---|---|---|---|
| Logical | Logical schema | External schema and applications | Views + external/logical mapping |
| Physical | Physical schema | Logical schema and applications | Storage engine + logical/physical mapping |
Where independence breaks down
Honest engineering requires naming the limits.
1. Applications that bypass the external level
An application with a SELECT * is coupled to the shape of the table: adding a column changes its result set and can break its deserialisation. An application that queries base tables instead of views is coupled to the logical schema. Independence is a discipline the application must respect.
2. ORMs that mirror the schema
An object-relational mapper generates classes from tables. Add a NOT NULL column without a default and the generated class requires it; rename a column and the mapping breaks. The ORM reintroduces exactly the coupling that data independence removes — unless the mapping is maintained deliberately.
3. Semantic change
Adding a column is structurally safe. Changing what a column means — from "current subscription" to "subscription at the time of the loan" — is never safe, whatever the views say. Structural independence cannot protect against a change in meaning.
4. Constraints that reject existing data
ALTER TABLE ... ADD CONSTRAINT ... NOT NULL fails if nulls exist. ADD FOREIGN KEY fails if orphans exist. These are not failures of independence but of data quality: the DBMS is telling you that the current state does not satisfy the new rule. The fix is a data migration, not a schema trick.
5. Performance coupling
Physical independence guarantees that a physical change will not break correctness. It does not guarantee that it will not break a service level. An index dropped by mistake turns a 5 ms query into a 40-second one; nothing fails, and everything is on fire.
A worked example
The library's prestito table has grown to 4 million rows. Three things must happen in the same year: an audit requires that the member's fiscal code never be visible to desk operators; the loans report becomes too slow; and the schema must record which staff member registered each loan.
Step 1 — external level (security). Create a view without the sensitive column, and grant the desk role access to the view only:
CREATE VIEW v_prestito_banco AS
SELECT p.id, p.socio, p.copia, p.dataInizio, p.dataScadenza, p.dataRestituzione
FROM prestito p;
REVOKE ALL ON prestito FROM 'desk'@'%';
GRANT SELECT, INSERT, UPDATE ON v_prestito_banco TO 'desk'@'%';
The desk application changes its FROM clause once. Afterwards, the underlying table may gain columns freely.
Step 2 — physical level (performance). Add the index the report needs:
CREATE INDEX idx_prestito_dataInizio ON prestito (dataInizio, socio);
No logical change, no application change.
Step 3 — logical level (new fact). Add the column, with a default so that existing inserts keep working:
ALTER TABLE prestito ADD COLUMN registratoDa VARCHAR(40) NULL;
Existing applications are unaffected: they do not name the column, and it accepts NULL. The view v_prestito_banco does not expose it, so the desk cannot see it until the application is ready.
Three changes at three levels, and exactly one line of application code changed — the one that had to, because the desk genuinely needed to see something new.
Classify the change
For each change below, state (a) which level it belongs to, (b) whether it is safe for existing applications that access the data through views and name their columns, and (c) which mechanism restores or preserves independence when it is not safe.
CREATE INDEX idx_socio_cognome ON socio (cognome);ALTER TABLE socio ADD COLUMN email VARCHAR(120) NULL;ALTER TABLE socio RENAME COLUMN telefono TO cellulare;- Splitting
prestitointoprestitoandprestito_storico. - Changing the storage engine from MyISAM to InnoDB.
ALTER TABLE socio ADD CONSTRAINT uq_email UNIQUE (email);when three members share an e-mail.ALTER TABLE prestito ADD COLUMN registratoDa VARCHAR(40) NOT NULL;- Partitioning
prestitoby year ofdataInizio. - Redefining
v_socioso thatabbonamentonow returns the subscription at the time of the loan instead of the current one. - Dropping an index that a nightly report relied on.
Show the commented solution
1. Physical. Safe. Nothing logical changed; only the plan may change (for the better).
2. Logical. Safe: the column is nullable and no application names it. This is the canonical example of logical independence.
3. Logical. Not safe — every SELECT naming telefono fails. Mechanism: create a view v_socio exposing telefono AS cellulare before the rename, point applications at the view, then rename the column and redefine the view. Note the ordering: the view must exist before the change, otherwise there is a window in which applications break.
4. Logical. Not safe in general, because a row that used to be one row is now two. Mechanism: a view joining the two tables to reproduce the old shape. But see point 9 — if the split changes the cardinality seen by the application, a view can hide the structure and not the semantics.
5. Physical. Safe, provided the features used exist in the new engine (InnoDB supports transactions and foreign keys, MyISAM does not — so the logical schema's constraints may be affected). This is a good example of a change that is physically independent but may reveal a logical gap.
6. Logical. Not safe — the statement will fail, because the current data violate the constraint. The mechanism is not a view but a data migration: reconcile the duplicates (which are three members sharing one mailbox, or a data-entry error?) and only then add the constraint. The DBMS is correctly refusing to accept a rule that the data do not satisfy.
7. Logical. Not safe: existing INSERTs that do not name registratoDa will fail. Two safe paths: add it nullable, backfill, then add the NOT NULL; or add it with a DEFAULT. Both are the standard three-step migration pattern.
8. Physical. Safe. Partitioning is a storage decision; queries are unchanged, and the optimiser may use partition pruning.
9. This is the interesting one: it is not a structural change at all, it is a semantic change. Structurally, the view still returns one row per member with a column called abbonamento. An application compiled against it will keep running and will silently start answering a different question. No level of data independence protects against this, because the change is in what the data mean. It requires a coordinated release, a new column name, or both. Structural independence is not semantic independence.
10. Physical. Structurally safe, operationally dangerous: correctness is preserved, performance is not. This is why physical independence is described as protecting correctness, not service levels.
What this exercise teaches. Independence is not a switch. It is a set of habits: name your columns, read through views, change schema in additive steps, and treat changes of meaning as the separate, dangerous category they are.
Common mistakes
- "
SELECT *is convenient." It is the most reliable way to destroy logical independence. The result-set shape becomes an implicit contract. - "Views slow everything down." A view is expanded into the query and optimised together with it. A simple view over one table usually costs nothing. A view with heavy joins and aggregates costs what the joins cost — which is the real query's cost anyway.
- "Physical independence means I don't need to think about indexes." It means a wrong index choice does not corrupt data. It means a wrong index choice makes the system unusable at 2 a.m.
- "If it compiles, the change is safe." Semantic changes compile perfectly and break the business.
- "I'll rename the column and fix the code later." Independence requires the view to exist before the change; "later" is the outage.
- "The DBMS gives me independence automatically." It gives you the mappings. You must use views, name columns, and change additively.
Summary
- A database is described at three levels: physical (how stored), logical (what stored), external (what each user sees).
- Logical data independence — the logical schema changes without affecting applications — is provided by views and the external/logical mapping.
- Physical data independence — the physical schema changes without affecting the logical schema or applications — is provided by the storage engine and the logical/physical mapping.
- Adding is safe; removing, renaming and tightening are not. The safe migration pattern is additive: add nullable, backfill, then constrain.
- Independence protects correctness, not performance and not meaning. Semantic changes and index mistakes are outside its scope.
- Independence is a discipline: named columns, access through views, additive migrations.
Verification questions
- Which level holds the actual data, and what do the other two levels hold?
- Give one change that is logically independent and one that is not, and explain the difference in terms of what an application depends on.
- What exactly does a view protect, and what does it not protect?
- Why does
ALTER TABLE socio ADD COLUMN email VARCHAR(120) NOT NULL;break applications while... NULL;does not? - A query becomes slow after a year with no code change and no schema change. Is this a failure of physical data independence? Explain.
Show the answers
-
Only the physical level holds data. The logical level holds the description of the tables and constraints; the external level holds the definitions of the views. Both are metadata in the catalogue.
-
Logically independent: adding a nullable column — applications that name their columns never reference it, so nothing they depend on changed. Not independent: renaming a column — applications depend on the name, which is part of the external contract. The criterion is: does the change alter anything the application explicitly mentions?
-
A view protects applications against changes to the structure of the tables beneath it, and can restrict which columns and rows a role sees. It does not protect against changes in meaning, does not protect applications that bypass it, and is often read-only when it contains joins or aggregates, so writes may not be possible through it.
-
Because existing
INSERTstatements that do not nameemailwould insertNULL, whichNOT NULLforbids — every insert fails. WithNULLallowed, the same statements succeed and the new column stays empty until the application starts filling it. This is the additive-migration principle. -
No — it is a consequence of physical independence working as designed. Nothing broke; the optimiser simply chose a different plan because the data volume and statistics changed. Physical independence guarantees that physical changes do not break correctness, not that performance is invariant. The fix is a physical one: examine the plan with
EXPLAINand add or adjust indexes (Module 7).
Where to go next
- The ANSI/SPARC Three-Level Architecture: the standard formalisation of these three levels.
- Relational and Non-Relational Databases: which families preserve these guarantees and which trade them away.
- The Phases of Database Design: where each level is produced in the design process.
- Database Management Systems: the components that maintain the mappings.