第5章 数据库应用系统设计

数据库原理与SQLServer应用第5章 数据库应用系统设计第五章第五章 数据库应用系统数据库应用系统设计设计5.1 数据库应用系统开发过程数据库应用系统开发过程5.2 数据库应用系统设计应用举例数据库应用系统设计应用举例数据库原理与SQLServer应用第5章 数据库应用系统设计 数据库设计是建立数据库及其应用系统的技术，数据库设计是建立数据库及其应用系统的技术，是信息系统开发和建议中的核心技术。由于数据是信息系统开发和建议中的核心技术。由于数据库应用的复杂性，为了支持相关应用程序的运行，库应用的复杂性，为了支持相关应用程序的运行，数据库设计就变得异常复杂，最佳设计不可能一数据库设计就变得异常复杂，最佳设计不可能一次就成。而只能是一种次就成。而只能是一种“反复探寻，逐步求精反复探寻，逐步求精”的的过程。过程。数据库设计内容包括数据库设计内容包括结构特性设计结构特性设计和和行为特行为特性设计性设计。前者是指数据库总体概念的设计，它应。前者是指数据库总体概念的设计，它应该是具有最小数据冗余的、能反映不同用户数据该是具有最小数据冗余的、能反映不同用户数据需求的、能实现数据共享的系统。后者是指实现需求的、能实现数据共享的系统。后者是指实现数据库用户业务活动的应用程序的设计，用户通数据库用户业务活动的应用程序的设计，用户通过应用程序来访问和操作数据库。过应用程序来访问和操作数据库。第五章第五章 数据库应用系统设计数据库应用系统设计数据库原理与SQLServer应用第5章 数据库应用系统设计 按照规范设计的方法，考虑数据库及其应用按照规范设计的方法，考虑数据库及其应用系统开发全过程，将数据库设计分为以下系统开发全过程，将数据库设计分为以下6个阶个阶段：段：u 需求分析阶段需求分析阶段u 概念结构设计阶段概念结构设计阶段u 逻辑结构设计阶段逻辑结构设计阶段u 物理结构设计阶段物理结构设计阶段u 数据库实施阶段数据库实施阶段u 数据库运行和维护阶段数据库运行和维护阶段5.1 数据库应用系统开发过程数据库应用系统开发过程概述概述数据库原理与SQLServer应用第5章 数据库应用系统设计 按数据库应用系统是在计算机硬件软件系统和某一种按数据库应用系统是在计算机硬件软件系统和某一种数据库管理系统的支持下，针对某一方面应用的信息管数据库管理系统的支持下，针对某一方面应用的信息管理系统。如常见的银行存取款系统、民航售票系统、图理系统。如常见的银行存取款系统、民航售票系统、图书借阅系统、财务管理系统、旅店服务系统、网上购物书借阅系统、财务管理系统、旅店服务系统、网上购物系统等都是数据库应用系统的具体事例。系统等都是数据库应用系统的具体事例。设计数据库应用系统不是一件容易的事，一般来说，设计数据库应用系统不是一件容易的事，一般来说，大型数据库的设计需要一年甚至几年的时间、汇集许多大型数据库的设计需要一年甚至几年的时间、汇集许多专家通力合作才能完成。比如要有以下的专家：专家通力合作才能完成。比如要有以下的专家：5.1 数据库应用系统开发过程数据库应用系统开发过程概述概述数据库原理与SQLServer应用第5章 数据库应用系统设计 比如要有以下的专家：比如要有以下的专家：熟悉数据库设计的理论专家熟悉数据库设计的理论专家 熟悉某一种或几种数据库管理系统的专家熟悉某一种或几种数据库管理系统的专家 熟悉某一种或几种数据库应用系统开发工具的专家熟悉某一种或几种数据库应用系统开发工具的专家 熟悉面向数据库应用的具体业务管理的专家熟悉面向数据库应用的具体业务管理的专家 但对小型数据库应用系统来说，可能这些角色主要但对小型数据库应用系统来说，可能这些角色主要由一个人来承担。由一个人来承担。5.1 数据库应用系统开发过程数据库应用系统开发过程概述概述数据库原理与SQLServer应用第5章 数据库应用系统设计 需求分析阶段的主要任务是：需求分析阶段的主要任务是：了解和掌握数据库应用系统开发对象（或称用户，了解和掌握数据库应用系统开发对象（或称用户，它将是利用数据库进行管理信息的单位）的工作业务流它将是利用数据库进行管理信息的单位）的工作业务流程和每个岗位、每个环节的职责；程和每个岗位、每个环节的职责；了解和掌握信息从开始产生或建立，到最后输出、了解和掌握信息从开始产生或建立，到最后输出、存档或消亡所经过的传递和转换过程；存档或消亡所经过的传递和转换过程；了解和掌握各种人员在整个系统活动过程中的作用了解和掌握各种人员在整个系统活动过程中的作用 需求分析阶段的需求分析阶段的主要目标主要目标是画出是画出数据流图数据流图，建立，建立数据字典数据字典和编写和编写需求说明书需求说明书。5.1.1 需求分析阶段需求分析阶段数据库原理与SQLServer应用第5章 数据库应用系统设计 数据流图数据流图(data flow diagram，简记，简记DFD)是描述实是描述实际业务管理系统工作流程的一种图形表示法，它使用带际业务管理系统工作流程的一种图形表示法，它使用带箭头的连线表示数据的流动方向或者表示前者（即不带箭头的连线表示数据的流动方向或者表示前者（即不带箭头的一端）对后者（即箭头所指向的一端）的作用，箭头的一端）对后者（即箭头所指向的一端）的作用，用圆圈表示进行信息处理的一个环节，用双线段或开口用圆圈表示进行信息处理的一个环节，用双线段或开口矩形表示存档文件或实物，用矩形表示参与活动的人员矩形表示存档文件或实物，用矩形表示参与活动的人员或部门。例如，购物活动的数据流图如下：或部门。例如，购物活动的数据流图如下：5.1.1 需求分析阶段需求分析阶段数据库原理与SQLServer应用第5章 数据库应用系统设计5.1.1 需求分析阶段需求分析阶段数据库原理与SQLServer应用第5章 数据库应用系统设计 数据字典数据字典(data dictionary，简记，简记DD)是对系统工是对系统工作流程中数据和处理的描述。在数据库应用系统设计作流程中数据和处理的描述。在数据库应用系统设计中，它是最原始的数据字典，以后在概念设计和逻辑中，它是最原始的数据字典，以后在概念设计和逻辑设计中的数据字典都由它依次变换和修改而得到。如设计中的数据字典都由它依次变换和修改而得到。如在购物活动中，主要数据就是营业员开据的购物单，在购物活动中，主要数据就是营业员开据的购物单，它可以包括商品代号、商品名称、品牌、数量、单价、它可以包括商品代号、商品名称、品牌、数量、单价、总价、付款盖章等数据项。购物活动中的处理任务包总价、付款盖章等数据项。购物活动中的处理任务包括选购处理和收款处理，选购处理又包括两项任务，括选购处理和收款处理，选购处理又包括两项任务，一是营业员按照顾客要求开据购物单并交给顾客，二一是营业员按照顾客要求开据购物单并交给顾客，二是根据顾客交款后的购物单付给顾客物品。是根据顾客交款后的购物单付给顾客物品。5.1.1 需求分析阶段需求分析阶段数据库原理与SQLServer应用第5章 数据库应用系统设计 需求说明书需求说明书就是系统总体设计方案，它包括上述的就是系统总体设计方案，它包括上述的数据流图和数据字典，包括系统设计的总体目标，系统数据流图和数据字典，包括系统设计的总体目标，系统适宜采用的计算机系统和数据库管理系统及相应配置情适宜采用的计算机系统和数据库管理系统及相应配置情况，包括系统开发人员组成、开发费用和时间，包括划况，包括系统开发人员组成、开发费用和时间，包括划分系统边界，即哪些数据和处理由计算机完成，哪些数分系统边界，即哪些数据和处理由计算机完成，哪些数据和处理仍由人工完成，包括对用户使用系统的要求等据和处理仍由人工完成，包括对用户使用系统的要求等许多方面的详细内容。许多方面的详细内容。需求说明书需求说明书是开发单位与用户单位共同协商达成的是开发单位与用户单位共同协商达成的文档，一般要经过有关方面的专家进行评审和通过。它文档，一般要经过有关方面的专家进行评审和通过。它是以后各阶段进行开发和设计的主要依据，也是最终进是以后各阶段进行开发和设计的主要依据，也是最终进行系统鉴定和评价的依据。行系统鉴定和评价的依据。5.1.1 需求分析阶段需求分析阶段数据库原理与SQLServer应用第5章 数据库应用系统设计 例如，例如，在购物活动中，可根据原始数据构成三种用于在购物活动中，可根据原始数据构成三种用于计算机管理的数据文件：计算机管理的数据文件：A.根据各种商品目录建立对应的商品数据文件，根据各种商品目录建立对应的商品数据文件，每种商品可以包括商品代号、名称、品牌、颜色、规每种商品可以包括商品代号、名称、品牌、颜色、规格、单价、数量等数据项；格、单价、数量等数据项；B.根据顾客购物单建立对应的销售数据文件，每根据顾客购物单建立对应的销售数据文件，每条记录包括商品代号、名称、品牌、单价、数量、营条记录包括商品代号、名称、品牌、单价、数量、营业员、顾客号等数据项；业员、顾客号等数据项；C.根据每笔收款建立对应的收款数据文件，如包根据每笔收款建立对应的收款数据文件，如包括商品代号、单价、数量、总价、收款员号、营业员括商品代号、单价、数量、总价、收款员号、营业员号等数据项。号等数据项。5.1.1 需求分析阶段需求分析阶段数据库原理与SQLServer应用第5章 数据库应用系统设计 概念设计阶段概念设计阶段的主要任务和目标是根据需求分析的的主要任务和目标是根据需求分析的结果找出所有数据实体，包括一般实体和联系实体，结果找出所有数据实体，包括一般实体和联系实体，画出对应的画出对应的ER图。对于较复杂的系统，通常首先要根图。对于较复杂的系统，通常首先要根据各个岗位、各个用户对数据和使用权限的不同要求据各个岗位、各个用户对数据和使用权限的不同要求画出各自对应的局部画出各自对应的局部ER图，然后把各个局部图，然后把各个局部ER图综合图综合起来形成统一的整体起来形成统一的整体ER图。由局部图。由局部ER图到整体图到整体ER图图不是简单的叠加，而是要把有关设计人员集中起来，不是简单的叠加，而是要把有关设计人员集中起来，进行讨论和协调，消除某些命名冲突和语义冲突，消进行讨论和协调，消除某些命名冲突和语义冲突，消除多余的实体、联系或属性，合并有关的实体、联系除多余的实体、联系或属性，合并有关的实体、联系或属性，分解有关的实体、联系或属性等。总之，要或属性，分解有关的实体、联系或属性等。总之，要在局部在局部ER图的基础上进行整体设计和优化，使之具有图的基础上进行整体设计和优化，使之具有较高的结构化和规范化。较高的结构化和规范化。5.1.2 概念设计阶段概念设计阶段数据库原理与SQLServer应用第5章 数据库应用系统设计 概念设计概念设计也要贯彻概念单一化原则，即一个实体只用来反映一种事实，一个实体也要贯彻概念单一化原则，即一个实体只用来反映一种事实，一个实体中的所有属性都是用来直接描述码的，而不是用来描述其他非码属性，甚至其他实体中的所有属性都是用来直接描述码的，而不是用来描述其他非码属性，甚至其他实体中的属性的。若实体中存在部分依赖和传递依赖，则应进行分解，使之消除这些依赖。中的属性的。若实体中存在部分依赖和传递依赖，则应进行分解，使之消除这些依赖。5.1.2 概念设计阶段概念设计阶段数据库原理与SQLServer应用第5章 数据库应用系统设计 在上述列举的购物活动中，通过需求分析，我们能在上述列举的购物活动中，通过需求分析，我们能够等到够等到商品实体商品实体、销售实体销售实体和和收款实体收款实体。商品实体的主。商品实体的主码是商品代号，销售实体中如没有一种特性做主码，就码是商品代号，销售实体中如没有一种特性做主码，就可以选用一个整数属性按自然顺序编码做主码，收款实可以选用一个整数属性按自然顺序编码做主码，收款实体也如此，没有合适的属性做主码。体也如此，没有合适的属性做主码。它们的它们的ER图如下：图如下：5.1.2 概念设计阶段概念设计阶段数据库原理与SQLServer应用第5章 数据库应用系统设计5.1.2 概念设计阶段概念设计阶段数据库原理与SQLServer应用第5章 数据库应用系统设计5.1.2 概念设计阶段概念设计阶段数据库原理与SQLServer应用第5章 数据库应用系统设计 逻辑设计阶段逻辑设计阶段的主要任务和目标是：根据概念设的主要任务和目标是：根据概念设计的结果设计出数据库的逻辑结构模式，包括全局模计的结果设计出数据库的逻辑结构模式，包括全局模式和各个外模式。由于现在普遍应用的是关系型数据式和各个外模式。由于现在普遍应用的是关系型数据库管理系统，所以要设计的数据库逻辑模式也就自然库管理系统，所以要设计的数据库逻辑模式也就自然是指关系型的逻辑结构模式。是指关系型的逻辑结构模式。物理设计阶段物理设计阶段的任务和目标是根据数据库的逻辑的任务和目标是根据数据库的逻辑设计结果设计出相应的内模式。对于关系型数据库系设计结果设计出相应的内模式。对于关系型数据库系统来说，它能够自动把用户设计好的数据库全局模式统来说，它能够自动把用户设计好的数据库全局模式转换为相应的内模式，至多需要用户考虑是否建立索转换为相应的内模式，至多需要用户考虑是否建立索引，使用什么方式的索引等简单问题，一般不需要对引，使用什么方式的索引等简单问题，一般不需要对数据库的物理设计进行专门的讨论。数据库的物理设计进行专门的讨论。5.1.3 逻辑设计和物理设计阶段逻辑设计和物理设计阶段数据库原理与SQLServer应用第5章 数据库应用系统设计 主在购物活动系统中，按前面的概念结构模式，主在购物活动系统中，按前面的概念结构模式，得到的逻辑结构模式应包括得到的逻辑结构模式应包括3个基本表，定义如下：个基本表，定义如下：商品表：商品表：create table 商品表商品表(商品代号商品代号 char(8)primary key,名称名称 char(8)not null,品牌品牌 char(6),体积体积 char(14),颜色颜色 char(6),单价单价 float check(单价单价0)not null,库存数量库存数量 int check(库存数量库存数量=0)default 0 )5.1.3 逻辑设计和物理设计阶段逻辑设计和物理设计阶段数据库原理与SQLServer应用第5章 数据库应用系统设计create table 销售表销售表(交易序号交易序号 int primary key check(交易序号交易序号0),商品代号商品代号 char(8)not null,名称名称 char(8)not null,品牌品牌 char(6),体积体积 char(14),颜色颜色 char(6),单价单价 float check(单价单价0)not null,购买数量购买数量 int check(库存数量库存数量=0)default 0，foreign key(商品代号商品代号)references 商品表商品表(商品代号商品代号)5.1.3 逻辑设计和物理设计阶段逻辑设计和物理设计阶段数据库原理与SQLServer应用第5章 数据库应用系统设计create table 收款表收款表(收款序号 int primary key check(收款序号0),商品代号 char(8)not null,单价 float check(单价0)not null,购买数量 int check(购买数量=0)default 0,总价 float check(总价=0)not null,foreign key(商品代号)references 商品表(商品代号)5.1.3 逻辑设计和物理设计阶段逻辑设计和物理设计阶段数据库原理与SQLServer应用第5章 数据库应用系统设计 机器实现阶段机器实现阶段的主要任务和目标就是在实际的计的主要任务和目标就是在实际的计算机系统中算机系统中建立数据库应用系统建立数据库应用系统。它包括建立数据库。它包括建立数据库模式（即逻辑结构模式和存储结构模式），通过装入模式（即逻辑结构模式和存储结构模式），通过装入数据建立真实的数据库，按照需求分析中规定的对数数据建立真实的数据库，按照需求分析中规定的对数据的各种处理要求，结合特定的据的各种处理要求，结合特定的DBMS和开发环境编和开发环境编写出相应的应用程序和开发出相应的操作界面。总之，写出相应的应用程序和开发出相应的操作界面。总之，要在机器上得到一个满足设计要求的、功能完善和操要在机器上得到一个满足设计要求的、功能完善和操作方便的数据库应用系统。作方便的数据库应用系统。5.1.4 机器实现阶段机器实现阶段数据库原理与SQLServer应用第5章 数据库应用系统设计 此阶段主要靠数据库管理员此阶段主要靠数据库管理员(DBA)做日常的系统管做日常的系统管理和维护工作，他需要经常听取用户意见，需要利用理和维护工作，他需要经常听取用户意见，需要利用专门的系统测试和分析软件对系统运行状态进行检测，专门的系统测试和分析软件对系统运行状态进行检测，以便更好地维护系统。以便更好地维护系统。当系统运行一段时间后，用户可能会提出新的功当系统运行一段时间后，用户可能会提出新的功能要求，能要求，DBA应尽量在原有系统的基础上给予修改应尽量在原有系统的基础上给予修改和扩充。随着时间的推移和计算机技术的飞速发展，和扩充。随着时间的推移和计算机技术的飞速发展，原有系统总有一天不能满足用户的基本要求和客观环原有系统总有一天不能满足用户的基本要求和客观环境的需要，必须重新建设，到此一个数据库应用系统境的需要，必须重新建设，到此一个数据库应用系统的生命周期就结束了，新系统的生命周期就开始了。的生命周期就结束了，新系统的生命周期就开始了。5.1.5 运行与维护阶段运行与维护阶段数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.1 需求分析需求分析 假定对一个旅店进行深入地业务流程分析后画出如下所示的数据流图。5.2 数据库应用系统设计应用数据库应用系统设计应用举例举例数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.1 需求分析需求分析数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.1 需求分析需求分析 1.数据表数据表 根据数据流图和数据库设计的要求建立各种数据根据数据流图和数据库设计的要求建立各种数据表如下：表如下：(1)旅客登记表旅客登记表 由旅客填写的登记单整理而成，它可以包括旅客由旅客填写的登记单整理而成，它可以包括旅客姓名、性别、来源地、单位、身份证号、登记日期、姓名、性别、来源地、单位、身份证号、登记日期、联系电话、消费卡号等数据项组成，其中消费卡号由联系电话、消费卡号等数据项组成，其中消费卡号由总台会计填写，其余由旅客填写。总台会计填写，其余由旅客填写。数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.1 需求分析需求分析(2)客房表客房表 给出旅店的所有房源登记，每条记录表示一个床给出旅店的所有房源登记，每条记录表示一个床位，它可以包括房序号、楼号、楼层号、房间号、床位，它可以包括房序号、楼号、楼层号、房间号、床位号、房间电话、价格位号、房间电话、价格(元元/天天)、已用等数据项、已用等数据项。其。其中房序号是自动增加用以做主码的字段。中房序号是自动增加用以做主码的字段。(3)客房空闲表客房空闲表 由客房表中所有空闲床位的记录所组成，它是客由客房表中所有空闲床位的记录所组成，它是客房表的一个视图，选择条件是房表的一个视图，选择条件是“已用已用”数据项的值为数据项的值为0。数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.1 需求分析需求分析(4)消费卡表消费卡表 由所有旅客消费卡构成，它可以包括卡号、资金由所有旅客消费卡构成，它可以包括卡号、资金总额、住宿费、伙食费、娱乐费、余额等数据项。其总额、住宿费、伙食费、娱乐费、余额等数据项。其中卡号是该表的主码，资金总额是旅客预交和补交的中卡号是该表的主码，资金总额是旅客预交和补交的金额总和。金额总和。(5)住宿表住宿表 给出所有旅客当前所有住房的登记，它只需要包给出所有旅客当前所有住房的登记，它只需要包括卡号和房序号这两个数据项。由于一个旅客可以住括卡号和房序号这两个数据项。由于一个旅客可以住用多个床位，所以卡号在表中不唯一，而房序号在表用多个床位，所以卡号在表中不唯一，而房序号在表中是唯一的，在需要时可把房序号设为主码中是唯一的，在需要时可把房序号设为主码。数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.1 需求分析需求分析(6)住宿费表住宿费表 由消费卡表中的卡号、住宿费、余额等三列数据由消费卡表中的卡号、住宿费、余额等三列数据组成，它是消费卡表的一个组成，它是消费卡表的一个视图视图，是消费卡表中的全，是消费卡表中的全部记录在上述三列上的投影。只授予总台服务员查看部记录在上述三列上的投影。只授予总台服务员查看该表和修改住宿费和余额值的权利。该表和修改住宿费和余额值的权利。(7)伙食费表伙食费表 是消费卡表的一个是消费卡表的一个视图视图，是消费卡表中的全部记，是消费卡表中的全部记录在卡号、伙食费、余额等三列数据的投影。只授予录在卡号、伙食费、余额等三列数据的投影。只授予伙食服务员查看该表和修改伙食费和余额值的权利。伙食服务员查看该表和修改伙食费和余额值的权利。数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.1 需求分析需求分析(8)娱乐费表娱乐费表 是消费卡表的一个是消费卡表的一个视图视图，是消费卡表中的全部记，是消费卡表中的全部记录在卡号、娱乐费、余额等三列数据的投影。只授予录在卡号、娱乐费、余额等三列数据的投影。只授予娱乐服务员查看该表和修改娱乐费和余额值的权利。娱乐服务员查看该表和修改娱乐费和余额值的权利。(9)催补款表催补款表 是消费卡表的一个是消费卡表的一个视图视图，包括卡号和余额两项数，包括卡号和余额两项数据，选择条件是资金余额不足一天住宿费。只授予总据，选择条件是资金余额不足一天住宿费。只授予总台服务员查看该表的权利台服务员查看该表的权利。数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.1 需求分析需求分析 2.数据处理数据处理 旅店管理所涉及到的数据处理要求大致如下：旅店管理所涉及到的数据处理要求大致如下：(1)给旅客建立消费卡给旅客建立消费卡 向消费卡表中插入旅客的一条记录，该记录的资向消费卡表中插入旅客的一条记录，该记录的资金总额为预交金额，余额也等于预交金额，其他消费金总额为预交金额，余额也等于预交金额，其他消费金额均采用默认值金额均采用默认值0。(2)向消费卡追加资金向消费卡追加资金 修改消费卡表中旅客对应记录的资金总额和余额修改消费卡表中旅客对应记录的资金总额和余额数据项的值，使之增加相应的金额。数据项的值，使之增加相应的金额。数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.1 需求分析需求分析(3)给旅客登记住房给旅客登记住房 首先把每条由旅客的卡号和房序号构成的记录插首先把每条由旅客的卡号和房序号构成的记录插入到住宿表中，接着计算出该卡号一天的住宿费，然入到住宿表中，接着计算出该卡号一天的住宿费，然后修改住宿费表，使得对应记录的住宿费增加和余额后修改住宿费表，使得对应记录的住宿费增加和余额减少相应的值，最后还要修改客房表，使相应记录的减少相应的值，最后还要修改客房表，使相应记录的“已用已用”属性的值置为属性的值置为1。(4)每天每天12点时催款点时催款 每天每天12点之后查看催补款表，按卡号通知相应旅点之后查看催补款表，按卡号通知相应旅客要么退房，要么补交资金。客要么退房，要么补交资金。数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.1 需求分析需求分析(5)每天每天12点半加一天住宿费点半加一天住宿费 假定只按整天、不按半天计费。首先根据住宿表假定只按整天、不按半天计费。首先根据住宿表和客房表计算出每个卡号一天的住宿费，然后按该卡和客房表计算出每个卡号一天的住宿费，然后按该卡号修改住宿费表中的对应记录，使住宿费项增加和余号修改住宿费表中的对应记录，使住宿费项增加和余额项减少相应的值。额项减少相应的值。(6)伙食消费伙食消费 按旅客使用的卡号修改伙食费表中对应的记录，按旅客使用的卡号修改伙食费表中对应的记录，使伙食费项增加和余额项减少一次就餐开支。使伙食费项增加和余额项减少一次就餐开支。数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.1 需求分析需求分析(7)娱乐消费娱乐消费 按旅客使用的卡号修改娱乐费表中对应的记录，按旅客使用的卡号修改娱乐费表中对应的记录，使娱乐费项增加和余额项减少一次娱乐开支。使娱乐费项增加和余额项减少一次娱乐开支。(8)结算处理结算处理 首先要收回消费卡，根据住宿表中该卡号对应的首先要收回消费卡，根据住宿表中该卡号对应的所有房序号，把客房表中对应记录的所有房序号，把客房表中对应记录的“已用已用”属性的值属性的值修改为修改为0，接着从住宿表中删除该卡号对应的全部记，接着从住宿表中删除该卡号对应的全部记录，然后根据消费卡表中的相应记录，退给旅客剩余录，然后根据消费卡表中的相应记录，退给旅客剩余的金额，删除该条记录，最后还要从旅客登记表中删的金额，删除该条记录，最后还要从旅客登记表中删除掉对应的记录。除掉对应的记录。数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.2 概念设计概念设计根据需求分析的结果，得到用根据需求分析的结果，得到用ER图表示的概念设计图：图表示的概念设计图：数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.3 逻辑和物理设计逻辑和物理设计 由上一步概念设计得到由上一步概念设计得到四个基本表四个基本表：客房表、住：客房表、住宿表、消费卡表和旅客登记表，得到宿表、消费卡表和旅客登记表，得到五个视图表五个视图表：客：客房空闲表、住宿费表、伙食费表、娱乐费表和催补款房空闲表、住宿费表、伙食费表、娱乐费表和催补款表。给出表。给出SQL语言对以上表的结构定义。语言对以上表的结构定义。数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.3 逻辑和物理设计逻辑和物理设计 1.基本表：基本表：(1)客房表客房表 create table 客房表客房表 (房序号房序号 int primary key,楼号楼号 int not null,楼层号楼层号 int not null,房间号房间号 int not null,床位号床位号 int default 1,房间电话房间电话 char(5),价格价格 int not null,已用已用 int check(已用已用=0 or 已用已用=1)default 0 )数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.3 逻辑和物理设计逻辑和物理设计(2)消费卡表消费卡表 create table 消费卡表消费卡表 (卡号卡号 char(6)primary key,资金总额资金总额 float default 0,住宿费住宿费 float default 0,伙食费伙食费 float default 0,娱乐费娱乐费 float default 0,余额余额 float default 0,check(余额余额=资金总额资金总额-住宿费住宿费-伙食费伙食费-娱乐费娱乐费)数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.3 逻辑和物理设计逻辑和物理设计(3)旅客登记表旅客登记表 create table 旅客登记表旅客登记表 (姓名姓名 char(8)not null,性别性别 char(2)not null check(性别性别=男男 or 性别性别=女女),来源地来源地 char(10),单位单位 char(10),身份证号身份证号 char(18)not null,登记日期登记日期 datetime not null,联系电话联系电话 char(13),消费卡号消费卡号 char(6)not null,foreign key(消费卡号消费卡号)references 消费卡表消费卡表(卡号卡号)数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.3 逻辑和物理设计逻辑和物理设计(4)住宿表住宿表 create table 住宿表住宿表 (卡号卡号 char(6)not null,房序号房序号 int not null unique,foreign key(房序号房序号)references 客房表客房表(房序号房序号),foreign key(卡号卡号)references 消费卡表消费卡表(卡号卡号)数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.3 逻辑和物理设计逻辑和物理设计 2.视图表：视图表：(1)客房空闲表客房空闲表 create view 客房空闲表客房空闲表 as select*from 客房表客房表 where 客房表客房表.已用已用=0 在视图定义语句中，若视图名后在视图定义语句中，若视图名后省略列名表省略列名表，则该，则该视图包含查询子句中投影出的所有列。该客房空闲表视图包含查询子句中投影出的所有列。该客房空闲表就包含客房表中的所有列的定义和就包含客房表中的所有列的定义和“已用已用”列值为列值为0的所的所有行的内容有行的内容 数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.3 逻辑和物理设计逻辑和物理设计(2)住宿费表住宿费表 create view 住宿费表住宿费表 as select 卡号卡号,住宿费住宿费,余额余额 from 消费卡表消费卡表(3)伙食费表伙食费表 create view 伙食费表伙食费表 as select 卡号卡号,伙食费伙食费,余额余额 from 消费卡表消费卡表(4)娱乐费表娱乐费表 create view 娱乐费表娱乐费表 as select 卡号卡号,娱乐费娱乐费,余额余额 from 消费卡表消费卡表 数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.3 逻辑和物理设计逻辑和物理设计(5)催补款表催补款表 create view 催补款表催补款表 as select 卡号卡号,余额余额 from 消费卡表消费卡表 a where 余额余额 any(select sum(y.价格价格)from 住宿表住宿表 x,客房表客房表 y where a.卡号卡号=x.卡号卡号 and x.房序号房序号=y.房序号房序号 group by x.卡号卡号 )and 卡号卡号 in(select 卡号卡号 from 住宿表住宿表 )数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.4 机器实现机器实现 机器实现的任务机器实现的任务是：首先把逻辑阶段设计的结果，是：首先把逻辑阶段设计的结果，即数据库的逻辑模式，它包括基本表和视图的定义，即数据库的逻辑模式，它包括基本表和视图的定义，在具体的计算机系统中建立起来，然后按照需求分析在具体的计算机系统中建立起来，然后按照需求分析对数据库的各种处理要求给出相应的实现方法。对数据库的各种处理要求给出相应的实现方法。(1)给旅客建立消费卡给旅客建立消费卡 insert into 消费卡表消费卡表(卡号卡号,资金总额资金总额,余额余额)values(a,x,x)这里假定类型为这里假定类型为char(6)的字符变量的字符变量a中已经保存着一个旅中已经保存着一个旅客的消费卡卡号，类型为客的消费卡卡号，类型为float的实型变量的实型变量x中已经保存着该旅中已经保存着该旅客的预交金额。消费卡表中的住宿费、伙食费和娱乐费没有对客的预交金额。消费卡表中的住宿费、伙食费和娱乐费没有对应值输入，则自动设置为默认值应值输入，则自动设置为默认值0。数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.4 机器实现机器实现(2)向消费卡追加资金向消费卡追加资金 update 消费卡表消费卡表 set 资金总额资金总额=资金总额资金总额+y,余额余额=余额余额+y where 卡号卡号=a(3)给旅客登记住房给旅客登记住房A使用插入语句向住宿表中插入同一卡号的一条或多条住宿使用插入语句向住宿表中插入同一卡号的一条或多条住宿记录。记录。insert into 住宿表（卡号，房序号）住宿表（卡号，房序号）values(a,b)数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.4 机器实现机器实现 B查询出卡号为查询出卡号为a值的一天住宿的费用，并把它保存到值的一天住宿的费用，并把它保存到x中。中。declare a char(6)set a=0003 declare x float set x=(select sum(y.价格价格)from 住宿表住宿表 x,客房表客房表 y where a=x.卡号卡号 and x.房序号房序号=y.房序号房序号 group by x.卡号卡号)数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.4 机器实现机器实现 C按卡号查询住宿费表中的余额的值是否小于按卡号查询住宿费表中的余额的值是否小于x的值，若小的值，若小于则不能安排住宿，应追加资金后再进行下一步。于则不能安排住宿，应追加资金后再进行下一步。select 你的费用为你的费用为，余额，余额，！请追加资金的数额不小于：！请追加资金的数额不小于：，x-金额金额 from 住宿费表住宿费表 where 卡号卡号=a and 余额余额xD修改住宿费表，修改住宿费表，a和和x为上一步使用的变量。为上一步使用的变量。update 住宿费表住宿费表 set 住宿费住宿费=住宿费住宿费+x,余额余额=余额余额-x where 卡号卡号=a 数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.4 机器实现机器实现 E修改客房表，修改客房表，a仍为上一步中使用的变量。仍为上一步中使用的变量。update 客房表客房表 set 已用已用=1 where 房序号房序号 in(select 房序号房序号 from 住宿表住宿表 where 卡号卡号=a）此修改语句也可改写如下：此修改语句也可改写如下：update 客房表客房表 set 已用已用=1 from 住宿表住宿表 where 客房表客房表.房序号房序号=住宿表住宿表.房序号房序号 and 住宿表住宿表.卡号卡号=a 数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.4 机器实现机器实现(4)每天每天12点催款点催款 催款就是查看催补款表催款就是查看催补款表 select*from 催补款表催补款表 (5)每天每天12点半加一天住宿费点半加一天住宿费 SQL语句见下页语句见下页数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.4 机器实现机器实现update 住宿费表住宿费表 set 住宿费住宿费=住宿费住宿费+（select sum(y.价格价格)from 住宿表住宿表 x,客房表客房表 ywhere 住宿费表住宿费表.卡号卡号=x.卡号卡号 and x.房序号房序号=y.房序号房序号group by x.卡号卡号),余额余额=余额余额-(select sum(y.价格价格)from 住宿表住宿表 x,客房表客房表 y where 住宿费表住宿费表.卡号卡号=x.卡号卡号 and x.房序号房序号=y.房序号房序号 group by x.卡号卡号)where 卡号卡号 in（select 卡号卡号 from 住宿表住宿表)数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.4 机器实现机器实现(6)伙食消费伙食消费 select 你的费用为你的费用为,余额余额,!请减少伙食费用请减少伙食费用!from 伙食费表伙食费表 where 卡号卡号=a and 余额余额x若执行此查询时未显示出提示信息，则执行下面修改语句。若执行此查询时未显示出提示信息，则执行下面修改语句。update 伙食费表伙食费表 set 伙食费伙食费=伙食费伙食费+x,余额余额=余额余额-x where 卡号卡号=a 数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.4 机器实现机器实现(7)娱乐消费娱乐消费 此处理过程与伙食消费类似。此处理过程与伙食消费类似。select 你的费用为你的费用为,余额余额,!请减少娱乐费用请减少娱乐费用!from 娱乐费表娱乐费表 where 卡号卡号=a and 余额余额x若执行此查询时未显示出提示信息，则执行下面修改语句。若执行此查询时未显示出提示信息，则执行下面修改语句。update 娱乐费表娱乐费表 set 娱乐费娱乐费=娱乐费娱乐费+x,余额余额=余额余额-x where 卡号卡号=a 数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.4 机器实现机器实现(8)结算处理结算处理 此此操作需要分此此操作需要分4步完成。步完成。A修改客房表，退掉相应的房间，变量修改客房表，退掉相应的房间，变量a中保存着退房者的中保存着退房者的卡号。卡号。update 客房表客房表 set 已用已用=0 from 住宿表住宿表 where 客房表客房表.房序号房序号=住宿表，房序号住宿表，房序号 and 住宿表住宿表.卡号卡号=a B删除住宿表中的卡号值等于删除住宿表中的卡号值等于a的值的所有记录的值的所有记录。delete from 住宿表住宿表where 卡号卡号=a 数据库原理与SQLServer应用第5章 数据库应用系统设计5.2.4 机器实现机器实现 C退款并从消费卡表中删除对应记录。退款并从消费卡表中删除对应记录。退款金额为：退款金额为：set x=(select 余额余额 from 消费卡表消费卡表 where 卡号卡号=a)删除对应记录的操作为：删除对应记录的操作为：delete from 消费卡表消费卡表 where 卡号卡号=a D从旅客登记表中删除卡号值等于从旅客登记表中删除卡号值等于a值的旅客记录。值的旅客记录。delete from 旅客登记表旅客登记表 where 卡号卡号=a