Partial Dependency (Databases)

Question

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?

Stabler

Answer 1

A FD (functional dependency) that holds in a relation is partial when removing one of the determining attributes gives a FD that holds in the relation. A FD that isn't partial is full.

Eg: Suppose {A,B} → {C} but also {A} → {C}. Then {A,B} → {C} is partial; {C} is partially functionally dependent on {A,B}; {C} is functionally dependent on a part of {A,B} that is not all of it. The consequent partial FD is not {A} → {C}. Whether that is partial depends on (per the definition of partial FD) whether a subset of {A} determines {C}; whether {} → {C}.

Eg: Here's a relation value where that example condition holds. (A FD holds in a relation variable when it holds in every value that can arise.)

A  B  C
1  1  1
1  2  1
2  1  1

The non-trivial FDs that hold: {A,B} determines {C}, {B,C}, {A,C} & {A,B,C}; {A}, {B} & {} also determine {C}. Of those: {A,B} → {C} is partial per {A} → {C}, {B} → {C} & {} → {C}; {A} → {C} & {B} → {C} are partial per {} → {C}; the others are full.

A functional dependency X → Y is a full functional dependency if removal of any attribute A from X means that the dependency does not hold any more; that is, for any attribute A ε X, (X – {A}) does not functionally determine Y. A functional dependency X → Y is a partial dependency if some attribute A ε X can be removed from X and the dependency still holds; that is, for some A ε X, (X – {A}) → Y.

-- Fundamentals OF Database Systems 6th Edition, Ramez Elmasri & Navathe

Notice that whether a FD is full vs partial doesn't depend on CKs (candidate keys), let alone one CK that you might be calling the PK (primary key).

(A definition of 2NF is that every non-CK attribute is fully functionally determined by every CK. Observe that the only CK is {A,B} & the only non-CK attribute C is partially dependent on it so this value is not in 2NF & indeed it is the lossless join of components/projections onto {A,B} & {A,C}, onto {A,B} & {B,C} & onto {A,B} & {C}.)

(Beware that that textbook's definition of "transitive FD" does not define the same sort of thing as the standard definition of "transitive FD".)

Streaming

Does partial dependency occur only when there is a composite primary key? Database Systems: Design, Implementation, & Management By Carlos Coronel, Steven Morris states that Conversion to 2NF occurs only when the 1NF has composite primary key. If the 1NF has a singleiattribute primary key, then the table is automatically in 2NF. I am so much confused in this too, please help – Tokenism

(You should really post this as a question.) No. There can be partial dependencies not involving CKs (candidate keys). Eg there are partial FDs for for every full FD whose left hand side isn't all the attributes because its right hand side is partially dependent on every proper superset of that left hand side. Read the last sentence in my answer. Partial/full is a property of FDs. The FDs collectively determine the CKs and highest NF up to BCNF. PS: PKs have no role in normalization. One simply picks one CK and calls it "the PK". – Streaming

PPS: 2NF needs no partial FDs of non-prime attributes on CKs. The book is saying that for non-2NF there can't be a FD from a proper subset of a one-attribute CK. But the book is wrong. They forgot the empty set as proper subset: One can have non-2NF with CK {A} yet a partial FD of non-prime B on it when {}->B (ie B values are the same). Textbooks & the SQL world often forget about the empty set in FDs. Eg this & this & this. – Streaming

Your abstract definition could benefit from a concrete example. – Cascarilla

@JeffPuckettII I gave an example. I don't know what you think makes a definition "abstract" vs not or an example "concrete" vs not. (Maybe for you, by definition of "definition" & "example"?) I suppose you mean, an example consisting one full FD, one partial FD, for each a relation/table value satisfying it, an enumeration of all the FDs that one can get by dropping a determining attribute from each FD, and the observations that none of the new FDs from the full FD are satisfied, hence it's full, and at least one of the new FDs from the partial FD is satisfied, hence it's partial? – Streaming

@philipxy; Can you please edit your answer with an Example consisting of arbitrary relational table So that we could get your point without any confusion. Thanks for your efforts. – Sager

@Sager See my edited post with example data. But--Please read my last comment, re "example". I already gave an "example"--"Eg if {A,B} → {C} but also {A} → {C} then {C} is partially functionally dependent on {A,B}." If you don't know how to determine whether an FD holds then you need to find out. Where do I stop explaining? You need to memorize definitions. Then to know whether you have a thing of a certain kind determine whether the definition of that kind of thing applies, ie determine whether the defining condition of that sort of thing holds, ie is satisfied. – Streaming

Answer 2

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.

Cloudless

Every FD that holds is either full or partial; keys don't enter into it. – Streaming

C is partially dependent on AB, ie AB->C is a partial FD, because C is dependent on a proper subset of AB, A. C is fully dependent on A, ie A->C is a full FD, because C is not functionally dependent on a proper subset of A. – Streaming

Hi. What is your reference for your definition of "partial FD"? See my answer for a different one--per the quoted textbook. Here is Codd's original definition of "full FD" in Further Normalization of the Data Base Relational Model: "Suppose D,E are two distinct subcollections of the attributes of a relation R and R.D -> R.E. If, in addition, E is not functionally dependent on any subset of D (other than D itself) then E is said to be fully dependent on D in R." (That's not defined for X->X. More to research.) – Streaming

