Quiz 3

15 - Canonical Cover & Extraneous Attributes

2748 words
14 min read
Python Week 1: the first filter for runtime behavior
Visual companion
Python
Type and operator map

Python Week 1: the first filter for runtime behavior

Open galleryRelated surface
View
Revision summary

What this note is really saying

Short form

# 15 - Canonical Cover & Extraneous Attributes ## ๐ŸŽฏ Learning Objectives After reading this topic, you will be able to: - Compute the canonical cover of a set of functional dependencies - Identify and remove extraneous attributes - Find candidate keys using attribute closure techniques - Count superkeys from candida...

15 - Canonical Cover & Extraneous Attributes

๐ŸŽฏ Learning Objectives

After reading this topic, you will be able to:
  • Compute the canonical cover of a set of functional dependencies
  • Identify and remove extraneous attributes
  • Find candidate keys using attribute closure techniques
  • Count superkeys from candidate keys
  • Understand why canonical cover matters for checking FDs efficiently

๐Ÿ“‹ Prerequisites

๐Ÿ“– Core Content

15.1 Intuition: Minimal FD Set

When you update a database, the DBMS must check that the update doesn't violate any functional dependencies. If you have 100 FDs, checking all 100 every time is expensive. A canonical cover (or minimal cover) FcF_c is a minimal set of FDs that is equivalent to the original set FF. It has:
  • No redundant FDs (each FD is necessary)
  • No extraneous attributes on either side
  • Only single attributes on the RHS
Why This Matters: Canonical cover reduces the number and complexity of FDs the system needs to check. It's also heavily tested in exams (~12% of all questions involve FDs and canonical cover).

15.2 Algorithm to Compute Canonical Cover

sql
canonical_cover(F):
    // Step 1: Decompose RHS to single attributes
    Replace each FD X โ†’ {Aโ‚, Aโ‚‚, ..., Aโ‚™} with X โ†’ Aโ‚, X โ†’ Aโ‚‚, ..., X โ†’ Aโ‚™
    // Step 2: Remove extraneous attributes from LHS
    For each FD X โ†’ A:
        For each attribute B in X:
            If (X - {B}) โ†’ A can be derived from F, remove B from X
    // Step 3: Remove redundant FDs
    For each FD X โ†’ A:
        If F - {X โ†’ A} implies X โ†’ A, remove X โ†’ A
    Return Fc

15.3 Extraneous Attributes

An attribute is extraneous in Xโ†’YX \rightarrow Y if removing it doesn't change the closure of F.

On the LHS (Left-Hand Side)

Given FF and an FD ABโ†’CAB \rightarrow C:
  • If AA is extraneous, then Bโ†’CB \rightarrow C can be derived from FF
  • Check: Compute {B}+\{B\}^+ using FF. If Cโˆˆ{B}+C \in \{B\}^+, then AA is extraneous

On the RHS (Right-Hand Side)

Given FF and an FD Aโ†’BCA \rightarrow BC:
  • If BB is extraneous, then Aโ†’BA \rightarrow B can be derived from Fโˆ’{Aโ†’BC}โˆช{Aโ†’C}F - \{A \rightarrow BC\} \cup \{A \rightarrow C\}
  • Check: Does FF imply Aโ†’BA \rightarrow B? Compute {A}+\{A\}^+ using Fโ€ฒ=Fโˆ’{Aโ†’BC}โˆช{Aโ†’C}F' = F - \{A \rightarrow BC\} \cup \{A \rightarrow C\}. If Bโˆˆ{A}+B \in \{A\}^+, then BB is extraneous.

15.4 Worked Examples: Canonical Cover

Example 1: Simple Canonical Cover

