Joined Relations-Examples course inner join prereg on course.course id prereg.course id course id title dept name credits prereg id course id BIO-301 Genetics Biology 4 BIO-101 BIO-301 CS-190 Game Design Comp.Sci. 4 CS-101 CS-190 What is the difference between the above,and a natural join? course left outerjoin prereg on course.course_id prereg.course_id course id title dept name credits prereg id course id BIO-301 Genetics Biology 4 BIO-101 BIO-301 CS-190 Game Design Comp.Sci. 4 CS-101 CS-190 CS-315 Robotics Comp.Sci. 3 null null Database System Concepts-7th Edition 4.20 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 4.20 ©Silberschatz, Korth and Sudarshan th Edition Joined Relations – Examples ▪ course inner join prereqon course.course_id = prereq.course_id ▪ What is the difference between the above, and a natural join? ▪ course left outer join prereqon course.course_id = prereq.course_id
Joined Relations-Examples course natural right outerjoin prereq course id title dept name credits prereg id BIO-301 Genetics Biology 4 BIO-101 CS-190 Game Design Comp.Sci. 4 CS-101 CS347 null null null CS-101 course full outer join prereq using(course_id) course id title dept name credits prereg_id BIO-301 Genetics Biology 4 BIO-101 CS190 Game Design Comp.Sci. 4 CS-101 CS-315 Robotics Comp.Sci. 3 null CS-347 null null null CS-101 Database System Concepts-7th Edition 4.21 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 4.21 ©Silberschatz, Korth and Sudarshan th Edition Joined Relations – Examples ▪ course natural right outer join prereq ▪ course full outer join prerequsing (course_id)
Views In some cases,it is not desirable for all users to see the entire logical model(that is,all the actual relations stored in the database.) ■ Consider a person who needs to know an instructors name and department,but not the salary.This person should see a relation described,in SQL,by select ID,name,dept name from instructor A view provides a mechanism to hide certain data from the view of certain users. Any relation that is not of the conceptual model but is made visible to a user as a "virtual relation is called a view. Database System Concepts-7th Edition 4.22 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 4.22 ©Silberschatz, Korth and Sudarshan th Edition Views ▪ In some cases, it is not desirable for all users to see the entire logical model (that is, all the actual relations stored in the database.) ▪ Consider a person who needs to know an instructors name and department, but not the salary. This person should see a relation described, in SQL, by select ID, name, dept_name from instructor ▪ A view provides a mechanism to hide certain data from the view of certain users. ▪ Any relation that is not of the conceptual model but is made visible to a user as a “virtual relation” is called a view
View Definition A view is defined using the create view statement which has the form create view v as query expression where <query expression>is any legal SQL expression.The view name is represented by v. Once a view is defined.the view name can be used to refer to the virtual relation that the view generates. ■ View definition is not the same as creating a new relation by evaluating the query expression Rather,a view definition causes the saving of an expression;the expression is substituted into queries using the view. Database System Concepts-7th Edition 4.23 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 4.23 ©Silberschatz, Korth and Sudarshan th Edition View Definition ▪ A view is defined using the create view statement which has the form create view v as < query expression > where <query expression> is any legal SQL expression. The view name is represented by v. ▪ Once a view is defined, the view name can be used to refer to the virtual relation that the view generates. ▪ View definition is not the same as creating a new relation by evaluating the query expression • Rather, a view definition causes the saving of an expression; the expression is substituted into queries using the view