Skip to content

Lesson 3: Three-Schema Architecture and Data Independence

Three levels, two mappings, two independences

About this lesson

Course: Database Management Systems

Unit 1: Introduction to Database · 5 Lecture Hours

Lesson duration: 1 lecture (50 minutes)

Reading: Elmasri & Navathe, Ch. 2.2 · ANSI/SPARC 1975

Learning Outcomes

  1. Draw and label the ANSI/SPARC three-schema architecture.
  2. Describe the internal, conceptual and external levels, and say what each contains.
  3. Explain the two mappings and the cost of maintaining them.
  4. Define logical and physical data independence and distinguish them with examples.
  5. Explain why logical data independence is harder to achieve than physical.

3.1 The Problem the Architecture Solves

Lesson 2 established program–data independence as a goal. The three-schema architecture, proposed by the ANSI/SPARC study group in 1975, is the design that achieves it.

The idea is to separate the user's view of the data from the way the data is physically stored, and to insert a stable layer between them. Three levels of description, therefore, rather than one.

          END USERS
     clerk       mentor       principal
       |            |             |
   +---v----+  +----v---+  +------v-----+
   | EXTERNAL  |EXTERNAL |  | EXTERNAL   |   <---- EXTERNAL / VIEW LEVEL
   | SCHEMA 1  |SCHEMA 2 |  | SCHEMA 3   |         (many; one per user group)
   +---+----+  +----+---+  +------+-----+
       |            |             |
       +----- external/conceptual mapping ------+
                    |
        +-----------v-------------+
        |   CONCEPTUAL SCHEMA     |             <---- CONCEPTUAL / LOGICAL LEVEL
        |  all entities, all      |                   (exactly ONE)
        |  attributes, all        |
        |  relationships,         |
        |  all constraints        |
        +-----------+-------------+
                    |
        +---- conceptual/internal mapping ----+
                    |
        +-----------v-------------+
        |   INTERNAL SCHEMA       |             <---- INTERNAL / PHYSICAL LEVEL
        |  file organisation,     |                   (exactly ONE)
        |  indexes, page size,    |
        |  compression, ordering  |
        +-----------+-------------+
                    |
        +-----------v-------------+
        |   STORED DATABASE       |
        |   (disk blocks)         |
        +-------------------------+

Figure 3.1 — The ANSI/SPARC three-schema architecture.

3.2 The Three Levels

Internal level (physical level)

What it describes: how the data is physically stored.

The internal schema, written in a physical data definition language, specifies:

  • the file organisation of each table — heap, sorted, hashed (Unit 6);
  • which indexes exist and of what kind (B+-tree, hash);
  • the record format — field order, sizes, encoding;
  • compression and encryption used;
  • physical placement and clustering of related records.

Illustrative internal schema:

STORED_STUDENT
    LENGTH = 48 bytes
    ORGANISATION = B+-tree clustered on Rollno
    PREFIX  : type = BYTE(1),   offset = 0
    Rollno  : type = BYTE(8),   offset = 1,  INDEX rollno_idx
    Name    : type = BYTE(30),  offset = 9
    Dob     : type = DATE(4),   offset = 39, INDEX dob_idx
    COMPRESSION = prefix

Note there is exactly one internal schema.

Careful

The internal level is not the physical device level. Deciding which disk sector a page lands on is the operating system's business, below the DBMS. The internal schema describes structures — files, pages, indexes — not hardware.

Conceptual level (logical level)

What it describes: what data is stored and what relationships exist among it — for the whole community of users — while hiding all physical detail.

The conceptual schema specifies entities, attributes, data types, relationships, user operations and constraints. It says nothing about indexes, file organisation or storage.

