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.
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.