Creating a database for a scenario involving students, teachers and courses
2
votes
1
answer
3257
views
I am undertaking a project that involves a student management system, but I am struggling to normalise/create relationships between certain tables. Here is my current setup:
## Models
*
(id, first name, last name, gender, password, email)
* (id, name)
* (id, name, level, day, start time, end time)
## Attempted relationships (might be wrong)
* 1->has->N Roles
(I think I need a user/roles bridging table)
* (user role 6) N->has(studies)->M Courses
* (user role 3-5) N->has(teaches)->M Courses
## Example data
Table: Courses
id name level teacher day start_time end_time students (0NF)
--+-------+--------+---------+---+----------+--------+--------------
1 English Beginner 1 Mo 12:00 13:00 101,102...
2 English Beginner 1 Tu 12:00 13:00 101,124...
3 English Beginner 1 Fr 13:00 14:00 101,105...
4 English Expert 1 We 14:00 15:00 145,155...
5 Maths Single 2 Mo 12:00 13:00 135,163...
6 Maths Single 3 Tu 13:00 14:00 192,123...
7 Maths Full 3 Tu 15:00 16:00 134,101...
## Example report
Student (Id = 101)
-----------------------------
Attends English for Beginners (Taught by Teacher 1)
-----------------------------
Mon - 12:00 to 13:00
Tue - 12:00 to 13:00
Fri - 13:00 to 14:00
Attends Maths (Full) (Taught by Teacher 3)
-----------------------------
Tue - 15:00 to 16:00
Please forgive me for any inconsistencies in structure - I am a beginner in DB design and DBA StackExchange also. Could I have the steps explained to arrive at a solution?
Asked by buzzysin
(121 rep)
May 18, 2019, 04:18 PM
Last activity: Jun 5, 2025, 03:07 AM
Last activity: Jun 5, 2025, 03:07 AM