Description
Question 1. Write the following queries using relational algebra using any operator that you wish:
(a) Return the RIN of all students who missed a homework that was due during their opt-in period.
Return the gid of the corresponding missed homeworks. Remember if there is no opt-out date,
all homeworks after opt-in date are required.
(b) Find the RIN, first and last name of all students who had the highest grades for an exam (i.e.
gtype ’exam’ or ’finalexam’). Also return the gid and label of the exams they got the
highest grades in.
1
Question 2. For each of the following new relations:
(1) list all the relevant functional dependencies based on the explanations below,
(2) find all keys based on your functional dependencies,
(3) discuss whether the relation is in BCNF (Boyce-Codd Normal Form) or not, explain why or
why not.
(4) discuss whether the relation is in 3NF (Boyce-Codd Normal Form) or not, explain why or why
not.
(a) The system keeps track of multiple submissions for the same homework gradable like submitty
in a relation called submissions:
submissions(gid, rin, filename, attemptno, submission datetime, isactive, totalruntime)
Each student, gradable and specific attempt corresponds to a specific filename. Each filename
corresponds to a specific student, gradable and attempt. For each filename, there is a specific
submission datetime, isactive value and totalruntime value.
(b) Homeworks, quizzes and exams have individual questions. We will store the details of grades
of each part separately using a relation called grade details:
grade details(rin, gid, partno, topic, maxpoints, pointsearned)
For each gradable (gid) and part, there is a maxpoints value. For each gradable, part and
student, there is pointsearned. Each gradable part may have multiple topics.
Question 3. Given the following relation, functional dependencies and decomposition, answer the
following questions:
Relation R(A, B, C, D, E, F) with F = {AB → F, BD → C, CE → F, F → D}
Decomposition: R1(A, B, D), R2(A, B, C, E), R3(B, D, E, F)
(a) Is this decomposition lossless? Show yes or no using Chase decomposition.
(b) Is this decomposition dependency preserving? Show your work.
Note: two show that two sets of functional dependencies, F1 and F2 are equivalent, it is sufficient
to show that (1) all functional dependencies in F1 are implied by F2, and (2) all all functional
dependencies in F2 are implied by F1.
Question 4. Given the following relation, use BCNF decomposition to convert it to relations in
BCNF.
R(A, B, C, D, E) F = {AB → C, C → E}
Question 5. Given the following relation, use 3NF decomposition to convert it to relations in
BCNF. For each resulting relation, check if it is also in BCNF.
R(A, B, C, D, E, F, G) F = {AB → C, CD → EF, CF → AG}
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 18 the
latest.
2
