CMSC424: Database DesignToday…ViewsExample QueriesSlide 5Views vs TablesSlide 7Update of a ViewNext…More SQL: NullsSlide 11Slide 12Slide 13More SQL: UnknownSlide 15Slide 16NextTransactionsTransactions (Cont.)Slide 20TriggersTrigger ExampleTrigger Example in SQL:1999Slide 24Triggers…Next:IC’sKey ConstraintsSlide 29Attribute ConstraintsSlide 31Slide 32CMSC424: Database DesignInstructor: Amol Deshpande [email protected]…Advanced SQLViewsTriggers TransactionsIntegrity ConstraintsViewsProvide a mechanism to hide certain data from the view of certain users. To create a view we use the command:Can be used in any place a normal table can be usedFor users, there is no distinction in terms of using itcreate view v as <query expression>where:<query expression> is any legal expressionThe view name is represented by vExample QueriesA view consisting of branches and their customersFind all customers of the Perryridge branchcreate view all-customers as (select branch-name, customer-name from depositor, account where depositor.account-number = account.account-number) union (select branch-name, customer-name from borrower, loan where borrower.loan-number = loan.loan-number)select customer-namefrom all-customerswhere branch-name = ‘Perryridge’ViewsIs it different from DBMS’s side ?Yes; a view may or may not be materializedPros/Cons ?Updates into views have to be treated differentlyIn most cases, disallowed.Views 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 changeUpdate of a ViewCreate a view of all loan data in loan relation, hiding the amount attributecreate view branch-loan asselect branch-name, loan-numberfrom loanAdd a new tuple to branch-loaninsert into branch-loanvalues (‘Perryridge’, ‘L-307’)This insertion must be represented by the insertion of the tuple(‘L-307’, ‘Perryridge’, null)into the loan relationUpdates on more complex views are difficult or impossible to translate, and hence are disallowed. Many SQL implementations allow updates only on simple views (without aggregates) defined on a single relationNext…SQL and NULLSSQL Advanced FeaturesIntegrity ConstraintsTransactionsTriggersMore SQL: NullsThe “dirty little secret” of SQL(major headache for query optimization)Can be a value of any attributee.g: branch =What does this mean?(unknown) We don’t know Waltham’s assets?(inapplicable) Waltham has a special kind of account without assets(withheld) We are not allowed to know bname bcity assetsDowntown Boston 9MPerry Horseneck 1.7MMianus Horseneck .4MWaltham Boston NULLMore SQL: NullsArithmetic Operations with Nulln + NULL = NULL (similarly for all arithmetic ops: +, -, *, /, mod, …)SELECT bname, assets * 2 as a2FROM branche.g: branch ==bname bcity assetsDowntown Boston 9MPerry Horseneck 1.7MMianus Horseneck .4MWaltham Boston NULLbname a2Downtown 18MPerry 3.4MMianus .8MWaltham NULLMore SQL: NullsBoolean Operations with Nulln < NULL = UNKNOWN (similarly for all boolean ops: >, <=, >=, <>, =, …)e.g: branch ==SELECT * FROM branchWHERE assets = NULLbname bcity assetsDowntown Boston 9MPerry Horseneck 1.7MMianus Horseneck .4MWaltham Boston NULLbname bcity assetsCounter-intuitive: select * from movies where length >= 120 or length <= 120Counter-intuitive: NULL * 0 = NULLMore SQL: NullsBoolean Operations with Nulln < NULL = UNKNOWN (similarly for all boolean ops: >, <=, >=, <>, =, …)e.g: branch ==SELECT * FROM branchWHERE assets IS NULLbname bcity assetsDowntown Boston 9MPerry Horseneck 1.7MMianus Horseneck .4MWaltham Boston NULLbname bcity assetsWaltham Boston NULLMore SQL: UnknownBoolean Operations with UnknownCan write:SELECT …FROM …WHERE booleanexp IS UNKNOWNIntuition: substitute each of TRUE, FALSE for unknown. If different answer results, results is unknownn < NULL = UNKNOWN (similarly for all boolean ops: >, <=, >=, <>, =, …)FALSE OR UNKNOWN = UNKNOWNTRUE AND UNKNOWN = UNKNOWNUNKNOWN OR UNKNOWN = UNKNOWNUNKNOWN AND UNKNOWN = UNKNOWNNOT (UNKNOWN) = UNKNOWNUNKNOWN tuples are not included in final resultMore SQL: NullsGivenbranch =Aggregate OperationsSELECT SUM (assets) = FROM branchNULL is ignoredSame for AVG (3.7M), MIN (0.4M), MAX (9M)But COUNT (assets) returnsSUM11.1 MCOUNT4bname bcity assetsDowntown Boston 9MPerry Horseneck 1.7MMianus Horseneck .4MWaltham Boston NULLMore SQL: NullsGivenbranch =SELECT SUM (assets) = FROM branch• Same as AVG, MIN, MAX• But COUNT (assets) returnsSUMNULLCOUNT0bname bcity assetsNextTransactionsTransactionsA transaction is a sequence of queries and update statements executed as a single unitTransactions are started implicitly and terminated by one of•commit work: makes all updates of the transaction permanent in the database•rollback work: undoes all updates performed by the transaction. Motivating exampleTransfer of money from one account to another involves two steps:• deduct from one account and credit to anotherIf one steps succeeds and the other fails, database is in an inconsistent stateTherefore, either both steps should succeed or neither shouldIf any step of a transaction fails, all work done by the transaction can be undone by rollback work. Rollback of incomplete
View Full Document
Unlocking...