Blog

DBMS Architecture and Data Independence Explained (Three-Level Architecture)

Part of the free Database Administration course on eLecturesAI. Lecture 2 of 16. Previous: What Does a DBA Do?. Next: SQL*Plus Explained: The DBA’s Command-Line Tool.

Here is a puzzle. A university has run its student database for ten years. Last week, the DBA moved all the data files to a bigger, faster disk. This week, the registrar added a new column called “email” to the student table. And yet nobody in the exam office had to rewrite a single program. The result sheets still print. The portal still logs students in.

How is that possible?

The answer is the idea behind this lecture: a database is described at three levels, and changes at one level do not have to break the others. This is called data independence, and it is one of the best ideas in database design.

By the end of this article you will know:

the three levels of a database: external, conceptual and internal,
what a schema is,
the difference between physical and logical data independence,
why a DBA cares about all of this,
the main parts of a database system.

The three-level architecture

A database can be described at three levels, from what users see down to how the data is physically stored. This is also known as the ANSI/SPARC three-level architecture, if you see that name in a textbook.

External level: what each user or application sees.
Conceptual level: the whole design: tables and the relations between them.
Internal level: the files and the storage on disk.

Think of a house. The guests see the rooms: that is the external level. The architect has the plan of the whole house: that is the conceptual level. The builder knows about the bricks and the wiring: that is the internal level. Three people, three views, one house.

  External level      (user views: what users see)
        |
  Conceptual level    (whole design: tables and relationships)
        |
  Internal level      (storage: files and disks)

The three levels compared

Level	Describes	Example
External	The part of the data meant for one user	A student sees only their own marks
Conceptual	All the tables and relations	Tables STUDENT, COURSE and RESULT
Internal	How and where the data is stored	Data files on the server disk

The description of a level is called a schema. So we speak of the external schema, the conceptual schema and the internal schema.

Here is the conceptual level in real SQL. This is the architect’s plan, written down:

CREATE TABLE student (
  roll_no  NUMBER(6) PRIMARY KEY,
  name     VARCHAR2(50),
  program  VARCHAR2(30)
);

And here is an external-level view. A student is given a window onto only part of the data (simplified for the example):

CREATE VIEW my_marks AS
SELECT course_id, marks
FROM   result
WHERE  roll_no = 101;

Between the levels, the DBMS converts the data automatically. A user asks for my_marks, and the DBMS works out which table and which file on disk to read. The user never has to know.

From our class: In class we talked about a simple fact: in a house, you cannot use a window or a ventilator in place of the door. Each one has its own job and its own place. The three levels of a database are the same. A request always travels in the same order: external, then conceptual, then internal. You cannot skip a level or swap their order.

This turns out to be very useful when something goes wrong, and it is how a DBA thinks. First, find out that there is a problem. Second, work out what kind of problem it is. Third, ask where in the DBMS it happened: at the external level (a user’s view), the conceptual level (a table or its design), or the internal level (a file or a disk). Because the levels always follow the same order, you can walk through them one by one instead of guessing.

Data independence

Now the big idea. Data independence means that a change at one level does not break the levels above it. There are two kinds.

Physical data independence means you can change how the data is stored, such as moving a file or adding a disk, without changing the tables or the programs.

Logical data independence means you can change the table design, such as adding a column, without breaking the user programs.

Type	Change happens at	Example
Physical	Internal level	Move a data file to a new disk
Logical	Conceptual level	Add a column to a table

Think of a car. If a mechanic replaces the engine (the internal part), you still drive the same way, with the same steering wheel and pedals. That is physical independence: the inside changed, the interface did not.

Back to our university. Adding the email column looks like this:

ALTER TABLE student ADD (email VARCHAR2(100));

SELECT roll_no, name FROM student;
-- still works, exactly as before

Programs that ask for the columns they need by name keep working. That is logical independence doing its job.

One honest warning: independence is not magic. A program that does INSERT INTO student VALUES (...) without naming its columns will fail after the new column is added, because it no longer supplies enough values. This is a good reason to always name your columns in your SQL.

Why the DBA cares

For a DBA, this is not just theory. It decides what the job looks like day to day:

Most DBA work happens at the internal level: files, disks and memory.
The DBA also manages the conceptual level: tables, constraints and views.
Views (external level) give each user only what they need. This makes them a security tool.
Because of data independence, the DBA can tune storage without stopping applications from working.

Level	DBA task
Internal	Storage, files, backup
Conceptual	Tables, constraints
External	Views, user access

So when the DBA moves data files to a faster disk on a Friday night, the applications on Monday morning do not even notice.

Parts of a database system

A working database system has four parts:

Part	Role
Users	People and applications that ask for data
DBMS software	Receives requests, finds the data and applies the rules
Database files	The stored data on disk
DBA	Looks after all of the above

A request follows a simple path: User → DBMS → Files, and the answer comes back the same way. Oracle is one example of a DBMS. We will look inside it in Lecture 8.

Common mistakes students make

Mixing up the levels. A view belongs to the external level. A table belongs to the conceptual level. A data file belongs to the internal level.
Mixing up the two independences. Moving a file or adding a disk is physical. Adding a column is logical. A trick: physical is about the place where data lives, logical is about the design of the tables.
Thinking a schema is the data. A schema is the description of a level, not the data itself.
Thinking data independence means nothing can ever break. It makes changes safer, but code that depends on column order, such as an INSERT without column names, can still fail.
Thinking the DBMS and the database are the same. The DBMS is the software. The database is the stored data.

From our class: e talked about what happens when you go for admission. You give all your details to the person at the first counter. Then you are called to the next counter, and the person sitting there has changed. Now you have to give everything again from the start. It is irritating, and it wastes everyone’s time.

Why did it happen? Because your data stayed with that one person, not in a shared place. A database solves exactly this. The data is stored once, and whoever needs it can read it, no matter how many people come and go at the counters. In database words, the person at the counter is the user or the application. The stored data is the database. The software that hands the data to whoever asks is the DBMS. People and programs change all the time, but the data stays where it is. That is why mixing up the DBMS and the database is such an expensive mistake: they are two different things doing two different jobs.

Summary

A database is described at three levels: external, conceptual and internal.
A description of a level is a schema.
Data independence: a change at one level does not break the others. Physical independence is about storage changes. Logical independence is about table design changes.
The DBA works mainly at the internal and conceptual levels, and uses views at the external level for security.
A database system has users, DBMS software, database files and the DBA. A request goes User → DBMS → Files and back.

Quick check

Try answering these before you scroll down:

Name the three levels.
Physical vs logical independence?
Which level do views belong to?

Ready to test yourself?

Reading is only half of learning. Now check what you actually remember.

Take the quiz: DBMS Architecture MCQs with Answers →

If you get a question wrong, come back to the matching section above. Use the headings as your revision map.

Frequently asked questions

What is the difference between a schema and a database? A schema is the description of the structure (the plan). The database is the actual data stored according to that plan.

Is the three-level architecture used in real databases like Oracle? Yes, as a way of thinking. Tables sit at the conceptual level, views at the external level, and data files at the internal level, and the DBMS converts between them.

Which is more important, physical or logical independence? Both matter. Physical independence lets the DBA tune storage freely. Logical independence lets the design of the tables grow without rewriting every program. Logical independence is usually harder to achieve fully.

Why do we need three levels instead of one? Because different people need different views, and each view should change on its own. Users want simple views, designers want the whole plan, and the DBA wants control over storage.

Next lecture: SQL*Plus Explained: The DBA’s Command-Line Tool. Back to the Database Administration course overview.

Written by Engnr.Dr. Muhammad Tahir Dlbar