DMC Database Normalization and Design Patterns

Question 1

What is First Normal Form (1NF) and what does it eliminate?

Accepted Answer

Eliminates repeating groups and multi-valued attributes, ensuring each column holds atomic (single) values

Answer

Eliminates transitive dependencies by splitting tables

Answer

Eliminates partial dependencies on composite keys

Answer

Eliminates join dependencies across tables

Question 2

What anomaly occurs when deleting a row unintentionally destroys other needed information?

Accepted Answer

Deletion anomaly

Answer

Insertion anomaly

Answer

Update anomaly

Answer

Referential anomaly

Question 3

What anomaly occurs when inserting new data requires entering dummy or NULL values for unrelated attributes?

Accepted Answer

Insertion anomaly

Answer

Deletion anomaly

Answer

Update anomaly

Answer

Referential anomaly

Question 4

What is an update anomaly in an unnormalized table?

Accepted Answer

When the same fact is stored in multiple rows and updating one row without updating all causes inconsistency

Answer

When a primary key value must be updated in every related table

Answer

When a stored procedure fails during a transaction

Answer

When an index is outdated after a bulk insert

Question 5

What is a join dependency, and which normal form addresses it?

Accepted Answer

A dependency where a table can only be reconstructed losslessly by joining three or more projections — addressed by 5NF (PJNF)

Answer

A constraint that two foreign keys must reference the same parent — addressed by 3NF

Answer

A dependency that requires a JOIN operation to retrieve a value — addressed by BCNF

Answer

A circular reference between two tables — addressed by 4NF

Question 6

What is the lossless decomposition property in normalization?

Accepted Answer

The guarantee that decomposing a table into smaller tables and then rejoining them reproduces the original table exactly

Answer

The ability to compress tables without losing row data

Answer

The property that all foreign keys retain their referenced values after splitting

Answer

The rule that normalization never reduces the number of tables