Tweeted twitter.com/StackDBAs/status/919078005733056515
2 Formatting for entity names and lists; reoganizes a little, and added info from comment.
source | link

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 'Staff', 'Branch', and 'BranchSection' (with 'Branch' representing the medical centres and 'BranchSection' representing the sections within each medical centre). The 'BranchSection' entity had a primary key called 'SectionNo' and for each 'SectionNo' there was a corresponding 'BranchNo' foreign key attribute (which is the primary key of the 'Branch' entity). :

  • 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'SectionNo attribute as a foreign key in the staff entity (see below). I intentionally didn't put the 'BranchNo'BranchNo attribute into the staff entity because the 'SectionNo'SectionNo determines the 'BranchNo'BranchNo attribute and so I thought there would be a transitive dependency if I had both 'SectionNo'SectionNo and 'BranchNo'BranchNo in the staff entity. However, when I got my result back I was marked down for not having 'BranchNo'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)

  • Branch (BranchNo, address, telephoneNo)

  • BranchSection (sectionNo, sectionName, branchNo)

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

BranchSectionNote: (sectionNo, sectionName, branchNo)

StaffsectionNo is a true primary key; it is unique across all (staffNo, firstName, lastName, position, birthDate, telephoneNo, sectionNo)Branches.

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 'Staff', 'Branch', and 'BranchSection' (with 'Branch' representing the medical centres and 'BranchSection' representing the sections within each medical centre). The 'BranchSection' entity had a primary key called 'SectionNo' and for each 'SectionNo' there was a corresponding 'BranchNo' foreign key attribute (which is the primary key of the 'Branch' entity).

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)

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.

1
source | link

Transitive dependency with foreign keys

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 'Staff', 'Branch', and 'BranchSection' (with 'Branch' representing the medical centres and 'BranchSection' representing the sections within each medical centre). The 'BranchSection' entity had a primary key called 'SectionNo' and for each 'SectionNo' there was a corresponding 'BranchNo' foreign key attribute (which is the primary key of the 'Branch' entity).

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)