Functional Dependency.pptx

Fictional character drawing

1. Functional Dependency Anupama Sahu Asst. Prof. of CSE&A IGIT,Sarang

2. Basic concept of Functional Dependency The functional dependency is a relationship that exists between two attributes. It typically exists between the primary key and non-key attribute within a table. Ex. X   →   Y   The left side of FD is known as a determinant, the right side of the production is known as a dependent.

3. For example: Assume we have an employee table with attributes: Emp_Id , Emp_Name , Emp_Address . Here Emp_Id attribute can uniquely identify the Emp_Name attribute of employee table because if we know the Emp_Id , we can tell that employee name associated with it. Functional dependency can be written as: Emp_Id  →  Emp_Name      We can say that Emp_Name is functionally dependent on Emp_Id .

4. Employee number Employee Name Salary City 1 Dana 50000 San Francisco 2 Francis 38000 London 3 Andrew 25000 Tokyo Example: In this example, if we know the value of Employee number, we can obtain Employee Name, city, salary, etc. By this, we can say that the city, Employee Name, and salary are functionally depended on Employee number.

5. Rules of Functional Dependencies The above mentioned types of functional dependencies are used in database design and normalization to ensure data integrity and consistency, and to minimize data redundancy.

6. Types of Functional Dependency Trivial functional dependency Non-trivial functional dependency Full functional dependency Partial functional dependency Multi-valued dependency Transitive dependency The above mentioned types of functional dependencies are used in database design and normalization to ensure data integrity and consistency, and to minimize data redundancy.

7. 1.Trivial functional dependency A functional dependency is trivial if the dependent attribute is a subset of the determinant attribute. For example, in a table “orders” the functional dependency (Order ID, Customer ID) -> (Customer ID) is trivial.

8. 2. Non-trivial functional dependency A functional dependency is non-trivial if the dependent attribute is not a subset of the determinant attribute. For example, in a table “orders” the functional dependency (Order ID) -> (Customer ID) is non-trivial.

9. 3. Full functional dependency A functional dependency is full if a determinant attribute functionally determines all other attributes in a table. For example, in a table “orders” (Order ID) -> (Order ID, Customer ID, Product ID, Quantity) is a full functional dependency.

10. 4. Partial functional dependency A functional dependency is partial if a determinant attribute functionally determines only some of the other attributes in a table. For example, in a table “orders” (Order ID, Customer ID) -> (Product ID, Quantity) is a partial functional dependency.

11. 5. Multi-valued dependency A multi-valued dependency exists when an attribute functionally determines more than one non-key attribute. For example, in a table “orders” (Order ID) -> (Customer ID, Product ID) is a multi-valued dependency.

12. 6. Transitive dependency A transitive dependency occurs when a non-key attribute functionally determines another non-key attribute. For example, in a table “orders” (Product ID) -> (Price) and (Price) -> (Discount) is a transitive dependency.

13. Inference Rules of Functional Dependency: The Armstrong's axioms are the basic inference rule. Armstrong's axioms are used to conclude functional dependencies on a relational database. Inference Rules can apply to a set of FD(functional dependency) to derive other FD. Using the inference rule, we can derive additional functional dependency from the initial set.

14. Types of Inference Rules: 1. Reflexive Rule (IR 1 ) In the reflexive rule, if Y is a subset of X, then X determines Y. If X ⊇ Y then X  →    Y   Example: X = {a, b, c, d, e}   Y = {a, b, c}  

15. 2. Augmentation Rule (IR 2 ) The augmentation is also called as a partial dependency. In augmentation, if X determines Y, then XZ determines YZ for any Z. If X    →  Y then XZ   →   YZ    Example: For R(ABCD),   if  A   →   B then AC  →   BC   

16. 3. Transitive Rule (IR 3 ) In the transitive rule, if X determines Y and Y determine Z, then X must also determine Z. If X   →   Y and Y  →  Z then X  →   Z    

17. 4. Union Rule (IR 4 ) Union rule says, if X determines Y and X determines Z, then X must also determine Y and Z. If X    →  Y and X   →  Z then X  →    YZ      

18. 5. Decomposition Rule (IR 5 ) Decomposition rule is also known as project rule. It is the reverse of union rule. This Rule says, if X determines Y and Z, then X determines Y and X determines Z separately. If X   →   YZ then X   →   Y and X  →    Z   

19. 5. Decomposition Rule (IR 5 ) Decomposition rule is also known as project rule. It is the reverse of union rule. This Rule says, if X determines Y and Z, then X determines Y and X determines Z separately. If X   →   YZ then X   →   Y and X  →    Z   

20. 6. Pseudo transitive Rule (IR 6 ) In Pseudo transitive Rule, if X determines Y and YZ determines W, then XZ determines W. If X   →   Y and YZ   →   W then XZ   →   W