1 / 46

C20.0046: Database Management Systems Lecture #13

C20.0046: Database Management Systems Lecture #13. Matthew P. Johnson Stern School of Business, NYU Spring, 2004. Agenda. Last time: G&A, modifications, defining schemata, indices This time:, Constraints, Triggers Views Constraints Triggers Homework 2 is due now Future:

gbarrera
Download Presentation

C20.0046: Database Management Systems Lecture #13

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. C20.0046: Database Management SystemsLecture #13 Matthew P. Johnson Stern School of Business, NYU Spring, 2004 M.P. Johnson, DBMS, Stern/NYU, Sp2004

  2. Agenda • Last time: G&A, modifications, defining schemata, indices • This time:, Constraints, Triggers • Views • Constraints • Triggers • Homework 2 is due now • Future: • Lots of programming for SQL M.P. Johnson, DBMS, Stern/NYU, Sp2004

  3. Quick review • Examples from sqlzoo.net M.P. Johnson, DBMS, Stern/NYU, Sp2004

  4. New topic: Views (6.7) • Stored relations physically exist and persist • Views are relations that don’t • in text, “table” = stored relation = “base table” • Basically names/references given to queries • maybe a relevant subset of a table • Employee(ssn, name, department, project, salary) • Payroll has access to Employee, others only to Developers CREATE VIEW Developers AS SELECT name, project FROM Employee WHERE department = ‘Development’ M.P. Johnson, DBMS, Stern/NYU, Sp2004

  5. A Different View • Person(name, city) • Purchase(buyer, seller, product, store) • Product(name, maker, category) • We have a new virtual table: NYC-view(buyer, seller, product, store) CREATE VIEW NYC-view AS SELECT buyer, seller, product, store FROM Person, Purchase WHERE Person.city = ‘New York’ AND Person.name = Purchase.buyer M.P. Johnson, DBMS, Stern/NYU, Sp2004

  6. A Different View CREATE VIEW NYC-view AS SELECT buyer, seller, product, store FROM Person, Purchase WHERE Person.city = ‘New York’ AND Person.name = Purchase.buyer • Now we can query the view: SELECT name, store FROMNYC-view, Product WHERENYC-view.product = Product.name AND Product.category = ‘shoes’ M.P. Johnson, DBMS, Stern/NYU, Sp2004

  7. What happens when we query a view ? SELECT name, NYC-view.store FROMNYC-view, Product WHERENYC-view.product = Product.name AND Product.category = ‘shoes’ SELECT name, Purchase.store FROMPerson, Purchase, Product WHEREPerson.city = ‘NYC’ AND Person.name = Purchase.buyer AND Purchase.product = Product.name AND Product.category = “shoes” M.P. Johnson, DBMS, Stern/NYU, Sp2004

  8. Can rename view fields • Views info: USER_VIEWS CREATE VIEW NYC-view(nycbuyer, nycseller, prod, store) AS SELECT buyer, seller, product, store FROM Person, Purchase WHERE Person.city = ‘NYC’ AND Person.name = Purchase.buyer M.P. Johnson, DBMS, Stern/NYU, Sp2004

  9. More complex views • NYC-View is just a subset of a table • Views can be arbitrarily complex • Imagine the database of student and course records • Students get one view: • For each semester, My-courses • Professors get another view • For each course, My-students • Bursar gets another view, etc. M.P. Johnson, DBMS, Stern/NYU, Sp2004

  10. Types of Views • Views discussed here: • Used in databases • Computed only on-demand – slow at runtime • Always up to date • Sometimes talk about “materialized” views • Used in data warehouses • Pre-computed offline – fast at runtime • May have stale data • More later… M.P. Johnson, DBMS, Stern/NYU, Sp2004

  11. 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: INSERT INTO Developers VALUES(‘Conrad’, ‘Optimizer’) INSERT INTO Employee(ssn, name, department, project, salary)VALUES(NULL, ‘Conrad’, NULL, ‘Optimizer’, NULL) It becomes: M.P. Johnson, DBMS, Stern/NYU, Sp2004

  12. Non-Updatable Views • Person(name, city) • Purchase(buyer, seller, product, store) • How can we add the following tuple to the view? (‘NYC’, ‘Nine West’) • We don’t know the name of the purchaser • cannot set to NULL (why?) • To delete: CREATE VIEW City-Store AS SELECT Person.city, Purchase.store FROM Person, Purchase WHERE Person.name = Purchase.buyer DROP VIEW City-Store M.P. Johnson, DBMS, Stern/NYU, Sp2004

  13. New topic: Constraints & Triggers (7) • Certain properties we’d like our database to hold • Enforce application semantics • e.g., sid is a key • prevent inconsistencies • e.g., sname has to be a string, age must be < 200 • Modification of tables may break these properties • Constrains enforced by either • Preventing actions causing illegal states • Performing compensating actions • Triggers are one option M.P. Johnson, DBMS, Stern/NYU, Sp2004

  14. Constraints in SQL • Domain constraints • Keys • Foreign keys/referential integrity • Attribute-level constraints • Tuple-level constraints • Global constraints: assertions • The more complex, the harder/more expensive to check and enforce simplest Mostcomplex M.P. Johnson, DBMS, Stern/NYU, Sp2004

  15. Denoting primary keys • In the CREATE TABLE statement, use: • PRIMARY KEY or UNIQUE • Example: • If NAME and ADDRESS are together the primary key, it must be written as: CREATE TABLE MovieStar ( name VARCHAR(30) PRIMARY KEY, address VARCHAR(255), gender CHAR(1) ) CREATE TABLE MovieStar ( name VARCHAR(30), address VARCHAR(255), gender CHAR(1), PRIMARY KEY (name, address) ) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  16. Effects of primary keys • No two tuples may agree on all key atts • Have key  no duplicates at all • No nulls allowed in any of the key atts • Only one primary key is allowed per table • NB: SQL key is slightly different from Relational Model key • In R.M., key fields values  all other field values • In SQL, key field values really are unique • NB: no concept of superkey in SQL • Of course, chosen keys should be minimal (why?) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  17. Multiple keys CREATE TABLE Product ( productID CHAR(10), name CHAR(30), category VARCHAR(20), price INT, UPC CHAR(20) UNIQUE, PRIMARYKEY (productID), UNIQUE (name, category)) There is at most one PRIMARY KEY;there can be many UNIQUE M.P. Johnson, DBMS, Stern/NYU, Sp2004

  18. PRIMARY KEY v. UNIQUE • Can use the UNIQUE keyword (instead of PRIMARY KEY) in (almost) same way • Unique keys are…not unique • NULLs allowed, and multiple NULLs allowed (why?) • Example: • If NAME and ADDRESS are together unique, we can write: CREATE TABLE MovieStar ( name VARCHAR(30) UNIQUE, address VARCHAR(255), gender CHAR(1) ) CREATE TABLE MovieStar ( name VARCHAR(30), address VARCHAR(255), gender CHAR(1), UNIQUE (name, address) ) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  19. Indices & keys • Close affinity between indices and keys • Want to keep values unique (key)  need easy way to check for existing values (index) • In many implementations (like Oracle): creating keys (primary or unique) automatically creates indices • Explicit unique index creation: • In effect, automatically creates key • Index info: USER_INDEXES CREATE UNIQUE INDEX MyIndex on MyTable(a,b,c) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  20. Foreign Key Constraint • ActedIn(ActorName, MovieName) • Movies(MovieName, year) • If MovieName in ActedIn is a foreign key for Movies, then: • The referenced atts must be a key in the ref-ed table • All non-null foreign key values must appear in the referenced table (1) Movies ActedIn (2) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  21. Foreign key example • Recall, ActedIn has FK MovieName... • Movies(MovieName, year) • (Fatal Attraction, 1987) • ActedIn(ActorName, MovieName) • (Michael Douglas, Fatal Attraction) (1) Movies ActedIn (2) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  22. Declaring Foreign Key Constraints • REFERENCES keyword: • Or summarize at end: CREATE TABLE ActedIn ( ActorName VARCHAR(30), MovieName VARCHAR(30) REFERENCES Movies(MovieName) ) CREATE TABLE ActedIn ( ActorName VARCHAR(30), MovieName VARCHAR(30), CONSTRAINT const_name FOREIGN KEY (MovieName) REFERENCES Movies(MovieName) ) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  23. Constraint example • Q: Is this the same as REFERENCES? CREATE TABLE StarrtedIn ( ActorName VARCHAR(30), MovieName VARCHAR(30) CHECK (MovieName IN (SELECT MovieName FROM Movies) ) ) (1) Movies ActedIn (2) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  24. Dangling tuples result from • Can result from: • In StarredIn: insert/update • In Movies: delete/update • Responses: • Reject • Cascade • Set-null M.P. Johnson, DBMS, Stern/NYU, Sp2004

  25. 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 M.P. Johnson, DBMS, Stern/NYU, Sp2004

  26. Cascading and set-null • Cascading: • Applies to last two causes: • Deleting exec • Bad exec update • I.e., loss/change of exec referred to • Strategy: del/change exec referred to del/change the reference to exec • Loss/change of exec referred to  set reference to null M.P. Johnson, DBMS, Stern/NYU, Sp2004

  27. Cascading and set-null • Cascade/set-null choices independent by att: CREATE TABLE Studio ( name CHAR(30) PRIMARY KEY, address VARCHAR(255), presC# int REFERENCES MovieExec(cert#) ON DELETE SET NULL ON UPDATE CASCADE ) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  28. Circular dependencies • Sometimes one table refers to another and the other refers to the first • Neither can be inserted without the other • Soln involves • Transactions (xacts) • Deferring constraint check until end of xact • Constraints can be NOT DEFERABLE (default) or DEFERABLE • DEFERABLE INITIALLY DEFERRED • DEFERABLE INITIALLY IMMEDIATE M.P. Johnson, DBMS, Stern/NYU, Sp2004

  29. Deferring constraints CREATE TABLE Studio ( name CHAR(30) PRIMARY KEY, address VARCHAR(255), presC# INT UNIQUE REFERENCES MovieExec(cert#) DEFERRABLE INITIALLY DEFERRED ) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  30. Constraints • Constraints can be applied to • On attributes • On tuples • Set update and delete actions inde’ly: • ON UPDATE not supported by Oracle CREATE TABLE StarredIn ( ActorName VARCHAR(30), MovieName VARCHAR(30) REFERENCES Movies(MovieName) ON DELETE SET CASCADE ) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  31. Constraining Attribute Values • Constrain invalid values • Any test legal in a WHERE clause • Subqueries (but not in Oracle!), etc. • The constraint is checked whenever the local attributes it references are changed or added name CHAR(30) NOT NULL gender CHAR(1) CHECK (gender IN (‘F’, ‘M’)) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  32. Is this the same as a foreign key? CREATE TABLE Purchase ( prodName CHAR(30) CHECK (prodName INSELECT Product.nameFROM Product), date DATETIME NOT NULL) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  33. Naming domains to constrain values • Can define new domains to use as the attribute type: • Then update our attribute definition... • gender GenderDomain • Note use of VALUE to refer to the att value • Named domains are not supported by Oracle CREATE DOMAIN GenderDomain CHAR(1) CHECK (VALUE IN (‘F’, ‘M’)) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  34. More complex constraints • Among several attributes in one table • Specify at the end of CREATE TABLE CHECK (gender = ‘F’ OR name NOT LIKE ‘Ms.%’) • Checked whenever a tuple of the relation is added or updated. CREATE TABLE Sailors ( sid INTEGER, sname CHAR(10), rating INTEGER, age REAL, PRIMARY KEY (sid), CHECK ( rating >= 1 AND rating <= 10) ) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  35. Adding/deleting named constraints • Add: • Delete: • Constraints info: USER_CONSTRAINTS ALTER TABLE Movie ADD CONSTRAINT Const1 PRIMARY KEY(name) ALTER TABLE Movie DROP CONSTRAINT Const1 M.P. Johnson, DBMS, Stern/NYU, Sp2004

  36. Assertions (7.4) • Constraint on entire database (not table!) • Checked upon any change to DB • Not supported by Oracle • CREATE ASSERTION <name> CHECK (<condition>) • Analogous to our assertions in R.A.: • Studio(Name, Address, PresC#); • MovieExec(Name, Address, Cert#, NetWorth); CREATE ASSERTION RichPres CHECK (NOT EXISTS (SELECT * FROM Studio, MovieExec WHERE presC# = cert# AND netWorth < 10,000,000)) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  37. Another assertion example • Movie(Title, Year, Length, inColor, StudioName, ProducerC#) • Q: Can we write this as a tuple constraint? • Q: Could we if >=  < ? CREATE ASSERTION SumLength CHECK (10000 >= ALL (SELECT SUM(length) FROM Movie GROUP BY studioName)) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  38. Another example • Q: What does this check? CREATE ASSERTION myAssert CHECKNOT EXISTS( SELECT Product.nameFROM Product, PurchaseWHERE Product.name = Purchase.prodNameGROUPBY Product.nameHAVING count(*) > 200) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  39. Different Constraint Types • Q: Why not always use assertions? M.P. Johnson, DBMS, Stern/NYU, Sp2004

  40. Triggers • Constraints state what must remain true • DBMS decides when to check • Triggers are instructions to perform at explicitly specified times • Three aspects: • An event (e.g., update to an attribute) • A condition (e.g., a query to check) • An action (the trigger’s effect) (deletion, update, insertion) • When the event occurs, DBMS checks the constraint, and if it is satisfied, performs the action M.P. Johnson, DBMS, Stern/NYU, Sp2004

  41. Triggers – important points • Can replace old row (result of event) with new row • Action may be performed before or after event • Can refer to old row and new row • WHEN clauses tests whether to continue • Action may be performed either • For each row involved in event • Once per event • Oracle does triggers as PL/SQL programs M.P. Johnson, DBMS, Stern/NYU, Sp2004

  42. Elements of Triggers • Timing of action execution: before, after or instead of triggering event • The action can refer to both the old and new state of the database • Update events may specify a particular column or set of columns • A condition is specified with an optional WHEN clause • The action can be performed either for • once for every tuple or • once for all the tuples that are changed by the database operation M.P. Johnson, DBMS, Stern/NYU, Sp2004

  43. Simple trigger example • R(id, data, last-modified) • data is a large string • Last-modified is a newly added date field • Goal: whenever data is modified, update last-modified date • Could modify all scripts/programs that touch this table • Bad idea • Better: user a trigger CREATE TRIGGER UpdateDateTrigger BEFORE UPDATE OF data ON R REFERENCING NEW ROW AS NewTuple FOR EACH STATEMENT BEGIN NewTuple.last-modified = sysdate; END; M.P. Johnson, DBMS, Stern/NYU, Sp2004

  44. Triggers: Row-level example • MovieExec(name, address, cert#, netWorth) • “If someone decreases a movie executive’s net worth, I want the database to reset itself to the previous net worth.” CREATE TRIGGER NetWorthTrigger AFTER UPDATE OF netWorth ON MovieExec REFERENCING NEW ROW AS NewTuple OLD ROW AS OldTuple FOR EACH ROW WHEN (OldTuple.netWorth>NewTuple.netWorth) UPDATE MovieExec SET netWorth = oldTuple.netWorth WHERE cert# = newTuple.cert#) M.P. Johnson, DBMS, Stern/NYU, Sp2004

  45. Triggers: Table-level example • MovieExec(name, address, cert#, netWorth) • “If someone updates the net worth of one movie exec so that the average net worth of all movie execs becomes less than $50,000, I want the database to reset itself.” CREATE TRIGGER AvgNetWorthTrigger AFTER UPDATE OF netWorth ON MovieExec REFERENCING OLD TABLE AS OldStuff, NEW TABLE AS NewStuff FOR EACH STATEMENT WHEN (50000 > (SELECT AVG(netWorth) FROM MovieExec)) BEGIN DELETE FROM MovieExec WHERE (Name, address, cert#, netWorth) IN NewStuff; INSERT INTO MovieExec (SELECT * FROM OldStuff); END; M.P. Johnson, DBMS, Stern/NYU, Sp2004

  46. End • Examples from sqlzoo.net • For next time: read 8.1-2 M.P. Johnson, DBMS, Stern/NYU, Sp2004

More Related