Transcript Can we fill in missing info? - University of California

StudentClass Table
sid name
addr
dept cnum title
unit
301 James 11 West
CS
143
Database
04
105 Elaine 84 East
EE
284
Signal Processing 03
301 James 11 West
ME
143
Mechanics
05
105 Elaine 84 East
CS
143
Database
04
207 Susan 12 North
EE
128
Microelectronics
03
1
Missing Info?
sid name
dept cnum title
unit
301 James 11 West
CS
143
Database
04
105 Elaine 84 East
EE
284
Signal Processing 03
301 James
?
ME
143
Mechanics
05
84 East
CS
143
?
?
EE
128
105
?
addr
207 Susan 12 North
Microelectronics
03
2
Closure of Attributes X
Start with X+ = X
Repeat until no change in X+
If there is Y -> Z with Y  X+, then
Add Z to X+
3
BCNF Decomposition Algorithm
For any
If (X
X
X
1)
2)
R in the schema
-> Y holds on R AND
-> Y is non-trivial AND
does not contain a key), then
Compute X+ (X+: closure of X)
Decompose R into R1(X+) and R2(X, Z)
// X becomes common attributes
// Z: all attributes in R except X+
Repeat until no more decomposition
4
Class(cnum, ta, sid)
cnum
TA
sid
Class 143
TA: Tony, James
Students: 100, 101, 103
143
Tony
100
143
Tony
101
143
Tony
103
Class 248
TA: Tony, Susan
Students: 100, 102
143
James
100
143
James
101
143
James
103
248
Tony
100
248
Tony
102
248
Susan
100
248
Susan
102
5
Inference rules for FDs and MVDs
Reflexivity: If Y  X, then X -> Y
Augmentation: If X -> Y, then ZX -> ZY
Transitivity: If X -> Y and Y -> Z, then X -> Z
Complementation: If X ->> Y, then X ->> R – (YX)
MVD augmentation: If X ->> Y and W  Z, then
ZX ->> WY
6. MVD transitivity: If X ->> Y and Y ->> Z, then
X ->> Z – Y
7. Replication: If X -> Y, then X ->> Y
8. Coalescence: If X ->> Y, W -> Y (Z  Y, WY=), then
X -> Z
The set of above rules are sound and complete
1.
2.
3.
4.
5.
6
4NF Decomposition Algorithm
For any R in the schema
If (non-trivial X ->> Y holds on R AND
X does not contain a key), then
Decompose R into R1(X,Y) and R2(X,Z)
// X becomes common attributes
// Z: all attributes in R except (X, Y)
Repeat until no more decomposition
7