5 Normalization and Data Integrity Free Online FlashCards

What is the main purpose of normalization?

Normalization organizes related data to reduce unnecessary duplication and prevent inconsistent updates.

What does a functional dependency $X \to Y$ state?

A functional dependency means each value of attribute set $X$ is associated with exactly one value of attribute set $Y$.

What is a candidate key?

A candidate key is a minimal set of attributes that uniquely identifies a row.

What is a composite key?

A key made up of multiple attributes is a composite key. In OrderItems, the pair $(OrderID, ProductID)$ identifies an order-product row.

What is an update anomaly?

An update anomaly occurs when a repeated fact is changed in some rows but not others, leaving inconsistent values.

What is an insertion anomaly?

An insertion anomaly occurs when a fact, such as a supplier, cannot be recorded unless an unrelated fact, such as a product, is also recorded.

What is a deletion anomaly?

A deletion anomaly occurs when deleting one fact, such as a supplier’s last product row, also removes the only stored record of another fact.

What is the defining requirement of 1NF?

A relation is in 1NF when each row-column intersection contains a single value of the appropriate type, not a list or repeating group.

What does 2NF require beyond 1NF?

A relation is in 2NF if it is in 1NF and every non-prime attribute depends on the whole of every candidate key.

Why is OrderDate a partial dependency in OrderLine?

In OrderLine, $OrderID \to OrderDate$ is partial because the composite key is $(OrderID, ProductID)$, but OrderDate depends only on OrderID.

What does 3NF require beyond 2NF?

A relation is in 3NF if it is in 2NF and has no inappropriate dependency of a non-key attribute on a key through another non-key attribute.

How does ProductID determine SupplierName transitively?

Because $ProductID \to SupplierID$ and $SupplierID \to SupplierName$, ProductID determines SupplierName indirectly through SupplierID.