18. Consider the relation schema CLASS with attributes Student, Subject, and Tea
ID: 3820211 • Letter: 1
Question
18. 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 dependecies, exist:
{Student, Subject} -> Teacher
Teacher -> Subject
Subject -/->
{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 consisten with these rules? Why or why not? (SEE SAMPLE ABOVE)
c. What causes CLASS to contain a BCNF violation? What anomalies does it exhibit?
d. Decompose CLASS if necessary so that resulting relational schema is in BCNF. Is your design attribute-preserving, dependecy-preserving, and a lossless-join decomposition? Explain.
Normal Forms Based on Functional Dependencies 18. 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 depen- dencies, exist: (Student, Subject) Teacher Teacher Subject Subject Teacher Student, Teacher) Subject a. What do these rules mean in words? Are all the rules necessary? If not, explain which are not needed. b. Is the following sample data consistent with these rules? Why or why not? Student Subject Teacher Smith Math White Physics Smith Green Math White Jones Physics Brown Jones 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 decompo- sition? Explain.Explanation / Answer
a. Rules state the relationship between the attributes
{Student,Subject} -> Teacher
Description: 1 student can learn 1 subject from 1 teacher
Teacher -> Subject
Description: One teacher can teach only one subject
Subject -/-> Teacher
Description: One subject can be taught by multiple teachers, this is many to many relationship
{Student, Teacher} -> Subject
Description: One teacher can teach one student only one subject
b. Applying rules to all the rows.
Rule 1: {Student,Subject} -> Teacher
{Smith,Math} -> White
{Smith,Physics} -> Green
{Jones,Math} -> White
{Jones,Physics} -> Brown
Rule is followed
Rule 2: Teacher -> Subject
White -> Math
Green -> Physics
Brown -> Physics
Rule 2 is followed
Rule 3: Subject -/-> Teacher
Physics -> Green
Physics -> Brown
Hence, rule 3 is also followed
Rule 4: {Student, Teacher} -> Subject
{Smith,White} -> Math
{Smith,Green} -> Physics
{Jones,White} -> Math
{Jones,Brown} -> Physics
No row for combination of Student and teacher is repeating and hence rule 4 is also followed.
c. Any non-BCNF table follows the {AB C, C B} functional dependencies
As CLASS Table follows below rules, it violates BCNF
{Student, Subject} -> Teacher
Teacher -> Subject
d. To make it BCNF, we will decompose FD: {Student, Subject} -> Teacher
There will be two tables:
1. Student, Teacher
Candidate key: {Student,Teacher}
2. Teacher, Student
Candidate key: {Teacher}
There will be referential relationship between Table 1 and 2 on Teacher column.
To check if decomposition is lossless
{Student, Subject} -> Teacher FD is decomposed into {Student,Teacher} and Teacher -> Subject
R1 U R2 -> R (this is true)
R1 intersection R2 = Teacher (this is not equal to NULL)
Natural JOIN of both relations on Teacher column will give the original relation, without loss of any data. Hence the decomposition is lossless.
For dependency preserving decomposition,
Student->Teacher can be ensured in R1() and Teacher->Student can be ensured in R2(). Hence it is dependency preserving decomposition
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.