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.
| SNo | Name | Status | City |
| S1 | Ram | 20 | Mumbai |
| S2 | Shyam | 30 | Kolkata |
| S3 | Jodhu | 10 | Delhi |
| S4 | Modhu | 20 | Chennai |
| S5 | Rakhi | 30 | Kolkata |
| PNo | Name | Color | Weight | City |
| P1 | Nut | Red | 12 | Mumbai |
| P2 | Bolt | Green | 15 | Kolkata |
| P3 | Screw | Blue | 14 | Goa |
| P4 | Handle | Red | 20 | Mumbai |
| P5 | Screw | Blue | 15 | Chennai |
| P6 | Wire | Red | 18 | Delhi |
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 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 remove 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.
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
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:
| Course | Teacher | Student | Time | Room |
| C | S. Roy | Ram | Monday | 3 |
| Java | B.Pal | Shyam | Thursday | 1 |
| Python | R.Payne | Jodhu | Wednesday | 4 |
| AI | K.Arora | Modhu | Monday | 1 |
| Android | B.Pal | Rahul | Thursday | 1 |
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.
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.
