Views

1
Views
2
Views

• A view is an SQL query which is permanently
  stored in the database with a given name
• To the user, the view is a virtual table. It
  consists of a set of data rows and columns
• In Microsoft Access, a saved SQL SELECT query is
  a view - queries can be based on tables and/or
  other queries
• Data are NOT stored in views it is just an
  illusion
• Views can be used to
• restrict access to data (some rows/columns may be
  confidential)
• simplify access to data (store common queries as
  views present data in natural structure)
• customise the look-and-feel of the database

3
Processing of Views

• A reference to a view in an SQL statement is
  treated by the DBMS by locating the view
  definition and translating the view into an
  equivalent reference to the source tables
• Simple views are handled on the fly
• Complex views may need to be materialised the
  underlying query is executed and its results are
  stored in a temporary space
• Transactions based on views appear to the user
  just as though they are transactions based on
  tables
• Note Views are not yet supported in MySQL

4
Views - Advantages

• Security
• users within the organisation may be granted
  access to data on a need-to-know basis
• thus, security may be defined at record-level
  (rows) and/or field-level (columns)
• example confidential employee data may be
  required by both personnel (e.g qualifications,
  home contact details, performance appraisal) and
  by payroll (e.g salary, bank details, special
  deductions), - but both are concerned with quite
  separate aspects, hence create two different
  views
• Simplicity
• a query which draws its data from many tables may
  be implemented as a view - instead of having to
  enter a complex SQL command, the user need only
  state
• SELECT FROM viewname

5
Views - Advantages

• Structure
• the way in which data is represented in a
  relational a database can be some distance
  removed from the natural real-world structures
  with which users are familiar - for example, data
  from a simple business document such as an
  invoice is typically normalised into two or more
  tables
• views present data in a way that is meaningful to
  the end-user
• Adaptability
• if applications are based on view definitions,
  they are better insulated from any structural
  changes that may over time be implemented upon
  underlying source tables

6
Views - Disadvantages

• Performance
• views appear to the user as though they are
  tables, but in actuality are queries which need
  to be parsed - if the query is complex and
  consists of a number of joins, the transaction
  may take longer to complete
• Updates, Deletions, and Insertions
• UPDATE, DELETE and INSERT operations performed
  upon a view are translated into equivalent
  operations against source tables and then
  executed
• depending on the structure of the view, it may be
  restricted to read-only (SELECT operations)

7
View Definition

• Horizontal Views Restrict access to limited set
  of rows. Example Create a view of employees
  which is restricted to those working in the
  Accounts department
• CREATE VIEW ACCOUNTS_STAFF AS
• SELECT FROM EMP
• WHERE DEPT_CODE 'ACC'
• Vertical Views Restrict access to limited set of
  columns. Example Create a view of employees to
  be used by payroll which lists just the RSI
  Number, employee name, and net pay of all
  employees
• CREATE VIEW PAYROLL_INFO AS
• SELECT RSI_NUM, EMP_NAME, NET_PAY
• FROM EMP

8
View Interrogation

• We can then interrogate these views as those they
  were tables ...

SQLgt SELECT RSI_NUM, EMP_NAME, DEPT_CODE FROM
ACCOUNTS_STAFF RSI_NUM EMP_NAME
DEPT_CODE --------- -----
----------------- ------------------
5664362B Tom Moran ACC 9746241S Aoife Lall
y ACC 6859357F Sean Moore ACC
9
View Interrogation
SQLgt SELECT FROM PAYROLL_INFO EMP_NAME RSI_NU
M NET_PAY ----------------- ------------
-------------- Bob Smyth 1089675D 388.00 Ted
Lyons 5235745J 486.00 Mike Ryan 6545346E
388.00 Barbara White 8435323S 452.00 Mary
Reilly 9623461P 444.00 Linda Keane 1322343K
442.50 Tom Moran 5664362B 388.00 Louise
Beatty 1278538E 388.00 Tommy Byrne 1456342K
260.00 Yvonne O Connell 4324246J
402.75 Aoife Lally 9746241S 388.00 Sean
Moore 6859357F 412.00 Brian Smyth 8745924F
367.70 Peter Phelan 6787341J 388.00
10
View Definition

