Transcript Views

Lecture 6: Views
Friday, January 17th, 2003
1
Updating Views
How can I insert a tuple into a table that doesn’t exist?
Employee(ssn, name, department, project, salary)
CREATE VIEW Developers AS
SELECT name, project
FROM Employee
WHERE department = “Development”
If we make the
following insertion:
It becomes:
INSERT INTO Developers
VALUES(“Joe”, “Optimizer”)
INSERT INTO Employee
VALUES(NULL, “Joe”, NULL, “Optimizer”, NULL)
2
Non-Updatable Views
CREATE VIEW Seattle-view AS
SELECT seller, product, store
FROM Person, Purchase
WHERE Person.city = “Seattle” AND
Person.name = Purchase.buyer
How can we add the following tuple to the view?
(“Joe”, “Shoe Model 12345”, “Nine West”)
We need to add “Joe” to Person first, but we don’t have all its attributes
3
Answering Queries Using Views
• What if we want to use a set of views to
answer a query.
• Why?
– The obvious reason…
– Answering queries over web data sources.
• Very cool stuff! (i.e., I did a lot of research
on this).
4
Reusing a Materialized View
• Suppose I have only the result of SeattleView:
SELECT buyer, seller, product, store
FROM Person, Purchase
WHERE Person.city = ‘Seattle’ AND
Person.per-name = Purchase.buyer
• and I want to answer the query
SELECT buyer, seller
FROM Person, Purchase
WHERE Person.city = ‘Seattle’ AND
Person.per-name = Purchase.buyer AND
Purchase.product=‘gizmo’.
Then, I can rewrite the query using the view.
5
Query Rewriting Using Views
Rewritten query:
SELECT buyer, seller
FROM
SeattleView
WHERE product= ‘gizmo’
Original query:
SELECT buyer, seller
FROM Person, Purchase
WHERE Person.city = ‘Seattle’ AND
Person.per-name = Purchase.buyer AND
Purchase.product=‘gizmo’.
6
Another Example
• I still have only the result of SeattleView:
SELECT buyer, seller, product, store
FROM Person, Purchase
WHERE Person.city = ‘Seattle’ AND
Person.per-name = Purchase.buyer
• but I want to answer the query
SELECT buyer, seller
FROM Person, Purchase
WHERE Person.city = ‘Seattle’ AND
Person.per-name = Purchase.buyer AND
Person.Phone LIKE ‘206 543 %’.
7
And Now?
• I still have only the result of SeattleView:
SELECT buyer, seller, product, store
FROM Person, Purchase, Product
WHERE Person.city = ‘Seattle’ AND
Person.per-name = Purchase.buyer AND
Purchase.product = Product.name
• but I want to answer the query
SELECT buyer, seller
FROM Person, Purchase
WHERE Person.city = ‘Seattle’ AND
Person.per-name = Purchase.buyer.
8
And Now?
• I still have only the result of:
SELECT seller, buyer, Sum(Price)
FROM Purchase
WHERE Purchase.store = ‘The Bon’
Group By seller, buyer
• but I want to answer the query
SELECT seller, Sum(Price)
FROM Purchase
WHERE Person.store = ‘The Bon’
Group By seller
And what if it’s the other way around?
9
Finally…
• I still have only the result of:
SELECT seller, buyer, Count(*)
FROM Purchase
WHERE Purchase.store = ‘The Bon’
Group By seller, buyer
• but I want to answer the query
SELECT seller, Count(*)
FROM Purchase
WHERE Person.store = ‘The Bon’
Group By seller
10
The General Problem
• Given a set of views V1,…,Vn, and a query
Q, can we answer Q using only the answers to
V1,…,Vn?
• Why do we care?
– We can answer queries more efficiently.
– We can query data sources on the WWW in a
principled manner.
• Many, many papers on this problem.
• The best performing algorithm: The MiniCon
Algorithm, (Pottinger & (Ha)Levy, 2000). 11
Querying the WWW
• Assume a virtual schema of the WWW, e.g.,
– Course(number, university, title, prof, quarter)
• Every data source on the web contains the
answer to a view over the virtual schema:
UW database: SELECT number, title, prof
FROM Course
WHERE univ=‘UW’ AND quarter=‘2/02’
Stanford database: SELECT number, title, prof, quarter
FROM Course
WHERE univ=‘Stanford’
User query: find all professors who teach “database systems”
12
Constraints in SQL
• A constraint = a property that we’d like our
database to hold
• The system will enforce the constraint by
taking some actions:
– forbid an update
– or perform compensating updates
13
Constraints in SQL
Constraints in SQL:
• Keys, foreign keys
• Attribute-level constraints
• Tuple-level constraints
• Global constraints: assertions
simplest
Most
complex
The more complex the constraint, the harder it is to
check and to enforce
14
Keys
CREATE TABLE Product (
name CHAR(30) PRIMARY KEY,
category VARCHAR(20))
OR:
CREATE TABLE Product (
name CHAR(30),
category VARCHAR(20)
PRIMARY KEY (name))
15
Keys with Multiple Attributes
CREATE TABLE Product (
name CHAR(30),
category VARCHAR(20),
price INT,
PRIMARY KEY (name, category))
16
Other Keys
CREATE TABLE Product (
productID CHAR(10),
name CHAR(30),
category VARCHAR(20),
price INT,
PRIMARY KEY (productID),
UNIQUE (name, category))
There is at most one PRIMARY KEY;
there can be many UNIQUE
17
Foreign Key Constraints
Referential
integrity
constraints
CREATE TABLE Purchase (
prodName CHAR(30)
REFERENCES Product(name),
date DATETIME)
prodName is a foreign key to Product(name)
name must be a key in Product
18
Product
Purchase
Name
Category
ProdName
Store
Gizmo
gadget
Gizmo
Wiz
Camera
Photo
Camera
Ritz
OneClick
Photo
Camera
Wiz
19
Foreign Key Constraints
• OR
CREATE TABLE Purchase (
prodName CHAR(30),
category VARCHAR(20),
date DATETIME,
FOREIGN KEY (prodName, category)
REFERENCES Product(name, category)
• (name, category) must be a PRIMARY
KEY
20
What happens during updates ?
Types of updates:
• In Purchase: insert/update
• In Product: delete/update
Product
Purchase
Name
Category
ProdName
Store
Gizmo
gadget
Gizmo
Wiz
Camera
Photo
Camera
Ritz
OneClick
Photo
Camera
Wiz
21
What happens during updates ?
• SQL has three policies for maintaining
referential integrity:
• Reject violating modifications (default)
• Cascade: after a delete/update do a
delete/update
• Set-null set foreign-key field to NULL
READING ASSIGNEMNT: 7.1.5, 7.1.6
22
Constraints on Attributes and
Tuples
• Constraints on attributes:
NOT NULL
-- obvious meaning...
CHECK condition -- any condition !
• Constraints on tuples
CHECK condition
23
What
is the difference from
Foreign-Key ?
CREATE TABLE Purchase (
prodName CHAR(30)
CHECK (prodName IN
SELECT Product.name
FROM Product),
date DATETIME NOT NULL)
24
General Assertions
CREATE ASSERTION myAssert CHECK
NOT EXISTS(
SELECT Product.name
FROM Product, Purchase
WHERE Product.name = Purchase.prodName
GROUP BY Product.name
HAVING count(*) > 200)
25
Final Comments on Constraints
• Can give them names, and alter later
– Read in the book !!!
• We need to understand exactly when they
are checked
• We need to understand exactly what actions
are taken if they fail
26
Triggers in SQL
• A trigger contains an event, a condition, an action.
• Event = INSERT, DELETE, UPDATE
• Condition = any WHERE condition (may refer to
the old and the new values)
• Action = more inserts, deletes, updates
• Many, many more bells and whistles...
• Read in the book (it only scratches the surface...)
27