Database management systems (DBMS)

You type a query and press enter. A hundred rows come back in 40 milliseconds.

Nothing you wrote said which file to open. Nothing said where on the disk those rows sit, or how to stop another user changing them while you read. Some piece of software did all of that for you.

That software is the database management system, written DBMS.

A DBMS is the program that sits between an application and the stored data. Applications never open the stored files themselves. They ask the DBMS, and the DBMS does the work.

The six jobs of a DBMS

Defining. You describe the data: the tables, the columns, the data types, and the rules each column must obey. The DBMS saves that description in the catalog. In SQL this is CREATE TABLE.

Constructing. The DBMS writes the data onto storage and chooses the layout. You never pick a file name or a byte position on disk.

Manipulating. Reading, adding, changing, and removing data. In SQL this is SELECT, INSERT, UPDATE, and DELETE.

Sharing. Many users and many programs work on the same data at the same time. The DBMS keeps their work from colliding.

Protecting. This covers two different dangers. Security protection stops a user reading or changing what they are not allowed to. Failure protection keeps the data correct when a machine crashes in the middle of a change.

Maintaining. A database lives for years while the business around it changes. The DBMS lets you add a column or build an index without rewriting the applications.

The old file processing approach could do the first three, badly. It could not do the last three at all.

What happens inside when you run a query

A DBMS is not one block of code. It is a few parts with clear jobs, and knowing them makes database behaviour much easier to predict.

The query processor takes your SQL text and checks that it is valid. It looks up every table and column name in the catalog to confirm they exist and that you are allowed to use them.

It then plans how to run the query. The same result can usually be produced in several ways, and some are thousands of times faster than others. The part that picks the plan is called the query optimizer.

This is why SQL is called declarative. You say what you want, and the DBMS decides how to get it.

The storage manager carries out the plan against the disk. It reads and writes fixed size blocks called pages.

It also keeps recently used pages in memory, in an area called the buffer pool. A query that finds its pages already in the buffer pool is far faster than one that has to read from disk.

The transaction manager keeps the data correct while many users work at once. It does this with two tools.

Locks stop two users changing the same row at the same moment. A log records every change before it is applied, so a crash can be repaired by replaying or undoing the log.

The catalog holds the description of everything: tables, columns, types, constraints, indexes, and user permissions. Every other part reads it.

A transaction, defined once

A transaction is a group of operations treated as one unit. Either all of them take effect, or none of them do.

Moving money between two accounts is the standard example. The subtraction and the addition are one transaction, so a crash between them cannot leave the money missing.

You will meet the four guarantees a transaction gives, known as ACID, in the lesson on relational and non-relational databases.

Data independence

This is the quiet benefit that makes everything else possible, and it has a precise meaning.

Data independence is the ability to change one level of the description without changing the level above it. It comes in two kinds.

Physical data independence means you can change how the data is stored without changing the tables. Add an index, move the files to faster disks, or change the page size, and every query keeps working.

Logical data independence means you can change the tables without changing applications that do not use the changed part. Add a column to Customers, and a report that reads only names and cities carries on unaffected.

File processing systems had neither. The layout lived in the program, so any change to storage meant editing and retesting every program that touched the file.

Who talks to a DBMS

Application programs send queries on behalf of end users. A shopping website is an application program, and the shopper never sees SQL.

Database administrators, usually shortened to DBAs, manage the database itself. They create users, grant permissions, plan backups, and watch performance.

Database designers decide what tables exist and how they connect. That is the job this course teaches.

Analysts write queries directly to answer business questions.

Key Takeaways

• A DBMS is the software between applications and stored data. No application touches the stored files directly.
• Its six jobs are defining, constructing, manipulating, sharing, protecting, and maintaining the data.
• The query processor checks and plans a query, and its optimizer chooses how to run it. This is why SQL states what you want rather than how to get it.
• The storage manager moves pages between disk and the buffer pool in memory.
• The transaction manager uses locks and a log so that many users can work at once and a crash can be repaired.
• A transaction is a group of operations where either all take effect or none do.
• Data independence lets you change storage without changing tables, and change tables without breaking applications that do not use the changed part.

A DBMS is general. It will manage whatever shape of data you describe to it. The next lesson is about one particular shape, the one this entire course is built on: the table.