Chapter 04 of 41

Three-Schema Architecture & Data Independence

A database is used by many kinds of people. An HR clerk wants to see employee names and departments. The payroll team needs salaries. The DBA worries about files, indexes and disk space. A good DBMS lets each of them see the data in the way that suits them, without forcing everyone to know how the data is actually stored.

The three-schema architecture is the classic design that makes this possible. It also gives us one of the most important ideas in DBMS: data independence. Both are favourite exam and interview topics.

Before we start, we need two basic terms.


Schema vs Instance

A schema is the description (design) of a database. An instance is the actual data stored in the database at a particular moment.

For example, this is part of the schema of our company database:

departments(dept_id INT, dept_name VARCHAR(50), location VARCHAR(50))

And this is its current instance:

+---------+-------------+-----------+
| dept_id | dept_name   | location  |
+---------+-------------+-----------+
|      10 | Engineering | Bengaluru |
|      20 | Sales       | Mumbai    |
|      30 | HR          | Delhi     |
|      40 | Marketing   | Pune      |
|      50 | Research    | Hyderabad |
+---------+-------------+-----------+

Schema

Instance

Structure / design / blueprint

Data / contents / snapshot

Changes rarely (only when the design changes)

Changes all the time (every insert, update, delete)

Like a class in programming

Like the objects of that class at some moment

Also called the intension

Also called the extension or database state


What Is the Three-Schema Architecture?

The three-schema architecture was proposed by the ANSI/SPARC study group in the 1970s. It describes a database at three levels of abstraction:

      User 1           User 2            User 3
        |                |                 |
  +-----------+    +-----------+     +-----------+
  | External  |    | External  |     | External  |      EXTERNAL LEVEL
  |  view 1   |    |  view 2   |     |  view 3   |      (how users see data)
  +-----------+    +-----------+     +-----------+
         \               |                /
          \   external/conceptual mapping
           \             |              /
        +----------------------------------+
        |        Conceptual schema         |            CONCEPTUAL LEVEL
        | (all entities, attributes,       |            (what data is stored)
        |  relationships, constraints)     |
        +----------------------------------+
                         |
             conceptual/internal mapping
                         |
        +----------------------------------+
        |         Internal schema          |            INTERNAL LEVEL
        | (files, pages, indexes, record   |            (how data is stored)
        |  layout, compression)            |
        +----------------------------------+
                         |
                  Stored database

The goal is separation: users' views are separated from the logical design, and the logical design is separated from physical storage. Let us look at each level.


1. Internal Level (Physical Level)

The internal schema describes how the data is physically stored on the storage device.

It includes things like:

  • File organisation (heap, sorted, hashed, clustered)

  • Indexes and their structure (B+ tree, hash)

  • Record layout, page (block) size

  • Compression and encryption of stored data

  • Where files are placed on disks

Example: In MySQL's InnoDB engine, each table is stored as a B+ tree ordered by its primary key (a clustered index), in 16 KB pages, with extra B+ trees for secondary indexes. The table employees is physically a B+ tree on emp_id.

Who cares about this level? Mainly the DBMS itself and the DBA.


2. Conceptual Level (Logical Level)

The conceptual schema describes what data is stored in the whole database and the relationships among the data, for the entire organisation. It hides physical details.

It includes:

  • All entities/tables and their attributes/columns

  • Data types

  • Relationships between tables

  • Constraints (keys, NOT NULL, CHECK, foreign keys)

Example: the conceptual schema of our company database:

departments(dept_id PK, dept_name UNIQUE, location)
employees(emp_id PK, name, email UNIQUE, dept_id FK -> departments,
          manager_id FK -> employees, salary, hire_date, city)
customers(customer_id PK, name, city, signup_date)
orders(order_id PK, customer_id FK -> customers, order_date, amount, status)

Notice there is nothing here about B+ trees or 16 KB pages. Database designers and DBAs work at this level, and it is what we normally mean by "the schema". There is exactly one conceptual schema per database.


3. External Level (View Level)

The external schema (also called a view or subschema) describes the part of the database that a particular user group is interested in, and hides the rest.

There can be many external schemas for one database. For example:

User group

What their view shows

What is hidden

Company directory (all staff)

