Your cart is currently empty!
General Instructions 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. Please use Piazza if you have questions about…
General Instructions
Feel free to use private posts or come to the office hours.
Homework Submission
attempt to submit well in advance of the due date/time.
1 Single-Relation Queries (30 pts)
Graph(n1 , n2)
A tuple (n1, n2) in Graph stores a directed edge from a node n1 to a node n2 in the corresponding 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.
Trained (student, master, year)
A tuple (S, M, Y) in Trained specifies that a SQL Master M trained student S who graduated 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.
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)
3 Database Manipulation and Views (25 pts)
4 Constraints and Triggers (25 pts)
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.
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’.