数据库系统概念原书第5版(英文)第三章ppt.ppt

* * Dept. of Comp. Sci. Tech., Tongji Uni. Same query as before: Increase all accounts with balances over $10,000 by 6%, all other accounts receive 5%. update account set balance = case  when balance = 10000 then balance *1.05 else balance * 1.06 end Case Statement for Conditional Updates * * Dept. of Comp. Sci. Tech., Tongji Uni. 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 (‘Perryridge’, ‘L-307’, null) into the loan relation * * Dept. of Comp. Sci. Tech., Tongji Uni. Update of a View (cont.) Updates on more complex views are difficult or impossible to translate, and hence are disallowed. Most SQL implementations allow updates only on simple views (without aggregates) defined on a single relation. In general, an SQL view is said to be updatable if: The from clause has only one database relation; The select clause contains only attribute names of the relation, and does not have any expressions, aggregates, or distinct specification; Any attribute not listed in the select clause can be set to null; The query does not have a group by or having clause. * * Dept. of Comp. Sci. Tech., Tongji Uni. 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. * * Dept. of Comp. Sci. Tech., Tongji Uni. Transactions (Cont.) 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 inconsist