Views Continued

• Oh, by the way, we are in chapter 7 big time now.
• You should read it.
• Use Patterns for Views
  • To Hide Columns and Rows
    • We discussed this briefly, in a table that contains names, addresses and salaries
    • Create a view to provide access to names and addresses, but not the salary.
  • To display results of computed columns
    • In the table we discussed yesterday, price → (Tax, total)
    • If we want to present the full table they had constructed we could accomplish this via a view.
  • To Hide Complicated SQL Syntax
    • Again, think of the table we saw (p 157) last class.
    • The owner is probably very comfortable with that table and would want it.
    • We, as database people are not
    • A view would to the "Merge From Somewhere Warm" for us.
  • Layering Built-in Functions
    • WHERE clause can not use built-in functions or computations
    • Do a nasty join, and computed field into a view
    • Select the required results from that view.
    • Again, in the previous example, we might want to select rows where the total (price + price*taxRate) > 5000
    • Build a view to hold the computed value TotalSale
    • Then a query on that view
    • A temp table would work here as well.
  • Isolation
    • This is an abstract data type idea.
    • Create a view that mimics a table.
    • Applications are required to use the view.
    • Then later, the underlying table could be changed, but the view remains the same.
    • But the applications would not have to change.
    • I could see this being useful in database redesign.
  • Permissions
    • Two proxy views could be set up for a table.
    • View1 read only permission for a wide variety of clients
    • View2 has read/write permission, but only for a limited set of clients.
    • The DBA has read/write/modify permission on the table.
  • Multiple triggers on the same data source.
    • Enforce O-M and M-M relationships.
    • One view is used for child deletion but prohibits if a parent must be removed.
    • The other is used to delete child and parent if that must occur.
    • Only limited applications have access to the second view.

More on Triggers

• SQL supports more on triggers than MySQL
  • An INSTEAD OF option, I mentioned earlier
  • This is used to replace the operation being performed.
    • Used for views supporting multiple tables to perform updates
    • So it could perform inserts into the different tables, not into the view directly
  • I saw another example which searched the table for a maximum value of an auto key, and computed the next value to use.
• He suggests 4 uses of triggers
  • Providing default values, we have seen this.
  • Enforcing data constraints, we have seen this as well.
  • Updating views, as discussed above.
  • Implement Referential integrity actions, we have discussed this in the previous section
  • This section is still worth a read.