# Three-Schema Architecture and Data Independence — Database Fundamentals

Source: https://www.skillbyai.com/en/database-fundamentals/f-arch

> Describe the external, conceptual and internal levels and the two kinds of 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.

```sql
-- 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.

**Quiz:** Adding an index without changing any table definitions or applications is an example of what?

- [ ] Logical data independence
- [ ] Referential integrity
- [ ] Normalisation
- [x] Physical data independence

*Answer:* Physical data independence. Changing physical storage without affecting the conceptual schema is physical data independence.