Question $2.$ The class management system database at PolyU is designed to efficiently manage and

schedule academic classes, professors, students, and classrooms. The database includes various entities and

relationships that facilitate the organization of course schedules, classroom assignments, and student

enrollments.

Relationships:

Course to Professor: One$-$to$-$One $($Each course is taught by one and only one professor$)$

Classroom to CourseSchedule: One$-$to$-$Many $($A classroom can host multiple courses at different

times$)$

Student to CourseEnrollment: Many$-$to$-$Many $($Students can enroll in multiple courses, and each

course can have multiple students$)$

Professor to Course: One$-$to$-$Many $($A professor can teach multiple courses$)$

Course to CourseSchedule: One$-$to$-$One $($Each course has one specific schedule$)$

Requirement: The class management system at PolyU is designed to efficiently handle course scheduling,

classroom assignments, and student enrollments. It facilitates professors in listing courses with detailed

schedules and allows students to enroll in their chosen courses easily. The system effectively manages

classroom resources by tracking and assigning spaces based on course needs and student capacity, ensuring

optimal utilization. This streamlined approach enhances academic planning and improves the educational

experience at PolyU.

Here are sample data for each table:

Classroom Table

Course Table

Professor Table

Student Table

CourseEnrollment Table

CourseSchedule Table

Please write SQL statements to answer the following queries:

$(1)$ List the total number of students in each department.

$(2)$ Find the classroom with the smallest capacity that has been scheduled for a course.

$(3)$ Find the names of all students enrolled in "Advanced Mathematics."

$(4)$ Find the name of the course with the highest number of enrolled students.

$(5)$ Display the names of professors along with the number of different courses they teach, sorted by the

number of courses in descending order.