CSCI 305 Introduction to Database Systems

1. <bookstore> <book category="COOKING"> <title lang="en">Everyday Italian</title> <author>Giada De Laurentiis</author> <year>2005</year> <price>30.00</price> </book> <book category="FICTION"> <title lang="en">Harry Potter</title> <author>J K. Rowling</author> <year>2005</year> <price>29.99</price> </book> <book category="WEB"> <title lang="en">Learning XML</title> <author>Erik T. Ray</author> <year>2003</year> <price>39.95</price> </book> </bookstore> XML SELECT * FROM Students WHERE Grade >= 90 CSCI 305Introduction to Database Systems Professor Brian R. King Bucknell University – Computer Science Dept. Constraints and Triggers in SQL

2. SQL provides a mechanism for active elements • Execute under certain conditions • Useful for maintaining integrity in your data • Restriction of values • Referential integrity • Auto assignment of values

3. Constraints and Triggers • Constraint • A relationship among data elements that DBMS is required to enforce • Examples: • key constraints (PRIMARY KEY) • UNIQUE • NOT NULL • Triggers: • Executed when a specified condition occurs, such as an insertion of a tuple • Often easier to implement than complex constraints

4. Kinds of Constraints • Keys • Foreign key • Referential integrity • Value-based constraints • Constrain values of a particular attribute • Tuple-based constraints • Relationship among components within a tuple • Assertions • Any SQL boolean expression • Global constraint

5. Keys • We've seen these constraints • In your schema, use PRIMARY KEY next to attribute • OR, use UNIQUE (allows NULL) • CREATE TABLE Beers ( • name CHAR(20) PRIMARY KEY • manf CHAR(20) • );

6. Multiattribute Keys • If you have multiple keys, must list them separately as a schema element • CREATE TABLE Sells ( • bar CHAR(20), • beer VARCHAR(20), • price FLOAT, • PRIMARY KEY(bar, beer) • );

7. Foreign Keys • Values appearing in attributes of one relation must appear together in certain attributes of another relation • Example: • Sells(bar,beer,price) • Beers(name,manf) • We might want to enforce the rule that any beers entered in the Sells relation also appears in Beers.name • Beers.name PRIMARY KEY • Sells.beer FOREIGN KEY that REFERENCES Beers.name

8. Foreign Keys in SQL • In schema, specify foreign key • after an attribute (for one-attribute keys only) with REFERENCES R(attr) • As an element of the schema:FOREIGN KEY (a1, a2, …) REFERENCES R(b1, b2, …) • Referenced attributes must be declared PRIMARY KEY or UNIQUE

9. Example: With Attribute • CREATE TABLE Beers ( name CHAR(20) PRIMARY KEY,manf CHAR(20)); • CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20) REFERENCES Beers(name), price FLOAT);

10. Example: As Schema Element • CREATE TABLE Beers ( name CHAR(20),manf CHAR(20), PRIMARY KEY (name)); • CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20), price FLOAT, FOREIGN KEY(beer) REFERENCES Beers(name));

11. Foreign-Key Constraints • Suppose R has a primary key, S has a foreign key referring to R's primary • Types of violations • An insert or update to S introduces values not found in R • A deletion or update to R causes some tuples of S to "dangle"

12. Maintaining referential integrity • Example: suppose R = Beers, S = Sells • An insert or update to Sells (with foreign key) that introduces a nonexistent beer must be rejected • A deletion or update to Beers (with primary key) that removes a beer value found in Sells can be handled in one of three ways…

13. Referential Integrity (cont.) • Default: • Reject the modification because it is being used • Cascade: • Make the same change in Sells • Delete a beer? Then delete the Sells tuple • Update a beer? Then change the value in Sells • SET NULL • Change the beer in Sells to NULL

14. Example: Cascade If our behavior is set to CASCADE: • Delete the Bud tuple from Beers: • Then, delete all tuples from the Sells relations that have beer = 'Bud' • Update the Bud tuple by changing 'Bud' to 'Budweiser' • Then, change all Sells tuples with beer = 'Bud' to beer = 'Budweiser'

15. Example: Set NULL If our behavior is set to SET NULL • Delete the Bud tuple from Beers • Change all tuples of Sells that have beer = 'Bud' to have beer = NULL • Update the Bud tuple by changing 'Bud' to 'Budweiser' • Same change as for deletion

16. Choosing a Policy in SQL • FOREIGN KEY (attr) REFERENCES tbl(attr) [ON DELETE (CASCADE | SET NULL)] [ON UPDATE (CASCADE | SET NULL)]

