Transcript Views

CSC 411/511:
DBMS Design
Views
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
1
Views
• A view is a virtual table defined by a query. It
provides a mechanism to create alternate ways of
working with the data in a database.
• A view acts much like a table. We can query it with
a SELECT, and some views even allow INSERT,
UPDATE, and DELETE.
• A view doesn’t have any data. All of its data are
ultimately derived from tables (called base tables).
Views are similar to derived tables.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
2
Create a View
• We can create a view using the CREATE VIEW
command.
CREATE VIEW <view name> [(<column list>)] AS
<SELECT statement>
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
3
Create Views
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
4
Query Views
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
5
View may contain expressions
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
6
Views
• The view is just a virtual table, any changes to the
base tables are instantly reflected in the view data.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
7
Why Views
• Usability- we can use a view as a wrapper around
very complex SELECT statements to make our
system more usable.
• Security- if we need to restrict the data a user can
access, we can create a view containing only the
permitted data. The user is given access to the
view instead of the base table(s).
• Reduced Dependency- the database schema
evolves over time as our enterprise changes. Such
changes can break existing applications that
expect a certain set of tables with certain columns.
We can fix this by having our applications access
views rather than base tables. When the base
tables change, existing applications still work as
long as the views are correct.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
8
Updating Views
• We can even perform INSERT, UPDATE, and
DELETE on a view, which is propagated to the
underlying tables;
• however, there are restrictions on the kinds of
views that can be updated. Unfortunately, not all
DBMSs allow updating through views.
• Even if your DBMS allows updates to views, you
should be careful about using it, because some
results may be unexpected.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
9
Drop Views
• DROP VIEW <view name> [CASCADE | RESTRICT]
• This removes the specified view; however, it does not
change any of the data in the database.
• SQL does not allow a view to be dropped if view is
contained in the SELECT statement of another view. This is
the default behavior or the result of using the RESTRICT
option.
• To remove such a view, specify the CASCADE option. This
will cause any dependent views to be removed before the
view is dropped.
• According to the SQL specification, you must specify either
CASCADE or RESTRICT with DROP VIEW. It is not
optional.
• We present it as optional because most (if not all) DBMSs
treat it as optional, with a default behavior of RESTRICT.
For compliance with the SQL standard, it’s a good idea to
specify either CASCADE or RESTRICT.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
10
Summary
• Views are virtual tables whose columns and data
are defined by a SELECT statement.
• You may SELECT from views just like any other
table.
• Some views even allow INSERT, UPDATE, and
DELETE. Updates to the base tables that a view is
built from are immediately reflected in the view.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
11
Practice
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
12
CSC 411/511:
DBMS Design
Questions?
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
13