●试题三 阅读下列说明,回答问题1~问题3,将解答填入答题纸的对应栏内。 【说明】 某超市的销售业

Question

●试题三

阅读下列说明，回答问题1～问题3，将解答填入答题纸的对应栏内。

【说明】

某超市的销售业务由一个销售业务管理系统进行管理，该系统每完成一次交易都需要提供顾客发票，其格式见表6所示。

对于这样一个销售业务管理系统，分别给出了以下两种关系数据库的设计(下划线表示主关键字)

设计一：

顾客Customer(顾客代码Cno，姓名name，住址address，联系电话phone)

收银员Salesman(收银员代码Sno，身份证号idno，姓名name，住址address，联系电话phone)

商品Merchandise(商品代码Mno，商品名称Mname，价格price)

发票Invoice(发票号码Ino，交易日期Idate，顾客代码Cno，收银员代码Sno，商品代码Mno，单价unitprice，数量amount)

设计二：

顾客Customer(顾客代码Cno，姓名name，住址address，联系电话phone)

收银员Salesman(收银员代码Sno，身份证号idno，姓名name，住址address，联系电话phone)

商品Merchandise(商品代码Mno，商品名称Mname，价格price)

发票Ivoice(发票号码Ino，交易日期Idate，顾客代码Cno，收银员代码Sno)

发票明细Invoicedetail(发票号码Ino，商品代码Mno，单价unitprice，数量amount)

【问题1】

设计一的关系模式Invoice最高满足第几范式?为什么?设计一和设计二哪个更加合理?为什么?

【问题2】

根据设计二中关系模式，以下SQL语句是用于"建立2005年1月期间每张发票的发票号，交易日期，交易商品件数和交易总金额的视图"的不完整语句，请填补其中的空缺。

CREATE VIEW Invoice -total (1)

SELECT Invoice.Ino，Idate， (2) ， (3)

FROM Invoice，Invoicedetail

WHERE (4) AND

Idate BETWEEN′2005-01-01′AND′2005-01-31′

GROUPBY (5) ；

【问题3】

根据设计二中关系模式，以下SQL语句是用于"查询从未售出的商品信息"的不完整语句，请填补其中的空缺。

SELECT Mno，Mname，price

FROM Merchandise (1)

WHERE (2)

(SELECT (3)

FROM Invoicedetail

WHERE A．Mno=Invoicedetail.Mno)；

【问题4】

设计二中关系Merchandise中由属性price表示商品价格，关系Invoicedetail中的属性unitprice也表示商品价格。两个是否有必要同时存在?为什么?

Answer 1

正确答案：
●试题三［问题1］【答案】设计一中Invoice最高满足第一范式。根据题意可得出以下函数依赖：Ino→Sno，Cno，Idate而关系Invoice的主码是Ino和Mno。非主属性Sno、Cno和Idate并非完全依赖于主码，因此关系Invoice不满足第二范式，最高满足第一范式。设计二更加合理。因为设计二解决了设计一中由于非主属性不完全依赖于主码而造成的数据冗余等问题。［问题2］【答案】(1)AS(2)SUM(amount)(3)SUM(unitprice*amount)(4)Invoice.Ino=Invoicedetail.Ino(5)Invoice.Ino，Idate或Invoicedetail.Ino.Idate［问题3］【答案】(1)A和ASA(2)NOTEXISTS(3)*［问题4］【答案】有必要。Merchandise中由属性price表示的是商品的当前价格，超市中的价格是有可能变动的，而关系Invoicedetail中的属性unitprice表示的是在开具发票时该商品的单价。【解析】设计一中根据题意可得出以下函数依赖：Ino→Sno，Cno，Idate而关系Invoice的主码是Ino和Mno。非主属性Sno、Cno和Idate并非完全依赖于主码，因此关系Invoice不满足第二范式，最高满足第一范式。关系Invoice的设计的不合理在于该关系中将发票的单值属性(发票号码Ino，交易日期Idate，顾客代码Cno，收银员代码Sno)和多值属性(商品代码Mno，单价unitprice，数量amount)混合在一个关系中，造成关系Invoice的冗余异常、修改异常和删除异常。而设计二则将设计一中关系Invoice分解，使得发票的单值属性和多值属性分开，避免了异常。因此，设计二明显比设计一要好。问题2是要建立2005年1月期间每张发票的发票号，交易日期，交易商品件数和交易总金额的视图。首先建立视图的格式为CREATEVIEW<视图名>AS<视图定义>，因此(1)空的答案为AS。本查询是从Invoice和Invoicedetail两个关系中查询，两关系的连接条件是两关系的Ino相等，因此(4)空的答案是Invoice.Ino=Invoicedetail.Ino。统计每张发票的信息需要按发票将数据分组，也就是按发票号Ino分组，但因为查询关系Invoice和Invoicedetail都有属性Ino。为了避免二义性，所以分组属性是Invoice.Ino或者是Invoicedetail.Ino。因为在包含聚合运算的Select子句中，只有在GroupBy子句中出现的属性才能在SELECT子句中以非聚合形式出现，而SELECT子句中有非聚合形式的属性Idate出现，所以(5)空的答案是Invoice.Ino.Idate或Invoicedetail.Ino.Idate。需要查询的是每张发票的交易商品件数和交易总金额。交易商品件数是发票商品数量的总和，因此(2)空的答案是SUM(amount)。交易总金额是每条交易商品明细中每条记录商品金额的总和，每条记录商品金额是unitprice*amount，因此(3)空的答案是SUM(unitprice*amount)。问题3是查询从未售出的商品信息。SQL语句中有两种格式为表或视图取别名："表名AS别名"或"表名别名"。由题中可以看出Merchandise的别名是A，因此填空(1)的答案是A或者ASA。要查询"从未出售"的商品，也就是要查询的商品在交易记录中不存在，因此(2)空的答案是NOTEXISTS。Merchandise中由属性price表示的是商品的当前价格，超市中的价格是有可能变动的，而关系Invoicedetail中的属性unitprice表示的是在开具发票时该商品的单价。如果缺少其中任意一个，将导致商品单价不能进行调整，否则，当商品的单价发生变化时，销售历史中的商品价格就随着发生变化。