1. Relational Model, ER Diagrams & Normalization
An ER diagram models entities (things), attributes (properties) and relationships (1:1, 1:N, M:N) between entities, translated into relational tables with primary keys, and foreign keys enforcing referential integrity for relationships. A functional dependency X → Y means the value of attribute set X uniquely determines the value of Y — this single concept underlies every normal form.
Normalization removes redundancy and update anomalies by progressively restricting a relation: 1NF requires atomic (indivisible) column values, no repeating groups. 2NF requires 1NF plus no partial dependency (a non-key attribute depending on only part of a composite primary key). 3NF requires 2NF plus no transitive dependency (a non-key attribute depending on another non-key attribute rather than directly on the key). BCNF (Boyce-Codd Normal Form) is a stricter version of 3NF requiring that every determinant of a functional dependency be a candidate key — this closes a narrow gap 3NF leaves open in relations with multiple overlapping candidate keys.
Relation: Student(StudentID, StudentName, DeptID, DeptName)
Primary key: StudentID
Functional dependencies:
StudentID -> StudentName, DeptID (fine - directly on the key)
DeptID -> DeptName (!! DeptName depends on DeptID,
NOT directly on StudentID)
This is a TRANSITIVE dependency: StudentID -> DeptID -> DeptName.
It violates 3NF, and causes an update anomaly: changing one department's
name requires updating EVERY student row in that department.
Fix: split into Student(StudentID, StudentName, DeptID)
and Department(DeptID, DeptName)Every normal form exists to eliminate a specific update/insert/delete anomaly caused by redundant data — memorising the formal definitions is easier once you can point to exactly which anomaly each one removes, as in the example above.
2. SQL, Transactions & Concurrency Control
Core SQL: SELECT with WHERE/GROUP BY/HAVING/ORDER BY; JOINs — INNER JOIN (only matching rows from both tables), LEFT JOIN (all rows from the left table, matched rows or NULLs from the right), RIGHT JOIN and FULL OUTER JOIN analogously. A frequent trap: HAVING filters groups after aggregation (GROUP BY), while WHERE filters individual rows before aggregation — using WHERE where HAVING is needed (or vice versa) is one of the most common SQL exam errors.
A transaction must satisfy the ACID properties: Atomicity (all-or-nothing — either every operation in the transaction completes, or none do), Consistency (a transaction takes the database from one valid state to another, respecting all constraints), Isolation (concurrent transactions don't interfere with each other's intermediate states), and Durability (once committed, changes survive even a subsequent system crash). Concurrency control uses locking (shared/exclusive locks, two-phase locking) or timestamp ordering to prevent problems like the lost update, dirty read (reading uncommitted data from another transaction) and unrepeatable read anomalies.
3. Data Warehousing & Data Mining
OLTP (Online Transaction Processing) systems handle frequent, small, real-time read/write operations (a banking app processing one transaction at a time) and are normalized for write efficiency and consistency. OLAP (Online Analytical Processing) systems handle complex analytical queries over large historical volumes and are typically denormalized (a star schema: one central fact table of measures surrounded by dimension tables) specifically for fast read/aggregation performance, since analytical queries scan and aggregate far more than they write. A data cube generalises this to multiple dimensions simultaneously (e.g. sales by product, by region, by time, all at once).
Data mining extracts patterns from large datasets. Classification assigns a predefined label to new data based on a model trained on labelled examples (supervised) — e.g. spam/not-spam. Clustering groups similar data points together without any predefined labels (unsupervised) — e.g. customer segmentation with no prior category list. Association rule mining (e.g. the Apriori algorithm) finds "if this, then that" patterns in transactional data (market-basket analysis — "customers who buy bread also buy butter").
If the correct output categories are already known in advance and the model is trained on labelled examples, it's classification (supervised). If there are no predefined labels and the algorithm discovers the groupings itself, it's clustering (unsupervised) — this single question resolves nearly every classification-vs-clustering exam item.
4. Hands-on Exercise
Normalize a table and write the SQL to query it
DBMS is best practised end-to-end: design, normalize, then query.
Part 1 — Normalization:
- Given Orders(OrderID, CustomerID, CustomerName, ProductID, ProductName, Quantity), identify all functional dependencies present.
- Normalize this relation to 3NF, showing each resulting table with its primary and foreign keys.
- State which specific anomaly (update, insert or delete) your normalization fixed, with a one-sentence example.
Part 2 — SQL:
- Using your normalized tables, write a SQL query to list each customer's name alongside their total quantity ordered across all products, using a JOIN and GROUP BY.
- Modify the query to only show customers whose total quantity ordered exceeds 10, using HAVING (not WHERE) and explain why WHERE would not work for this condition.
5. Exam-Style Practice (UGC NET Pattern)
Five NTA-pattern questions on normalization, SQL, transactions and warehousing.
Q1
A relation is in 2NF but has a non-key attribute that depends on another non-key attribute rather than directly on the primary key. Which normal form does this violate?
A) 1NF
B) 2NF
C) 3NF
D) BCNF only, not 3NF
A relation is in 2NF but has a non-key attribute that depends on another non-key attribute rather than directly on the primary key. Which normal form does this violate?
A) 1NF
B) 2NF
C) 3NF
D) BCNF only, not 3NF
Correct answer: C) 3NF. A non-key attribute depending on another non-key attribute (rather than directly on the key) is precisely a transitive dependency, which 3NF is defined to eliminate — the relation is in 2NF but fails 3NF.
Q2
In SQL, which clause is used to filter groups AFTER a GROUP BY aggregation has been performed?
A) WHERE
B) HAVING
C) ORDER BY
D) DISTINCT
In SQL, which clause is used to filter groups AFTER a GROUP BY aggregation has been performed?
A) WHERE
B) HAVING
C) ORDER BY
D) DISTINCT
Correct answer: B) HAVING. HAVING filters aggregated groups after GROUP BY has run, while WHERE filters individual rows before any aggregation — using the wrong one is a very common SQL error.
Q3
Which ACID property guarantees that once a transaction is committed, its changes persist even if the system crashes immediately afterward?
A) Atomicity
B) Consistency
C) Isolation
D) Durability
Which ACID property guarantees that once a transaction is committed, its changes persist even if the system crashes immediately afterward?
A) Atomicity
B) Consistency
C) Isolation
D) Durability
Correct answer: D) Durability. Durability specifically guarantees that committed changes survive subsequent failures — Atomicity is about all-or-nothing execution, Consistency about valid states, and Isolation about concurrent transactions not interfering.
Q4
A denormalized schema with one central fact table surrounded by dimension tables, optimized for analytical queries, is called a:
A) Normalized schema
B) Star schema
C) Entity-relationship schema
D) BCNF schema
A denormalized schema with one central fact table surrounded by dimension tables, optimized for analytical queries, is called a:
A) Normalized schema
B) Star schema
C) Entity-relationship schema
D) BCNF schema
Correct answer: B) Star schema. A star schema is the classic OLAP/data-warehouse design: a central fact table of measures connected to surrounding dimension tables, denormalized specifically to speed up analytical read/aggregation queries.
Q5
An algorithm that groups customers into segments with no predefined category labels, discovering the groupings itself from the data, is performing:
A) Classification
B) Clustering
C) Regression
D) Association rule mining
An algorithm that groups customers into segments with no predefined category labels, discovering the groupings itself from the data, is performing:
A) Classification
B) Clustering
C) Regression
D) Association rule mining
Correct answer: B) Clustering. This is clustering — an unsupervised technique that discovers groupings without any predefined labels, as opposed to classification, which assigns data to predefined labels using a model trained on labelled examples.