---

Decompositions that preserve Functional Dependencies

---

• Motivation...

  • The decomposition technique that we have learned guarantees that:

    Resulting relations are lossless The decomposition does not contain bogus tuples (information)

    ---

    ---

  • There is another important property embedded inside a relation:

    Functional dependencies !!!

    Effect of functional dependencies:

    When there is a functional dependency A → B , then: Whenever we see the same attribute value for A , we must see the same attribute value for B Graphically: A +------+-----+----------+-----+-----+ | .... | a1 | ...... | b1 | ... | +------+-----+----------+-----+-----+ | .... | a1 | ...... | b1 | ... | +------+-----+----------+-----+-----+ | .... | a2 | ...... | b2 | ... | +------+-----+----------+-----+-----+ | .... | a2 | ...... | b2 | ... | +------+-----+----------+-----+-----+

  ---

  ---

  ---

  ---

  ---

• Data integrity and functional dependencies
  • Fact:

    A function dependency specifies a constraint on the data that must be satisfied !!! Functional dependencies are basis of data correctness !!!

    Example:

    Employee2(SSN, FName, LName, DNumber, DName, MgrSSN) Functional dependency: DNumber → DName SSN FName LName DNumber DName MgrSSN +-------------+------+-------+--------+----------+-------------+ | 111-11-1111 | John | Smith | 5 | Research | 222-22-2222 | +-------------+------+-------+--------+----------+-------------+ | 111-11-1111 | John | Smith | 5 | Payroll | 222-22-2222 | +-------------+------+-------+--------+----------+-------------+ ^ ^ | | Data corruption !!!

    ---

    ---

  • Conclussion:

    A violation of a functional dependency means that the integrity of the database has been compromised. !!!

  ---

  ---

  ---

  ---

• Preserving functional dependencies
  • Suppose a functional dependency X → A holds in relation R

  • A decomposition of R into R₁ and R₂ preserves the functional dependency X → A if:

    {X,A} ⊆ R1    (or {X,A} ⊆ R2) OR:   ∃   B ⊆ R:   X → B   and   B → A,     and:   {X,B} ⊆ R1 and   {B, A} ⊆ R2

  ---

  ---

  ---

  ---

• Example of a dependency preserving decomposition
  • Consider the following relation:

    Employee2(SSN, FName, LName, DNumber, DName, MgrSSN) Functional dependencies: SSN → FName, LName, DNumber, DName, MgrSSN DNumber → DName, MgrSSN

    The complete list of functional dependencies is actually as follows:

    SSN → FName SSN → LName SSN → DNumber SSN → DName SSN → MgerSSN DNumber → DName DNumber → MgrSSN

    ---

    ---

  • Consider the following decomposition of the relation Employee2:

    R1(SSN, FName, LName, DNumber) R2(DNumber, DName, MgrSSN)

    Now consider whether each of the (original) functional dependencies was preserved:

    SSN → FName preserved in R1 SSN → LName preserved in R1 SSN → DNumber preserved in R1 SSN → DName preserved in R1 SSN → MgerSSN preserved in R1 DNumber → DName preserved in R2 DNumber → MgrSSN preserved in R2

    ---

    ---

  • Conclusion:

    The decomposition was dependency-preserving !!!

  ---

  ---

  ---

  ---

• Example of a non-dependency preserving decomposition
  • Consider the following relation:

    TJS(Teacher, Subject, Student) Functional dependencies: Student, Subject → Teacher Teacher → Subject

    The complete list of functional dependencies is as follows:

    Student, Subject → Teacher Teacher → Subject

    ---

    ---

  • Consider the following decomposition of the relation TJS:

    R1(Student, Teacher) R2(Teacher, Subject)

    Now consider whether each of the (original) functional dependencies was preserved:

    Student, Subject → Teacher lost !!! (Student, Subject are not found in same relation together !) Teacher → Subject preserved in R2

    ---

    ---

  • Conclusion:

    The decomposition was not dependency-preserving !!!

  ---

  ---

  ---

  ---

• The importance of preserving functional dependencies in a decomposition
  • Fact:

    A decomposition that preserves all functional dependencies allows us to: Easily check whether some functional dependency has been compromised !!! (Checking for data integrity can be done without using a join operation !!!)

  • Conversely:

    Check for dependency violations in a decomposition that do not preserve functional dependencies requires the use of join operations (The join operation is required to reconstruct the original content)

    ---

    ---

  • Example: checking for data consistency in a dependency-preserving decomposition

    The original relation: Employee2(SSN, FName, LName, DNumber, DName, MgrSSN) Functional dependencies: SSN → FName, LName, DNumber, DName, MgrSSN DNumber → DName, MgrSSN The complete list of functional dependencies is actually as follows: SSN → FName SSN → LName SSN → DNumber SSN → DName SSN → MgerSSN DNumber → DName DNumber → MgrSSN The decomposed relations: R1(SSN, FName, LName, DNumber) R2(DNumber, DName, MgrSSN) SSN FName LName DNumber DNumber DName MgrSSN +---+-------+-------+--------+ +--------+------+-------+ | A | John | Smith | 5 | | 5 | Res | B | +---+-------+-------+--------+ +--------+------+-------+ | B | Peter | Pan | 5 | | 5 | Res | B | +---+-------+-------+--------+ +--------+------+-------+ How to check for violations of these functional dependency: SSN → FName use R1 How: When you insert a new tuple in R1 you can check if (SSN, FName) is consistent SSN → LName use R1 SSN → DNumber use R1 SSN → DName use R1 SSN → MgerSSN use R1 DNumber → DName use R2 DNumber → MgrSSN use R2

    ---

    ---

    ---

    ---

  • Example: checking for data consistency in a non dependency-preserving decomposition

    The original relation: TJS(Teacher, Subject, Student) Functional dependencies: Student, Subject → Teacher Teacher → Subject Sample content: Teacher Subject Student +--------+----------+---------+ | cheung | cs170 | John | +--------+----------+---------+ | cheung | cs170 | Pete | +--------+----------+---------+ | summet | cs170 | Jack | +--------+----------+---------+ | summet | cs170 | Kate | +--------+----------+---------+ How to check for functional dependency violation: Insert: (summet, cs170,John) Violation detected by the existence of the tuple (cheung, cs170,John) Insert: (cheung,cs255 ,John) Violation detected by the existence of the tuple (cheung,cs170 ,John) Hence: Functional dependency violations can be easily detected with the original relation The decomposed relations: R1(Teacher, Subject) R2(Teacher, Student) Teacher Subject Teacher Student +--------+----------+ +--------+--------+ | cheung | cs170 | | cheung | John | +--------+----------+ +--------+--------+ | summet | cs170 | | cheung | Pete | +--------+----------+ +--------+--------+ | summet | Jack | +--------+--------+ | summet | Kate | +--------+--------+ How to check for violations of these functional dependency: Teacher → Subject use R1 How: When you insert a new tuple in R1 you can check if (Teacher,Subject) is consistent Student,Subject → Teacher ?????? --- need join !!

  • Disadvantage of non-dependency preserving decomposition:

    To check for violations of a lost functional dependency, we must use a join operation to re-construct the original relation !!!

  ---

  ---

  ---

  ---

• Dependency preserving decomposition: summary of the technique

  • The technique of dependency preserving decomposition is based on looking at all the functional dependencies at once

  • It is based on the notion of

    equivalency of sets of functional dependencies...