Sample Header Ad - 728x90

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