Core CS · DBMS

Seven guarantees, four jobs, three users, one honest cost

Every advantage of a DBMS is one rule moved out of your code into one place nobody can skip. You will follow a single fee payment through those rules, meet the four jobs that put them there and the three kinds of user who live with them, and see the days when a plain file is the better answer.

two clerks, one shared file · 20,000 paid, 8,000 recorded

→

two clerks, one DBMS · 20,000 paid, 20,000 recorded

Watch two clerks lose a payment →

01 The idea

Every advantage is a rule you stopped writing

The file-system problems in the previous lesson all came from the same source: the rules lived inside whichever program happened to be writing. Two programs meant two copies of the rule, and sooner or later one copy was out of date. A DBMS does not fix those problems one at a time. It fixes them once, by putting the rules underneath every program at the same moment.

That is why the advantages read like seven unrelated virtues and are really one move seen from seven angles. fee_paid >= 0 written as a CHECK constraint is integrity. The same constraint stops a careless import script producing a negative balance that contradicts the receipts, which is consistency. It is declared once in the schema instead of once per application, which is an enforced standard. And it holds for the clerk, the student portal and the nightly job at the same time, which is what sharing means. One line, four advantages, and no application code.

A DBMS is not a better filing cabinet. It is the one place a rule can be written so that no program, no script and no person gets past it.

ConstraintA rule stored in the schema, not in an app. CHECK (fee_paid >= 0) is tested on every insert and update, from every client, including one typed by hand in a terminal.

TransactionA group of statements that either all take effect or none do. COMMIT is the moment the database promises the change survives a power cut; before it, ROLLBACK erases the lot.

PrivilegePermission to do one thing to one object, given with GRANT and taken back with REVOKE. The database enforces it, so connecting with a different tool does not get you round it.

02 Worked example

One payment, five promises kept

Asha pays 12,000 rupees of her fee at the counter. A clerk opens the fees app and clicks Record payment. The app sends exactly one statement. Here is the table it lands on, and the statement itself.

CREATE TABLE student (  sid       TEXT PRIMARY KEY,  name      TEXT NOT NULL,  fee_paid  NUMERIC(9,2) NOT NULL DEFAULT 0            CHECK (fee_paid >= 0));GRANT SELECT ON student TO clerk;GRANT UPDATE (fee_paid) ON student TO clerk;-- what the clerk's app sends when Asha pays 12000:UPDATE student SET fee_paid = fee_paid + 12000 WHERE sid = 'S1';

clerk on lines 7 and 8 is a database role, not a person. Every counter clerk logs in as a member of it, so the permission is written once and revoked once. Line 8 is column-level: the role may change fee_paid and nothing else on that table. Now follow the statement from the click to the disk.

1 · App sends SQLone UPDATE, in the one language every client here speaks

→

2 · Permissionmay this login write fee_paid? asked before the row is touched

→

3 · ConstraintsNOT NULL, NUMERIC(9,2), CHECK (fee_paid ≥ 0), straight from the schema

→

4 · Log, then committhe change reaches the log on disk before COMMIT returns

→

5 · One row for allportal, report and the next clerk read this row, not a copy of it

The highlighted stop is the one students underrate. CHECK (fee_paid >= 0) is four words, and it fires for the clerk's app, for the accounts team's import script, for a developer who connects to production by accident, and for you at 2am in a terminal. Write the same rule in application code and you have written it for exactly one of those four.

Five stops, and the application supplied only the first one. Everything after it came from the schema and the server. Stop 2 is security. Stop 3 is integrity. Stop 4 is what makes backup and recovery possible at all, because a log entry written before the commit returns is what lets a restore replay the payment after a crash. Stop 5 is controlled redundancy, consistency and sharing at once, because there is only one copy to be consistent about. The seventh, enforced standards, is stop 1: NUMERIC(9,2) is what fee_paid means for the portal, the accounts export and the nightly job, and none of them gets a vote.

03 Mechanics

Who put each of those rules there

The five stops did not appear on their own. Somebody chose the column type, somebody wrote the GRANT, somebody decided the backup runs at 2am, and somebody clicked the button. Those are different jobs with different outputs, and an interviewer will ask you to tell them apart.

The four professional roles

