Database SQL Queries and Schema

This document presents a database schema and a set of SQL query problems. The schema includes tables for users, courses, enrollments, materials, assignments, groups, submissions, and submitted files. The SQL queries are designed to retrieve information such as teachers with the most course access, courses that assess performance purely by examination, students who have not submitted assignments, and the number of non-submissions per assignment. The document provides a detailed structure for a database system and practical SQL query exercises.

Data Schema (Note that primary keys are boldfaced)
// userType to indicate whether the user is an administrator, a student, or a teacher
User(userID, password, email, userType)
Course(courseID, courseTitle)
// course rooms that users can access; note that users include all sorts of users such as teachers
// and administrators
Enroll(userID, courseID)
// Teacher’s userID is recorded when a piece of learning material is uploaded to a particular course room
// so that update right to the material can be confined to the concerned teacher. For simplicity,
// materials are assumed to be presented in text no larger than 64KB.
Material(materialID, materialText, userID, courseID)
// Teacher’s userID is recorded when an assignment is uploaded to a particular course room so that
// update right to the assignment can be confined to the concerned teacher. For simplicity, assignments
// are assumed to be presented in text no larger than 64KB.
Assignment(assignmentID, assignmentText, submissionDeadline, userID, courseID)
// For ease of processing, groups are still created for individual assignments. In such cases, each group is
// composed of one member only.
GroupMember(groupID, userID)
// For each assignment, one or more groups are created.
Group(groupID, assignmentID)
// Each submission is associated with an assignment and submitted by a student group member. Once a
// submission is made, any change to the submission is restricted to that group member. If a submission
// is removed, any group member can upload a new submission.
Submission(submissionID, assignmentID, groupID, submissionDate, userID)
// Each submission is composed of one or more files.
SubmittedFile(fileID, filePath, filename, submissionID)
SQL Queries
You are asked to implement SQL queries to address the following needs.
1. Find three teachers who have access to most number of course rooms. Should there be a tie
break, choose the ones with smaller user IDs. List the user ID and email in the output.
(2 marks)
2. Find the courses that assess student performance purely by examination. List the course title
in the output.

(2 marks)
3. Find the students who should submit their work for the assignment with assignmentID equal
to “1023” but they actually do not. List their user ID and email in the output.
(3 marks)
4. For each course, list the assignment ID, and number of students failed to submit that
assignment. The output should include course title, assignment ID, number of non-
submission(s).
(3 marks)