School management System (Database)

[paperchazer]

Hi mates, i will appreciate your help on this work...i am learning php and mysql and i want to apply them in real life problem.

I am doing a school database management system, which keeps data of students: students.parents details, students.subjects offered, student.attendance per day, students.class and student.grade they belong to, subject: subjects.name, subjects.for different grades and specifi subjects.for each classes, teachers: teachers details, teachers.subjects they teach, teachers.classes they teach, exam records: exam.types, exam.type.score. wow and lots more if possible. really a complex database for me as a noob in database design.

I have been able to come up with a diagram of the database schema (not complete though) but i am still have some little problems linking the table with normalization in mind.

this is how far i have gone::banghead:

students{(id), (details),(parent_id)} parent_id as FK from parent

attendance {(student_id), (date),(status)}

parents {(id),(details)}

teachers {(id), (details),(class_id)} class_is as FK from class

subject {(id),(name)}

class {(id), (subject_id), (teacher_id)}

exam_types{(id), (name), (description)}

exam_record {(id), (student_id), (exam_type_id),(subject_id), (mark)}


How schooling system are in my country :

1. we have 6 years grading system...grade 1 to 6 but each grades have different classes (classroom)
e.g we can have Grade1 A, Grade1 B, Grade1 C and also for other grades.

i am not sure if i am right but i represented them on same table (class) so class::name can have Grade 1 A, Grade1 B, Grade1 C, Grade2 A.....Grade6 C.

2. teachers can teaches ONLY one subject but can teach as many grades as possible (note: not class but a whole grade, lets say a teacher of biology taking grade 3 and grade 6...he will take grade3 a, grade3 b, grade3 c and grade6 a, grade6 b, grade6 c)

3. subjects should represent the students and the subjects they choose to offer.

I think that is pretty much everything i am thinking of implementing for now.

any suggestion will be extremely appreciated, i realy want o get this project done so i can be presenting it as my project when invited for job interviews as i am currently not employed. thanks

 

[Mihail]

Looks good for me.
Maybe except this (as well as you have remarked):

i am not sure if i am right but i represented them on same table (class) so class::name can have Grade 1 A, Grade1 B, Grade1 C, Grade2 A.....Grade6 C.

I think that you need one more table here, for grades.
But you can wait for more answers because my English is not very good so it is possible to misunderstand something.
Good luck !

 

[paperchazer]

^Thank you, i seems to get your arguement and i think you are right, will try that out whilst wait for other comments, thanks

 

[Pat Hartman]

The relationship between students and parents (guardians) is many-to-many not 1-to-many as you currently have it modeled.

Remove ParentID from the student table and create a new table.

tblStudentGuardians
StudentID (PK fld1, fk to tblPeople)
GuardianID (PK fld2, fk to tblPeople)
RelationshipRole (mother, father, step father, etc.)

I would also combine all people - students, teachers, guardians into a single table. Add one flag to indicate that the person is a student and a second to indicate that he is a teacher. Teachers may also be students and so may parents depending on what kind of institution you are running. Guardianship is handled with the junction table described above.

 

[paperchazer]

Thanks, i will post that in now, appreciate you sir

 

[leedavid161]

Having a school management software can bring a whole new definition of an academic institution, providing a lot of benefits. Now-a- days, in this Internet era, such a system is inevitable for a school or a college to excel in their field.