CSCI 204 Introduction to Computer Science II

SSEL E WWHE LEECCTT * Everyday ItalianHERRE E GGr * FFRROOM Giada De Laurentiis raadde M SStu 2005 e >>= tudden 30.00 = 990 entts s

0 Harry Potter J K. Rowling 2005 29.99 Learning XML Erik T. Ray 2003 39.95

XML CSCI 305 Introduction to Database Systems Constraints and Triggers in SQL Professor Brian R. King Bucknell University Computer Science Dept. 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 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 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 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) ); 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) ); 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 PRIMARY KEY FOREIGN KEY that REFERENCES 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 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

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

); 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" 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 Referential Integrity (cont.) 1. Default: Reject the modification because it is being used 2. Cascade: Make the same change in Sells

Delete a beer? Then delete the Sells tuple Update a beer? Then change the value in Sells 3. SET NULL Change the beer in Sells to NULL 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' 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

Choosing a Policy in SQL FOREIGN KEY (attr) REFERENCES tbl(attr) [ON DELETE (CASCADE | SET NULL)] [ON UPDATE (CASCADE | SET NULL)] 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 ); 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 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.

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.) 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 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#) ) 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), ) 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), ) Exercise 7.1.1d CREATE TABLE StarsIn ( movieTitle CHAR(100) REFERENCES Movie(title), movieYear INT, starName

CHAR(30), PRIMARY KEY (movieTitle,movieYear,starName), ) 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!) 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

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) ); 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 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 multi-attribute CHECK Need a check that requires multiple attributes? Add a CHECK () 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 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) );

Exercise 7.2.1a year INT CHECK (year >= 1915) length INT CHECK (length >= 60 AND length <= 250) studioName SET('Disney','Fox','MGM','Paramount') Naming Constraints Book discusses using keyword CONSTRAINT in a schema This is OPTIONAL, and only makes sense if you want to name your constraints

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! Material after Exam #1 begins here

Enforcing integrity on an entire database: CREATE ASSERTION CREATE TRIGGER 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 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 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 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 assertName CHECK 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 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))

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 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) ); Comparison of Constraints


FROM Product NATURAL JOIN Laptop AS L GROUP BY L.maker ) ) ); 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 MySQL Does not support CREATE ASSERTIONS

:-b A solution that addresses the inefficiency of assertions: Triggers Triggers let you decide when to check for any condition Event-Condition-Action Rules Another name for a "trigger" is an ECA rule, or eventcondition-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 MySQL Triggers CREATE TRIGGER triggerName (BEFORE | AFTER) (INSERT | DELETE | UPDATE) ON

tblName FOR EACH ROW statement; Refer to attributes using aliases OLD and NEW OLD.attr refers to attr of existing row before updating or deletion NEW.attr refers to attr of a new row to be inserted OR existing row after updating Example Schema: 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 beer CREATE TRIGGER BeerTrig AFTER INSERT ON Sells FOR EACH ROW INSERT INTO Beers(name) VALUES; Block of statements in trigger Body of trigger can be a compound statement Syntax: BEGIN statement_list

END The statement_list is a list of SQL statements, each terminated by a semicolon This is a problem when using the client program delimiter delimiter $$ CREATE TRIGGER FOR EACH ROW BEGIN stmt_1; stmt_n;

END$$ delimiter ; MySQL differences from book No REFERENCING clause New row is always NEW, old is always OLD No WHEN clause Standard SQL has a WHEN clause. Usually not necessary because the condition can be included in WHERE clause for DELETE and UPDATE For INSERT, this is not possible We can use an IF / THEN / END IF to get the same

effect Schema: Sells(bar, beer, price) Automatically maintain a list of bars called RipOff(bar) that raise the price of any beer by more than $1 CREATE TRIGGER PriceTrig

