Question

Question

1. Execute the join of the two relations and verify that there are spurious false.

2. Do the same for all 3 possible decompositions given above.

3. Verify that the last partition of the attributes (D3) does not produce spurious tuples and only D1, and D2 produce spurious tuples.

Problem on Join SQL Functional dependencies and decomposition

Two FDs exist in the relation

TEACH(student,course,instructor):

– fd1: { student, course} → instructor

– fd2: instructor → course

• {student, course} is a candidate key for this relation

Three possible decompositions for relation TEACH

– D1: {student, instructor} and {student, course}

– D2: {course, instructor} and {course, student}

– D3: {instructor, course} and {instructor, student}

TEACH Student Course Instructor Database Mark Navathe Narayan Smith Smith Smith Wallace Database Operating Systems Theory Database Operating Systems Database Database Operating Systems Wallace Ammar Schulman Mark Ahamad Omiecinski Navathe Ammar Wong Zelaya Narayan

Answer 1

2. TEACH (Student, course, instructor) . and we have two functional dependencies given. over TEACH table. fal: student courses instructore fd 2: instructor Course Three possible decomposition for TECH relation are given! DI: { Student, instructory and {student Courses D2: { course, instructory and course, student} D3: hinstructor courses and {instructor, student} Spurious & It Join of two relation gives false tuples there they are spurious. DI: { Student, insmouctory and student, course} R2. Relation R, and R2 has common attribute Student Student (t) Now, we check that student is a Candidate key or not in any of the table R, Orr, (Student) t = {studenty so student is not a candidare key in ktable R, or R2. so Join of R, and R₂ is Spurious false. . D2 : { course, Instructory and { course student? R2 RI Relation R, and R2 has Common attribute course.

Now, we check Course is a Candidate key or not an ether of Relation R, or R₂ (Course) = lourse} so; Course is not a Candidate key for elther Rior R2 : So, Join of R, and R₂ is spurious false. D3 : { instructor , course 4 and {onstructor, student} - Ri - R₂ Instructor is common attribute In R, and R2 Now, we check Instructor is a Candidate key or not in R, or R2.. (Instructor)t = {Instructor courses .so, Instructor 25 Candidate key for R, so, Join of R, and R2 is not spurious. 37 BD3:4 instructor, course y and constructor, student} R To check st 33 produce spurious tuple or not, we have to Join R, and Ra on Common attribute which is either primary key in R, or R₂. So, their Join produce non spunous tuple. Instructor is common attribute in R, and Rai Now, we check Instructor is a Candidate key or not in R, or R2. (Instructor) + = { Instructor, Lourse} 'So, Instructor is a candidate key to ki so, decomposition D3 does not produce spurious tuple.

DI: & student, instructory and student courses Student is common attribute for R, and R₂ we check for the Candidate key of student. (student) t = { Student} Student is not a Candidate key so Deo in either Ror R2 . So, Decomposition B of produce Spurious tuple. D2 : { Course, Instructors and he course, student} I RI Course is common attribute in R and Rz. with we check for the Candidate key of course (Course) t = { course}. So, course is not a Candidate key either in Rior R2. So Decomposition de D2 produce spurious tuple. R2