Transcript ppt

Database Modifications
CIS 4301
Lecture Notes
Lecture 19 - 3/30/2006
Recall Join Expressions




Cartesian Product: Multiple relations in the
FROM clause
Join: Specify join condition in WHERE clause
Remember, when specifying join condition, only
tuples which satisfy join condition will be
selected
Example R NATURAL JOIN S


If a tuple t from R does not match any tuple s from
S, all trace of t will disappear from result
Could pose problems – when?
© CIS 4301 - Spring 2006
Lecture 19
2
Outer Join


Differs from “inner” join
Adding to result any tuple of either
relation that does not join with at least
one tuple of the other relation



Recall, those tuples are called dangling tuples
Pad out dangling tuples with NULL’s in those
attributes belonging to the other relation
before adding to result
Left and Right Outer Join
© CIS 4301 - Spring 2006
Lecture 19
3
Example
SELECT *
FROM MovieStar LEFT OUTER JOIN MovieExec
ON MovieStar.Name = MovieExec.name;

Dangling tuples of the left (first) relation are
padded with NULLs and included in result
SELECT *
FROM MovieStar RIGHT OUTER JOIN MovieExec
ON MovieStar.Name = MovieExec.name;

Dangling tuples of the right (second) relation
are padded with NULLs and included in result
© CIS 4301 - Spring 2006
Lecture 19
4
Example (Older Syntax)
SELECT *
FROM MovieStar, MovieExec
WHERE MovieStar.Name(+) = MovieExec.name;
SELECT *
FROM MovieStar, MovieExec
WHERE MovieStar.Name = MovieExec.name(+);
© CIS 4301 - Spring 2006
Lecture 19
5
Where to go for details?

Oracle 9i SQL Reference

