MusicGroups

GroupName	ArtistName	YearFormed	YearBorn	YearJoined
----------------------------------------------------------------------------
Queen		Brian May        1971           1947            1971
Queen		Freddy Mercury   1971           1946            1971
Queen		John Deacon      1971           1951            1972
Queen		Roger Taylor     1971           1949            1971
Smile		Brian May        1969           1947            1969
Smile		Freddy Mercury   1969           1946            1969
Smile		Roger Taylor     1969           1949            1969
Smile		Tim Staffel      1969           1949            1969


Normalization Theory
======================

First normal form: Attributes must have simple values.


Functional Dependency (fd) for a relation R(A,B,C,D) is an expression
of the form

X -> Y  where X is a set of attributes of R and Y is a set of attributes of R

such that:  whenever two tuples in R have the same values for all attributes in
X, then they must have the same values for all attributes in Y.


MusicGroups(GroupName, ArtistName, YearFormed, YearBorn, YearJoined)

GroupName -> YearFormed
ArtistName -> YearBorn
GroupName ArtistName -> YearJoined


Students(RIN, Name, Class, Major, Email)

RIN -> Name Email Class

RIN -> RIN
RIN -> RIN Name
Email -> RIN
Email -> Name Class


Class(CRN, CourseCode, CourseNum, CourseName, Semester, Year, Section, Instructor)

CRN -> CourseCode CourseNum CourseName Semester Year Section
CourseCode CourseNum Semester Year Section -> CRN CourseName

-------

Rules of inference for set of functional dependencies:

Given a relation R and a set F of functional dependencies, we can infer
new functional dependencies as follows:

1. Trivial functional dependency:

Whenever Y subset or equal to X, then X -> Y is trivially true.

A -> A
A B -> A
A B -> B
A B C -> B C

2. Splitting rule:

X -> Y Z can be divided into:

X -> Y
X -> Z

A -> B C, then A -> B, A -> C

3. Combining rule:

X -> Y and X -> Z is true then, X -> Y Z

4. Transitivity

X -> Y and Y -> Z is true, then  X -> Z

A -> B C
B C -> D

then

A -> D

5. Augmentation:

If X -> Y is true, then X Z -> Y Z

---------

Closure of a set of f.d.
------------------------

Given a set F of functional dependencies, the set of all possible
fd implied by F is called its closure, denoted by F+

F+ = set of all fds implied by F

R(A,B,C)

F = {A->B, B->C}

F+ = {A->B, B->C, A->C, A->BC, AC->BC, AB->BC, A->A, B->B, C->C, AB->AB, ...}

Implication
-------------
A functional dependency is implied by a set F, if it is in the closure!


Key:
-----
A key for a relation R is a functional dependency X -> Y in F+ such that

1. Y is the set of all attributes, and
2. no subset of X implies all the attributes in R (minimal)

Example:

MusicGroups(GroupName, ArtistName, YearFormed, YearBorn, YearJoined)

GroupName -> YearFormed
ArtistName -> YearBorn
GroupName ArtistName -> YearJoined



GroupName ArtistName -> YearJoined  given

ArtistName GroupName -> ArtistName YearFormed	(augmentation)
ArtistName GroupName -> YearBorn GroupName (augmentation)

GroupName ArtistName -> ArtistName YearFormed YearBorn GroupName YearJoined 

Key: GroupName ArtistName

--------------

Closure of a set of attributes (X+)
-----------------------------------

Given a set of attribute X and a set of fd F, closure of X (all
attributes implied by X) is computed as follows.

X+ = {X}   ## initialize

Repeat until X+ does not change:
    Find a functional dependency  Y -> Z in R
        such that X+ contains all attributes in Y
        then add Z to X+

If X+ is the closure of X, then  X->X+ is in F+.

Of X-> X+ is implied by F.


R(A,B,C,D,E)

A->B
BD->C
CD->E

A+ = {A,B}

AD+ = {A,B,C,D,E}

BD+ = {B,D,C,E}

----------------------

Given a relation R and a set F of fd, X is a key
if

1. X+ is all attributes in R and
2. no subset of X implies all attributes in R.

R(A,B,C,D,E)

A->C
B->D
AB-> E

AB+={A,B,C,D,E} is a key
A+ = {A,C}
B+ = {B,D}