Three-Schema Architecture

The three-schema architecture, also known as the ANSI/SPARC architecture, is a foundational framework for designing and managing databases. Its primary goal is to separate user applications from the physical database, which is what achieves data independence. This separation makes systems more flexible, scalable, and easier to maintain.

This page builds on the three-schema architecture covered earlier in this module, going deeper into how the three levels connect to each other and where the same architecture shows up in systems well beyond the relational databases it was originally designed for.

1. External Schema (View Level)

This is the highest level of abstraction, defining how specific users or applications see the data. An external schema presents a tailored view that shows only the relevant parts of the database, simplifying interaction and enhancing security. A single database can have many external schemas.

• Who uses it: End users, application developers.
• Example: Stories on CD, Inc.'s order-entry screen might have a view showing a customer's name, address, and order history, while hiding distributor cost data entirely.

2. Conceptual Schema (Logical Level)

The conceptual schema is a unified, logical representation of the entire database. It describes all the entities, attributes, relationships, and integrity constraints. This level hides physical storage details, focusing on the overall data model. There is only one conceptual schema per database.

• Who uses it: Database designers, data architects.
• Example: The ER diagram defining Stories on CD, Inc.'s Customer, Order, CD, and Distributor tables, along with their primary and foreign key relationships, covered earlier in this course.

3. Internal Schema (Physical Level)

This schema describes how data is physically stored on hardware: file structures, indexes, data compression, and storage access paths. Its goal is to optimize performance and storage efficiency. There is only one internal schema per database.

• Who uses it: Database administrators, infrastructure engineers.
• Example: Specifying that Stories on CD, Inc.'s Order table is indexed on OrderNo for fast lookup, and deciding which storage tier the table itself lives on.

The separation of these schemas provides two crucial types of data independence:

• Logical data independence. The ability to change the conceptual schema without having to change the external schemas. Adding a new ShippingNotes column to Stories on CD's Order table, for instance, would not break an existing order-entry view that does not reference that column.
• Physical data independence. The ability to change the internal schema without having to change the conceptual schema. A DBA could move the database files to a different storage device or change the indexing strategy to improve performance, and neither the applications nor the logical data model would be affected.

The database management system acts as a referee, managing the mappings between these levels to ensure everything works together seamlessly.

• External-conceptual mapping. Translates requests from a user's view (external schema) into operations on the logical model (conceptual schema).
• Conceptual-internal mapping. Translates requests on the logical model into operations on the physical data storage (internal schema).

The DBMS uses these mappings to find the physical data a user requests and to ensure every operation is consistent with the rules and constraints defined at the conceptual level.

Tracing a single request through both mappings makes the mechanism concrete. A Stories on CD customer service representative opens the order-entry view and asks for order number 4471. That request hits the external-conceptual mapping first, which translates "give me what the order-entry view shows for order 4471" into a query against the conceptual schema's Order and Customer tables, joined on CustID. The conceptual-internal mapping then takes that logical query and translates it into the actual physical operation: which file the Order table's rows live in, which index to use to jump straight to row 4471 rather than scanning the whole table, and how to read that data off disk. Two translations happen in the time it takes to load a screen, and the representative never needs to know either one occurred.

While originally conceived for relational databases, the principles of the three-schema architecture are more relevant than ever in today's technology landscape.

• Cloud databases, such as AWS RDS or Azure SQL: you define the logical schema, tables and columns, while the cloud provider manages the entire internal, physical schema for you.
• Object-relational mappers (ORMs) create an object-oriented external view for developers, mapping it to the conceptual schema of a relational database underneath.
• APIs and microservices: an API often serves as an external schema, providing a stable contract for data access while the underlying conceptual or physical database can be changed independently.

Each of these is the same idea wearing different clothes. A cloud provider managing the internal schema for you is physical data independence sold as a service: you never move a file or rebuild an index yourself, because the provider's own infrastructure absorbs that work behind a conceptual schema you still control. An ORM's object-oriented view is an external schema in the strictest ANSI/SPARC sense: a tailored way of seeing the data, shaped for one kind of consumer, that can change its own presentation without touching the tables underneath. And an API contract that stays stable while the database behind it is rebuilt, migrated, or replaced entirely is logical and physical data independence extended past the database's own boundary, out to every client that calls the API.

This course's own Customer, Order, CD, and Distributor design is, itself, a conceptual schema; every application that will ever be built against Stories on CD, Inc., an order-entry screen, a reporting dashboard, an API serving a mobile app, is an external schema built on top of it. Understanding the three-schema architecture is, in a real sense, understanding why that design choice from Module 1 still holds up no matter what gets built on top of it later.

A short recap of this deeper look:

• The three-schema (ANSI/SPARC) architecture separates a database into external, conceptual, and internal levels, each serving a different group: end users and application developers, database designers, and DBAs respectively.
• Explicit mappings, external-conceptual and conceptual-internal, are what deliver logical and physical data independence: changes at one level do not ripple into the others.
• The same pattern, not the same names, shows up throughout modern systems: cloud databases, ORMs, and API contracts all separate a stable external view from an internal implementation that is free to change.