PART 2,RELATIONAL DATABASES,Chapter 8 Application Design and Development,Database System Concepts - Chapter8 Application Design and Development -,3,8.1-8.5 Issues related to DBAS Development,8.1 用户接口与工具 8.2 数据库的Web接口 8.3 Web基础 8.4 Servlets 与JSP 8.5 构建大型Web应用,Database System Concepts - Chapter8 Application Design and Development -,4,8.1-8.5 Issues related to DBAS Development,数据库应用系统DBAS设计 DB, DBMS, users, application programs refer to Fig. 8.0.1 电信网管系统示意图 DBAS设计 refer to Fig. 8.0.2 DBAS 生命周期模型 参照软件工程中软件开发瀑布模型原理，DBAS的生命周期由项目规划、需求分析、系统设计、实现与部署、运行管理与维护等5个基本活动组成 根据DBAS的软件组成和各自功能，分为数据组织与存储设计、数据访问与处理设计、应用设计三条设计主线，分别用于设计数据库、数据库事务和应用程序,人机界面模块,配置管理,性能管理,ODBC/JDBC 数据库接口,SQL SERVER/DB2数据库管理系统,数据库DB： 配置数据、性能数据、故障数据 、安全数据、计费数据,故障管理,安全管理,计费管理,配置数据 访问事务,性能数据 访问事务,故障数据 访问事务,安全数据 访问事务,计费数据 访问事务,Fig. 8.0.1 电信网管系统示意图,Fig. 8.0.2 DBAS 生命周期模型,需求分析,概念 设计,逻辑 设计,物理 设计,性能/存储/安全需求,数据项分析,数据流与事务分析,程序需求分析,程序概要设计,程序详细设计,系统总体设计,数据访问与处理,应用 程序,数据组织与存储,系统运行维护,设计,DB概念模式设计,DB逻辑模式设计,系统实现 和部署,事务详细设计,DB物理模式设计,事务概要设计,构造原型(可选),系统实现,数据转换与加载,系统测试、部署与交付,规划与分析,项目规划,运行维护,Specification of user requirements (需求分析说明书),DB conceptual schema , i.e. E-R 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. 8.0.3 DB design phases,Application areas/problems in real worlds,Requirements analysis,Conceptual DB design,Logical DB design,Physical DB design,Database System Concepts - Chapter8 Application Design and Development -,8,Database System Concepts - Chapter8 Application Design and Development -,9,Case study used in this chapter,return,Fig. 8.0 Schema of banking enterprise,Database System Concepts - Chapter8 Application Design and Development -,10,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-standard syntax by most databases,Database System Concepts - Chapter8 Application Design and Development -,11,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. insert, 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,Database System Concepts - Chapter8 Application Design and Development -,12,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 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,Database System Concepts - Chapter8 Application Design and Development -,13,trigger actions are insert on borrower, loan relations, and update on account relation,Example One (cont.),create trigger overdraft-trigger after update on account -event referencing new row as nrow for each row when nrow.balance 0 -condition begin atomic -action insert into borrower (select customer-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 = 0 where account.account-number = nrow.account-number end,Fig.8.8 Overdraft-trigger,Database System Concepts - Chapter8 Application Design and Development -,15,GSM网络配置数据库中，利用触发器实现 向小区(cell)中新增频点，约束：每个小区最多只有8个频点,Example Two,create trigger overfre on Cell_TCH for insert /* 事件 event*/ as IF exists (select cellid, count(TCHno) as num /*条件condition*/ 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. 触发器“新增小区频点”,动 作,Database System Concepts - Chapter8 Application Design and Development -,17,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 account triggers can be activated before or after an event, which can serve as extra constraints values of attributes before and afte