How to implement many-to-many relation between three table?

Question

I have these tables

StudentInfo:

  subjectId(primary key)
  name
  location

SubjectInfo:

  subjectId(primary key)
  subjectName
  totalmarks

TeacherInfo:

  teacherId(primary key)
  teacherName

Grades:

  studentId(foreign key from studentInfo)
  subjectId(foreign key from subjectInfo)
  marks
  attendence 

Junctiontable:

  studentId(foreign key from studentInfo)
  subjectId(foreign key from subjectInfo)
  professorId(foreign key from professorInfo)

There is a many-to-many relation between student, professor, subject.

That is

1. a student can have many subject to enroll with and can be taught by many

2. a teacher can teach many subject and many student

3. a subject can be enroll by many student and can be taught by many teachers

Questions:

1. Is that junction table correct or should I have 2 junction tables?

2. What is the SQL query to find the report for student on the basis of roll no? The detail in report should be subjectname, subjectmarks, subject, attendence, professor name who taught this subject

The query that I tried returns redundant data.

select 
    teacherInfo.teacherName,
    grade.marks,
    grades.attendence,
    subjectinfo.subjectName   
from 
    teacherinfo 
join 
    junctiontable on junctiontable.teacherId = teacherInfo.teacherId
join 
    studentinfo on studentinfo.studentId = junction.studentId
join 
    grades on grades.studentId = studentinfo.studentId 
where 
    studentinfo.studentId = 1;

Answer 1

A teacher can teach the same class several times in the same day and many times over years. A student can re-take a class. But, a teacher can only teach 1 class at a time and a student can only take one class at a time. I would add class instance and class enrollment tables as your junction tables. Your initial attempt isn't that far off from this:

1.studentInfo
  studentId(primary key)
  name
  location

2.subjectInfo
  subjectId(primary key)
  subjectName
  totalmarks

3.teacherInfo
  teacherId(primary key)
  teacherName

4.classInstance
  classId(primary key)
  subjectId(foreign key from subjectInfo)
  teacherId(foreign key from teacherInfo)
  year, semester, time, building, room nr, etc

5.classEnrollment
  studentId(foreign key from studentInfo)
  classId(foreign key from classInstance)
  marks
  attendance 

Answer 2

Hmmm,

I would modify you system in this simple manner :

Assumming several teachers, teaching the same subject to a given subject can give hime a grade, you could have only 4 tables :

• student(studentID PK, ....)
• teacher(teacherID PK, ....)
• subject(subjectID PK, ....)
• grade ((student_ID, teacher_ID, subjectID) PK, grade, attendence, ...)

Hence all you queries will occur, mostly, on the grade table. From that table, you can, conceptually, accept the below assumtions :

• several teacher can teach a given student an give him a grade for a given subject
• a teacher can teach several subject ; hence he can give grades to students
• a student can enroll is sevaral subjects ; hence he can get grades from several teachers in the same or different subjects

Really, really simplified modeling here.... But I think you WON'T get redundancy with it. Correct me if I'm wrong though.