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-6th Edition 4.12 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 6 4.12 ©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 vas 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-6th Edition 4.13 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 4.13 ©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
Example Views A view of instructors without their salary create view faculty as select ID,name,dept name from instructor Find all instructors in the Biology department select name from faculty where dept_name ='Biology' Create a view of department salary totals create view departments_total salary(dept name,total salary)as select dept_name,sum (salary) from instructor group by dept name; Database System Concepts-6th Edition 4.14 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 4.14 ©Silberschatz, Korth and Sudarshan th Edition Example Views A view of instructors without their salary create view faculty as select ID, name, dept_name from instructor Find all instructors in the Biology department select name from faculty where dept_name = ‘Biology’ Create a view of department salary totals create view departments_total_salary(dept_name, total_salary) as select dept_name, sum (salary) from instructor group by dept_name;
Views Defined Using Other Views create view physics_fall_2009 as select course.course id,sec id,building,room number from course,section where course.course id=section.course id and course.dept_name='Physics' and section.semester='Fall' and section.year='2009'; create view physics fall 2009_watson as select course_id,room_number from physics_fall_2009 where building='Watson'; Database System Concepts-6th Edition 4.15 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 4.15 ©Silberschatz, Korth and Sudarshan th Edition Views Defined Using Other Views create view physics_fall_2009 as select course.course_id, sec_id, building, room_number from course, section where course.course_id = section.course_id and course.dept_name = ’Physics’ and section.semester = ’Fall’ and section.year = ’2009’; create view physics_fall_2009_watson as select course_id, room_number from physics_fall_2009 where building= ’Watson’;
View Expansion Expand use of a view in a query/another view create view physics_fall_2009_watson as (select course id,room_number from(select course.course id,building,room_number from course,section where course.course id section.course id and course.dept_name ='Physics' and section.semester='Fall' and section.year='2009) where building='Watson'; Database System Concepts-6th Edition 4.16 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 4.16 ©Silberschatz, Korth and Sudarshan th Edition View Expansion Expand use of a view in a query/another view create view physics_fall_2009_watson as (select course_id, room_number from (select course.course_id, building, room_number from course, section where course.course_id = section.course_id and course.dept_name = ’Physics’ and section.semester = ’Fall’ and section.year = ’2009’) where building= ’Watson’;