A school stores a large amount of data. This includes student attendance, qualification, and contact details. The school’s software uses a file-based approach to store this data.
a) The school is considering changing to a DBMS.
i) State what DBMS stands for.
ii) Describe two ways in which the Database Administrator (DBA) could use the DBMS software to ensure the security of the student data.
iii) A feature of the DBMS software is a query processor.
Describe how the school secretary could use this software.
iv) The DBMS has replaced software that used a file-based approach with a relational database.
Describe how using a relational database has overcome the previous problems associated with a file-based approach.
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.
ii) Describe the relationship between CLASS-GROUP and STUDENT.
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.
iv) Write an SQL script to display the LastName of all students who attend the class whose ClassID is CS1.