CMSC424 Database Design Instructor Amol Deshpande amol cs umd edu Today Advanced SQL Views Triggers Transactions Integrity Constraints Views Provide a mechanism to hide certain data from the view of certain users To create a view we use the command create view v as query expression where query expression is any legal expression The view name is represented by v Can be used in any place a normal table can be used For users there is no distinction in terms of using it Example Queries A view consisting of branches and their customers create 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 Find all customers of the Perryridge branch select customer name from all customers where branch name Perryridge Views Is it different from DBMS s side Yes a view may or may not be materialized Pros Cons Updates into views have to be treated differently In most cases disallowed Views vs Tables Creating Create view V as select from A B where Create table T as select from A B where Can be used In any select query Only some update queries It s a new table You can do what you want Maintained as 1 Evaluate the query and store it on disk as if a table 2 Don t store Substitute in queries when referenced It s a new table Stored on disk What if a tuple inserted in A 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 T is a separate table there is no reason why DBMS should keep it updated If you want that you must define a trigger Views vs Tables Views strictly supercede create a table and define a trigger to keep it updated Two main reasons for using them Security authorization Ease of writing queries E 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 cases Especially if the underlying tables don t change Update of a View Create a view of all loan data in loan relation hiding the amount attribute create view branch loan as select branch name loan number from loan Add a new tuple to branch loan insert into branch loan values Perryridge L 307 This insertion must be represented by the insertion of the tuple L 307 Perryridge null into the loan relation Updates 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 relation Next SQL and NULLS SQL Advanced Features Integrity Constraints Transactions Triggers More SQL Nulls The dirty little secret of SQL major headache for query optimization Can be a value of any attribute e g branch bname bcity assets Downtown Boston 9M Perry Horseneck 1 7M Mianus Horseneck 4M Waltham Boston NULL 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 More SQL Nulls Arithmetic Operations with Null n NULL NULL similarly for all arithmetic ops mod e g branch bname bcity assets Downtown Boston 9M Perry Horseneck 1 7M Mianus Horseneck 4M Waltham Boston NULL SELECT bname assets 2 as a2 FROM branch bname a2 Downtown 18M Perry 3 4M Mianus 8M Waltham NULL More SQL Nulls Boolean Operations with Null n NULL UNKNOWN e g branch similarly for all boolean ops bname bcity assets Downtown Boston 9M Perry Horseneck 1 7M Mianus Horseneck 4M Waltham Boston NULL SELECT FROM branch WHERE assets NULL Counter intuitive NULL 0 NULL Counter intuitive select from movies where length 120 or length 120 bname bcity assets More SQL Nulls Boolean Operations with Null n NULL UNKNOWN e g branch similarly for all boolean ops bname bcity assets Downtown Boston 9M Perry Horseneck 1 7M Mianus Horseneck 4M Waltham Boston NULL SELECT FROM branch WHERE assets IS NULL bname bcity assets Waltham Boston NULL More SQL Unknown Boolean Operations with Unknown n NULL UNKNOWN similarly for all boolean ops FALSE OR UNKNOWN UNKNOWN TRUE AND UNKNOWN UNKNOWN Intuition substitute each of TRUE FALSE for unknown If different answer results results is unknown UNKNOWN OR UNKNOWN UNKNOWN UNKNOWN AND UNKNOWN UNKNOWN NOT UNKNOWN UNKNOWN Can write SELECT FROM WHERE booleanexp IS UNKNOWN UNKNOWN tuples are not included in final result More SQL Nulls Given branch bname bcity assets Downtown Boston 9M Perry Horseneck 1 7M Mianus Horseneck 4M Waltham Boston NULL Aggregate Operations SELECT SUM assets FROM branch SUM 11 1 M NULL is ignored Same for AVG 3 7M MIN 0 4M MAX 9M But COUNT assets returns COUNT 4 More SQL Nulls Given branch bname bcity assets SELECT SUM assets SUM FROM branch NULL Same as AVG MIN MAX But COUNT assets returns COUNT 0 Next Transactions Transactions A transaction is a sequence of queries and update statements executed as a single unit Transactions 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 example Transfer of money from one account to another involves two steps deduct from one account and credit to another If one steps succeeds and the other fails database is in an inconsistent state Therefore either both steps should succeed or neither should If any step of a transaction fails all work done by the transaction can be undone by rollback work Rollback of incomplete transactions is done automatically in case of system failures Transactions Cont In most database systems each SQL statement that executes successfully is automatically committed Each transaction would then consist of only a single statement Automatic commit can usually be turned off allowing multi statement transactions but how to do so depends on the database system Another option in SQL 1999 enclose statements within begin atomic end Next Triggers Triggers A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database Trigger Example Suppose that instead of allowing negative account balances the bank deals with overdrafts by 1 setting the account
View Full Document
Unlocking...