Materialized View, Simple Views, Complex View, Dynamic Views

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

Updating Multiple Tables, Transaction Management >>

Database Management System (CS403)

that give a group of user's access to just the information they are allowed to

see. For example, we can define a view that allows students to see other

students' name and age but not their GPA, and allow all students to access

this view, but not the underlying Students table.

There are two ways to create a new view in your database. You can:

Create a new view from scratch.

Or, make a copy of an existing view and then modify it.

Characteristics /Types of Views:

We have a number of views type of which some of the important views types are

listed below:

Materialized View

Simple Views

Complex View

Dynamic Views.

A materialized view is a replica of a target master from a single point in time.

The master can be either a master table at a master site or a master

materialized view at a materialized view site. Whereas in multi-master

replication tables are continuously updated by other master sites, materialized

views are updated from one or more masters through individual batch

updates, known as a refreshes, from a single master site or master

refreshes

materialized view site

Simple Views

283

Database Management System (CS403)

As defined earlier simple views are created from tables and are used for

creating secure manipulation over the tables or structures of the database.

Views make the manipulations easier to perform on the database.

Complex Views

Complex views are by definition views of type which may comprise of many of

elements, such as tables, views sequences and other similar objects of the

database. When talking about the views we can have views of one table,

views of one table and one view, views of multiple tables views of multiple

views and so on...

Dynamic Views

Dynamic views are those types of views for which data is not stored and the

expressions used to build the view are used to collect the data dynamically.

These views are not executed only once when they are referred for the first

time, rather they are created and the data contained in such views is updated

every time the view is accessed or used in any other view or query.

Dynamic views generally are complex views, views of views, and views of

multiple tables.

An example of a dynamic view creation is given below:

CREATE VIEW st_view1 AS (select stName, stFname, prName

FROM student

WHERE prName = 'MCS')

284

Database Management System (CS403)

Views can be referred in SQL statements like tables

We can have view created on functions and other views as well. Where the

function used for the view creation and the other nested view will be used as a

simple table or relation.

Examples:

View Using another View

CREATE VIEW CLASSLOC2

AS SELECT COURSE#, ROOM

FROM CLASSLOC

View Using Function

CREATE VIEW CLASSCOUNT(COURSE#, TOTCOUNT)

AS SELECT COURSE#,

COUNT(*)

FROM ENROLL

GROUP BY COURSE#;

Dynamic Views

SELECT * FROM st_view1

With Check Option

CREATE VIEW st_view2

AS (SELECT stName, stFname, prName FROM student WHERE prName = `BCS')

WITH CHECK OPTION

UPDATE ST_VIEW1 set prName = `BCS'

Where stFname = `Loving'

285

Database Management System (CS403)

SELECT * from ST_VIEW1

SELECT * FROM ST_VIEW2

Update ST_VIEW2 set prName = `MCS'

Where stFname = `Loving'

Characteristics of Views

· Computed attributes

· Nesting of views

CREATE VIEW enr_view AS (select * from enroll)

CREATE VIEW enr_view1 as (select stId, crcode, smrks, mterm, smrks +

mterm sessional from enr_view)

Select * from enr_view1

286

Database Management System (CS403)

Deleting Views:

A view can be dropped using the DROP VIEW command, which is just like

DROP TABLE.

Updates on Views

Updating a view is quite simple and is performed in the same way as we

perform updates on any of the database relations. But this simplicity is limited

to those views only which are created using a single relation. Those views

which comprise of multiple relations the updation are hard to perform and

needs additional care and precaution.

As we know that the views may contain some fields which are not the actual

data fields in the relation but may also contain computed attributes. So update

or insertions in this case are not performed through the views created on

these tables.

287

Table of Contents: