DOC PREVIEW
UW CSE 444 - Lecture Notes

This preview shows page 1-2-3-25-26-27 out of 27 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 27 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 27 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 27 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 27 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 27 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 27 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 27 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Lecture 6: ViewsUpdating ViewsNon-Updatable ViewsAnswering Queries Using ViewsReusing a Materialized ViewQuery Rewriting Using ViewsAnother ExampleAnd Now?Slide 9Finally…The General ProblemQuerying the WWWConstraints in SQLSlide 14KeysKeys with Multiple AttributesOther KeysForeign Key ConstraintsPowerPoint PresentationSlide 20What happens during updates ?Slide 22Constraints on Attributes and TuplesSlide 24General AssertionsFinal Comments on ConstraintsTriggers in SQL1Lecture 6: ViewsFriday, January 17th, 20032Updating ViewsHow 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”CREATE VIEW Developers AS SELECT name, project FROM Employee WHERE department = “Development”INSERT INTO Developers VALUES(“Joe”, “Optimizer”)INSERT INTO Developers VALUES(“Joe”, “Optimizer”)INSERT INTO Employee VALUES(NULL, “Joe”, NULL, “Optimizer”, NULL)INSERT INTO Employee VALUES(NULL, “Joe”, NULL, “Optimizer”, NULL)If we make thefollowing insertion: It becomes:3Non-Updatable ViewsCREATE VIEW Seattle-view AS SELECT seller, product, store FROM Person, Purchase WHERE Person.city = “Seattle” AND Person.name = Purchase.buyerCREATE VIEW Seattle-view AS SELECT seller, product, store FROM Person, Purchase WHERE Person.city = “Seattle” AND Person.name = Purchase.buyerHow 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 attributes4Answering 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).5Reusing 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.6Query Rewriting Using ViewsRewritten 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’.7Another 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 %’.8And 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.9And 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 sellerAnd what if it’s the other way around?10Finally…•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 seller11The 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).12Querying 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”13Constraints 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 updates14Constraints in SQLConstraints in SQL:•Keys, foreign keys•Attribute-level constraints•Tuple-level constraints•Global constraints: assertionsThe more complex the constraint, the harder it is to check and to enforcesimplestMostcomplex15KeysOR:CREATE TABLE Product (name CHAR(30) PRIMARY KEY,category VARCHAR(20))CREATE TABLE Product (name CHAR(30) PRIMARY KEY,category VARCHAR(20))CREATE TABLE Product (name CHAR(30),category VARCHAR(20)PRIMARY KEY (name))CREATE TABLE Product (name CHAR(30),category VARCHAR(20)PRIMARY KEY (name))16Keys with Multiple AttributesCREATE TABLE Product (name CHAR(30),category VARCHAR(20),price INT, PRIMARY KEY (name, category))CREATE TABLE Product (name CHAR(30),category VARCHAR(20),price INT, PRIMARY KEY (name, category))17Other KeysCREATE TABLE Product ( productID CHAR(10),name CHAR(30),category VARCHAR(20),price INT, PRIMARY KEY (productID), UNIQUE (name, category))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 UNIQUE18Foreign Key ConstraintsCREATE TABLE Purchase (prodName CHAR(30)REFERENCES Product(name), date DATETIME)CREATE TABLE


View Full Document

UW CSE 444 - Lecture Notes

Documents in this Course
XML

XML

48 pages

SQL

SQL

25 pages

SQL

SQL

42 pages

Recovery

Recovery

30 pages

SQL

SQL

36 pages

Indexes

Indexes

35 pages

Security

Security

36 pages

Wrap-up

Wrap-up

6 pages

SQL

SQL

37 pages

More SQL

More SQL

48 pages

SQL

SQL

35 pages

XML

XML

46 pages

Triggers

Triggers

26 pages

Load more
Download Lecture Notes
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Lecture Notes and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Lecture Notes 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?