Page 71 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes
Example: To find all customers having a loan, an account, or both at the bank, we write
(select customer-name
from depositor)
union
(select customer-name
from borrower)

The union operation automatically eliminates duplicates, unlike the select clause. Thus, in the
preceding query, if a customer-say, Jones-has several accounts or loans (or both) at the bank,
then Jones will appear only once in the result.
If we want to retain all duplicates, we must write union all in place of union:
(select customer-name
from depositor)
union all (select Customer-name
from borrower)
The number of duplicate tuples in the result is equal to the total number of duplicates that
appear in both d and b. Thus, if Jones has three accounts and two loans at the bank, then there
will be five tuples with the name Jones in the result.

4.4 Views

A view is a virtual table, which does not actually store data. But if it does not store any data, then
what does it contain?
A view actually is a query and thus has a SELECT FROM WHERE ….. clause which works on
physical table which stores the data. Thus, the view is a collection of relevant information for a
specific entity.

Example: A student’s database may have the following tables:
STUDENT (name, enrolment-no, dateofbirth)
MARKS (enrolment-no, subjectcode, smarks)
For the database above a view can be created for a Teacher who is allowed to view only the
performance of the student in his/her subject, let us say MM-01.
CREATE VIEW SUBJECT-PERFORMANCE AS
(SELECT s.enrolment-no, name, subjectcode, smarks
FROM STUDENT s, MARKS m
WHERE s.enrolment-no = m.enrolment-no AND
subjectcode ‘MM-01’ ORDER BY s.enrolment-no;

A view can be dropped using a DROP statement as:
DROP VIEW SUBJECT-PERFORMANCE;
The table, which stores the data on which the statement of the view is written, is sometimes
referred to as the base table. You can create views on two or more base tables by combining the
data using joins. Thus, a view hides the logic of joining the tables from a user. You can also index
the views too. This may speed up the performance. Indexed views may be beneficial for very
large tables. Once a view has been created, it can be queried exactly like a base table.

64 LOVELY PROFESSIONAL UNIVERSITY