Given the database description and set of clerifications, answer question 4. Dat

Question

Given the database description and set of clerifications, answer question 4.

Database Description. Suppose you are given the following database for keeping track of grades in this course students(rin, fname, lname, email, optindate, 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 output!)

Explanation / Answer

Answer)

Q 4a) For each student return the RIN, name, id, grade, points.

select examgrades.rin, examgrades.name, examgrades.id, examgrades.grade, exams.points from examgrades inner join exams on (examgrades.name = exams.name) and (examgrades.id = exams.id);

Here we joined the 2 tables examgrades and exams to show the RIN, name, id, grade, points by inner joining them with name and id.

Q 4b) Find RIN, Exam 1 grade of students who did not complete homework before Exam 1.

SELECT examgrades.rin, examgrades.grade from

FROM ((examgrades

INNER JOIN hwgrades ON examgrades.rin = hwgrades.rin)

INNER JOIN hws ON hwgrades.id = hws.id)

where examgrades.id=1 and hwgrades.submission_date > (

select exams.exam_date from exams where id=1

);

We have performed inner join on the 3 tables - examgrades, hwgrades, and hws and then filtered out the RIN and grades of students who did not complete homework before Exam 1.

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.