For an assignment I have to make a relational model for a School (it's just a practice exercise). The goal is to solve organizational problems.
I made the model, identified entities and relationships (although with some I have doubts that I will explain below). But I still don't know if it would be correct.
College Data:
small school; It consists of 20 courses with 15 to 30 students per course. (A student only corresponds to one course clearly).
5 Subjects
10 Professors (a professor can teach different subjects).
5 Rooms (shared. That is to say, from time to time the courses and teachers change rooms. There is not an exclusive room for a course).
1 day shift.
Relational Model:
Doubts:
My main doubt is that if the table
Clases
is correctly implemented.I have with the cardinality and relationship between the tables
Profesor
andAsignatura
.Taking into account that a teacher can teach different subjects. (There are 10 teachers and 5 subjects).
What I interpreted is that a professor can dictate several subjects and a subject can be dictated by several professors (more than one professor. Would it be correct? And since it is a Many to Many relationship, create the intermediate table
Asignaturas_por_Profesor
I don't know if an entity to add could be
Jornada
, since perhaps at a given moment said school may choose to add more than one day. Or maybe also because it is needed in the tableClases
?
Well, to be your first model, you're not bad: the generalization of
Personas
:) is very good. The things that can be improved are quite subtle. I tell you:Notas_por_Alumno
does not include the student, how would you know which student each note belongs to?The specification tells you that a professor can teach more than one subject, but it does not say that a subject can be taught by more than one professor. Which allows you to propagate the
rut_profesor
to the table aAsignaturas
and dispense with the tableAsignaturas_por_Profesor
Clases
should not depend onAsignaturas_por_Profesor
, but directly onAsignaturas
, unless it was taught by more than one teacher and you would like to know which one is in the classroom at all times. But I don't see that something like that is being asked of you.Asignaturas
should depend on theCursos
, otherwise you would have no way of differentiating between 1st grade and 2nd grade math, for example.As data modeling is not very well seen in SO because, by supporting different solutions, it is considered opinion-based and the question is likely to be closed, I encourage you to contact me through the linkedin link in my profile to clarify your doubts . Via comments the explanations can be very long.
I hope you found it useful.