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