We have a task to model following situation using conceptual ERD model:
We have teachers and students. Students belong into groups. The teachers can teach different lessons/courses, while a course can be taught by different teacher. Each group has its own timetable. And the timetable determines the room & course and the teacher giving the lesson.
The tool used to create the conceptual ERD model is Oracle Data Modeler (although this doesn't matter much in my opinion).
The task description is a bit vague, so I've added few more definitions:
The timetable consists of days: Mo - Fri (possibly up to Sunday) and says, which day and time a lesson takes place and who gives the lessons.
Another point is, a teacher can have "expertise" in teaching different lessons, but this doesn't necessarily mean he will be giving lessons for some course. This is also puzzling me how to reflect this in the model and not duplicate the entities etc.
So I was thinking to add days/hours to the timetable, but then a day would be multiple value attribute, which is not allowed in relational model what I understand.
I'm including the current state of my conceptual model.
I've created part of the conceptual model, however I've got stuck with the design and I'm not sure how to continue from logical point of view. Model is attached as picture (don't know how to export the model from ODM into some "source-code").
May I ask you for your insight how you would model the situation? No need to include complete models, verbal explanation is sufficient.
When I read your first paragraph, I came up with something very close to yours, except the section that handles the timetable. IMHO you do not need the Timetable and Monday tables. Link the group directly to the TimeFrame.
I think you are confusing tables with lines for Timetable / TimeFrame. No need for a table called TimeFrame_8:00-8:45. There should be only a Timetable (between Room and Teacher in your diagram) which contains:
Your idea of linking Teacher with Lesson to specify the qualifications of the teacher is good. Your application will need to verify that this link exists before assigning a teacher to a timeframe.
Disclaimer: my response is from the point of view of a developer / implementer, not an academic database expert.