CS 405G: Introduction to Database Systems

Lecture 13: SQL V Instructor: Chen Qian Slides 2-10 are for self-study and will not be covered by exams

Views

 A view is like a “virtual” table    Defined by a query, which describes how to compute the view contents on the fly DBMS stores the view definition query instead of view contents Can be used in queries just like a regular table 2 4/28/2020 Chen Qian @ Univ. of Kentucky

Creating and dropping views

 Example: CS647roster  CREATE VIEW CS647Roster AS SELECT SID, name, age, GPA Called “ base tables ” FROM Student WHERE SID IN (SELECT SID FROM Enroll WHERE CID = ’CS647’);  To drop a view  DROP VIEW

view_name

; 4/28/2020 Chen Qian @ Univ. of Kentucky 3

Using views in queries

 Example: find the average GPA of CS647 students  SELECT AVG(GPA) FROM CS647Roster ;   To process the query, replace the reference to the view by its definition SELECT AVG(GPA) FROM ( SELECT SID, name, age, GPA FROM Student WHERE SID IN (SELECT SID FROM Enroll WHERE CID = ’CS647’) ); 4/28/2020 Chen Qian @ Univ. of Kentucky 4

Why use views?

 To hide data from users  To hide complexity from users  Logical data independence   If applications deal with views, we can change the underlying schema without affecting applications Recall physical data independence : change the physical organization of data without affecting applications  To provide a uniform interface for different implementations or sources  Real database applications use tons of views 4/28/2020 Chen Qian @ Univ. of Kentucky 5

Modifying views

 Does not seem to make sense since views are virtual  But does make sense if that is how users see the database  Goal: modify the

base tables

such that the modification would appear to have been accomplished on the view  Be careful!

 There may be one way to modify   There may be many ways to modify There may be no way to modify 4/28/2020 Chen Qian @ Univ. of Kentucky 6

A simple case

CREATE VIEW StudentGPA AS SELECT SID, GPA FROM Student; DELETE FROM StudentGPA WHERE SID = 123; translates to: DELETE FROM Student WHERE SID = 123; 4/28/2020 Chen Qian @ Univ. of Kentucky 7

An impossible case

CREATE VIEW HighGPAStudent AS SELECT SID, GPA FROM Student WHERE GPA > 3.7; INSERT INTO HighGPAStudent VALUES(987, 2.5);  No matter what you do on

Student

, the inserted row will not be in

HighGPAStudent

4/28/2020 Chen Qian @ Univ. of Kentucky 8

A case with too many possibilities

CREATE VIEW AverageGPA(GPA) AS SELECT AVG(GPA) FROM Student;  Note that you can rename columns in view definition UPDATE AverageGPA SET GPA = 2.5;    Set everybody’s GPA to 2.5?

Adjust everybody’s GPA by the same amount?

Just lower Lisa’s GPA?

4/28/2020 Chen Qian @ Univ. of Kentucky 9

SQL92 updateable views

 More or less just single-table selection queries    No join No aggregation No subqueries  Arguably somewhat restrictive  Still might get it wrong in some cases   See the slide titled “An impossible case” Adding WITH CHECK OPTION to the end of the view definition will make DBMS reject such modifications 4/28/2020 Chen Qian @ Univ. of Kentucky 10

Summary of SQL features covered

 Query  Modification  Constraints  Triggers  Views 4/28/2020 Chen Qian @ Univ. of Kentucky 11

Exercise

 Consider the following relational schema and briefly answer the questions that follow:  Define a table constraint on Emp that will ensure that every employee makes at least $10,000.

4/28/2020 Chen Qian @ Univ. of Kentucky 12

Exercise

 Define a table constraint on Emp that will ensure that every employee makes at least $10,000.

4/28/2020 Chen Qian @ Univ. of Kentucky 13

Exercise

 Define a table constraint on Dept that will ensure that all managers have age > 30.

4/28/2020 Chen Qian @ Univ. of Kentucky 14

Exercise

 Print the names and ages of each employee who works in both the Hardware department and the Software department.

15 4/28/2020 Chen Qian @ Univ. of Kentucky

Exercise

 For each department with more than 20 full-time-equivalent employees (i.e., where the part-time and full-time employees add up to at least that many full-time employees. Each full-time employee time is counted as 100.), print the

did

together with the number of employees that work in that department.

16 4/28/2020 Chen Qian @ Univ. of Kentucky

Exercise

 Print the name of each employee whose salary exceeds the budget of all of the departments that he or she works in.

17 4/28/2020 Chen Qian @ Univ. of Kentucky

Exercise

 Find the enames of managers who manage the departments with the largest budgets.

4/28/2020 Chen Qian @ Univ. of Kentucky 18

Exercise

 If a manager manages more than one department, he or she controls the sum of all the budgets for those departments. Find the managerids of managers who control more than $5 million.

4/28/2020 Chen Qian @ Univ. of Kentucky 19

Exercise

 Find the managerids of managers who control the largest amounts.

4/28/2020 Chen Qian @ Univ. of Kentucky 20

Exercise

 Find the enames of managers who manage only departments with budgets larger than $1 million, but at least one department with budget less than $5 million.

4/28/2020 Chen Qian @ Univ. of Kentucky 21

More

 Project description  Mid-project report due 3/26 4/28/2020 Chen Qian @ Univ. of Kentucky 22