Role	The question they answer	What they hand over	The SQL they write
System analyst	What does the college need to record, and what should the screens do?	Requirements, and specifications for the canned transactions clerks will run all day	None. They write documents and talk to people.
Database designer	Which tables, columns, keys and constraints express that?	The schema and the ER diagram, including `NUMERIC(9,2)` and `CHECK (fee_paid >= 0)`	DDL: `CREATE TABLE`, `ALTER TABLE`
Application programmer	What statements does the app send, and where do the transactions begin and end?	The fees app, and the single UPDATE you traced in section 02	DML: `INSERT`, `UPDATE`, `DELETE`, and `SELECT`, which many books count as DML and some split out as DQL. Plus the TCL around it: `BEGIN`, `COMMIT`, `ROLLBACK`
DBA	Who may connect, who may see what, and what happens when it breaks?	Roles and grants, the backup schedule and the restore drill, indexes, tuned settings	DCL: `GRANT`, `REVOKE`. Then admin work that is DDL or vendor tooling: `CREATE INDEX`, backups, restores

And the three kinds of end user

Kind of end user	How they reach the data	In this lesson	What they know about the schema
Naive, also called parametric	A screen someone else built, running the same few canned transactions all day with different parameters	The fees clerk clicking Record payment	Nothing. They do not know a table exists.
Sophisticated	Ad-hoc SQL or a reporting tool, writing their own queries	The finance officer totalling this term's collections	Enough to read a schema diagram and write a join.
Specialised	An application they build themselves, with data needs the plain relational toolkit was not designed for	The team building the campus map, storing shapes and asking “what is within 200 metres of this hostel?”	They know it well and often extend it.

The line that gets missed. A designer decides what the data means; a DBA decides who may touch it and keeps it alive. On a five-person team one person does both, and the interviewer still wants two separate answers. And naive does not mean unskilled. The clerk may be the fastest person in the building at that screen. Naive means the database is invisible to them, which is the application programmer's job done properly.

05 Cheat sheet

Seven advantages, and what each one costs

Advantage	What it actually means	What it costs you
Controlled redundancy	One fact lives in one place. Copies exist only where you deliberately put them, such as an index or a replica, and the database maintains those.	A read that once touched one file now joins several tables.
Consistency	If a fact is stored once, no two readers can be told different things about it.	You give up the freedom to let one app keep its own slightly different version.
Sharing	Many users write at the same time, and the database, not your code, decides the order.	Locks and waits. A blocked transaction is the price of not losing one.
Enforced standards	Names, types and formats are declared once in the schema and every client obeys them.	Changing a column is a migration, coordinated across every app that reads it.
Integrity	`CHECK`, `NOT NULL` and foreign keys are tested on every write from every client.	Every insert and update pays for the check before the row lands.
Security	`GRANT` and `REVOKE` per user, per table, per column. Views hand out a slice and hide the rest.	Somebody has to own and review those grants, and that somebody is a job.
Backup and recovery	A committed transaction survives a crash, and with log archiving switched on you can restore to a chosen moment.	Disk for logs and backups, and a restore drill that has to be rehearsed to be real.

Overhead is a server, not SQLThe weight people mean by "a DBMS is heavy" is the server process, the connection pool, the migration discipline and the person who owns it. SQLite has none of those and is still a full DBMS with transactions and constraints. Separate the two before you argue about it.

Every guarantee is paid on the write pathA constraint is a check before the row lands, a lock is a wait before the row is touched, and a commit is a disk flush before the call returns. Nothing in the middle column is free, and all of it is billed at write time.

Recovery is a rehearsal, not a settingA backup nobody has ever restored is a hypothesis. The textbook advantage is "backup and recovery", and the half that is actually an advantage is the recovery. Only the DBA can tell you when it was last tested.

06 Where & why

Where the guarantees pay, and where they are pure cost

A DBMS is a trade, not a virtue. You pay latency on every write, a schema you have to migrate, and a person to look after it. You get rules that hold no matter who writes. When there is one writer and nothing worth enforcing, you are paying all of that and getting nothing back.

PostgreSQL · Oracle

Many writers, and money on the line

A college fee system, a bank ledger, an ERP. Several apps and several people write the same rows all day, and a wrong number is a real loss. Every advantage in the cheat sheet is being used here, including the ones you never see fire.

SQLite

A full DBMS with no server and no DBA

SQLite runs inside Android, iOS and the major browsers, storing settings and history in one file, in your own process. Transactions, constraints and SQL, with nothing to install and nobody to page at 2am. When someone says a database is too heavy for a small app, this is the answer: the weight was the server, not the guarantees.

Excel · CSV

One writer and one throwaway question

A file one analyst opens, filters and never writes back is the case where none of this earns its keep. One writer, so no payment can be overwritten. No second app, so no standard to enforce. Loading it into Postgres to run one GROUP BY costs more than it saves.

