Functional Dependency in DBMS Enggg.pptx

DBMS Presentation

1. Functional Dependency in DBMS Understanding Concepts through Employee and Project Tables

2. Introduction to Functional Dependency A functional dependency (FD) in a database occurs when one attribute uniquely determines. The functional dependency is a relationship that exists between two attributes. It typically exists between the primary key and non-key attribute within a tables another. Importance in DBMS: Essential for normalization Ensures data integrity and minimizes redundancy Example - Emp_ID → Emp_Name (An employee ID determines the employee name)

3. Employee Table CREATE TABLE Employee ( Emp_ID VARCHAR2(10), Emp_Name VARCHAR2(50), Dept_ID VARCHAR2(10), Salary NUMBER ); INSERT INTO Employee VALUES ('E001', 'Alice Smith', 'D01', 70000); INSERT INTO Employee VALUES ('E002', 'Bob Johnson', 'D02', 65000); INSERT INTO Employee VALUES ('E003', 'Charlie Lee', 'D01', 72000);

4. Employee Table

5. Project Table CREATE TABLE Project ( Proj_ID VARCHAR2(10), Proj_Name VARCHAR2(50), Budget NUMBER ); INSERT INTO Project VALUES ('P101', 'Alpha Development', 500000); INSERT INTO Project VALUES ('P102', 'Beta Testing', 300000); INSERT INTO Project VALUES ('P103', 'Gamma Launch', 400000);

6. Project Table

7. Works_On Table CREATE TABLE Works_On ( Emp_ID VARCHAR2(10), Proj_ID VARCHAR2(10), Hours_Worked NUMBER ); INSERT INTO Works_On VALUES ('E001', 'P101', 35); INSERT INTO Works_On VALUES ('E002', 'P102', 40); INSERT INTO Works_On VALUES ('E003', 'P101', 30); INSERT INTO Works_On VALUES ('E001', 'P103', 20);

8. Works_On Table

9. Types of Functional Dependencies Trivial Functional Dependency Non-Trivial Functional Dependency Partial Functional Dependency Full Functional Dependency Transitive Functional Dependency

10. Types of Functional Dependencies 1. Trivial Functional Dependency - A dependency where an attribute is functionally dependent on itself or a subset of attributes. Example:Emp_ID , Proj_ID → Emp_ID Here, Emp_ID is trivially dependent on ( Emp_ID , Proj_ID ). SELECT Emp_ID , Emp_ID FROM Employee GROUP BY Emp_ID ;

11. Non-Trivial Functional Dependency When an attribute is functionally dependent on a combination of other attributes, and it’s not a trivial dependency. Example: Emp_ID → Dept Employee ID determines the department, which is non-trivial because Dept is not part of the primary key. SELECT Emp_ID , Emp_Name FROM Employee GROUP BY Emp_ID , Emp_Name ;

12. Partial Functional Dependency A non-prime attribute is functionally dependent on part of a candidate key. Example:( Emp_ID , Proj_ID ) → Emp_NameIn the Works On table, if Emp_ID is part of the composite key, but Emp_Name depends only on Emp_ID . SELECT Proj_ID , SUM( Hours_Worked ) FROM Works_On GROUP BY Proj_ID ;

13. Full Functional Dependency A non-prime attribute depends on the full composite key and not just a part of it. Example: ( Emp_ID , Proj_ID ) → Hours_Worked Hours worked by an employee depends on both the employee and the project. SELECT Emp_ID , Proj_ID , Hours_Worked FROM Works_On GROUP BY Emp_ID , Proj_ID , Hours_Worked ;

14. Transitive Functional Dependency When one attribute depends on another through a third attribute. Example: Emp_ID → Dept and Dept → Manager Therefore, Emp_ID → Manager (transitive dependency). CREATE TABLE Department ( Dept_ID VARCHAR2(10), Dept_Name VARCHAR2(50), Manager_ID VARCHAR2(10) ); INSERT INTO Department VALUES ('D01', 'Development', 'M001’); INSERT INTO Department VALUES ('D02', 'Testing', 'M002');

15. Transitive Functional Dependency -- Transitive Dependency Query SELECT E.Emp_ID , D.Manager_ID FROM Employee E JOIN Department D ON E.Dept_ID = D.Dept_ID ;

16. Inference Rules (Armstrong’s Axioms) Reflexivity Rule If Y is a subset of X, then X → Y. Example: Emp_ID → Emp_ID SELECT Emp_ID , Emp_Name FROM Employee GROUP BY Emp_ID , Emp_Name ;

17. Inference Rules (Armstrong’s Axioms) Augmentation Rule If X → Y, then XZ → YZ (adding additional attributes does not affect the dependency). Example: Emp_ID → Emp_Name implies ( Emp_ID , Proj_ID ) → ( Emp_Name , Proj_ID ) SELECT Emp_ID , Emp_Name , Proj_ID FROM Employee E JOIN Works_On W ON E.Emp_ID = W.Emp_ID ;

18. Inference Rules Transitivity Rule If X → Y and Y → Z, then X → Z. Example: Emp_ID → Dept and Dept → Manager implies Emp_ID → Manager SELECT E.Emp_ID , D.Manager_ID FROM Employee E JOIN Department D ON E.Dept_ID = D.Dept_ID ;

19. Inference Rules Decomposition Rule If X → YZ, then X → Y and X → Z. Example: ( Emp_ID , Proj_ID ) → ( Emp_Name , Hours_Worked ) implies Emp_ID → Emp_Name and ( Emp_ID , Proj_ID ) → Hours_Worked SELECT Emp_ID , Emp_Name , Salary FROM Employee GROUP BY Emp_ID , Emp_Name , Salary;

20. Inference Rules Union Rule If X → Y and X → Z, then X → YZ. Example: Emp_ID → Emp_Name and Emp_ID → Dept implies Emp_ID → ( Emp_Name , Dept) SELECT Emp_ID , Emp_Name , Salary FROM Employee GROUP BY Emp_ID , Emp_Name , Salary;

21. Inference Rules Pseudo-Transitivity Rule If X → Y and WY → Z, then WX → Z. Example: Emp_ID → Dept and ( Proj_ID , Dept) → Manager implies ( Emp_ID , Proj_ID ) → Manager SELECT W.Emp_ID , W.Proj_ID , P.Budget FROM Works_On W JOIN Project P ON W.Proj_ID = P.Proj_ID ;

22. Case Study - Normalizing Works_On Table Issue: Emp_Name repeats for the same Emp_ID . Solution: First Normal Form (1NF): Already satisfied (atomic values). Second Normal Form (2NF): Remove partial dependencies. Separate Emp_Name into Employee table.

23. THANK YOU