/7-39_files/7-3900001im.jpg" width=645 height=1029
useMap=#Map>
Database
Management System
(CS403)
VU
Lecture No. 39 and
40
Reading
Material
"Database
Systems Principles, Design
and Implementation" written by
Catherine Ricardo,
Maxwell
Macmillan.
"Database
Management System" by Jeffery A
Hoffer
Overview of
Lecture
Introduction
to Views
o
Views,
Data Independence,
Security
o
Choosing
a Vertical and Horizontal
Subset of a Table
o
A View
Using Two Tables
o
A View of
a View
o
A View
Using a Function
o
Updates
on Views
o
Views
Views
are generally used to focus,
simplify, and customize the
perception
each
user has of the database.
Views can be used as
security mechanisms
by
allowing users to access
data through the view,
without granting the
users
permissions
to directly access the
underlying base tables of
the view.
To Focus on
Specific Data
Views
allow users to focus on
specific data that interests
them and on the
specific
tasks for which they
are responsible. Unnecessary
data can be left
out
of the view. This also
increases the security of
the data because
users
280
/7-39_files/7-3900002im.jpg" width=639 height=1029
useMap=#Map>
Database
Management System
(CS403)
VU
can
see only the data
that is defined in the view
and not the data in
the
underlying
table.
A
database view displays one
or more database records on
the same page. A
view
can display some or all of
the database fields. Views
have filters to
determine
which records they show.
Views can be sorted to
control the record
order
and grouped to display records in
related sets. Views have
other options
such
as totals and subtotals.
Most
users interact with the
database using the database
views. A key to
creating
a useful database is a well-chosen
set of views. Luckily, while
views
are
powerful, they are also
easy to create.
A
"view" is essentially a dynamically
generated "result" table
that is put
together
based upon the parameters
you have defined in your
query. For
example,
you might instruct the
database to give you a list of
all the
employees
in the EMPLOYEES table with
salaries greater than 50,000
USD
per
year. The database would
check out the EMPLOYEES
table and return
the
requested list as a "virtual
table".
Similarly,
a view could be composed of
the results of a query on
several
tables
all at once (sometimes
called a "join"). Thus, you
might create a view
of
all
the employees with a salary
of greater than 50K from
several stores by
281
/7-39_files/7-3900003im.jpg" width=639 height=1029
useMap=#Map>
Database
Management System
(CS403)
VU
accumulating
the results from queries to
the EMPLOYEES and
STORES
databases.
The possibilities are
limitless.
You
can customize all aspects of
a view, including:
The
name of the view
·
The
fields that appear in the
view
·
The
column title for each
field in the view
·
The
order of the fields in the
view
·
The
width of columns in the
view, as well as the overall
width of the view
·
The
set of records that appear in the
view (Filtering)
·
The
order in which records are
displayed in the view
(Sorting & Grouping)
·
Column
totals for numeric and
currency fields (Totaling &
Subtotaling)
·
The
physical schema for a
relational database describes
how the relations in
the
conceptual schema are
stored, in terms of the file
organizations and
indexes
used. The conceptual schema is
the collection of schemas of
the
relations
stored in the database.
While some relations in the
conceptual
schema
can also be exposed to
applications, i.e., be part of
the external
schema
of the database, additional
relations in the external
schema can be
defined
using the view mechanism.
The view mechanism thus
provides the
support
for logical data
independence in the relational
model. That is, it
can
be
used to define relations in
the external schema that
mask changes in the
conceptual
schema of the database from
applications. For example, if
the
schema
of a stored relation is changed, we
can define a view with
the old
schema,
and applications that expect to
see the old schema
can now use this
view.
Views are also valuable in
the context of security: We
can define views
282