I'm struggling to work out how foreign keys can affect partial and transitive dependencies when normalising databases. I have multiple tables, two of which are job offer and accepted job. in job offer, offerID -> expectedStart, expectedEnd, hourlyPay, in the accepted job table jobID -> startDate, endDate, totalPay, offerID(FK).

My thinking is that the expected start and end dates can change after the job offer, so start and end dates are derived just from jobID, however totalPay is derived from startDate, endDate and offerID as the hourly pay is in the job offer table (of which offerID is the foreign key).

I'm trying to normalise the tables but am really struggling to work out if totalPay is causing partial or transitive dependencies as it is derived from two other entries in accepted jobs and also a foreign key.


1 Answer 1


If you quote & use definitions for FD (functional dependency), partial FD & transitive FD & for when they hold in a table (value or variable), you will see that they have nothing to do with FKs (foreign keys) or any other table(s).

Also you will see that they have nothing to do with an everyday notion of "derived from". A set of columns is "(functionally) dependent on" another set or single column when/iff certain conditions are true, as given in the definitions.

Also you will see that the FDs that hold in a table variable are determined by all the values that can arise for it. That is determined by the condition for rows going in the variable in a business situation & all the possible business situations that can arise. But it has nothing to do with whether (a part of) a variable's value "can change after" it holds some different value.

Also if you quote & use definitions for 2NF & 3NF you will see that it is only certain partial and transitive FDs that matter to those NFs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.