1. (50 points) Consider the following relational schema:

   department ( name, chair )
   professor ( ssn, name, employer )
   student ( SID, name, dept )
   course ( name, offered_by )
   section ( course, number, taught_by, credits )
   enrollment ( course, number, student )
   

   that has the following foreign keys:

   department.chair → professor.ssn
   professor.employer → department.name
   student.dept → department.name
   course.offered_by → department.name
   section.course → course.name
   section.taught_by → professor.ssn
   enrollment.course → section.course
   enrollment.number → section.number
   enrollment.student  → student.SID
   

   Based on this schema, express the following queries in SQL (10 points for each query).
   1. print the total number of students who are enrolled in cse3330, section 1.
   2. for each department, print the department name and the total number of courses offered by this department.
   3. for each student, print her name and her total number of credits of all the courses she is currently enrolled.
   4. for each course offered by CSE, print the course name and the total number of students enrolled in this course.
   5. for each section, print the course name, the section number, and the total number of students enrolled in this section only if this number is greater than 20 students.

2. (50 points) Based on the relational schema given in Problem 1, express the following queries in relational algebra (10 points for each query).
   1. print the course names taught by professor John Smith.
   2. print the student names who are enrolled in cse3330, section 1.
   3. print the professor names who teach cse3330 but not cse2320.
   4. print the course names and section numbers enrolled by the student Mary Jones.
   5. print the course names and section numbers of all sections that are taught by a professor who is not employed by the department that offers this course.

How to Submit the Homework

You need to submit your homework solution online using the following web form. Acceptable document formats are: plain text, Microsoft word document, PDF, and postscript. You may submit it as many times as you like, but only the most recently submitted file will be retained and evaluated. Not that, this time we will not accept any email or hardcopy submission. If you cannot login or have a problem submitting the homework using this form, ask the GTA for help.