data dependency in relational database management

data dependency in relational database management

• 1.
  RELATIONAL DATABASE INTEGRITY A database is a collection of data.  Relations form the Table/database.  Integrity means to maintain the consistency of data.  Data integrity in the database is the correctness, consistency and completeness of data.
• 3.
  Redundancy And AssociatedProblems Data redundancy means repetition of same data in more than one place in a database. Redundancy means having multiple copies of same data in the database. This problem arises when a database is not normalized.
• 4.
  Associated Problems DueToRedundancy
• 7.
  Basic concepts ofDependencies Functional Dependency in DBMS refers to a relationship that is present between attributes of any table that are dependent on each other. Dependencies in DBMS is a relation between two or more attributes. E. F. Codd introduced it, and it helps in avoiding data redundancy and getting to know more about bad designs. It has the following types in DBMS −  Functional Dependency  Single value Dependency  Multivalued Dependency  Partial Functional Dependency  Transitive Dependency  Join Dependency
• 8.
  Functional Dependency  Ifthe information stored in a table can uniquely determine another information in the same table, then it is called Functional Dependency. Determinant and Dependent The expression X->Y means if the values if X is known, then the value of Y can be determined” Here X is the determinant andY is the dependent attribute. The value X determines the value ofY.The valueY depends on the value of X. Single valued dependencies It is described as “An attribute is functionally dependent if its value is determined by another attribute”. EmpID -> EmpName In the below table, EmpName is functionally dependent on EmpID because EmpName can take only one value for the given value of EmpID: EmpID EmpName Age E01 Amit 28 E02 Rohit 31
• 9.
   Multi-Valued Dependency A table involves multi-valued dependency if it contains multiple values for an entity. X->->Y  X multi-determines Y when for each value of X, we can have more than one value of Y.  Multivalued dependency occurs when two attributes in a table are independent of each other but, both depend on a third attribute.  Example: Suppose there is a bike manufacturer company which produces two colors(white and black) of each model every year.  BIKE_MODEL MANUF_YEAR → →  BIKE_MODEL COLOR → →  This can be read as "BIKE_MODEL multidetermined MANUF_YEAR" and "BIKE_MODEL multidetermined COLOR". BIKE_MODEL MANUF_YEAR COLOR M2011 2008 White M2001 2008 Black M3001 2013 White M3001 2013 Black M4006 2017 White M4006 2017 Black
• 10.
  Partial Dependency Partial Dependencyoccurs when a non-prime attribute is functionally dependent on part of a candidate key. The prime key attributes are StudentID and ProjectNo,  StudentID = Unique ID of the student  StudentName = Name of the student  ProjectNo = Unique ID of the project  ProjectName = Name of the project  The StudentName can be determined by StudentID, which makes the relation Partial Dependent.  The ProjectName can be determined by ProjectNo, which makes the relation Partial Dependent. StudentID ProjectNo StudentName ProjectName S01 199 Katie Geo Location S02 120 Ollie Cluster Exploration
• 11.
  Transitive Dependency Whenever someindirect relationship happens to cause functional dependency (FC), it is known asTransitive Dependency.  A B and →  B C therefore →  A C CalledTransitive Dependency →  Course Student: → Here, the Course attribute determines the Student attribute. If we know the course, we can learn the student name.  Student City: → If we know the student name then we can determine City.  Course City: → If we know the course, we can determine the city via the Student column.  If we take a closer look into the functional dependencies discussed above, they are indeed forming a pattern Similarly,  A Course, →  B Student and →  C City → STUDENT ID STUDENT COURSE CITY 101 NINJA 1 DSA HYD 102 NINJA 2 ML DELHI 103 NINJA 3 JAVA MUMBAI
• 12.
  Transitive Dependency Whenever someindirect relationship happens to cause functional dependency (FC), it is known asTransitive Dependency.  A B and →  B C therefore →  A C CalledTransitive Dependency →  Course Student: → Here, the Course attribute determines the Student attribute. If we know the course, we can learn the student name.  Student City: → If we know the student name then we can determine City.  Course City: → If we know the course, we can determine the city via the Student column.  If we take a closer look into the functional dependencies discussed above, they are indeed forming a pattern Similarly,  A Course, →  B Student and →  C City → STUDENT ID STUDENT COURSE CITY 101 NINJA 1 DSA HYD 102 NINJA 2 ML DELHI 103 NINJA 3 JAVA MUMBAI
• 13.
  JOIN DEPENDENCY A relationR satisfies join dependency if R is equal to the join of R1,R2,.....Rn where Ri is a subset of the set of attributes of R. The relation can be decomposed into following three relations −  R1(dept, subject)  R2(dept, name) and  R3(subject, name) and it can be shown that decomposition is lossless. Relation R Relation R1 Relation R2 Relation R3 Dept Subject Name CSE C arun CSE C rama CSE Java rama IT C Bhanu Dept Subject CSE C CSE Java IT C Dept Name CSE arun CSE rama IT Bhanu Subject Name C arun C rama Java rama