Database normalization for School Management System

WebNovice picture WebNovice · Nov 3, 2011 · Viewed 62.4k times · Source

I am creating system for a school management system and come up with the attached database schema.

Database diagram

Following is how the system works:

  1. A school has many students and teachers. It has also many courses(subjects) taught. A grade level can have many courses assigned. These courses in turn will be assigned to the students in that particular grade.
  2. The levels of students are categorized into grades and sections. A student can be in Grade 5, but if grade 5 students are huge in number, they are divided into sections. eg: Grade 5 section A, Grade 5 section B.
  3. Students are placed in unique classrooms. A classroom will be unique throughout. Classroom of Grade 5 Section A of year 2010 will be different from Classroom of Grade 5 Section A of year 2011.
  4. Students are assigned parents. A parent can have more than one student in the school.
  5. One or more classrooms can be assigned to a teacher.
  6. Attendance for students are taken by their teacher on a daily basis
  7. There will be many types of exams. Exam results are stored for each subject (course).

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?

Answer

APC picture APC · Nov 7, 2011

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.