【计算机类职业资格】数据库系统工程师-关系模式规范化与应用1及答案解析.doc

1、数据库系统工程师-关系模式规范化与应用 1及答案解析(总分：30.00，做题时间：90 分钟)一、试题 1(总题数：1，分数：15.00)说明某超市的销售业务由一个销售业务管理系统进行管理，该系统每完成一次交易都需要提供顾客发票，其格式如表 5-31所示。（分数：15.00）(1).(1) 设计一的关系模式“发票”最高满足第几范式?请用 200字以内的文字简要说明理由。(2) 设计一和设计二哪个更加合理?请用 100字以内的文字简要说明理由。（分数：3.75）_(2).根据设计二中的关系模式，以下 SQL语句是用于“建立 2010年 11月期间每张发票的发票号、交易日期、交易商品件数和交易总金

2、额的视图”的不完整语句，请填补其中的空缺。CREATE VIEW 发票_合计 (1) SELECT 发票.发票号码,交易日期, (2) , (3) FROM 发票,发票明细WHERE (4) AND交易日期 BETWEEN 2010-11-01AND 2010-11-30GROUP BY (5) ;（分数：3.75）_(3).根据设计二中的关系模式，以下 SQL语句是用于“查询从未售出的商品信息”的不完整语句，请填补其中的空缺。SELECT 商品代码,商品名称,价格FROM商品 (6) WHERE (7) ( SELECT (7) FROM 发票明细WHERE A. 商品代码=发票明细. 商品

3、代码);（分数：3.75）_(4).设计二中关系“商品”中的属性“价格”表示商品价格，关系“发票明细”中的属性“单价”也表示商品价格，这两个属性是否有必要同时存在?请用 200字以内的文字简要说明理由。（分数：3.75）_二、试题 2(总题数：1，分数：15.00)说明某简化的网上购物系统的 E-R图如图 5-10所示。（分数：15.00）(1).请根据相关约束条件，请将图 5-10中(a)(f)空缺处的联系类型补充完整。（分数：5.00）_(2).请将如图 5-10所示的 E-R图转换成符合 3NF的关系模式，并给出各关系模式的主键和外键。（分数：5.00）_(3).请修改所设计的关系模式，

4、使其支持下列功能(仅列出发生修改的关系模式，并给出这些修改后关系模式的主键和外键)。(1) 使客户能方便了解每个订购细目的处理状态和到货时间。(2) 使客户能方便查询商品供应商的编号、名称、所在城市、联系电话和信誉(限定一个供应商可以供应多种商品，一种商品只选自一个供应商)。（分数：5.00）_数据库系统工程师-关系模式规范化与应用 1答案解析(总分：30.00，做题时间：90 分钟)一、试题 1(总题数：1，分数：15.00)说明某超市的销售业务由一个销售业务管理系统进行管理，该系统每完成一次交易都需要提供顾客发票，其格式如表 5-31所示。（分数：15.00）(1).(1) 设计一的关系模

5、式“发票”最高满足第几范式?请用 200字以内的文字简要说明理由。(2) 设计一和设计二哪个更加合理?请用 100字以内的文字简要说明理由。（分数：3.75）_正确答案：(1)设计一的关系模式“发票”最高满足第一范式(1NF)因为由题意可得出以下函数依赖。发票号码(顾客代码,收银员代码,交易日期)而关系“发票”的主键是(发票号码,商品代码)。非主属性“顾客代码”、“收银员代码”和“交易日期”并非完全依赖于主码，因此该关系模式不能满足第二范式(2NF)，最高满足第一范式(2) 设计二更加合理。因为设计二解决了设计一中由于非主属性不完全依赖于主码而造成的数据冗余等问题)解析：(2).根据设计二中的

6、关系模式，以下 SQL语句是用于“建立 2010年 11月期间每张发票的发票号、交易日期、交易商品件数和交易总金额的视图”的不完整语句，请填补其中的空缺。CREATE VIEW 发票_合计 (1) SELECT 发票.发票号码,交易日期, (2) , (3) FROM 发票,发票明细WHERE (4) AND交易日期 BETWEEN 2010-11-01AND 2010-11-30GROUP BY (5) ;（分数：3.75）_正确答案：(1)AS(2) SUM(数量)(3) SUM(单价*数量)(4) 发票.发票号码=发票明细.发票号码(5)发票.发票号码,交易日期或发票明细.发票号码,交易

7、日期)解析：(3).根据设计二中的关系模式，以下 SQL语句是用于“查询从未售出的商品信息”的不完整语句，请填补其中的空缺。SELECT 商品代码,商品名称,价格FROM商品 (6) WHERE (7) ( SELECT (7) FROM 发票明细WHERE A. 商品代码=发票明细. 商品代码);（分数：3.75）_正确答案：(6)A 或者 AS A(7) NOT EXISTS(8) *)解析：(4).设计二中关系“商品”中的属性“价格”表示商品价格，关系“发票明细”中的属性“单价”也表示商品价格，这两个属性是否有必要同时存在?请用 200字以内的文字简要说明理由。（分数：3.75）_正确答

8、案：(有必要。因为属性“价格”表示的是商品的当前价格，它在不同时间段有可能是变动的；属性“单价”表示的是在开具发票时该商品的单价。若缺少其中任意一个，将导致商品单价不能进行调整，否则当商品的单价发生变化时，销售历史中的商品价格将随之发生变化)解析：二、试题 2(总题数：1，分数：15.00)说明某简化的网上购物系统的 E-R图如图 5-10所示。（分数：15.00）(1).请根据相关约束条件，请将图 5-10中(a)(f)空缺处的联系类型补充完整。（分数：5.00）_正确答案：(a) 1 (b) n 或 m或*(c) 1 (d) n或 m或*(e) n或 m或* (f) 1)解析：(2).请将

9、如图 5-10所示的 E-R图转换成符合 3NF的关系模式，并给出各关系模式的主键和外键。（分数：5.00）_正确答案：(客户(客户编号,姓名,电话,E-mail)订单(订单编号,订购时间,客户编号)订购细目(订购细目编号,订购数量,支付金额,订单编号,商品编号)商品(商品编号,商品名称,单价,生产日期,商品类别号)商品类别(商品类别号,商品类别名)关系模式 主键 外键客户 客户编号 无订单 订单编号 客户编号订购细目 订购细目编号 订单编号,商品编号商品 商品编号 商品类别号商品类别 商品类别号 无)解析：(3).请修改所设计的关系模式，使其支持下列功能(仅列出发生修改的关系模式，并给出这些

10、修改后关系模式的主键和外键)。(1) 使客户能方便了解每个订购细目的处理状态和到货时间。(2) 使客户能方便查询商品供应商的编号、名称、所在城市、联系电话和信誉(限定一个供应商可以供应多种商品，一种商品只选自一个供应商)。（分数：5.00）_正确答案：(1)订购细目(订购细目编号,订购数量,支付金额,订单编号,商品编号,处理状态,到货时间)(2) 商品供应商(商品供应商编号,名称,所在城市,联系电话,信誉)商品(商品编号,商品名称,单价,生产日期,商品类别号,商品供应商编号)关系模式 主键 外键订购细目 订购细目编号 订单编号,商品编号商品 商品编号 商品类别号,商品供应商编号商品供应商 商品供应商编号 无)解析：