What a DBMS does and why files are not enough

A database management system (DBMS) is the software that keeps a collection of data and handles every request to read or change it. Programs do not open the data files themselves: they send requests, usually in SQL, and the DBMS decides how to carry each one out. Because every read and write passes through it, the DBMS can do four jobs that plain files leave to every program that touches them. It checks the rules the data must obey (integrity). It stops two writers from undoing each other’s work (concurrency control). It brings the data back to a consistent state after a crash (recovery). And it decides who may see or change what (security). It also keeps a description of the data, the schema, so that a program can ask a question without knowing how the data is laid out on disk.

This lesson reproduces two of the failures that plain files suffer, shows what a DBMS does instead, and then covers the vocabulary every database course and interview uses: schemas and the catalog, the three-schema architecture and data independence, the classes of SQL statements, the people who work with a database, and the main data models.

Five ways plain files let you down

Picture a college that keeps its records in files: a spreadsheet of students in the office, a CSV file of hostel rooms on the warden’s computer, and a JSON file of marks that a small web app reads and rewrites. That works while one person uses each file. Once the data is shared, the same five problems appear again and again:

• Redundant, inconsistent copies. A student’s phone number sits in the office spreadsheet and in the hostel file. A change made in one copy never reaches the other, and nobody can tell which copy is right.
• Rules buried in code. The web app refuses a mark above 100, but the script that imports marks from the exam portal checks nothing, so a mark of 140 gets in through the script.
• Lost updates. Two people change the same file at about the same time. Each starts from the old version, and whoever saves last silently undoes the other’s change.
• Half-written files. A program dies, or the power fails, while it is rewriting a file, and leaves something that is neither the old version nor the new one.
• All-or-nothing security. The operating system grants access to a whole file. It cannot let the warden read room numbers but not marks when both live in the same file.

A DBMS answers each problem with a service of its own:

The next four sections show four of these at work, with programs whose output you can check line by line.

Two writers, one lost update

The program below has two writers, A and B, and each adds 1 to a shared counter. Real writers run at the same time, so their steps can interleave in many orders. The program fixes one unlucky order, so that every run gives the same result: both writers read the counter before either writes it back.

"""Two writers each add 1 to a shared counter, with their steps interleaved in a fixed order."""
import json
import sqlite3

# 1. The counter in a JSON file: each writer reads the file, adds 1 and writes it back.
with open("counter.json", "w") as f:
    json.dump({"n": 0}, f)


def read_file():
    with open("counter.json") as f:
        return json.load(f)["n"]


def write_file(n):
    with open("counter.json", "w") as f:
        json.dump({"n": n}, f)


a = read_file()      # writer A reads 0
b = read_file()      # writer B reads 0, before A has written anything
write_file(a + 1)    # A writes 1
write_file(b + 1)    # B writes 1 as well: A's increment is gone
print("JSON file, read then write:     n =", read_file())

# 2. The same two steps against SQLite: a SELECT, then an UPDATE with the value Python computed.
setup = sqlite3.connect("counter.db", autocommit=True)
setup.execute("CREATE TABLE counter (n INTEGER NOT NULL)")
setup.execute("INSERT INTO counter VALUES (0)")
A = sqlite3.connect("counter.db", autocommit=True, timeout=0)
B = sqlite3.connect("counter.db", autocommit=True, timeout=0)
READ = "SELECT n FROM counter"
a = A.execute(READ).fetchone()[0]
b = B.execute(READ).fetchone()[0]
A.execute("UPDATE counter SET n = ?", (a + 1,))
B.execute("UPDATE counter SET n = ?", (b + 1,))
print("SQLite, read then write:        n =", setup.execute(READ).fetchone()[0])

# 3. The database does the arithmetic, inside a write transaction.
setup.execute("UPDATE counter SET n = 0")
A.execute("BEGIN IMMEDIATE")              # A starts a write transaction
A.execute("UPDATE counter SET n = n + 1")
try:
    B.execute("BEGIN IMMEDIATE")          # B asks to write while A's transaction is open
