Description

• Feel free to talk to other members of the class in doing the homework. You should, however, write down your solutions yourself. List the names of everyone you worked with at the top of your submission.
• Keep your solutions brief and clear.
Homework Submission
• We DO NOT accept late homework submissions.
• We will be using Compass for collecting the homework assignments. Please submit your answers via Compass. Hard copies are not accepted.
• The homework must be submitted in pdf format. Scanned handwritten and/or handdrawn pictures in your documents won’t be accepted.
• Please do not zip the answer document (PDF) so that the graders can read it directly on Compass. You need to submit one answer document, named as hw3 netid.pdf.
• Please see the assignments page for more details. In particular, we will be announcing errata, if any, on this page.
1
2
1 Single-Relation Queries (30 pts)
1. [10] Consider the following relation:
Graph(n1, n2)
A tuple (n1, n2) in Graph stores a directed edge from a node n1 to a node n2 in thecorresponding graph. Your goal is to, for every node in the graph, count the number of outgoing edges of that node. Note that for nodes without any outgoing edges, their edge count would be zero; you need to output this as well.
You can assume that (1) there are no duplicates or null values in the table; and (2) every node in the graph is involved in at least one edge.
2. [10] Consider the following relation:
Trained(student, master, year)
A tuple (S, M, Y) in Trained specifies that a SQL Master M trained student S whograduated in year Y. Your goal is to find the count of SQL Masters who trained a student who graduated in the same year that ‘Alice’ or ‘Bob’ graduated.
3. [10] Consider the following relation:
DBMS(operator, system, performance)

A tuple (O, S, P) in DBMS specifies an operator O in system S and has the perfor-mance value P. Your goal is to find those systems whose operators achieves a higher performance value on average than the average performance value in a system named ‘PostgreSQL’.
2 Multi-Relation Queries (20 pts)
Consider the following relations representing student information at UIUC:
Mentorship(mentee sid, mentor_sid)
Study(sid, credits)
Enrollment(did, sid)
Student(sid, street, city)
• A tuple (M1, M2) in Mentorship specifies that M2 is a mentor of another student M1.
• A tuple (S, C) in Study specifies that the student S has taken C credits.
• A tuple in Enrollment (D, S) specifies that student S is enrolled in department D.
• A (ST, S, C) in Student specifies that student ST lives on street S in city C.
1. [10] Find all students who live in the same city and on the same street as their mentor.
2. [10] Find all students (sid) who have taken more credits than the average credits of all of the students of their department.
3
3 Database Manipulation and Views (25 pts)
1. [5] In the Study relation, insert a new student, whose id is 66666 and has 0 credits.
2. [5] In the Study relation, delete students who have graduated (i.e., the ones who have more than 200 credits).
3. [5] In the Study relation, add 2 credits for students who are mentors.
4. [10] Incoming students are those who have been accepted (i.e., exist in the Student relation) but have not registered in any department (i.e., do not exist in the Enrollment relation). Create a View that contains sid of all incoming students.
4 Constraints and Triggers (25 pts)
1. [10] Consider the following relation:
Payment(salary, bonus)
Write a schema-level assertion using the “CREATE ASSERTION” statement to ensure that no bonus is larger than the maximum salary in the Payment relation.
2. [15] Consider the following relation:
Study(sid, major, GPA)
Write a trigger T1 that increases the GPA by 10% for those students who transform their major from any Non-CS major to ‘CS’.