Database normalization for School Management System

Question

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

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?

Solution

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.

Comments

[Tadeck]

Hey, students in your school can have only one parent? Rather strange :) Plus, why classroom is assigned only one teacher? Should not it be assigned to the course?

[WebNovice]

@Tadeck The parent table stores details of an authorized parent, who will be able to view and check the student records. It does not indicate all the parent details of the student. Eg: A father could be issued the parent username/password to check his student. The mother could also use it, but generally only one would be able to do it. As for the classroom, a classroom will only ONE assigned class teacher who fills in the attendance/grades of the students in that particular class.Course refers to Subjects a student studies. Grades will be assigned subjects & classrooms will be assigned grades.

[Pekka]

@Damchey be prepared that things like the 1-authorized-parent rule might change, and your app will be required to deal with it somehow. It might be a good idea to design this so that a student can have any number of authorized guardians - you can then limit the number to 1 on UI level

[Mike Sherrill 'Cat Recall']

". . . during login, a user will have to select the type of user from a dropdown list." In almost 30 years of IT work, I've never seen anything like that during login.

[WebNovice]

@Pekka Good point, maybe I can then have a parent_student relationship table with a status column which can be checked/unchecked by admin.

[WebNovice]

@Catcall I have come across systems where you have to select the type of user during login. On the login page before/after your username/password field, there will be a drop-down that will list the type of users. eg: administrator, editor, subscriber etc. Is it not a good idea to do it that way?

[Mike Sherrill 'Cat Recall']

@Damchey: No, it's not a good way. Login systems should be able to determine everything they need to know about a user without requiring them to tell you the truth about what "kind" of user they are.

[APC]

@Catcall - obviously you've never logged on to an Oracle database AS SYSDBA.

[Mike Sherrill 'Cat Recall']

@APC: I look at elevated privileges as a different issue, and not one I'd consider relevant to students. (A student should never be asked whether she wants to be logged in as a parent or a teacher.)

[APC]

@Damchey - a data model implements a set of business rules. You haven't posted your business rules. So while your data model apparently contains omissions (no teacher hierarchy, nothing regarding discipline) and odd relationships (students assigned to rooms not courses, exams which can cover multiple subjects) there is no way for us to say whether your data model is correct. So, voting to close as NOT CONSTRUCTIVE.

[APC]

@Catcall - I have worked with applications that allow users to swap roles and get different access rights. They were enterprise applications comprising multiple sub-systems. Now normally I would expect such applications to apply a default role when the user logs in. But, why not let the user decide? It's a design choice. Now whether such a mechanism is appropriate for a tiddly school system is a different question: I agree with you that it would be wrong in this scenario.

[WebNovice]

@APC Sorry, I have added the business rules or how the system works.

[Rob Paller]

With regards to you user table and the type of user why not introduce a Role entity (RoleID, RoleName, RoleDescription) and a UserRole entity (UserId, RoleID, EffectiveDate, ExpireDate). Consider the possibility that an adult in your school may be a teacher and a parent/guardian at the same time. Your Roles entity can account for a Primary Guardian, Alternate Guardian, Emergency Contact, Teacher, Administrator, Admin. Assistant, etc. Since the relationship between Users and Roles is M:M you can manage privileges in the system accordingly at the Role level instead of the individual level.