except sqlite3.OperationalError as e:
    print("B asks to write while A is busy:", e)
A.execute("COMMIT")
B.execute("BEGIN IMMEDIATE")              # now it is B's turn
B.execute("UPDATE counter SET n = n + 1")
B.execute("COMMIT")
print("SQLite, UPDATE ... n = n + 1:   n =", setup.execute(READ).fetchone()[0])

JSON file, read then write:     n = 1
SQLite, read then write:        n = 1
B asks to write while A is busy: database is locked
SQLite, UPDATE ... n = n + 1:   n = 2

Read the three results in turn:

1. The JSON file ends at 1, not 2. B computed its new value from a stale read, so A’s increment vanished. No error was raised; the data is simply wrong.
2. SQLite ends at 1 as well. The program read the value with one statement, added 1 in Python and wrote the result with a second statement. Each statement was correct on its own; the bug lives in the gap between them. A DBMS gives you the tools against lost updates, but a program that reads, computes and writes in separate steps, outside a transaction, still loses them.
3. UPDATE counter SET n = n + 1 ends at 2. The read and the write are now one statement that SQLite runs under its write lock, and A wraps it in a write transaction (BEGIN IMMEDIATE). While that transaction is open, B’s request to start writing is refused with “database is locked”. The program sets timeout=0 so that the refusal shows; by default Python’s sqlite3 module waits up to five seconds for the lock instead. Once A commits, B takes its turn, and both increments count.

SQLite allows any number of readers but only one writer at a time per database file, as its own documentation says. PostgreSQL and MySQL let many transactions write at once: PostgreSQL keeps them apart with row versions (multiversion concurrency control) and row-level locks, and MySQL’s InnoDB engine locks individual rows. The modules on transactions and concurrency control in this track show how.

A crash in the middle of an update

The second program raises every mark by 5 and dies halfway through, once with a JSON file and once with SQLite. Each update runs in a separate Python process that ends with os._exit(1), which stops a program the way a crash does: nothing is cleaned up, flushed or closed.

"""A program dies halfway through an update: first with a plain file, then with SQLite."""
import hashlib
import json
import os
import sqlite3
import subprocess
import sys

STUDENTS = 1000
marks = {f"21CS{i:04}": 40 + i % 50 for i in range(STUDENTS)}


def die_after(code):
    """Runs `code` in a new Python process that is killed (os._exit) once the code has run."""
    subprocess.run([sys.executable, "-c", code + "\nimport os\nos._exit(1)"], check=False)


def fingerprint(path):
    with open(path, "rb") as f:
        return hashlib.sha256(f.read()).hexdigest()


# 1. A plain file. Every mark goes up by 5, but the program dies while it rewrites the file.
with open("marks.json", "w") as f:
    json.dump(marks, f)
die_after("""
import json
marks = json.load(open("marks.json"))
text = json.dumps({roll: mark + 5 for roll, mark in marks.items()})
f = open("marks.json", "w")        # opening a file for writing empties it at once
f.write(text[: len(text) // 2])    # half of the new text is written...
f.flush()                          # ...and handed to the operating system
""")
text = open("marks.json").read()
full = len(json.dumps(marks))
print(f"file: {len(text):,} of {full:,} characters left, ending {text[-15:]!r}")
try:
    json.loads(text)
except json.JSONDecodeError as e:
    print("file: unreadable:", e.msg)

