CMSC424 Database Design Lecture 8 CMSC424 Spring 2005 1 Homework Discussion TA Office Hours Today from 4p to 5p Project info on the class web page CMSC424 Spring 2005 2 Next Integrity constraints Prevent semantic inconsistencies CMSC424 Spring 2005 3 IC s Predicates 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 no Attribute constraints 1 table e g accts must have nonnegative balance Referential Integrity constraints 2 tables E g bnames associated w loans must be names of real branches Global Constraints n tables E g all loans must be carried by at least 1 customer with a svngs acct CMSC424 Spring 2005 4 Key Constraints Idea specifies that a relation is a set not a bag SQL 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 5 Key Constraints Effect 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 insertion Updates to any of A1 A2 An treat as insertion of entire tuple Primary 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 6 Attribute Constraints Idea Attach constraints to values of attributes Enhances 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 values 2 CHECK e g CREATE TABLE depositor balance int NOT NULL CHECK balance 0 affect insertions update in affected columns CMSC424 Spring 2005 7 Attribute Constraints Domains can associate constraints with DOMAINS rather than attributes e g instead of One can write CREATE TABLE depositor balance INT NOT NULL CHECK balance 0 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 8 Attribute Constraints Advantage of associating constraints with domains 1 can avoid repeating specification of same constraint for multiple columns 2 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 9 Referential Integrity Constraints Idea prevent dangling tuples e g a loan with a bname Kenmore when no Kenmore tuple in branch Referencing Relation e g loan foreign key bname Referenced Relation e g branch primary key bname Ref 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 10 Referential Integrity Constraints bname Referencing Relation e g loan x x bname x Referenced Relation e g branch In 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 relation CMSC424 Spring 2005 11 Referential Integrity Constraints c ti x tj x c x A B what happens when we try to delete this tuple Ans 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 values CMSC424 Spring 2005 12 Referential Integrity Constraints c ti x tj x c x A B CREATE TABLE A FOREIGN KEY c REFERENCES B action Action what happens when we try to delete this tuple 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 values CMSC424 Spring 2005 13 Global Constraints Idea two kinds 1 single relation constraints spans multiple columns E g CHECK total svngs check declared in the CREATE TABLE 2 multiple relations CREATE ASSERTION SQL examples 1 single relation All Bkln branches must have assets 5M Affects CREATE TABLE branch bcity CHAR 15 assets INT CHECK NOT bcity Bkln OR assets 5M insertions into branch updates of bcity or assets in branch CMSC424 Spring 2005 14 Global Constraints SQL 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 loan constraint CHECK Checked with EVERY DB update very expensive CMSC424 Spring 2005 15 Summary Integrity Constraints Constraint Type Where declared Affects Expense Key Constraints CREATE TABLE Insertions Updates Moderate Insertions Updates Cheap 1 Insertions into referencing rel n 1 2 like key constraints Another reason to index sort on the primary keys PRIMARY KEY UNIQUE Attribute Constraints CREATE TABLE CREATE DOMAIN Referential Integrity Not NULL CHECK Table Tag FOREIGN KEY REFERENCES Global Constraints Table Tag CHECK or outside table CREATE ASSERTION 2 Updates of referencing rel n of relevant attrs 3 4 depends on 3 Deletions from referenced rel n a update delete policy chosen 4 Update of referenced rel n 1 For single rel n constraint with insertion deletion of relevant attrs b existence of indexes on foreign key 2 For assesrtions w every db modification CMSC424 Spring 2005 1 cheap 2 very expensive 16 Triggers A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database CMSC424 Spring 2005 17 Trigger Example Suppose that instead of allowing negative account balances the bank deals with overdrafts by setting the account balance to zero creating a loan in the amount of the overdraft giving this loan a loan number identical to the account number of the overdrawn account CMSC424 Spring 2005 18 Trigger Example in SQL 1999 create trigger overdraft trigger after update on account referencing
View Full Document
Unlocking...