Monday 7 December 2020

Dependencies in DBMS

 

Dependencies in DBMS:

A dependency in DBMS is a relation between two or more attributes. It has the following types in DBMS:

  1. Functional Dependency
  2. Fully-Functional Dependency
  3. Transitive Dependency
  4. Multivalued Dependency
  5. Partial Dependency

 

1.   Functional Dependency:

If the information stored in a table can uniquely determine another information in the same table, then it is called Functional Dependency. Consider it as an association between two attributes of the same relation.

If P functionally determines Q, then P -> Q.

Ex:

EmpID

EmpName

EmpAge

E101

Ajit

30

E102

Rohit

32

 

In the above table, as you can see that, EmpName is functionally dependent on EmpID because EmpName can take only one value for the given value of EmpID. So,

EmpID ->EmpName

Thus, Employee Name (EmpName) is functionally dependent on Employee ID (EmpID).

 

2.   Fully-functionally Dependency:

An attribute is fully functional dependent on another attribute, if it is Functionally Dependent on that attribute and not on any of its proper subset.

For ex:, an attribute Q is fully functional dependent on another attribute P, if it is Functionally Dependent on P and not on any of the proper subset of P.

Ex: Consider the following ProjectCost and EmployeeProject relations.

ProjectID

ProjectCost

001

1000

002

5000

 

 

EmpID

ProjectID

Days (spent on the project)

E101

P01

220

E102

P02

150


The above relations states:

EmpID, ProjectID, ProjectCost -> Days

However, it is not fully functional dependent.

Whereas the subset {EmpID, ProjectID} can easily determine the {Days} spent on the project by the employee.

This summarizes and gives our fully functional dependency −

{EmpID, ProjectID}  -> (Days)



3.   Transitive Dependency

When an indirect relationship causes functional dependency it is called Transitive Dependency.

If P -> Q and Q -> R is true, then P-> R is a transitive dependency.

 

4.   Multivalued Dependency

When existence of one or more rows in a table implies one or more other rows in the same table, then the Multi-valued dependencies occur.

If a table has attributes P, Q and R, then Q and R are multi-valued facts of P.

It is represented by double arrow (->->).


For our example:

P->->Q
Q->->R

In the above case, Multivalued Dependency exists only if Q and R are independent attributes.

 

5.   Partial Dependency

Partial Dependency occurs when a nonprime attribute is functionally dependent on part of a candidate key.

The 2nd Normal Form (2NF) eliminates the Partial Dependency. For ex. consider the below StudentProject relation:

 

StudentID

ProjectNo

StudentName

ProjectName

S01

199

Katie

Geo Location

S02

120

Ollie

Cluster Exploration

In the above table, we have partial dependency; let us see how −

·         The prime key attributes are StudentID and ProjectNo.

·         As stated earlier, the non-prime attributes i.e. StudentName and ProjectName should be functionally dependent on part of a candidate key, to be Partial Dependent.

·         The StudentName can be determined by StudentID that makes the relation Partial Dependent.

·         The ProjectName can be determined by ProjectID, which that the relation Partial Dependent.

No comments:

Post a Comment