CREATE TABLE STUDENT (
    Rollno      VARCHAR(8)  PRIMARY KEY,
    Name        VARCHAR(40) NOT NULL,
    Dateofbirth DATE
);
CREATE TABLE COURSE (
    SID        VARCHAR(6)  PRIMARY KEY,
    Cname      VARCHAR(40) NOT NULL,
    TotalSeats INT         CHECK (TotalSeats > 0),
    Coursetype VARCHAR(10) CHECK (Coursetype IN ('Fulltime','Parttime'))
);
CREATE TABLE ADMISSION (
    Rollno         VARCHAR(8),
    SID            VARCHAR(6),
    Dateofadmission DATE,
    PRIMARY KEY (Rollno, SID),
    FOREIGN KEY (Rollno) REFERENCES STUDENT(Rollno),
    FOREIGN KEY (SID)    REFERENCES COURSE(SID)
);

There is exactly one conceptual schema. It is the stable centre of the architecture — the single agreed description of the enterprise's data.

External level (view level)

What it describes: the part of the database that a particular user group sees, in the form most convenient to them. Everything else is hidden.

There are many external schemas — one per user group. An external schema may:

  • expose a subset of tables and a subset of columns;
  • rename things to suit the user's vocabulary;
  • present derived data that is not stored at all (e.g. Age computed from Dateofbirth);
  • restructure — join several tables so the user sees one.
-- External schema for the Society Mentor
CREATE VIEW MentorView AS
SELECT s.Rollno, s.Name AS StudentName, y.Socname AS Society
FROM   STUDENT s
JOIN   ENROLLMENT e ON s.Rollno = e.Rollno
JOIN   SOCIETY    y ON e.SocID  = y.SocID;

-- External schema for the Principal (aggregated, no personal data at all)
CREATE VIEW CourseLoad AS
SELECT c.Cname, COUNT(a.Rollno) AS Enrolled, c.TotalSeats
FROM   COURSE c LEFT JOIN ADMISSION a ON c.SID = a.SID
GROUP  BY c.SID, c.Cname, c.TotalSeats;

The mentor cannot see Dateofbirth; the Principal cannot see any individual student. The view is simultaneously a convenience and a security boundary.

Summary table

Internal Conceptual External
Question answered How is it stored? What is stored? What does this user see?
How many One One Many
Written by DBA / system designer DBA with the enterprise DBA per user group
Contains Files, indexes, record formats Tables, types, relationships, constraints Views
Hides Nothing below it All physical detail Everything the user need not see
DBMS artefact Storage engine settings, indexes Base tables (CREATE TABLE) Views (CREATE VIEW)

3.3 The Two Mappings

The three levels are only descriptions. Something must translate a request stated at one level into a request at the level below. That is the job of the two mappings, maintained by the DBMS.

External/conceptual mapping — relates each external schema to the conceptual schema. It tells the DBMS that the mentor's StudentName is the conceptual STUDENT.Name, and that MentorView is a join of three conceptual tables.

Conceptual/internal mapping — relates the conceptual schema to the internal schema. It tells the DBMS that conceptual column STUDENT.Rollno occupies bytes 1–8 of the stored record in the B+-tree-organised file, and that an index rollno_idx may be used.

Query processing across the levels

A user submits SELECT StudentName FROM MentorView WHERE Society = 'Debating';

  1. The DBMS uses the external/conceptual mapping to rewrite the query against base tables STUDENT, ENROLLMENT, SOCIETY.
  2. The optimiser uses the conceptual/internal mapping to choose access paths — "use soc_idx on SOCIETY.Socname, then the clustered index on Rollno".
  3. The storage engine reads the pages.
  4. The result travels back up, being reshaped at each level, until the user receives exactly the columns their external schema promised.

This is called mapping or, when it happens in the reverse direction for results, transformation.

The cost of the architecture

Every query pays for two levels of translation. That is a real overhead. Most commercial DBMSs therefore do not implement the three levels as three literally separate schema files; they support the effect of the architecture (base tables, views, storage parameters and indexes) while blending the levels internally for speed.

3.4 Data Independence

Data Independence

The capacity to change the schema at one level of a database system without having to change the schema at the next higher level. Only the mapping between the two levels is changed; everything above is untouched.

Physical data independence

Physical data independence

