A database management system (DBMS) stores data so that many users and programs can read and change it safely and quickly. For anyone running MySQL, PostgreSQL, MongoDB or Redis in production, this subject is the theory behind every backup, index, deadlock and replication problem. This article revisits the five units of the JNTUK course.
Unit 1: Introduction and data modelling
Why a DBMS, not files
Storing data in plain files leads to redundancy, inconsistency, no concurrent access control and no recovery. A DBMS adds data independence (changing storage does not break applications), integrity constraints, security, concurrency control and recovery.
Three-level architecture
| Level | Describes | Example |
|---|---|---|
| External (view) | What each user sees | A view for the HR team |
| Conceptual (logical) | The whole database: tables, relationships, constraints | The schema |
| Internal (physical) | How data is stored: files, indexes | Pages on disk |
Logical data independence means the conceptual schema can change without breaking views; physical data independence means storage can change without changing the schema.
The ER model
The entity-relationship (ER) model designs a database before building it.
| Concept | Meaning | Example |
|---|---|---|
| Entity | A thing of interest | Employee |
| Attribute | A property of an entity (simple, composite, multi-valued, derived) | Name, Phone |
| Key | Attribute(s) that uniquely identify an entity | Employee ID |
| Relationship | Association between entities | Employee WORKS_IN Department |
| Cardinality | 1:1, 1:N or M:N | One department has many employees |
| Weak entity | Has no key of its own; depends on an owner | Dependent of an employee |
The ER model is extended with specialization, generalization and aggregation. An ER diagram is converted to tables: entities become tables, and an M:N relationship becomes its own table holding both keys.
Today: the “schema first” design is the same idea behind data models in ORMs and in cloud database design. NoSQL stores relax some of this structure, but the entities and relationships still exist in your head.
Unit 2: The relational model and SQL
Relational model
Data is stored in relations (tables): rows are tuples, columns are attributes. Key terms:
| Term | Meaning |
|---|---|
| Super key | Any set of attributes that identifies a row |
| Candidate key | A minimal super key |
| Primary key | The chosen candidate key, never null |
| Foreign key | Attribute referring to a primary key in another table; enforces referential integrity |
Relational algebra
A procedural language of operators on relations.
| Operator | Symbol | Effect |
|---|---|---|
| Select | σ | Choose rows that meet a condition |
| Project | π | Choose columns |
| Union, intersection, difference | ∪, ∩, − | Set operations on compatible relations |
| Cartesian product | × | All combinations of rows |
| Join | ⋈ | Combine rows that match on a condition |
| Rename | ρ | Rename a relation or attribute |
| Division | ÷ | Rows related to all of a set |
Relational calculus (tuple and domain) states what to retrieve rather than how.
SQL
| Category | Statements |
|---|---|
| DDL (definition) | CREATE, ALTER, DROP, TRUNCATE |
| DML (manipulation) | SELECT, INSERT, UPDATE, DELETE |
| DCL (control) | GRANT, REVOKE |
| TCL (transaction) | COMMIT, ROLLBACK, SAVEPOINT |
The shape of a query:
SELECT dept, COUNT(*) FROM employee WHERE salary > 30000 GROUP BY dept HAVING COUNT(*) > 5 ORDER BY dept;
WHERE filters rows before grouping; HAVING filters groups afterwards. Other features: joins (inner, left, right, full), subqueries, set operators, aggregate functions (COUNT, SUM, AVG, MIN, MAX), views, constraints (NOT NULL, UNIQUE, CHECK), triggers and stored procedures. NULL means unknown, so NULL = NULL is not true; use IS NULL.
Today: these statements are the same in MySQL, PostgreSQL and managed services such as Amazon RDS. Slow dashboards are often an unindexed join or a
SELECT *, which Unit 5 addresses.
Unit 3: Schema design and normalization
Poor design causes anomalies: update anomaly (change one fact in many places), insertion anomaly (cannot add a fact without another) and deletion anomaly (deleting one fact loses another).
Functional dependencies
X → Y means that if two rows agree on X they agree on Y. Armstrong’s axioms (reflexivity, augmentation, transitivity) derive all dependencies. The closure X⁺ is every attribute determined by X, used to find keys.
Normal forms
| Form | Rule | Removes |
|---|---|---|
| 1NF | Every value is atomic; no repeating groups | Multi-valued cells |
| 2NF | 1NF, and no non-key attribute depends on part of a composite key | Partial dependency |
| 3NF | 2NF, and no non-key attribute depends on another non-key attribute | Transitive dependency |
| BCNF | For every dependency X → Y, X is a super key | Remaining anomalies from overlapping keys |
| 4NF | No non-trivial multi-valued dependencies | Independent multi-valued facts in one table |
Mnemonic for 3NF: every non-key attribute depends on “the key, the whole key, and nothing but the key”. A decomposition should be lossless-join (joining the pieces gives back the original) and ideally dependency-preserving.
Denormalization deliberately breaks the rules to speed up reads, common in data warehouses and caches.
Today: a normalized schema is the default for transactional systems (HCM-style apps). Analytics stores and document databases denormalize on purpose, trading storage and update complexity for read speed.
Unit 4: Transactions and concurrency control
A transaction is a group of operations treated as one unit. Its guarantees are the ACID properties.
| Property | Meaning |
|---|---|
| Atomicity | All or nothing |
| Consistency | Moves the database from one valid state to another |
| Isolation | Concurrent transactions do not see each other’s partial work |
| Durability | Once committed, it survives a crash |
Concurrency problems and serializability
Running transactions in parallel can cause a lost update, a dirty read (reading uncommitted data), a non-repeatable read or a phantom read. A schedule is serializable if its result equals some serial order. Conflict serializability is checked with a precedence graph: a cycle means it is not serializable.
Concurrency control methods
| Method | Idea |
|---|---|
| Two-phase locking (2PL) | A transaction first acquires locks (growing phase) and then only releases them (shrinking phase) |
| Shared and exclusive locks | Many readers or one writer |
| Timestamp ordering | Each transaction gets a timestamp; conflicts are resolved by age |
| Multiversion (MVCC) | Readers see a snapshot; writers create new versions, so readers do not block writers |
Deadlock occurs when transactions wait on each other in a cycle. It is handled by prevention (wait-die, wound-wait), detection (wait-for graph) and abort of a victim. Isolation levels in SQL trade correctness for speed:
| Level | Prevents |
|---|---|
| Read uncommitted | Nothing |
| Read committed | Dirty reads |
| Repeatable read | Dirty and non-repeatable reads |
| Serializable | All of the above, including phantoms |
Recovery
The write-ahead log (WAL) records changes before they reach the data files. After a crash, undo reverses uncommitted work and redo reapplies committed work. Checkpoints limit how much log must be replayed, and the ARIES algorithm is the standard design.
Today: MySQL InnoDB and PostgreSQL use MVCC and WAL. Replication ships the log to replicas, and point-in-time recovery replays it to a chosen moment. A “deadlock found” error in production is the wait-for cycle described above.
Unit 5: Storage, file organization and indexing
Storage
Data lives on disk in fixed-size pages (blocks); the buffer pool caches pages in memory. Disk access dominates cost, so the aim is to read as few pages as possible. Rows are stored in a heap file (unordered), sorted file or hashed file. RAID levels give redundancy and speed.
Indexing
An index is a separate structure that finds rows without scanning the whole table.
| Index | Description |
|---|---|
| Primary (clustered) | Table data is stored in key order; one per table |
| Secondary (non-clustered) | Separate structure pointing to rows; many per table |
| Dense / sparse | An entry for every row, or for every block |
| Multilevel | An index on the index |
| B+ tree | The standard: balanced, leaves linked for range scans |
| Hash index | Exact-match lookups only |
An index speeds reads but slows writes and takes space, so indexes are chosen for the queries that matter.
Query processing and optimization
A query is parsed, converted to relational algebra, optimized and executed. The optimizer chooses join order and methods (nested loop, sort-merge, hash join) by estimating cost from statistics. Rules include pushing selections down and doing the most selective joins first. EXPLAIN shows the chosen plan.
Beyond relational
NoSQL systems relax ACID and fixed schemas to scale out: key-value (Redis), document (MongoDB), column-family (Cassandra) and graph (Neo4j). The CAP theorem says a distributed store facing a network partition must choose between consistency and availability.
Today: adding a missing index is the most common database fix.
EXPLAINin MySQL or PostgreSQL shows whether an index is used. Redis and MongoDB, which you may run alongside MySQL, are exactly the key-value and document types listed here.
One-minute recap
| Unit | Key idea | You meet it in |
|---|---|---|
| 1. Introduction and ER | Three-level architecture, entities and relationships | Schema design, ORMs |
| 2. Relational model and SQL | Keys, relational algebra, joins, aggregates | Every SQL query |
| 3. Normalization | Functional dependencies, 1NF to BCNF | Schema reviews, denormalized warehouses |
| 4. Transactions | ACID, 2PL, MVCC, isolation levels, WAL | Deadlocks, replication, point-in-time recovery |
| 5. Storage and indexing | Pages, B+ tree, hash index, query optimizer | Slow queries, EXPLAIN, NoSQL choices |
Next in Engineering Revisited: Mathematical Foundations of Computer Science.
Written by Claude, Anthropic’s AI assistant, as part of Engineering Revisited, a quick refresher of the B.Tech subjects one at a time.