Implementing View in database relasional

Implementing View in database relasional

• 1.
  Implementing ViewRSL 1 ImplementingView Database by: Rosni Lumbantoruan
• 2.
  Content • Introduction toViews • Advantages of Views • Defining Views • Modifying Data Through Views
• 3.
  Content • Introduction to Views •Advantages of Views • Defining Views • Modifying Data Through Views Introduction to Views  A View is a Virtual Table or stored query which consists of a subset of columns from one or more tables.  Views are called virtual tables because the result set of a view is not usually saved in the database.  i.e. View is an object that derives its data from one or more tables (Base tables or Underlying Tables).
• 4.
  Content • Introduction to Views •Advantages of Views • Defining Views • Modifying Data Through Views Advantages of Views  To focus on specific data  Focus on important or appropriate data only.  Limiting access to sensitive data  Increase the security of data because user can only see the data in the view, not in the base table.  To simplify data manipulation  Defining frequently used query as views  Defining complex query as views or Join columns from multiple tables so that they look like a single table  To Simplify Management of User Permissions  To export and import data  To combine partitioned data (from multiple tables or multiple sources)  Aggregate information instead of supplying details.
• 5.
  Content • Introduction to Views •Advantages of Views • Defining Views • Modifying Data Through Views Defining Views CREATE VIEW view_name [WITH ENCRYPTION] AS select_statement [ WITH CHECK OPTION ] A view can reference another view. A view can reference a maximum of 1,024 columns.
• 6.
  Content • Introduction to Views •Advantages of Views • Defining Views • Modifying Data Through Views Create View-Example EmployeeView Lastname Firstname Davolio Fuller Leverling Nancy Andrew Janet Employees EmployeeID LastName Firstname Title 1 2 3 Davolio Fuller Leverling Nancy Andrew Janet ~~~ ~~~ ~~~ User’s View USE Northwind GO CREATE VIEW EmployeeView AS SELECT LastName, Firstname FROM Employees
• 7.
  Content • Introduction to Views •Advantages of Views • Defining Views • Modifying Data Through Views Create View-Example of Join Tables OrderID 10663 10827 10427 10451 10515 CustomerID BONAP BONAP PICCO QUICK QUICK ~~~ ~~~ ~~~ ~~~ ~~~ RequiredDate 1997-09-24 1998-01-26 1997-02-24 1997-03-05 1997-05-07 ShippedDate 1997-10-03 1998-02-06 1997-03-03 1997-03-12 1997-05-23 Orders Customers ShipStatusView USE Northwind GO CREATE VIEW ShipStatusView AS SELECT OrderID, RequiredDate, ShippedDate, ContactName FROM Customers c INNER JOIN Orders o ON c.CustomerID = O.CustomerID WHERE RequiredDate < ShippedDate * Select * from ShipStatusView CustomerID BONAP PICCO QUICK CompanyName Bon app' Piccolo und mehr QUICK-Stop ContactName Laurence Lebihan Georg Pipps Horst Kloss OrderID 10264 10271 10280 1996-08-21 1996-08-29 1996-09-11 ShippedDate 1996-08-23 1996-08-30 1996-09-12 ContactName Laurence Lebihan Georg Pipps Horst Kloss
• 8.
  Content • Introduction to Views •Advantages of Views • Defining Views • Modifying Data Through Views Create View - Restrictions  There are a few restrictions on the SELECT clauses in a view definition. A CREATE VIEW statement CANNOT:  Include COMPUTE or COMPUTE BY clauses.  Include ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement.  Include the INTO keyword.  Reference a temporary table or a table variable.  Select statement can use multiple SELECT statements separated by UNION or UNION ALL.  Functions can be used in the select_statement.
• 9.
  Content • Introduction to Views •Advantages of Views • Defining Views • Modifying Data Through Views Altering and Dropping Views  Altering Views - Retains assigned permissions - Causes new SELECT statement and options to replace existing definition  Dropping Views USE Northwind GO ALTER VIEW EmployeeView AS SELECT LastName, FirstName, Extension FROM Employees DROP VIEW ShipStatusView
• 10.
  Content • Introduction to Views •Advantages of Views • Defining Views • Modifying Data Through Views Locating View Definition Information  Locating View Definitions sp_helptext ‘View_Name’  Locating View Dependencies - Lists objects upon which view depends sp_depends ‘View_Name’
• 11.
  Content • Introduction to Views •Advantages of Views • Defining Views • Modifying Data Through Views Hiding View Definitions  Use the WITH ENCRYPTION Option USE Northwind GO CREATE VIEW customers_view WITH ENCRYPTION AS SELECT customerid,companyname FROM customers GO sp_helptext ‘customers_view’ The object comments have been encrypted.
• 12.
  Content • Introduction to Views •Advantages of Views • Defining Views • Modifying Data Through Views With Check Option CREATE VIEW v_stud WITH ENCRYPTION AS SELECT * FROM student WHERE age BETWEEN 18 AND 20 WITH CHECK OPTION • However, the WITH CHECK OPTION clause forces all data modification statements executed against the view to follow the criteria set within the SELECT statement defining the view. • If you use this clause, rows cannot be modified in a way that causes them to disobey the CHECK option defined in the view. • Any modification that would cause this to happen is cancelled and an error is displayed.
• 13.
  Content • Introduction to Views •Advantages of Views • Defining Views • Modifying Data Through Views With Check Option (cont…) insert into svc values('1111119','abc','Male',21,'Delhi') Server: Msg 550, Level 16, State 1, Line 1 The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint. The statement has been terminated. CREATE VIEW v_stud WITH ENCRYPTION AS SELECT * FROM student WHERE age BETWEEN 18 AND 20 WITH CHECK OPTION
• 14.
  Content • Introduction to Views •Advantages of Views • Defining Views • Modifying Data Through Views Modifying Data Through Views  Only one of its base tables can be modified.  No columns with aggregate functions, based on computed values, and based on built-in functions.  All the columns in the underlying table that are being updated and do not allow null values have values specified in either the INSERT statement or DEFAULT definitions. This ensures that all the columns in the underlying table that require values have them.  The data modified in the columns in the underlying table must adhere to the restrictions on those columns, such as nullability, constraints, DEFAULT definitions and so on. For example, if a row is deleted, all the underlying FOREIGN KEY constraints in related tables must still be satisfied for the delete to succeed.  All data modification statement must adhere to the criteria set within the SELECT statement if WITH CHECK OPTION clause is used.
• 15.
  Content • Introduction to Views •Advantages of Views • Defining Views • Modifying Data Through Views Examples CREATE VIEW titleview AS SELECT title, au_ord, au_lname, price, ytd_sales, pub_id FROM authors AS a JOIN titleauthor AS ta ON (a.au_id = ta.au_id) JOIN titles AS t ON (t.title_id = ta.title_id) SELECT * FROM titleview CREATE VIEW Cust_titleview AS SELECT title, au_lname, price, pub_id FROM titleview -- Increase the prices for publisher '0736' by 10%. UPDATE titleview SET price = price * 1.10 WHERE pub_id = '0736' GO
• 16.
  Content • Introduction to Views •Advantages of Views • Defining Views • Modifying Data Through Views Adding Data Through Views e.g.:  CREATE TABLE T1 (column_1 int, column_2 varchar(30)) GO  CREATE VIEW V1 AS SELECT column_2, column_1 FROM T1 GO  INSERT INTO V1 VALUES ('Row 1',1)  SELECT * FROM T1
• 17.
  Content • Introduction to Views •Advantages of Views • Defining Views • Modifying Data Through Views Database System Concepts, 6th Ed. ©Silberschatz, Korth and Sudarshan