Views

• Mostly from Connolly
• A view is a virtual table.
• The actual table does not exist, but can be created upon request.
  • Defined as a query on one or more relations.
  • Operations on views are translated into operations on the base table.
  • And operations on the base tables are reflected in the view.
• To create a view:
  • CREATE VIEW viewname AS select_statement.
  • Man Page
  • (Using cape codd)

  • CREATE VIEW OrderView AS 
       SELECT OrderNumber, ORDER_ITEM.SKU, SKU_Description, 
                 Quantity, Price, ExtendedPrice 
       FROM ORDER_ITEM, SKU_DATA 
       WHERE ORDER_ITEM.SKU = SKU_DATA.SKU;
    	

  • CREATE VIEW WarehouseView AS  
        SELECT WarehouseCity, WarehouseState, Manager, SKU, SKU_Description
        FROM WAREHOUSE, INVENTORY 
        WHERE WAREHOUSE.WarehouseID=INVENTORY.WarehouseID;
    	

• They appear as tables now
  • Show tables;
  • Select * from WarehouseView;
  • Drop view OrderView

  • Select * 
    FROM WarehouseView 
    WHERE Manager LIKE "%Rogers";
           

• Changes to the view/underlying table:

  • UPDATE WarehouseView
       SET Manager="Roy Rogers"
       WHERE Manager LIKE "%Rogers";
    	

  • Now select * from both WarehouseView and WAREHOUSE.

  • UPDATE WAREHOUSE
       SET Manager="Dale Rogers"
       WHERE Manager LIKE "%Rogers";
    	

  • Now select * from both WarehouseView and WAREHOUSE.
  • But more on this later.
• Why use views?
  • It can be used to hide portions of a database from some db users
    • Given access to a view, they can, for example, update an order but not mess with sku information.
    • Thy don't know about fields that are in a table, but not in the view they have access to.
    • We have not yet discussed security, but we can control access to lots of things.
    • The WaterSportsOrderReport below does this somewhat, it only shows low inventory data for water sports, not for all departments. It also drops the QuantityOnOrder field.
  • Provides access customized to user's needs.
    • Data can be seen in different ways by different users.

    • CREATE VIEW WarehouseInventoryView AS 
         SELECT WarehouseID, SKU, SKU_Description, QuantityOnHand 
         FROM INVENTORY 
         WHERE QuantityOnHand > 0 
         ORDER BY WarehouseID;
      
      	    

  • Simplify complex operations.
    • If the view is the result of a complex join.

    • CREATE VIEW WaterSportsOrderReport AS 
          SELECT WarehouseID, SKU_DATA.SKU, SKU_DATA.SKU_Description, 
                 QuantityOnHand, QuantityOnOrder 
          FROM INVENTORY, SKU_DATA 
          WHERE Department="Water Sports" 
               AND INVENTORY.SKU = SKU_DATA.SKU 
      	 AND QuantityOnHand <=100;
      	

    • The user may manipulate the view, which will manipulate the underlying tables.
  • Views should be used to provide the external model users find familiar
    • This views can be used to "un-normalize" the data.
    • Users can get the reports have been using all along.
    • Tables can be changed, split, updated with new columns and the user of the view doesn't need to know this.
    • Strikes me somewhat like an ADT.
• Like tables, views can be modified with an ALTER statement.
• View Updates
  • Can only be done when the DBMS can trace any row or column back to the row or column in the source table.
  • The ISO standard gives the following restrictions (Can't update if)
    • The DISTINCT option is used in creation, ie some rows are thrown away.
    • Every element of the SELECT list is a column name, and appears no more than once (IE no computations)
    • The from clause specifies only one table
    • The Where clause does not have nested selects
    • There is no GROUP BY or HAVING.
  • MYSQL is somewhat more relaxed.
• MYSQL has some rules for creating views on the man page.