📘 3 Levels of Data Abstraction in DBMS
(from the ANSI/SPARC model)
Data abstraction in DBMS is implemented using three levels:
External Level, Conceptual Level, and Internal Level
Each level hides certain details from the one above it to simplify interaction and isolate complexity.
🧱 1️⃣ Internal Level — Physical Level (Lowest)
🔍 What it is:
-
Describes how data is physically stored in memory (disk blocks, file formats, indexing).
-
Closest to the operating system and storage hardware.
📦 Example:
-
Rows stored in
.dbfiles -
Use of B+ trees, hash indexes, compression, page size
-
Whether storage engine is row-store or column-store
✅ Hidden from:
-
End users
-
Application developers
🧠 Interview Tip:
Changes here (e.g., how data is indexed) don’t affect queries — thanks to abstraction.
🧱 2️⃣ Conceptual Level — Logical Level (Middle)
🔍 What it is:
-
Describes the entire logical structure of the database.
-
Defines all entities, attributes, data types, relationships, and constraints.
-
This is where the schema lives.
📦 Example:
Table: Employees
- id INT PRIMARY KEY
- name VARCHAR(100)
- dept_id INT FOREIGN KEY- Doesn’t care how data is stored — just what exists.
✅ Hidden from:
- How data is stored physically (internal level)
🧠 Interview Tip:
If the schema changes (like adding a column), it doesn’t break the views at the external level — unless they rely on that column.
🧱 3️⃣ External Level — View Level (Top)
🔍 What it is:
-
Defines how individual users or applications view the data.
-
Can have multiple external views for different roles/users.
-
Implements subschemas and access control.
📦 Example:
-
HR app sees:
name,salary -
Intern app sees: only
name -
Executives see: analytics summaries via a
VIEW
✅ Hides:
-
Complexity of schema and storage
-
Sensitive columns (e.g., salaries, passwords)
🖼️ Summary Table:
| Level | Description | Who Uses It | What It Hides |
|---|---|---|---|
| External | What a user or app sees (views) | End Users, Apps | Schema + Physical storage |
| Conceptual | What the whole DB looks like (schema) | DB Designers | Physical storage |
| Internal | How data is actually stored (files/indexes) | DBMS Engine, Admins | Everything above it sees only logic |
🔁 Interaction Between Levels:
User Query (External View)
↓
Conceptual Schema (Logical Mapping)
↓
Internal Schema (Actual Storage Access)
🧠 Interview Line:
“The three levels of data abstraction in DBMS are: Internal Level (physical storage), Conceptual Level (logical schema), and External Level (user views). This separation allows flexibility, security, and simplified user interaction with complex data systems.”