Functional Dependencies and Table Decomposition

1. What is Functional Dependency (FD)?

  • A rule that shows how one column can predict another.
    • Example: If you know Roll_No, you can find Name.
    • Written as: Roll_No → Name.

2. Key Terms

  • Determinant: The “predictor” (e.g., Roll_No in Roll_No → Name).
  • Dependent: The “predicted” (e.g., Name in Roll_No → Name).

3. Why Use FDs?

  • To keep data clean and avoid mistakes (no repeated or wrong data).

4. Types of FDs

  1. Trivial: When the dependent is already part of the determinant.
    Example: {A, B} → A.
  2. Non-Trivial: When the dependent is not part of the determinant.
    Example: A → B.

5. Keys and Closure

  • Candidate Key: A column (or set of columns) that can uniquely identify a row.
    Example: Roll_No in a student table.
  • Closure: All the columns you can predict using a key.
    Example: For Roll_No → {Name, Class}, closure of Roll_No is {Roll_No, Name, Class}.

6. Why Break Tables (Decomposition)?

  • To fix bad tables with problems like:
    1. Redundancy: Repeated data wastes space.
    2. Update Problems: Changing one row means updating many rows.
    3. Insert/Delete Problems: Missing or extra data.

7. How to Break Tables?

  1. Lossless Decomposition: When splitting tables, no data should disappear.
    Rule: Keep shared data between tables.
  2. Dependency Preservation: FDs should still work after splitting.

8. Normal Forms (NF) – Clean Your Tables!

  1. 1NF (1st Normal Form): No repeating values or groups.
    Example: Change “Subjects: Math, English” → “Subjects: Math” and “Subjects: English”.
  2. 2NF (2nd Normal Form): Remove partial dependencies (part of the key predicts other columns).
    Example: Split tables if {StudentID, CourseID} → CourseName.
  3. 3NF (3rd Normal Form): Remove transitive dependencies (A → B → C).
    Example: If StudentID → Class → Teacher, remove Teacher from the main table.
  4. BCNF: Make sure every determinant is a key.

9. Simple Steps to Solve Questions

  1. Find Keys: Look for unique columns (or sets of columns).
  2. Check Normal Forms: Start with 1NF → 2NF → 3NF → BCNF.
  3. Break Tables: Split tables if needed (follow lossless and dependency rules).

10. Easy Way to Remember

  1. FDs are rules: “If I know this, I can find that.”
    Example: Roll_No → Name.
  2. Keys unlock tables: Find the smallest set that uniquely identifies rows.
  3. Decompose for Clean Tables:
    • Remove repeating data (1NF).
    • Remove partial key problems (2NF).
    • Remove indirect relationships (3NF).