Normalization#

Artist	DJoin	DLeft	DOB	Group	GFormed	Genre
John Lennon	1960	1969	1940	Beatles	1960	British Invasion
Paul McCartney	1960	1970	1942	Beatles	1960	British Invasion
George Harrison	1960	1970	1943	Beatles	1960	British Invasion
Ringo Starr	1962	1970	1940	Beatles	1960	British Invasion
Stuart Sutcliffe	1960	1961	1940	Beatles	1960	British Invasion
Pete Best	1960	1962	1941	Beatles	1960	British Invasion
John Lennon	1960	1969	1940	Beatles	1960	Pop/Rock
Paul McCartney	1960	1970	1942	Beatles	1960	Pop/Rock
George Harrison	1960	1970	1943	Beatles	1960	Pop/Rock
Ringo Starr	1962	1970	1940	Beatles	1960	Pop/Rock
Stuart Sutcliffe	1960	1961	1940	Beatles	1960	Pop/Rock
Pete Best	1960	1962	1941	Beatles	1960	Pop/Rock
George Harrison	1988	1992	1943	The Traveling Wilburys	1988	Pop/Rock
Roy Orbison	1988	1992	1936	The Traveling Wilburys	1988	Pop/Rock
Bob Dylan	1988	1992	1941	The Traveling Wilburys	1988	Pop/Rock
Tom Petty	1988	1992	1950	The Traveling Wilburys	1988	Pop/Rock

Key is a minimal set of attributes such that no two tuples can have the same value for the key.

Key: Group, Artist, Genre

Lots of repeated information, if you change it in one place, you need to change it everywhere
Coupled information, I cannot enter a group without knowing at least one artist in it.
If I delete someone from a group, I may loose other information about them like DOB of an artist.

Functional dependencies#

Given a relation R, a functional dependency (fd) is an expression of the form

X -> Y

where

X and Y are a set of attributes in R Means: whenever X->Y is true, then if two tuples have the same values for all attributes in X, then it must have the same value for all attributes in Y.

Artist -> DOB
Group -> DFormed
Artist Group -> DJoin DLeft #assuming artist cannot join back

Artist -> Artist
Artist Group -> Group ##trivial
Artist Group -> DFormed
Artist Group -> DJoin
Artist Group -> DLeft

Inference rules for functional dependencies

If \(Y\subseteq X\) then X->Y is true. (Trivial, true for any relation)
If X->Y and Y->Z then X->Z is also true. (Transitivity)
If X->YZ then X->Y and X->Z is also true. (Decomposition, only for the right hand side!)
If X->Y then XZ->YZ is also true. (Accumulation)

Y = {Group}
X = {Artist, Group}
Y is a subset of X, X->Y

Closure of a set of functional dependencies (F+)#

If I have a set of functional dependencies F for a relation R, the closure (F+) is the set of all functional dependencies in F or are implied by F (i.e. obtained by applying the inference rules to F).

MusicGroup(Artist, DJoin, Dleft, DoB, DFormed, Group, Genre)

F= {Artist -> DOB, Group -> DFormed, Artist Group -> DJoin DLeft }

A superkey for a relation R and a set of fds F is a set of attributes X such that X->Y is in F+ and Y is the set of all attributes in R.

A key is a superkey that is minimal.

Normalization

Contents

Normalization#

Functional dependencies#

Closure of a set of functional dependencies (F+)#