Three-Schema Architecture

Internal (physical) schema

How the data is stored: file organisation, page size, record layout, which indexes exist, compression. There is one of these, and it is the only level where anything physically exists.

Conceptual (logical) schema

The whole database described once, in terms of entities, attributes, relationships and constraints — with no mention of files, pages or indexes anywhere. There is one of these too, and it is what people usually mean by "the schema".

External schema (view)

One user group's slice: the columns they need, the rows they may see, sometimes values computed rather than stored. There are many of these. A user group that cannot see the salary column has no way to ask for it.

All three are schemas — descriptions. Only the stored data is an instance. The internal schema *describes* the storage; it is not the storage.

Between the levels sit two mappings, and they are machinery rather than documentation — every query is translated through them.

1. 1A query arrives written against an external schema.
2. 2The external/conceptual mapping rewrites it in terms of the conceptual schema.
3. 3The conceptual/internal mapping turns that into file and index operations.
4. 4Results travel back up, reshaped at each boundary into what the view promised.

The architecture exists to bound the blast radius of a change. Two guarantees, and the distinction between them is the single most-asked thing here.

Physical data independence

Change the internal schema — add an index, change page size, reorganise files — without changing the conceptual schema or any application. Only the conceptual/internal mapping is rewritten. This is routinely achieved, which is why you can index a live system on a Tuesday afternoon.

Logical data independence

Change the conceptual schema — split a table, add an attribute — without changing existing external views. The external/conceptual mappings absorb it, typically by redefining a view as a join. Harder, and only possible while the information remains derivable.

The mnemonic that survives exam pressure: physical independence protects the conceptual schema from storage changes; logical independence protects the views from schema changes. Physical is the easier of the two.

• Security. A view that omits salary is not a filter that can be bypassed — the column is not in the user's schema at all.
• Simplicity. A five-table join presented as one apparent table, so casual users never see the structure.
• Stability. Legacy applications keep the shape they were written against while the real schema moves on underneath.

The limit is that a view is a definition, not a copy. If an attribute is genuinely deleted from the conceptual schema, no mapping can reconstruct it, and the view has to change — logical data independence is a strong property, not an unconditional one.

Do not confuse the three schemas with the two- or three-tier deployment architecture, which is about where software runs rather than how data is described.