I am creating system for a school management system and come up with the attached database schema.
Following is how the system works:
I am a beginner in database normalization and would be glad if anyone could give me some hints if the database looks alright or not.
EDIT:
Also, there will only be one point of login. In the above case, during login, a user will have to select the type of user from a dropdown list. That dropdown selection will be used to query to respective table to login to the system. Another alternative is to use a common user
table, which will store the user_id, email, password, last_login_date, last_login_ip
but will store other details in respective tables such as student, parent, teacher
. So, what is the preferred/correct way to implement it?
You don't model GRADE_SECTIONS at all.
Unless your school has a massive programmr of demolition and construction every summer holiday the classrooms will be the same. It is the assignments which change each year. So CLASSROOMS should be assigned to a separate GRADE_SECTION entity, instead of merging SECTIONS and CLASSROOMS as you do now.
Students should be assigned to GRADE_SECTIONS not CLASSROOMS.
COURSES should have many EXAMS rather than many EXAM_RESULTS. It simply doesn't make sense that a French Exam could be taken by students learning Maths and Russian.