Student scheduling query...

  • Thread starter Thread starter Jamie Risk
  • Start date Start date
J

Jamie Risk

I've got four tables that make up a classroom schedule.

How can I design a query for each student that shows their class schedule
with teachers name, room, subject and all the other students in that class?

- Jamie

CREATE TABLE Teachers (Teach_ID Autonumbered Primary Key, Name, Surname)
CREATE TABLE Students (Student_ID Autonumbered Primary Key, Name, Surname)
CREATE TABLE Class (Class_ID Autonumbered Primary Key, Room, Time, Subject,
Teach_ID)
CREATE TABLE Attendance (Att_ID Autonumbered Primary Key, Student_ID,
Class_ID)

The Class has a compound key on the fields Teach_ID, Time and Room and a
compound key on Time and Room so that a teacher can't appear in two places
at once and the room can't be used for two different subjects at the same
time. Also, the Attendance table has a compound index on the Student_ID and
Class_ID so the student isn't scheduled twice for same class.
 
Back
Top