《数据库系统概念 Database System Concepts》原书教学资源（第七版，PPT课件讲稿，英文版）Chapter 4 Intermediate SQL

Outline ■Join Expressions ■Views ■Transactions Integrity Constraints SQL Data Types and Schemas Index Definition in SQL ■Authorization Database System Concepts-7th Edition 4.2 ©Silberscha乜，Korth and Sudarshan

Database System Concepts - 7 4.2 ©Silberschatz, Korth and Sudarshan th Edition Outline ▪ Join Expressions ▪ Views ▪ Transactions ▪ Integrity Constraints ▪ SQL Data Types and Schemas ▪ Index Definition in SQL ▪ Authorization

Joined Relations Join operations take two relations and return as a result another relation. A join operation is a Cartesian product which requires that tuples in the two relations match(under some condition).It also specifies the attributes that are present in the result of the join The join operations are typically used as subquery expressions in the from clause ■Three types of joins: ·Natural join 。Inner join Outer join Database System Concepts-7th Edition 4.3 ©Silberscha乜，Korth and Sudarshan

Database System Concepts - 7 4.3 ©Silberschatz, Korth and Sudarshan th Edition Joined Relations ▪ Join operations take two relations and return as a result another relation. ▪ A join operation is a Cartesian product which requires that tuples in the two relations match (under some condition). It also specifies the attributes that are present in the result of the join ▪ The join operations are typically used as subquery expressions in the from clause ▪ Three types of joins: • Natural join • Inner join • Outer join

Natural Join in SQL Natural join matches tuples with the same values for all common attributes,and retains only one copy of each common column. List the names of instructors along with the course Id of the courses that they taught select name,course id from students.takes where student.ID takes.ID: Same query in SQL with "natural join"construct select name,course_id from student natural join fakes; Database System Concepts-7th Edition 4.4 ©Silberscha乜，Korth and Sudarshan

Database System Concepts - 7 4.4 ©Silberschatz, Korth and Sudarshan th Edition Natural Join in SQL ▪ Natural join matches tuples with the same values for all common attributes, and retains only one copy of each common column. ▪ List the names of instructors along with the course ID of the courses that they taught • select name, course_id from students, takes where student.ID = takes.ID; ▪ Same query in SQL with “natural join” construct • select name, course_id from student natural join takes;

Natural Join in SQL (Cont.) The from clause can have multiple relations combined using natural join: select A1,A2,...An from r naturaljoin r2 naturaljoin..natural join r where P; Database System Concepts-7th Edition 4.5 @Silberschatz,Korth and Sudarshan

Database System Concepts - 7 4.5 ©Silberschatz, Korth and Sudarshan th Edition Natural Join in SQL (Cont.) ▪ The from clause can have multiple relations combined using natural join: select A1 , A2 , … An from r1 natural join r2 natural join .. natural join rn where P ;

Student Relation ID name dept name tot cred 00128 Z☑hang Comp.Sci. 102 12345 Shankar Comp.Sci. 32 19991 Brandt History 80 23121 Chavez Finance 110 44553 Peltier Physics 45678 Levy Physics 46 54321 Williams Comp.Sci. 55739 Sanchez Music 70557 Snow Physics 0 76543 Brown Comp.Sci. 76653 Aoi Elec.Eng. 0 98765 Bourikas Elec.Eng. 98 98988 Tanaka Biology 120 Database System Concepts-7th Edition 4.6 @Silberschatz,Korth and Sudarshan

Database System Concepts - 7 4.6 ©Silberschatz, Korth and Sudarshan th Edition Student Relation

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