502 Types Of Functional Dependencies PDF

502 Types of Functional Dependencies * * In relational databases, different types of functional dependencies help in understanding ** ** *...

502 Types of Functional Dependencies * * In relational databases, different types of functional dependencies help in understanding ** ** * ** ** relationships between attributes. * * These dependencies guide database normalization, ensuring data consistency, reducing ** ** ** ** ~~ redundancy, and maintaining data integrity. ~~ ** ** * Let’s go over four main types of functional dependencies using examples based on a * ** *** ** ` Student and Course table. ` ** ** ` ` ** 1. Trivial Functional Dependency *~ ~* A trivial functional dependency occurs when an attribute (or set of attributes) in the ** ** ==  ℹ ** dependent set is a subset of the determinant set. ** ***~ ~*** ** ** == In simple terms, if an attribute on the right side of a dependency is part of the left side, it is considered trivial. ** Notation: X → Y is trivial if Y is a subset of X. ** ` ` * ** ** * ` ` * * ` ` ** Example: Consider the Student table with attributes {Student_ID, Course_ID, ** ** ` ` ** ` Name}. ` ` Student_ID ` ` Course_ID ` ` Name ` 101 C101 Alice Smith 102 C102 Bob Johnson 103 C101 Carol White 104 C103 David Brown ** Here: ** ` {Student_ID, Course_ID} → {Course_ID} ` * is a trivial functional dependency * *** * ** * because Course_ID is a subset of {Student_ID, Course_ID}. * ** ` ` ** * * ` ` ` {Student_ID} → {Student_ID} ` * is also a trivial functional dependency * *** * ** * since Student_ID is dependent on itself. * ==** ` ` ** * *== * == In other words, if you know Student_ID and Course_ID , then you already know ` ` ` ` ** ** ` Course_ID , which is trivial. ` ** ** == * ** Why Does This Matter? ** * Trivial dependencies might seem obvious, but they are part of the natural structure of ** the data. ** * * While they don’t introduce redundancy, recognizing them helps focus on non-trivial ** dependencies that may cause data duplication or anomalies. ** * 2. Non-Trivial Functional Dependency *~ ~* A non-trivial functional dependency occurs when the dependent attribute is not a subset ** ** * ** ** ** ** ~~ ~~ of the determinant attribute. ** ** * This type of dependency provides meaningful information about the relationships between attributes in a table. ** Notation: ** ` X → Y is non-trivial ` * * ** ** *** if Y is not a subset of X. *** ` ` * ** ** ~~ ~~ * ` ` ** Example: In the Student table above: ** ** ` ` ** ` {Student_ID} → {Name} ` is a non-trivial functional dependency ** ** because Name is not a subset of Student_ID. ** ` ` ** *** *** ~~ ~~ ** ` ` ** * This implies that each Student_ID uniquely determines the Name of the student, ** ` ` ** == == ** ` ` ** ensuring that Student_ID is unique to each name. ** ` ` ** * 3. Multivalued Functional Dependency *~ ~* A multivalued functional dependency exists when one attribute determines a set of values *** * ** * == for another attribute independently of other attributes. ** **== *  ℹ In this case, the dependent attributes are not dependent on each other. *** *** ~~ ~~ ** Notation: ** ` X →→ {Y, Z} ` * implies a multivalued dependency if Y and Z are independent. ** ** ** ** ` ` ` ` ** ** * ** Example: Consider a modified Student_Course table ** ** ` ` ** * where each student can have multiple courses and multiple contact numbers. ` ` ` ` ` ` * ** Student_ID ** ** Course_ID ** ** Contact_Number ** 101 C101 123-456-7890 101 C102 123-456-7890 101 C101 098-765-4321 102 C103 234-567-8901 ** Here: ** ` Student_ID →→ {Course_ID, Contact_Number} ` * is a multivalued functional dependency ** *** * because Course_ID and Contact_Number are independently associated with ** ` ` ** ** ` ` ** ** ` Student_ID. ` ** * * The contact number does not depend on the course , and vice versa. ` ` ** ** ~~ ~~ ` ` * Appendix * In other words, knowing the Student_ID lets you find all associated courses and ` ` ** ** ` ` ` contact numbers , but the courses and contact numbers aren’t tied to each other – they ` ` ` ` ` ~~ ~~ are both tied only to the Student_ID. ** ** ` ` *  ➡ # ❗ 4. Transitive Functional Dependency *~ ~* A transitive functional dependency occurs when there is an indirect relationship between ** ** ==* ** attributes. ** *== If we have two dependencies, ` X → Y and ` ` Y → Z, ` == then by transitivity, X → Z also holds. ` ` == == * In other words, *== == a transitive dependency occurs when ** ** * a non-prime attribute* ** **== ==* depends on another non-prime attribute * *** * **== ==* This type of dependency should be eliminated in normalization to achieve the third normal form (3NF). ** ** *== ** Notation: ** * If X → Y and Y → Z , ` ` ` ` then X → Z is a transitive dependency. ` ` ** ** * ** Example: Consider the following Student_Department table. ** ** ` ` ** ** Student_ID ** ** Department_ID ** ** Department_Name ** 101 D01 Science 102 D02 Arts 103 D01 Science 104 D03 Commerce * In this case: * ` Student_ID → Department_ID and Department_ID → Department_Name. ` * * ` ` * By transitivity, Student_ID → Department_Name also holds, which is a transitive ` ` dependency. * This dependency implies that Department_Name can be derived from Student_ID ` ` ` ` through Department_ID , resulting in ` ` * redundant information. * * ==** Transitive dependencies are often eliminated during normalization. ** == * Summary Table of Functional Dependencies ** Dependency ** Definition ** ** Example ** What to do Type ** *** Trivial * Dependent ` {Student_ID, Trivial Dependency ** attribute is a Course_ID} → dependencies *** subset of the *** Course_ID ` don’t require determinant action during attribute normalization. They are ignored or disregarded because they don’t affect the database design negatively. *** Non-Trivial * Dependent ` Student_ID → *** Keep non-trivial *** Dependency ** attribute is not a *** *** Name ` dependencies. ~~ subset of the ~~ They are determinant important for attribute defining the relationships between attributes in your table.  *** Multivalued * One attribute ` Student_ID →→ *** Retain *** Dependency ** determines {Course_ID, multivalued *** multiple * Contact_Number} ` dependencies – independent they highlight values for another ** cases where attributes should be split into *** separate tables *** to avoid redundancy. *** Transitive * *** Indirect *** ` Student_ID → *** Eliminate *** Dependency ** dependency Department_ID , ` transitive where one ` Department_ID dependencies by attribute depends → restructuring on another Department_Name ` tables, use a join *** *** through a third as needed. attribute Understanding these dependencies is essential for effective database normalization and ** ** ensures that data remains consistent, non-redundant, and easy to maintain.