This assignment practices developing Python code that uses a MySQL database.
The database is a university database consisting of students, professors, courses, sections, and enrollments. There is a list of courses each of which may have 1 or more sections. A student enrolls in a section which is taught by a professor. Assume that a student can only enroll in a course once. When the student completes the course, a grade is assigned that is used to calculate their overall GPA. The database schema is below:
student (sid, sname, sex, birthdate, gpa) prof (pname, dname) course (cnum, dname, cname) section (cnum, secnum, pname) enroll (sid, cnum, secnum, grade)
Download the starter code (Python file, Jupyter Notebook) and the test file (Python file, Jupyter Notebook). There is also a DDL script to create the database.
Write the code to complete the methods in EnrollDB (look for TODO items).
Your code will be graded based on it passing the unit tests. You can show the TA all unit tests passing to receive full marks. Otherwise, submit your source code online.
| Operation | Marks |
|---|---|
| List all students in the database (listAllStudents) | 1 mark |
| List all professors in a department (listDeptProfessors) | 2 marks |
| List all students in a course (listCourseStudents) | 2 marks |
| Compute student GPA (computeGPA) | 2 marks |
| Add a student (addStudent) | 2 marks |
| Delete a student (deleteStudent) (Make sure to also delete all courses enrolled in). | 2 marks |
| Update a student (updateStudent) | 2 marks |
| New student enrolment (newEnroll) | 2 marks |
| Update student GPA (updateStudentGPA) | 2 marks |
| Update student mark (updateStudentMark) | 2 marks |
| Remove student from section (removeStudentFromSection) | 2 marks |
| Query1: Return the list of students that have not been in any course section. Hint: Left join can be used instead of a subquery. | 3 marks |
| Query2: For each student return their id and name, number of course sections registered in (called numcourses), and gpa (average of grades). Return only students born after March 15, 1992. A student is also only in the result if their gpa is above 3.1 or registered in 0 courses. Order by GPA descending then student name ascending and show only the top 5. | 3 marks |
| Query3: For each course, return the number of sections (numsections), total number of students enrolled (numstudents), average grade (avggrade), and number of distinct professors who taught the course (numprofs). Only show courses in Chemistry or Computer Science department. Make sure to show courses even if they have no students. Do not show a course if there are no professors teaching that course. | 4 marks |
| Query4: Return the students who received a higher grade than their course section average in at least two courses. Order by number of courses higher than the average and only show top 5. | 4 marks |