The capacity to change the internal schema without changing the conceptual schema — and therefore without changing the external schemas or the application programs.

Changes that should be invisible upwards:

  • Creating or dropping an index. CREATE INDEX idx_name ON STUDENT(Name); makes queries faster and changes nothing about what data exists.
  • Reorganising a file from heap to sorted, or to hashed on Rollno.
  • Moving the database to a faster disk, or splitting it across several disks.
  • Changing the page size, or enabling compression.

After any of these, SELECT Name FROM STUDENT WHERE Rollno='X2019'; returns exactly the same answer, and no application is recompiled. Only the conceptual/internal mapping is revised.

Physical data independence is generally achieved well by modern DBMSs, because there is a clean, well-understood boundary: the conceptual level was defined from the outset to exclude all storage detail.

Logical data independence

Logical data independence

The capacity to change the conceptual schema without changing the external schemas — and therefore without changing the application programs.

Changes that can be absorbed:

  • Adding a column. ALTER TABLE STUDENT ADD COLUMN Mobile CHAR(10); — existing views that list columns explicitly are unaffected.
  • Adding a new table or a new relationship that existing users do not use.
  • Splitting a table by normalisation. Suppose STUDENT(Rollno, Name, Dob, Deptname, Deptphone) is decomposed into STUDENT(Rollno, Name, Dob, Dno) and DEPT(Dno, Deptname, Deptphone). Applications that expected the wide table need not change: redefine their external schema as a join view with the original name and columns.
-- Absorbing a decomposition; the old application still sees the old shape
CREATE VIEW STUDENT_OLD AS
SELECT s.Rollno, s.Name, s.Dob, d.Deptname, d.Deptphone
FROM   STUDENT s JOIN DEPT d ON s.Dno = d.Dno;
  • Removing a column or table that no external schema uses.

Changes that cannot be absorbed:

  • Removing a column that a view actually exposes. If Dateofbirth is deleted from the conceptual schema, no mapping can conjure it back for a view that publishes it. The information is simply gone.
  • Narrowing a domain so that data the view promised no longer fits.

Why logical data independence is harder

  1. Applications are written directly against the logical structure — they name tables and columns. Physical structures (indexes, page sizes) are never named by an application, so hiding them is easy.
  2. A change to the conceptual schema may destroy information rather than merely relocate it. A physical change never destroys information; it only moves or reorganises it.
  3. Views are not fully updatable. Even if a view can reproduce the old read shape after a decomposition, INSERT/UPDATE/DELETE through a multi-table join view is restricted or forbidden by most DBMSs, so write-path applications may still break.

Hence: physical data independence is generally achievable; logical data independence is only partially achievable.

Comparison

Physical data independence Logical data independence
Insulates Conceptual level from internal level External level from conceptual level
Uses which mapping Conceptual/internal External/conceptual
Example change Add an index; switch to a hashed file Add a column; split a table by normalisation
Difficulty Easier — generally achieved Harder — only partially achieved
Why Applications never name physical structures Applications name tables and columns directly

3.5 Solved Questions

Solved Question 1

Q. Explain the three-schema architecture with a neat diagram. Why are three levels needed rather than two?

A. (Diagram: reproduce Figure 3.1.)

Internal level — described by the internal schema; specifies the physical storage structures: file organisation, indexes, record layout, compression. There is one internal schema.

Conceptual level — described by the conceptual schema; specifies all entities, attributes, relationships, data types and constraints for the whole community of users, hiding all physical detail. There is one conceptual schema.

External level — described by many external schemas (views); each shows one user group the part of the database relevant to it, possibly renamed, restructured or derived, and hides the rest.

Two mappings connect the levels: external/conceptual and conceptual/internal. The DBMS uses them to translate a user request down to storage operations and the result back up.

