Database Management Systems: Definition and Functions
A DBMS is the software layer that turns a collection of data into a controlled database. This lesson lists what it actually does — and, more importantly, what it refuses to do for you.
Beginner
Learning objectives
- Distinguish a database from a DBMS and from a database application
- List and explain the main functions of a DBMS
- Describe the internal components of a DBMS and what each one does
- Explain the four ACID properties in one sentence each
- Recognise the responsibilities that remain with the designer even when a DBMS is used
Prerequisites
- Data and Information
- Traditional File Archives and Their Limits
- Introduction to Databases
Introduction
The previous lesson defined a database as a permanent, shared, organised, controlled and self-describing collection of data. None of those five properties is a property of a folder. They are all guarantees, and guarantees require something that enforces them. That something is the DBMS.
The distinction matters because the two words are constantly confused, and the confusion has practical consequences. Someone who thinks "the database does it" will not write constraints. Someone who thinks "MySQL is the database" will believe that installing MySQL has given them a database, when in fact it has given them the possibility of building one.
DBMS
A Database Management System (DBMS) is the software system that manages a database: it stores the data, keeps the description of the data (the catalogue), accepts requests written in a data language, enforces the declared rules, coordinates concurrent access, and recovers a consistent state after a failure.
The database is the content; the DBMS is the engine; the application is the interface the users actually touch.
| Term | What it is | Example |
|---|---|---|
| Database | The data plus its schema | The library's socio, opera, copia, prestito tables and their rows |
| DBMS | The software that manages it | MySQL 8.0, PostgreSQL 16, Oracle, SQL Server, SQLite |
| Application | The program users interact with | ERMap's web interface, a library desk application |
| Database system | All three together | The library's complete information system |
The nine functions
A DBMS is not a single feature but a bundle of services that only make sense together. Each of the nine functions below corresponds to a problem the file archive could not solve.
1. Data definition
The DBMS accepts a Data Definition Language (DDL) with which the designer declares the structure: tables, columns, types, keys, constraints, indexes, views, users. This declaration is stored in the catalogue.
CREATE TABLE socio (
codice CHAR(6) NOT NULL,
cognome VARCHAR(60) NOT NULL,
nome VARCHAR(60) NOT NULL,
telefono VARCHAR(20) NULL,
dataIscrizione DATE NOT NULL,
PRIMARY KEY (codice)
);
Note what this statement does: it does not insert data, it inserts knowledge about data into the catalogue. From this moment the DBMS knows that codice exists, is six characters, cannot be null, and identifies a row.
2. Data manipulation
A Data Manipulation Language (DML) inserts, updates and deletes data:
INSERT INTO socio VALUES ('1042', 'Rossi', 'Anna', '055-123456', '2024-09-01');
UPDATE socio SET telefono = '055-999888' WHERE codice = '1042';
DELETE FROM socio WHERE codice = '1042';
The DBMS checks each operation against the catalogue before applying it. An INSERT with codice = NULL is rejected, not stored. This is the crucial difference from a file: the rule lives with the data, not with the program.
3. Querying
A query language — in relational systems, SQL — lets users ask questions without writing an algorithm:
SELECT s.cognome, COUNT(*) AS prestiti
FROM socio s
JOIN prestito p ON p.socio = s.codice
WHERE p.dataInizio >= '2025-01-01'
GROUP BY s.cognome
ORDER BY prestiti DESC;
The user declares what they want; the DBMS decides how to get it: which index to use, in which order to join, whether to sort in memory or on disk. This separation is the answer to the "difficulty of access" problem, and it is why the same query keeps working when the data grows by a factor of a hundred.
4. Integrity enforcement
The DBMS rejects any operation that would leave the database in an invalid state, according to the constraints declared in the catalogue:
NOT NULL— a value is mandatoryUNIQUE— no two rows may share a valuePRIMARY KEY— unique and not null, one per tableFOREIGN KEY— a value must exist in another tableCHECK— an arbitrary condition on a rowDEFAULT,AUTO_INCREMENT, domain types
Integrity is enforced centrally: whether the write comes from the web application, a batch script, a migration or a human at a SQL prompt, the same rules apply. This is the single most valuable property of a DBMS, and the one most often abandoned by developers who disable checks "just for the import".
5. Concurrency control
Many users write at the same time. The DBMS serialises the conflicting parts so that the result is as if the transactions had run one after the other, without blocking the non-conflicting parts.
Two mechanisms dominate: locking (a transaction holds a lock on the rows it modifies) and multi-version concurrency control (MVCC: readers see a consistent snapshot and are never blocked by writers). MySQL's InnoDB engine uses MVCC plus row-level locks; this is why a library with forty simultaneous desk operators does not queue.
6. Transaction management
A transaction is a unit of work that either completes entirely or has no effect at all.
START TRANSACTION;
UPDATE copia SET stato = 'disponibile' WHERE inventario = 'INV-7731';
UPDATE prestito SET dataRestituzione = CURDATE() WHERE id = 8821;
COMMIT;
If the machine loses power between the two UPDATEs, the DBMS's recovery procedure, on restart, finds the unfinished transaction in its log and undoes it. The database returns to the state before START TRANSACTION. This is the answer to the atomicity problem, and it is not something an application can implement reliably on its own — an application cannot undo a write that has already reached the disk.
7. Recovery
After a crash, a disk failure or an operator error, the DBMS restores a consistent state using two artefacts:
- the transaction log (write-ahead log), which records the intention and the effect of every change;
- backups, taken consistently (typically from a snapshot or from the log).
Recovery is not "restore last night's copy": it is "restore last night's copy and replay the log up to the last committed transaction before the failure". The result is that committed work is not lost and uncommitted work does not appear.
8. Access control and security
The DBMS authenticates users and authorises operations per object and per operation, not per file:
CREATE USER 'desk'@'%' IDENTIFIED BY '...';
GRANT SELECT, INSERT ON biblioteca.prestito TO 'desk'@'%';
GRANT SELECT ON biblioteca.vista_soci_pubblici TO 'desk'@'%';
The desk operator can read and insert loans but cannot delete them, cannot read the socio table's fiscal code, and can read a view that exposes only the columns they need. This granularity is impossible with file permissions, which are all-or-nothing.
9. Utilities and administration
Import and export, statistics for the optimiser, index rebuilding, consistency checks, monitoring, replication, point-in-time recovery, schema comparison, migration support. These are not glamorous, and they are what makes a database survivable over ten years.
Inside the DBMS
The nine functions are implemented by a small number of components. Knowing them makes it much easier to understand why a query is slow, why a lock is held, or why a SELECT sometimes reads stale data.
┌──────────────────────────────┐
SQL request ────▶│ Query processor │
│ parser → rewriter → │
│ optimiser → executor │
└──────────────┬───────────────┘
│
┌──────────────▼───────────────┐
│ Transaction manager │
│ begin / commit / rollback │
│ locks, isolation levels │
└──────────────┬───────────────┘
│
┌──────────────▼───────────────┐
│ Storage engine │
│ buffer pool, pages, B+trees │
│ write-ahead log, recovery │
└──────────────┬───────────────┘
│
files on disk
- Query processor. Parses SQL, checks it against the catalogue, rewrites it into a canonical form, and chooses an execution plan. The optimiser is the component that decides whether to use an index; it works from statistics about the data, which is why those statistics must be kept up to date.
- Transaction manager. Assigns transaction identifiers, records the begin and end of each transaction, acquires and releases locks, and enforces the isolation level.
- Storage engine. Reads and writes pages (typically 16 KB) rather than rows, keeps a buffer pool of pages in memory, organises indexes as B+ trees, and writes to the log before writing to the data files (the write-ahead rule, which is what makes recovery possible).
- Catalogue. Stores the schema itself. In MySQL it is queryable as
INFORMATION_SCHEMA.
ACID
The guarantees a transaction provides are summarised by four properties. They are introduced here because they define what the DBMS promises; Module 7 examines how they are implemented.
ACID
- Atomicity — a transaction is all-or-nothing. If it fails halfway, no partial effect remains.
- Consistency — a transaction takes the database from one valid state to another valid state; all declared constraints hold at commit time.
- Isolation — concurrent transactions do not see each other's intermediate states; the result is equivalent to some serial execution.
- Durability — once a transaction has committed, its effect survives a crash.
| Property | The question it answers | What breaks without it |
|---|---|---|
| Atomicity | "What if it fails halfway?" | A loan closed but the copy still marked as borrowed |
| Consistency | "Are the rules still satisfied?" | A loan pointing to a member who does not exist |
| Isolation | "What if two people write at once?" | Lost updates, phantom rows in reports |
| Durability | "What if the power fails after COMMIT?" | A payment recorded, then gone |
What a DBMS does not do
This is the part that most courses skip, and it is the part that decides whether a project succeeds.
| Responsibility | Whose job |
|---|---|
| Deciding what facts to store and what to leave out | The designer (Module 2) |
| Choosing the entities, attributes and associations | The designer (Module 3) |
| Deciding which constraints exist in the real world | The designer — the DBMS can only enforce the constraints you declare |
| Declaring those constraints | The designer (Modules 5, 7) |
| Deciding where transactions are needed | The designer and developer |
| Choosing indexes | The designer (Module 7), guided by EXPLAIN |
| Deciding who may see what | The designer and the security owner |
| Writing correct queries | The developer (Module 8) |
| Choosing types and sizes | The designer (Module 7) |
| Keeping statistics fresh, monitoring, backups | The DBA — but the designer must know it happens |
The pattern: a DBMS enforces rules; it does not invent them. A database with no declared constraints is a database that guarantees nothing beyond "the bytes are there". It will happily store a loan for a member who does not exist, a return date before the loan date, and two copies of the same ISBN with different titles.
A comparison, concretely
The same operation — record a loan — in the two worlds.
File archive. The program opens PRESTITI.DAT, reads it entirely, appends a record, writes it back, then opens COPIE.DAT and marks the copy as borrowed. Three failure points, no rollback, no protection against a second program doing the same thing.
Database. One transaction:
START TRANSACTION;
INSERT INTO prestito (socio, copia, dataInizio, dataScadenza)
VALUES ('1042', 'INV-7731', CURDATE(), CURDATE() + INTERVAL 30 DAY);
UPDATE copia SET stato = 'inPrestito' WHERE inventario = 'INV-7731';
COMMIT;
If socio = '1042' does not exist, the INSERT fails on the foreign key and nothing happens. If the copy is already borrowed, the constraint (or the application's check inside the same transaction) rejects it. If the power fails before COMMIT, recovery removes both effects. If two operators try simultaneously, one waits a few milliseconds for the row lock and then sees the updated state.
None of this required a single line of application code to implement the guarantee. That is the value of the DBMS — and it is only realised because the schema declares the keys and the code opens the transaction.
Which function is missing?
A small company migrates from a set of Access files to MySQL. Six months later, an audit finds the following situations. For each one, name the DBMS function that was not used and the specific action that would have prevented it.
- An invoice exists for a customer code that does not exist in the customer table.
- Two employees edited the same order at the same time; one edit vanished without any message.
- A report ran for forty minutes and blocked all order entry.
- The server's disk failed; the last backup was from the previous week, and the transactions of that week were lost.
- A junior developer connected with the application's credentials and deleted the entire
orderstable with a mistypedDELETE. - Every month, a script recalculates the
totalcolumn ofordersbecause "sometimes it is wrong". - A customer's name is spelled two different ways in two different tables.
SELECT * FROM orders WHERE customerId = 42takes 8 seconds on 2 million rows.
Show the commented solution
1. Integrity enforcement — FOREIGN KEY. orders.customerId must be declared as a foreign key referencing customers.id. Then the invoice cannot be inserted at all. Note that the audit finding is not "the application had a bug" but "the schema permitted a state that has no meaning".
2. Concurrency control + transaction management. Two UPDATEs outside a transaction, each read-modify-write, produce a lost update. The fix is a transaction with an appropriate isolation level, or an atomic UPDATE ... SET qty = qty - 1 WHERE qty >= 1 that lets the engine do the read and the write as one operation.
3. Querying / optimisation, and concurrency control. A forty-minute report blocking data entry is a symptom of a missing index (the report scans everything) combined with long-held locks or a blocking isolation level. The fixes: create the right index (function 9 statistics + optimiser), and use a consistent snapshot (MVCC) so readers do not block writers. This is Module 7 material, but the diagnosis is a DBMS-function diagnosis.
4. Recovery. Two failures: backups were too infrequent, and the transaction log was not being archived. With binary logging enabled and archived, point-in-time recovery restores the database to the last second before the failure. Note that this is the DBA's job, not the designer's — but the designer must know that the guarantee exists only if it is configured.
5. Access control. The application's user must have only SELECT, INSERT, UPDATE, DELETE on the tables it needs, and DROP must not be granted to it. Administrative operations belong to a separate account used only by administrators. A DBMS that grants everything to everyone is a DBMS whose security function is switched off.
6. Integrity + a missing single source of truth. A total that "sometimes is wrong" is a redundancy without a single maintainer. Two legitimate solutions: remove the column and compute the total when needed, or keep it and maintain it with a trigger (or generated column) so that no code path can write a wrong value. Recalculating monthly is a symptom that the maintenance mechanism does not exist.
7. Data definition + a missing single source of truth. The name is stored twice with no mechanism keeping the copies aligned. The real fix is structural: the customer's name lives in one table and is referenced, not copied. This is exactly the normalisation problem of Module 6.
8. Indexes and the optimiser. Without an index on customerId, the engine reads all two million rows. With one, it reads a few dozen. EXPLAIN is the tool that shows this; creating the index is a physical design decision (Module 7).
What this exercise teaches. Every one of the eight symptoms is a missing DBMS function, not a "database limitation". The DBMS could have prevented all of them. It did not, because nobody told it to — the schema had no constraints, the code had no transactions, the account had too many privileges, the indexes were missing. The DBMS is only as strong as the design that uses it.
Common mistakes
- "The DBMS optimises everything automatically." It optimises queries, using statistics and indexes you must create. It does not fix a bad schema.
- "A DBMS makes the application slower." Compared to reading a local file, a network round-trip costs more. Compared to a correct implementation of concurrency, atomicity and recovery, it costs far less — because you would otherwise have to build all three.
- "ACID means my data is always correct." ACID means transactions behave correctly. A transaction that writes a wrong value commits correctly. Correctness is the designer's job.
- "I can disable the foreign keys for the import and fix things later." Sometimes necessary; always dangerous. If it is done, the reconciliation must be part of the procedure, not an afterthought.
- "The DBMS is the DBA's responsibility." The DBA operates it. The designer decides what it must guarantee.
- "A view is just a saved query." A view is also a security boundary and a stability layer: it lets you expose exactly the columns a role needs, and change the underlying tables without breaking the consumer.
Summary
- A DBMS is the software that manages a database: it stores data, keeps the catalogue, accepts a data language, enforces constraints, coordinates concurrency and recovers from failures.
- Its nine functions are: data definition, data manipulation, querying, integrity, concurrency control, transaction management, recovery, access control, and utilities.
- Internally it is a query processor, a transaction manager and a storage engine, plus a catalogue.
- ACID — atomicity, consistency, isolation, durability — describes what a transaction guarantees.
- A DBMS enforces rules; it does not invent them. Every guarantee in this lesson is available only if the schema declares it and the code uses it.
- The three words to keep apart: database (content), DBMS (engine), application (interface).
Verification questions
- Give the one-sentence definitions of database, DBMS and application, and explain why "MySQL is a database" is imprecise.
- Which DBMS function makes the "difficulty of access" problem disappear, and why does the same query keep working when the data grows a hundredfold?
- Explain the difference between the transaction manager and the storage engine in terms of what a
COMMITdoes. - A team declares no
FOREIGN KEYconstraints "because the ORM validates them". What exactly have they lost? - Which of the four ACID properties is violated by a lost update, and which by a database that loses a committed payment after a power failure?
Show the answers
-
Database: the data plus its schema. DBMS: the software that manages it. Application: the program users interact with. "MySQL is a database" confuses the engine with the content — MySQL can host thousands of databases, and a database can be moved to another DBMS (with effort) while remaining the same data.
-
Querying — the declarative query language. The query states what is wanted; the optimiser re-decides how on every execution, using current statistics and indexes. When the data grows, the plan changes and the query keeps working, whereas a hand-written algorithm would have to be redesigned.
-
The transaction manager decides whether the work may commit: it holds the locks, checks the isolation level, and writes the commit record to the log. The storage engine makes it durable: it writes the changed pages to the log (write-ahead) and eventually to the data files, and it is the component that replays or undoes the log during recovery.
COMMITis "transaction manager writes the commit record to the log and releases the locks"; the actual page writes may happen later, and that is safe precisely because the log exists. -
The central enforcement of referential integrity. An ORM validates only the writes that pass through that ORM, in that application version. It does not cover batch imports, migrations, other services, ad-hoc SQL, or a bug in the ORM's own validation. They have also lost the documentation value: a foreign key in the catalogue is a statement of fact about the model, readable by any tool — including ERMap's schema importer.
-
A lost update violates isolation (two transactions interfered; the result is not equivalent to any serial order). Losing a committed payment after a power failure violates durability.
Where to go next
- Logical and Physical Data Independence: why the DBMS's three layers let you change the schema without rewriting applications.
- The ANSI/SPARC Three-Level Architecture: the standard model of those three layers.
- Relational and Non-Relational Databases: which guarantees each family actually provides.
- Student and Course: open a complete diagram in the editor and see the structures the DBMS will enforce.