Views, Data Independence, Security, Vertical and Horizontal Subset of a Table

<< Ordered, Dense, Sparse, Multi-Level Indices, Clustered, Non-clustered Indexes

Materialized View, Simple Views, Complex View, Dynamic Views >>

Database Management System (CS403)

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

Views, Data Independence, Security

Choosing a Vertical and Horizontal Subset of a Table

A View Using Two Tables

A View of a View

A View Using a Function

Updates on Views

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

Database Management System (CS403)

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

Database Management System (CS403)

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

Table of Contents: