b) The database design has three tables to store the classes that students attend.
STUDENT (StudentID, FirstName, LastName, Year, TutorGroup)
CLASS (ClassID, Subject)
CLASS-GROUP (StudentID, ClassID)
Primary keys are not shown.
There is a one-to-many relationship between CLASS and CLASS-GROUP.
i) Describe how this relationship is implemented. [2]
ii) Describe the relationship between CLASS-GROUP and STUDENT. [1]
iii) Write an SQL script to display the StudentID and FirstName of all students who are in the tutor group 10B. Display the list in alphabetical order of LastName. [4]
iv) Write an SQL script to display the LastName of all students who attend the class whose ClassID is CS1. [4]
Answer (2)
i) In a database, a one-to-many relationship between two tables, such as CLASS and CLASS-GROUP, is implemented using a foreign key. In this setup, the CLASS table is the 'one' side, and the CLASS-GROUP table is the 'many' side. This means that each record in the CLASS table can be associated with multiple records in the CLASS-GROUP table. The CLASS-GROUP table includes a foreign key, which is the ClassID, referencing the primary key in the CLASS table to link several class-group entries to a single class entry.
ii) The relationship between CLASS-GROUP and STUDENT is also a one-to-many relationship. In this case, the CLASS-GROUP table acts as the 'many' side, and the STUDENT table is the 'one' side. Each student in the STUDENT table can be associated with multiple records in the CLASS-GROUP table through the StudentID, which acts as a foreign key in the CLASS-GROUP table.
iii) To display the StudentID and FirstName of all students who are in the tutor group 10B, you can use the following SQL script. The results will be sorted in alphabetical order by LastName:
SELECT StudentID, FirstName FROM STUDENT WHERE TutorGroup = '10B' ORDER BY LastName ASC;
iv) To display the LastName of all students who attend the class with ClassID CS1, you need to join the relevant tables and filter by the specific ClassID. Here is the SQL script:
SELECT s.LastName FROM STUDENT s JOIN CLASS-GROUP cg ON s.StudentID = cg.StudentID WHERE cg.ClassID = 'CS1';
These SQL queries help you retrieve specific data from the database designed to manage students and their class enrollments. Understanding the relationships between tables and how to query them is crucial for efficiently extracting meaningful information from databases.
The one-to-many relationship between CLASS and CLASS-GROUP is established using a foreign key in CLASS-GROUP that references the primary key in CLASS. CLASS-GROUP also has a one-to-many relationship with STUDENT through a foreign key linking StudentID. SQL scripts can be utilized to retrieve specific student information based on criteria like tutor group and class enrollment.
;