@philipxy: I don't have a reference. I have no formal training in anything to do with computers. I'm entirely self-taught. I'd be surprised if I got everything right. – Congest

Thanks. Unfortunately this answer is wrong & people come to this post & think wrong things. Actually I suspect that they don't even get to the page to think those wrong things, because at page top above any links when I google 'partial functional dependency' is text from this page's posts saying wrong things. Just now that text was the start of your post, but other times it's from other wrong posts. (I haven't seen my answer though. OK I may have learned something about ranking & grammar there.) PS Your & dportas/sqlvogel/nvogel's DB theory posts are (typically) of exceptionally high quality. – Streaming

The last paragraph of your answer incorrectly says that A -> C is partial, but it is not, and the partial FD that violates 2NF is AB -> C. Which I didn't make clear in first comments on this answer. – Streaming

Answer 3

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.

Geiss

What is your reference for your definition of "partial FD"? – Streaming

Answer 4

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.

Flynn

What is your reference for your definition of "partial FD"? – Streaming

I have written this based on my intuition. That's why I can not able to give reference. If find any error, obviously inform . – Flynn

Technical terms have meanings & histories. "Partial FD" was defined to mean a certain thing & it has consistently been used with that meaning in research & textbooks. It is easy to find a definition in a textbook. It is not defined in terms of CKs or PKs. See my answer. But this post by you is not consistent with what it means. (Also your English is not clear.) So I was interested in why you think it means whatever you think it means--although your post is not clear enough to know what you think it means. "Intuition" is irrelevant & it is very strange that you mention it. – Streaming

Answer 5

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.

Cosimo

What is your reference for your definition of "partial FD"? – Streaming

Answer 6

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.

Bookmark

Answer 7

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.

Roughish

The first sentence is wrong, see my answer & my comments on it. The second sentence is wrong & unclear, see my answer. The rest is unclear. – Streaming

What is your reference for your definition of "partial FD"? – Streaming

Answer 8

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://mcmap.net/q/468323/-partial-dependency-databases) 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).

Sager

This is not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. Don't try to cram everything into one phrase or sentence. Also this says wrong things. Those FDs need to form a cover or you can't determine the CKs. PKs are irrelevant. "sufficient to find" is just everyday words. "we can say B is partially dependent on A as well as C"--no, B is partially dependent on AC. Def-1 garbles "partial FD" with "2NF". How can a source you quote be unknown?--And what is the point of giving it?--And what are you doing with it? ... – Streaming

... See my comments on my answer & my edited answer re how a FD with a single attribute as determinant can be partial because {} can be a determinant. "because B is completely dependent on A" is wrong, it is because B is not dependent on not all of A--obviously in X->Y Y is always dependent on all of X. What is the point of your example data?--You don't use its content for anything. Etc etc. – Streaming

@Streaming updated my answer for more clarification that might help you to get my point. However, in a practical scenario, I have never seen empty attribute or empty set -- {} is determining anything. It might be possible theoretically. But there is no practical usage of this. – Sager

This still has most of the problems--I guess we disagree. PS My last comment already contradicts your last comment re {}. (Just apply the definition of FD.) PS {}->{X} when/iff X must have at most one value. We don't see that much because it's typically an obviously bad design. But 2 examples from practice are a 1-row table of parameter values ({} is the one CK) & a certain SQL idiom for constraining declaratively where a subtype table has a column with only its own type tag as FK to a subtype tag column in the supertype table. – Streaming

PS re "Given" FDs: What does "I have these FDs" mean? "These are all the FDs that hold"?--Not possible. "These are all the non-trivial FDs that hold"?--Not possible. "These are some FDs that hold"?--Question can't be answered. Find out what a cover is & what the exact conditions are to apply a particular definition/rule/algorithm. To determine CKs & NFs we must be given FDs that form a cover. Sometimes a minimal/irreducible cover. See this answer. – Streaming

Answer 9

• 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 .

Formulary