Description
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 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.
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 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.
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 performance 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’.