AFTER UDPATE ON Sells FOR EACH ROW BEGIN IF (NEW.price OLD.price > 1.00) THEN INSERT INTO RipOff VALUES; END IF; END; Some good examples Schema: Student(id,name,class) Create a trigger that automatically places a backup of each entry deleted into a backup table

Schema: Student_Backup(id,name,class,deleteTime:TIME) CREATE TRIGGER StudentDeleteTrig BEFORE DELETE ON Student FOR EACH ROW BEGIN INSERT INTO Student_Backup VALUES(,,OLD.class,CURTIME());

END$$ (For these examples, I'll assume using the prompt rather than a script, and set END to trail with $$ Useful functions SELECT CURTIME(); 11:34:25 SELECT CURDATE(); 2011-02-28 SELECT LOCALTIME(); 2011-02-28 11:34:25

SELECT CURRENT_USER(); brk009 SELECT USER(); [email protected] Another example Schema Student(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 inserted CREATE TRIGGER StudentPlacementTrig BEFORE INSERT ON Student


END IF; END$$ Schema Student(id,name,class); For each insertion, add an entry of the user id to a log that perform the DB modification StudentInsertLog(userID,description,time);

CREATE TRIGGER StuInsertLogTrigger AFTER INSERT ON Student FOR EACH ROW BEGIN INSERT INTO StudentInsertLog VALUES (CURRENT_USER(), CONCAT('Insert Student ',, ' ',, ' ', NEW.class), CURTIME());

END$$ Example tml ers.shtml

Recently Viewed Presentations

  • Communication in times of crisis: hazardous tool or

    Communication in times of crisis: hazardous tool or

    Anne-Marie Gagné, Ph.D. Professor, TELUQ. Is communication in times of crisis important? Companies must be able to respond to the emergency. Companies use various means and techniques to prepare for a potential . crisis. Crisis management is 80 % communication
  • Radboud University Nijmegen

    Radboud University Nijmegen

    Institute for Management Research / European Master in System Dynamics. EURO 2015 Glasgow 12-15 July 2015. ... Areas of interest (AOIs) Pictures of eye fixations are processed. Area viewed > 50% of participants important area, and then defined as AOI.
  • PSSA Precodes Quick Tips

    PSSA Precodes Quick Tips

    Precodesfor PSSAQuick Tips. Thank you for taking time out of your busy schedules to review this webinar on the Precodes for the PSSAs. The Division of Assessment and Accountability (DAA) in collaboration with the Office of Data Quality (ODQ) are...
  • CS455 GUI Design and Implementation

    CS455 GUI Design and Implementation

    CS774 Human-Computer Interaction Lecturer: Roger D. Eastman ... Examples of GUI bloopers Goals of HCI design HCI as design, implementation, evaluation Course overview Homework 1 End of class review For next class Read Sneiderman, start Norman Visit UI Hall of...
  • Qualitative Research Designs

    Qualitative Research Designs

    QUALITATIVE RESEARCH DESIGNS Professor Lisa High ... Experiencing "Gestalt Change" Philosophy and Qualitative Research DESIGN OF QUALITATIVE STUDIES Design of Qualitative Research Characteristics of Qualitative Research Design Qualitative Designs Qualitative Design Features Qualitative ...
  • Structure of Wood Society of Wood Science and

    Structure of Wood Society of Wood Science and

    SWST Teaching Unit 1 Slide Set 2 SWST Teaching Unit 1 Slide Set 2 SOFTWOODS Now you know the structure of wood is quite complicated, especially the structure of hard-woods. The structure of soft-woods is much simpler. Here is a...
  • BIS2 -

    BIS2 -

    Technical aspects vs. Innovation challenges. Summary and input for discussion: Technical support. for privacy should be understood as an innovation driver/asset, not an obstacle!
  • Guided Notes about Seawater

    Guided Notes about Seawater

    Guided Notes about Seawater Chapter 15, Section 2 1. Seawater is a solution of about 96.5% water and 3.5% dissolved salts. The most abundant salt in seawater is sodium chloride (NaCl).