Part 4 of 5 · 2 min read
Physical data independence
Why a storage change should not force the conceptual level, or anything above it, to change.
Physical data independence
Think of a library moving every book to a new shelving system overnight, while every catalog card still lists the same title, author and call number as before.
In plain words: physical data independence is the ability to change the internal level without changing the conceptual schema or any query written against it. The internal level covers things like adding an index, changing a file's layout, or moving data to new storage. A query such as SELECT * FROM customers WHERE customer_id = 107 keeps working. It keeps meaning the same thing. This holds no matter how the DBMS physically finds that row underneath.
A worked example. Adding an index on orders.customer_id to speed up a common query changes only the internal level. The orders table's columns, the orders schema, and every query written against it stay exactly the same; only how fast the query runs, not what it returns, changes.
The key lines.
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- every existing query against orders still runs, unchanged, just faster
Physical data independence
Physical data independence is the property that internal-level changes do not require changing the conceptual schema or the queries written against it. Adding an index or changing file storage are typical examples of such a change.
Example: adding idx_orders_customer changes nothing about how orders is queried; it only changes how the DBMS finds the matching rows internally.
For the curious Which independence is harder to get
Physical data independence is comparatively easy for a mature DBMS to provide, since storage details were always meant to be hidden. Logical data independence is harder. A conceptual-level change can genuinely break a view that depended on the exact shape being changed. Level 2's module on views covers where that break can happen.
Quick notes
- The internal level is physical storage; the conceptual level is the logical schema; the external level is a user- or application-specific view.
- Logical data independence: conceptual-level changes do not force unaffected external-level applications to change.
- Physical data independence: internal-level changes do not force the conceptual level or its queries to change.
- A view is the practical tool that provides logical data independence in a real SQL database.
- An index is a common example of a change that only affects the internal level.