- DBMS Tutorial
- What is Database Management System (DBMS)?
- Components of DBMS
- Applications of DBMS
- Three Schema DBMS Architecture
- Difference between DBMS and RDBMS?
- Difference between File Oriented System and DBMS
- Types of Data Models
- DBMS Schema and Instances
- Data Independence and Data Abstraction
- Database Users and Administrator
- DBMS Languages and Interfaces
DBMS ER Model
DBMS Relational Data Model
Functional Dependency (FD)
A functional dependency is a relationship between any two attributes. Assume that given the value of one attribute, we can get the value of another attribute. For example, if we know the value of the customer account number, we can get the value of customer balance. We say that customer balance is functionally dependent on the customer account number. For example, if we have the price of an item, and if we have the number of items purchased, then we can evaluate the total price for that element as follows.
In this case, we would say that Total Price is functionally dependent on Item Price and Number of Items.
Syntax of Functional Dependency (FD)
P → Q
In this syntax, the left side of the symbol is defined as a Determinant, while the right side of the symbol is defined as a Dependent. P would always be the primary key attribute, and Q would be any dependent non-key attribute from a similar table as the primary key. This display P primary key attribute is functionally dependent on the Q non-key attribute. In some other terms, If the column P attribute of the table recognizes the column Q attribute of the similar table, then the functional dependency of the column Q on column P is represented as P→Q .
Consider the relation STUDENT
In the above table Student, if you know the value of SID of any student, which is unique for each student, we can obtain NAME, AGE, and COURSE of that student with the help of that SID.
By this, we can say that the NAME, AGE, and COURSE are functionally dependent on SID, or we can say SID determines NAME, AGE, and COURSE of the Student.
Formal Definition of Functional Dependency
Let R be a relation, and X and Y be arbitrary subsets on the set of attributes of R. Then, Y is functional dependent on X (in token of X→Y) if and only if each X-value in R has linked with it exactly one Y-value in R.
In other words, whenever two tuples of R agree on their X-value, then they also agree on their Y-value. Mathematically for any two tuples t_1 and t_2 in R that have
t_1 [X]=t_2 [X]
We must also have
t_1 [Y]=t_2 [Y]
Consider the relation FIRST (S#, STATUS, CITY, P#, QTY), as shown in the figure.
Here we assume that each supplier has a unique supplier number (S#), precisely one status code and location. A supplier can supply different parts. Further, we assume that STATUS is functionally dependent on CITY. Therefore all Delhi suppliers must have a status of 20.
The following functional dependencies should influence in FIRST Relational.
The figure demonstrates the functional dependency diagram for this relation. The key to the FIRST relation is the combination of (S# , P#).
Fully Functional Dependency
In a relation R, an attribute of Y is said to be fully functionally dependent on attribute X if it is functionally dependent on X and not functionally dependent on any proper subset of X
- In the relation STUDENT, AGE is not fully functionally dependent on concatenated key (SID, NAME, COURSE) because AGE is functionally dependent on the NAME and COURSE.
- In the FIRST relation, QTY is fully functionally dependent on key (S#, P#).
Types of Functional Dependency
Following are the various types of functional dependencies are
Trivial Functional Dependency
An FD is trivial if and only if the right-hand side is a subset of the left-hand side.
P→Q is a trivial functional dependency if Q is a subset of P.
The following dependencies are also trivial: P→P and Q→Q.
Consider the table STUDENT
SID NAME 111 Shubham 112 Ankita 113 Mahesh 114 Ramesh
[SID,NAME]→SID is a trivial functional dependency, as SID is a subset of
[SID,NAME]. That creates sense because if we see the values of SID and NAME, then the value of SID can be simply determined.
- Non-Trivial Functional Dependency
- Transitive Functional Dependency
An FD, which is not trivial FD, is known as Non-Trivial Functional Dependency.
P→Q is said to be a non-trivial FD, if and only if Q is not a subset of P.
Consider the table STUDENT
SID→NAME is a non-trivial functional dependency as Name is not a subset of SID.
The functional dependency is said to be transitive if two functional dependencies indirectly produced it. For example,
P→R is a transitive dependency if the following three functional dependencies contains true:
- P → Q
- Q does not →P
Consider the table STUDENTConsider the table STUDENT
[SID→NAME] [If we know the SID, we know its student name]
[NAME→AGE] [If we know the Name, we know the Age]
Therefore according to the rule of transitive dependency:
[SID→AGE] It should hold, that makes sense because if we know the SID, we can know his age.
Note: We want to understand that transitive dependency can merely appear in a relationship of three or additional attributes.
A→B is a partial dependency if B is dependent on a proper subset of A and B is said to be partially dependent upon A. A→B where A is not the minimal set of attributes that uniquely determines B. Some attributes could be removed from A, and the dependency would still hold. Partial dependencies must be decomposed into separate relations. It is partial if the removal of any attribute from A does not result in the violation of the rule.