Why three rather than two? With only "user view" and "storage" there would be a single mapping, so any storage change would ripple straight into user views. The conceptual level is inserted as a stable buffer: it is the one agreed, technology-free description of the enterprise's data. Physical changes stop at the conceptual level (physical data independence), and user-specific concerns stop at the external level (logical data independence). Additionally the conceptual level provides a single place to state constraints once for everybody, and lets many differing views coexist over one consistent design.

Solved Question 2

Q. Distinguish between logical and physical data independence. Which is harder to achieve, and why?

A. See the comparison table in Section 3.4.3 for the core distinction.

Physical data independence insulates the conceptual schema from changes to the internal schema. Building an index on STUDENT.Name speeds queries up but changes no table, no column and no application.

Logical data independence insulates external schemas from changes to the conceptual schema. Adding a Mobile column to STUDENT should not disturb a view that lists only Rollno and Name.

Logical data independence is harder, for three reasons:

  1. Applications refer to logical objects by name — they say SELECT Name FROM STUDENT. They never refer to a B+-tree or a page size, so physical structures can be swapped freely beneath them.
  2. A conceptual change can remove information. Dropping Dateofbirth cannot be hidden from a view that shows Dateofbirth; a physical change never destroys information.
  3. Even when a view can restore the old read shape (for instance after a normalising decomposition), updates through join views are restricted, so applications that write may still fail.

In practice DBMSs achieve physical data independence well, and logical data independence only partially.

Solved Question 3

Q. For each change, state which level it affects, which mapping must be revised, and which kind of data independence (if any) protects the applications.

a. Building a hash index on COURSE.Cname. b. Adding Mobile to STUDENT. c. Decomposing STUDENT into STUDENT and DEPT. d. Dropping Dateofbirth, which the admission view displays. e. Migrating the database from one disk to a RAID array.

A.

Level changed Mapping revised Independence Applications safe?
a Internal Conceptual/internal Physical Yes — queries return the same answers, only faster.
b Conceptual External/conceptual Logical Yes — views that name columns explicitly are unaffected. (A view using SELECT * may change shape, which is one reason SELECT * is discouraged in view definitions.)
c Conceptual External/conceptual Logical Mostly — define a join view with the old name and columns, and readers are unaffected. Applications that update the old wide shape may break, since join views are not generally updatable.
d Conceptual Cannot be repaired Logical independence fails No — the information no longer exists; no mapping can recreate it.
e Internal (indeed below it) Conceptual/internal Physical Yes — completely invisible above the internal level.

Solved Question 4

Q. Trace the processing of the query SELECT StudentName FROM MentorView WHERE Society = 'Debating'; through the three levels.

A.

  1. External level. The user's request is expressed against MentorView, which their external schema defines as columns (Rollno, StudentName, Society).

  2. External/conceptual mapping. The DBMS looks up the view definition in the catalog and rewrites the query against conceptual base tables. StudentName is resolved to STUDENT.Name, Society to SOCIETY.Socname:

SELECT s.Name
FROM   STUDENT s
JOIN   ENROLLMENT e ON s.Rollno = e.Rollno
JOIN   SOCIETY    y ON e.SocID  = y.SocID
WHERE  y.Socname = 'Debating';
  1. Conceptual level. Constraints and privileges are checked: does this user hold SELECT on the view? Are the joins over declared foreign keys?

  2. Conceptual/internal mapping. The optimiser consults the internal schema for available access paths and statistics, and chooses a plan — for example, use the index on SOCIETY.Socname to find SocID, probe the index on ENROLLMENT.SocID, then look up STUDENT rows by the clustered index on Rollno.

  3. Internal level. The storage engine reads the relevant disk pages into the buffer pool and extracts the byte ranges holding Name.

  4. Result travels back up, being transformed at each mapping, and the user receives a single column labelled StudentName — the shape their external schema promised, with no knowledge of the three underlying tables or of any index.

Solved Question 5

Q. Why do most commercial DBMSs not implement the three-schema architecture literally?

A. Because of performance and pragmatism.

