Normalization Dependencies

Question

Im just trying to make sure that im thinking of it the right way

1)full dependencies are when one or more primary keys determine another attribute

2)partial dependencies are when one of the primary keys determines another attribute or attributes

3)transitive dependencies are when a nonkey attribute determines another attribute

am i thinking of it right?

Nicknack

Answer 1

This answer is directly from my CS course and obtained from the Connolly and Begg text book.

Full Functional Dependencies

Identify the candidate keys (here, propertyNo, iDate and pAddress). This is because any combination of those 3 can allow you to find what the other attributes are for a given tuple (I can find the staffNo that did the inspection given those three things, I can find the carReg the staffNo used given those 3 things etc.). But note, you need all of those 3 to find the other attributes, not just a subset. Full dependencies always relate to non-candidate keys depending on candidate keys, either depending on all or depending on some.

Partial Dependencies

Given those three candidate keys, look within the candidate keys. Is there any subset(s) of the candidate key which is dependent on the other? Yes, it is pAddress. Given a propertyNo, you can figure out what the address of the property. Then look outside of the candidate keys. Is there any of these keys that depend on only parts of the candidate key, not all components? In this case there are not. So partial dependencies are always dependencies within the candidate keys or dependencies of non-candidate keys on only parts of the candidate keys rather than all components

Transitive Dependencies

Now, look at the non-candidate keys (staffNo, comments, iTime (inspection time), sName, carReg). Within those, is there anything that is functionally dependent on the other? Yes, it is sName - given a staffNo, you can figure out the name of the staff member. But staffNo is functionally dependent on the 3 candidate keys. So by transitivity, propertyNo + iDate + pAddress -> staffNo -> sName, so sName is transitively dependent on staffNo. Transitive dependencies always relate to attributes outside of candidate keys.

Callery

Unfortunately, that is a very poor textbook & full of mistakes. This muddles notions about FDs with unrelated notions of CKs & NFs. Moreover this is merely vague & introductory. It says some things about FDs & other things, but doesn't actually say what any of them are. – Pail

Answer 2

Not quite. It would help to be more exact in your terminology: when you say things like "one or more primary keys" you (presumably) really mean "one or more of the columns of the primary key"?

The distinction between a full and a partial dependency only arises when a key consists of more than one column (a composite key):

1) Full dependencies are when the full key is required (all columns of the key) to determine another attribute.

2) Partial dependencies are when the key is composite and some but not all of the columns of the key determine another attribute. (This may still be more than one column.)

3) Transitive dependencies are as you said.

Horology

"The distinction ... only arises when a key consists of more than one column" not quite true. A partial dependency involving a simple key occurs in the relatively rare but perfectly possible case when the empty set {} is a determinant. – Corelli

Answer 3

Fully dependent means dependent on all the attributes in question, usually meaning all the attributes of a candidate key. It doesn't have to be a key designated as "primary" because primary keys don't play any special role in dependency theory and normalization.

Partially dependent means dependent on a proper subset of those attributes, usually meaning a proper subset of some candidate key.

Depending on the context, transitive dependency can mean either one of the following:

(1) a dependency of the form A->B, B->C

(2) a dependency of the form A->B, B->C where B isn't a superkey

Almost always the term transitive dependency is used when referring to the situation described by (2) and has become virtually synonymous with that sense even though (1) is the more formally correct meaning.

Corelli

Answer 4

Partial Dependency: Where an attribute in a table depends on only a part of the primary key and not on the whole key. (For detail see this link) https://www.studytonight.com/dbms/second-normal-form.php

Transitive Dependency: When a non-prime attribute depends on other non-prime attributes rather than depending upon the prime attributes or primary key. (For detail see this link) https://www.studytonight.com/dbms/third-normal-form.php

Laundryman