17. Example • CREATE TABLE Beers ( name CHAR(20),manf CHAR(20), PRIMARY KEY (name)); • CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20), price FLOAT, FOREIGN KEY(beer) REFERENCES Beers(name) ON DELETE SET NULL ON UPDATE CASCADE);

18. ALTER TABLE • Reminder – you do not need to create an entirely new table. You can alter an existing one • ALTER TABLE tbl_name ADD PRIMARY KEY (col_name,..)| ADD FOREIGN KEY (col_name,…) REFERENCES …| DROP PRIMARY KEY| DROP FOREIGN KEY

19. MySQL and referential integrity FROM MySQL Reference Manual: • For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it.

20. …MySQL… • Do keep in mind that these benefits come at the cost of additional overhead for the database server to perform the necessary checks. Additional checking by the server affects performance, which for some applications may be sufficiently undesirable as to be avoided if possible. (Some major commercial applications have coded the foreign key logic at the application level for this reason.)

21. …MySQL • MySQL gives database developers the choice of which approach to use. If you don't need foreign keys and want to avoid the overhead associated with enforcing referential integrity, you can choose another storage engine instead, such as MyISAM. (For example, the MyISAM storage engine offers very fast performance for applications that perform only INSERT and SELECT operations. • See http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html

22. Exercise 7.1.1a CREATE TABLE Movies ( title CHAR(100), year INT, length INT, genre CHAR(10), studioName CHAR(30), producerC# INT, PRIMARY KEY (title, year), FOREIGN KEY (producerC#) REFERENCES MovieExec(cert#) )

23. Exercise 7.1.1b CREATE TABLE Movies ( title CHAR(100), year INT, length INT, genre CHAR(10), studioName CHAR(30), producerC# INT REFERENCES MovieExec(cert#) ON DELETE SET NULL ON UPDATE SET NULL, PRIMARY KEY (title, year), )

24. Exercise 7.1.1c CREATE TABLE Movies ( title CHAR(100), year INT, length INT, genre CHAR(10), studioName CHAR(30), producerC# INT REFERENCES MovieExec(cert#) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (title, year), )

25. Exercise 7.1.1d CREATE TABLE StarsIn ( movieTitle CHAR(100) REFERENCES Movie(title), movieYear INT, starName CHAR(30), PRIMARY KEY (movieTitle,movieYear,starName), )

26. Attribute-Based Checks • Constraints on a value of a particular attribute • Example: • NOT NULL • Do NOT allow attributes with this qualifier to be NULL • Example: If I do not want any price in Sells(bar,beer,price) to be null: • CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20) REFERENCES Beers(name), price FLOAT NOT NULL); • (MySQL supports this!)

27. CHECK constraints • Add CHECK(condition) to the declaration for the attribute • The condition may freely use the name of the attribute • However, any other relation or attribute name must be in a subquery

28. Example: Attribute-Based Check • CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20) CHECK (beer IN (SELECT name FROM Beers)), price FLOAT CHECK (price <= 5.00));

