Three-Schema Architecture and Data Independence

Levels of abstraction

The ANSI/SPARC three-schema architecture separates how users see data from how it is stored. The internal (physical) level describes storage: files, pages, indexes and compression. The conceptual (logical) level describes the whole database for the community of users: tables, columns, data types, relationships and constraints. The external (view) level describes the parts relevant to particular users or applications, for example a view that shows students only their own marks. Mappings connect the levels. This gives data independence. Physical data independence: you can change the internal level (add an index, move to faster disks, reorganise files) without changing the conceptual schema or applications. Logical data independence: you can change the conceptual schema (add a column, split a table) without changing external views and the applications that use them; this is harder to achieve. A schema is the design (it changes rarely); an instance is the data at a particular moment (it changes constantly).

The three levels in SQL terms

A view (external), tables (conceptual) and an index (internal) for the same data.

-- conceptual level: the logical schema
CREATE TABLE students (
    roll_no   INT PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    dept      VARCHAR(10)  NOT NULL
);
CREATE TABLE marks (
    roll_no   INT REFERENCES students(roll_no),
    subject   VARCHAR(20),
    score     INT CHECK (score BETWEEN 0 AND 100),
    PRIMARY KEY (roll_no, subject)
);

-- external level: what one application sees
CREATE VIEW cse_toppers AS
SELECT s.name, m.subject, m.score
FROM students s JOIN marks m ON m.roll_no = s.roll_no
WHERE s.dept = 'CSE' AND m.score >= 90;

-- internal level: a physical access path, invisible to queries
CREATE INDEX idx_marks_score ON marks (score);

A building, its floor plan and tenant views

The internal level is the plumbing and wiring, the conceptual level is the building's floor plan, and the external level is what each tenant sees of their own flat. You can re-route pipes without redrawing the floor plan, and add a room without changing what other tenants see.

त्वरित जाँच: Adding an index without changing any table definitions or applications is an example of what?

• Logical data independence
• Referential integrity
• Normalisation
• Physical data independence