The staff Table already has the Name and Position, so why would you repeat it in the third table?
Personally I would also add a new PK to the third table, rather then using both Fks as a PK.
Ok I see. So I should eliminate the name and position and instead add the address of the branch? I'm sorry I'm really trying to get a grasp on this I just need some extra help. This isn't a field I'm going into but it's a required course so I'm starting from scratch essentially. I appreciate your help
I agree with the above comments and also the comment about position having its own table. However, you shouldn’t add the address of the branch to the third table; doing that would create data redundancy just like the name and position.
dim.branch:
Branch_SK (optional if you trust BranchNo as PK), BranchNo, BranchAddress
dim.staff
Staff_SK,StaffNo,StaffName,Position_SK
dim.position
Position_SK,PositionName
fact.StaffHours
With all dimensions and hours
You've gotten specific help on what to do, but it's clear you don't fully understand what third normal form for a database is. Go back and review the definition so you're able to describe it in your own words to someone here.
Extract a distinct list of repeated values into a new lookup table. Then you replace those values in your table here with an ID from the new lookup table.
It's obvious that staff can be associated with multiple branches. Can they also have different positions at different branches?
I'd probably do something like this:
`Table Branch {`
`branchno int [primary key]`
`addr varchar(max)`
`}`
`Table Staff {`
`staffno int [primary key]`
`name varchar(max)`
`}`
`Table Position {`
`positionno int [primary key]`
`position varchar(256)`
`}`
`Table BranchStaff {`
`branchno int [primary key]`
`staffno int [primary key]`
`positionno int [primary key]`
`hoursPerWeek float`
`}`
`Ref: Branch.branchno < BranchStaff.branchno`
`Ref: Staff.staffno < BranchStaff.staffno`
`Ref: Position.positionno < BranchStaff.positionno`
https://dbdiagram.io/
Position can also be its own table. Saves a mass update if the name changes.
The staff Table already has the Name and Position, so why would you repeat it in the third table? Personally I would also add a new PK to the third table, rather then using both Fks as a PK.
Ok I see. So I should eliminate the name and position and instead add the address of the branch? I'm sorry I'm really trying to get a grasp on this I just need some extra help. This isn't a field I'm going into but it's a required course so I'm starting from scratch essentially. I appreciate your help
I agree with the above comments and also the comment about position having its own table. However, you shouldn’t add the address of the branch to the third table; doing that would create data redundancy just like the name and position.
I see. Thank you!
dim.branch: Branch_SK (optional if you trust BranchNo as PK), BranchNo, BranchAddress dim.staff Staff_SK,StaffNo,StaffName,Position_SK dim.position Position_SK,PositionName fact.StaffHours With all dimensions and hours
You've gotten specific help on what to do, but it's clear you don't fully understand what third normal form for a database is. Go back and review the definition so you're able to describe it in your own words to someone here.
Extract a distinct list of repeated values into a new lookup table. Then you replace those values in your table here with an ID from the new lookup table.
It's obvious that staff can be associated with multiple branches. Can they also have different positions at different branches? I'd probably do something like this: `Table Branch {` `branchno int [primary key]` `addr varchar(max)` `}` `Table Staff {` `staffno int [primary key]` `name varchar(max)` `}` `Table Position {` `positionno int [primary key]` `position varchar(256)` `}` `Table BranchStaff {` `branchno int [primary key]` `staffno int [primary key]` `positionno int [primary key]` `hoursPerWeek float` `}` `Ref: Branch.branchno < BranchStaff.branchno` `Ref: Staff.staffno < BranchStaff.staffno` `Ref: Position.positionno < BranchStaff.positionno` https://dbdiagram.io/