CMSC424: Database DesignHomework DiscussionNext:IC’sKey ConstraintsSlide 6Attribute ConstraintsSlide 8Slide 9Referential Integrity ConstraintsSlide 11Slide 12Slide 13Global ConstraintsSlide 15Summary: Integrity ConstraintsTriggersTrigger ExampleTrigger Example in SQL:1999Slide 20Triggers…Next…Pitfalls in Relational Database DesignExampleDecompositionExample of Non Lossless-Join DecompositionCMSC424, Spring 2005 1CMSC424: Database DesignLecture 8CMSC424, Spring 2005 2Homework DiscussionTA Office Hours Today from 4p to 5pProject info on the class web pageCMSC424, Spring 2005 3Next:Integrity constraints??Prevent semantic inconsistenciesCMSC424, Spring 2005 4IC’sPredicates on the database Must always be true (:, checked whenever db gets updated)There are the following 4 types of IC’s:Key constraints (1 table)e.g., 2 accts can’t share the same acct_noAttribute constraints (1 table)e.g., accts must have nonnegative balanceReferential Integrity constraints ( 2 tables)E.g. bnames associated w/ loans must be names of real branchesGlobal Constraints (n tables)E.g., all loans must be carried by at least 1 customer with a svngs acctCMSC424, Spring 2005 5Key ConstraintsIdea: specifies that a relation is a set, not a bagSQL examples: 1. Primary Key: CREATE TABLE branch( bname CHAR(15) PRIMARY KEY, bcity CHAR(20), assets INT); or CREATE TABLE depositor( cname CHAR(15), acct_no CHAR(5), PRIMARY KEY(cname, acct_no)); 2. Candidate Keys: CREATE TABLE customer ( ssn CHAR(9) PRIMARY KEY, cname CHAR(15), address CHAR(30), city CHAR(10), UNIQUE (cname, address, city));CMSC424, Spring 2005 6Key ConstraintsEffect of SQL Key declarations PRIMARY (A1, A2, .., An) or UNIQUE (A1, A2, ..., An)Insertions: check if any tuple has same values for A1, A2, .., An as any inserted tuple. If found, reject insertionUpdates to any of A1, A2, ..., An: treat as insertion of entire tuplePrimary vs Unique (candidate)1. 1 primary key per table, several unique keys allowed.2. Only primary key can be referenced by “foreign key” (ref integrity)3. DBMS may treat primary key differently (e.g.: create an index on PK)How would you implement something like this ?CMSC424, Spring 2005 7Attribute ConstraintsIdea:Attach constraints to values of attributesEnhances types system (e.g.: >= 0 rather than integer)In SQL: 1. NOT NULL e.g.: CREATE TABLE branch( bname CHAR(15) NOT NULL, .... )Note: declaring bname as primary key also prevents null values2. CHECK e.g.: CREATE TABLE depositor( .... balance int NOT NULL, CHECK( balance >= 0), .... )affect insertions, update in affected columnsCMSC424, Spring 2005 8Attribute ConstraintsDomains: can associate constraints with DOMAINS rather than attributese.g: instead of: CREATE TABLE depositor( .... balance INT NOT NULL, CHECK (balance >= 0) )One can write: CREATE DOMAIN bank-balance INT ( CONSTRAINT not-overdrawn CHECK (value >= 0), CONSTRAINT not-null-value CHECK( value NOT NULL)); CREATE TABLE depositor ( ..... balance bank-balance, )Advantages?CMSC424, Spring 2005 9Attribute ConstraintsAdvantage of associating constraints with domains:1. can avoid repeating specification of same constraint for multiple columns2. can name constraints e.g.: CREATE DOMAIN bank-balance INT ( CONSTRAINT not-overdrawn CHECK (value >= 0), CONSTRAINT not-null-value CHECK( value NOT NULL)); allows one to: 1. add or remove: ALTER DOMAIN bank-balance ADD CONSTRAINT capped CHECK( value <= 10000) 2. report better errors (know which constraint violated)CMSC424, Spring 2005 10Referential Integrity ConstraintsIdea: prevent “dangling tuples” (e.g.: a loan with a bname, Kenmore, when no Kenmore tuple in branch)ReferencingRelation(e.g. loan)ReferencedRelation(e.g. branch)“foreign key” bnameprimary key bnameRef Integrity: ensure that: foreign key value primary key value(note: need not to ensure , i.e., not all branches have to have loans)CMSC424, Spring 2005 11Referential Integrity ConstraintsReferencingRelation(e.g. loan)ReferencedRelation(e.g. branch)bnamebnamexxxIn SQL: CREATE TABLE branch( bname CHAR(15) PRIMARY KEY ....) CREATE TABLE loan ( ......... FOREIGN KEY bname REFERENCES branch);Affects: 1) Insertions, updates of referencing relation 2) Deletions, updates of referenced relationCMSC424, Spring 2005 12Referential Integrity ConstraintsccxxxABwhat happens whenwe try to deletethis tuple?titjAns: 3 possibilities 1) reject deletion/ update 2) set ti [c], tj[c] = NULL 3) propagate deletion/update DELETE: delete ti, tj UPDATE: set ti[c], tj[c] to updated valuesCMSC424, Spring 2005 13Referential Integrity ConstraintsccxxxABwhat happens whenwe try to deletethis tuple?titjCREATE TABLE A ( ..... FOREIGN KEY c REFERENCES B action .......... )Action: 1) left blank (deletion/update rejected) 2) ON DELETE SET NULL/ ON UPDATE SET NULL sets ti[c] = NULL, tj[c] = NULL 3) ON DELETE CASCADE deletes ti, tj ON UPDATE CASCADE sets ti[c], tj[c] to new key valuesCMSC424, Spring 2005 14Global ConstraintsIdea: two kinds1) single relation (constraints spans multiple columns)E.g.: CHECK (total = svngs + check) declared in the CREATE TABLE2) multiple relations: CREATE ASSERTIONSQL examples: 1) single relation: All Bkln branches must have assets > 5M CREATE TABLE branch ( .......... bcity CHAR(15), assets INT, CHECK (NOT(bcity = ‘Bkln’) OR assets > 5M))Affects: insertions into branch updates of bcity or assets in branchCMSC424, Spring 2005 15Global ConstraintsSQL example:2) Multiple relations: every loan has a borrower with a savings account CHECK (NOT EXISTS ( SELECT * FROM loan AS L WHERE NOT EXISTS( SELECT * FROM borrower B, depositor D, account A WHERE B.cname = D.cname AND D.acct_no = A.acct_no AND L.lno = B.lno)))Problem: Where to put this constraint? At depositor? Loan? ....Ans: None of the above: CREATE ASSERTION
View Full Document
Unlocking...