CMSC 424 – Database designLecture 8SQL, constraintsMihai PopNext:Integrity constraints??Prevent semantic inconsistenciesIC’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 acctKey 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));Key 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 ?Attribute 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 columnsAttribute 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?Attribute 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)JoinsNatural (inner) joinOuter joins (left, right)account loanname acc_no name loan_no-------- ----------- --------- -----------Bob 101 Bob L1Bob 102 Jane L3Jane 107 Harry L4Janice 109 Tom L7SQL Query Examples•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#)•Queries:–Producer with maximum average length of movies–Find producer of Star Wars.–All producers of movies in which harrison ford starsSQL Query Examples•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#)•Queries:–Find movie titles that appear more than once–Find number of people 3 hops away from Kevin
View Full Document
Unlocking...