1 / 10

Referential Integrity checks, Triggers and Assertions

Referential Integrity checks, Triggers and Assertions. Examples from Chapter 7 of Database Systems: the Complete Book Garcia-Molina, Ullman, & Widom. Movie Database. Movie (title, year, length, inColor, studioName, producerC#) StarsIn (movieTitle, movieYear, starName)

balthasar
Download Presentation

Referential Integrity checks, Triggers and Assertions

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. Referential Integrity checks, Triggers and Assertions Examples from Chapter 7 of Database Systems: the Complete Book Garcia-Molina, Ullman, & Widom

  2. Movie Database • Movie (title, year, length, inColor, studioName, producerC#) • StarsIn (movieTitle, movieYear, starName) • MovieStar(name, address, gender, birthdate) • MovieExec(name, address, cert#, netWorth) • Studio(name, address, presC#)

  3. Specifying Update/Delete Handling • CREATE TABLE Studio ( name char(30) primary key, address varchar(255), presC# int references MovieExec(cert#) on delete set null on update cascade • Deleting the corresponding MovieExec record sets presC# to Null • Updating the MovieExec record modifies presC#

  4. Not-null constraint • CREATE TABLE Studio ( name char(30) primary key, address varchar(255), presC# int references MovieExec(cert#) Not Null on update cascade • No longer possible to follow set-null policy on deletes to MovieExec

  5. Attribute-value constraint • CREATE TABLE Studio ( name char(30) primary key, address varchar(255), presC# int references MovieExec(cert#) Check (presC# >= 100000) • Insertions or updates will fail if they violate the check condition

  6. Attribute-value constraint • CREATE TABLE Studio ( name char(30) primary key, address varchar(255), presC# int references MovieExec(cert#) Check (presC# in (Select cert# from MovieExec) • Insertions or updates on this table will fail unless the new presC# matches an existing MovieExec • However, updates or deletes on MovieExec that falsify the condition will not be stopped.

  7. Tuple-based constraint • CREATE TABLE MovieStar ( name char(30) primary key, address varchar(255), gender char(1), birthdate date, Check (gender=‘F’ or name NOT LIKE “Ms%”) • Check condition is a relationship between two different attributes

  8. Assertion • CREATE Assertion RichPres CHECK (NOT EXISTS (SELECT * FROM Studio, MovieExec WHERE Studio.presC#=MovieExec.cert# AND MovieExec.netWorth<10000000)) • Check condition requires an SQL statement involving multiple tables • Condition says that any president of a movie studio must be worth at least $10,000,000

  9. Another Assertion • CREATE Assertion SumLength CHECK (10000>=ALL (SELECT SUM (length) FROM Movie GROUP BY StudionName)) • Assertion says that the lengths of all movies made by any studio must be no more than 10,000 minutes • Note the >= ALL quantifier!

  10. Trigger • CREATE Trigger NetWorthTrigger AFTER UPDATE OF netWorth ON MovieExec REFERENCING OLD ROW AS OldTuple NEW ROW AS NewTuple FOR EACH ROW WHEN (OldTuple.netWorth>NewTuple.netWorth) UPDATE MovieExec SET netWorth = OldTuple.netWorth WHERE cert#=newTuple.cert# • Prevents reducing the net worth of a movie exec • Note this cannot be expressed as a constraint on tuple values!

More Related