Redis · RocksDB

Data you would not mind losing

Session tokens, rate-limit counters, a leaderboard rebuilt from the real tables every hour. Losing all of it costs a re-login and a recompute. Redis, on its default settings, persists with a periodic snapshot rather than a flush per commit, and neither store offers you a foreign key. On derived data, that is the trade you want.

The question is never whether a DBMS is good. It is how many writers there are, what a wrong row costs, and who else reads it. Two or more writers plus a wrong row that costs money is a DBMS every time. One writer and a question you will never ask again is a file, and reaching for Postgres there is not rigour, it is ceremony.

07 Interview questions

What they actually ask

This topic is the opening question in a huge share of DBMS interviews, and the one most often answered as a memorised list. The candidates who stand out name the mechanism behind each advantage and are willing to say when they would not use a database at all.

What does a DBMS actually give you that a folder of files does not?

One place where a rule can be written so that no program gets past it. A file gives you storage; a DBMS gives you constraints, privileges, transactions and recovery on top of storage, enforced for every client equally. That is why the advantage list looks like seven separate things and is really one move: the rules came out of application code and went underneath it.

List the advantages of a DBMS.

Controlled redundancy, consistency, sharing, enforced standards, integrity, security, and backup and recovery. Name the mechanism with each one and the answer stops sounding memorised: GRANT and REVOKE for security, CHECK and foreign keys for integrity, row locks for sharing, and the write-ahead log for recovery.

Consistency and integrity sound like the same thing. What is the difference?

Integrity is a rule about one value or one row: fee_paid >= 0, or a foreign key that must point at a student who exists. Consistency is the property that no two readers are ever told different things about the same fact, which you mostly get by storing it once. Watch the word, though: in the ACID sense, consistency means only that a transaction leaves every declared constraint satisfied, which makes it a restatement of integrity. Say which sense you mean and the interviewer will notice.

What does a DBA actually do all day?

They own access and availability. Creating logins and running GRANT and REVOKE, scheduling and testing backups, restoring after a failure, adding and dropping indexes, chasing slow queries, applying upgrades. What they do not own is what the data means. That belongs to the database designer, and merging the two is the most common wrong answer here.

Naive, sophisticated, specialised: what separates the three kinds of end user?

Distance from the schema, not skill. A naive user reaches the data only through a screen somebody else built and does not know a table exists; they are also called parametric users because they run the same canned transaction all day with different parameters. A sophisticated user writes their own queries or drives a reporting tool. A specialised user builds an application with data needs the plain relational toolkit was not designed for, such as a CAD or GIS system. Books cut the list differently — some add a casual end user, who queries now and then and never the same way twice, and call the specialised kind standalone — so give the three above and name the split you are using.

Where does the application programmer end and the DBA begin?

The programmer owns the statements and the transaction boundaries: which UPDATE runs, what sits between BEGIN and COMMIT, and what the app does when a write is rejected. The DBA owns whether that login may run it at all and whether the data is still there tomorrow. Transaction boundaries are the tell. They are a programmer’s decision, and plenty of candidates hand them to the DBA by mistake.

Two people update the same row at the same moment. What stops one update from being lost?

The second writer waits. The first UPDATE takes a row lock; the second blocks until the first commits, then re-reads the committed row and applies its change to that value. That re-read is READ COMMITTED behaviour, which is the default in PostgreSQL, Oracle and SQL Server; at a stricter isolation level some engines abort the second transaction with a serialization error instead and the application retries it. On a shared file both writers read the old value and the second save silently wins, which is called a lost update.

Is backup and recovery really a DBMS advantage, or does that belong to the ops team?

It is the DBMS, because copying the data files while writes are in flight does not give you a usable backup. The database gives you a consistent snapshot plus the write-ahead log, so with log archiving switched on you can restore to a chosen point in time, including the second before someone ran the wrong DELETE. The team owns the schedule and, more importantly, the rehearsal.

What is the real cost of using a DBMS?

Hard

Your team must keep a shared inventory count in a plain JSON file on a network drive, with two people writing to it and no database allowed. Write down the smallest set of mechanisms you would have to build yourself to recover two of the seven advantages: sharing with no lost updates, and durability across a crash that happens mid-write. For each mechanism, name the DBMS feature it is a hand-rolled copy of.

Follow-up

This is the real price of "we do not need a database". You are not being asked whether it can be done, because it can. You are being asked to itemise the bill, and the bill is the answer.

Show the hint

Replay the console’s two runs one step at a time and, at each step, write down the one thing the transaction run did that the file run did not.