<bookstore> - PowerPoint Presentation

Slide1

<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>

CSCI 305

Introduction to Database Systems

Professor Brian R. KingBucknell University – Computer Science Dept.

Constraints and Triggers in SQL

SELECT * FROM Students WHERE Grade >= 90

XMLSlide2

SQL provides a mechanism for active elementsExecute under certain conditionsUseful for maintaining integrity in your dataRestriction of valuesReferential integrityAuto assignment of valuesSlide3

Constraints and TriggersConstraintA relationship among data elements that DBMS is required to enforceExamples:key constraints (PRIMARY KEY)UNIQUENOT NULLTriggers:Executed when a specified condition occurs, such as an insertion of a tupleOften easier to implement than complex constraintsSlide4

Kinds of ConstraintsKeysForeign key Referential integrityValue-based constraintsConstrain values of a particular attributeTuple-based constraintsRelationship among components within a tupleAssertionsAny SQL boolean expressionGlobal constraintSlide5

KeysWe've seen these constraintsIn your schema, use PRIMARY KEY next to attributeOR, use UNIQUE (allows NULL)CREATE TABLE Beers ( name CHAR(20) PRIMARY KEY manf CHAR(20));Slide6

Multiattribute KeysIf you have multiple keys, must list them separately as a schema elementCREATE TABLE Sells ( bar CHAR(20), beer VARCHAR(20), price FLOAT, PRIMARY KEY(bar, beer));Slide7

Foreign KeysValues appearing in attributes of one relation must appear together in certain attributes of another relationExample: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.nameBeers.name  PRIMARY KEYSells.beer  FOREIGN KEY that REFERENCES Beers.nameSlide8

Foreign Keys in SQLIn schema, specify foreign keyafter 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 UNIQUESlide9

Example: With AttributeCREATE TABLE Beers ( name CHAR(20) PRIMARY KEY, manf CHAR(20));CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20) REFERENCES Beers(name), price FLOAT);Slide10

Example: As Schema ElementCREATE 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));Slide11

Foreign-Key ConstraintsSuppose R has a primary key, S has a foreign key referring to R's primaryTypes of violationsAn insert or update to S introduces values not found in RA deletion or update to R causes some tuples of S to "dangle"Slide12

Maintaining referential integrityExample: suppose R = Beers, S = SellsAn insert or update to Sells (with foreign key) that introduces a nonexistent beer must be rejectedA deletion or update to Beers (with primary key) that removes a beer value found in Sells can be handled in one of three ways…Slide13

Referential Integrity (cont.)Default: Reject the modification because it is being usedCascade:Make the same change in SellsDelete a beer? Then delete the Sells tupleUpdate a beer? Then change the value in SellsSET NULLChange the beer in Sells to NULLSlide14

Example: CascadeIf 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'Slide15

Example: Set NULLIf our behavior is set to SET NULLDelete the Bud tuple from BeersChange all tuples of Sells that have beer = 'Bud' to have beer = NULLUpdate the Bud tuple by changing 'Bud' to 'Budweiser'Same change as for deletionSlide16

Choosing a Policy in SQLFOREIGN KEY (attr) REFERENCES tbl(attr) [ON DELETE (CASCADE | SET NULL)] [ON UPDATE (CASCADE | SET NULL)]Slide17

ExampleCREATE 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);Slide18

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

MySQL and referential integrityFROM 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.Slide20

…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.)Slide21

…MySQLMySQL 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.htmlSlide22

Exercise 7.1.1aCREATE 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#))Slide23

Exercise 7.1.1bCREATE 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),)Slide24

Exercise 7.1.1cCREATE 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),)Slide25

Exercise 7.1.1dCREATE TABLE StarsIn ( movieTitle CHAR(100) REFERENCES Movie(title), movieYear INT, starName CHAR(30), PRIMARY KEY (movieTitle,movieYear,starName),)Slide26

Attribute-Based ChecksConstraints on a value of a particular attributeExample:NOT NULLDo NOT allow attributes with this qualifier to be NULLExample: 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!)Slide27

CHECK constraintsAdd CHECK(condition) to the declaration for the attributeThe condition may freely use the name of the attributeHowever, any other relation or attribute name must be in a subquerySlide28

Example: Attribute-Based CheckCREATE TABLE Sells ( bar CHAR(20), beer CHAR(20) CHECK (beer IN (SELECT name FROM Beers)), price FLOAT CHECK (price <= 5.00));Slide29

