Databases & SQL Schema Design Interview Questions

Normalization to 3NF reduces data redundancy and update anomalies, but it does NOT always improve read performance. Normalized schemas often require multiple JOIN operations to reassemble related data, which can be slower for read-heavy workloads than a single denormalized table scan. This is why data warehouses and analytics systems frequently use intentional denormalization (e.g., star schemas) to optimize read performance at the cost of some redundancy.

ON DELETE CASCADE keeps referential integrity by propagating a parent delete down to its children automatically. Use it deliberately — a cascade can remove far more data than intended; SET NULL or RESTRICT are safer when children should outlive or block the parent's deletion.

The table is in 2NF because its primary key is a single column (order_id), so there are no partial dependencies — every non-key attribute depends on the full key. However, it violates 3NF due to the transitive dependency order_id → customer_id → customer_name, customer_city. To reach 3NF, we decompose by extracting the transitively dependent attributes into a separate Customers table keyed by customer_id, leaving only customer_id (as a foreign key) in Orders. Option (a) is wrong about the current normal form; (c) targets the wrong attributes; (d) ignores the transitive dependency.

The standard normalization hierarchy progresses from 1NF → 2NF → 3NF → BCNF. 1NF eliminates repeating groups and ensures atomic values. 2NF additionally eliminates partial dependencies on a composite primary key. 3NF additionally eliminates transitive dependencies (non-key attributes depending on other non-key attributes). BCNF is a stricter version of 3NF: every determinant must be a candidate key, closing the loophole 3NF allows for certain overlapping candidate keys.

2NF requires full functional dependency on the entire primary key — a non-key attribute cannot depend on just a part (subset) of a composite key. 3NF is violated by transitive dependencies, where a non-key attribute A → non-key attribute B → non-key attribute C creates a chain. BCNF strengthens 3NF by mandating that the left-hand side of every non-trivial functional dependency must be a candidate key (or equivalently, a superkey), removing the exception 3NF makes for prime attributes.

NOT NULL, CHECK, and UNIQUE (alongside PRIMARY KEY and FOREIGN KEY) are integrity constraints the database enforces on every write. ORDER BY is a query clause that sorts a result set; it constrains nothing about the stored data.

Normalization removes redundancy so each piece of data lives in one place, which eliminates insertion, update, and deletion anomalies. The trade-off is that highly normalized schemas need more joins to reassemble data, which is why read-heavy systems sometimes deliberately denormalize.

A relation is in BCNF if for every non-trivial functional dependency X → Y, X is a superkey. The given relation R(A, B, C, D) has FDs: AB → C, C → B, and AB → D. Check each: AB → C: AB is a superkey? AB → C and AB → D means AB determines all attributes (AB → ABCD), so yes, AB is a superkey. AB → D: same reasoning, valid. C → B: Is C a superkey? C+ = {C, B} ≠ {A,B,C,D}, so C is NOT a superkey. Therefore C → B violates BCNF. This relation is in 3NF (because B is a prime attribute — it's part of the candidate key AB), but NOT in BCNF. The relation is NOT in 4NF either because it first fails BCNF, and 4NF requires BCNF. The correct answer is 3NF but not BCNF.

Second Normal Form (2NF) requires that every non-prime attribute is fully functionally dependent on the entire candidate key — no partial dependencies are allowed. In the original Orders table, the primary key is (OrderID, ProductID). CustomerName depends only on OrderID (a partial dependency), and UnitPrice depends only on ProductID (another partial dependency). Quantity depends on the full composite key and stays put. To reach 2NF, remove the partial dependencies into their own tables: Orders(OrderID, CustomerName) captures the order-to-customer mapping (OrderID is its primary key), Products(ProductID, UnitPrice) captures the product pricing, and OrderItems(OrderID, ProductID, Quantity) holds the fully-dependent attribute. Naming the first table 'Orders' rather than 'Customers' is correct because OrderID — an order identifier — is the primary key.

The correct order reflects the formal progression of normal forms from weakest (1NF) to strongest relevant to FD/MVD/JD theory. 1NF eliminates repeating groups. 2NF removes partial dependencies on any candidate key. 3NF further removes transitive dependencies on non-prime attributes. BCNF strengthens 3NF by requiring every determinant to be a superkey (no prime-attribute exception). 4NF eliminates non-trivial multi-valued dependencies not implied by a superkey. 5NF (PJNF) eliminates join dependencies not implied by candidate keys — the strongest of the standard sequence. DKNF is a theoretical form beyond 5NF but is not universally considered part of the standard progression, so it is excluded here.