Mumbai University > Information Technology > Sem 3 > Database Management System

Marks: 10 M

Year: May 2015

1. Definition of Views:

i. Basically, a view is a single table derived from multiple tables or a logically representing subset of data.

ii. These other tables can be base tables or previously defined views.

iii. A view is considered to be a virtual table, in contrast to base tables, whose tuples are physically stored in database.

2. Implementation of View:

i. In SQL, the command to specify a view is CREATE VIEW. The view is given a (virtual) table name (or view name), a list of attribute names, and a query to specify the contents of the view. Following is the syntax of view.

CREATE VIEW viewname AS

SELECT column1, column2

FROM tablename

WHERE columnname=expressionList;

ii. Consider a table Tbl_Employee with the fields Emp_ID, Emp_Name, Emp_DOB, Emp_Address,

Emp_DateOfJoining, Emp_Gender, Emp_EmailID. Following is the view vw_EmployeeContactDetails which contains the name and Email ID of the employee.

CREATE VIEW vw_EmployeeContactDetails

SELECT Emp_Name, Emp_EmailID

FROM Tbl_Employee

It results in the creation of view. To fetch the items of view, a select statement can be written as follows:

SELECT * FROM vw_EmployeeContactDetails

iii. If we do not need a view any more, we can use the DROP VIEW command to dispose of it. Syntax is DROP VIEW viewname Example: To drop the view vw_EmployeeContactDetails, following SQL statement must be executed: DROP VIEW vw_EmployeeContactDetails

3. Problems in updating Views:

i. Updating of views is complicated and can be ambiguous task.

ii. In general, an update on a view defined on a single table without any aggregate functions can be mapped to an update on the underlying base table under certain conditions.

iii. For a view involving joins, an update operation may be mapped to update operations on the underlying base relations in multiple ways. Hence, it is often not possible for the DBMS to determine which of the updates is intended.

iv. Generally, a view update is feasible when only one possible update on the base relations can accomplish the desired update effect on the view.

v. Whenever an update on the view can be mapped to more than one update on the underlying base relations, it is necessary to have a certain procedure for choosing one of the possible updates as the most likely one.

vi. Thus problems with updating a view can be summarized as follows:

• A view with a single defining table is updatable if the view attributes contain the primary key of the base relation, as well as all attributes with the NOT NULL constraint that do not have default values specified.
• It is generally not possible to update views defined on multiple tables.
• It is not possible to update views defined using grouping and aggregate functions