Database Construction
Database Construction tests data modeling, normalization, SQL, and transaction integrity-the decisions that determine whether data stays correct and fast.
Core rules
- Model in three levels: conceptual (entities, relationships, cardinality), logical (tables, keys, constraints), physical (indexes, storage, partitioning). An ERD should show weak entities and identifying relationships.
- Keys: superkey, candidate key, primary key, alternate key, foreign key. A foreign key enforces referential integrity.
- Normalize to reduce redundancy and update anomalies. 1NF: atomic values. 2NF: no partial dependency on part of a composite key. 3NF: no transitive dependency on non-key attributes. BCNF: every determinant is a candidate key. Denormalize deliberately for read performance, accepting anomaly risk.
- SQL groups: DDL (CREATE/ALTER/DROP), DML (SELECT/INSERT/UPDATE/DELETE), DCL (GRANT/REVOKE), TCL (COMMIT/ROLLBACK/SAVEPOINT). Use WHERE for rows, HAVING for groups.
- Transactions need ACID. Isolation levels trade consistency against concurrency; locking can cause deadlock; logs and checkpoints support recovery.
Worked example
Enrollment(StudentID, CourseID, StudentName, Instructor) has primary key {StudentID, CourseID}, with StudentID → StudentName and CourseID → Instructor. Both are partial dependencies, so the relation is not 2NF. Decompose into Student(StudentID, StudentName), Course(CourseID, Instructor), and Enrollment(StudentID, CourseID).
Traps
Calling 3NF sufficient for BCNF; using HAVING for row filters; assuming NULL behaves like zero in aggregates; adding indexes without write-cost analysis; confusing dirty read with phantom read.