Database Architecture & Schemas

Database architecture defines how data is structured, how users interact with it, and how internal components connect. A well-designed architecture allows applications to work correctly even when the underlying storage changes.

This chapter covers schemas, instances, the three-schema architecture, and data independence — concepts that appear in almost every DBMS interview.

---

Schema vs Instance

Schema

A Schema is the blueprint or structure of the database.

It defines:

• What tables exist.
• What columns each table has.
• What data types are allowed.
• What constraints apply (keys, defaults).
• What relationships exist between tables.

The schema changes rarely — only when the application requirements change.

Example of a schema definition:

CREATE TABLE Students (
    Student_ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Age INT CHECK (Age > 0),
    Email VARCHAR(255) UNIQUE
);

This is the schema for a Students table.

Instance

An Instance is the actual data stored in the database at a particular moment.

If the schema is the blueprint, the instance is the building.

Example:

Student_ID	Name	Age	Email
101	Rahul	22	rahul@email.com
102	Priya	23	priya@email.com

This is an instance of the Students schema.

Instances change constantly as data is inserted, updated, and deleted.

---

Key Difference

Feature	Schema	Instance
Definition	Structure of the database	Data in the database
Changes	Rarely	Frequently
Analogy	Blueprint of a house	The actual house
SQL	CREATE TABLE, ALTER TABLE	INSERT, UPDATE, DELETE

---

The Three-Schema Architecture (ANSI-SPARC)

In 1975, the ANSI-SPARC committee proposed a three-level architecture for database systems. This architecture separates the user’s view of data from the physical storage.

┌─────────────────────┐
│   View Level        │  ← External Schema (User Views)
│   (multiple views)  │
├─────────────────────┤
│   Logical Level     │  ← Conceptual Schema (Table Structures)
│   (one schema)      │
├─────────────────────┤
│   Physical Level    │  ← Internal Schema (Storage Details)
│   (one schema)      │
└─────────────────────┘

1. Physical Level (Internal Schema)

The Physical Level describes how data is actually stored on the storage medium.

It defines:

• File organizations (heap, sequential, indexed).
• Index structures (B+ Trees, hash indexes).
• Data compression and encryption.
• Storage allocation (pages, blocks, extents).
• Access paths.

The physical level is managed by the database administrator (DBA).

Example:

“Student records are stored as fixed-length records in a B+ Tree clustered index on Student_ID, occupying 8KB pages on SSD storage.”

2. Logical Level (Conceptual Schema)

The Logical Level describes what data is stored and the relationships between them.

It defines:

• Tables and their columns.
• Data types and constraints.
• Primary keys, foreign keys.
• Relationships between tables.
• Views and security rules.

This is the level that database designers and developers work with.

Example:

“The Student table has columns: Student_ID (INT, PK), Name (VARCHAR), and Email (VARCHAR, UNIQUE).“

3. View Level (External Schema)

The View Level describes only the part of the database that a particular user sees.

It defines:

• Subsets of the database relevant to specific users.
• Custom views that hide sensitive or irrelevant data.
• User-specific formatting and access rights.

Multiple views can exist for the same database.

Example:

A college database:

• Student View: Sees their own grades and attendance.
• Professor View: Sees grades of their students, student contact info.
• Admin View: Sees everything, including salary data.
• Registrar View: Sees enrollment statistics, but not individual grades.

---

Why Three Layers?

1. Data Independence

Changes at one level should not affect higher levels.

2. Multiple Views

Different users see only the data relevant to them.

3. Security

Sensitive data (passwords, salaries) can be hidden from unauthorized users.

4. Complexity Hiding

Application developers work with a simple view, unaware of storage internals.

---

Data Independence

Data Independence is the ability to change the schema at one level without affecting the schema at the next higher level.

This is one of the most important features of a DBMS.

---

Physical Data Independence

Physical Data Independence allows changing the physical storage without affecting the logical schema.

What can change:

• Moving from HDD to SSD.
• Adding or removing indexes.
• Changing file organization from heap to B+ Tree.
• Changing compression algorithms.
• Moving data to a different server.

What is NOT affected:

• Table structures (the logical schema).
• Application queries.
• User views.

Real-world example:

A bank migrates its database from on-premise spinning disks to AWS SSD storage.

The logical schema (Account table, Transaction table) remains unchanged.

Applications continue to work without any code changes.

How easy is it?

Physical data independence is relatively easy to achieve.

The DBMS hides storage details behind an abstraction layer.

---

Logical Data Independence

Logical Data Independence allows changing the logical schema without affecting external views.

What can change:

• Adding a new column to a table.
• Splitting one table into two.
• Merging two tables into one.
• Changing a column’s data type.
• Adding or removing a constraint.

What is NOT affected:

• User views (as long as the view’s columns still exist).
• Existing applications (if they use views).

Real-world example:

A university adds a Middle_Name column to the Student table.

The existing view StudentGrades (which shows Student_ID and Grade) is unaffected.

Applications using StudentGrades continue to work.

How easy is it?

Logical data independence is much harder to achieve.

Application code often references column names directly.

Changing a table structure may break queries in hundreds of places.

Views provide a buffer, but not all applications use views.

---

Comparison

Aspect	Physical Independence	Logical Independence
What changes	Storage, hardware, indexes	Table structure, columns
Affects	Physical schema only	Logical schema only
Ease	Easy	Difficult
Why hard?	DBMS handles abstraction	Applications depend on schema

---

Real-World Architecture: Web Application

A modern web application uses a 3-tier architecture:

Browser (View Level)
     ↓
Application Server (Logic)
     ↓
Database Server (Physical + Logical)

• Browser: Displays data to the user (similar to View Level).
• App Server: Runs the business logic, communicates with database (similar to Logical Level).
• Database Server: Stores and retrieves data (Physical Level).

This separation allows independent scaling and maintenance.

---

Quick Review

Schema       = Blueprint (rarely changes)
Instance     = Actual data (changes constantly)

Physical     = HOW data is stored (files, indexes, pages)
Logical      = WHAT data is stored (tables, columns, constraints)
View         = WHAT user sees (subset of logical)

Physical Independence = Change storage without changing tables
Logical Independence  = Change tables without changing views

---

Interview Deep Dive

---

Key Takeaways

• Schema is the structure; instance is the actual data.
• The three-schema architecture has Physical, Logical, and View levels.
• Physical level describes how data is stored.
• Logical level describes what data is stored.
• View level shows only relevant subsets to users.
• Physical data independence protects against storage changes.
• Logical data independence protects against structural changes.
• Logical independence is harder to achieve than physical independence.