DMC Database Normalization and Design Patterns 1 — Questions and Answers
Question 1: What is First Normal Form (1NF) and what does it eliminate?
- Eliminates transitive dependencies by splitting tables
- Eliminates repeating groups and multi-valued attributes, ensuring each column holds atomic (single) values (Correct answer)
- Eliminates partial dependencies on composite keys
- Eliminates join dependencies across tables
Correct answer: Eliminates repeating groups and multi-valued attributes, ensuring each column holds atomic (single) values
1NF requires that every column holds a single, indivisible atomic value, eliminating repeating groups, arrays, and nested collections from table columns.
Question 2: What anomaly occurs when deleting a row unintentionally destroys other needed information?
- Insertion anomaly
- Deletion anomaly (Correct answer)
- Update anomaly
- Referential anomaly
Correct answer: Deletion anomaly
A deletion anomaly occurs when removing a row destroys other data that was stored in the same row but had no logical reason to be deleted.
Question 3: What anomaly occurs when inserting new data requires entering dummy or NULL values for unrelated attributes?
- Deletion anomaly
- Update anomaly
- Insertion anomaly (Correct answer)
- Referential anomaly
Correct answer: Insertion anomaly
An insertion anomaly occurs when you can't add new data without also supplying values for unrelated attributes that have no current meaning.
Question 4: What is an update anomaly in an unnormalized table?
- When a primary key value must be updated in every related table
- When the same fact is stored in multiple rows and updating one row without updating all causes inconsistency (Correct answer)
- When a stored procedure fails during a transaction
- When an index is outdated after a bulk insert
Correct answer: When the same fact is stored in multiple rows and updating one row without updating all causes inconsistency
An update anomaly occurs when a single logical fact is stored in multiple rows, so updating it in one place without updating all copies creates inconsistent data.
Question 5: What is a join dependency, and which normal form addresses it?
- A constraint that two foreign keys must reference the same parent — addressed by 3NF
- A dependency where a table can only be reconstructed losslessly by joining three or more projections — addressed by 5NF (PJNF) (Correct answer)
- A dependency that requires a JOIN operation to retrieve a value — addressed by BCNF
- A circular reference between two tables — addressed by 4NF
Correct answer: A dependency where a table can only be reconstructed losslessly by joining three or more projections — addressed by 5NF (PJNF)
A join dependency exists when a table can only be decomposed into more than two tables and still be reconstructed losslessly, which Fifth Normal Form (5NF/PJNF) eliminates.
Question 6: What is the lossless decomposition property in normalization?
- The ability to compress tables without losing row data
- The guarantee that decomposing a table into smaller tables and then rejoining them reproduces the original table exactly (Correct answer)
- The property that all foreign keys retain their referenced values after splitting
- The rule that normalization never reduces the number of tables
Correct answer: The guarantee that decomposing a table into smaller tables and then rejoining them reproduces the original table exactly
Lossless decomposition guarantees that when you split a table into two or more tables, joining them back together returns exactly the original data with no extra or missing rows.
DMC Database Normalization and Design Patterns 1
What is First Normal Form (1NF) and what does it eliminate?