Page 72 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 4: Advanced SQL

Notes
Example: SELECT *
FROM STUDENT-PERFORMANCE

WHERE smarks >50

How the Views are Implemented?

There are two strategies for implementing the views. These are:
1. Query modification
2. View materialisation.
In the query modification strategy, any query that is made on the view is modified to include the
view defining expression.

Example: Consider the view STUDENT-PERFORMANCE. A query on this view may be:
The teacher of the course MM-0a wants to find the maximum and average marks in the course.
The query for this in SQL will be:

SELECT MAX(smarks), AVG(smarks)
FROM SUBJECT-PERFORMANCE
Since SUBJECT-PERFORMANCE is itself a view the query will be modified automatically as:

SELECT MAX (smarks), AVG (smarks)
FROM STUDENT s, MARKS m
WHERE s.enrolment-no=m.enrolment-no AND subjectcode= “MM-01”;

However, this approach has a major disadvantage. For a large database system, if complex
queries have to be repeatedly executed on a view, the query modification will have to be done
each time, leading to inefficient utilisation of resources such as time and space.
The view materialisation strategy solves this problem by creating a temporary physical table
for a view, thus, materialising it. However, this strategy is not useful in situations where many
database updates are made on the tables, which are used for view creation, as it will require
suitable updating of a temporary table each time the base table is updated.

Can views be used for Data Manipulations?

Views can be used during DML operations like INSERT, DELETE and UPDATE. When you
perform DML operations, such modifications need to be passed to the underlying base table.
However, this is not allowed on all the views. Conditions for the view that may allow Data
Manipulation are:
A view allows data updating, if it follows the following conditions:

1. If the view is created from a single table, then:
(a) For INSERT operation, the PRIMARY KEY column(s) and all the NOT NULL columns
must be included in the view.

(b) View should not be defined using any aggregate function or GROUP BY or HAVING
or DISTINCT clauses. This is due to the fact that any update in such aggregated
attributes or groups cannot be traced back to a single tuple of the base table. For
example, consider a view avgmarks (coursecode, avgmark) created on a base table

LOVELY PROFESSIONAL UNIVERSITY 65