How do foreign keys affect partial and transitive dependencies (databases)

database-designnormalization

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.

Best Answer

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.

Related Solutions

Is DB Normalization done purely based on the primary key or is it done based on all the candidate keys

Normalization is concerned with all Candidate Keys. A Primary Key is just a candidate key. Primary keys are no different to any other candidate key.

Potential confusion arises because in the early days of relational database theory the term Primary Key used to mean any and all candidate keys whereas modern usage is that Primary Key means only one key that is "preferred" or deemed to have some special significance for database users. Unfortunately many of the "definitions" you will see online or even in print are wrong as a result.

Confusion normalizing data to 3NF and transitive dependencies

You have:

R (ABCDEFGH)

AB-->D
B-->C
B-->E
A-->H
H-->G

I'm not sure how you resulted in the decompositions but the first thing to do usually is to find the candidate keys if the relations. Here there is only one, the ABF.

Then you identify that the relation is not in 2NF, as there are dependencies on parts of candidate keys and not the whole of them (this part seems to have been done it quite well.) The partial dependencies (on parts of the candidate keys) are these and you only missed the last one, that can be derived from the others:

AB-->D
B-->C
B-->E
A-->H
A-->G             -- missed this one

So (leaving the first question for the end), the decompositions to:

R (ABCDEFGH) 
                          -- splitting because of the AB -> D, fine
R*(ABCEFGH)  R1 (ABD)
                          -- splitting because of the B -> C, fine
R**(ABEFGH)  R2 (BC)
                          -- splitting because of the B -> E, fine
R***(ABFGH)  R3 (BE)

are rather fine, except some minor details like the D reappearing in the R relation.

The next part which is your second question regarding the A->H and H-G was problematic due to your missing of the implied A->G dependency. It should be:

                          -- splitting because of the A -> HG
R****(ABF)   R4 (AHG)

We have now resulted in 5 relations, all in 2NF:

R**** (ABF)
R1 (ABD)      -- AB-> D
R2 (BC)       -- B -> C
R3 (BE)       -- B -> E
R4 (AHG)      -- A -> HG ,  H -> G

In fact all are in 3NF except for R4. So, we have to split that into two relations:

R4* (AH)      -- A -> H
R5 (HG)       -- H -> G

We now have 6 relations (R****, R1, R2, R3, R4*, R5) which are all in 3NF and BCNF as well. In fact they are all in 4, 5 and 6NF.

Regarding your first question, you could have used one split into (BCE) instead of the two (BC) and (BE) and it would have been perfectly fine. Your final result would be five relations (instead of six), all in 3NF - and all the way up to BCNF, 4NF, 5NF, 6NF (except for that (BCE) which is in 5NF.)

Best Answer

Related Solutions

Is DB Normalization done purely based on the primary key or is it done based on all the candidate keys

Confusion normalizing data to 3NF and transitive dependencies

Related Question