What databases are for

Almost every program you use keeps data that must outlive a single run: a bank's accounts, a hospital's patient charts, a university's course registrations, the lessons and quiz results on this site. Storing that data in ordinary files works for a while, then breaks down as the data grows, as more programs and people use it at once, and as mistakes become expensive. A database management system, or DBMS, is software built to solve those problems once, well, for everyone. This course teaches the relational model that underlies most databases, the SQL language used to define and query them, how to design a database so that it stays correct as it changes, and how a DBMS keeps data safe when many users work at the same time and machines fail. This first lesson explains what a database is for, what goes wrong without one, and how a DBMS is organized.

Data, databases and database management systems

A database is an organized collection of related data that models some part of the world, such as the students, courses and grades of a college. A DBMS is the software that defines, stores, retrieves and protects that data on behalf of many applications. The pair together, plus the applications that use them, is often called a database system. When people say 'the database' they may mean any of the three, so it is worth being precise: PostgreSQL, MySQL, MariaDB, SQLite, Oracle Database and Microsoft SQL Server are DBMSs; the collection of tables that a particular college keeps in one of them is a database.

A DBMS does more than store bytes. It enforces the structure and rules that the designers declared, answers questions written in a high-level query language, lets many users read and change data at once without corrupting it, recovers the data after crashes, and controls who may see or change what. Each of these is a large engineering problem. Writing them yourself for every application would be slow and error-prone, which is why almost every serious application hands its persistent data to a DBMS. Even this course site follows that pattern: its accounts, progress records and quiz attempts live in a MariaDB database, while the course text you are reading lives in files.

What goes wrong with plain files

Before DBMSs, and still today in many small offices, data lived in application-specific files: one program writes a file of student records, another writes a file of course registrations, a spreadsheet tracks fees. This file-processing approach has well-known weaknesses, and each one corresponds to a feature a DBMS provides.

The fifth row deserves emphasis because it is the hardest to get right by hand. Suppose a program transfers $500 between two accounts by subtracting from one record and adding to another. If the machine fails between the two writes, the money has vanished. A DBMS groups the two changes into a transaction that either happens completely or not at all, even across a crash, using techniques covered in lesson 10.

# A tiny file-based "database" and the inconsistency it invites
registrar = {"101": {"name": "Ava", "email": "ava@old.example"}}
fees      = {"101": {"name": "Ava", "email": "ava@old.example", "owing": 1250}}

# The registrar's program updates the email...
registrar["101"]["email"] = "ava@new.example"
# ...but the fees program keeps its own copy and is never told.

for system, data in (("registrar", registrar), ("fees", fees)):
    print(f"{system:9s} thinks Ava's email is {data['101']['email']}")
same = registrar["101"]["email"] == fees["101"]["email"]
print("consistent:", same)

registrar thinks Ava's email is ava@new.example
fees      thinks Ava's email is ava@old.example
consistent: False

Data models and schemas

A data model is a collection of concepts for describing data, the relationships between data, and the rules the data must obey. The relational model, which this course uses, describes data as tables of rows and columns; lesson 2 makes it precise. Older hierarchical and network models described data as trees and graphs of linked records. Newer document, key-value, column-family and graph models, often grouped as NoSQL, are covered briefly in lesson 12. Each model comes with a way to describe a particular database's structure, called its schema, and a way to query it.

It helps to separate the schema, which describes the structure and changes rarely, from the instance or state, which is the actual data at a given moment and changes constantly. The schema of a college database might say that every student has an identifier, a name, a home department and a count of credits earned. The instance on a given day contains the 22,000 actual students. A useful analogy is a programming language's type declaration versus the values of variables of that type: the declaration says what values can look like; the values change as the program runs.

Levels of abstraction and data independence

A key idea in database architecture is to describe data at three levels of abstraction, an arrangement known as the ANSI/SPARC three-schema architecture, proposed in the 1970s and still the standard way of thinking about database design.

The internal or physical level describes how the data is stored: files, pages, record layouts, indexes and compression. The conceptual or logical level describes what data is stored and how it is related, for the whole database, as tables, columns, keys and constraints, without saying how they are laid out on disk. The external or view level describes the part of the database that a particular user or application sees, possibly reshaped or restricted. A financial aid office might see a view of students with their fees but no grades; a professor might see a view of their own class list.

