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