Name, department name, city

Salary, email, manager, hire date

Payroll team

Employee id, name, salary

Customers, orders

Sales team

Customers and their orders

Employee salaries

External views give two big benefits:

  • Simplicity: each user sees only what they need, sometimes with friendly column names or pre-joined tables.

  • Security: sensitive columns like salary simply do not exist in the directory view.

In SQL, external schemas are usually implemented with views. Here is the "company directory" view:

CREATE VIEW emp_directory AS
SELECT e.name, d.dept_name, e.city
FROM employees e JOIN departments d ON e.dept_id = d.dept_id;

SELECT * FROM emp_directory WHERE dept_name = 'Sales';
+--------------+-----------+---------+
| name         | dept_name | city    |
+--------------+-----------+---------+
| Sneha Kapoor | Sales     | Mumbai  |
| Vikram Singh | Sales     | Mumbai  |
| Ananya Das   | Sales     | Kolkata |
+--------------+-----------+---------+

A user of this view does not know (or need to know) that the data comes from two tables joined on dept_id.


Mappings Between Levels

Only the internal level is actually stored on disk. The other two levels are descriptions. So when a user queries a view, the DBMS must translate the request down the levels, and translate the results back up. These translations are called mappings.

  • External/conceptual mapping: how each view is derived from the conceptual schema. For emp_directory, it is the view definition: "join employees and departments on dept_id and take these three columns".

  • Conceptual/internal mapping: how each logical table and column is stored physically: which file, which B+ tree, which indexes.

Mappings are the secret behind data independence: when one level changes, we only need to change a mapping, not everything above it.


Data Independence

Data independence is the ability to change the schema at one level without having to change the schema at the next higher level (and so without changing application programs).

Remember the "program-data dependence" problem from File System vs DBMS? Data independence is its cure. There are two types.

Physical Data Independence

Physical data independence is the ability to change the internal schema without changing the conceptual schema (or the external schemas and applications).

Examples of physical changes:

  • Creating or dropping an index

  • Changing file organisation, e.g. from heap to hashed

  • Moving data files to a faster SSD

  • Turning on compression

Let us try it. Suppose many queries search by city, so the DBA adds an index. Before the index, MySQL scans the whole table (type = ALL, all 10 rows):

EXPLAIN SELECT name FROM employees WHERE city = 'Mumbai';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

Now the physical change:

CREATE INDEX idx_emp_city ON employees(city);
EXPLAIN SELECT name FROM employees WHERE city = 'Mumbai';
+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_emp_city  | idx_emp_city | 203     | const |    2 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

The way the data is accessed changed (now an index lookup reading about 2 rows), but the query text is identical, and so is its result:

+--------------+
| name         |
+--------------+
| Sneha Kapoor |
| Vikram Singh |
+--------------+

Our emp_directory view also kept working unchanged. No application had to be modified. That is physical data independence.

Logical Data Independence

Logical data independence is the ability to change the conceptual schema without changing the external schemas (views) or application programs.

Examples of logical changes:

  • Adding a new column or a new table

  • Removing a column that some views do not use

  • Renaming a column, or splitting one table into two

Case 1: adding a column. The company starts storing phone numbers:

ALTER TABLE employees ADD COLUMN phone VARCHAR(15);
SELECT * FROM emp_directory WHERE dept_name = 'Sales';
+--------------+-----------+---------+
| name         | dept_name | city    |
+--------------+-----------+---------+
| Sneha Kapoor | Sales     | Mumbai  |
| Vikram Singh | Sales     | Mumbai  |
| Ananya Das   | Sales     | Kolkata |
+--------------+-----------+---------+

The view is unaffected, because it never mentioned phone.

Case 2: renaming a column the view uses. Now a designer renames city to work_city:

ALTER TABLE employees RENAME COLUMN city TO work_city;
SELECT * FROM emp_directory WHERE dept_name = 'Sales';
ERROR 1356 (HY000): View 'company_db.emp_directory' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

The view broke, because its mapping still refers to the old column. But here is the important part: we fix it by changing only the mapping (the view definition), keeping the view's columns exactly as users know them:

CREATE OR REPLACE VIEW emp_directory AS
SELECT e.name, d.dept_name, e.work_city AS city
FROM employees e JOIN departments d ON e.dept_id = d.dept_id;

SELECT * FROM emp_directory WHERE dept_name = 'Sales';
+--------------+-----------+---------+
| name         | dept_name | city    |
+--------------+-----------+---------+
| Sneha Kapoor | Sales     | Mumbai  |
| Vikram Singh | Sales     | Mumbai  |
| Ananya Das   | Sales     | Kolkata |
+--------------+-----------+---------+

Every application that uses emp_directory still sees a column called city and needs no change. The conceptual schema changed; the external schema did not. That is logical data independence, achieved by updating the external/conceptual mapping.

(These demos were run on a copy of the sample database so the original stays untouched.)

Why Is Logical Data Independence Harder?

Physical changes only affect how data is reached, and the DBMS handles that automatically through the optimizer. Logical changes affect what data exists, and views and programs refer directly to tables and columns by name. If a column is removed and some view genuinely needs that data, no mapping can bring it back. Also, applications in practice often query base tables directly instead of going through views.

Physical data independence is easier to achieve than logical data independence. Almost every DBMS gives physical independence; logical independence depends on how carefully views and applications are designed.

Logical vs Physical Data Independence

Basis

Logical Data Independence

Physical Data Independence

Change happens at

Conceptual level

Internal level

Protected level

External level (views, applications)

Conceptual level (and above)

Mapping that absorbs the change

External/conceptual

Conceptual/internal

Example changes

Add/remove/rename columns or tables, split a table

Add an index, change file organisation, move to SSD, compress

Difficulty

Harder

Easier

Mainly concerns

Structure / meaning of data

Storage / performance


Why Is the Three-Schema Architecture Useful?

  • Data independence: storage can be tuned and the design can evolve without rewriting applications.

  • Security: users see only their view of the data.

  • Simplicity: users work with simple, tailored views.

  • Multiple users, one database: different departments share the same data while seeing it differently.

The cost is some overhead: the DBMS must translate requests through the mappings. In practice, many DBMSs do not separate all three levels perfectly, but the model remains the standard way to think about abstraction.


Common Mistakes Students Make

  • Mixing up which independence is which. Trick: the name tells you where the change happens. Physical independence = change at the physical (internal) level. Logical independence = change at the logical (conceptual) level.

  • Saying there are many conceptual schemas. There is one conceptual and one internal schema per database, but many external schemas.

  • Confusing the three-schema architecture with 3-tier architecture. The three schemas are levels of abstraction of data; the three tiers are machines in a deployment.

  • Thinking views store data. A normal view stores only its definition (the mapping); data is read from the base tables each time.


Interview Questions

1. What are the three levels of the three-schema architecture?

External (view) level: how individual users see the data. Conceptual (logical) level: what data the whole database stores and its relationships and constraints. Internal (physical) level: how the data is actually stored.

2. What is data independence? What are its types?

The ability to change the schema at one level without changing the schema at the next higher level. Physical data independence protects the conceptual level from internal changes; logical data independence protects external views from conceptual changes.

3. Which type of data independence is harder to achieve and why?

Logical data independence, because views and programs refer directly to tables and columns; removing or restructuring data they depend on cannot always be hidden by a mapping.

4. Is adding an index a logical or physical change?

Physical. It changes how data is accessed, not what data exists, so queries keep working unchanged.

5. What is the difference between a schema and an instance?

The schema is the design of the database and changes rarely; the instance is the data stored at a given moment and changes frequently.

6. How are external schemas implemented in SQL?

Mainly with views (CREATE VIEW), combined with privileges so that users can access the view but not the underlying tables.


Summary

  • A schema is the design; an instance is the data at a moment.

  • The three-schema architecture has external (views), conceptual (logical design) and internal (physical storage) levels, connected by two mappings.

  • Physical data independence: change storage (indexes, files) without changing the conceptual schema. Easier.

  • Logical data independence: change the logical design (columns, tables) without changing views and apps. Harder.

  • Views implement external schemas; when the base design changes, updating the view (the mapping) can keep applications unchanged.

Next, in Data Models, we will look at the different ways of describing the conceptual structure of data: hierarchical, network, relational, ER, object-based and more.