SISTEM BASIS DATA II

Intermediate SQL

Join Expressions 

A) Views 

B) Transactions 

C) Integrity Constraints

D) SQL Data Types and Schemas 

E) Authorization

Joined Relations

1) Join operations take two relations and return as a result another relation

2)It also specifies the attributes that are present in the result of the join.

 3)The join operations are typically used as subquery expressions in the from clause.

 Joined Operation

 - Example 

1.Relation course

2.Relation prereq 

3.Observe that prereq information is missing for CS-315 and course information is missing for CS-437.

Outer Join

-An extension of the join operation that avoids loss of information

-Uses null values. 

Left Outer Join

Course natural left outer join prereq

Right Outer Join

 course natural right outer join prereq 

Joined Relation

 Join operations take two relations and return as a result another relation

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

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. 

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'; 

 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'; 

Defined Using Other Views

 - One view may be used in the expression defining another view 

- A view relation v1 is said to depend directly on a view relation v2 if v2 is used in the expression defining v1.

- A view relation v1 is said to depend on view relation v2 if either v1 depends directly to v2 or there is a path of dependencies from v1 to v2.

- A view relation v is said to be recursive if it depends on itself. cSilberschatz, Database System Concepts -6th Edition 4.18 Korth and Sudarshan View Expansion.

 - A way to define the meaning of views defined in terms of other views