Normalization

Normalization

technique used to produce suitable relations that support enterprise data requirements to minimize data redundancy and create accurate representations in data, its relationships, and constraints

Lossless-join property

find any instance in the original relation from corresponding instances in the smaller relations

Dependency Preservation Property

enforce constraints on original relation by enforcing them on each of the smaller ones

Insertion anomaly

Difficulties/inconsistencies that arise when attempting adding new data in DB

Deletion Anomaly

unintended loss of data when a deleting a row in DB

Modification Anomaly

data inconsistencies when updating data that appears in multiple places

Functional dependency

If B is fully dependent on A, every value in A is associated with 1 value in B

Determinant

attribute on left hand side of functional dependacy

Full functional dependency

B is fully functional dependent if functionally dependent on A but not a proper subset of A

Partial Dependent

attribute is functionally dependent on subset of primary key

Transitive Dependency

When A determines B and B determines C, C is transitive dependent on A via B

Multi-valued dependency

for each value of A, there is a set of values for B and C, but B and C are independent of each other

Join Dependency

Every legal values is equal to the join of its projections

Reflexivity

If B is a subset of A, then A -> B

Augmentation

If A → B, AC → BC (adding same attribute to each side)

Transitivity

If A → B and B → C, A → C

Self Determination

A → A

Decomposition

If A → B, C, A → B and A → C (determinant pointing to multiple attributes can have them broken down)

Union

A → B and A → C, A → B, C

Composition

If A → B and C→ D, A, C → B, D (2 separate dependencies can combine their determinants and dependents

UNF

table containing 1 or more repeating groups

1NF

relations where intersection of each row/column contains one value and all repeating groups are removed

2NF

1NF relations and every nonprimary key attribute is fully functionally dependent on the primary key by removing partial dependencies

3NF

2NF relations where no nonprimary key attribute is transitively dependent on the primary key

BCNF

relation where every determinant is a candidate key

4NF

BCNF relation without containing nontrivial multi-valued dependency

5NF

relation with no join dependency

3NF vs BCNF

Every relation in BCNF is also in 3NF, but a relation in 3NF may not be in BCNF.

Partial Dependency vs. Transitive Dependency

Partial dependencies violate 2NF and occur when attributes depend on only a portion of the primary key. Transitive dependencies violate 3NF and occur when a non-key attribute depends on another non-key attribute, which in turn depends on the primary key.