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
BranchSection(the sections within each medical centre; primary key
branchNoas foreign key to
Staff(the people working at the medical centres; primary key
sectionNoas foreign key to
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
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).
sectionNo is a true primary key; it is unique across all