Data Models & Three-Schema Architecture
Press Next → or use ← → arrow keys
The Story — Three Ways to Draw One Family
Cousin B draws a web of arrows — people linked in every direction. It captures the complexity, but becomes a maze to navigate.
Cousin C draws a simple table — one row per person, columns for name, parent, spouse. Less pretty, but trivial to search and change.
A data model is not the data itself — it is the blueprint for how data is structured and related. The same facts modeled as a tree, a graph, or tables carry different consequences for decades.
What Exactly Is a Data Model?
A data model = concepts to describe structure (data types, relationships, constraints) + operations to retrieve and update. Models sit at three levels of abstraction:
The three representational models — hierarchical, network and relational — and the architecture that wraps them for data independence.
Schema vs Instance
| Term | What it is | How often it changes |
|---|---|---|
| Schema | The database description — structure and rules; the blueprint | Rarely |
| Instance | The actual data at a moment — the database state | Every insert / update / delete |
| Relationship | One schema supports infinitely many instances over time | |
The schema is the architect's blueprint; the instance is who lives in the building today. Same blueprint, endless changes of residents.
The Three Record-Based Models — A Map
Hierarchical = tree of pointers, Network = graph of pointers, Relational = tables linked by values. Pointers tie data to storage; values set it free.
The Hierarchical Model — A Tree
The Network Model — A Graph
The Relational Model — Tables Linked by Values
In 1970, E. F. Codd proposed: forget pointers and trees — store everything as relations (tables), and express relationships through shared values.
| roll 🔑 | name | dept |
|---|---|---|
| 1 | Riya | CSE |
| 2 | Aman | ECE |
| 3 | Neha | CSE |
| roll 🔗 | course | grade |
|---|---|---|
| 1 | DBMS | A |
| 1 | OS | B |
| 3 | DBMS | A |
The M:N student–course relationship is expressed purely by matching ENROLL.roll to STUDENT.roll — the student's name is never copied. This is why relational dominated: simple tables, a rigorous set-theory foundation, declarative SQL, and the strongest data independence of the three.
Core Relational Vocabulary
| Term | Meaning | Example |
|---|---|---|
| Relation | A table | STUDENT |
| Tuple | A row — one instance | (1, Riya, CSE) |
| Attribute | A column | name |
| Domain | Allowed values for an attribute | dept ∈ {CSE, ECE, …} |
| Degree | Number of attributes (columns) | 3 |
| Cardinality | Number of tuples (rows) now | 3 |
| Primary Key | Attribute(s) uniquely identifying a tuple | roll |
| Foreign Key | Attribute referencing a key in another relation | ENROLL.roll |
Degree counts columns (fixed by the schema). Cardinality counts rows (changes with every insert or delete).
Hierarchical vs Network vs Relational
| Property | Hierarchical | Network | Relational |
|---|---|---|---|
| Structure | Tree | Graph | Tables |
| Relationships | 1:N only | 1:N & M:N | 1:1, 1:N, M:N via keys |
| Links by | Pointers | Pointers | Matching values |
| Access | Navigate from root | Navigate pointers | Declarative SQL |
| Data independence | Very low | Low | High |
| Redundancy | High | Moderate | Controlled |
| Ease of use | Moderate | Hard | Easy |
| Theory basis | None | None | Set theory / algebra |
| Systems | IBM IMS | IDMS, CODASYL | MySQL, PostgreSQL, Oracle |
| Era | 1960s | Late 60s–70s | 1970 → today |
The Three-Schema Architecture (ANSI/SPARC, 1975)
Many external views · one conceptual schema · one internal schema. Separating them is exactly what makes data independence possible.
The Two Mappings & a Request's Journey
Data Independence — The Whole Point
The capacity to change the schema at one level without changing the schema at the next higher level. Two shields deliver it:
Schema vs Instance — A Concrete Look
STUDENT(roll, name, dept)
ENROLL(roll, course, grade)
# constraints
ENROLL.roll → STUDENT.roll # FK
dept ∈ {CSE, ECE, ME}# STUDENT
(1, Riya, CSE)
(2, Aman, ECE)
(3, Neha, CSE)
# ENROLL
(1, DBMS, A) (1, OS, B) (3, DBMS, A)
# tomorrow: add a student →
# the schema above is unchangedThe blueprint stays put while the data churns beneath it — insert, update, delete all day, and the schema never moves.
Relational Model & Independence in Python
A conceptual schema (tables), an external schema (a VIEW), then we prove both kinds of independence.
# CONCEPTUAL SCHEMA — the whole DB as relations
cur.executescript("""
CREATE TABLE student(roll INTEGER PRIMARY KEY, name TEXT, dept TEXT);
CREATE TABLE enroll(roll INTEGER, course TEXT, grade TEXT,
FOREIGN KEY(roll) REFERENCES student(roll));
""")
# EXTERNAL SCHEMA — a view for 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'""")
# LOGICAL independence: add a column the view never used
cur.execute("ALTER TABLE student ADD COLUMN email TEXT") # view unaffected ✔
# PHYSICAL independence: add an index (pure storage choice)
cur.execute("CREATE INDEX idx_enroll_roll ON enroll(roll)") # results identical ✔Adding a column (a conceptual change) didn't break the view → logical independence. Adding an index (an internal change) didn't alter results → physical independence. The three-schema architecture made both possible.
Key Takeaways — Rules 1 to 4
Key Takeaways — Rules 5 to 7
Pointers tie data to storage; values set it free. Layer three schemas over those values and the database can evolve for decades without endless rewrites.
Why Cousin C Sleeps Soundly
This quiet resilience is the modern database. Everything next — ER modelling, keys and normalization, relational algebra and SQL — builds directly on data models, the three-schema architecture and data independence.
🧬 End of tutorial · Press ← to review, or click Restart