Partial Dependency in DBMS

Partial dependency is a situation in database normalization where a non-prime attribute is functionally dependent on only a part of a candidate key. The second normal form (2NF) is a normalization process that specifically addresses and eliminates partial dependencies.

What is Partial Dependency in DBMS?

To understand partial dependency, let us first know some basic terminologies with the help of an example.

Consider a relation(table) having four attributes, $P$, $Q$, $R$, $S$ having the following dependencies:

$P, Q \rightarrow S$
$Q \rightarrow R$

Using $P$ and $Q$, we can derive $S$, and using only $Q$, we can derive $R$. Hence, we can say that if we use both $P$ and $Q$ together, then we can derive all the attributes of the table, i.e., $P$, $Q$, $R$, $S$. (since $P \rightarrow P$ and $Q \rightarrow Q$ is self-explanatory).

We can write as $(PQ)^+ = \{P, Q, R, S\}$, or in simple words, we can say the closure of $P$ and $Q$ gives us all the attributes of the relation. The minimal sets like $P$Q in a relation(table) that are capable of deriving all the attributes of a relation(table) are called $Candidate\ keys$. There can be more than one candidate key in a table.

If an attribute is a part of any candidate key of the relation, then it is called a $Primary\ attribute$ else, it is said to be a $Non-Primary\ attribute$. In the example above, we can say that $P$ and $Q$ are primary attributes, and $R$ and $S$ are non-primary attributes.

We now know the basic definitions required to understand the concept of partial dependency. In the above example, $S$ is dependent on all the primary attributes, i.e., $P$ and $Q$. If either $P$ or $Q$ are missing, then we cannot derive $S$. In the case of $R$, it is not the same.

Even if $P$, a primary attribute, is missing, we can still derive $R$ using only $Q$. Hence, instead of depending totally on the candidate key, $R$ is partially dependent on $Q$, part of a candidate key. This is the concept of partial\ dependency.

What Causes Partial Dependency to Occur?

As we saw in the above section, partial dependency occurs whenever a non-prime attribute depends functionally on a part of the given candidate key.

In other words, Partial Dependency occurs when an attribute in a table depends on only a part of the primary key and not the whole key.

Partial Functional Dependency

A functional dependency denoted as $X \rightarrow Y$ where $X$ and $Y$ are an attribute set of a relation, is a partial dependency , if some attribute $A \in X$ can be removed and the dependency still holds.

Let us take an example, consider an example of a College. A student studies in a course, and every student in the college has a unique Roll number.

Suppose you are a student at this college. If a professor asks you to go and give a notebook to the student who has a roll. No. 2020CS37, you can quickly identify the student by observing his/her roll. no., i.e., $2020CS37$. S/he is from $2020$ batch, studying $Computer\ Science(CS)$ and his Roll. No. is $37$.

Hence, you can successfully give him/her the notebook. You don't even need to know the Course that s/he is pursuing because you can easily determine it with his/her unique Roll. No.

In other words, if someone provides you with a just the roll number, you can quickly tell the student's name. A roll number alone is sufficient to identify or know the student's name. The $Name$ attribute is partially dependent on the $Roll.\ No.$ attribute.

Full Functional Dependency

A functional dependency denoted as $X \rightarrow Y$ where $X$ and $Y$ are an attribute set of a relation, is a full dependency , if all the attributes present in $X$ are required to maintain the dependency.

Let us take an example, consider an example of a school. A student studies in a class, and in each class, every student has a unique Roll number.

Suppose you are a student at this school. If a teacher asks you to go and give a notebook to the student who has a roll. No. 37, you will get confused. Then you will ask the teacher to tell you about the class in which s/he is studying. You can then quickly identify the student and successfully give him/her the notebook.

In other words, if someone provides you with a class and the roll number, you can quickly tell the student's name. A class or a roll number alone is insufficient to identify or know the student's name. The $Name$ attribute is fully dependent on the $Class$ and $Roll.\ No.$ attribute.