DBMS Architecture & Data Independence

By the end of this lesson you will be able to draw the three-schema architecture, explain logical and physical data independence with examples, name the main parts inside a DBMS, and compare 1-tier, 2-tier and 3-tier applications.

The problem architecture solves

A college database has many users. A student wants to see only her own marks. The exam cell wants every mark. The DBA wants to move the data to a faster SSD. If each change forced every program to be rewritten, nobody could change anything.

The answer is to separate how data looks to users from how it is stored. Think of a railway ticket counter. Passengers see a simple window: give your details, get a ticket. Behind the window are reservation charts, computers and rules. The railway can replace the computers without changing what passengers do at the window.

The three-schema architecture

The three-schema architecture, proposed by the ANSI/SPARC study group in the 1970s, describes a database at three levels. A schema is a description of the structure of data, not the data itself.

  Student app        Exam cell app        Library app
       |                   |                   |
+--------------+   +--------------+   +--------------+
| External     |   | External     |   | External     |   EXTERNAL LEVEL
| view 1       |   | view 2       |   | view 3       |   (many user views)
+--------------+   +--------------+   +--------------+
        \                 |                 /
         \   external/conceptual mapping   /
          +-------------------------------+
          |      Conceptual schema        |              CONCEPTUAL LEVEL
          | all tables, columns, keys,    |              (one, for the
          | relationships and constraints |               whole database)
          +-------------------------------+
                         |
            conceptual/internal mapping
                         |
          +-------------------------------+
          |       Internal schema         |              INTERNAL LEVEL
          | files, pages, indexes,        |              (physical storage)
          | compression, record layout    |
          +-------------------------------+
                         |
                   [ disk / SSD ]

1. Internal level (physical level): how data is really stored. File layout, page size, which columns have indexes, whether data is compressed.
2. Conceptual level (logical level): what data exists and how it is related, for the whole organisation. Table names, columns, data types, keys and constraints. There is exactly one conceptual schema.
3. External level (view level): what each group of users sees. There can be many external schemas, each showing only part of the data.

The DBMS keeps two mappings that translate between levels. When a student app asks for "my marks", the DBMS maps that request to conceptual tables, then to physical pages on disk.

External views in SQL

In SQL, an external schema is usually built with views. A view is a saved query that looks like a table. Step 1: create a conceptual table. Step 2: create a view that hides the salary column. Step 3: query the view.

CREATE VIEW staff_directory AS
SELECT first_name, last_name, department, job_title
FROM employees;

SELECT first_name, department, job_title
FROM staff_directory
WHERE department = 'Sales';

first_name | department | job_title
-----------+------------+----------------
Simran     | Sales      | Sales Manager
Rahul      | Sales      | Sales Executive
Nisha      | Sales      | Sales Executive
(3 rows)

You should see names and job titles, but no salaries. The employees table (conceptual level) has a salary column, but users of this view (external level) never see it. Views are covered in detail in Views.

Data independence

Data independence means you can change the schema at one level without changing the schema, or the programs, at the level above it. It comes in two kinds.

Physical data independence is easier to achieve, and real systems achieve it almost completely. You add an index and every query keeps working, only faster.

Step 1: see physical data independence yourself. Run a query, add an index, and run the same query again.

SELECT first_name, city FROM customers WHERE city = 'Rohtak';

CREATE INDEX idx_customers_city ON customers (city);

SELECT first_name, city FROM customers WHERE city = 'Rohtak';

first_name | city
-----------+-------
Aarav      | Rohtak
Vikram     | Rohtak
(2 rows)

first_name | city
-----------+-------
Aarav      | Rohtak
Vikram     | Rohtak
(2 rows)

You should see exactly the same result twice. The storage changed, the query did not. On a table with millions of rows the second query would also be much faster.

Logical data independence is harder. Adding a column is usually safe. But if you split a table, programs that used the old table break, unless you create a view with the old name that joins the new tables back together. That view is the "mapping" doing its job.

Inside a DBMS

A DBMS is not one program but several cooperating parts:

The data dictionary is "data about data" (metadata). You can query it like any other table. In MySQL it is exposed through information_schema:

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'shop' AND table_name = 'products'
ORDER BY ordinal_position;

+------------+--------------+-----------+
| TABLE_NAME | COLUMN_NAME  | DATA_TYPE |
+------------+--------------+-----------+
| products   | product_id   | int       |
| products   | product_name | varchar   |
| products   | category     | varchar   |
| products   | price        | decimal   |
| products   | stock        | int       |
+------------+--------------+-----------+

SQLite keeps its catalog in a table called sqlite_schema, so this MySQL query does not run in the browser.

1-tier, 2-tier and 3-tier designs

The word tier means a separate layer of the system, often on a separate machine.

1-tier:  [ user + app + DBMS on one computer ]          e.g. SQLite in a desktop app

2-tier:  [ client app ] <----- SQL -----> [ DB server ]   e.g. a Tally-style desktop client

3-tier:  [ browser ] <-- HTTP --> [ app server ] <-- SQL --> [ DB server ]
                                   e.g. a college result website

• In 1-tier, everything runs on one machine. Good for learning and small tools.
• In 2-tier, the client program talks directly to the database server. Simple, but every client needs database access, which is a security risk.
• In 3-tier, the browser never talks to the database. A middle application server checks the user, runs the business rules and sends SQL. Almost every website works this way.

Common mistakes

• Mixing up the two kinds of independence. Physical = change storage, keep tables. Logical = change tables, keep views and programs.
• Thinking there are many conceptual schemas. There is one conceptual schema and one internal schema, but many external schemas.
• Saying a view stores a copy of data. A normal view stores only its query. The rows come from the base tables each time.

Key points

• The three-schema architecture has external, conceptual and internal levels.
• Mappings between levels let one level change without breaking the level above.
• Physical data independence: change storage without changing the conceptual schema.
• Logical data independence: change the conceptual schema without changing external views.
• Views implement external schemas; the data dictionary stores metadata.
• Modern web apps use a 3-tier design: browser, application server, database server.

CREATE VIEW placement_students AS
SELECT roll_no, name, branch, cgpa
FROM students;