Question 2: For each of the following new relations: (1) list all the relevant f

Question

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.

Explanation / Answer

(a). submissions(gid, rin, filename, attemptno, submission datetime, isactive, totalruntime)

1. The functional dependencies are :

filename -> submission datetime

filename -> isactive

filename -> totalruntime

filename -> gradable

filename -> attempt

filename -> rin

rin,gid,attempno -> filename

2. The keys based on functional dependencies are :

From the above functional dependencies, the attribute filename derives every other attribute. Therefore, filename is the key for the above relation.

3. Whether the relation is in BCNF :

No, the relation is not in BCNF. Because, a relation is said to be in BCNF if LHS of every functional dependency is a key.

Here, rin,gid,attempno -> filename violates this condition.

4. Whether the relation is in 3NF :

Yes, Since there are no transitive dependencies the relation is in 3NF.

(b). grade details(rin, gid, partno, topic, maxpoints, pointsearned)

1. The functional dependencies are :

gid,partno -> maxpoints

gid,partno,rin -> pointsearned

2. The keys based on the functional dependencies are :

Based on the functional dependencies, the key corresponding to the given relation is

(gid,partno,rin) as it gives pointsearned. Whereas (gid,partno) gives maxpoints value.

3. Whether the relation is in BCNF :

No, the relation is not in BCNF as it does not satisfy the 3NF definition. When the relation is not in 3NF, it is not in BCNF also.

4. Whether the relation is in 3NF :

NO, the relation has a partial dependency so it does not satisfy 2NF condition. So, the relation is not in 3NF also.

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

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