W4111 Database Systems, Section 1 Spring 2020
Homework 2, due 3/4/2020.
1. Considerthefollowingdatabaseschemadescribingstudentsenrolledinsectionsofclassesatauniversity.
• student(sid*, sname, sex, age, year, gpa) [Year is a number between 1 and 5].
• dept(dname*, numphds)
• prof(pname*, dname)
• course(cno*, cname, dname*) [Course numbers are unique within departments.] • major(dname*, sid*)
• section(dname*, cno*, sectno*, pname) [Associates an instructor to a section.] • enroll(sid*, grade, dname*, cno*, sectno*) [Associates students to sections.]
Attributes that form part of the key are listed with a “*”; the “*” is not part of the actual attribute name. You should execute the queries below by going to one of the following URLs: http://w4111p1.cs.columbia.edu/ or http://w4111p2.cs.columbia.edu/.
Write the following queries in SQL. We do not care if your answer contains duplicate rows or not, unless we specify otherwise. There must be exactly one SQL query per question; the use of temporary tables is not allowed for any query. Note that the queries are not listed in order of difficulty.
(a) Print the sid of students majoring in ”Civil Engineering” who have enrolled in at least one course offered by the ”Chemical Engineering” department.
(b) For each course with less than 12 students enrolled, print the course number, department name, enrollment and average grade. Assume that the enrollment of a course is the sum of the enrollment of all its sections.
(c) For each department with more than 15 students majoring in the department, we want to print information about the student(s) with the highest gpa within the department. In particular, for each such student, we want to print the student id, student name and gpa, and the department name the student is major in.
(d) We want to find popular courses offered within each department. Print the department name, course number, and course enrollment of each course that has an enrollment of at least 10% higher than the average enrollment of all courses offered by the same department.
(e) Find all courses whose titles start with the word “Advanced”.
(f) How many different student ages are there in the database?
(g) Give the names of each professor who has in one of his/her classes a student with a gpa of at least 4.0. List each professor at most once.
(h) Show all majors together with the total number of students in the major. Order the output in decreasing order of popularity.
(i) For sections with fewer than 15 students, output the course number, section number and average grade.
(j) Find all information about students who have not declared a major.
(k) Give the names of departments that have either more than fifteen majors, or fewer than five 5th year students.
(l) Find all pairs of different students that are enrolled in two or more sections together. Show just the sids, and don’t repeat pairs of sids in the output.
(m) For each department, list (in a single record) the total number of enrollments in the department, the total number of enrollments from students majoring in that department, and the total number of enrollments from students majoring in other departments. Hint: use the SQL CASE statement in the SELECT clause.
1
(n) How many students have taken courses from either “Computer Sciences” or “Sanitary Engineer- ing”? (Make sure you don’t count a student more than once.)
(o) Find pairs of student names and department names such that the given student has taken every course offered by that department. Don’t list a student/department pair more than once. Hint. This is a tricky query, which will need nesting.
2. The following questions relate to the same schema as above, but do not require execution through the SQL interface.
(a) Write an SQL assertion statement to check the constraint that no section of a course can have an enrollment over 100.
(b) Professors may teach courses outside of their own departments. Write an SQL assertion statement to check the constraint that every professor must teach at least one course from their own department.
(c) Suppose that the table “prof” has an additional attribute “salary”. Write a trigger that adds $1 to a professor’s salary each time a new student signs up for one of his/her classes, and a second trigger that subtracts $1 when a student drops such a class.
(d) Because of resource constraints, two sections of a class are offered only if the total enrollment of both sections is over 20. Write a constraint that checks that for each course having exactly two sections, the total enrollment in the two sections of that course is at least 20. Explain why enforcing such a constraint might not work as intended in practice.
(e) For the directory of classes, we want to show the total enrollment for each section, but not the individual students enrolled. Create a view that provides this information.
(f) Suppose that the database system has access to an identifier (think of something like Columbia’s UNI) that comes from the same domain as the sid field for students. Suppose this identifier is available in the system variable $LOGIN which can be used within SQL statements. Create a view that defines the section and course information for the currently logged-in student.
(g) Imagine we have three user roles: Administrators (who manage enrollments), students, and communications (who manage the directory of classes). Describe a suitable set of table permissions (specified via grant statements) for each role. (Include the views from the previous questions.)
Submission Instructions
As mentioned above, you should execute the queries in question 1 by going to one of the following URLs: http://w4111p1.cs.columbia.edu/ or http://w4111p2.cs.columbia.edu/.
For each query in question 1, paste both the query text and the output of the database server for your query into your homework document. The homework document should be submitted using the dropbox on courseworks.
2