数据库系统原理(英文)-8-11.ppt

1、PART 2,RELATIONAL DATABASES,Chapter 8 Application Design and Development,April 2011,Database System Concepts - Chapter8 Application Design and Development -,3,Fig. 1.0.6 Data abstraction and Schema,April 2011,Database System Concepts - Chapter8 Application Design and Development -,4,用户A1,宿主语言 + DML,

2、用户A2,用户B1,用户B2,外部视图A,外部视图B,概念/逻辑视图,外模式,概念/逻辑模式,内模式,DBMS,外模式/概念 模式映射A,外模式/概念 模式映射B,概念模式/内 模式映射,宿主语言 + DML,宿主语言 + DML,宿主语言 + DML,Fig. 1.0.4,April 2011,Database System Concepts - Chapter8 Application Design and Development -,5,Specification of user requirements (需求分析说明书),DB conceptual schema , i.e. E-R

3、 diagram (chapter 6),DB logical schema , i.e. relational data schema,DB physical schema, e.g. physical storage structure and access method (chapter 11, 12),initial relational schema generating(6.9),relational schema normalizing (chapter 7),Fig. 6.0.1 DB design phases,Application areas/problems in re

4、al worlds,Requirements analysis,Conceptual DB design,Logical DB design,Physical DB design,April 2011,Database System Concepts - Chapter8 Application Design and Development -,6,Fig. 6.0.3 DBAS 生命周期模型,需求分析,概念 设计,逻辑 设计,物理 设计,性能/存储/安全需求,数据项分析,数据流与事务分析,程序需求分析,程序概要设计,程序详细设计,系统总体设计,数据访问与处理,应用 程序,数据组织与存储,系统

5、运行维护,设计,DB概念模式设计,DB逻辑模式设计,系统实现 和部署,事务详细设计,DB物理模式设计,事务概要设计,构造原型(可选),系统实现,数据转换与加载,系统测试、部署与交付,规划与分析,项目规划,运行维护,April 2011,Database System Concepts - Chapter8 Application Design and Development -,7,Case study used in this chapter,return,Fig. 8.0 Schema of banking enterprise,April 2011,Database System Con

6、cepts - Chapter8 Application Design and Development -,8,8.6 Trigger (触发器),Trigger a statement that is executed automatically by DBMS as a side effect of a modification to the database As an integrity control mechanism, Trigger is introduced to SQL1999 standard, but supported even earlier using non-s

7、tandard syntax by most databases,April 2011,Database System Concepts - Chapter8 Application Design and Development -,9,8.6 Trigger (cont.),Trigger is an event-condition-action model based integrity definition, checking, remedy mechanism specify what events cause the trigger to be executed (e.g. inse

8、rt, delete, update), and under which conditions the trigger execution will proceed integrity constraints checking specify the actions to be taken when the trigger executes if constraints is violated, remedy actions are taken,April 2011,Database System Concepts - Chapter8 Application Design and Devel

9、opment -,10,Suppose that, instead of allowing negative account balances, the bank deals with overdrafts (透支) by following actions setting the account balance to zero creating a loan in the amount of the overdraft giving this loan a loan number identical to the account number of the overdrawn account

10、 Trigger overdraft-trigger in Fig.8.8 the triggering event is update on balance the condition for executing the trigger is an modification to the account relation that results in a negative balance value,Example One,April 2011,Database System Concepts - Chapter8 Application Design and Development -,

11、11,trigger actions are insert on borrower, loan relations, and update on account relation,Example One (cont.),create trigger overdraft-trigger after update on account -eventreferencing new row as nrow for each rowwhen nrow.balance 0 -conditionbegin atomic -action insert into borrower (select custome

12、r-name, account-number from depositor where nrow.account-number = depositor.account-number); insert into loan values(nrow.account-number, nrow.branch-name, nrow.balance); update account set balance = 0where account.account-number = nrow.account-numberend,Fig.8.8 Overdraft-trigger,April 2011,Database

13、 System Concepts - Chapter8 Application Design and Development -,13,GSM网络配置数据库中，利用触发器实现 向小区(cell)中新增频点，约束：每个小区最多只有8个频点 计算性能指标“BSC切换成功率”，原理：每当发生一次BSC切换时，根据本次切换是否成功重新计算“切换成功率”,Example Two,create trigger overfre on Cell_TCH for insert /* 事件 event*/ as IF exists (select cellid, count(TCHno) as num /*条件c

14、ondition*/ from Cell_TCH group by cellid having count(TCHno)8) Begin transaction delete from Cell_TCH where Cell_TCH.TCHno = (select min(TCHno) as minfreno from Cell_TCH group by cellid having count(TCHno) 8) print 插入TCH频点多于8个，频点号最小的已经被删除！ End,Fig. 触发器“新增小区频点”,动 作,April 2011,Database System Concepts

15、 - Chapter8 Application Design and Development -,15,The events and actions in trigger can take many forms the trigger events can also be insert or delete, instead of update triggers on update can be restricted to specific attributes e.g. create trigger overdraft-trigger after update of balance on ac

16、count triggers can be activated before or after an event, which can serve as extra constraints values of attributes before and after an update can be referenced referencing old row as: for deletes and updates referencing new row as: for inserts and updates,8.6 Trigger(cont.),April 2011,Database Syst

