Consider the relation schema CLASS with attributes Student, Subject, and Teacher. The meaning of this relation is that the specified student is taught the specified subject by the specified teacher. Assume that semantic rules, depicted by the following functional dependencies, exist:
{Student, Subject} †’ Teacher
Teacher †’ Subject
Subject --/--> Teacher
{Student, Teacher} †’ Subject
a. What do these rules mean in words? Are all these rules necessary? If not, explain which are not needed.
b. Is the following sample data consistent with these rules? Why or why not?

c. What causes CLASS to contain a BCNF violation? What anomalies does it exhibit?
d. Decompose CLASS if necessary so that the resulting relational schema is in BCNF. Is your design attribute-preserving, dependency-preserving, and a lossless-join decomposition? Explain.

Transcribed Image Text:

Student Smith Smith Jones Jones Subject Math Physics Math Teacher White Green White Brown PhysiCS