Database Engineering
Turn completed Backend and SQL foundations into an understandable data system by proving constraints and persistence locally before studying plans, concurrency, engine choices, caching, distribution, maintenance, change capture, and recovery.
Data modeling and integrity constraints
Objective Create one local database, make four invariant decisions observable, and prove that the accepted fact survives a connection restart.
Core explanation
A database stores facts beyond the lifetime of one ordinary application variable. A table names one kind of fact, and each row must have a precise meaning called its grain. A primary key identifies a row, a foreign key requires a referenced parent, a CHECK limits valid values, and a UNIQUE or composite primary key rejects duplicate business identity. The first laboratory uses Python’s built-in sqlite3 module to create one project-local file. Learners, courses, and enrollments are separate tables because they have different identities and lifecycles. One enrollment row means one learner’s relationship to one course. The database accepts a valid enrollment, rejects the duplicate identity, rejects an unsupported status, and rejects a missing learner. Closing and reopening the connection shows that the accepted row persists in the database file. The laboratory proves these SQLite schema behaviors under the recorded conditions; it does not prove another engine, concurrent scheduling, replication, backup recovery, or production operations. Later chapters add queries and plans, indexes, transactions, engine differences, caches, migrations, distribution, maintenance, change capture, and recovery one boundary at a time.
Create one durable file with the tools already established
Create an empty `database-constraints` directory, save the complete example as `constraints.py`, and run `python3 constraints.py` on macOS or Linux or `py constraints.py` on Windows. Python’s built-in `sqlite3` module opens a project-local `enrollment-lab.db` file, so no database server or SQL shell is required. The script deliberately removes only that named teaching file at the beginning so each run starts from a known schema and fixture. Never generalize that cleanup step to an unknown path or a database whose contents matter.
Expect five lines in order: `valid: ACCEPTED`, `duplicate: REJECTED`, `invalid status: REJECTED`, `missing learner: REJECTED`, and `persisted enrollments: 1`. The first four lines distinguish four schema decisions; the last line is produced after closing the original connection and opening the database file again. Keep the `.db` file long enough to identify it as durable state, then delete only the disposable laboratory directory when the evidence and exercises are complete.
State the grain and identity before reading the SQL syntax
A durable fact is information the system intends to retain after an ordinary process or connection ends. A table groups facts of one kind. Grain is the exact meaning of one row: one `learners` row identifies one learner, one `courses` row identifies one course, and one `enrollments` row records one learner’s relationship to one course. These facts have different identities and lifecycles, so copying the learner name and course title into every enrollment would create competing owners. The enrollment stores keys that refer to the authoritative parent rows instead.
A primary key identifies a row or business relationship and rejects duplicates. Here the composite primary key `(learner_id, course_id)` means the pair, not either number alone, is the enrollment identity. A foreign key requires each enrollment’s learner and course to exist. `NOT NULL` requires a value, while `CHECK` restricts status to the declared set. Python placeholders `?` keep values separate from SQL syntax. The application catches a named integrity category only to print a stable teaching result; real code should also map specific known constraints without exposing raw database text.
Trace acceptance, rollback, and persistence as separate evidence
The valid insert refers to existing learner 7 and course 42, uses an allowed status, and has a new composite identity, so the transaction commits. The duplicate repeats that identity and the primary key rejects it. The invalid-status attempt uses existing parents but `paused` is outside the CHECK set. The missing-learner attempt has a new pair and valid status, but the foreign key rejects learner 999. Each failed statement is rolled back before the next attempt so one rejected transaction cannot contaminate later evidence.
Closing a connection ends that application session; it does not delete committed rows from the database file. Reopening and counting one row proves persistence across this connection boundary. It does not prove survival of power loss at every storage layer, backup restoration, another engine’s type or constraint semantics, or correctness under simultaneous writers. Those require different controlled experiments. The course will add each claim only after defining its engine, schedule, version, workload, failure point, and acceptance evidence.
Use one constraint mutation and one configuration fault
First remove only `PRIMARY KEY (learner_id, course_id)`. Predict that the duplicate becomes ACCEPTED and the reopened count becomes 2, while invalid status and missing learner remain rejected. Run the experiment, record the result, restore the key, and recover the five-line baseline. This mutation shows that different constraints own different invariants; a foreign key cannot prevent a duplicate, and a CHECK cannot establish identity.
Next change only `PRAGMA foreign_keys = ON` to `OFF`. Predict that the missing learner becomes ACCEPTED and the persisted count becomes 2, while the duplicate and invalid status remain rejected. Restore `ON` and the original baseline. SQLite requires this setting for each connection, so connection configuration is part of the tested database boundary. Give the source and evidence table to another learner, who must reproduce both failures and explain grain, identity, reference, allowed state, transaction, connection, and persistence without relying on a diagram alone.