Views

• Our author has very little to say about views.
• So this is from Widom and Ullman.
• And technical details are from here
• Views are relations that are defined by queries on other relations.
  • not actually stored (as a table)
  • But can be queried.
  • In such a query, the DBMS will replace the view with the definition of the view.
• CREATE TABLE
  • Builds a physical organization where data is stored.
  • They are Persistent
    • They exist indefinitely
    • Do not change unless explicitly changed
• CREATE VIEW
  • Do not exist physically
  • But behave as if they do.
• Tables and views are both relations
• View Creation/Alter/Drop view

  • CREATE
        [OR REPLACE]
        [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
        [DEFINER = { user | CURRENT_USER }]
        [SQL SECURITY { DEFINER | INVOKER }]
        VIEW view_name [(column_list)]
        AS select_statement
        [WITH [CASCADED | LOCAL] CHECK OPTION]
    	

  • CREATE OR REPLACE VIEW is the same
    • CREATE VIEW if the view does not exist
    • ALTER VIEW if the view does exist.
  • Select: From tables or other views.
    • Columns can be table columns ore expressions
    • Column names, like in tables, must be unique
      • By default column names are from the tables.
      • But can be defined otherwise as part of the view creation.
    • Can not contain a sub query in FROM
    • Can not refer to user-defined variables
    • In a stored program can not refer to parameters or local variables
    • Can not be a prepared statement
    • Tables and views referred to must exist at creation
    • If a table or view is dropped that it refers to , the view will return an error.
    • Can not refer to a temporary table.
    • Can not associate a trigger.
    • Order by is ignored
  • Algorithm: A mysql extension, so we will not worry about it now.
  • A view is updatable
    • if there is a 1 to 1 relationship between rows in the view and rows in the (single) underlying table.
    • Does not contain:
      • DISTINCT,
      • GROUP BY,
      • HAVING,
      • UNION,
      • Subquery,
      • some joins,
  • Check
    • WITH CHECK OPTIONS prevents inserts/updates to rows except those in which the WHERE clause is true.
    • LOCAL: Does a local check.
    • CASCADE: Checks underlying tables and views as well.
• DROP VIEW IF EXISTS VIEWNAME
• ALTER VIEW changes the definition of the view.
• Widom tells us that some DMBS support triggers on views.
  • INSTEAD OF in place of BEFORE or AFTER
  • Allows better control of changes to the underlying tables.
• See view.sql
• SHOW CREATE VIEW viewname
• SHOW FULL TABLES;