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