Data Models in DBMS: Hierarchical, Network, Relational, the Three-Schema Architecture & Data Independence

The Story That Explains Data Models

A data model is the lens through which we describe data, its relationships, its meaning, and the rules it must obey. Choosing a model shapes everything — how you store data, how you query it, and how painful change will be later. Let's build the whole picture from the ground up.

What Exactly Is a Data Model?

Formally, a data model is a collection of concepts used to describe the structure of a database — the data types, relationships, and constraints — plus a set of operations for retrieving and updating that data. Data models are grouped by how close they sit to the machine.

The Three Record-Based Models — A Map

Three representational models defined the history of databases. They appeared in roughly this order, each fixing a weakness of the one before.

The Hierarchical Model

In the hierarchical model, data is organized as a tree. Each record (except the root) has exactly one parent, and a parent may have many children — a strict one-to-many (1:N) relationship. To find any record you must travel down from the root.

The Network Model

The network model fixes the tree's biggest flaw. Instead of a tree, data is a graph: a record can have many parents (owners) and many children (members), so many-to-many (M:N) relationships are expressed directly. Relationships are called sets, standardized by the CODASYL DBTG committee.

The Relational Model

In 1970, E. F. Codd proposed something radical: forget pointers and trees — store everything as relations (tables). A relation is a set of tuples (rows), each with the same attributes (columns). Relationships are not pointers but shared values — a value in one table that matches a key in another.

The Same Data, As Relations

The M:N relationship that needed a pointer maze in the network model is here expressed by a plain matching value: ENROLL.roll matches STUDENT.roll. No pointers, no duplication of the student's name.

Hierarchical vs Network vs Relational

The Three-Schema Architecture

Knowing the relational model is great — but how does a DBMS keep users insulated from storage details? The answer is the ANSI/SPARC Three-Schema Architecture (1975), which separates a database description into three levels of abstraction. This separation is the engine that makes data independence possible.

The Two Mappings

The three levels are glued together by two mappings. These mappings are what let the DBMS translate a user's request, expressed against a view, all the way down to bytes on disk — and the answer back up.

Data Independence — The Whole Point

Data independence is the capacity to change the schema at one level without having to change the schema at the next higher level. The three-schema architecture exists precisely to deliver it. There are two kinds.

Schema vs Instance — A Concrete Look

Hands-On — Relational Model & Data Independence in Python

Let's make the abstract concrete. Using sqlite3, we build the relational tables, create a view (an external schema), then demonstrate logical and physical data independence — proving the view survives change.

Building the Relational Schema and an External View

import sqlite3

conn = sqlite3.connect(':memory:')   # temporary in-memory database
cur  = conn.cursor()

# --- CONCEPTUAL SCHEMA: the whole database, as relations ---
cur.executescript("""
    CREATE TABLE student (
        roll INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        dept TEXT NOT NULL
    );
    CREATE TABLE enroll (
        roll   INTEGER,
        course TEXT,
        grade  TEXT,
        FOREIGN KEY (roll) REFERENCES student(roll)
    );
""")

cur.executemany("INSERT INTO student VALUES (?,?,?)",
    [(1, 'Riya', 'CSE'), (2, 'Aman', 'ECE'), (3, 'Neha', 'CSE')])
cur.executemany("INSERT INTO enroll VALUES (?,?,?)",
    [(1, 'DBMS', 'A'), (1, 'OS', 'B'), (3, 'DBMS', 'A')])

# --- EXTERNAL SCHEMA: a view for the CSE faculty (only what they need) ---
cur.execute("""
    CREATE VIEW cse_grades AS
    SELECT s.name, e.course, e.grade
    FROM   student s JOIN enroll e ON s.roll = e.roll
    WHERE  s.dept = 'CSE'
""")

print("CSE faculty view:")
for row in cur.execute("SELECT * FROM cse_grades"):
    print("  ", row)

Proving Logical Data Independence

Now change the conceptual schema — add a brand-new column. The faculty's external view does not mention the new column, so it keeps working unchanged.

# Change the CONCEPTUAL schema: add a column the view never referenced
cur.execute("ALTER TABLE student ADD COLUMN email TEXT")

# The external view is untouched — logical data independence in action
print("Same view, after adding student.email:")
for row in cur.execute("SELECT * FROM cse_grades"):
    print("  ", row)

Proving Physical Data Independence

# Change the INTERNAL schema: add an index (a pure storage decision)
cur.execute("CREATE INDEX idx_enroll_roll ON enroll(roll)")

# Conceptual schema and query results are IDENTICAL — only speed changed
print("Query result is unchanged; only the access path improved.")
conn.close()

Bringing It Together

Golden Rules