[SOLVED] database SQL Data Warehousing and Decision Support

Data Warehousing and Decision Support

Views

CS430/630
Lecture 11

Slides based on Database Management Systems 3rd ed, Ramakrishnan and Gehrke

Views

So far, we have looked at SQL tables

Relations that are persistent

Physically stored in the DBMS

It is also possible to have virtual relations, or views

Defined by an expression which is a SQL query

Do not exist physically in DBMS

Although it is possible to used materialized views

Views can be queried directly

In some cases, it is also possible to modify views

2

External

Schema

Levels of Abstraction

Data

Physical Schema

Conceptual Schema

View 1 View 2 View 3

Describes
files and
indexes

used

Defines
logical data

structure

Views
define how

users see
data

3

Creating a view

CREATE VIEW RegionalSales (category, sales, state)
AS SELECT P.category, S.sales, L.state
FROM Products P, Sales S, Locations L
WHERE P.pid=S.pid AND S.locid=L.locid

View

Defining Query
(also referred to as
View Subquery)

Base Tables

4

Querying views

SELECT R.category, R.state, SUM(R.sales)
FROM RegionalSales R GROUP BY R.category, R.state

Querying Views

Views are queried just like regular tables

A view is just another relation (albeit a virtual one)

Queries can involve both views and base tables

Helps to think of views in terms of analogy with window on data

5

Views as subqueries

SELECT R.category, R.state, SUM(R.sales)
FROM (SELECT P.category, S.sales, L.state
FROM Products P, Sales S, Locations L
WHERE P.pid=S.pid AND S.locid=L.locid) R
GROUP BY R.category, R.state

Equivalent Query
(without views)

SubQuery

6

Why are views useful?(1/3)

Usability

Certain information must be retrieved from many tables

View abstraction can get all info in one (virtual) table

Queries are much easier to write on a single table

Subqueries that are often used can be included in queries

without need for nesting

7

Why are views useful? (2/3)

Compatibility

Shield users and application developer from changes

What if a schema changes? Define view that looks like the old

schema

Users/applications access view, no changes needed in queries

Obsolete tables are preserved using views

8

Why are views useful? (3/3)

Security

Restrict user access to certain data only

Managers and employees are given different views of same data

Both column- and row-level access control possible

Column-wise: students can only access Name and Age

columns from a Student table

Row-wise: access only transactions above $10,000 value

9

Modifying views

Is it possible to insert, update, delete tuples in a view?

Views are virtual

so modifications must be reflected in the base tables

Why modifying views is a subtle issue?

Difficulty of translating view modifications in a unique way of

updating base tables

Must be non-ambiguous in how to trace the base table tuple to update

Views can be modified subject to restrictions

These are called updatable views

Still, many views are not updatable

10

Updatable Views

SQL-92 provides formal definition of updatable view:

1. View involves a single relation R. If R is a view, it must also be

updatable (relaxed in SQL-99)

2. Aggregate operations are not present in the view definition

3. The DISTINCT keyword is not specified in SELECT clause

4. All columns in subquery are simple columns, not expressions

5. The WHERE clause must not contain a subquery involving R

6. All attributes in R that are not in the SELECT clause of the

view must not have both NOT NULL restriction and no

default
11

Updatable Views (contd.)

Insertion can be done directly on the base table

Other attributes in R set to NULL

Deletion also possible

Delete tuple from base table

Both insertion and deletion may cause problems!

12

Issues with insertion
View Definition

Now lets insert a new student

CREATE VIEW TopStudents (sname)
AS SELECT Name
FROM Students S
WHERE S.gpa > 3.0;

INSERT INTO TopStudents VALUES(FirstLastName);

GPA is set to NULL

Tuple falls outside view definition!

Not a mistake, but update will not be reflected in view!

WITH CHECK OPTION clause disallows such an insertion

One solution is to include GPA in view definition

13

Issues with deletion

Now lets delete students named Johnson

CREATE VIEW TopStudents (sname)
AS SELECT Name
FROM Students S
WHERE S.gpa > 3.0;

DELETE FROM TopStudents WHERE Name LIKE %Johnson%;

Must only affect tuples in the view!

Outside tuples must be inaccessible (views used for security, too)

DBMS appends WHERE clause in view definition to statement

DELETE FROM Students WHERE Name LIKE %Johnson%
AND S.gpa > 3.0;

14

Deleting views

View deleted from the schema

Note that, underlying data still intact

Contrast this with DROP TABLE!

DROP VIEW RegionalSales;

15

View Materialization

Materialized views can help speed up popular queries

Result has to be maintained when base tables change

They are stored just like base tables

But their contents are not independent;they must constantly

reflect base tables

16

Example

Create view ActorSummary that lists for every actor the actor identifier, actor
name, number of movies starred in, and the year of debut (i.e., the year of the
earliest movie(s) the actor starred in). The view will have four columns with
headings:
ID, ActorName, MovieCount and DebutYear

CREATE VIEW ActorSummary(ID, ActorName, MovieCount, DebutYear )
AS

SELECT A.actor_id, A.name, COUNT(M.movie_id), MIN(M.year)
FROM Actors A, StarsIn S, Movies M
WHERE A.actor_id = S.actor_id AND S.movie_id = M.movie_id
GROUP BY A.actor_id, A.name;

Example 2

CREATE VIEW ManagerSummary(DeptName, MgrName, MgrID,
MgrSalary, EmpCount) AS

SELECT D.dname, D.managerid, E.ename, E.salary, COUNT(W.eid)
FROM Department D, Employee E, Works W
WHERE D.managerid = E.eid AND D.did = W.did
GROUP BY D.did, D.dname, D.managerid, E.ename, E.salary;

CreateaviewManagerSummarythatlistsforeverydepartmentthe
departmentname, managerIDandmanagername,managersalaryand
thenumberofemployeesinthatdepartment. The view will have five
columns with headings:
DeptName,MgrID, MgrName, MgrSalary and EmpCount.