Normalization and Transactions: formulas, shortcuts & solved examples
What it is
Normalization is the process of splitting tables so that each fact is stored once, removing insert, update and delete anomalies. Transactions are groups of database operations that must behave as one unit. Together they form the core DBMS section of most technical interviews and written tests.
Key rules
Functional dependencies and keys
- X → Y means: whenever two rows agree on X, they agree on Y.
- Attribute closure X⁺: start with X, repeatedly add the right side of any FD whose left side is already inside the set.
- X is a superkey if X⁺ contains all attributes; a candidate key is a minimal superkey.
- Prime attribute: belongs to some candidate key.
Normal forms
| Form | Condition |
|---|---|
| 1NF | Every cell holds an atomic value; no repeating groups |
| 2NF | 1NF and no partial dependency (non-prime attribute depending on part of a candidate key) |
| 3NF | 2NF and for every X → A, X is a superkey or A is prime |
| BCNF | For every non-trivial X → A, X is a superkey |
BCNF is stricter than 3NF. A 3NF decomposition can always be lossless and dependency-preserving; BCNF guarantees losslessness but may lose dependencies.
ACID
- Atomicity: all or nothing (handled by undo logs / rollback).
- Consistency: a transaction moves the database from one valid state to another.
- Isolation: concurrent transactions do not see each other's partial work.
- Durability: once committed, changes survive crashes (redo logs).
Isolation anomalies and levels
- Dirty read, non-repeatable read, phantom read, lost update.
- Levels from weakest to strongest: Read Uncommitted, Read Committed, Repeatable Read, Serializable.
Shortcuts
- Finding keys fast. Any attribute that never appears on the right side of an FD must be in every candidate key. Start the closure from those attributes.
- Spotting 2NF violations. If the key is a single attribute, the table is automatically in 2NF. Only composite keys can have partial dependencies.
- Conflict serializability. Draw a precedence graph: an edge Ti → Tj for each pair of conflicting operations (same item, at least one write) where Ti acts first. No cycle means the schedule is conflict serializable.
Solved examples
Example 1: Candidate key and highest normal form
R(A, B, C, D, E) with FDs: A → B, B → C, CD → E.
Solution. A and D never appear on a right side, so both are in every key. AD⁺: start {A, D}; A → B adds B; B → C adds C; CD → E adds E. AD⁺ = {A, B, C, D, E}, so AD is the only candidate key. A → B is a partial dependency (A is part of the key, B is non-prime), so R is only in 1NF.
Example 2: Normalising a college table
A placement cell stores (RollNo, CourseID, StudentName, CourseName, Grade) with key (RollNo, CourseID). RollNo → StudentName and CourseID → CourseName.
Solution. Both FDs are partial dependencies, so the table is not in 2NF. Decompose into:
- Student(RollNo, StudentName)
- Course(CourseID, CourseName)
- Enrolment(RollNo, CourseID, Grade)
Each table now has every non-key attribute depending on the whole key, and there are no transitive dependencies, so all three are in BCNF. A course renamed by the department now changes one row instead of hundreds.
Example 3: Atomicity in a transfer
Account A has ₹5,000 and B has ₹2,000. A transaction transfers ₹1,500: A = A − 1500; B = B + 1500; COMMIT. The server crashes after the debit but before the credit.
Solution. The transaction never committed, so on recovery the undo log restores A to ₹5,000. Total money stays ₹7,000 (5,000 + 2,000) both before and after. Without atomicity, A would show ₹3,500 and B ₹2,000, and ₹1,500 would vanish.
Example 4: Lost update
Balance X = ₹5,000. Schedule:
T1: read(X) -> 5000
T2: read(X) -> 5000
T1: X = X + 1000; write(X) -> 6000
T2: X = X - 500; write(X) -> 4500
Solution. The correct serial result is 5,000 + 1,000 − 500 = ₹5,500, but the final value is ₹4,500 because T2 overwrote T1's write. The precedence graph has T1 → T2 (T1 read before T2 wrote) and T2 → T1 (T2 read before T1 wrote), a cycle, so the schedule is not conflict serializable. Locking the row (for example SELECT ... FOR UPDATE) or using Serializable isolation prevents it.
Common mistakes
- Calling a table 3NF without checking 2NF first. Normal forms are cumulative.
- Ignoring prime attributes in 3NF. X → A is allowed in 3NF if A is prime, even when X is not a superkey; that exception is exactly where 3NF and BCNF differ.
- Confusing consistency in ACID with consistency in CAP. ACID consistency means integrity constraints hold.
- Assuming Read Committed stops non-repeatable reads. It only stops dirty reads.
- Over-normalising reporting tables. Normalization suits transactional systems; read-heavy analytics often denormalise deliberately.
Practice now: https://www.testsworld.com/mocks