# 2. SQLite. The same update inside a transaction; the program dies before COMMIT.
db = sqlite3.connect("marks.db", autocommit=True)
db.execute("CREATE TABLE marks (roll TEXT PRIMARY KEY, mark INTEGER NOT NULL, remarks TEXT)")
rows = [(roll, mark, "-" * 300) for roll, mark in marks.items()]
db.executemany("INSERT INTO marks VALUES (?, ?, ?)", rows)
db.close()
before = fingerprint("marks.db")
die_after("""
import sqlite3
db = sqlite3.connect("marks.db", autocommit=True)
# Room for 10 pages in memory, far fewer than the update changes, so SQLite
# writes changed pages into the database file before COMMIT.
db.execute("PRAGMA cache_size = 10")
db.execute("PRAGMA cache_spill = ON")
db.execute("BEGIN")
db.execute("UPDATE marks SET mark = mark + 5")
""")
print("database file changed on disk before COMMIT:", fingerprint("marks.db") != before)
print("rollback journal next to it:", os.path.exists("marks.db-journal"))

db = sqlite3.connect("marks.db")         # the next program that opens the database
CHANGED = "SELECT count(*) FROM marks WHERE mark <> 40 + CAST(substr(roll, 5) AS INTEGER) % 50"
changed = db.execute(CHANGED).fetchone()[0]
print(f"after reopening: {changed} of {STUDENTS:,} marks changed")
db.close()
print("rollback journal still there:", os.path.exists("marks.db-journal"))
print("database file identical to before the update:", fingerprint("marks.db") == before)

file: 8,000 of 16,000 characters left, ending '"21CS0499": 94,'
file: unreadable: Expecting property name enclosed in double quotes
database file changed on disk before COMMIT: True
rollback journal next to it: True
after reopening: 0 of 1,000 marks changed
rollback journal still there: False
database file identical to before the update: True

The plain file is ruined. Opening a file for writing empties it at once, and the program died after writing half of the new text, so 8,000 of the 16,000 characters are left: the old marks are gone and only half of the new ones arrived. The JSON reader cannot even parse what is left.

The SQLite database is untouched, and not because nothing reached the disk. The program set a tiny page cache (10 pages) and allowed SQLite to spill changed pages into the database file before the transaction ended, so the file on disk really was half-updated when the program died. Before SQLite changes a page, it copies the page’s original content into a rollback journal next to the database. The next connection to read the database found that journal, wrote the original pages back and deleted it: no mark changed, and the file is byte for byte what it was before the update, as the matching SHA-256 fingerprints show.

An update that happens completely or not at all is called atomic. Careful programs can get atomic replacement of a single file by writing a new copy and renaming it over the old one: Python’s os.replace documents that a successful rename is atomic. A DBMS gives the same guarantee for a change that touches many rows of many tables, while other users keep working. SQLite does it with a rollback journal or a write-ahead log; the module on logging and recovery explains both.

Rules the database enforces

When the rules are part of the schema, the DBMS checks them on every write, whichever program sends it. This table of marks accepts a mark only for a student who exists, only between 0 and 100, and only once per student and subject:

-- The rules live in the schema, so the database checks them on every write,
-- whichever program sends it.
PRAGMA foreign_keys = ON;   -- SQLite checks foreign keys only when this is switched on

