1.2The three-level schema and data independence

What DBAs and application developers face day to day is the judgment of how far a change's impact reaches—"if I add a column to this table, will the existing application break?" or "I want to change the storage method to improve performance, but will the application's SQL need rewriting?" The three-level schema is precisely the design philosophy behind this judgment, and it needs to be understood not as rote terminology but as a framework for determining at which level a change stops, and to which level it propagates.

1.2.1The structure of the three-level schema

• The three-level schema is a database logical design model proposed by ANSI/SPARC, consisting of three levels from top to bottom: the external schema (the per-user/per-application view—the data structure seen by a view or an individual screen), the conceptual schema (the logical data structure shared organization-wide—table definitions and constraints), and the internal schema (the actual storage format—file layout, indexes, and storage order).
• Each level is connected via mappings: the external schema obtains data through a mapping to the conceptual schema, and the conceptual schema accesses the actual stored data through a mapping to the internal schema. This mapping is the actual mechanism that keeps a change at one level from propagating to another.

1.2.2Logical and physical data independence

• Logical data independence is the property that a change to the conceptual schema does not affect the external schema (applications/views). For example, adding a new column to an existing table lets existing views or SQL that do not reference that column keep working unchanged (the mapping absorbs the change).
• Physical data independence is the property that a change to the internal schema (storage format, indexes, file placement) does not affect the conceptual schema (table definitions). For example, adding or changing an index for performance, or relocating a table to a different disk area, requires no change to how SQL (queries based on the table definition) is written.

Suppose you are the DBA for a core business system and receive a proposal to "add a new index to speed up searches on the order-line-item table, and also relocate the storage file to faster storage." The first thing to judge is whether this change is confined entirely to the internal schema (storage format, index, file placement). Adding an index or physically relocating a file does not change the table's logical definition (column names, data types, constraints), so physical data independence lets you conclude that the application-side SQL needs no change at all. Next, suppose the sales department asks to "add a new column (a discount-applied flag) to the order line items, but under no circumstances break the existing invoicing application's SQL." This is a change to the conceptual schema (the table definition), and in theory, logical data independence can absorb the impact on the existing application, provided it does not reference the new column. In practice, however, an application using SELECT *, or one whose processing depends on the physical column order, can be unintentionally affected even though independence holds in theory. It would be dangerous here to over-trust that "the three-level schema guarantees nothing will ever break when a column is added"—you must not forget the premise that independence is only as good as how carefully the mappings are designed and implemented.

1.2.3Section summary

• The three-level schema consists of the external schema (application view), the conceptual schema (logical overall structure), and the internal schema (actual storage format)
• Physical data independence: a change to the internal schema (index, storage method) does not affect the conceptual schema. Logical data independence: a change to the conceptual schema (e.g., adding a column) does not affect the external schema
• Theoretical independence rests on the premise that mappings are properly designed and the application is implemented accordingly (e.g., avoiding SELECT *)—do not over-trust that nothing will ever break unconditionally