1 / 100

Lecture 03: Views and Constraints

Lecture 03: Views and Constraints. Wednesday, October 13, 2010. Announcements. HW1: was due yesterday HW2: due next Tuesday. Outline and Reading Material. Constraints and triggers Book: 3.2, 3.3, 5.8 Views Book: 3.6

aretha
Download Presentation

Lecture 03: Views and Constraints

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lecture 03:Views and Constraints Wednesday, October 13, 2010 Dan Suciu -- CSEP544 Fall 2010

  2. Announcements • HW1: was due yesterday • HW2: due next Tuesday Dan Suciu -- CSEP544 Fall 2010

  3. Outline and Reading Material • Constraints and triggers • Book: 3.2, 3.3, 5.8 • Views • Book: 3.6 • Answering queries using views: A survey, A.Y. Halevy: Sections 1 and 2 (Section 3 is optional) Most of today’s material is NOT covered in the book.Read the slides carefully Dan Suciu -- CSEP544 Fall 2010

  4. Constraints • A constraint = a property that we’d like our database to hold • Enforce it by taking some actions: • Forbid an update • Or perform compensating updates • Two approaches: • Declarative integrity constraints • Triggers Dan Suciu -- CSEP544 Fall 2010

  5. Integrity Constraints in SQL simple • Keys, foreign keys • Attribute-level constraints • Tuple-level constraints • Global constraints: assertions The more complex the constraint, the harder it is to check and to enforce complex Dan Suciu -- CSEP544 Fall 2010

  6. Keys CREATE TABLE Product ( name CHAR(30) PRIMARY KEY, price INT) OR: Product(name, price) CREATE TABLE Product ( name CHAR(30), price INT, PRIMARY KEY (name)) Dan Suciu -- CSEP544 Fall 2010

  7. CREATE TABLE Product ( name CHAR(30), category VARCHAR(20), price INT, PRIMARY KEY (name, category)) Keys with Multiple Attributes Product(name, category, price)

  8. Other Keys CREATE TABLE Product ( productID CHAR(10), name CHAR(30), category VARCHAR(20), price INT,PRIMARYKEY (productID), UNIQUE (name, category)) There is at most one PRIMARY KEY;there can be many UNIQUE Dan Suciu -- CSEP544 Fall 2010

  9. Foreign Key Constraints CREATE TABLE Purchase ( buyer CHAR(30), seller CHAR(30), product CHAR(30) REFERENCES Product(name), store VARCHAR(30)) Foreign key Purchase(buyer, seller, product, store)Product(name, price) Dan Suciu -- CSEP544 Fall 2010

  10. Product Purchase Dan Suciu -- CSEP544 Fall 2010

  11. Foreign Key Constraints CREATE TABLE Purchase( buyer VARCHAR(50), seller VARCHAR(50), product CHAR(20), category VAVRCHAR(20), store VARCHAR(30),FOREIGN KEY (product, category)REFERENCESProduct(name, category) ); Purchase(buyer, seller, product, category, store)Product(name, category, price) Dan Suciu -- CSEP544 Fall 2010

  12. What happens during updates ? Types of updates: • In Purchase: insert/update • In Product: delete/update Product Purchase

  13. 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 Dan Suciu -- CSEP544 Fall 2010

  14. Constraints on Attributes and Tuples Attribute level constraints: CREATE TABLE Purchase ( . . . store VARCHAR(30) NOT NULL, . . . ) CREATE TABLE Product ( . . . price INT CHECK (price >0 and price < 999)) Tuple level constraints: . . . CHECK (price * quantity < 10000) . . . Dan Suciu -- CSEP544 Fall 2010

  15. Whatis the difference fromForeign-Key ? CREATE TABLE Purchase ( prodName CHAR(30) CHECK (prodName INSELECTProduct.nameFROM Product), date DATETIME NOT NULL) Dan Suciu -- CSEP544 Fall 2010

  16. General Assertions CREATE ASSERTIONmyAssertCHECKNOT EXISTS( SELECTProduct.nameFROM Product, PurchaseWHEREProduct.name = Purchase.prodNameGROUPBYProduct.nameHAVING count(*) > 200) Dan Suciu -- CSEP544 Fall 2010

  17. Comments on Constraints • Can give them names, and alter later • We need to understand exactly when they are checked • We need to understand exactly what actions are taken if they fail Dan Suciu -- CSEP544 Fall 2010

  18. Semantic Optimization using Constraints Purchase(buyer, seller, product, store)Product(name, price) SELECT Purchase.storeFROM Product, PurchaseWHEREProduct.name=Purchase.product Why ? and When ? SELECT Purchase.storeFROM Purchase

  19. Triggers Trigger = a procedure invoked by the DBMS in response to an update to the database Some applications use triggers to enforce integrity constraints Trigger = Event + Condition + Action Dan Suciu -- CSEP544 Fall 2010

  20. Triggers in SQL • Event = INSERT, DELETE, UPDATE • Condition = any WHERE condition • Refers to the old and the new values • Action = more inserts, deletes, updates • May result in cascading effects ! Dan Suciu -- CSEP544 Fall 2010

  21. Example: Row Level Trigger CREATE TRIGGERInsertPromotionsAFTER UPDATEOF price ON Product REFERENCING OLD AS x NEW AS y FOR EACH ROW WHEN (x.price > y.price) INSERTINTOPromotions(name, discount) VALUESx.name, (x.price-y.price)*100/x.price Event Condition Action Warning: complex syntax and vendor specific.Take away from the slides the main ideas, not the syntactic details

  22. EVENTS INSERT, DELETE, UPDATE • Trigger can be: • AFTER event • INSTEAD of event Dan Suciu -- CSEP544 Fall 2010

  23. Scope • FOR EACH ROW = trigger executed for every row affected by update • OLD ROW • NEW ROW • FOR EACH STATEMENT = trigger executed once for the entire statement • OLD TABLE • NEW TABLE Dan Suciu -- CSEP544 Fall 2010

  24. Statement Level Trigger CREATE TRIGGERavg-price INSTEAD OF UPDATE OF price ON Product REFERENCING OLD_TABLE AS OldStuff NEW_TABLE AS NewStuff FOR EACH STATEMENT WHEN (1000 < (SELECT AVG (price) FROM ((Product EXCEPT OldStuff) UNION NewStuff)) DELETE FROM Product WHERE (name, price, company) IN OldStuff; INSERT INTO Product (SELECT * FROM NewStuff) Dan Suciu -- CSEP544 Fall 2010

  25. Trigersv.s. Constraints Active database = a database with triggers • Triggers can be used to enforce ICs • Triggers are more general: alerts, log events • But hard to understand: recursive triggers • Syntax is vendor specific, and may vary significantly • Postgres has rules in addition to triggers Dan Suciu -- CSEP544 Fall 2010

  26. Views: Overview • Virtual views • Applications • Technical challenges • Materialized views • Applications • Technical challenges Dan Suciu -- CSEP544 Fall 2010

  27. Views Views are relations, but may not be physically stored. For presenting different information to different users Employee(ssn, name, department, project, salary) Payroll has access to Employee, others only to Developers CREATE VIEWDevelopers AS SELECT name, project FROM Employee WHERE department = ‘Development’ Dan Suciu -- CSEP544 Fall 2010

  28. Example Purchase(customer, product, store) Product(pname, price) CREATE VIEWCustomerPrice AS SELECTx.customer, y.price FROM Purchase x, Product y WHEREx.product = y.pname CustomerPrice(customer, price) “virtual table” Dan Suciu -- CSEP544 Fall 2010

  29. Purchase(customer, product, store) Product(pname, price) CustomerPrice(customer, price) We can later use the view: SELECT u.customer, v.store FROMCustomerPriceu, Purchase v WHEREu.customer = v.customer AND u.price > 100 Dan Suciu -- CSEP544 Fall 2010

  30. Types of Views • Virtual views: • Used in databases • Computed only on-demand – slow at runtime • Always up to date • Materialized views • Used in data warehouses • Pre-computed offline – fast at runtime • May have stale data or expensive synchronization Dan Suciu -- CSEP544 Fall 2010

  31. Purchase(customer, product, store) Product(pname, price) CustomerPrice(customer, price) Queries Over Virtual Views:Query Modification CREATE VIEWCustomerPrice AS SELECTx.customer, y.price FROM Purchase x, Product y WHEREx.product = y.pname View: SELECT u.customer, v.store FROMCustomerPriceu, Purchase v WHEREu.customer = v.customer AND u.price > 100 Query: Dan Suciu -- CSEP544 Fall 2010

  32. Purchase(customer, product, store) Product(pname, price) CustomerPrice(customer, price) Queries Over Virtual Views:Query Modification Modified query: SELECT u.customer, v.store FROM (SELECTx.customer, y.price FROM Purchase x, Product y WHEREx.product = y.pname) u, Purchase v WHEREu.customer = v.customer AND u.price > 100 Dan Suciu -- CSEP544 Fall 2010

  33. Purchase(customer, product, store) Product(pname, price) CustomerPrice(customer, price) Queries Over Virtual Views:Query Modification Modified and unnested query: SELECT x.customer, v.store FROMPurchase x, Product y, Purchase v, WHEREx.customer = v.customer AND y.price > 100 AND x.product = y.pname Dan Suciu -- CSEP544 Fall 2010

  34. Purchase(customer, product, store) Product(pname, price) CustomerPrice(customer, price) Another Example SELECT DISTINCTu.customer, v.store FROMCustomerPriceu, Purchase v WHEREu.customer = v.customer AND u.price > 100 ?? Dan Suciu -- CSEP544 Fall 2010

  35. Purchase(customer, product, store) Product(pname, price) CustomerPrice(customer, price) Answer SELECT DISTINCTu.customer, v.store FROMCustomerPriceu, Purchase v WHEREu.customer = v.customer AND u.price > 100 SELECT DISTINCTx.customer, v.store FROMPurchase x, Product y, Purchase v, WHEREx.customer = v.customer AND y.price > 100 AND x.product = y.pname Dan Suciu -- CSEP544 Fall 2010

  36. Applications of Virtual Views • Physical data independence. E.g. • Vertical data partitioning • Horizontal data partitioning • Security • The view reveals only what the users are allowed to know Dan Suciu -- CSEP544 Fall 2010

  37. Vertical Partitioning Resumes T1 T2 T3

  38. Vertical Partitioning CREATE VIEWResumes AS SELECT T1.ssn, T1.name, T1.address, T2.resume, T3.picture FROM T1,T2,T3 WHERE T1.ssn=T2.ssn and T2.ssn=T3.ssn Dan Suciu -- CSEP544 Fall 2010

  39. Vertical Partitioning SELECT address FROMResumes WHERE name = ‘Sue’ Which of the tables T1, T2, T3 willbe queried by the system ? Dan Suciu -- CSEP544 Fall 2010

  40. Vertical Partitioning When to do this: • When some fields are large, and rarely accessed • E.g. Picture • In distributed databases • Customer personal info at one site, customer profile at another • In data integration • T1 comes from one source • T2 comes from a different source Dan Suciu -- CSEP544 Fall 2010

  41. Horizontal Partitioning Customers CustomersInHuston CustomersInSeattle CustomersInCanada

  42. Horizontal Partitioning CREATE VIEWCustomers AS CustomersInHuston UNION ALL CustomersInSeattle UNION ALL . . . Dan Suciu -- CSEP544 Fall 2010

  43. Horizontal Partitioning SELECT name FROMCusotmers WHERE city = ‘Seattle’ Which tables are inspected by the system ? WHY ??? Dan Suciu -- CSEP544 Fall 2010

  44. Horizontal Partitioning SELECT name FROMCusotmers WHERE city = ‘Seattle’ CREATE VIEWCustomers AS CustomersInXXX UNION ALL CustomersInYYY UNION ALL . . . Now even humanscan’t tell which tablecontains customersin Seattle Dan Suciu -- CSEP544 Fall 2010

  45. Horizontal Partitioning Better: CREATE VIEWCustomers AS (SELECT * FROM CustomersInHuston WHERE city = ‘Huston’) UNION ALL (SELECT * FROM CustomersInSeattle WHERE city = ‘Seattle’) UNION ALL . . . Dan Suciu -- CSEP544 Fall 2010

  46. Horizontal Partitioning SELECT name FROMCusotmers WHERE city = ‘Seattle’ SELECT name FROMCusotmersInSeattle Dan Suciu -- CSEP544 Fall 2010

  47. Horizontal Partitioning Applications: • Optimizations: • E.g. archived applications and active applications • Distributed databases • Data integration Dan Suciu -- CSEP544 Fall 2010

  48. Views and Security Fred is notallowed tosee this Customers: Fred isallowed tosee this CREATE VIEW PublicCustomers SELECT Name, Address FROM Customers Dan Suciu -- CSEP544 Fall 2010

  49. Views and Security John isnot allowedto see >0balances Customers: CREATE VIEW BadCreditCustomers SELECT * FROM Customers WHERE Balance < 0 Dan Suciu -- CSEP544 Fall 2010

  50. Technical Challenges in Virtual Views • Simplifying queries over virtual views • Updating virtual views Dan Suciu -- CSEP544 Fall 2010

More Related