CREATE TABLE students (
  roll TEXT PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE marks (
  roll    TEXT NOT NULL REFERENCES students (roll),
  subject TEXT NOT NULL,
  mark    INTEGER NOT NULL CHECK (mark BETWEEN 0 AND 100),
  PRIMARY KEY (roll, subject)
);

INSERT INTO students VALUES ('21CS0001', 'Asha'), ('21CS0002', 'Ravi');
INSERT INTO marks VALUES ('21CS0001', 'DBMS', 78);

INSERT INTO marks VALUES ('21CS0002', 'DBMS', 140);  -- a mark out of range
INSERT INTO marks VALUES ('21CS0099', 'DBMS', 65);   -- a student who does not exist
INSERT INTO marks VALUES ('21CS0001', 'DBMS', 81);   -- a second DBMS mark for Asha
INSERT INTO students VALUES ('21CS0003', NULL);      -- a student without a name

SELECT * FROM marks;

┌──────────┬─────────┬──────┐
│   roll   │ subject │ mark │
├──────────┼─────────┼──────┤
│ 21CS0001 │ DBMS    │ 78   │
└──────────┴─────────┴──────┘

Runtime error near line 19: CHECK constraint failed: mark BETWEEN 0 AND 100
Runtime error near line 20: FOREIGN KEY constraint failed
Runtime error near line 21: UNIQUE constraint failed: marks.roll, marks.subject
Runtime error near line 22: NOT NULL constraint failed: students.name

Four bad rows were sent and four were refused, each with the name of the rule it broke: the CHECK on the range, the FOREIGN KEY to students, the UNIQUE combination of roll number and subject (the primary key), and NOT NULL on the name. Only the valid mark remains. One SQLite detail matters here: foreign keys are checked only after PRAGMA foreign_keys = ON, which every connection must run for itself, because the check is off by default for compatibility with old applications.

Who may see what

A file’s permissions apply to the whole file. A client-server DBMS grants privileges on tables, views and even single columns, to users and to groups of users. In PostgreSQL, for example, GRANT SELECT (roll, hostel, room) ON students TO warden lets the warden’s role read three columns and nothing else. SQLite has no GRANT or REVOKE at all: it is a library that reads an ordinary file, so its documentation explains that only the operating system’s file permissions apply. That is one reason SQLite describes itself as storage for one application or device rather than a store that many users share over a network.

Schema, instance and the catalog

The schema describes the data: the tables, their columns and types, the keys and constraints, the views and the indexes. It changes rarely. The instance, also called the state of the database, is the data the database holds at one moment, and every insert, update and delete changes it.

A DBMS stores its schema as data too, in tables known as the catalog or data dictionary, and you query it like any other table. SQLite keeps one row per table, index, view and trigger in sqlite_schema (older programs use its other name, sqlite_master), with the CREATE statement that would rebuild the object:

CREATE TABLE students (
  roll   TEXT PRIMARY KEY,
  name   TEXT NOT NULL,
  hostel TEXT
);
CREATE INDEX students_by_hostel ON students (hostel);
CREATE VIEW hostel_list AS SELECT hostel, name FROM students;

-- The schema itself is stored as rows of a table, and you query it like any other table.
SELECT type, name, tbl_name, sql FROM sqlite_schema;

┌───────┬─────────────────────────────┬─────────────┬──────────────────────────────────────────────────────────────┐
│ type  │            name             │  tbl_name   │                             sql                              │
├───────┼─────────────────────────────┼─────────────┼──────────────────────────────────────────────────────────────┤
│ table │ students                    │ students    │ CREATE TABLE students (                                      │
│       │                             │             │   roll   TEXT PRIMARY KEY,                                   │
│       │                             │             │   name   TEXT NOT NULL,                                      │
│       │                             │             │   hostel TEXT                                                │
│       │                             │             │ )                                                            │
├───────┼─────────────────────────────┼─────────────┼──────────────────────────────────────────────────────────────┤
│ index │ sqlite_autoindex_students_1 │ students    │                                                              │
├───────┼─────────────────────────────┼─────────────┼──────────────────────────────────────────────────────────────┤
│ index │ students_by_hostel          │ students    │ CREATE INDEX students_by_hostel ON students (hostel)         │
├───────┼─────────────────────────────┼─────────────┼──────────────────────────────────────────────────────────────┤
│ view  │ hostel_list                 │ hostel_list │ CREATE VIEW hostel_list AS SELECT hostel, name FROM students │
└───────┴─────────────────────────────┴─────────────┴──────────────────────────────────────────────────────────────┘

Three details are worth noticing. The primary key created an index of its own, sqlite_autoindex_students_1, and its sql column is empty because no statement of yours created it. The index and the view name the table they belong to in tbl_name. And the stored text is your own statement, line breaks and all, with only small normalisations such as the capitals of its first keywords.

PostgreSQL offers two catalogs. The information schema is a set of views defined by the SQL standard, so the same queries work on other systems; the view information_schema.columns, for example, lists the columns of every table you are allowed to see. The system catalogs live in the schema pg_catalog, which every PostgreSQL database contains; they are PostgreSQL’s own tables and also describe the features the standard does not know about. Its documentation warns that they are regular tables you could damage by hand, and that commands such as CREATE DATABASE are the way to change them: that command inserts a row into the catalog pg_database and creates the database on disk.

Three levels and data independence

The standard way to describe how a DBMS separates users from storage is the three-schema architecture, with one level for each kind of description:

• The external level holds the views that groups of users work with. The warden sees rooms, the office sees fees, and neither needs the rest.
• The conceptual level, or logical level, holds one schema for the whole database: every table, column, type, key and constraint.
• The internal level, or physical level, describes storage: files and pages, indexes, the layout of each record.

The DBMS maps each level onto the one below it, and those mappings are what make data independence possible, the ability to change one level without rewriting what sits above it:

• Physical data independence means you can change the internal level, by adding an index, reorganising files or moving to a faster disk, without changing the logical schema or the programs. E. F. Codd’s 1970 paper that introduced the relational model opens with exactly this aim: users and most programs should not have to know how the data is organised inside the machine, and should keep working when that organisation changes.
• Logical data independence means you can change the logical schema, by splitting a table or adding a column, without breaking the views and the programs that use them. PostgreSQL’s tutorial makes the same point about views: they hide the structure of your tables, which may change as an application evolves, behind interfaces that stay the same.

An index changes the plan, not the answer

EXPLAIN QUERY PLAN asks SQLite how it would run a query, without running it. Its detail column is the part to read: SCAN means every row of the table is visited, and SEARCH means only some rows are, found through an index.

CREATE TABLE students (roll TEXT PRIMARY KEY, name TEXT NOT NULL, hostel TEXT);
INSERT INTO students VALUES
  ('21CS0001', 'Asha', 'Kaveri'),
  ('21CS0002', 'Ravi', 'Godavari'),
  ('21CS0003', 'Meera', 'Kaveri');

-- The query, and how the engine plans to run it, before the index exists.
EXPLAIN QUERY PLAN SELECT name FROM students WHERE hostel = 'Kaveri' ORDER BY name;
SELECT name FROM students WHERE hostel = 'Kaveri' ORDER BY name;

-- A change at the storage level: a new index.
CREATE INDEX students_by_hostel ON students (hostel);

-- The same query text: a different plan, the same result.
EXPLAIN QUERY PLAN SELECT name FROM students WHERE hostel = 'Kaveri' ORDER BY name;
SELECT name FROM students WHERE hostel = 'Kaveri' ORDER BY name;

┌────┬────────┬─────────┬──────────────────────────────┐
│ id │ parent │ notused │            detail            │
├────┼────────┼─────────┼──────────────────────────────┤
│ 3  │ 0      │ 216     │ SCAN students                │
│ 11 │ 0      │ 0       │ USE TEMP B-TREE FOR ORDER BY │
└────┴────────┴─────────┴──────────────────────────────┘
┌───────┐
│ name  │
├───────┤
│ Asha  │
│ Meera │
└───────┘
┌────┬────────┬─────────┬───────────────────────────────────────────────────────────┐
│ id │ parent │ notused │                          detail                           │
├────┼────────┼─────────┼───────────────────────────────────────────────────────────┤
│ 4  │ 0      │ 62      │ SEARCH students USING INDEX students_by_hostel (hostel=?) │
│ 13 │ 0      │ 0       │ USE TEMP B-TREE FOR ORDER BY                              │
└────┴────────┴─────────┴───────────────────────────────────────────────────────────┘
┌───────┐
│ name  │
├───────┤
│ Asha  │
│ Meera │
└───────┘

Before the index, SQLite scans the whole table; after it, it searches the index for hostel = 'Kaveri'. Both times it sorts the result for ORDER BY in a temporary B-tree, and both times the answer is Asha and Meera. Not a word of the query changed. That is physical data independence, and it is the everyday kind: database administrators add and drop indexes on live systems, and the programs never notice, apart from the speed.

A view keeps the old shape, for reading

This time the logical schema changes. Hostel rooms move out of students into a table of their own, and a view with the old name puts the old shape back together:

-- Version 1 of the schema: hostel rooms are columns of students.
CREATE TABLE students (roll TEXT PRIMARY KEY, name TEXT NOT NULL, hostel TEXT, room INTEGER);
INSERT INTO students VALUES
  ('21CS0001', 'Asha', 'Kaveri', 101),
  ('21CS0002', 'Ravi', 'Godavari', 214),
  ('21CS0003', 'Meera', NULL, NULL);

-- A report that programs already run:
SELECT name, hostel, room FROM students ORDER BY roll;

-- Version 2: room allotments move to a table of their own...
CREATE TABLE student (roll TEXT PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE allotment (
  roll   TEXT PRIMARY KEY REFERENCES student (roll),
  hostel TEXT NOT NULL,
  room   INTEGER NOT NULL
);
INSERT INTO student SELECT roll, name FROM students;
INSERT INTO allotment SELECT roll, hostel, room FROM students WHERE hostel IS NOT NULL;
DROP TABLE students;

-- ...and a view keeps the old shape under the old name.
CREATE VIEW students AS
  SELECT s.roll, s.name, a.hostel, a.room
  FROM student AS s LEFT JOIN allotment AS a ON a.roll = s.roll;

-- The old report runs unchanged and returns the same rows:
SELECT name, hostel, room FROM students ORDER BY roll;

-- But a program that writes to the old shape now fails:
INSERT INTO students VALUES ('21CS0004', 'Kiran', 'Kaveri', 102);

┌───────┬──────────┬──────┐
│ name  │  hostel  │ room │
├───────┼──────────┼──────┤
│ Asha  │ Kaveri   │ 101  │
│ Ravi  │ Godavari │ 214  │
│ Meera │          │      │
└───────┴──────────┴──────┘
┌───────┬──────────┬──────┐
│ name  │  hostel  │ room │
├───────┼──────────┼──────┤
│ Asha  │ Kaveri   │ 101  │
│ Ravi  │ Godavari │ 214  │
│ Meera │          │      │
└───────┴──────────┴──────┘

Parse error near line 31: cannot modify students because it is a view

The report that read students runs unchanged and returns the same three rows, now from two tables. The last statement shows the limit. A program that inserted into students now fails, because a view in SQLite is read-only; writes through it need INSTEAD OF triggers that send each change to the right table. PostgreSQL would refuse the insert too: it updates a view automatically only when the view reads exactly one table, and this one joins two.

This is why logical data independence is the harder kind. Views hide changes from readers well, from writers only with extra work, and some changes they cannot hide at all: once a column is dropped, no view can bring its values back.

Four kinds of SQL statements

SQL statements are usually grouped into four classes:

These four names are a teaching convention, and the manuals of real systems draw the lines differently. Oracle Database’s SQL reference lists GRANT, REVOKE and TRUNCATE as DDL, counts SELECT as DML, and adds classes for session control and system control. MySQL’s manual says that TRUNCATE TABLE, although it empties a table much as DELETE does, is a DDL statement. Some textbooks give SELECT a class of its own, DQL, for data query language. For an exam, use the four classes above; for real work, check what your own DBMS does with each statement.

The difference matters most for transactions. In SQLite, DDL takes part in the transaction it runs in, so a ROLLBACK undoes a CREATE TABLE:

-- In SQLite, CREATE TABLE is part of the transaction it runs in.
BEGIN;
CREATE TABLE notices (id INTEGER PRIMARY KEY, body TEXT NOT NULL);
INSERT INTO notices (body) VALUES ('The DBMS lab moves to room 204');
SELECT name AS tables_inside_the_transaction FROM sqlite_schema WHERE type = 'table';
ROLLBACK;

SELECT count(*) AS tables_after_rollback FROM sqlite_schema WHERE type = 'table';

┌───────────────────────────────┐
│ tables_inside_the_transaction │
├───────────────────────────────┤
│ notices                       │
└───────────────────────────────┘
┌───────────────────────┐
│ tables_after_rollback │
├───────────────────────┤
│ 0                     │
└───────────────────────┘

PostgreSQL behaves the same way for most DDL. Its manual says, for example, that TRUNCATE is rolled back if the transaction around it does not commit, and the few commands that cannot run inside a transaction at all, such as CREATE DATABASE, say so on their own manual pages. MySQL behaves differently. Statements such as CREATE TABLE, ALTER TABLE, DROP TABLE and TRUNCATE TABLE end any open transaction with an implicit commit, and Oracle Database commits before and after every DDL statement. A migration script that mixes schema changes and data changes can therefore be all or nothing on PostgreSQL and SQLite, but not on MySQL or Oracle Database.

Who works with a database

Several groups of people use the same database in different ways:

• End users work through applications and never write SQL: a student checking results, a warden allotting rooms.
• Application developers write the programs that send SQL, and the tests for them.
• Database designers decide the schema: the tables, keys and constraints. The ER model and normalisation, later in this track, are their tools.
• Database administrators (DBAs) run the system: they create users, grant privileges, plan backups and recovery, watch performance and upgrade versions.
• Analysts query the data directly to answer questions, often on a read-only copy.

In PostgreSQL, each of these people, or the application acting for them, connects as a role. A role can be a single user or a group of users, can own tables and other objects, and can grant privileges on them to other roles. In a small team one person often plays several of these parts.

Data models at a glance

A data model is the set of ideas a DBMS gives you for describing data: what a record looks like, how records refer to each other and which operations exist. Six models cover nearly every database you will meet:

• Relational: tables of rows with typed columns. Keys link the tables, and SQL combines them with joins. PostgreSQL, MySQL, SQLite, Microsoft SQL Server and Oracle Database are relational.
• Document: each record is a document of fields and values, which can nest other documents and arrays. MongoDB stores documents in BSON, a binary form of JSON.
• Key-value: you store a value under a key and fetch it by that key. Redis is a widely used example, with values that can be strings, hashes, lists, sets and other structures.
• Wide-column: rows are grouped into partitions, and a hash of each row’s partition key decides which machine stores it, so one table spreads across many machines. Apache Cassandra works this way.
• Graph: nodes and the relationships between them, both with properties, and queries that follow paths. Even the SQL standard now has a part for it, Property Graph Queries (SQL/PGQ).
• Vector: each item is stored with an embedding, a long list of numbers, and queries look for the nearest neighbours by distance. The pgvector extension adds this to PostgreSQL.

For an application that records business events such as orders, bookings, marks or payments, the relational model is the sensible default: it checks rules, joins freely and runs transactions. Choose another model when one access pattern clearly needs it. The system design track compares the models by access pattern in Choosing a data model.

How widely each one is used is a different question from how good it is. In the 2025 Stack Overflow Developer Survey, the databases that the most respondents had done extensive work with in the past year were PostgreSQL (55.6% of the 26,083 who answered), MySQL (40.5%) and SQLite (37.5%), ahead of Microsoft SQL Server (30.1%), Redis (28%) and MongoDB (24%). Many projects use more than one.

Where the relational model came from

The first database systems were hierarchical or network systems: records were linked into trees or more general networks, and a program found data by following those links in the order the structure allowed. When the structure changed, the programs had to change with it. Codd’s paper of 1970 proposed something else: data as relations, which are tables, described only by their contents, so that programs depend on what the data means rather than on how it is stored. Relational systems and SQL followed. SQL is now an international standard, ISO/IEC 9075 “Database Language SQL”, revised every few years; PostgreSQL 18 measures its conformance against the 2023 revision, SQL:2023.

Exercises

CREATE TABLE rooms (room_no INTEGER PRIMARY KEY, hostel TEXT NOT NULL, label TEXT UNIQUE);
CREATE INDEX rooms_by_hostel ON rooms (hostel);

┌──────────────────────────┬────────────┐
│        index_name        │ table_name │
├──────────────────────────┼────────────┤
│ sqlite_autoindex_rooms_1 │ rooms      │
│ rooms_by_hostel          │ rooms      │
└──────────────────────────┴────────────┘

-- Return index_name and table_name for every index of the database.
SELECT name FROM sqlite_schema;

# Sample tests (public): each test builds its own schema in a fresh database, runs the learner's query and compares
# the result with the expected rows (the order of rows does not matter; column names must match).
tests:
  - name: lists the index of a primary key and an index created by hand
    setup: |
      CREATE TABLE students (roll TEXT PRIMARY KEY, name TEXT NOT NULL, hostel TEXT);
      CREATE INDEX students_by_hostel ON students (hostel);
      CREATE VIEW hostel_list AS SELECT hostel, name FROM students;
    expected:
      columns: [index_name, table_name]
      rows:
        - [sqlite_autoindex_students_1, students]
        - [students_by_hostel, students]
  - name: finds indexes on several tables and skips an INTEGER PRIMARY KEY, which needs no index
    setup: |
      CREATE TABLE courses (course_id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE, title TEXT NOT NULL);
      CREATE TABLE enrolments (
        student_id INTEGER NOT NULL,
        course_id  INTEGER NOT NULL REFERENCES courses (course_id),
        PRIMARY KEY (student_id, course_id)
      );
      CREATE INDEX enrolments_by_course ON enrolments (course_id);
      CREATE TRIGGER no_empty_code BEFORE INSERT ON courses WHEN NEW.code = '' BEGIN SELECT RAISE(ABORT, 'empty code'); END;
    expected:
      columns: [index_name, table_name]
      rows:
        - [sqlite_autoindex_courses_1, courses]
        - [sqlite_autoindex_enrolments_1, enrolments]
        - [enrolments_by_course, enrolments]

Interview questions

Fresher · What is data independence, and which kind is harder to achieve?

Data independence is the ability to change how data is described at one level without changing what sits above it. Physical data independence lets you change storage, such as indexes, file organisation or disks, without changing the logical schema or the programs. Logical data independence lets you change the logical schema, such as splitting a table or adding a column, without breaking the external views and the programs that use them. Physical independence is easier and routine: adding an index changes the query plan but not the query text or its result. Logical independence is harder, because views can rebuild an old shape for reading, but writes through a view need extra work such as INSTEAD OF triggers, and a change that removes information cannot be hidden by any view.

Fresher · Is TRUNCATE a DDL or a DML statement?

It depends on the system, and that is the point to make. TRUNCATE empties a table, as a DELETE without a WHERE clause would, but MySQL’s manual and Oracle Database’s SQL reference both classify it as DDL. In MySQL it causes an implicit commit and cannot be rolled back, and it does not fire ON DELETE triggers. In PostgreSQL it is transaction-safe: a TRUNCATE inside a transaction that rolls back leaves the rows in place. So name the textbook class, then say what your own DBMS does with it, because that decides whether a script containing it can be undone.

Key takeaways

• A DBMS keeps the data and handles every access to it, which lets it enforce integrity, control concurrency, recover from crashes and decide who may see what: jobs that plain files leave to every program.
• Lost updates come from reading, computing and writing in separate steps; let the database do the arithmetic inside a transaction (UPDATE … SET n = n + 1) and the update is safe.
• A rollback journal or write-ahead log makes a change atomic: after a crash, the database is either fully updated or exactly as it was.
• The catalog stores the schema as data: sqlite_schema in SQLite, information_schema and pg_catalog in PostgreSQL.
• The three-schema architecture separates views, the logical schema and storage. Physical data independence is routine; logical data independence is harder, because views hide changes from readers more easily than from writers.
• DDL, DML, DCL and TCL are textbook classes; real systems classify some statements differently and treat DDL inside transactions differently.