Database Management Systems Chapter 5

1. SQL Database Management SystemsChapter 5

2. R1 Example Instances S1 • We will use these instances of the Sailors and Reserves relations in our examples. • If the key for the Reserves relation contained only the attributes sid and bid, how would the semantics differ? S2

3. View • A database view is a virtual table or logical table which is defined as a SQL Select Query. • A database view is dynamic because it is not related to the physical schema. When the data of the tables changes, the view reflects that changes as well. • Most database management systems, including MySQL, allow you to update data in the underlying tables through the database view with some prerequisites.

4. Advantage of Views • Simplify complex queries and hide the complexity of underlying tables to the end-users and external applications. • Limit data access to specific users - expose only non-sensitive data. • Provides extra security layer for a database management system - read-only view. • Enable computed columns. • Enables backward compatibility - create database views with the same schema as the legacy tables.

5. Disadvantage • Performance: querying data from a database view can be slow especially if the view is created based on other views. • Tables dependency: you create view based on underlying tables of the a database. Whenever you change the structure of those tables that view associates with, you have to change the view as well.

6. Create View Statement • Within a database, a view and a table cannot have the same name • CREATEVIEW [database_name].[view_name] • AS [SELECT  statement] CREATEVIEW ReservationPerSailor AS SELECT S.sid, S.sname, COUNT( *) as RCount FROM Reservation R, Sailor S WHERE R.sid = S.sid GROUPby S.sid ORDER BY S.sid DESC;

7. Create View with Subquery • The SELECT statement can contain a subquery in a WHERE clause, but not in FROM clause. CREATEVIEW aboveAvgSailor AS SELECT S2.sid, S2.sname, S2.rating FROM   Sailor S2 WHERE         S2.rating > (SELECT AVG(S1.rating) FROM Sailor S1) ORDER BY S2.rating DESC;

8. Query Against View • You can query a view as if it is a table. • You can create a view based on other views. • Queries against the views are executed in two ways: • A temporary table is created based on the view definition statement and executes the incoming query on this temporary table. • The incoming query with the query defined the view are combined into one query and executes the combined query.

9. Show View Definition • Create view AboveAvgStudents as • Select S2.sid, concat (S2.fname,' ', S2.lname) as StudentName, • S2.points/S2.credits as GPA • From Students S2 • Where S2.points/S2.credits > (select AVG(S1.points/S1.credits) from Students S1) • order by S2.sid; Show create view AboveAvgStudents;

10. Modify an Existing View ALTERVIEW [database_name].  [view_name]   AS [SELECT  statement] • Alter view AboveAvgStudents as • Select S2.sid, concat (S2.fname,' ', S2.lname) as StudentName, • TimeStampDiff(YEAR, S2.DOB, CurDate()) as Age, • S2.points/S2.credits as GPA • From Students S2 • Where S2.points/S2.credits > (select AVG(S1.points/S1.credits) from Students S1) • order by S2.sid;

11. Modified View

12. Create or Replace View • If a view already exists, MySQL simply modifies the view. • If the view does not exist, MySQL create a new view. CREATE OR REPLACEVIEW [database_name].  [view_name]   AS [SELECT  statement]

13. Drop a View • IF EXISTS clause allows you to check whether the view exists or not. DROPVIEW [IF EXISTS] [database_name].[view_name]

14. Create an Updatable View • Views are not only query-able but also updatable. • Using INSERT or UPDATE statement to insert or update rows of the base table through the updatable view. • Using DELETE statement to remove rows of the underlying table through the view. • To be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table.

15. Create view Sub_Students as Select fname as FirstName, lname as LastName, credits as Credits, points as Points From Students;

16. Update Sub_Students SET Credits = Credits + 1 WHERE Points > 340;

17. Insertable View • An updatable view is insertable if it also satisfies additional requirements • No duplicate view column names; • The view must contain all columns in the underlying table that do not have a default value. • The view columns must be simple column references. They must not be expressions or composite expression.

18. INSERT INTO Sub_Students values ('Lisa', 'Li', 100, 300);

19. Non-Updatable View • A view is not updatable if it contains any of the following • Aggregate functions such as MIN, MAX, SUM, AVG, COUNT, etc. • DISTINCT, GROUP BY, HAVING clause. • UNION or UNION ALL clause. • Subquery in the SELECT clause or in the WHERE clause that refers to the table appeared in the FROM clause. • Reference to non-updatable view in the FROM clause. • Reference only to literal values. • Multiple references to any column of the base table.

20. Update Mutli-table View • Only one single underlying table can be updated in a multi-table view definition. • SET Clause must name only columns from one of the tables in the view. Create View StudentsEnrollment As Select S1.fname as FirstName, S1.lname as LastName, S2.course_title as CourseTitle, E.grade as Grade From Students S1, Sessions S2, Enrolled E Where S1.sid = E.sid AND S2.call_num = E.call_num;

21. Update StudentsEnrollment SET LastName = concat(LastName,’A’) Where Grade = ‘A’;

23. Removing Rows Through View DELETE from Sub_Students WHERE Credits = 104;

25. WITH CHECK OPTION • When updatable view shows only partial data of the underlying table,  it is possible to update data which is not visible through the view. • To ensure data consistency of the view, WITH CHECK OPTION clause is used when you create the view. CREATEOR REPLACEVIEW view_name AS  select_statement  WITHCHECK OPTION;

26. CREATEOR REPLACEVIEW vps AS SELECT         employeeNumber,         lastname,         firstname,         jobtitle,         extension,         email,         officeCode,         reportsTo FROM         employees WHERE         jobTitle LIKE '%VP%';

27. Select employeeNumber, lastname, firstname, jobtitle From vps; INSERTINTO vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reportsTo) values(1703,'Lily','Bush','IT Manager','x9111','lilybush@classicmodelcars.com',1,1002);

28. SELECT     * FROM    employees ORDER BY employeeNumber DESC; This is not what we want to do through the view of “vps’

29. CREATEOR REPLACEVIEW vps AS SELECT         employeeNumber,         lastname,         firstname,         jobtitle,         extension,         email,         officeCode,         reportsTo FROM         employees WHERE         jobTitle LIKE '%VP%' WITHCHECK OPTION;

30. INSERTINTO vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reportsTo) values(1703,'Lily','Bush','IT Manager','x9111','lilybush@classicmodelcars.com',1,1002); INSERTINTO vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reportsTo) VALUES(1704,'John','Smith','SVP Marketing','x9112','johnsmith@classicmodelcars.com',1,1076); It works as expected.