Determining Transitive Dependencies

dependencies

I am working on figuring out partial and transitive dependencies. The Transitive ones are really confusing me because I can see a transitive dependency on nearly all of these.enter image description here

The partial dependencies are Agree_No -> Agree_Date, Cust_No -> Cust_Name and Cust_Phone; Serial_No -> Item_Name and Item_Rate.

The Transitive Dependencies that I see are Item_Rate -> Item_Name. But could Cust_Phone be a transitive to Cust_Name and the Agree_No/Date? Are there others I am missing?

Best Answer

A FD (functional dependency) X -> Z is transitive when there exists a Y such that X -> Y AND Y -> Z AND NOT Y -> X AND NOT Y = Z. (X, Y & Z are attribute sets.)

There can be lots of X, Y & Zs where X -> Y AND Y -> Z but X -> Z is not transitive because Y -> X. Eg this happens whenever X and Y are CKs (candidate keys). (Because CKs functionally determine all attributes--each determines the other and Z.) And there can be lots of transitive FDs. Eg this happens whenever there is a FD whose attributes are non-prime. (Because a CK determines that FD's determinant which itself doesn't determine the CK--if it did then it would be a CK, which with non-prime attributes it isn't.) The FDs among all those ones that are important for normalization are transitive FDs of non-prime attributes on CKs when a relation is in 2NF. Because it is always possible to losslessly decompose such a relation to components free of them, ie in 3NF.