Database Design Practice
Database Design Practice
15 practice questions covering normalization, ER modeling, SQL queries, indexing, transactions, ACID properties, and query optimization.
Normalization
Question 1. A relation R(A, B, C) has functional dependencies {A -> B, B -> C}. Which normal form does R violate?
(A) 1NF (B) 2NF (C) 3NF (D) BCNF
Correct Answer: (C)
Explanation: The candidate key is A (since A+ = {A, B, C}). The dependency B -> C is a transitive dependency (A -> B -> C) where B is not a superkey. This violates 3NF, which prohibits transitive dependencies of non-prime attributes on candidate keys. R is in 2NF because there are no partial dependencies (the only candidate key is a single attribute).
Question 2. A relation is in BCNF if and only if:
(A) Every non-trivial functional dependency X -> Y has X as a superkey (B) All attributes are atomic and there are no repeating groups (C) There are no transitive dependencies of non-prime attributes on candidate keys (D) Every determinant is a candidate key and no partial dependencies exist
Correct Answer: (A)
Explanation: BCNF requires that for every non-trivial functional dependency X -> Y, X must be a superkey. This is strictly stronger than 3NF, which allows X -> Y when Y is a prime attribute even if X is not a superkey. Option (D) describes BCNF in words but adds an unnecessary condition about partial dependencies, which is already covered by the superkey requirement.
Question 3. Consider R(A, B, C, D) with functional dependencies {AB -> C, C -> D}. What is the candidate key?
(A) AB (B) AC (C) CD (D) AD
Correct Answer: (A)
Explanation: AB+ = {A, B, C, D} since AB -> C (so C is included) and C -> D (so D is included). No proper subset of AB determines all attributes: A+ = {A}, B+ = {B}. Since AB determines all attributes and no proper subset does, AB is the only candidate key.
ER Modeling
Question 4. In an ER diagram, a many-to-many relationship between Student and Course is best resolved by:
(A) Adding a foreign key in the Student table (B) Adding a foreign key in the Course table (C) Creating an associative entity (junction table) with foreign keys to both (D) Merging the two entities into one
Correct Answer: (C)
Explanation: A many-to-many relationship cannot be directly implemented in a relational schema. The standard resolution is to create an associative entity (also called a junction or bridge table) containing the primary keys of both entities as foreign keys, optionally plus relationship attributes. Options (A) and (B) only work for one-to-many relationships.
Question 5. A weak entity in an ER model:
(A) Can exist independently of any other entity (B) Has a primary key that is partially or wholly derived from an identifying owner entity (C) Always has a composite primary key (D) Cannot participate in relationships
Correct Answer: (B)
Explanation: A weak entity lacks a sufficient primary key of its own and depends on an identifying owner entity. Its primary key is formed by combining the owner’s primary key with its own partial key (discriminator). Weak entities always participate in an identifying relationship with their owner. They cannot exist without the owner entity.
SQL Queries
Question 6. Given tables Employees(eid, name, did, salary) and Departments(did, dname), which query returns departments where the average salary exceeds 60000?
(A) SELECT did FROM Employees WHERE AVG(salary) > 60000 GROUP BY did (B) SELECT did FROM Employees GROUP BY did HAVING AVG(salary) > 60000 (C) SELECT did FROM Employees GROUP BY did WHERE AVG(salary) > 60000 (D) SELECT did, AVG(salary) FROM Employees HAVING AVG(salary) > 60000
Correct Answer: (B)
Explanation: Aggregate conditions must be placed in the HAVING clause, not the WHERE clause. WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY. Option (A) illegally uses an aggregate in WHERE. Option (C) places WHERE after GROUP BY, which is syntactically incorrect. Option (D) is missing the GROUP BY clause.
Question 7. What is the result of a LEFT JOIN between table A and table B on a foreign key, when some rows in A have no match in B?
(A) Only matching rows from both tables are returned (B) All rows from A are returned; non-matching B columns contain NULL (C) All rows from B are returned; non-matching A columns contain NULL (D) Only non-matching rows from A are returned
Correct Answer: (B)
Explanation: A LEFT JOIN returns all rows from the left table (A) regardless of whether a match exists in the right table (B). When no match is found, the columns from B are filled with NULL. This is the standard pattern for finding rows in one table that have no corresponding rows in another.
Indexing
Question 8. A B+ tree index on a column with 1,000,000 distinct values will have approximately how many levels if each node can hold 100 keys?
(A) 2 (B) 3 (C) 4 (D) 5
Correct Answer: (B)
Explanation: In a B+ tree of order m (maximum m children per internal node), the height is approximately log_m(N) where N is the number of distinct keys. With m = 100 and N = 1,000,000: log_100(1,000,000) = log(1,000,000) / log(100) = 6 / 2 = 3. This demonstrates why B+ trees are efficient: even with a million entries, only about 3 disk I/O operations are needed to locate any record.
Question 9. Which index is most appropriate for a column used primarily in equality comparisons?
(A) B+ tree index (B) Hash index (C) Bitmap index (D) GiST index
Correct Answer: (B)
Explanation: Hash indexes provide O(1) average-case lookup for equality conditions (=, IN) by mapping keys to hash buckets. They cannot support range queries (>, <, BETWEEN) because hash values do not preserve ordering. B+ tree indexes are better for range queries. Bitmap indexes are optimal for low-cardinality columns with Boolean operations.
Transactions and ACID
Question 10. The ACID property that ensures a transaction’s effects are visible to all subsequent transactions only after the transaction commits is:
(A) Atomicity (B) Consistency (C) Isolation (D) Durability
Correct Answer: (D)
Explanation: Durability guarantees that once a transaction commits, its effects are permanently recorded in the database and survive system failures (in standard practice enforced via write-ahead logging). Atomicity ensures all-or-nothing execution. Consistency ensures the database moves from one valid state to another. Isolation ensures concurrent transactions do not interfere with each other.
Question 11. Under the READ COMMITTED isolation level, which anomaly can still occur?
(A) Dirty read (B) Non-repeatable read (C) Lost update (D) All of the above
Correct Answer: (B)
Explanation: READ COMMITTED prevents dirty reads by only reading committed data, but it does not prevent non-repeatable reads. Between two reads within the same transaction, another transaction may modify and commit the same row, yielding different results. Dirty reads require READ UNCOMMITTED. Preventing non-repeatable reads requires REPEATABLE READ. Preventing phantom reads requires SERIALIZABLE.
Question 12. Which ACID property is primarily enforced by write-ahead logging (WAL)?
(A) Atomicity (B) Durability (C) Isolation (D) Consistency
Correct Answer: (A)
Explanation: Write-ahead logging ensures that log records describing changes are written to stable storage before the actual data pages are modified. If a transaction must be rolled back (due to a crash or explicit ROLLBACK), the log is used to undo the changes. This is the primary mechanism for enforcing atomicity (all-or-nothing). WAL also supports durability, but its core role is atomicity through undo logging.
Query Optimization
Question 13. Given relation R with 10,000 tuples and S with 5,000 tuples, an equijoin on a common attribute with 100 distinct values in each relation produces approximately how many tuples?
(A) 500 (B) 5,000 (C) 50,000 (D) 500,000
Correct Answer: (C)
Explanation: Using the standard selectivity formula: |R JOIN S| = |R| x |S| / max(V(R, attr), V(S, attr)) = 10,000 x 5,000 / 100 = 500,000 / 10 = 50,000. Each of the 100 distinct values matches approximately 100 tuples in R and 50 tuples in S, yielding roughly 100 x 50 = 5,000 result tuples per value, times 100 values.
Question 14. A query planner chooses a nested loop join over a hash join when:
(A) The input relations are large and the join attribute has high cardinality (B) One of the input relations is small enough to fit in memory (C) The join attribute is indexed with a B+ tree (D) The result set is expected to be very large
Correct Answer: (B)
Explanation: Nested loop join is efficient when one input (the inner relation) is small enough to fit in the buffer pool. For each tuple in the outer relation, the inner relation is scanned. If the inner relation fits in memory, this requires only one scan. Hash join is preferred for large-large joins on equijoin predicates. Sort-merge join is chosen when inputs are already sorted or when the join is not an equijoin.
Question 15. Which of the following is NOT a valid heuristic used by relational query optimizers?
(A) Pushing selection operations as close to the base relations as possible (B) Performing projection early to reduce the width of intermediate results (C) Executing joins in left-to-right order as written in the query (D) Reordering joins to minimize the size of intermediate results
Correct Answer: (C)
Explanation: Query optimizers do not follow the syntactic order of joins. They use cost-based optimization to evaluate alternative join orderings and select the plan with the lowest estimated cost. Options (A), (B), and (D) are all standard optimizer heuristics: pushing selections reduces the number of tuples early, early projection reduces tuple width, and join reordering minimizes intermediate result sizes.
Intuition
Good database design is the foundation of reliable applications: The schema you choose determines how efficiently data can be queried, how efficiently it can be updated, and how well it scales. Design decisions made early are expensive to change later.
Why it matters: Database design affects application performance, data integrity, and developer productivity. A well-designed database makes applications faster and easier to maintain.
The key insight: Normalisation follows a progression (1NF, 2NF, 3NF, BCNF) where each level eliminates a specific type of redundancy, understanding why each level exists is more important than memorising the rules.
Common Mistakes
Confusing primary keys with unique keys: A primary key cannot be NULL and there can be only one per table. A unique key allows NULL values (in most RDBMS) and a table can have multiple unique keys. Choose primary keys carefully, they affect index performance and foreign key references.
Over-normalising to 5NF/6NF without justification: Boyce-Codd Normal Form handles most practical cases. Higher normal forms address rare edge cases (join dependencies) that almost never matter in real applications. Over-normalising makes queries complex without meaningful benefit.
Forgetting to index foreign keys: Foreign keys are frequently used in JOIN operations but are not automatically indexed in most RDBMS. Always create indexes on foreign key columns to avoid full table scans on joins.
Cross-References
- Site Home: Main landing page for databases notes.
- Practice: Practice problems for revision.