Non Trivial FD

1
Non Trivial FD
2
(No Transcript)
3
Candidate Key
4
(No Transcript)
5
(No Transcript)
6
(No Transcript)
7
FDs that Hold on S
8
3NF

• Consider R A, B, C, D, E, F, G, H with a set
  of FDs
• F CD?A, EC?H, GHB?AB, C?D, EG?A, H?B, BE?CD,
  EC?B
• The candidate keys are
• BEFG, CEFG, EFGH

9
F CD?A, EC?H, GHB?AB, C?D, EG?A, H?B, BE?CD,
EC?B

• No, R w.r.t. F is NOT in 3NF, because CD?A
  violates the 3NF requirements.
• i.e.
• CD?A is not trivial FD
• CD is not a superkey
• CD is not a key, but A is not part of any key of
  R either

10
Binary Decomposition Approach

• Considering R
• Keys BEFG, CEFG, EFGH
• F CD?A, EC?H, GHB?AB, C?D, EG?A, H?B, BE?CD,
  EC?B
• Decomposition 1
• CD?A is a violating FD
• R is decomposed into R1 and R2
• R1 (A,C,D)
• We need to project FDs F onto relation R1
• A A
• C CDA (C ?DA)
• D D
• AC ACD (AC?D)
• AD AD
• CD CDA (CD ? A)
• So, F1 C?DA, AC?D, CD?A

11

• R2 ( B,C,D,E,F,G,H)
• In general, we should project F onto R2. However,
  if we look carefully, we can easily see that the
  only difference between R and R2 is attribute A.
  Attribute A has never appeared on LHS of any FD.
  So, removing it wont make any change in F2.
• So, F2EC?H, GHB?B, C?D, H?B, BE?CD, EC?B
• FDs that are lost in Decomposition 1 are
• Lost GHB?A, EG?A
• Do we need further decomposition?

12

• Consider R1(A,C,D)
• F1 C?DA, AC?D, CD?A
• Since CACD, C is a key.
• C (in C?DA), CD (in CD?A), and AC (in AC?D) are
  key/super keys. Therefore, we have no violating
  FD. (So, we are done with this branch.)
• Consider R2
• F2EC?H, C?D, H?B, BE?CD, EC?B
• Keys of R2 Keys of R BEFG, CEFG, EFGH
• EC?H is not a violating FD, since H is part of a
  key.
• C?D is a violating FD, since C is not a super key
  and D is not part of any key.
• So, further decomposition is needed.

13

• Decomposition 2
• C?D is a violating FD
• R2 is decomposed into R21 and R22
• R21 (C, D)
• We need to project F2 onto relation R21
• CCD
• DD
• So, F21C?D
• R22 (B,C,E,F,G,H)
• In general, we should project F2 onto R22.
  However, if we look carefully, we can easily see
  that the only difference between R2 and R22 is
  attribute D. Attribute D has never appeared on
  LHS of any FD. So, removing it wont make any
  change in F22.
• So, F22 EC?H, H?B, BE?C, EC?B

14

• FDs that are lost in Decomposition 2 is
• Lost BE?D
• So, overall, weve lost the following FDs
• Lost GHB?A, EG?A, BE?D
• Do we need further decomposition?
• Consider R21(C,D)
• F21C?D
• Since CCD, C is a key. Therefore, we have no
  violating FD. (So, we are done with this branch.)
• Consider R22(B,C,E,F,G,H)
• Keys of R22 Keys of R2
• BEFG, CEFG, EFGH

15

• F22 EC?H, H?B, BE?C, EC?B
• EC?H is not a violating FD since H is part of a
  key.
• H?B is not a violating FD since B is part of a
  key.
• BE?C is not a violating FD since C is part of a
  key.
• EC?B is not a violating FD since B is part of a
  key.
• So, we are done with this branch.
• Overall, we have
• R1 (A, C, D) F1 C?DA, AC?D, CD?A
• R21 (C, D) F21 C?D
• R22 (B, C, E, F, G, H) F22 EC?H, H?B, BE?C,
  EC?B

16

• Since R1 includes R21 we might want to remove
  R21.
• This is a loss-less join decomposition, but it is
  not dependency preserving.
• To make the decomposition dependency preserving,
  we need to add the lost FDs as new relations.
• The lost FDs are
• Lost GHB?AB, EG?A, BE?D
• So, we add three relations
• L1(A, B, G, H) FL1 GHB?AB
• L2(A, E, G) FL2 EG?A
• L3(B, D, E) FL3 BE?D

17
Synthesis Approach

• R A, B, C, D, E, F, G, H with a set of FDs
• F CD?A, EC?H, GHB?AB, C?D, EG?A, H?B, BE?CD,
  EC?B
• The candidate keys are BEFG, CEFG, EFGH
• Canonical cover for F is
• FC C?AD, EC?H, GH?A, EG?A, H?B, BE?C

18
FC C?AD, EC?H, GH?A, EG?A, H?B, BE?C

• Now, we create the relations
• R1 A, C, D F1 C?AD
• R2 E, C, H F2 EC?H
• R3 A, G, H F3 GH?A
• R4 A, E, G F4 EG?A
• R5 B, H F5 H?B
• R6 B, C, E F6 BE?C
• Now, we need to check if at least one of the keys
  exists in the above relations.
• The candidate keys are BEFG, CEFG, EFGH

19

• Since none of these keys is in the relations,
  this decomposition is not lossless. So, we need
  to add an extra relation containing those
  attributes that form any key of R
• R7 B, E, F, G F7

20
Check lossless join

• Check if the decomposition of
• R(A, B, C, D, E, F, G) with the set of FDs
• FC?AD, E?G, FG?A, EF?A, G?B, BE?C
• into the following relations is lossless join.
• R1 A, C, D
• R2 E, C, G
• R3 A, F, G
• R4 A, E, F
• R5 B, G
• R6 B, C, E

21
Step 1- Table initialization
22
Round 1
23
(No Transcript)
24
(No Transcript)
25
(No Transcript)
26
(No Transcript)
27
(No Transcript)
28
Round 2
29
(No Transcript)