• Row/Column Subset Views Restrict access to
  limited set of rows and columns
• Example Create a view of listing barcode,
  description and reorder quantity of all items
  which need to be reordered.
• CREATE VIEW STOCKOUT AS
• SELECT BARCODE, DESCRIPTION, REORDER_QTY
• FROM STOCK
• WHERE QTY_ON_HAND lt MINIMUM_LEVEL
• SELECT FROM STOCKOUT
• BARCODE DESCRIPTION REORDER_QTY
• ------------------- -------------------- -------
  ----------------
• 875875796641 Ratchet 625
• 389811489021 Gadget 710
• 173297097028 Spade 750
• 448578793377 Shovel 695
• 685856198710 Crowbar 545

11
View Definition

• Joined Views simplify data access by combining
  tables
• Example Create a view listing names, employee
  numbers and salaries of all those who work in the
  marketing department
• CREATE VIEW MARKETING_PAYROLL AS
• SELECT EMP_NO, EMP_NAME, AMOUNT
• FROM EMP, SALARY, DEPT
• WHERE EMP.SALGRADE SALARY.SALGRADE
• AND EMP.DEPTCODE DEPT.DEPTCODE
• AND DEPTNAME 'Marketing'
• SELECT FROM MARKETING_PAYROLL
• EMP_NO EMP_NAME AMOUNT
• --------- --------------- ------------
• 5 Mary Reilly 48000
• 6 Linda Keane 16000
• 14 John Smith 48000
• 15 Brian Smyth 25000

12
View Definition

• Grouped Views used to summarise and aggregate
  data
• Example Create a view calculating Total Sales
  revenue for 2002 grouped by product.
• CREATE VIEW SALES_REVENUE (PRODUCT, REVENUE) AS
• SELECT PROD_DESCRIPTION, SUM(QTYUNIT_PRICE)
• FROM S_INVOICE, S_INV_LINE, STOCK
• WHERE S_INV_LINE.BARCODE STOCK.BARCODE
• AND S_INVOICE.S_INVOICE_NO S_INV_LINE.S_INVOICE_
  NO
• AND S_INVOICE_DATE BETWEEN '01-JAN-2002' AND
  '31-DEC-2002'
• GROUP BY PROD_DESCRIPTION
• SELECT FROM SALES_REVENUE
• PRODUCT REVENUE
• --------------- ---------
• Brace Pin 7089
• Crowbar 27900
• Pickaxe 26950
• Plate 166950
• Shovel 48015

13
View Definition

• In ORACLE SQL/Plus, it is possible to generate a
  list of all tables and views in the workspace by
  issuing the command
• SELECT TNAME, TABTYPE FROM TAB
• TNAME TABTYPE
• ------------------------------ -------------
• ACCOUNTS_STAFF VIEW
• DEPT TABLE
• EMP TABLE
• MARKETING_PAYROLL VIEW
• PAYROLL_INFO VIEW
• P_INVOICE TABLE
• P_INV_LINE TABLE
• SALARY TABLE
• SALES_REVENUE VIEW
• STOCK TABLE
• STOCKOUT VIEW
• SUPPLIER TABLE
• S_INVOICE TABLE
• S_INV_LINE TABLE

14
View Updates

• For a view to be updatable, it must satisfy the
  following criteria
• Duplicate rows are not eliminated
• SELECT DISTINCT is illegal
• in a UNION query, UNION ALL must be specified
• The view must specify a single table in the FROM
  clause
• The user must have the necessary access
  privileges
• No subqueries are included within the criteria
  specification
• No GROUP BY or HAVING clauses are permitted

15
Deleting Views

• In Microsoft Access, simply delete the query
  (Edit - Delete menu command)
• In ANSI/SQL, the DROP VIEW command is used
• DROP VIEW viewname
• If other views are based on a view, there are
  additional options
• DROP VIEW viewname RESTRICT
• DROP VIEW viewname CASCADE