29. Timing of checks • Attribute-based checks are performed only when a value for that attribute is inserted or updated • If CHECK fails, then the tuple is not inserted (or updated) • Note: CHECK is ONLY performed when update / insert is made on this schema, even if CHECK refers to another relation • EXAMPLE: CHECK (beer IN (SELECT name FROM Beers) is NOT checked if a beer is deleted from Beers • Foreign Keys do this check

30. SET check • You can restrict components to be only a specific set of values • BOOK: • gender CHAR(1) CHECK (gender IN ('F','M')), • MySQL: • gender SET('F','M'), • Both accomplish the same thing

31. multi-attribute CHECK • Need a check that requires multiple attributes? • Add a CHECK (<condition>) as a schema element • Condition can refer to any attribute of the relation • Other attributes require a subquery • Like single-attribute check, checked on insert or update only

32. Example: Tuple-based CHECK • Only Joe's Bar can sell beer for more than $5 • CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20), price FLOAT, CHECK (bar = 'Joe''s Bar' OR price <= 5.00));

33. Exercise 7.2.1a • year INT CHECK (year >= 1915) • length INT CHECK (length >= 60 AND length <= 250) • studioNameSET('Disney','Fox','MGM','Paramount')

34. Naming Constraints • Book discusses using keyword CONSTRAINT in a schema • This is OPTIONAL, and only makes sense if you want to name your constraints

35. HW3.SQL • You can resubmit your HW3.SQL before Thursday morning • Why? EXCELLENT PREPARATION FOR EXAM! • You will receive 50% of your lost credit back IFF you also include, for EVERY SQL statement that had the incorrect output, a clear explanation (as /* */ comment) of what was wrong with your query and why, and how you corrected it. • i.e. Do NOT just copy my source!!! • DUE: Thursday before exam! Submitted on SVN!

36. Material after Exam #1 begins here

37. Enforcing integrity on an entire database: CREATE ASSERTION CREATE TRIGGER

38. Review of Constraints Our constraints thus far have been on: • a single attribute of a tuple inserted or updated • Add CHECK(condition) to the declaration for the attribute • Through an attr definition (e.g. SET, UNIQUE, NOT NULL, PRIMARY KEY, REFERENCES, etc.) • a complete tuple inserted or updated • Add CHECK(condition) as a schema element • (PRIMARY | FOREIGN) KEY as schema element • Useful for ensuring that only specific tuples are allowed into a table

39. Subqueries and CHECK constraints • We learned that we could allow subqueries as part of the CHECK condition • Example: only allow beers in Sells(beer, bar, price) as long as the beer is in Beers relation. • beer CHAR(20) CHECK (beer IN (SELECT name FROM Beers)) • PROBLEM: what if a beer is removed from Beers? • Manually make sure that you have cross checks everywhere? HOW??? • CREATE TABLE Beers ( name CHAR(20) CHECK (name IN (SELECT beer FROM Sells) • Makes no sense! • We saw one solution – FOREIGN KEY • Not all DBMSs implement them

40. Attribute and tuple constraints • Very limited capabilities • For an UPDATE, single attribute CHECK not verified if an attribute does not change • If CHECK condition mentions other relation in a subquery, and data in subquery changes, this does not change the data that was already verified. • If you want to constrain data across tables in a database schema, attribute- and tuple-checks are very limited • SOLUTION: Assertions and Triggers

41. Assertions • An assertion, by definition, is a statement that must be true at all times • An assertion in SQL is a boolean-valued SQL expression that must be true at all times • Defined by: • CREATE ASSERTION assertNameCHECK cond; • Condition may refer to any relation or attribute in the entire schema • Created as an element of the database schema • At same level as CREATE TABLE

42. Example: Assertion • A common approach: • Specify a query that selects tuples that violate the desired condition, and use this with NOT EXISTS • Example: • No bar may charge an average price of more than $5 • Schema: • Sells(bar, beer, price); • CREATE ASSERTION NoRipOffs • CHECK • (NOT EXISTS • (SELECT bar FROM Sells GROUP BY bar HAVING 5.00 < AVG(price))

43. Why is this better than a tuple-based constraint? • If a low priced beer is no longer sold, it is deleted, and the average price is increased • What if this deletion yields average price > 5? • Our tuple-based constraints only checked on INSERT and DELETE

44. Example: Assertion • Any approach that yields a boolean condition is a valid assertion • Example: • Let's make sure there are not more bars than there are drinkers • Schema: • Drinkers(name, addr, phone) • Bars(name, addr, license) • CREATE ASSERTION MoreDrinkers • CHECK ( • (SELECT COUNT(*) FROM Bars) <= (SELECT COUNT(*) FROM Drinkers));

45. Comparison of Constraints

46. Exercise 7.4.1a • CREATE ASSERTION CHECK • (NOT EXISTS • (SELECT maker • FROM Product NATURAL JOIN PC AS P • WHERE maker IN • (SELECT L.maker • FROM Product NATURAL JOIN Laptop AS L • GROUP BY L.maker • ) • ) • );

47. Assertions – not efficient! • An assertion must be checked after every database modification to any relation in the database • Extremely powerful, but extremely inefficient • Optimize assertion to check only affected relations and operations? • The DBMS usually can't do this • Attribute and tuple-based checks are checked at known times (insert and update only), but not that powerful • Useful for ensuring only valid tuples get added, or updates don't violate said constraints

48. MySQL Does not support CREATE ASSERTIONS :-b

49. A solution that addresses the inefficiency of assertions: Triggers Triggers let you decide when to check for any condition

50. Event-Condition-Action Rules • Another name for a "trigger" is an ECA rule, or event-condition-action rule • Event: • Typically a type of database modification • Example: BEFORE INSERT ON Sells • Condition: • Any SQL boolean expression • Action: • Any SQL statements • NOTE: In MySQL, the condition is set up in the Action • Ex: DELETE FROM … WHERE cond