Distinguish physical data independence from logical data independence, give a concrete example of each, and explain why logical independence is much harder for a database system to deliver.
answer
- physical: storage → logical schema unaffected
- logical: logical schema → application views unaffected
- declarative SQL + optimiser = free physical independence
- views are the only logical insulation, and only if derivable
- cardinality change and dropped columns are not derivable; writes break first
basics
~20 sPhysical independence: storage changes such as adding an index or repartitioning leave the logical schema and all queries untouched. Logical independence: logical schema changes leave application views untouched. Logical is harder because applications depend on the logical schema directly, and views cannot always reconstruct what was removed or restructured.
solid answer
~60 s**Physical data independence** insulates the logical schema — and therefore applications — from changes to storage. Add or drop an index, change the row layout, compress a table, repartition it, move it to different storage: the tables, columns and constraints are unchanged and no query text changes. Engines deliver this well, because queries are declarative — they name relations and predicates, never access paths, and the optimiser chooses the path at planning time. **Logical data independence** insulates applications from changes to the logical schema. Split one table into two, rename a column, change a one-to-one into a one-to-many, and the application's view still presents what it always did. The tool is a view layer. Logical is harder for three reasons. Applications sit **directly against the logical level**, with only views as insulation. Views absorb only changes that remain **derivable** — if a split now allows many child rows, no view can reconstruct the old single-valued column. And **writes** are the sticking point: only a narrow class of views is updatable, so write paths break even when reads survive.
go deeper
Get the two definitions the right way round and give one example each: adding an index versus splitting a table behind a view.
Explain why declarative SQL plus an optimiser gives physical independence for free, and name a non-derivable logical change.
Cover the performance caveat, ordering assumptions breaking on physical change, and the read-versus-write asymmetry of view-based insulation.
Set policy by level of change: which changes are operational, which need a compatibility layer and deprecation window, and who owns the conceptual schema as a shared contract.
## The two independences Both ideas come from the ANSI/SPARC three-level model — internal (storage), conceptual (logical schema), external (per-application views) — and each independence is about one boundary between levels. **Physical data independence** is immunity of the conceptual level to internal-level change. The logical schema, and every query written against it, survive changes to how bytes are stored. **Logical data independence** is immunity of the external level to conceptual-level change. Application views survive changes to the logical schema itself. ## Physical independence: what it covers, and why it works Concrete changes it absorbs: creating or dropping an index; changing index type; clustering a table on a different order; enabling compression; converting a table to a partitioned one; moving a table to different storage; updating statistics; changing fill factors and page sizes. It works because SQL is **declarative**. A query names tables, predicates and the shape of the result; it never names an access path. The optimiser reads the current internal schema and picks a plan when the statement is planned. Change the internal schema and the next plan simply differs. This is why a DBA can tune a live production system without a code release, and it is by far the strongest independence real engines provide. Its limit is cost, not correctness. Results stay identical; **performance does not**. Dropping the index a report depends on turns a fast index lookup into a full scan — same answer, unusable latency. Physical independence guarantees the query still means the same thing, never that it still runs in the same time. Some behavioural leaks also exist: rows may come back in a different order when nothing pinned the order, so code that relied on incidental ordering breaks. That is a bug in the application — it was depending on physical placement — but it is a real failure mode of a purely physical change. ## Logical independence: why it is harder Three structural reasons. **1. Applications live at the logical level.** Queries name tables and columns from the conceptual schema. There is no optimiser equivalent that automatically re-derives an application's expectations after a logical change — the only insulation is a view layer someone must build and maintain, and most systems query base tables directly. **2. Some changes are not derivable.** A view can reproduce the old shape only if the information is still there and still functionally equivalent. Adding a column, renaming one, or splitting a table vertically into one-to-one halves are all absorbable: a view rejoins or aliases and the old shape returns. But if you split a single-valued attribute into a one-to-many child table — one phone number becomes many — no view can honestly reproduce the old single column; picking one row is a lie the application will eventually notice. Dropping a column outright, or narrowing a domain, is likewise irreversible. **3. Writes break before reads do.** Even when a view reproduces the old read shape perfectly, writing through it works only for a narrow class of views — essentially single-table, key-preserving projections and restrictions. Any view that joins, aggregates, or de-duplicates needs an explicit translation supplied by a developer. So the read half of the contract survives a restructuring while the write half needs hand-written handling. A fourth, practical reason: performance again. Even a derivable view may be far slower than the original table access, so the insulation exists but the system regresses. ## How the difference shows up in planning A useful test when reviewing a change: which level are you touching? - **Internal only** (index, partitioning, storage) — an operational change. Verify plans and latency, no application coordination. - **Conceptual, additive** (new nullable column, new table) — usually absorbable; existing consumers unaffected if they do not select every column blindly. - **Conceptual, restructuring** (split, rename, cardinality change) — requires a compatibility view for reads, an explicit plan for writes, and a deprecation window while consumers migrate. This is where logical independence is bought manually, not received for free. The honest summary: physical independence is a property the engine gives you; logical independence is a property you engineer, partially, at real cost, and only for changes that remain derivable.
- Name a logical schema change that no view can hide from applications, and say why.Turning a single-valued attribute into a one-to-many relationship — one phone number per customer becoming a child table of phone numbers. A view can pick one row, but that is not the same information: the old column promised at most one value and that guarantee is gone. Anything that drops a column or narrows a domain is likewise irreversible, because the information no longer exists to be re-derived.
- If physical data independence guarantees results are unchanged, how can dropping an index break production?It cannot change the answer, only the cost. The optimiser replans without the index, and what was an index lookup becomes a scan, so latency and resource use can rise by orders of magnitude. Correctness independence and performance independence are different things, and only the first is guaranteed.
Physical independence is rerouting the plumbing behind the wall — the tap still works. Logical independence is moving the bathroom and expecting people to still find it by the old directions.
saying these in an interview costs you the question
- Swapping the definitions — physical independence is about storage changes, logical about schema changes.
- Claiming physical independence means performance is unaffected; only results are guaranteed unchanged.
- Believing a view layer can absorb any logical change, including cardinality changes and dropped columns.
- Forgetting that write paths break long before read paths when a schema is restructured behind views.