Types of Functional Dependencies in DBMS

1. Trivial functional dependency

2. Non-Trivial functional dependency
3. Multivalued functional dependency
4. Transitive functional dependency
1. Trivial Functional Dependency
In Trivial Functional Dependency, a dependent is always a subset of the determinant. i.e.
If X → Y and Y is the subset of X, then it is called trivial functional dependency
Example:
roll_no name age

42 abc 17

43 pqr 18

44 xyz 18
Here, {roll_no, name} → name is a trivial functional dependency, since the
dependent name is a subset of determinant set {roll_no, name}. Similarly, roll_no →
roll_no is also an example of trivial functional dependency.
2. Non-trivial Functional Dependency
In Non-trivial functional dependency, the dependent is strictly not a subset of the
determinant. i.e. If X → Y and Y is not a subset of X, then it is called Non-trivial
functional dependency.
Example:
roll_no name age

42 abc 17

43 pqr 18

44 xyz 18
Here, roll_no → name is a non-trivial functional dependency, since the
dependent name is not a subset of determinant roll_no. Similarly, {roll_no, name} →
age is also a non-trivial functional dependency, since age is not a subset of {roll_no,
name}
3. Multivalued Functional Dependency
In Multivalued functional dependency, entities of the dependent set are not
dependent on each other. i.e. If a → {b, c} and there exists no functional
dependency between b and c, then it is called a multivalued functional dependency.
For example,
roll_no name age

42 abc 17

43 pqr 18

44 xyz 18

45 abc 19
Here, roll_no → {name, age} is a multivalued functional dependency, since the
dependents name & age are not dependent on each other(i.e. name → age or age →
name doesn’t exist !)
4. Transitive Functional Dependency
In transitive functional dependency, dependent is indirectly dependent on determinant. i.e.
If a → b & b → c, then according to axiom of transitivity, a → c. This is a transitive
functional dependency.
For example,
nam
enrol_no e dept building_no

42 abc CO 4

43 pqr EC 2

44 xyz IT 1

45 abc EC 2
Here, enrol_no → dept and dept → building_no. Hence, according to the axiom of
transitivity, enrol_no → building_no is a valid functional dependency. This is an indirect
functional dependency, hence called Transitive functional dependency.
5. Fully Functional Dependency
In full functional dependency an attribute or a set of attributes uniquely determines another
attribute or set of attributes. If a relation R has attributes X, Y, Z with the dependencies X-
>Y and X->Z which states that those dependencies are fully functional.
6. Partial Functional Dependency
In partial functional dependency a non key attribute depends on a part of the composite
key, rather than the whole key. If a relation R has attributes X, Y, Z where X and Y are the
composite key and Z is non key attribute. Then X->Z is a partial functional dependency in
RBDMS.
Advantages of Functional Dependencies
Functional dependencies having numerous applications in the field of database
management system. Here are some applications listed below:
1. Data Normalization
Data normalization is the process of organizing data in a database in order to minimize
redundancy and increase data integrity. Functional dependencies play an important part in
data normalization. With the help of functional dependencies we are able to identify the
primary key, candidate key in a table which in turns helps in normalization.
2. Query Optimization
With the help of functional dependencies we are able to decide the connectivity between
the tables and the necessary attributes need to be projected to retrieve the required data
from the tables. This helps in query optimization and improves performance.
3. Consistency of Data
Functional dependencies ensures the consistency of the data by removing any
redundancies or inconsistencies that may exist in the data. Functional dependency ensures
that the changes made in one attribute does not affect inconsistency in another set of
attributes thus it maintains the consistency of the data in database.
4. Data Quality Improvement
Functional dependencies ensure that the data in the database to be accurate, complete and
updated. This helps to improve the overall quality of the data, as well as it eliminates
errors and inaccuracies that might occur during data analysis and decision making, thus
functional dependency helps in improving the quality of data in database.

Normalization of DBMS
In database management systems (DBMS), normal forms are a series of guidelines that
help to ensure that the design of a database is efficient, organized, and free from data
anomalies. There are several levels of normalization, each with its own set of guidelines,
known as normal forms.
Normal forms help to reduce data redundancy, increase data consistency, and improve
database performance. However, higher levels of normalization can lead to more complex
database designs and queries. It is important to strike a balance between normalization and
practicality when designing a database.
Advantages of Normal Form
 Reduced data redundancy: Normalization helps to eliminate duplicate data in
tables, reducing the amount of storage space needed and improving database
efficiency.
 Improved data consistency: Normalization ensures that data is stored in a
consistent and organized manner, reducing the risk of data inconsistencies and errors.
 Simplified database design: Normalization provides guidelines for organizing
tables and data relationships, making it easier to design and maintain a database.
 Improved query performance: Normalized tables are typically easier to search and
retrieve data from, resulting in faster query performance.
 Easier database maintenance: Normalization reduces the complexity of a database
by breaking it down into smaller, more manageable tables, making it easier to add,
modify, and delete data.

