SOLVED CSCI 4380-01 Homework # 1

Database Description. Suppose you are given the following database for keeping track of grades
in this course.
students(rin, fname, lname, email, optin date, optout date)
exams(name, id, exam date, maxgrade, points)
examgrades(rin, name, id, grade)
hws(id, name, points, given date, due date, maxgrade, nextexam name, nextexam id)
hwgrades(rin, id, submission date, grade)
Each student may have an opt-in date, if there is no date (i.e. the value is NULL) then the
homeworks are optional for this student. If there is an opt-in and no opt-out date, then homeworks
are required. If there are dates for both opt-in and opt-out, only the homeworks that have a due
date within within the given dates are required.
The exams relation contains both quizzes and exams, stored in name, id fields as ’Exam’, 1,
’Exam’, 2, ’Exam’, 3 (for final), or ’Quiz’, 1, ’Quiz’, 2, etc. The id is used to show which
exam comes after another.
The grade for each exam references both the name and the id of the exam for each student and
their grade.
Homeworks are stored similarly in the hws relation with id 1,2,3, etc. For each homework, the
upcoming (midterm or final) exam is listed. As discussed, points for skipped homeworks will count
towards the next exam. The name for each homework is ’hw’ for simplicity.
The grades for each homework is stored similarly in hwgrades where id is the id of the homework.
Note: All date fields are formatted as mon-day-year, e.g. 01-31-2016. You can assume that you
can check if a date value X comes after another value Y by checking whether X > Y.
Write the following queries using relational algebra (pay attention to the attributes required in the
output!):
Question 1. The following queries only need a single SELECT (σ), followed by a PROJECT (π)
and RENAMING (ρ) as necessary:
(a) Return the first and last name of all students who have opted in to the homeworks before
’9/25/2018’ and did not opt out.
(b) Return the id and points for all homeworks that are due before ’Exam’, 2.
1
Question 2. The following queries combine SELECT (σ), SET operations (∩, ∪, −), PROJECTION (π) and RENAMING (ρ) as necessary:
(a) Find and return the name, id and points of all exams and homeworks given or had due dates
before 
(b) Find the id of homeworks that no student has submitted.
(c) Find and return the RIN of all students who turned in both homeworks 1 and 2.
Question 3. The following queries combine SELECT (σ) statements with any number of JOINS
as neede (./, theta or natural) (or CARTESIAN PRODUCT), followed by a PROJECT (π) and
RENAMING (ρ) as necessary:
(a) Find the first and last name of all students who submitted homework #1.
(b) Find the RIN and Exam 1 grades of all students who submitted at least one homework due
before Exam 1.
Question 4. Freeform, you decide which combination is needed. Any relational algebra operator is
fine. Remember to construct these in parts and provide comments on what each part is computing.
This will make it possible for us to give partial credit.
(a) For each student, return their RIN, name, exam/homework name, exam/homework id, grade
and points for all exams (including quizzes) and homeworks that they have a grade for.
(b) Find and return the RIN and Exam 1 grade of the students who did not complete any of the
homeworks before Exam 1.
If you are finished with all these queries but find yourself in need of a personal challenge, try to write
this query to explore the expressive power of relational algebra (no hw credit for this question):
Find and return the RIN of all students who have completed all the assigned homeworks. Also try
to find students who completed two homeworks in a row. (No credit for these questions, try them
to challenge yourself.)
SUBMISSION INSTRUCTIONS. Submit a PDF document for this homework using Gradescope. No other format and no hand written homeworks please. No late submissions will be
allowed.
The gradescope for homework submissions will become available by Tuesday September 11 the
latest. We will announce it on Piazza.
2