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
new group Smile2 ---- formed in 2020, don't know the artists
1. Information is repeated (YearFormed, YearBorn) and if any change is made,
it has to be made everywhere otherwise data consistency is a problem.
2. Since information is coupled, if we delete one tuple, we may loose
extra information. If we don't want to share Joh Deacon as a member of
Queen, we also loose his date of birth.
3. Key for this relation is GroupName, ArtistName. ArtistName cannot
be empty, so I cannot store any info about the group only.
MusicGroups(GroupName, ArtistName, YearFormed, YearBorn, YearJoined)
GroupName -> YearFormed
ArtistName -> YearBorn
GroupName ArtistName -> YearJoined
Key: ArtistName GroupName
-----------------------
FUNCTIONAL DEPENDENCY:
A 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.
KEY:
Given a relation R and a set F of fds, X is a key if
1. X+ is all attributes in R (in other words: X->X+ is in F+),
2. no subset of X implies all attributes in R (minimality).
SUPERKEY (superset of key):
Given a relation R and a set F of fds, X is a superkey if
X+ is all attributes in R (in other words: X->X+ is in F+).
PRIME ATTRIBUTE:
Given a relation R and a set F of fds, a prime attribute is an
attribute in a key.
BASIS:
A set of functional dependencies forms a basis, if there is only
one attribute on the right hand side of each functional dependency.
BOYCE-CODD NORMAL FORM (BCNF):
Given a relation R and a set of functional dependencies F.
R is in BCNF iff for all functional dependencies in F of the form X->Y
one of the following is true:
1. X is a superkey of R, or
2. X->Y is trivial.
(in other words: If for all functional dependencies in F that are NOT
trivial, the left hand side is a superkey, then R is in BCNF. )
THIRD NORMAL FORM (3NF):
Given a relation R and a set of functional dependencies F that forms a
basis (only one attribute on the right hand side).
R is in 3NF iff for all functional dependencies in F of the form X->Y
one of the following is true:
1. X is a superkey of R, or
2. X->Y is trivial, or
3. Y is a prime attribute.
If a relation is in BCNF, then it is also in 3NF.
---------------
R1(A,B,C,D)
AB->C ok (AB is a superkey)
AB->D ok (AB is a superkey)
C->A not ok (C is not a superkey), NOT IN BCNF
A->A ok (trivial)
ABD->C ok (ABD is a superkey)
Key: AB, BC
BC+ = {B,C,A,D}
Superkeys: AB, BC, ABC, ABCD, BCD
Prime attributes: A,B,C
NOT IN BCNF
3NF?
AB->C ok (AB is a superkey)
AB->D ok (AB is a superkey)
C->A ok (C is not a superkey, but A is a prime attribute)
A->A ok (trivial)
ABD->C ok (ABD is a superkey)
BUT, it is in 3NF
---------------
R2(A,B,C,D)
AB->C
C->D
Key: AB
AB+ = {A,B,C,D}
Prime attributes: A,B
NOT in BCNF due to C->D
NOT in 3NF
---------------
R3(A,B,C,D)
A->B
A->C
A->D
Key: A
A+ = {A,B,C,D}
Prime attribute: A
IN BCNF, 3NF!
---------------
R4(A,B,C,D,E)
A->B
A->C
BD->E
Key: AD
Prime attributes: A,D
Not in BCNF
Not in 3NF: A->B, not trivial, A is not a superkey and B is not a prime attribute
-----------------
R5(A,B,C,D,E)
AB->C
AB->D x not in 3NF
C->A
Key: ABE,BCE
Prime attributes: A,B,C,E
Not in BCNF, not in 3NF
------------
MusicGroups(GroupName, ArtistName, YearFormed, YearBorn, YearJoined)
GroupName -> YearFormed
ArtistName -> YearBorn
GroupName ArtistName -> YearJoined
Key: ArtistName GroupName
Not in BCNF, because of the first two functional dependencies
Not in 3NF