The point of the layers is data independence: the ability to change one level without changing the levels above. Physical data independence means the storage design can change, for instance by adding an index or moving a table to a faster disk, without changing the conceptual schema or any application. Logical data independence means the conceptual schema can change, for instance by adding a column or splitting a table, while existing views and the programs that use them keep working. Physical independence is achieved very well in practice; logical independence is harder, but views help a great deal. Both are big improvements over file processing, where any change to a file layout broke every program.

Database languages

A DBMS offers languages for different jobs. A data definition language (DDL) declares schemas: tables, columns, types, keys and constraints. A data manipulation language (DML) inserts, updates, deletes and queries data. A data control language grants and revokes privileges. In relational systems all three are parts of one language, SQL, the Structured Query Language, first developed at IBM in the 1970s and standardized by ANSI and ISO since 1986, with the latest revision published in 2023.

SQL is declarative: you describe what result you want, and the DBMS decides how to compute it. A query optimizer considers many possible strategies, such as which index to use and in which order to join tables, and picks one it estimates to be cheap. This is a major contrast with the file approach, where the programmer writes the loops. It also means the same query keeps working when the physical design changes, which is physical data independence again.

import sqlite3

con = sqlite3.connect(":memory:")            # a throwaway database in memory
con.execute("CREATE TABLE student (student_id INTEGER PRIMARY KEY, name TEXT NOT NULL, credits INTEGER)")
con.executemany("INSERT INTO student VALUES (?, ?, ?)",
                [(101, "Ava", 54), (102, "Ben", 30), (103, "Chloe", 87)])

# Declarative: say WHAT you want, not how to loop over the data
for row in con.execute("SELECT name, credits FROM student WHERE credits >= 50 ORDER BY credits DESC"):
    print(row)
print(con.execute("SELECT COUNT(*), AVG(credits) FROM student").fetchone())

('Chloe', 87)
('Ava', 54)
(3, 57.0)

This course runs its SQL examples in SQLite, a small embedded DBMS that is built into Python's standard library and into every smartphone, web browser and many other devices; by some estimates it is the most widely deployed database engine in the world. SQLite stores a whole database in a single file and runs inside the application rather than as a separate server. Client-server systems such as PostgreSQL, MySQL and MariaDB run as separate server processes that many applications connect to over a network. The SQL you learn transfers almost unchanged; where SQLite differs from the standard or from other systems, the lessons say so.

Inside a DBMS

A DBMS has several major components. The query processor parses SQL, checks it against the schema, and turns it into an efficient execution plan. The storage manager reads and writes pages of data on disk, keeps frequently used pages in a memory buffer pool, and maintains indexes. The transaction manager ensures that concurrent transactions do not interfere and that committed changes survive failures. The catalogue, also called the data dictionary, stores the schema itself as data, so the DBMS can describe its own tables.

Different people interact with these components. Database administrators install and tune the system, manage storage, backups and security. Database designers decide what tables, columns and constraints the database should have; that is the subject of lessons 6 to 8. Application programmers write programs that issue SQL, covered in lesson 11. End users run applications or, for analysts, write queries directly.

A short history

Commercial database systems appeared in the 1960s with IBM's IMS, a hierarchical system built partly for the Apollo program, and the network model standardized by CODASYL. In 1970 Edgar F. Codd, working at IBM, published the relational model, which proposed describing all data as relations and querying it with a high-level language independent of storage. Prototypes such as IBM's System R and Berkeley's Ingres followed, and commercial relational systems, including Oracle and IBM DB2, appeared around 1980. Codd received the Turing Award in 1981. The relational model has dominated ever since, absorbing object, XML and JSON features along the way. The 2000s brought web-scale systems that relaxed some relational guarantees to spread data across thousands of machines, and the 2010s brought distributed SQL systems that try to offer both scale and strong guarantees. Distributed databases are the subject of CMPUT-450.

Where this course goes

Lessons 2 to 5 cover the relational model and SQL in depth: relational algebra as the foundation, then SQL for defining data, querying it with joins and aggregation, and writing advanced queries with subqueries, views, window functions and recursion. Lessons 6 to 8 cover design: entity-relationship modelling, translating diagrams into tables, and normalization with functional dependencies. Lesson 9 looks under the hood at storage, indexes and how queries are executed. Lesson 10 covers transactions, concurrency and recovery. Lesson 11 covers using a database from a program safely, including SQL injection and privacy law. Lesson 12 is a capstone that designs and builds a database from requirements and surveys NoSQL systems. The discrete mathematics of CMPUT-272, especially sets, relations and logic, is assumed and will pay off immediately in lesson 2.