Normalization

Functional dependency

A generalization of the notion of a key and allow us to express constraints that cannot be expressed using super keys.

Join dependencies

Generalize multivalued dependencies

Complete

Generate all functional dependencies that hold. If a dependency is true, we can eventually derive it using these rules.

Sound

Generate only functional dependencies that actually hold. Any dependency generated by these rules will always hold in the relation.

BCNF

A relation schema R is in respect to a set F of functional dependencies if for all functional dependencies in F+ of the form a → B where a (_ R and B (_ R, at least one of the following holds: • a → B is trivial (i.e., B (_ a) • a is a super key for R

Extraneous attributes

An attribute of a functional dependency in F that can be removed without changing F+.

Project-join Normal Form (PJNF) (5NF)

Ensures that a relation cannot be decomposed into smaller relations without losing information due to join dependencies.

Domain-key Normal Form (DKNF)

Ensures that all constraints in the database are only defined using domain constraints (data types) and key constraints without needing extra dependency rules.

Union rule

If a → B holds and a → y holds, then a → By holds

Pseudotransitivity rule

If a → B holds and yB → S holds, then ay → S holds.

Transitivity rule

If a → B, and B → y, then a → y

Augmentation rule

If a → B, then ya → yB

Decomposition rule

If a → By holds, then a → B holds and a → y holds

Third normal form

If for all: a → B in F+ at least one of the following holds: • a → B is trivial (i.e., B E a) • a is a super key for R • Each attribute A in B - a is a prime attribute, meaning it is contained in at least one candidate key of R.

First normal form

If the domains of all attributes of R are atomic.

Lossless Decomposition

If there is no loss of information by replacing R with the two relation schemas R1 U R2

Advantages to 3NF

It is always possible to obtain a 3NF design without sacrificing losslessness or dependency preservation.

Dependency Preservation

Testing functional dependency constraints every time the database is updated can be costly

When an E-R diagram is carefully designed, and all entities are correctly identified.

The tables generated from the E-R diagram should not require further normalization.

Second normal form

A level of database normalization that aims to eliminate redundancy and ensure that all non-key attributes are fully functionally dependent on the entire primary key

Canonical Cover

a minimal set of functional dependencies that is equivalent to the original set but contains no extraneous attributes

K is a candidate key

for R if and only if • K → R, and • for no a ( K, a → R

K is a superkey

for relation schema R if and only if K → R

Reflexive rule

if B (_ a, then a → B

A functional dependency is trivial

if it is satisfied by all instances of a relation. ▪ In general, a →B is trivial if B (_ a

Goal for a relational database design:

• BCNF. • Lossless join. • Dependency preservation.

Disadvantages to 3NF

• We may have to use null values to represent some of the possible meaningful relationships among data items. • There is the problem of repetition of information

Functional dependency a → B holds on R

• if and only if for any legal relations r(R), • whenever any two tuples t1 and t2 of r agree on the attributes a, they also agree on the attributes B. • That is: t1[a] = t2 [a] => t1[B] = t2 [B]

Closure of a Set of Functional Dependencies

▪ Given a set F set of functional dependencies, there are certain other functional dependencies that are logically implied by F. • If A → B and B → C, then we can infer that A → C ▪ The set of all functional dependencies logically implied by F is the closure of F. ▪ We denote the closure of F by F+.

Goals of Normalization

▪ Let R be a relation scheme with a set F of functional dependencies. ▪ Decide whether a relation scheme R is in "good" form. ▪ In the case that a relation scheme R is not in "good" form, need to decompose it into a set of relation scheme {R1, R2, ..., Rn} such that: • Each relation scheme is in good form. • The decomposition is a lossless decomposition. • Preferably, the decomposition should be dependency preserving.

The set of all functional dependencies logically implied by F

▪ The closure of F. ▪ We denote the closure of F by F+.