计算机数据库三级设计与应用题讲述.doc

设计与应用题 1、设某教学管理系统，其查询模块需要提供如下功能： Ⅰ．查询系信息，列出各系编号、系名和系办公电话； Ⅱ．查询某系教师的信息，列出教师号、教师名、工资和聘用日期； Ⅲ．查询某教师讲授的课程信息，列出课程号、课程名和学分； Ⅳ．查询讲授某门课程的教师信息，列出教师名和职称； Ⅴ．查询某门课程的先修课程信息，列出先修课程号和先修课程名。 系统有如下业务规则： Ⅰ．一个系可聘用多名教师，一名教师只能受聘于一个系； Ⅱ．一名教师可讲授多门课程，一门课程可由多名教师讲授； Ⅲ．一门课程可以有多门先修课程，也可以没有先修课程。 (1)请根据以上查询功能与业务规则，用ER图描述该系统的概念模型。（5分） (2)将ER图转换为满足3NF的关系模式，并说明每个关系模式的主码和外码。（5分） (1)【解题思路】 E-R图也称实体-联系图，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。为了简化E-R图的处置，现实世界的事物能作为属性对待的则尽量作为属性对待。实体与属性的划分给出如下两条规则：①作为属性，不能再具有需要描述的性质，属性必须是不可分的数据项，不能包含其它属性。②属性不能与其它实体有联系，即E-R图中所表示的联系是实体之间的联系。本题中一个系可以聘用多名教师，一名教师只能受聘于一个系，所以系实体与教师实体有联系；一名教师可以讲授多门课程，一门课程可由多名教师讲授，所以教师实体与课程实体有联系，一门课程可以有多门先修课程，所以课程间也有联系。 (2)【解题思路】 要想使转换生成的关系模式满足3NF，则必须满足关系模式中每一个非主属性既不部分依赖于码也不传递依赖于码。 2、设有商品表（商品号，商品名，单价）和销售表（销售单据号，商品号，销售时间，销售数量，销售单价）。其中，商品号代表一类商品，商品号、单价、销售数量和销售单价均为整型。 请编写查询某年某商品的销售总毛利的存储过程，毛利=销售数量×（销售单价－单价）。要求商品号和年份为输入参数，总毛利用输出参数返回。（10分） 【解题思路】 存储过程是由PL/SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，使用时只要调用即可。使用存储过程具有以下优点：其已经编译和优化过了，所以运行效率高，提供了在服务器端快速执行SQL语句的有效途径；存储过程降低了客户端和服务器之间的通信量；方便实施企业规则，当企业规则发生变化时只要修改存储过程，而无需修改其他应用程序。 创建存储过程： create procedure 过程名 @［参数名］ ［类型］,@［参数名］ ［类型］ /*过程首部*/ As Declare/*as下面对应的PL/SQL块为过程体*/ ………. begin ......... end 如上所示，存储过程包括过程首部和过程体。过程名是数据库服务器合法的对象标识；参数列表：用名字来标识调用时给出的参数值，必须指定值的数据类型。参数可以是输入参数或输出参数，默认为输入参数。 3、设某全国性的运输企业建立了大型OLTP系统，并在该系统之上建立了数据仓库。OLTP系统和数据仓库中有如下数据表： 运输明细表（运输单ID，发送站ID，终到站ID，货物ID，货物重量，运输价格，发货日期） 汇总表1（发送站ID，终到站ID，货物ID，发货日期，总重，总运价） 汇总表2（发送站ID，终到地区ID，货物ID，发货日期，总重，总运价） 汇总表3（发送站ID，终到站ID，货物ID，发货月份，总重，总运价） 汇总表4（发送地区ID，终到地区ID，货物类别ID，发货日期，总重，总运价） 该企业管理的货运站约有100个，货物约有500种共10类，各汇总表都建有主码，且各表有合理的维护策略，在每次维护后数据能保持一致。设有视图V，该视图的访问频率很高，其查询结果模式为（发送地区ID，终到站ID，发货月份，总重，总运价），该视图现以汇总表1为计算数据源。经监控发现，汇总表1的被访问频率过高，导致系统整体性能下降，而其它汇总表被访问频率较低。在不增加汇总表和索引的情况下，请给出一个改善系统服务性能的优化方案，并简要说明理由。（10分） 【解题思路】 计算机系统中存在着两类不同的数据处理工作：操作型处理和分析型处理，也称作OLTP(联机事务处理)和OLAP(联机分析处理)。操作型处理也叫事务处理，是指对数据库联机的日常操作，通常是对一个或一组纪录的查询或修改，例如火车售票系统、银行通存通兑系统、税务征收管理系统等。这些系统要求快速响应用户请求，对数据的安全性、完整性以及事务吞吐量要求很高。结合本题中存在的问题，视图本身的访问量很高，而又仅仅以汇总表1为计算数据源，而其它汇总表访问率低，导致了资源利用不合理。因此本题考察了联机事务处理中的资源调度问题。 4、设在SQL