http://www.cise.ufl.edu/help/database/oracle
-docs/server.920/a96540.pdf
© CIS 4301 - Spring 2006
Lecture 19
6
Database Modifications: Insert
INSERT INTO R(A1,A2, …, An)
VALUES (v1,v2, …, vn)
Example:
INSERT INTO
Movies(title,year,length,inColor,Studio
Name,producerC#)
VALUES (‘Star
Wars’,1997,191,‘y’,‘Fox’,12345);



May omit list of attributes if we provide all values
Values may be provided explicitly through SFW
subquery
© CIS 4301 - Spring 2006
Lecture 19
7
Timing of Insertions

Add to Studio all movie studios mentioned in
Movie who do not appear in Studio.
1 INSERT INTO Studio(name)
2
3
4
5
6

SELECT studioName
FROM Movie
WHERE studioName NOT IN
(SELECT Name
FROM Studio);
Result can be affected by timing of insertion
© CIS 4301 - Spring 2006
Lecture 19
8
Database Modifications: Delete
DELETE FROM R WHERE <condition>

Delete the fact that R. Moore was a star in
the Maltese Falcon
DELETE
FROM StarsIn
WHERE movietitle =‘The Maltese Falcon’
AND movieYear = 1942
AND starName = ‘R. Moore’;
© CIS 4301 - Spring 2006
Lecture 19
9
Database Modifications: Update
UPDATE R SET <new value assignment>
WHERE <condition>;
 Find all tuples in R that satisfy WHERE-clause
 Each tuple will be updated by evaluating the
formula(s) in the SET-clause and making
assignments

Prepend the tittle ‘Pres.’ in front of every movie
executive who is a president of a studio
UPDATE MovieExec
SET name = ‘Pres.’ || name
WHERE cert# IN
(SELECT presC# FROM Studio);
© CIS 4301 - Spring 2006
Lecture 19
10
SQL Views

Two kinds of relations



Virtual relation = view




Physically present in database: tables or base relations
Virtual relations, do not exist physically
Can be queried (as if they existed physically)
In some cases, can even be modified!
Be clear about which type of relation you mean
View defined by an expression (like a query)
CREATE VIEW <view-name> AS
<view-definition>
SFW query
© CIS 4301 - Spring 2006
Lecture 19
11
Example

Create a view of the Movie relation that
includes only movies made by Paramount
CREATE VIEW Paramount_Movies AS
SELECT title, year
FROM Movie
WHERE studioName = ‘Paramount’;

Use it in query:
SELECT title
FROM Paramount_Movies
WHERE year = 1970;
© CIS 4301 - Spring 2006
Lecture 19
12
Facts About Views

Can be queried just like any other table
Allow data to been seen differently by different users
Often used as a security mechanism

Cannot always be updated




Updateable views: translate the modification of the view into
equivalent modifications on the corresponding base table(s)
Complex rules about which views are updateable
© CIS 4301 - Spring 2006
Lecture 19
13
Modifying Views


Suppose we try to insert (through view
Paramount_Movies) the movie (‘Star Trek’, 1979)
into the base relation Movie
Problem, studioName is not part of the view  not
updateable


Would leave studioName in Movie without value (NULL)
New view definition:
CREATE VIEW Paramount_Movies AS
SELECT title, year, studioName
FROM Movie
WHERE studioName = ‘Paramount’;
© CIS 4301 - Spring 2006
Lecture 19
14
More View Examples
CREATE VIEW MovieProd AS
SELECT title, name
FROM Movie, Movie Exec
WHERE producerC#= cert#;

What happens when we try:
INSERT INTO MovieProd VALUES
(‘Greatest …’, ‘Cecile B. DeMille’);


Cannot update the two relations Movie and
MovieExec correctly
MovieProd is not updateable!
© CIS 4301 - Spring 2006
Lecture 19
15
Interpreting Queries Involving
Views

In order to process query, it must first be represented
by its expression tree in relational algebra




To evaluate, leaves must be base tables
In case query involves views, some of the leaves are
virtual relations
Represent virtual relations by their definitions, which
must also be in terms of expression trees
To form the query over base tables, substitute for
each leaf in tree that is a view, the root of a copy of
the tree that defines that view
© CIS 4301 - Spring 2006
Lecture 19
16
Example
View:
CREATE VIEW ParamountMovie AS
SELECT title, year
FROM Movie
WHERE studioName =
‘Paramount’;
Query:
SELECT title
FROM ParamountMovie
WHERE year = 1979;
title, year
studioName = ‘Paramount’
title
year = 1979
Movie
ParamountMovie
base table
© CIS 4301 - Spring 2006
virtual table
Lecture 19
17
Expressing the Query in Terms
of Base Tables
title
year = 1979
simplified
title
year = 1979 AND
title, year
studioName = ‘Paramount’
Movie
studioName = ‘Paramount’
Movie
replace virtual table by
its view definition
© CIS 4301 - Spring 2006
Lecture 19
18
Indexes




Index I on attribute A of relation R
Data structure that makes it efficient to find those
tuples that have a fixed value for attribute A
E.g., “Find all MovieStars where gender = ‘M’”
Prevents database from having to scan all tuples of a
relation


Different types of indexes for different types of
lookup


Useful when relation is large
Can include multiple attributes in index
Determining which attributes to index on is not
always easy and requires experience

Trade-off between speed and overhead for space and
maintenance
© CIS 4301 - Spring 2006
Lecture 19
19
Sample Indexes
CREATE INDEX Movie_index
ON Movie(producerC#)
TABLESPACE cis4301_sp06_ind;
DROP INDEX Movie_index;
CREATE TABLE Test (
a INT PRIMARY KEY USING INDEX TABLESPACE
cis4301_sp06_ind,
b VARCHAR(20) UNIQUE USING INDEX TABLESPACE
cis4301_sp06_ind
);
Drawback: using the implicit declaration, we cannot
name index!

© CIS 4301 - Spring 2006
Lecture 19
20
Selection of Indexes


Why do we not index ALL attributes and
their combinations?
Two factors

Speed vs. overhead
© CIS 4301 - Spring 2006
Lecture 19
21
Intro to Database Tuning

Assume we have the schema
StarsIn(movieTitle,movieYear,starName)


Assume we have following query mix
Q1:
SELECT starName
FROM StarsIn
WHERE starName = s;
Q2:
SELECT starName
FROM StarsIn
WHERE movieTitle = t AND movieYear = y;
Q3:
INSERT INTO StarsIn VALUES (t,y,s);
Which indexes should we create?
© CIS 4301 - Spring 2006
Lecture 19
22