W4111 Database Systems, Section 1Spring 2020Homework 2, due 3/4/2020.1. Consider the following database schema describing students enrolled in sections of classes at a university.• 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 attributename. 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 temporarytables 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 courseoffered 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 enrollmentof all its sections.(c) For each department with more than 15 students majoring in the department, we want to printinformation about the student(s) with the highest gpa within the department. In particular, foreach such student, we want to print the student id, student name and gpa, and the departmentname 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% higherthan 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 least4.0. List each professor at most once.(h) Show all majors together with the total number of students in the major. Order the output indecreasing order of popularity.(i) For sections with fewer than 15 students, output the course number, section number and averagegrade.(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 5thyear students.(l) Find all pairs of different students that are enrolled in two or more sections together. Show justthe sids, and don’t repeat pairs of sids in the output.(m) For each department, list (in a single record) W4111留学生作业代做、代写Database Systems作业、SQL编程语言作业调试、SQL实验作业代写 代写Pythe total number of enrollments in the department,the total number of enrollments from students majoring in that department, and the total numberof enrollments from students majoring in other departments. Hint: use the SQL CASE statementin the SELECT clause.1(n) How many students have taken courses from either “Computer Sciences” or “Sanitary Engineering”?(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 everycourse 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 theSQL interface.(a) Write an SQL assertion statement to check the constraint that no section of a course can have anenrollment over 100.(b) Professors may teach courses outside of their own departments. Write an SQL assertion statementto check the constraint that every professor must teach at least one course from their owndepartment.(c) Suppose that the table “prof” has an additional attribute “salary”. Write a trigger that adds $1to a professor’s salary each time a new student signs up for one of his/her classes, and a secondtrigger 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 enrollmentof both sections is over 20. Write a constraint that checks that for each course having exactlytwo sections, the total enrollment in the two sections of that course is at least 20. Explain whyenforcing 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 theindividual 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’sUNI) that comes from the same domain as the sid field for students. Suppose this identifier isavailable in the system variable $LOGIN which can be used within SQL statements. Create a viewthat defines the section and course information for the currently logged-in student.(g) Imagine we have three user roles: Administrators (who manage enrollments), students, andcommunications (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 InstructionsAs 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 yourquery into your homework document. The homework document should be submitted using the dropbox oncourseworks.2转自：http://www.3zuoye.com/contents/15/4820.html