CODEWITHSUNDEEP
phpmysqldatabase-design
Neliswa Astute
Neliswa Astute

Reputation: 71

Implementing a report card generation system

I want to create a system in php and mysql , that is supposed to calculate student grades and display the results, such that the school can print out a report card.

When a teacher logs onto the system, he/she will only see the students he/she teaches

I already have three tables in the database (teachers table, students table, subjects table).

A teacher teaches more than one subject and a teacher teaches more than one class.

How can I implement that in the database?

Upvotes: 1

Views: 1191

Answers (1)

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

This question is not really appropriate for StackOverflow, but I will try to get you started by providing a first draft for your tables:

Teacher

TeacherId INT NOT NULL Primary key Autogenerated
FirstName VARCHAR(100)
LastName VARCHAR(100)

Student

StudentId INT NOT NULL Primary key Autogenerated
FirstName VARCHAR(100)
LastName VARCHAR(100)

Subject

SubjectId INT NOT NULL Primary key Autogenerated
SubjectName VARCHAR(100)

TeacherXSubject (association between teachers and subjects)

TeacherXSubjectId INT NOT NULL Primary key Autogenerated
TeacherId INT NOT NULL FK -> Teacher
SubjectId INT NOT NULL FK -> Subject

an unique key should be placed on (TeacherId, SubjectId)

Class

ClassId INT NOT NULL Primary key Autogenerated
ClassName VARCHAR(100)

TeacherXClass (classes one teacher may teach)

TeacherXClassId INT NOT NULL Primary key Autogenerated
TeacherId INT NOT NULL FK -> Teacher
ClassId INT NOT NULL FK -> Class

an unique key should be placed on (TeacherId, ClassId)

ClassXStudent (the classes a student may attend)

ClassXStudentId INT NOT NULL Primary key Autogenerated
ClassId INT NOT NULL FK -> Class
StudentId INT NOT NULL FK -> Student
Grade INT    -- type may depend on how the grade looks (numeric vs. A, B, ...)

For the first request: see the students he/she teaches

SELECT S.*
FROM Student S
   INNER JOIN ClassXStudent CxS ON CxS.StudentId = S.StudentId
   INNER JOIN TeacherXClass TxS ON TxS.ClassId = CxS.ClassId
WHERE T.TeacherId = <current teacher id>

Give it a try and come back with more targeted questions :).

Upvotes: 1

Related Questions