Database Description. Suppose you are given the following database for keeping t

Question

Database Description. Suppose you are given the following database for keeping track of grades in this course. The data model from Homework #1 is significantly simplified where all gradable items (hw,quiz, exams) are combined into a single relation. Similarly all grades are also combined into a single relation) students(rin, fname, lname, email, optin date, optout date) gradables(gid, gtype, label, given date, duedate, maxgrade, points, nextg-id) grades(rin, gid, 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 date 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 All gradable assignments are stored in gradables. The gtype is one of 'quiz', 'hw', 'exam' 'finalexam'. The label is the name given to the gradable as a string such as 'Hw 1' or 'Exam 2' If the gradable is a homework, we store the gid of the exam that it is directly before in nextg-id. All grades are stored in grades which stores the grade for each student. All date fields are formatted as mon-day-year, e.g. 01-31-2016

Explanation / Answer

(a).RIN of all students who missed a homework that was during their opt-in period

select s.rin  
from students s inner join grades gr on
s.rin = gr.rin inner join gradables gs on
gs.gid = gr.gid where
(s.optin_date is not null and s.optout_date is null and gs.given_date is null)
or
(s.optin_date is not null and s.optout_date is not null and gs.due_date between s.optin_date and s.optout_date and
gs.given_date > gs.due_date)

Return gid of the corresponding missed homeworks

select gs.gid  
from students s inner join grades gr on
s.rin = gr.rin inner join gradables gs on
gs.gid = gr.gid where
(s.optin_date is not null and s.optout_date is null and gs.given_date is null)
or
(s.optin_date is not null and s.optout_date is not null and gs.due_date between s.optin_date and s.optout_date and
gs.given_date > gs.due_date)

Some data is missing e.g.

1 => Can given_date be NULL?

2 => If the given_date is after due_date, will that be considered as missed. ?(I considered it as missed)

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

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