Find the canonical cover of F={Aโ†’BC,Bโ†’C,ABโ†’C}F = \{A \rightarrow BC, B \rightarrow C, AB \rightarrow C\}. Step 1: Decompose RHS to single attributes F={Aโ†’B,Aโ†’C,Bโ†’C,ABโ†’C}F = \{A \rightarrow B, A \rightarrow C, B \rightarrow C, AB \rightarrow C\} Step 2: Remove extraneous attributes from LHS Check ABโ†’CAB \rightarrow C:
  • Is AA extraneous? Compute {B}+\{B\}^+ using F.
    • result = {B}
    • Bโ†’CB \rightarrow C: B โŠ† result โ†’ result = {B, C}
    • C โˆˆ {B}+\{B\}^+? Yes! So A is extraneous.
    • Remove A: Bโ†’CB \rightarrow C remains (already exists in F)
  • Is BB extraneous? Compute {A}+\{A\}^+ using F.
    • result = {A}
    • Aโ†’BA \rightarrow B โ†’ {A, B}
    • Aโ†’CA \rightarrow C โ†’ {A, B, C}
    • C โˆˆ {A}+\{A\}^+? Yes! So B is extraneous.
    • Remove B: Aโ†’CA \rightarrow C remains (already exists in F) After checking, ABโ†’CAB \rightarrow C is redundant (covered by Aโ†’CA \rightarrow C and Bโ†’CB \rightarrow C). Remove it. F={Aโ†’B,Aโ†’C,Bโ†’C}F = \{A \rightarrow B, A \rightarrow C, B \rightarrow C\} Step 3: Remove redundant FDs Check Aโ†’BA \rightarrow B: Can we derive it from the remaining FDs? Fโˆ’{Aโ†’B}={Aโ†’C,Bโ†’C}F - \{A \rightarrow B\} = \{A \rightarrow C, B \rightarrow C\} Compute {A}+\{A\}^+ using this set: {A} โ†’ Aโ†’CA \rightarrow C โ†’ {A, C}. B โˆ‰ {A, C}. So Aโ†’BA \rightarrow B is NOT redundant. Check Aโ†’CA \rightarrow C: Fโˆ’{Aโ†’C}={Aโ†’B,Bโ†’C}F - \{A \rightarrow C\} = \{A \rightarrow B, B \rightarrow C\} Compute {A}+\{A\}^+: {A} โ†’ Aโ†’BA \rightarrow B โ†’ {A, B} โ†’ Bโ†’CB \rightarrow C โ†’ {A, B, C}. C โˆˆ {A, C}! So Aโ†’CA \rightarrow C is redundant. Check Bโ†’CB \rightarrow C: Fโˆ’{Bโ†’C}={Aโ†’B,Aโ†’C}F - \{B \rightarrow C\} = \{A \rightarrow B, A \rightarrow C\} Compute {B}+\{B\}^+: {B}. C โˆ‰ {B}. So Bโ†’CB \rightarrow C is NOT redundant. Canonical cover: Fc={Aโ†’B,Bโ†’C}F_c = \{A \rightarrow B, B \rightarrow C\}

Example 2: More Complex Canonical Cover

