Database Systems: Understanding Views, Materialized Views, and Indexes, Lecture notes of Database Management Systems (DBMS)

Download Database Systems: Understanding Views, Materialized Views, and Indexes and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!

CS 420 – Introduction

to Database

Systems

Views and

Indexes

Silicon Valley University

Agenda

View Definition

Materialized

View

Indexes

Declaring

Views

Declare by:

CREATE [MATERIALIZED]

VIEW

AS ;

Default is virtual.

Example: View

Definition

CanDrink(drinker, beer) is a view

“containing” the drinker-beer pairs such

that the drinker frequents at least one

bar that serves the beer:

CREATE VIEW CanDrink AS

SELECT drinker, beer

FROM Frequents, Sells

WHEREFrequents.bar = Sells.bar;

Example

 the staff view does not show the

salary or commission_pct columns

of the base table employees

7

View Usage - Hide

Data

Complexity

8

A single view can be defined with a

join, which is a collection of related

columns or rows in multiple tables

 However, the view hides the fact that this

information actually originates from several

tables

A query might also perform

extensive calculations with

table information

Thus, users can query a view without

knowing how to perform a join or

calculations

View Usage -

Different

Presentation

1

Present the data in a different

perspective from that of the base

table

For example, the columns of a view

can be renamed without affecting

the tables on which the view is

based

View Usage -

Independence

Isolate applications from

changes in definitions of base

tables

Example

 A view references three columns of

a four column table

 A fifth column is added to the table

 The definition of the view is not

affected

 All applications using the view

1

1

Materialized

View

1

Materialized Views

(MVs)

1

Materialized views are query results that

have been stored or "materialized" in

advance as schema objects

The FROM clause of the query can

name tables, views, and

materialized views

Collectively these objects are called

master tables (a replication term) or

detail tables (a data warehousing

term)

Base Table Dropped

Lets now drop table sales , which

is a master table for sales_mv

Then queries

sales_mv.

1

Data Selected

The query selects data because

the rows are stored

(materialized) separately from

the data in the master tables

1

MV Usage

1

Materialized views are used to

summarize, compute,

replicate, and distribute data

In data warehouses, you can use

materialized views to compute

and store data generated from

aggregate functions such as

sums and averages

Example: A Data

Warehouse

2

0

Wal-Mart stores every sale at

every store in a database.

Overnight, the sales for the day

are used to update a data

warehouse = materialized

views of the sales.

The warehouse is used by

analysts to predict trends and

move goods to where they are

selling best.