## 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