F={Aโ†’BCDE,CDโ†’E,Bโ†’D,Eโ†’A}F = \{A \rightarrow BCDE, CD \rightarrow E, B \rightarrow D, E \rightarrow A\} Step 1: Decompose RHS F={Aโ†’B,Aโ†’C,Aโ†’D,Aโ†’E,CDโ†’E,Bโ†’D,Eโ†’A}F = \{A \rightarrow B, A \rightarrow C, A \rightarrow D, A \rightarrow E, CD \rightarrow E, B \rightarrow D, E \rightarrow A\} Step 2: Remove extraneous LHS attributes Check CDโ†’ECD \rightarrow E:
  • Is C extraneous? Compute {D}+\{D\}^+ using F.
    • result = {D}
    • Bโ†’DB \rightarrow D: B not in result. Eโ†’AE \rightarrow A: E not in result.
    • Actually, {D}+={D}\{D\}^+ = \{D\}. E โˆ‰ {D}+\{D\}^+, so C is NOT extraneous.
  • Is D extraneous? Compute {C}+\{C\}^+ using F.
    • result = {C}. C doesn't appear on any LHS except in CD.
    • {C}+={C}\{C\}^+ = \{C\}. E โˆ‰ {C}+\{C\}^+, so D is NOT extraneous. Step 3: Remove redundant FDs Check Aโ†’BA \rightarrow B: Can BB be derived from others? Fโˆ’{Aโ†’B}=...F - \{A \rightarrow B\} = ... Need to check. Honestly computing by hand is complex. The key insight: since Eโ†’AE \rightarrow A and all Aโ†’... FDs, much is interconnected. A simpler approach might be needed. Let me try a different method. Given F={Aโ†’BC,CDโ†’E,Bโ†’D,Eโ†’A}F = \{A \rightarrow BC, CD \rightarrow E, B \rightarrow D, E \rightarrow A\} Step 1: Decompose RHS F={Aโ†’B,Aโ†’C,CDโ†’E,Bโ†’D,Eโ†’A}F = \{A \rightarrow B, A \rightarrow C, CD \rightarrow E, B \rightarrow D, E \rightarrow A\} Step 2: Check extraneous LHS. Check CDโ†’ECD \rightarrow E:
  • Is C extraneous? Can we derive Dโ†’ED \rightarrow E?
    • {D}+\{D\}^+ with F = {D}. Bโ†’DB \rightarrow D doesn't help (B not in result). CD โ†’ E needs C. So {D}+={D}\{D\}^+ = \{D\}. E โˆ‰ {D}+\{D\}^+. C is NOT extraneous.
  • Is D extraneous? Can we derive Cโ†’EC \rightarrow E?
    • {C}+\{C\}^+ = {C}. E โˆ‰ {C}+\{C\}^+. D is NOT extraneous. Step 3: Remove redundant FDs Check Aโ†’BA \rightarrow B in Fโ€ฒ=Fโˆ’{Aโ†’B}={Aโ†’C,CDโ†’E,Bโ†’D,Eโ†’A}F' = F - \{A \rightarrow B\} = \{A \rightarrow C, CD \rightarrow E, B \rightarrow D, E \rightarrow A\}:
  • {A}+\{A\}^+ using F':
    • result = {A}
    • Aโ†’CA \rightarrow C โ†’ {A, C}
    • Eโ†’AE \rightarrow A: E not in result
    • CDโ†’ECD \rightarrow E: C and D? D not in result
    • Bโ†’DB \rightarrow D: B not in result
    • {A}+={A,C}\{A\}^+ = \{A, C\}. B โˆ‰ {A,C}\{A, C\}. So Aโ†’BA \rightarrow B is NOT redundant. Check Aโ†’CA \rightarrow C in Fโ€ฒ={Aโ†’B,CDโ†’E,Bโ†’D,Eโ†’A}F' = \{A \rightarrow B, CD \rightarrow E, B \rightarrow D, E \rightarrow A\}:
  • {A}+\{A\}^+: {A} โ†’ Aโ†’BA \rightarrow B โ†’ {A, B} โ†’ Bโ†’DB \rightarrow D โ†’ {A, B, D}. C not in result.
  • So Aโ†’CA \rightarrow C is NOT redundant. Check CDโ†’ECD \rightarrow E in Fโ€ฒ={Aโ†’B,Aโ†’C,Bโ†’D,Eโ†’A}F' = \{A \rightarrow B, A \rightarrow C, B \rightarrow D, E \rightarrow A\}:
  • {C,D}+\{C, D\}^+: {C, D}. No FD has just {C, D} on LHS except CDโ†’E which we removed.
  • So CDโ†’ECD \rightarrow E is NOT redundant. Check Bโ†’DB \rightarrow D in Fโ€ฒ={Aโ†’B,Aโ†’C,CDโ†’E,Eโ†’A}F' = \{A \rightarrow B, A \rightarrow C, CD \rightarrow E, E \rightarrow A\}:
  • {B}+\{B\}^+: {B}. No FD applies. D not in result.
  • So Bโ†’DB \rightarrow D is NOT redundant. Check Eโ†’AE \rightarrow A in Fโ€ฒ={Aโ†’B,Aโ†’C,CDโ†’E,Bโ†’D}F' = \{A \rightarrow B, A \rightarrow C, CD \rightarrow E, B \rightarrow D\}:
  • {E}+\{E\}^+: {E}. A not in result.
  • So Eโ†’AE \rightarrow A is NOT redundant. Canonical cover: Fc={Aโ†’B,Aโ†’C,CDโ†’E,Bโ†’D,Eโ†’A}F_c = \{A \rightarrow B, A \rightarrow C, CD \rightarrow E, B \rightarrow D, E \rightarrow A\} Hmm, same as the original (after decomposition). No redundancy.

