Partial Dependency in DBMS

What is Partial Dependency in DBMS?

In order 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.