17、em Concepts - Chapter8 Application Design and Development -,16,e.g. in Fig.8.8, the clause referencing new row as nrow create a variable nrow to store the new values of the updated tuples in account instead of executing a separate action for each affected row, a single action can be executed for the

18、 entire SQL statement that caused the insert/delete/update, i.e. for all rows affected by a single transaction use for each statement instead of for each row use referencing old table or referencing new table to refer to temporary/transition tables containing the affected rows,8.6 Trigger(cont.),Apr

19、il 2011,Database System Concepts - Chapter8 Application Design and Development -,17,a single statement can then be used to carry out multiple actions on the basis of the temporary/transition tables, and thus is more efficient when dealing with SQL statements that update a large number of rows Althou

20、gh trigger can be used to maintain DBS integrity, it may be inefficient in some cases if events occurs frequently, actions will be frequently taken to access on DB Some more efficient mechanisms, such as materialized views, instead of triggers, are used to maintain data in DB For more details, refer

21、 to Appendix A When Not To Use Triggers,8.6 Trigger(cont.),April 2011,Database System Concepts - Chapter8 Application Design and Development -,18,8.7 Authorization in SQL,Authorization：授权，鉴权，特许 Authentication：验证，鉴别 Privilege：权限，特权 Encryption：加密 Encryption key：密钥 Cryptography (密码技术) Security mechanis

22、m guarding against malicious/illegal access to DBS, such as unauthorized reading, modification, and destruction of data,April 2011,Database System Concepts - Chapter8 Application Design and Development -,19,Fig. Security Control Architecture in Database Application Systems,User Identifier (口令),Autho

23、rization (in a narrow sense): privilege definition (授权) privilege checking,security protection,encryption techniques,User,DBMS,Network referred to as cascading of the revoke,April 2011,Database System Concepts - Chapter8 Application Design and Development -,32,Users can be given authorization on vie

24、ws, instead of being given authorization on the relations used in the view definition ability of views to data hiding for security and to data attention Combination of relational-level security and view-level security can be used to limit a users access to precisely the data that user needs, and imp

25、roves DBS security,8.7.5 Authorization and Views,April 2011,Database System Concepts - Chapter8 Application Design and Development -,33,E.g. suppose a bank clerk needs to know the names of the customers of each branch, but is not authorized to see specific loan information approach: deny direct acce

26、ss to the loan relation, but grant access to the view cust-loan, which consists only of the names of customers and the branches at which they have a loan create view cust-loan as select branch-name, customer-name from borrower, loan where borrower.loan-number = loan.loan-number,8.7.5 Authorization a

27、nd Views (cont.),April 2011,Database System Concepts - Chapter8 Application Design and Development -,34,the clerk is authorized to see the result of the query select *from cust-loan the query processor translates this query into a query on borrower and loan but authorization must be checked on the c

28、lerks query before query processing replaces a view by the definition of the view,8.7.5 Authorization and Views (cont.),April 2011,Database System Concepts - Chapter8 Application Design and Development -,35,Creation of view does not require resources authorization (creating new relations) since no r

29、eal relation is being created The creator of a view gets only those privileges that provide no additional authorization beyond that he already had e.g. if creator of view cust-loan had only select, insert, and delete authorization on borrower and loan, he gets only select , insert, and delete author

30、ization on cust-loan,8.7.5 Authorization and Views (cont.),user privilege: select, insert, delete select, insert, delete,April 2011,Database System Concepts - Chapter8 Application Design and Development -,36,Audit trail (审计追踪) a log (日志) of all changes (inserts/deletes/updates) to the database along

31、 with information such as which user performed the change, and when the change was performed Another Definition Audit trail of in Microsoft Press Computing Dictionary in reference to computing, a means of tracing all activities affecting a piece of information, such as a data record, from the time i

32、t is entered into a system to the time it is removed an audit trail make it possible to document, for example, who made a change to a particular record and when,8.7.7 Audit Trail,T1: begin-transaction read (A) A:= A-50 write (A) read (B) B:= B+50 write(B) commit,T2: begin-transaction read(A) temp:=

33、A*0.1 A := A- temp write (A) read (B) B:= B + temp write(B) abort,Fig. Concurrent executing of T1 and T2,log ,Initially, A=1000, B=2000,April 2011,Database System Concepts - Chapter8 Application Design and Development -,38,Audit trail is used to track erroneous/fraudulent updates As shown in Chapter

34、 17, on the basis of log, DBS recovery is implemented Audit trail can be implemented using triggers, but many database systems provide direct support,8.7.7 Audit Trail (cont.),April 2011,Database System Concepts - Chapter8 Application Design and Development -,39,8.8 Application security,8.8.1 Encryp

35、tion Techniques Data may be encrypted when database authorization provisions do not offer sufficient protection,8.8.3 Authentication Authentication refers to the task of verifying the identity of a person/software connecting to a database Password is a kind of widely used authentication mechanism, a

36、t OS-level and database-level,April 2011,Database System Concepts - Chapter8 Application Design and Development -,40,Challenge-response systems avoid transmission of passwords across networks DB sends a (randomly generated) challenge string to user user encrypts string and returns result DB verifies

37、 identity by decrypting result,8.8 Application security (cont.),Digital signatures are also used to verify authenticity of data,April 2011,Database System Concepts - Chapter8 Application Design and Development -,41,Appendix A When Not To Use Triggers,Triggers were used earlier for tasks such as maintaining summary data (e.g. total salary o