Outline Active elements CS 235: Maintain database integrity and - PDF document

Outline • Active elements CS 235: – Maintain database integrity and consistency. – Part of database schema. Introduction to Databases • Constraints Svetlozar Nestorov Lecture Notes #13 Constraints Constraint Types • Restrictions on the data in your database. 1. Primary key declarations (already covered). • Commercial relational systems allow much 2. Foreign-keys = referential integrity constraints. more fine-tuning of constraints than do the 3. Attribute- and tuple-based checks = constraints modeling languages we learned earlier. within relations. • In essence: SQL programming is used to 4. SQL Assertions = global constraints. describe constraints. – Not found in MySQL. 5. MySQL Triggers. – A substitute for assertions. Foreign Keys Example CREATE TABLE Beers ( • In relation R a clause that attribute A references name CHAR(20) PRIMARY KEY, S(B) says that whatever values appear in the A manf CHAR(20) column of R must also appear in the B column of ); relation S . • B must be declared the primary key (or unique) CREATE TABLE Sells ( for S . bar CHAR(20), – Why is this restriction necessary? beer CHAR(20) REFERENCES Beers(name) , price REAL ); 1

Alternative Declaration Foreign Keys in MySQL • Both the referenced and referencing • Add another element declaring the foreign key, tables must be of type InnoDB. as: CREATE TABLE Sells ( – Default type is MyISAM (indexed sequential bar CHAR(20), access method) beer CHAR(20), • The FOREIGN KEY syntax must be price REAL, used. FOREIGN KEY (beer) REFERENCES Beers(name) ); • In the referenced table, there must be an • Extra element essential if the foreign key is more index on the referenced columns than one attribute. – PRIMARY KEY or UNIQUE create one • MySQL recognizes only this declaration . automatically. MySQL Example Foreign Key Constraint Violations CREATE TABLE Beers ( 1. Insert or update a Sells tuple so it refers to a name CHAR(20) PRIMARY KEY, nonexistent beer. manf CHAR(20) Always rejected. • ) TYPE = InnoDB; 2. Delete or update a Beers tuple that has a beer value some Sells tuples refer to: CREATE TABLE Sells ( a) Default : reject the modification. bar CHAR(20), b) Cascade : Ripple changes to referring Sells beer CHAR(20), tuple . price REAL, c) Set Null : Change referring tuples to have FOREIGN KEY (beer) REFERENCES Beers(name) NULL in referring components. ) TYPE = InnoDB; Example (Cascade) Example (Set-Null) • Delete Bud. • Delete Bud. • Cascade deletes all Sells tuples that mention • Set-null makes all Sells tuples with Bud in Bud. the beer component have NULL there. • Update Bud to Budweiser. • Update Bud to Budweiser. • Change all Sells tuples with Bud in beer column • Set-null makes all Sells tuples with Bud in to be Budweiser. the beer component have NULL there. 2

Selecting a Policy Attribute-Based Checks • Add ON [DELETE, UPDATE] [CASCADE, SET NULL] to • Follow an attribute by a condition that must hold foreign key declaration. for that attribute in each tuple of its relation. CREATE TABLE Sells ( • CHECK (condition). bar CHAR(20), beer CHAR(20), – Condition may involve the checked attribute. price REAL, – Other attributes and relations may be involved, but FOREIGN KEY (beer) REFERENCES Beers(name) only in subqueries. ON DELETE SET NULL ON UPDATE CASCADE – MySQL: CHECK parsed but ignored . ); • Condition is checked only when the associated • Correct policy is a design decision. attribute changes (i.e., an insert or update – E.g ., what does it mean if a beer goes away? What if a beer occurs). changes its name? Example Tuple-Based Checks CREATE TABLE Sells ( • Separate element of table declaration. bar CHAR(20), beer CHAR(20) CHECK (beer IN ( • Form: like attribute-based check. SELECT name • But condition can refer to any attribute of FROM Beers)), the relation. price REAL CHECK (price <= 5.00) ); – Or to other relations/attributes in subqueries. • Check on beer is like a foreign-key constraint, – Again: MySQL parses but ignores checks. except: • Checked whenever a tuple is inserted or – The check occurs only when we add a tuple or change the beer in an existing tuple, not when we delete a updated. tuple from Beers. Example SQL Assertions • Only Ripoff bar can sell beer for more than • Database-schema constraint. $10. • Not present in MySQL. CREATE TABLE Sells ( • Checked whenever a mentioned relation bar CHAR(20), changes. beer CHAR(20), • Syntax: price REAL, CREATE ASSERTION <name> CHECK(bar = ‘Ripoff’ OR CHECK(<condition>); price <= 10.00) ); 3

Example Example • No bar may charge an average of more than $5 • There cannot be more bars than drinkers. for beer. Sells(bar, beer, price) Bars(name, addr, license) Drinkers(name, addr, phone) CREATE ASSERTION NoRipoffBars CREATE ASSERTION FewBars CHECK(NOT EXISTS( CHECK( SELECT bar (SELECT COUNT(*) FROM Bars) <= FROM Sells (SELECT COUNT(*) FROM Drinkers) GROUP BY bar ); HAVING 5.0 < AVG(price) ) • Checked whenever Bars or Drinkers changes. ); • Checked whenever Sells changes. Example Aggregation Queries • Find the person who likes the most beers. • Find the most likely pairing of a person and a beer. – Most bars, frequented by a person, that serve the beer. – Another condition? • Find the most likely couple: drinkers that frequent the most bars and like the most beers in common. – Can we weigh number of bars and beers differently? 4