answer_3.htm

软件设计师模拟试卷,点击里面的Exe文件可以随机出题

style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>表示的是在开具发票时该商品的单价。<span
lang=EN-US><o:p></o:p></span></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>【解析】设计一中根据题意可得出以下函数依赖：<span
lang=EN-US><o:p></o:p></span></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Ino</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>→</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Sno</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>，</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Cno</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>，</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Idate<o:p></o:p></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>而关系</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Invoice</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>的主码是</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Ino</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>和</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Mno</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>。非主属性</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Sno</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>、</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Cno</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>和</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Idate</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>并非完全依赖于主码，因此关系</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Invoice</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>不满足第二范式，最高满足第一范式。<span
lang=EN-US><o:p></o:p></span></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>关系</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Invoice</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>的设计的不合理在于该关系中将发票的单值属性</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>(</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>发票号码</span><u><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Ino</span></u><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>，交易日期</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Idate</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>，顾客代码</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Cno</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>，收银员代码</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Sno)</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>和多值属性</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>(</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>商品代码</span><u><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Mno</span></u><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>，单价</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>unitprice</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>，数量</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>amount)</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>混合在一个关系中，造成关系</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Invoice</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>的冗余异常、修改异常和删除异常。而设计二则将设计一中关系</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Invoice</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>分解，使得发票的单值属性和多值属性分开，避免了异常。因此，设计二明显比设计一要好。<span
lang=EN-US><o:p></o:p></span></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>问题</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>2</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>是要建立</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>2005</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>年</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>1</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>月期间每张发票的发票号，交易日期，交易商品件数和交易总金额的视图。<span
lang=EN-US><o:p></o:p></span></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>首先建立视图的格式为</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>CREATE VIEW&lt;</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>视图名</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>&gt;AS&lt;</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>视图定义</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>&gt;</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>，因此</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>(1)</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>空的答案为</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>AS</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>。<span
lang=EN-US><o:p></o:p></span></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>本查询是从</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Invoice</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>和</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Invoicedetail</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>两个关系中查询，两关系的连接条件是两关系的</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Ino</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>相等，因此</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>(4)</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>空的答案是</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Invoice.Ino=Invoicedetail.Ino</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>。<span
lang=EN-US><o:p></o:p></span></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>统计每张发票的信息需要按发票将数据分组，也就是按发票号</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Ino</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>分组，但因为查询关系</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Invoice</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>和</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Invoicedetail</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>都有属性</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Ino</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>。为了避免二义性，所以分组属性是</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Invoice.Ino</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>或者是</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Invoicedetail.Ino</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>。因为在包含聚合运算的</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Select</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>子句中，只有在</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Group By</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>子句中出现的属性才能在</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>SELECT</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>子句中以非聚合形式出现，而</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>SELECT</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>子句中有非聚合形式的属性</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Idate</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>出现，所以</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>(5)</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>空的答案是</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Invoice.Ino.Idate</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>或</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Invoicedetail.Ino.Idate</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>。<span
lang=EN-US><o:p></o:p></span></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>需要查询的是每张发票的交易商品件数和交易总金额。交易商品件数是发票商品数量的总和，因此</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>(2)</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>空的答案是</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>SUM(amount)</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>。交易总金额是每条交易商品明细中每条记录商品金额的总和，每条记录商品金额是</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>unitprice*amount</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>，因此</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>(3)</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>空的答案是</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>SUM(unitprice*amount)</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>。<span
lang=EN-US><o:p></o:p></span></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>问题</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>3</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>是查询从未售出的商品信息。<span
lang=EN-US><o:p></o:p></span></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>SQL</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>语句中有两种格式为表或视图取别名：<span
lang=EN-US>&quot;表名</span></span><span lang=EN-US style='mso-bidi-font-size:
10.5pt'>AS</span><span style='mso-bidi-font-size:10.5pt;font-family:宋体;
mso-hansi-font-family:"Times New Roman"'>别名<span lang=EN-US>&quot;或&quot;表名别名&quot;。由题中可以看出</span></span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Merchandise</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>的别名是</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>A</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>，因此填空</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>(1)</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>的答案是</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>A</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>或者</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>AS A</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>。<span
lang=EN-US><o:p></o:p></span></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>要查询<span
lang=EN-US>&quot;从未出售&quot;的商品，也就是要查询的商品在交易记录中不存在，因此</span></span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>(2)</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>空的答案是</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>NOT EXISTS</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>。<span
lang=EN-US><o:p></o:p></span></span></p>

<p class=MsoNormal align=left style='text-align:left;mso-layout-grid-align:
none;text-autospace:none'><span lang=EN-US style='mso-bidi-font-size:10.5pt'>Merchandise</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>中由属性</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>price</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>表示的是商品的当前价格，超市中的价格是有可能变动的，而关系</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>Invoicedetail</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>中的属性</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>unitprice</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>表示的是在开具发票时该商品的单价。如果缺少其中任意一个，将导致商品单价不能进行调整，否则，当商品的单价发生变化时，销售历史中的商品价格就随着发生变化。</span><span
style='font-size:8.5pt;font-family:"MS Sans Serif";mso-font-kerning:0pt;
mso-ansi-language:ZH-CN'><o:p></o:p></span></p>

<p class=MsoNormal><span lang=EN-US><![if !supportEmptyParas]>&nbsp;<![endif]><o:p></o:p></span></p>

</div>

</body>

</html>