Timing of checksAttribute-based checks are performed only when a value for that attribute is inserted or updatedIf 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 relationEXAMPLE: CHECK (beer IN (SELECT name FROM Beers) is NOT checked if a beer is deleted from BeersForeign Keys do this checkSlide30

SET checkYou can restrict components to be only a specific set of valuesBOOK:gender CHAR(1) CHECK (gender IN ('F','M')),MySQL:gender SET('F','M'),Both accomplish the same thingSlide31

multi-attribute CHECKNeed a check that requires multiple attributes?Add a CHECK (<condition>) as a schema elementCondition can refer to any attribute of the relationOther attributes require a subqueryLike single-attribute check, checked on insert or update onlySlide32

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

Exercise 7.2.1ayear INT CHECK (year >= 1915)length INT CHECK (length >= 60 AND length <= 250)studioName SET('Disney','Fox','MGM','Paramount')Slide34

Naming ConstraintsBook discusses using keyword CONSTRAINT in a schemaThis is OPTIONAL, and only makes sense if you want to name your constraintsSlide35

HW3.SQLYou can resubmit your HW3.SQL before Thursday morningWhy? 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!Slide36

Material after Exam #1 begins hereSlide37

Enforcing integrity on an entire database:CREATE ASSERTIONCREATE TRIGGERSlide38

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

Subqueries and CHECK constraintsWe learned that we could allow subqueries as part of the CHECK conditionExample: 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 KEYNot all DBMSs implement themSlide40

Attribute and tuple constraintsVery limited capabilitiesFor an UPDATE, single attribute CHECK not verified if an attribute does not changeIf 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 limitedSOLUTION: Assertions and TriggersSlide41

AssertionsAn assertion, by definition, is a statement that must be true at all timesAn assertion in SQL is a boolean-valued SQL expression that must be true at all timesDefined by:CREATE ASSERTION assertNameCHECK cond;Condition may refer to any relation or attribute in the entire schemaCreated as an element of the database schemaAt same level as CREATE TABLESlide42

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

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 increasedWhat if this deletion yields average price > 5?Our tuple-based constraints only checked on INSERT and DELETESlide44

Example: AssertionAny approach that yields a boolean condition is a valid assertionExample:Let's make sure there are not more bars than there are drinkersSchema:Drinkers(name, addr, phone)Bars(name, addr, license)CREATE ASSERTION MoreDrinkersCHECK ( (SELECT COUNT(*) FROM Bars) <= (SELECT COUNT(*) FROM Drinkers));Slide45

Comparison of ConstraintsSlide46

Exercise 7.4.1aCREATE 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 ) ) );Slide47

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

MySQLDoes not support CREATE ASSERTIONS:-bSlide49

A solution that addresses the inefficiency of assertions:TriggersTriggers let you decide when to check for any conditionSlide50

Event-Condition-Action RulesAnother name for a "trigger" is an ECA rule, or event-condition-action ruleEvent:Typically a type of database modificationExample: BEFORE INSERT ON SellsCondition:Any SQL boolean expressionAction:Any SQL statementsNOTE: In MySQL, the condition is set up in the ActionEx: DELETE FROM … WHERE condSlide51

MySQL TriggersCREATE TRIGGER triggerName(BEFORE | AFTER)(INSERT | DELETE | UPDATE) ON tblNameFOR EACH ROWstatement;Refer to attributes using aliases OLD and NEWOLD.attr refers to attr of existing row before updating or deletionNEW.attr

refers to attr of a new row to be inserted OR existing row after updatingSlide52

ExampleSchema:Beers(name, manf)Sells(bar, beer, price)Suppose we want to automatically insert a beer to Beers if a tuple is added to Sells with an unknown beerCREATE TRIGGER BeerTrigAFTER INSERT ON SellsFOR EACH ROW INSERT INTO Beers(name) VALUES NEW.beer;Slide53

Block of statements in triggerBody of trigger can be a compound statementSyntax:BEGIN statement_listENDThe statement_list is a list of SQL statements, each terminated by a semicolonThis is a problem when using the client program…Slide54

delimiterdelimiter $$CREATE TRIGGER ……FOR EACH ROWBEGIN stmt_1; … stmt_n; END$$delimiter ;Slide55

MySQL differences from bookNo REFERENCING clauseNew row is always NEW, old is always OLDNo WHEN clauseStandard SQL has a WHEN clause. Usually not necessary because the condition can be included in WHERE clause for DELETE and UPDATEFor INSERT, this is not possibleWe can use an IF / THEN / END IF to get the same effectSlide56

Schema:Sells(bar, beer, price)Automatically maintain a list of bars called RipOff(bar) that raise the price of any beer by more than $1CREATE TRIGGER PriceTrigAFTER UDPATE ON SellsFOR EACH ROWBEGIN IF (NEW.price – OLD.price > 1.00) THEN INSERT INTO RipOff VALUES NEW.bar; END IF;END;Slide57

Some good examplesSchema:Student(id,name,class)Create a trigger that automatically places a backup of each entry deleted into a backup tableSchema:Student_Backup(id,name,class,deleteTime:TIME)CREATE TRIGGER StudentDeleteTrigBEFORE DELETE ON StudentFOR EACH ROWBEGIN INSERT INTO Student_Backup VALUES(OLD.id,OLD.name,OLD.class,CURTIME());END$$(For these examples, I'll assume using the prompt rather than a script, and set END to trail with $$Slide58

Useful functionsSELECT CURTIME();11:34:25SELECT CURDATE();2011-02-28SELECT LOCALTIME();2011-02-28 11:34:25SELECT CURRENT_USER();brk009SELECT USER();brk009@linuxremote1.eg.bucknell.eduSlide59

Another exampleSchemaStudent(id,name,SAT:INT,placement:VARCHAR);Set a trigger to automatically assign placement based on SAT each time a new record is about to be insertedCREATE TRIGGER StudentPlacementTrigBEFORE INSERT ON StudentFOR EACH ROWBEGIN IF NEW.SAT < 1200 THEN SET NEW.placement = "REJECT"; ELSEIF NEW.SAT < 1400 THEN SET NEW.placement = "PROVISIONAL ACCEPT"; ELSE SET NEW.placement = "ACCEPT"; END IF;END$$Slide60

SchemaStudent(id,name,class);For each insertion, add an entry of the user id to a log that perform the DB modificationStudentInsertLog(userID,description,time);CREATE TRIGGER StuInsertLogTriggerAFTER INSERT ON StudentFOR EACH ROWBEGIN INSERT INTO StudentInsertLog VALUES (CURRENT_USER(), CONCAT('Insert Student ', NEW.id, ' ', NEW.name, ' ', NEW.class), CURTIME());END$$Slide61

Examplehttp://www.roseindia.net/sql/trigger/index.shtmlhttp://www.roseindia.net/mysql/mysql5/triggers.shtml