15.5 Superkey Counting

Formula: Given N total attributes and a candidate key CK of size n:
  • Number of superkeys = 2(Nโˆ’n)2^{(N-n)} With two candidate keys CKโ‚ (size nโ‚) and CKโ‚‚ (size nโ‚‚):
  • Number of superkeys = 2(Nโˆ’n1)+2(Nโˆ’n2)โˆ’2(Nโˆ’(n1+n2))2^{(N-nโ‚)} + 2^{(N-nโ‚‚)} - 2^{(N-(nโ‚+nโ‚‚))}
  • (Subtract the overlap counted twice) Example: R(J,K,L,M,N,O)R(J, K, L, M, N, O) with F={Kโ†’JL,Lโ†’K,Jโ†’O,Mโ†’N}F = \{K \rightarrow JL, L \rightarrow K, J \rightarrow O, M \rightarrow N\} Find candidate keys:
  • Attributes not on RHS: M (not on any RHS). So M is mandatory.
  • Compute {M}โบ = {M, N} (using Mโ†’N). Not all attributes.
  • Try {M, K}โบ:
    • result = {M, K}
    • Kโ†’JL โ†’ {J, K, L, M}
    • Jโ†’O โ†’ {J, K, L, M, O}
    • Mโ†’N โ†’ {J, K, L, M, N, O} = R
  • {M, K} is a superkey. Is K alone with M minimal? {M, K} - M = {K}: Kโบ = {J, K, L} โ‰  R. {M, K} - K = {M}: Mโบ = {M, N} โ‰  R. So {M, K} is a candidate key. Similarly, {M, L}โบ:
  • result = {M, L}
  • Lโ†’K โ†’ {K, L, M}
  • Kโ†’JL โ†’ {J, K, L, M}
  • Jโ†’O โ†’ {J, K, L, M, O}
  • Mโ†’N โ†’ {J, K, L, M, N, O} = R
  • {M, L} is another candidate key. Two candidate keys: {M, K} (nโ‚=2) and {M, L} (nโ‚‚=2). They share attribute M. Using formula: 26โˆ’2+26โˆ’2โˆ’26โˆ’(2+2โˆ’1)=24+24โˆ’23=16+16โˆ’8=242^{6-2} + 2^{6-2} - 2^{6-(2+2-1)} = 2^4 + 2^4 - 2^{3} = 16 + 16 - 8 = 24 superkeys.

๐Ÿ“ Key Formulas / Concepts

ConceptDescription
**Canonical cover FcF_c **Minimal equivalent FD set (no redundancies)
Extraneous attribute (LHS)Attribute that can be removed while the FD is still derivable
Extraneous attribute (RHS)Attribute that can be removed from the RHS while preserving Fโบ
Superkey count (1 CK)2Nโˆ’n2^{N-n}
Superkey count (2 CKs)2Nโˆ’n1+2Nโˆ’n2โˆ’2Nโˆ’(n1+n2โˆ’overlap)2^{N-nโ‚} + 2^{N-nโ‚‚} - 2^{N-(nโ‚+nโ‚‚-overlap)}

โš ๏ธ Common Pitfalls

Pitfall 1: Forgetting Step 1 โ€” Decompose RHS First

The Mistake: Skipping the decomposition step and trying to find extraneous attributes on compound RHS. Fix: Always decompose first: Aโ†’BCA \rightarrow BC โ†’ Aโ†’B,Aโ†’CA \rightarrow B, A \rightarrow C.

Pitfall 2: Confusing Extraneous Attribute Check for LHS vs RHS

The Mistake: Using the same procedure for both sides. Fix:
  • LHS extraneous: Remove the candidate attribute from LHS, compute closure. If RHS is in closure โ†’ extraneous.
  • RHS extraneous: Temporarily remove the attribute from F (not just one FD), compute closure of LHS. If the attribute is in closure โ†’ extraneous.

Pitfall 3: Double-Counting Overlapping Superkeys

