Takes Relation D course id sec id semester year grade 00128 CS-101 1 Fall 2017 A 00128 CS-347 Fall 2017 A 12345 CS-101 1 Fall 2017 C 12345 CS-190 2 Spring 2017 A 12345 CS-315 1 Spring 2018 A 12345 CS-347 1 Fall 2017 A 19991 HIS-351 Spring 2018 B 23121 FIN-201 Spring 2018 C+ 44553 PHY-101 Fall 2017 B- 45678 CS-101 1 Fall 2017 F 45678 CS-101 1 Spring 2018 B+ 45678 CS-319 Spring 2018 B 54321 CS-101 Fall 2017 A 54321 CS-190 2 Spring 2017 B+ 55739 MU-199 1 Spring 2018 A 76543 CS-101 1 Fall 2017 A 76543 CS-319 2 Spring 2018 A 76653 EE-181 1 Spring 2017 C 98765 CS-101 Fall 2017 C 98765 CS-315 Spring 2018 B 98988 BIO-101 Summer 2017 A 98988 BIO-301 Summer 2018 null Database System Concepts-7th Edition 4.7 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 4.7 ©Silberschatz, Korth and Sudarshan th Edition Takes Relation
student natural join takes D name dept name tot cred course id sec id semester year grade 00128 Zhang Comp.Sci. 102 CS-101 1 Fall 2017 A 00128 Zhang Comp.Sci. 102 CS-347 Fall 2017 A 12345 Shankar Comp.Sci. 32 CS-101 Fall 2017 C 12345 Shankar Comp.Sci. 32 CS-190 2 Spring 2017 A 12345 Shankar Comp.Sci. 32 CS-315 Spring 2018 A 12345 Shankar Comp.Sci. 32 CS-347 Fall 2017 A 19991 Brandt History 80 HIS-351 Spring 2018 B 23121 Chavez Finance 110 FIN-201 Spring 2018 C+ 44553 Peltier Physics 56 PHY-101 Fall 2017 B 45678 Levy Physics 46 CS-101 Fall 2017 F 45678 Levy Physics 46 CS-101 Spring 2018 B+ 45678 Levy Physics 46 CS-319 Spring 2018 B 54321 Williams Comp.Sci. 54 CS-101 Fall 2017 A 54321 Williams Comp.Sci. 54 CS-190 2 Spring 2017 B+ 55739 Sanchez Music 38 MU-199 1 Spring 2018 A 76543 Brown Comp.Sci. 58 CS-101 1 Fall 2017 A 76543 Brown Comp.Sci. 58 CS-319 2 Spring 2018 A 76653 Aoi Elec.Eng. 60 EE-181 Spring 2017 C 98765 Bourikas Elec.Eng. 98 CS-101 Fall 2017 C 98765 Bourikas Elec.Eng. 98 CS-315 Spring 2018 B 98988 Tanaka Biology 120 BIO-101 Summer 2017 98988 Tanaka Biology 120 BIO-301 Summer 2018 null Database System Concepts-7th Edition 4.8 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 4.8 ©Silberschatz, Korth and Sudarshan th Edition student natural join takes
Dangerous in Natural Join Beware of unrelated attributes with same name which get equated incorrectly Example --List the names of students instructors along with the titles of courses that they have taken ·Correct version select name,title from student natural join takes,course where takes.course_id course.course_id; ·Incorrect version select name,title from student natural join takes natural join course; This query omits all(student name,course title)pairs where the student takes a course in a department other than the student's own department. The correct version(above),correctly outputs such pairs. Database System Concepts-7th Edition 4.9 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 4.9 ©Silberschatz, Korth and Sudarshan th Edition Dangerous in Natural Join ▪ Beware of unrelated attributes with same name which get equated incorrectly ▪ Example -- List the names of students instructors along with the titles of courses that they have taken • Correct version select name, title from student natural join takes, course where takes.course_id = course.course_id; • Incorrect version select name, title from student natural join takes natural join course; ▪ This query omits all (student name, course title) pairs where the student takes a course in a department other than the student's own department. ▪ The correct version (above), correctly outputs such pairs
Outer Join An extension of the join operation that avoids loss of information. ■ Computes the join and then adds tuples form one relation that does not match tuples in the other relation to the result of the join. ■Uses null values. Three forms of outer join: ·left outer join ·right outer join ·full outer join Database System Concepts-7th Edition 4.13 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 4.13 ©Silberschatz, Korth and Sudarshan th Edition Outer Join ▪ An extension of the join operation that avoids loss of information. ▪ Computes the join and then adds tuples form one relation that does not match tuples in the other relation to the result of the join. ▪ Uses null values. ▪ Three forms of outer join: • left outer join • right outer join • full outer join
Outer Join Examples ■Relation course course id title dept name credits BIO-301 Genetics Biology 4 CS-190 Game Design Comp.Sci. 4 CS-315 Robotics Comp.Sci. 3 Relation prereg course id prereq id BIO-301 BIC-101 CS-190 CS-101 CS-347 CS-101 ■Observe that course information is missing CS-347 prereg information is missing CS-315 Database System Concepts-7th Edition 4.14 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 4.14 ©Silberschatz, Korth and Sudarshan th Edition Outer Join Examples ▪ Relation course ▪ Relation prereq ▪ Observe that course information is missing CS-347 prereq information is missing CS-315