Types of Dependency in DBMS

Dependency in DBMS

Dependency is a constraint that defines the relationship between attributes. It also describes as a relationship where knowing the value of one attribute is enough to tell you the value of another attribute in the same table.

Types of Dependency:

There are mainly Two Types of Dependency in DBMS:
1. Functional Dependency
2. Full Functional Dependency

Functional Dependency:

Functional Dependency is the consequence of the inter-relationships among attributes of an entity represents by relation, it may be due to the relationship between entities.

A functional dependency denoted by X->Y, between two sets of attributes X and Y.
To understand functional dependencies in a better way, let us see the database containing information concerning the supplier’s table and product table. The supplier number (SNo) and product number (PNo) identify the suppliers and products.

SNoNameStatusCity
S1Ram20Mumbai
S2Shyam30Kolkata
S3Jodhu10Delhi
S4Modhu20Chennai
S5Rakhi30Kolkata

PNoNameColorWeightCity
P1NutRed12Mumbai
P2BoltGreen15Kolkata
P3ScrewBlue14Goa
P4HandleRed20Mumbai
P5ScrewBlue15Chennai
P6WireRed18Delhi

In the Supplier and Product databases attributes Name, Status, and City of relation S are each functionally dependent on attribute SNo, because given a particular value for SNo, there exists precisely one corresponding value for each of Name, Status and City. Symbolically We can write:
S.SNo->S.Name
S.SNo->S.Status
S.SNo->S.City

The statement S.SNo->S.City is read as attribute S.City is functionally dependent on attribute S.SNo.
The statement S.SNo->S. (Name, Status, City) can be similarly interpreted if we consider the combination (Name, Status, City) as a composite key of relations. Functional Dependency can be shown diagrammatically below figure:

Types of Dependency

Full Functional Dependency:

All non-key attributes depend on the key attribute, it says Full Functional Dependency.
X->Y is a full-functional dependency because of removal of any attribute a form X would result in the cancellation of dependency.

Types of Dependency

In figure, non-key attributes (Name, Address, Age, and Course) are dependent attribute RNo. So, here RNo are Full Functional Dependency.

Types of Functional Dependency:

There are four types of Functional Dependency in DBMS:

Partial Dependency:

A functional dependency X->Y is a Partial Dependency if some attributes AєX can removes from X and the dependency still holds.

Partial dependency in a record type occurs when some non-key attributes depend on the key attributes. The remaining non-key attributes depend on key attributes and one or more non-key attributes. In other words, all the non-key attributes are not dependent on the key attribute. There is a partial dependency of non-key attributes either on the key attribute or on the non-key attribute.

Types of Dependency

Transitive Dependency:

A functional dependency X->Y about scheme R is a Transitive Dependency if there is a set of attributes Z that is neither a candidate key nor a subset key of R and both X->Z and Z->Y hold.

Transitive Dependency occurs because one non-key attribute is dependent on other non-key attributes. A general case of transitive dependencies is given as follows:
A, B, C are three columns in a table.
If C is related to B
If B is related to A
Then C is indirectly related to A

This is the case that exists in a table, We can remove this dependency by splitting each relation into two separate tables, which are to be linked using a foreign key. When one non-key attribute depends on other non-key attributes, it says Transitive Dependency.

Types of Dependency

In figure, to calculate Distance which is a non-key attribute, we must know Origin and Destination which are also non-key attributes. One non-key attribute is dependent on one or more than one non-key attribute. Here, Destination has the transitive dependency.

Multivalued Dependency:

Multivalued Dependencies are a consequence of the first normal form. The first normal form doesn’t allow an attribute in the tuple to have more than one value. If we have two or more multivalued independent attributes in the same relation schema. We would get into the problem of repeating every value of one of the attributes with every value of the other attribute to keep the relation instances consistent. This constraint is specified by a multivalued dependency.

Consider the Course database (Course, Teacher, Student, Time, Room) relation as given below:

CourseTeacherStudentTimeRoom
CS. RoyRamMonday3
JavaB.PalShyamThursday1
PythonR.PayneJodhuWednesday4
AIK.AroraModhuMonday1
AndroidB.PalRahulThursday1

m1: Course->-> Time, Room
m2: Course->-> Student

The meaning of these two multivalued dependencies is that the same teacher is teaching a particular course irrespective of the time or room in which the course is held. The students registered for a course aren’t determined by the time or room where the course is held.

Join Dependency:

Join Dependency is a constraint, it is just like a functional dependency or multivalued dependency. It is satisfied if and only if the relation concerned is the joining of a certain number of projections. This type of constraint is called a join dependency.

We now consider a special class join dependencies which help to capture data dependencies present in a Hierarchical Data Structure.

Types of Dependency

In the figure, a Nurs_home database shown, it has an inherent hierarchical organization. It implies that information regarding wards and patients currently admitted to a ward depends only on the Nurs_home. But the facilities are present in that hospital (Vice-Versa).

A Nurs_home can have multiple wards, functional dependencies aren’t adequate to describe the data dependency among NURS_HOME and WARDS or FACILITIES. In this case, Multivalued dependencies are NURS_HOME->->WARD OR NURS_HOME->-> FACILITIES hold When docomposed, it has the following representations:

Decomposition

Thus we store the NURS_HOME database as the lossless join of :
NURS_FACILITY(NURS_HOME, FACILITY)
NURS_WARD(NURS_HOME, WARD, PATIENT, COMPLAINTS, TREATMENT, DOCTOR)
relations.

Advantages of Dependency:

1. Dependency avoids data redundancy.
2. It helps you to maintain the quality of data in the database.
3. Dependency helps you to define the meanings and constraints of databases.
4. It helps you to identify the bad designs of the database.
5. It provides the basic building blocks used in database normalization.