Three Schema Architecture

Definition

The Three Schema Architecture (also called the ANSI-SPARC architecture, proposed in 1975) is a framework that describes a database at three separate levels: the External level, the Conceptual level, and the Internal level. Its purpose is to separate how individual users see data, how the whole organization logically models data, and how data is physically stored, so that changes at one level do not force changes at the others.

How It Works — One Piece of Data, Three Views

Consider a college database holding employee salary data.

• Internal schema: the Employee record is stored as a fixed-width 64-byte row in a heap file, indexed by EmpID using a B+-tree, on a particular disk partition.
• Conceptual schema: logically, Employee(EmpID, Name, DeptID, Salary) exists as one integrated table with a foreign key DeptID → Department(DeptID) and a constraint Salary > 0.
• External schema: the Payroll application only sees a view exposing (EmpID, Name, Salary); the HR application sees a different view exposing (EmpID, Name, DeptID). Neither application sees the other's columns or knows anything about the B+-tree underneath.

Two mappings connect the levels: the external/conceptual mapping and the conceptual/internal mapping. These mappings are exactly what makes data independence (covered in 2.5 and 2.6) possible.

Edge Cases and Pitfalls

• If an external schema exposes a computed column (e.g., AnnualSalary = Salary * 12), the external/conceptual mapping must know how to derive it — it does not exist as a stored column.
• Two different external schemas can use conflicting names for the same conceptual attribute (Payroll calls it Salary, Finance calls it Pay) — the mapping must resolve this per view.
• A change made purely at the internal level (e.g., re-indexing) must never require touching an external schema; if it does, the architecture is not being used correctly.

Interview Takeaways

• Q: Why three schemas instead of one? To isolate end users and applications from physical storage details, and to let different user groups see only the portion of data relevant to them.
• Q: Which level does a DBA primarily design? The conceptual schema (the full logical model); the internal schema is the DBA's storage-tuning concern, and external schemas are typically built per application/user group.