Database System Concepts, 6th Ed.Silberschatz, Korth and SudarshanSee www.db- for conditions on re-use Chapter 4: Intermediate SQLSilberschatz, Korth and Sudarshan4.2Database System Concepts - 6th EditionChapter 4: Intermediate SQLnJoin ExpressionsnViewsnTransactionsnIntegrity ConstraintsnSQL Data Types and SchemasnAuthorizationSilberschatz, Korth and Sudarshan4.3Database System Concepts - 6th EditionJoined RelationsnJoin operations take two relations and return as a result another relation.nA 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 nThe join operations are typically used as subquery expressions in the from clauseSilberschatz, Korth and Sudarshan4.4Database System Concepts - 6th EditionJoin operations ExamplenRelation coursenRelation prereqn Observe that prereq information is missing for CS-315 and course information is missing for CS-437Silberschatz, Korth and Sudarshan4.5Database System Concepts - 6th EditionOuter JoinnAn extension of the join operation that avoids loss of information.nComputes the join and then adds tuples form one relation that does not match tuples in the other relation to the result of the join. nUses null values.Silberschatz, Korth and Sudarshan4.6Database System Concepts - 6th EditionLeft Outer Joinn course natural left outer join prereqSilberschatz, Korth and Sudarshan4.7Database System Concepts - 6th EditionRight Outer Joinn course natural right outer join prereqSilberschatz, Korth and Sudarshan4.8Database System Concepts - 6th EditionJoined RelationsnJoin operations take two relations and return as a result another relation.nThese additional operations are typically used as subquery expressions in the from clausenJoin condition defines which tuples in the two relations match, and what attributes are present in the result of the join.nJoin type defines how tuples in each relation that do not match any tuple in the other relation (based on the join condition) are treated.Silberschatz, Korth and Sudarshan4.9Database System Concepts - 6th EditionFull Outer Joinn course natural full outer join prereqSilberschatz, Korth and Sudarshan4.10Database System Concepts - 6th EditionJoined Relations Examples ncourse inner join prereq oncourse.course_id = prereq.course_idnWhat is the difference between the above, and a natural join? ncourse left outer join prereq oncourse.course_id = prereq.course_idSilberschatz, Korth and Sudarshan4.11Database System Concepts - 6th EditionJoined Relations Examplesncourse natural right outer join prereqn course full outer join prereq using (course_id)Silberschatz, Korth and Sudarshan4.12Database System Concepts - 6th EditionViewsnIn 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.)nConsider 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 instructornA view provides a mechanism to hide certain data from the view of certain users. nAny relation that is not of the conceptual model but is made visible to a user as a “virtual relation” is called a view.Silberschatz, Korth and Sudarshan4.13Database System Concepts - 6th EditionView DefinitionnA view is defined using the create view statement which has the formcreate view v as where is any legal SQL expression. The view name is represented by v.nOnce a view is defined, the view name can be used to refer to the virtual relation that the view generates.nView definition is not the same as creating a new relation by evaluating the query expression lRather, a view definition causes the saving of an expression; the expression is substituted into queries using the view.Silberschatz, Korth and Sudarshan4.14Database System Concepts - 6th EditionExample ViewsnA view of instructors without their salary create view faculty as select ID, name, dept_name from instructornFind all instructors in the Biology department select name from faculty where dept_name = BiologynCreate 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; Silberschatz, Korth and Sudarshan4.15Database System Concepts - 6th EditionViews Defined Using Other Viewsncreate 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;ncreate view physics_fall_2009_watson as select course_id, room_number from physics_fall_2009 where building= Watson;Silberschatz, Korth and Sudarshan4.16Database System Concepts - 6th EditionView ExpansionnExpand use of a view in a query/another viewcreate view physics_fall_2009_watson as(select course_id, ro