A module leader supervises 1..5 modules. Many module tutors can teach many modules, this is a many to many multiplicity, so i have made a new entity called ModuleuTutorTeaches, in the middle of the 2 entities.

What attributes would the 4 tabels have?

Module Leader (staff ID{FK}ModuleID) < this would uniquly identify what staff was leading what module. The two attributes which will be FKs will create a PPK. This is fine

Module (ModuleID, moduleName, moduleCode, moduleDescirption) This does not need chaning

The 2 entities which i am stugiling to find atributes for are Module Tutor and ModuleuTutorTeaches.

The reason is the primary key for module tutor would be the same as the module leader, would this be ok? I don't think it would be as there could possible be 2 of the same primary keys which can't happen, can anyone suggest any other attributes i could put in to make it unique?

Additionally what attributes would go in the ModuleuTutorTeaches?

6 Years
Discussion Span
Last Post by BitBlt

Let's see if I can help. I may be making some wrong assumptions based on what you posted, but I'll do what I can.
1. It sounds like you should have an entity "StaffMember", uniquely identified by "StaffId". This would contain the list of all Module Leaders and all Tutors.
2. You should have an entity "Module", uniquely identified by "ModuleId".
3. If a "Module" can have one and only one leader, then there should be an FK relationship between "StaffMember" and "Module", where "StaffId" migrates and can be given the rolename "ModuleLeaderId" and resolve to "StaffId".
4. If a "Module" can have more than one tutor, then there should be a separate resolution entity "ModuleTutor" that has foreign keys to both "Module" and "StaffMember". You can rolename the StaffId to "ModuleTutorId" for clarity's sake.
5. To summarize:
Module(ModuleId[pk], moduleName, moduleCode, moduleDescription, ModuleLeaderId[fk, StaffMember->StaffId])
StaffMember(StaffId[pk], StaffMemberName)
ModuleTutor(ModuleId[pk, fk Module->ModuleId], ModuleTutorId[pk, fk StaffMember->StaffId])

There are other possibilities to add attributes, such as a StaffMemberType in "Staff" if a ModuleLeader can never be a ModuleTutor. Or, if you want to have a "Module" able to have many Leaders, then you could create a resolution entity similar to what you did with "ModuleTutor", just have the rolename for StaffId be "ModuleLeaderId" or some such.

To make things even more generalized, you could have only one resolution table, and call it "ModuleStaffRelation", and specify another table to non-identifying foreign key into it to specify what KIND of relationship (e.g. "Leader" or "Tutor"). There are even more possibilities to specify (for instance) whether or not one StaffMember could be both Leader and Tutor for the same Module. It goes on and on.

This would of course be much easier with pictures...I hope I haven't completely confused you. Good luck!

Edited by BitBlt: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.