Data modification anomalies can be categorized into three types:

o Insertion Anomaly: Insertion Anomaly refers to when one cannot insert a new tuple
into a relationship due to lack of data.
o Deletion Anomaly: The delete anomaly refers to the situation where the deletion of
data results in the unintended loss of some other important data.
o Updatation Anomaly: The update anomaly is when an update of a single data value
requires multiple rows of data to be updated.

First Normal Form

If a relation contain composite or multi-valued attribute, it violates first normal form or a
relation is in first normal form if it does not contain any composite or multi-valued
attribute. A relation is in first normal form if every attribute in that relation is singled
valued attribute.

Second Normal Form

A relation is in 2NF if it is in 1NF and any non-prime attribute (attributes which are not
part of any candidate key) is not partially dependent on any proper subset of any candidate
key of the table. In other words, we can say that, every non-prime attribute must be fully
dependent on each candidate key.
A functional dependency X->Y (where X and Y are set of attributes) is said to be
in partial dependency, if Y can be determined by any proper subset of X.
However, in 2NF it is possible for a prime attribute to be partially dependent on any
candidate key, but every non-prime attribute must be fully dependent(or not partially
dependent) on each candidate key of the table.

Third Normal Form

A relation is said to be in third normal form, if we did not have any transitive dependency
for non-prime attributes. The basic condition with the Third Normal Form is that, the
relation must be in Second Normal Form.
Below mentioned is the basic condition that must be hold in the non-trivial functional
dependency X -> Y:
 X is a Super Key.
 Y is a Prime Attribute ( this means that element of Y is some part of Candidate Key).

BCNF
BCNF (Boyce-Codd Normal Form) is just a advanced version of Third Normal Form.
Here we have some additional rules than Third Normal Form. The basic condition for any
relation to be in BCNF is that it must be in Third Normal Form.
We have to focus on some basic rules that are for BCNF:
1. Table must be in Third Normal Form.
2. In relation X->Y, X must be a superkey in a relation.

Fourth Normal Form

Fourth Normal Form contains no non-trivial multivalued dependency except candidate
key. The basic condition with Fourth Normal Form is that the relation must be in BCNF.
The basic rules are mentioned below.
1. It must be in BCNF.
2. It does not have any multi-valued dependency.
Fifth Normal Form
Fifth Normal Form is also called as Projected Normal Form. The basic conditions of Fifth
Normal Form is mentioned below.
Relation must be in Fourth Normal Form.
The relation must not be further non loss decomposed.

What is Decomposition in DBMS?

When we divide a table into multiple tables or divide a relation into multiple relations,
then this process is termed Decomposition in DBMS. We perform decomposition in
DBMS when we want to process a particular data set. It is performed in a database
management system when we need to ensure consistency and remove anomalies and
duplicate data present in the database. When we perform decomposition in DBMS, we
must try to ensure that no information or data is lost.

Types of Decomposition
There are two types of Decomposition:
 Lossless Decomposition
 Lossy Decomposition
Lossless Decomposition
The process in which where we can regain the original relation R with the help of joins
from the multiple relations formed after decomposition. This process is termed as lossless
decomposition. It is used to remove the redundant data from the database while retaining
the useful information. The lossless decomposition tries to ensure following things:
 While regaining the original relation, no information should be lost.
 If we perform join operation on the sub-divided relations, we must get the original
relation.
Example:
There is a relation called R(A, B, C)
A B C

55 16 27

48 52 89
Now we decompose this relation into two sub relations R1 and R2
R1(A, B)
A B

55 16

48 52
R2(B, C)
B C

16 27

52 89
After performing the Join operation we get the same original relation
A B C

55 16 27

48 52 89
Lossy Decomposition
As the name suggests, lossy decomposition means when we perform join operation on the
sub-relations it doesn’t result to the same relation which was decomposed. After the join
operation, we always found some extraneous tuples. These extra tuples genrates difficulty
for the user to identify the original tuples.
Example:
We have a relation R(A, B, C)
A B C

1 2 1

2 5 3

3 3 3
Now , we decompose it into sub-relations R1 and R2
R1(A, B)
A B

1 2

2 5

3 3
R2(B, C)
B C

2 1

5 3

3 3
Now After performing join operation
A B C

1 2 1

2 5 3

2 3 3

3 5 3

3 3 3
Properties of Decomposition
 Lossless: All the decomposition that we perform in Database management system
should be lossless. All the information should not be lost while performing the join
on the sub-relation to get back the original relation. It helps to remove the redundant
data from the database.
 Dependency Preservation: Dependency Preservation is an important technique in
database management system. It ensures that the functional dependencies between
the entities is maintained while performing decomposition. It helps to improve the
database efficiency, maintain consistency and integrity.
 Lack of Data Redundancy: Data Redundancy is generally termed as duplicate data
or repeated data. This property states that the decomposition performed should not
suffer redundant data. It will help us to get rid of unwanted data and focus only on
the useful data or information.