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!