Foreign Key Dependencies – Understanding Transitive Dependency with Foreign Keys

dependenciesforeign keynormalization

For an assignment, I had to design a database from a given problem (based on a fictional health service organisation with 3 medical centres).

Three of the entities I had (among many others) were:

  • Branch (the actual medical centres; primary key branchNo)
  • BranchSection (the sections within each medical centre; primary key sectionNo; includes branchNo as foreign key to Branch table.)
  • Staff (the people working at the medical centres; primary key staffNo; includes sectionNo as foreign key to BranchSection)

One of the requirements of the given problem was that the staff entity hold information about the section and branch of each staff member, and so I added the SectionNo attribute as a foreign key in the staff entity (see below). I intentionally didn't put the BranchNo attribute into the staff entity because the SectionNo determines the BranchNo attribute and so I thought there would be a transitive dependency if I had both SectionNo and BranchNo in the staff entity. However, when I got my result back I was marked down for not having BranchNo in the staff entity. Could someone please clarify whether I was right in assuming this would cause a transitive dependency or whether my tutor was right to mark me down.

Here is a visual representation of these 3 entities (to make it easier to understand what I'm saying).

  • Branch (BranchNo, address, telephoneNo)

  • BranchSection (sectionNo, sectionName, branchNo)

  • Staff (staffNo, firstName, lastName, position, birthdate, telephoneNo, sectionNo)

Note: sectionNo is a true primary key; it is unique across all Branches.

Best Answer

While I would say you are definitely correct, and this was the correct design, your tutor may be technically correct (it's difficult to tell, especially, if you're translating form another language - can't tell if you are or not (congrats!)).

You are correct, that a given sectionNo determines the branchNo, since that value exists in BranchSection. Including branchNo in Staff in addition to sectionNo is redundant, and would indeed lead to having to make sure that all linked branchNo values in Staff are updated if the branchNo in BranchSection were ever changed.

However, you said:

One of the requirements of the given problem was that the staff entity hold information about the section and branch of each staff member

(Emphasis added)

Technically, Staff does not (directly) hold information about the branch; only about the section.

If you haven't already argued too much with the tutor, I would note the maintenance (and normalization) issues, and ask him why he wants you to start off with a denormalized design.

And I mean ask him, seriously. There may be a reason. At the conceptual level, you do need to tie a staff person to the branch they're located at; perhaps he believes that, for a high-level design, you need to leave that sort of thing in place, even if it's not literally going to be implemented that way in the actual database. Maybe they wanted everyone to include it so they can explain the importance of normalization (a bad reason to mark you down still, but if you've got tutors with an actual professor over them, they may be following instructions overly literally). Or, maybe they're training you for the real world; customers sometimes have specific requests that defy best DB practices, and that you can't talk them out of.

It's even possible that this implies a deeper issue with the design. Perhaps BranchSection should be just Section, and should apply to all instances of a given section, regardless of the branch. If so, then branchNo wouldn't belong, and each Staff row would need both branchNo and sectionNo. That's not how I read the question as presented, and it seems rather unlikely to me, but it's not impossible.

So, especially if you'll have further assignments based off this one, I'd say you have a valid reason to ask for further explanation, to valid your reading of the assignment.

If all else fails, and there is a class professor over the tutor, talk to them. They may have the latitude to accept a "more correct" design than expected, if that's what happened, and may have a better view on the class overall, and might have the explanation you need.

Related Question