Database Normalization and Design Patterns Flashcards

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

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.

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

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.

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

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.

What is an update anomaly in an unnormalized table?

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.

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

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.

What is the lossless decomposition property in normalization?

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.