设计与应用题
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 下面对应的 块为过程体 */
……… .
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 类,各汇总表都建有主码, 且各表有合理的维护策
略,在每次维护后数据