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}