Lecture 4 - In class discussion
-------------------------------
R(A,B,C,D)
AB->C
B->D
A B C D
a1 b1 c1 d1
a1 b2 c3 d2
a2 b3 c2 d1
AB together uniquely determines a tuple in R,
then AB is superkey. Let's check minimality: A is not a key, B is not a key.
AB is minimal, it is a key!
R(A,B,C,D)
F = {AB->C, B->D}
Check if AB->ABCD is in F+
AB+ = {A,B,C,D}
AB->ABCD
B+ = {B,D} not key
A+ = {A} not key
Key: AB
Superkey: AB, ABC, ABD, ABCD
Prime attributes: A, B
Closure of a set of attributes
Superkeys: set of attribuets X that imply all attributes, such that X+ is all attributes in the relation
Keys: Superkey but also minimal, no subset of it is a key.
Prime attributes: Any attribute in a key
----------------
BCNF: A relation R given a set of F is in BCNF iff for all fds X->Y in F, either one of hte following is true:
1. X is a superkey, or
2. X->Y is trivial
R1(A,B,C,D,E) F = {A->BC, A->DE}
Key: A
A->BCDE A is a superkey, so R1 is in BCNF.
R2(A,B,C,D,E,F) F = {A->BCDE}
Key: AF
A->BCDE No, A is not a superkey
Not in BCNF
Students(RIN, Name, Email, Major)
RIN->Name, Email
Not in BCNF
R3(A,B,C,D,E,F) F = {A->BCD D->EF}
Key: A
A->BCD ok
D->EF D is not a superkey
Not in BCNF
MusicGroups(GroupName, ArtistName, DoB, YearFounded)
GroupName -> YearFounded no
ArtistName -> DoB no
Key: GroupName ArtistName
Not in BCNF
R4(A,B,C,D,E,F,G) F={AC->BD, ACD->AEFG, BC->B, EFG->AC}
Key: AC, EFG
AC->BD ok, superkey on lhs
ACD->AEFG ok, superkey on lhs
BC->B trivial
EFG->AC ok, superkey on lhs
---------------
BCNF: Good! It enforces the notion of modularity, each relation should be a single module
-------------------
3NF: A relation R given a set of F is in 3NF iff for all fds X->Y in F, either one of the following is true:
1. X is a superkey, or
2. X->Y is trivial, or
3. all attributes on the right hand side are prime attributes.
If a relation is in BCNF, then it is in 3NF.
R5(A,B,C,D,E,F,G) F={AC->BD, ACD->AEFG, BC->B, EFG->A}
Key: AC, CEFG
Prime attributes: A,C,E,F,G
AC->BD Ok, superkey
ACD->AEFG Ok, superkey
BC->B Ok, trivial
EFG->A EFG is not a superkey, but A is a prime attribute!
Not in BCNF because of EFG->A, but it is in 3NF
Address(Street, State, City, Zip)
Street, State, City -> Zip
Zip -> State, City
Key: Street, State, City or Zip, Street
Not in BCNF, in 3NF
-------------------------
Minimal basis: If a set F is minimal, it cannot be simplified.
F1 = {A->B, B->C, A->C}
F2 = {A->B, A->C}
Is F1 equivalent to F2?
(=>) F1 implies F2, everything in F2 is implied by F1.
Yes! F2 subset of F1 already!
(<=) F2 implies F1, everything in F1 is implied by F2.
A->B in F2
A->B in F2
B->C in F1, is B->C implied by F2?
Find B+ with respect to F2, B+={B}, C is not in it, B->C is not implied by F2.
i.e. F2 subset of F1, but F1 not a subset of F2.
Examples:
F1 = {A->B, B->C, A->C} --> simplify F1' = {A->B, B->C}
F2 = {A->B, B->C, A->A} --> simplify F2'={A->B, B->C}
F3 = {A->B, AB->C, C->D} --> simplify F3' = {A->B, A->C, C->D}
--------------------------
Minimal Basis
---------------
F={AC->BD, ABCD->AEFG, BC->B, EF->A, EFC-> G}
Step 1: Convert to basis
AC->B
AC->D
ABCD->A
ABCD->E
ABCD->F
ABCD->G
BC->B
EF->A
EFC->G
Step 2: Remove trivial fds
AC->B
AC->D
ABCD->E
ABCD->F
ABCD->G
EF->A
EFC->G
Step 3: Remove one fd at a time and see if it is implied by the rest
F1:
AC->B
AC->D
ABCD->E
ABCD->F
ABCD->G
EF->A
EFC->G *** can we remove this?
------------
F2:
AC->B
AC->D
ABCD->E
ABCD->F
ABCD->G
EF->A
In F2, EFC+ = {E,F,C,A,B,D,G}
since G in EFC+, then I can remove EFC->G
Step 4. Try to remove one attribute from left, XY->Z changed to X->Z, then check if X+ is the same!
F1:
AC->B
AC->D
ABCD->E
ABCD->F
ABCD->G
EF->A
F2:
AC->B
AC->D
ACD->E ***
ABCD->F
ABCD->G
EF->A
ACD+ in F1: {A,C,D,B,E,F,G} in F2: {A,B,C,D,E,F,G}
F1:
AC->B
AC->D
ACD->E
ACD->F
ACD->G
EF->A
F2:
AC->B
AC->D
AC->E ***
ACD->F
ACD->G
EF->A
AC+ = {A,C,B,D,E,F,G} in F1, and same in F2
This is true for others ACD as well (try yourself), you will get:
AC->B
AC->D
AC->E
AC->F
AC->G
EF->A
Minimal
Given
F1 = {A->B, AB->C, C->D}
Can we remove A?
F2 = {A->B, B->C **, C->D}
in F1, B+ = {B}
in F2, B+ = {B,C,D}
Not the same, cannot remove
F1 = {A->B, AB->C, C->D}
Can we remove B?
F2 = {A->B, A->C, C->D}
in F1, A+ = {A,B,C,D}
in F2, A+ = {A,B,C,D}
SAME, CAN REMOVE B.
----