Assignment #3 Solution

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  the homework but do not post answers.

Feel free to use private  posts or come to the office hours.

 

 

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.

 

• Contact  the TAs if you are having technical  difficulties in submitting the assignment;

attempt to submit  well in advance of the due date/time.

 

• The homework must be submitted in pdf  format.  Scanned handwritten and/or hand- drawn 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 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  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 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’.