Designing a Student Database
A school wants a simple database to track students, classes, and enrolments. Each student has a unique ID, a name, and a grade level. Each class has a unique code, a title, and a teacher. A student may enrol in many classes, and each class may contain many students. An enrolment also records the date the student joined the class. The school has 600 students and 40 classes. The IT teacher asks the students to design the tables, choose keys, and explain how to answer a query: "List all students in class MTH12 who joined after 1 September."
1. Propose a set of tables with primary and foreign keys, and explain why a separate enrolment table is needed.
Read the worked answer
Tables:
- Student(StudentID PK, Name, GradeLevel)
- Class(ClassCode PK, Title, Teacher)
- Enrolment(StudentID FK → Student, ClassCode FK → Class, JoinDate), with primary key (StudentID, ClassCode). The enrolment table is needed because the relationship is many-to-many: one student takes many classes and one class has many students. A relational table cannot store a list in a single cell without violating normal form. The composite key (StudentID, ClassCode) prevents duplicate enrolments, and JoinDate is an attribute of the relationship (it depends on the pair, not on the student or class alone), so it belongs in Enrolment.
2. Write the query in pseudocode or SQL to list students in class MTH12 who joined after 1 September.
Read the worked answer
SQL: SELECT s.Name FROM Student s JOIN Enrolment e ON s.StudentID = e.StudentID WHERE e.ClassCode = 'MTH12' AND e.JoinDate > '2024-09-01'; Pseudocode: for each enrolment where ClassCode = MTH12 and JoinDate > 1 Sept, look up the StudentID in Student and output the Name. The join is on StudentID; the filter uses both ClassCode and JoinDate, which live in the Enrolment table.
3. A class can have only one teacher, but a teacher may teach several classes. Does your design handle this correctly? Explain.
Read the worked answer
Yes. Teacher is stored as an attribute of Class, so each class has exactly one teacher value. A teacher teaching several classes simply appears in several Class rows - that is a one-to-many relationship from Teacher to Class, represented by the Teacher field in Class. No separate table is strictly required unless teacher details (contact, department) must be stored, in which case a Teacher(TeacherID PK, ...) table with Class.TeacherID as FK would be cleaner to avoid repeating teacher data. The current design is correct for the stated requirement but would duplicate teacher information if more teacher attributes were added.