Partial Dependency in DBMS

DBMSDatabaseMySQL

What is Partial Dependency?

Partial Dependency occurs when a non-prime attribute is functionally dependent on part of a candidate key.

The 2nd Normal Form (2NF) eliminates the Partial Dependency.

Let us see an example −

Example

<StudentProject>

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, and

StudentID =  Unique ID of the student
StudentName = Name of the student
ProjectNo = Unique ID of the project
ProjectName = Name of the project


As stated, 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, which makes the relation Partial Dependent.

The ProjectName can be determined by ProjectNo, which makes the relation Partial Dependent.

Therefore, the <StudentProject> relation violates the 2NF in Normalization and is considered a bad database design.

To remove Partial Dependency and violation on 2NF, decompose the tables −

<StudentInfo>

StudentID
ProjectNo
StudentName
S01
199
Katie
S02
120
Ollie


<ProjectInfo>

ProjectNo
ProjectName
199
Geo Location
120
Cluster Exploration


Now the relation is in 2nd Normal form of Database Normalization.

raja
Published on 03-Jul-2018 22:54:28
Advertisements