The Mistake: When two candidate keys share attributes, counting the overlap twice. Fix: Use inclusion-exclusion: โˆฃAโˆชBโˆฃ=โˆฃAโˆฃ+โˆฃBโˆฃโˆ’โˆฃAโˆฉBโˆฃ|A \cup B| = |A| + |B| - |A \cap B|

๐Ÿ“ Practice Questions

Q1. Find the canonical cover of F={Aโ†’B,Bโ†’C,Aโ†’C}F = \{A \rightarrow B, B \rightarrow C, A \rightarrow C\}.

Answer
Step 1: RHS already single attributes. โœ“
Step 2: No compound LHS to check. โœ“
Step 3: Remove redundant FDs.
  • Check Aโ†’CA \rightarrow C: In Fโ€ฒ={Aโ†’B,Bโ†’C}F' = \{A \rightarrow B, B \rightarrow C\}, compute {A}+\{A\}^+ = {A, B, C}. C is in closure โ†’ redundant. Remove.
Canonical cover: Fc={Aโ†’B,Bโ†’C}F_c = \{A \rightarrow B, B \rightarrow C\}
(We can also keep Aโ†’CA \rightarrow C and remove one of the others, but this is the standard minimal form.)

Q2. Find the canonical cover of F={ABโ†’C,Cโ†’A,BCโ†’D}F = \{AB \rightarrow C, C \rightarrow A, BC \rightarrow D\}.

Answer
Step 1: RHS already single attributes. โœ“
Step 2: Check extraneous LHS attributes.
Check ABโ†’CAB \rightarrow C:
  • Is A extraneous? Compute {B}+\{B\}^+: {B}. C โˆ‰ {B}. Not extraneous.
  • Is B extraneous? Compute {A}+\{A\}^+: {A}. C โˆ‰ {A}. Not extraneous.
Check BCโ†’DBC \rightarrow D:
  • Is B extraneous? Compute {C}+\{C\}^+: {C}. Cโ†’AC \rightarrow A โ†’ {A, C}. D โˆ‰ {A, C}. Not extraneous.
  • Is C extraneous? Compute {B}+\{B\}^+: {B}. D โˆ‰ {B}. Not extraneous.
Step 3: Check redundant FDs.
  • ABโ†’CAB \rightarrow C in {Cโ†’A,BCโ†’D}\{C \rightarrow A, BC \rightarrow D\}: {AB}+\{AB\}^+ = {A, B}. C โˆ‰ {A, B}. Not redundant.
  • Cโ†’AC \rightarrow A in {ABโ†’C,BCโ†’D}\{AB \rightarrow C, BC \rightarrow D\}: {C}+\{C\}^+ = {C}. A โˆ‰ {C}. Not redundant.
  • BCโ†’DBC \rightarrow D in {ABโ†’C,Cโ†’A}\{AB \rightarrow C, C \rightarrow A\}: {BC}+\{BC\}^+ = {B, C}. Cโ†’AC \rightarrow A โ†’ {A, B, C}. D โˆ‰ {A, B, C}. Not redundant.
Canonical cover: Fc={ABโ†’C,Cโ†’A,BCโ†’D}F_c = \{AB \rightarrow C, C \rightarrow A, BC \rightarrow D\} (unchanged)

Q3. Given R(A,B,C,D,E)R(A, B, C, D, E) with F={Aโ†’B,Cโ†’D,Bโ†’E}F = \{A \rightarrow B, C \rightarrow D, B \rightarrow E\}, find all candidate keys.

Answer
Step 1: Attributes not on RHS: A, C (both appear only on LHS).
Step 2: Compute {A,C}+\{A, C\}^+:
  • result = {A, C}
  • Aโ†’BA \rightarrow B โ†’ {A, B, C}
  • Bโ†’EB \rightarrow E โ†’ {A, B, C, E}
  • Cโ†’DC \rightarrow D โ†’ {A, B, C, D, E} = R
Step 3: {A,C}+\{A, C\}^+ covers all attributes. Is it minimal?
  • Remove A: {C}+={C,D}โ‰ R\{C\}^+ = \{C, D\} \neq R
  • Remove C: {A}+={A,B,E}โ‰ R\{A\}^+ = \{A, B, E\} \neq R
Candidate key: {A, C}

