联系方式

  • QQ:99515681
  • 邮箱:99515681@qq.com
  • 工作时间:8:00-23:00
  • 微信:codinghelp

您当前位置:首页 >> Database作业Database作业

日期:2020-03-01 08:31

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


版权所有:留学生编程辅导网 2020 All Rights Reserved 联系方式:QQ:99515681 微信:codinghelp 电子信箱:99515681@qq.com
免责声明:本站部分内容从网络整理而来,只供参考!如有版权问题可联系本站删除。 站长地图

python代写
微信客服:codinghelp