W4111留学生讲解、辅导Database Systems、SQL编程语言调试、SQL辅导 辅导Python编程|讲解SPSS
- 首页 >> Java编程 W4111 Database Systems, Section 1
Spring 2020
Homework 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 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 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 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
Spring 2020
Homework 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 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 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 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