What a database is
A database keeps data so that many programs and people can find it, change it and trust it. Meet the database management system (DBMS) that runs it, see how data sits in tables of rows and columns, and follow your first SQL query, SELECT name FROM books WHERE year > 2000, as the DBMS answers it step by step.
5 quick questions
Pick an answer to see why. Your score saves with your progress.
01What is a DBMS?
A database management system, such as PostgreSQL, MySQL or SQLite, is the program that looks after a database. Apps ask it for data instead of editing the files themselves.
02The front desk tries to store the year 'soon' for a new book. What does PostgreSQL do?
Every column has a type, and the DBMS checks each change against it. PostgreSQL answers: invalid input syntax for type integer: "soon". A spreadsheet cell or a plain file would keep the text.
03With the five books in the figure, what does SELECT name FROM books WHERE year > 2000 return?
WHERE keeps only the rows with a year after 2000 (2002, 2006 and 2012), and SELECT name gives back just their names. Without ORDER BY the order of the rows is not promised.
04The library app reads the books table while the front desk adds Piranesi. In PostgreSQL, what does the app's query see?
Each statement sees a consistent snapshot of the data, so a change is either all there or not there yet. In PostgreSQL readers and writers do not block each other.
05Why can an app trust that a change the DBMS called 'saved' survives a power cut?
This is write-ahead logging. PostgreSQL calls the log the WAL; after a crash it replays the log so every saved change is back.
Glossary
- Database
- An organised store of data, kept so that many programs and people can find it, change it and trust it.
- DBMS
- Database management system: the program that runs a database, such as PostgreSQL, MySQL or SQLite.
- Table
- A named set of rows that all have the same columns, such as books(id, name, year).
- Row
- One entry in a table: one thing, such as one book.
- Column
- One named fact that every row has, such as year. Every value in a column has the same type.
- SQL
- Structured Query Language: the language apps use to ask a table-based (relational) database questions and to change its data.
- Query
- A question sent to the database, such as SELECT name FROM books WHERE year > 2000.
- Write-ahead log (WAL)
- A log on disk where the DBMS records each change before it reports the change as saved, so it can recover after a crash.
Notes and sources
- Without ORDER BY, SQL does not promise any order for the rows of an answer. The figure shows them in id order; add ORDER BY id to ask for that.
- SQLite is more relaxed about types than PostgreSQL: an ordinary SQLite table would store 'soon' in an INTEGER column. A STRICT table (SQLite 3.37.0 and later) refuses it.
- SQLite is not a separate server: it is a library that runs inside the app. The app still goes through it and never edits the database file directly. SQLite also lets only one writer change a database at a time.
- Real databases rarely test every row one by one: an index can find the matching rows directly, as a later lesson shows. The answer is the same.
- Exactly what each user sees during a change depends on the isolation level. PostgreSQL’s default, Read Committed, gives each statement a snapshot taken when it starts.
- The DBMS waits for the log to reach the disk before it reports a change as saved. That is PostgreSQL’s default (synchronous_commit is on); it can be relaxed for speed, at the risk of losing the last few changes in a crash.
- The Python example uses the sqlite3 module that ships with Python. Run it and it prints Coraline, The Road, Wonder, one per line.
- PostgreSQL documentation: Tutorial, Concepts
- PostgreSQL documentation: Tutorial, Querying a Table
- PostgreSQL documentation: SELECT
- PostgreSQL documentation: Numeric Types
- PostgreSQL documentation: Concurrency Control, Introduction (MVCC)
- PostgreSQL documentation: Write-Ahead Logging (WAL)
- MySQL 8.4 Reference Manual: What is MySQL?
- SQLite: Datatypes In SQLite
- SQLite: STRICT Tables
- SQLite: About SQLite
- SQLite: Appropriate Uses For SQLite
- Python Library Reference: sqlite3
- Silberschatz, Korth and Sudarshan, Database System Concepts (7th ed.), chapter 1
Quiz Unlocks after the last step.