Q4. How many superkeys does a relation with 7 attributes and one candidate key of size 2 have?

Answer
Using formula: 2(Nโˆ’n)=27โˆ’2=25=322^{(N-n)} = 2^{7-2} = 2^{5} = 32 superkeys.
Any superset of the candidate key is a superkey. With 5 remaining attributes, any subset of them (including empty) can be added to the candidate key, giving 252^5 possibilities.

Q5. What is the purpose of computing a canonical cover?

Answer
A canonical cover is a minimal set of FDs equivalent to the original set. Its purposes:
  1. Efficiency: Fewer FDs to check during updates (reduces overhead)
  2. Clarity: Removes redundancy โ€” each FD is necessary
  3. Normalization: Used in algorithms to decompose relations into 3NF
  4. Equivalence checking: Comparing canonical covers is easier than comparing full FD sets

Q6. Find the canonical cover of F={Aโ†’BC,Bโ†’C,ABโ†’C}F = \{A \rightarrow BC, B \rightarrow C, AB \rightarrow C\}.

Answer
Step 1: Decompose: F={Aโ†’B,Aโ†’C,Bโ†’C,ABโ†’C}F = \{A \rightarrow B, A \rightarrow C, B \rightarrow C, AB \rightarrow C\}
Step 2: Check ABโ†’CAB \rightarrow C:
  • Is A extraneous? {B}+\{B\}^+ = {B}. Bโ†’CB \rightarrow C โ†’ {B, C}. C โˆˆ {B, C}? Yes! A is extraneous.
  • Remove A: Bโ†’CB \rightarrow C (already exists)
  • Is B extraneous? {A}+\{A\}^+ = {A}. Aโ†’BA \rightarrow B โ†’ {A, B}. Aโ†’CA \rightarrow C โ†’ {A, B, C}. C โˆˆ {A, B, C}? Yes! B is extraneous.
  • Both A and B are extraneous โ†’ ABโ†’C is fully redundant. Remove.
Step 3: F={Aโ†’B,Aโ†’C,Bโ†’C}F = \{A \rightarrow B, A \rightarrow C, B \rightarrow C\}
  • Check Aโ†’CA \rightarrow C: In {Aโ†’B,Bโ†’C}\{A \rightarrow B, B \rightarrow C\}, {A}+\{A\}^+ = {A, B, C}. C is in closure โ†’ redundant. Remove.
Canonical cover: Fc={Aโ†’B,Bโ†’C}F_c = \{A \rightarrow B, B \rightarrow C\}

Q7. Given R(A,B,C,D)R(A, B, C, D) with candidate key {A,B}\{A, B\}, how many superkeys exist?

Answer
N=4N = 4 (total attributes), n=2n = 2 (candidate key size).
Number of superkeys = 24โˆ’2=22=42^{4-2} = 2^2 = 4.
The superkeys are: {A,B}, {A,B,C}, {A,B,D}, {A,B,C,D}.

Q8. Check if F1={Aโ†’B,Bโ†’C}F_1 = \{A \rightarrow B, B \rightarrow C\} and F2={Aโ†’BC}F_2 = \{A \rightarrow BC\} are equivalent.

Answer
Check F1F_1 โ†’ F2F_2: Can we derive Aโ†’BCA \rightarrow BC from F1F_1?
  • A+A^+ using F1F_1 = {A, B, C} (via Aโ†’Bโ†’C). BC โŠ† A+A^+. โœ“
Check F2F_2 โ†’ F1F_1: Can we derive Aโ†’BA \rightarrow B and Bโ†’CB \rightarrow C from F2F_2?
  • Aโ†’BA \rightarrow B: A+A^+ = {A, B, C}. B โŠ† A+A^+. โœ“
  • Bโ†’CB \rightarrow C: B+B^+ = {B}. C โˆ‰ {B}. โœ—
F1F_1 and F2F_2 are NOT equivalent because Bโ†’CB \rightarrow C cannot be derived from F2F_2.

๐Ÿ”— Cross-References

Document outline

Keep your place and jump directly to a heading.

Table of contents

No note selected

Open a chapter to see its outline, backlinks, and sources here.

Open libraryCheckpoint desk