數(shù)據(jù)庫系統(tǒng)原理(英文)-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,概念/邏輯視圖,外模式,概念/邏輯模式,內(nèi)模式,DBMS,外模式/概念 模式映射A,外模式/概念 模式映射B,概念模式/內(nèi) 模式映射,宿主語言 + 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 生命周期模型,需求分析,概念 設計,邏輯 設計,物理 設計,性能/存儲/安全需求,數(shù)據(jù)項分析,數(shù)據(jù)流與事務分析,程序需求分析,程序概要設計,程序詳細設計,系統(tǒng)總體設計,數(shù)據(jù)訪問與處理,應用 程序,數(shù)據(jù)組織與存儲,系統(tǒng)

5、運行維護,設計,DB概念模式設計,DB邏輯模式設計,系統(tǒng)實現(xiàn) 和部署,事務詳細設計,DB物理模式設計,事務概要設計,構造原型(可選),系統(tǒng)實現(xiàn),數(shù)據(jù)轉(zhuǎn)換與加載,系統(tǒng)測試、部署與交付,規(guī)劃與分析,項目規(guī)劃,運行維護,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 (觸發(fā)器),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網(wǎng)絡配置數(shù)據(jù)庫中，利用觸發(fā)器實現(xiàn) 向小區(qū)(cell)中新增頻點，約束：每個小區(qū)最多只有8個頻點 計算性能指標“BSC切換成功率”，原理：每當發(fā)生一次BSC切換時，根據(jù)本次切換是否成功重新計算“切換成功率”,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個，頻點號最小的已經(jīng)被刪除！ End,Fig. 觸發(fā)器“新增小區(qū)頻點”,動 作,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