The architecture is a reference model: it describes a clean separation of concerns, not a mandatory implementation. Implementing it literally would mean that every single query is parsed and translated twice — once through the external/conceptual mapping and once through the conceptual/internal mapping — and that each result is transformed twice on the way back. For high-throughput OLTP workloads that overhead is unacceptable.

Real systems therefore support the effect without the literal structure. In MySQL or Oracle:

  • the conceptual schema appears as the set of base tables created with CREATE TABLE;
  • the external schemas appear as views and per-user privileges;
  • the internal schema appears as storage-engine choices, tablespaces, indexes and physical parameters — specified as clauses of the same DDL rather than in a separate schema language.

The optimiser flattens views into the base query and compiles a single execution plan, so the two translations collapse into one pass. Users still get logical and physical data independence; they simply do not pay for two literal levels of indirection.

3.6 Exercises

A. Short answer

  1. Who proposed the three-schema architecture, and in what year?
  2. How many internal, conceptual and external schemas can a database have?
  3. Name the two mappings and say what each connects.
  4. Define physical data independence in one sentence.
  5. Give one example of a change that logical data independence cannot absorb.
  6. At which level are constraints such as PRIMARY KEY specified?

B. Long answer

  1. Explain the three-schema architecture with a labelled diagram. Describe the contents of each schema and the purpose of each mapping.
  2. Define logical and physical data independence. Compare them in a table and explain, with three arguments, why logical independence is the harder of the two.
  3. "The conceptual level is the stable centre of the architecture." Explain and justify this statement.
  4. Describe how a query is processed through the three levels, from submission to result.

C. Application and analytical

  1. For a BANK database with tables CUSTOMER, ACCOUNT, TRANSACTION, write: a. two sentences of the conceptual schema; b. two different external schemas (as CREATE VIEW statements) — one for a teller and one for an auditor; c. three plausible entries of the internal schema.
  2. Classify each change as internal, conceptual or external, and state whether applications break: a. Adding a CHECK constraint to TotalSeats. b. Increasing the buffer pool size. c. Granting a new user access to an existing view. d. Renaming column Cname to CourseName. e. Rebuilding a fragmented index. f. Merging two tables into one.
  3. A company normalises EMPLOYEE(Eno, Ename, Dno, Dname, Dlocation) into EMPLOYEE(Eno, Ename, Dno) and DEPARTMENT(Dno, Dname, Dlocation). Write the view that preserves the old external schema. Then explain precisely which operations on that view will still fail, and why.
  4. Argue for or against: "With modern hardware, physical data independence no longer matters."
  5. A hospital DBA wants to encrypt the Diagnosis column at rest. Which level does this change? Which independence protects the applications? What could still go wrong?

D. Objective — True or False with reason

  1. A database can have several conceptual schemas.
  2. Views belong to the internal level.
  3. Adding an index changes the conceptual schema.
  4. Logical data independence is easier to achieve than physical.
  5. The internal schema describes which disk sector each record occupies.
  6. Mappings are stored in the catalog.

Answers to Section D

  1. False. Exactly one — it is the single agreed description of the whole enterprise's data.
  2. False. Views are the external level.
  3. False. An index is an internal-level structure; the conceptual schema is unchanged. This is precisely physical data independence.
  4. False. It is harder, because applications name logical objects directly and conceptual changes can destroy information.
  5. False. Sector allocation is the operating system's concern. The internal schema describes files, records, indexes and access structures.
  6. True. The catalog stores view definitions (external/conceptual) and storage/column layout information (conceptual/internal).

Lesson Summary

  • Three levels: internal (how it is stored, one), conceptual (what is stored, one), external (what a user sees, many).
  • Two mappings: external/conceptual and conceptual/internal, maintained by the DBMS in the catalog.
  • Physical data independence: change the internal schema without touching the conceptual schema. Generally achieved.
  • Logical data independence: change the conceptual schema without touching external schemas. Only partially achieved — because applications name logical objects, conceptual changes can destroy information, and join views are not fully updatable.
  • Commercial systems realise the effect of the architecture without literally implementing three separate schema layers, for performance reasons.