Functional Dependency in DBMS - ISRO Previous Year Questions

Jul 7, 2024

Functional Dependency in DBMS - ISRO Previous Year Questions

Introduction

  • This lecture covers ISRO previous year questions focused on the topic of Functional Dependency in DBMS.
  • Understand concepts like super keys, candidate keys, and Armstrong's Axioms.

Key Concepts

Entity-Relationship Model (ER Model)

  • Entity: Corresponds to a row in a table in RDBMS.
  • Entity Set: Corresponds to a table or relation in RDBMS.
  • In ER Model, we use terms like entity and entity set; in relational model, we use tuple, row, relation, and table.

Super Keys and Candidate Keys

  • Super Key: A set of attributes that can uniquely identify a tuple.
  • Candidate Key: A minimal super key, i.e., a super key with no redundant attributes.
  • Example Question: Given super keys EX, YZ, ABC, identify candidate keys.
    • Check minimal super key criteria: Removing any attribute should not make it non-unique.
    • Examples: If XY is a super key, check if X is still a super key; if yes, then XY is not minimal.

Armstrong’s Axioms

  • Reflexivity Rule: If Y ⊆ X, then X → Y.
  • Augmentation Rule: If X → Y, then X,Z → Y,Z.
  • Transitivity Rule: If X → Y and Y → Z, then X → Z.
  • Use these rules to derive other functional dependencies from a given set.
    • Example: Given functional dependencies (FDs), derive possible FDs using these axioms.

Sample ISRO Questions

ISRO 2014 Question

  • Given: Super keys EX, YZ, A, B, C.
  • Task: Identify candidate keys.
    • Check if removal of any attribute from super keys still results in a super key.
    • Results: Candidate keys are X, A, Y, B.

ISRO 2011 Question

  • Asked: Armstrong’s Inference Rules for FDs.
  • Answer: Reflexivity, Augmentation, and Transitivity rules.
  • Another Question: Missing mutual dependency in Armstrong’s rules.
    • Answer: Option D (does not ensure mutual dependency).

ISRO 2018 Question

  • Given: Table and its attributes.
  • Task: Identify possible candidate keys based on provided data.
    • Check non-repeating values in the attributes to determine possible candidate keys.
    • Apply minimal super key criteria.
    • Answer: Room, Shift can be a candidate key.

ISRO 2009 Question

  • Question: Purpose of Foreign Key.
  • Answer: Ensure Referential Integrity.
    • Every foreign key must correspond to a primary key in another table.

ISRO 2015 Question

  • Given: Domains D1, D2, ..., Dn in relational model.
  • Question: A relation is a subset of what?
  • Answer: Cartesian Product of domain sets, D1 x D2 x ... x Dn.

Gate 2024 Questions

Functional Dependency and Candidate Keys

  • Use Armstrong’s axioms to derive new FDs.
  • Use closures to find candidate keys.
    • Example: Given FDs, find X+, the closure of X, to derive Y from X.

Useful Functional Dependencies

  • Definition: X → Y is a useful FD if and only if: X ≠ ∅, Y ≠ ∅, and X ∩ Y = ∅.
  • Example: In a relation with four attributes, calculate the number of possible useful FDs using combinatorics and closure properties.
  • General Formula: For n attributes, useful FDs = 3^n - 2^n + 1.

Strategy and Techniques

  1. Identifying Candidate Keys: Use FD Closures. Determine non-redundant attributes that uniquely identify a row.
  2. Using Armstrong’s Axioms: Derive secondary FDs, check implied dependencies, and simplify relation designs.
  3. Quiz Yourself: Practice identifying candidate keys and using FD rules frequently.

Conclusion

  • Understanding functional dependency is crucial for database normalization and integrity constraints.
  • Practice with previous year questions aids in grasping the application of theoretical concepts.

Thank you for attending the session!