CMSC424: Database DesignTodayViews vs TablesSlide 4Referential Integrity ConstraintsSlide 6Slide 7Slide 8Global ConstraintsSlide 10Summary: Integrity ConstraintsSQLQuestions ?Relational Database DesignMovies Database SchemaExample RelationCMSC424: Database DesignInstructor: Amol Deshpande [email protected]Views vs TablesIntegrity Constraints ContinuedRelational Database DesignViews vs TablesIt’s a new table.You can do what you want.In any select query.Only some update queries.Can be usedIt’s a new table.Stored on disk.1. Evaluate the query and store it on disk as if a table.2. Don’t store. Substitute in queries when referenced.Maintained asT is a separate table; there is no reason why DBMS should keep it updated. If you want that, you must define a trigger.1. If stored on disk, the stored table is automatically updated to be accurate.2. If we are just substituting, there is no need to do anything.What if a tuple inserted in A ?Create table T as (select * from A, B where …)Create view V as (select * from A, B where …)CreatingViews vs TablesViews strictly supercede “create a table and define a trigger to keep it updated”Two main reasons for using them:Security/authorizationEase of writing queriesE.g. Collaborators table if you were asked to write a lot of queries about it.The way we are doing it, the collaborators table is an instance of “creating table”, and not “creating view”Creating a view might have been better.Perhaps the only reason to create a table is to force the DBMS to choose the option of “materializing”That has efficiency advantages in some casesEspecially if the underlying tables don’t changeReferential 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)Referential 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 relationReferential 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 valuesReferential 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 valuesGlobal 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 branchGlobal 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 loan-constraint CHECK( ..... )Checked with EVERY DB update! very expensive.....Summary: Integrity ConstraintsConstraint Type Where declared Affects... ExpenseKey Constraints CREATE TABLE (PRIMARY KEY, UNIQUE)Insertions, Updates ModerateAttribute Constraints CREATE TABLECREATE DOMAIN(Not NULL, CHECK)Insertions, Updates CheapReferential Integrity Table Tag(FOREIGN KEY ....REFERENCES ....)1.Insertions into referencing rel’n2. Updates of referencing rel’n of relevant attrs3. Deletions from referenced rel’n4. Update of referenced rel’n1,2: like key constraints. Another reason to index/sort on the primary keys3,4: depends on a. update/delete policy chosenb. existence of indexes on foreign key Global Constraints Table Tag (CHECK) oroutside table (CREATE ASSERTION)1. For single rel’n constraint, with insertion, deletion of relevant attrs2. For assesrtions w/ every db modification1. cheap2. very expensiveSQLIs that it ?Unfortunately NoSQL 3 standard is several hundreds of pages (if not several thousands)And expensive too..We will discuss a few more constructs along the wayE.g. Embedded SQL, creating indexes etcAgain, this is what the reference books are for; you just need to know where to look in the reference bookQuestions ?Next:Relational Database DesignRelational Database DesignWhere did we come up with the schema that we used ?E.g. why not store the actor names with movies ?Or, store the author names with the papers ?Topics:Formal definition of what it means to be a “good” schema.How to achieve it.Movies Database SchemaMovie(title, year, length, inColor, studioName, producerC#)StarsIn(movieTitle, movieYear, starName)MovieStar(name, address, gender, birthdate)MovieExec(name, address, cert#, netWorth)Studio(name, address, presC#)Movie(title, year, length, inColor, studioName, producerC#, starName)<merged into above>MovieStar(name, address, gender, birthdate)MovieExec(name, address, cert#, netWorth)Studio(name, address, presC#)Changed to:Example RelationTitle Year Length StudioName prodC# StarNameStar wars 1977 120 Fox 128 HamillStar wars 1977 120 Fox 128 FisherStar wars 1977 120 Fox 128 H. FordKing Kong 2005 .. Studio_A 150 NaomiKing Kong 1940 .. Studio_B 20 FayeMovie(title, year, length, inColor, studioName, producerC#, starName)<merged into above>MovieStar(name, address, gender, birthdate)MovieExec(name, address, cert#,
View Full Document
Unlocking...