[database] Partial Dependency (Databases)

I fabricated a definition that a partial dependency is when fields are indirectly dependent on the primary key or partially dependent but are also dependent on other keys that depend on the primary such that if the field which another field depends on is deleted then that field will still exist due to its dependence on the primary key. I am not sure if it is correct. I have researched and every definition sounds misleading. Is my definition correct and if not what is?

This question is related to database functional-dependencies

The answer is


  • consider a table={cid,sid,location}
  • candidate key: cidsid (uniquely identify the row)
  • prime attributes: cid and sid (attributes which are used in making of candidate key)
  • non-prime attribute: location(attribute other than candidate key)

if candidate key determine non-prime attribute:

i.e cidsid--->location (---->=determining) 
   then, it is fully functional dependent

if proper subset of candidate key determining non-prime attribute:

 i.e sid--->location (proper subset are sid and cid)
         then it is term as partial dependency

to remove partial dependency we divide the table accordingly .


Partial Dependency is one kind of functional dependency that occur when primary key must be candidate key and non prime attribute are depends on the subset/part of candidates key (more than one primary key).

Try to understand partial dependency relate through example :

Seller(Id, Product, Price)

Candidate Key : Id, Product
Non prime attribute : Price

Price attribute only depends on only Product attribute which is a subset of candidate key, Not the whole candidate key(Id, Product) key . It is called partial dependency.

So we can say that Product->Price is partial dependency.


I hope this explaination gives a more intuitive appeal to dependency than the answers previously given.

Functional Dependency

An analysis of dependency operates on the attribute level, i.e. one or more attribute is determined by another attribute, it comes before the concept of keys. 'The role of a key is based on the concept of determination. 'Determination is the state in which knowing the value of one attribute makes it possible to determine the value of another.' Database Systems 12ed

Functional dependency is when one or more attributes determine one or more attributes. For instance:

Social Security Number -> First Name, Last Name.

However, by definition of functional dependency:

(SSN, First Name) -> Last Name

This is also a valid functional dependency. The determinants (The attribute that which determines another attribution) are called super key.

Full Functional Dependency

Thus, as a subset of functional dependency, there is the concept of full functional dependency, where the bare minimal determinant is considered. We refer those bare minimal determinants collectively as one candidate key (weird linguistic quirk in my opinion, like the concept of vector).

Partial Functional Dependency

However, sometimes one of the attributes in the candidate key is sufficient to determine another attribute(s), BUT not all, in a relation (a table with no rows). That, is when you have a partial functional dependency within a relation.


Partial dependency implies is a situation where a non-prime attribute(An attribute that does not form part of the determinant(Primary key/Candidate key)) is functionally dependent to a portion/part of a primary key/Candidate key.


Partial Functional Dependency occurs only in relation with composite keys. Partial functional dependency occurs when one or more non key attribute are depending on a part of the primary key.

Example:

Table: Stud_id, Course_id, Stud_name, Course_Name

Where: Primary Key = Stud_id + Course_id

Then: To determine name of student we use only Stud_id, which is part of primary key.

{Stud_id} -> {Stud_Name}

Hence,Stud_name is partially dependent on Stud_id. This is called partial dependency.


Partial dependence is solved for arriving to a relation in 2NF but 2NF is a "stepping stone" (C. Date) for solving any transitive dependency and arriving to a relation in 3NF (which is the operational target). However, the most interested thing on partial dependence is that it is a particular case of the own transitive dependency. This was demostrated by P. A. Berstein in 1976: IF {(x•y)?z but y?z} THEN {(x•y)?y & y?z}. The 3NF synthesizer algorithm of Berstein does not need doing distintions among these two type of relational defects.


If there is a Relation R(ABC)

-----------
|A | B | C |
-----------
|a | 1 | x |
|b | 1 | x |
|c | 1 | x |
|d | 2 | y |
|e | 2 | y |
|f | 3 | z |
|g | 3 | z |
 ----------
Given,
F1: A --> B 
F2: B --> C

The Primary Key and Candidate Key is: A

As the closure of A+ = {ABC} or R --- So only attribute A is sufficient to find Relation R.

DEF-1: From Some Definitions (unknown source) - A partial dependency is a dependency when prime attribute (i.e., an attribute that is a part(or proper subset) of Candidate Key) determines non-prime attribute (i.e., an attribute that is not the part (or subset) of Candidate Key).

Hence, A is a prime(P) attribute and B, C are non-prime(NP) attributes.

So, from the above DEF-1,

CONSIDERATION-1:: F1: A --> B (P determines NP) --- It must be Partial Dependency.

CONSIDERATION-2:: F2: B --> C (NP determines NP) --- Transitive Dependency.

What I understood from @philipxy answer (https://stackoverflow.com/a/25827210/6009502) is...

CONSIDERATION-1:: F1: A --> B; Should be fully functional dependency because B is completely dependent on A and If we Remove A then there is no proper subset of (for complete clarification consider L.H.S. as X NOT BY SINGLE ATTRIBUTE) that could determine B.

For Example: If I consider F1: X --> Y where X = {A} and Y = {B} then if we remove A from X; i.e., X - {A} = {}; and an empty set is not considered generally (or not at all) to define functional dependency. So, there is no proper subset of X that could hold the dependency F1: X --> Y; Hence, it is fully functional dependency.

F1: A --> B If we remove A then there is no attribute that could hold functional dependency F1. Hence, F1 is fully functional dependency not partial dependency.

If F1 were, F1: AC --> B;
and F2 were, F2: C --> B; 
then on the removal of A;
C --> B that means B is still dependent on C; 
we can say F1 is partial dependecy.

So, @philipxy answer contradicts DEF-1 and CONSIDERATION-1 that is true and crystal clear.

Hence, F1: A --> B is Fully Functional Dependency not partial dependency.

I have considered X to show left hand side of functional dependency because single attribute couldn't have a proper subset of attributes. Here, I am considering X as a set of attributes and in current scenario X is {A}

-- For the source of DEF-1, please search on google you may be able to hit similar definitions. (Consider that DEF-1 is incorrect or do not work in the above-mentioned example).


Partial dependency means that a nonprime attribute is functionally dependent on part of a candidate key. (A nonprime attribute is an attribute that's not part of any candidate key.)

For example, let's start with R{ABCD}, and the functional dependencies AB->CD and A->C.

The only candidate key for R is AB. C and D are a nonprime attributes. C is functionally dependent on A. A is part of a candidate key. That's a partial dependency.