《第六章数据库设计和模型.ppt》由会员分享,可在线阅读,更多相关《第六章数据库设计和模型.ppt(23页珍藏版)》请在三一文库上搜索。
1、Copyright by ECNU CS DBLAB All rights reserved. 第六章 数据库设计和E-R模型 数据库系统概念 习题分析与解答 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspos
2、e Pty Ltd. Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Copyright by ECNU CS DBLAB All rights reserved. 数据库系统概念 u
3、习题目录 l习题6.1 (参阅课本P167) l习题6.2 (参阅课本P167) l习题6.3 (参阅课本P168) l习题6.6 (参阅课本P168) l习题6.12 (参阅课本P168) l习题6.13 (参阅课本P169) l习题6.15 (参阅课本P169) l习题6.21 (参阅课本P169) 2 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Pr
4、ofile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011
5、 Aspose Pty Ltd. Copyright by ECNU CS DBLAB All rights reserved. 数据库系统概念 3 v 第六章 数据库设计和E-R模型 6.1 为车辆保险公司设计一个E - R 图。每个客户有一 到多辆车。每辆车可能发生0次或任意多次事故的记录。 返回 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profi
6、le 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 As
7、pose Pty Ltd. Copyright by ECNU CS DBLAB All rights reserved. 数据库系统概念 4 6.2为一个大学的注册办公室维护关于以下实体的数据 (a)课程,包括编号、名称、学分、课程提纲和选修条件; (b)课程提供,包括课程编号、年、学期、节数、教师、时 间和教室; (c)学生,包括学号、姓名和计划(p r o g r a m ); (d)教师,包括标识号、姓名、系和职称。此外,学生课程 的登记和学生所选的每门课的成绩评定都要适当地建模。 v 第六章 数据库设计和E-R模型 Evaluation only.Evaluation only. C
8、reated with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created
9、 with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Copyright by ECNU CS DBLAB All rights reserved. 数据库系统概念 5 参考解答: 为注册办公室构造一个E - R 图,并说明你所做的映射约束的假设。 这里有以下几个实体集:student,instructor,course和course- offerings,其中course-offerings是
10、弱实体集,依赖于course。所做的假 设如下: 一个班级只能在一个特定的地方。该E-R图不满足在不同时间地方也不同 的班级。 两个班级有可能会在时间、地点上发生冲突。 一个班级一个班主任(指导老师)。 v 第六章 数据库设计和E-R模型 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011
11、Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Copyright by ECNU
12、CS DBLAB All rights reserved. 数据库系统概念 6 v 第六章 数据库设计和E-R模型 返回 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Evaluati
13、on only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Copyright by ECNU CS DBLAB All rights reserved. 数据库系统概念 7 6.3考虑一个用于记录学生各种选课考试
14、成绩的数据库。 a. 构造一个将考试建模成实体的E - R 图,为以上的数据库 设计一个三元联系。 b. 构造一个只用二元联系来连接students 和course- offerings 的可选E - R 图。要求在特定学生和课程对之间只 有一个联系,而且可以表示出学生在不同选修的课程的成绩。 v 第六章 数据库设计和E-R模型 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.
15、5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyrigh
16、t 2004-2011 Aspose Pty Ltd. Copyright by ECNU CS DBLAB All rights reserved. 数据库系统概念 8 v 第六章 数据库设计和E-R模型 6.3.a Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty
17、 Ltd.Copyright 2004-2011 Aspose Pty Ltd. Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Copyright by ECNU CS DBLAB A
18、ll rights reserved. 数据库系统概念 9 v 第六章 数据库设计和E-R模型 6.3.b 返回 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Evaluation o
19、nly.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Copyright by ECNU CS DBLAB All rights reserved. 数据库系统概念 10 v第二章 关系模型 6.6考虑一个为期末考试
20、安排教师的大学数据库。这个数据库可 被建模为具有属性course -name 、section-number、room- number和time的单个实体exa m 。也可以定义一个或多个附加 实体集,同时用联系集来代替exam 实体集的一些属性,例如: course 有属性name、department、c-number 。 section 有属性s-number和enrollment,并作为依赖course 上的一个弱实 体集。 room有属性r-number、capacity 和building 。 a. 用E - R 图来说明列出的三个附加实体集的应用。 b. 解释哪些应用特征会对是否
21、加入一些附加实体集的决定产生影响。 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Evaluation only.Evaluation only. Created with Aspos
22、e.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Copyright by ECNU CS DBLAB All rights reserved. 数据库系统概念 11 v 第六章 数据库设计和E-R模型 6.6.a Evaluation only.Evaluation only. Crea
23、ted with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created wi
24、th Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Copyright by ECNU CS DBLAB All rights reserved. 数据库系统概念 l如果我们想要把实体的属性作为数据库的一部分来存储, 那么附加的实体集也是有用的。对course实体集,我们让 它包含3个属性。如果仅包含主码(c-number),并且每 个course都只有一个section,那么用一个exam的一个属
25、 性(c-name)去代替course(和section)实体是合适的 。把course的多个属性当作exam的一个属性不合适的理由 就是很难维护关于course的数据,尤其当一门course没有 或者有多个exam时。同样的情况也发生在room实体集上。 12 6.6.b v 第六章 数据库设计和E-R模型 返回 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Clien
26、t Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-
27、2011 Aspose Pty Ltd. Copyright by ECNU CS DBLAB All rights reserved. 数据库系统概念 6.12 两个相互独立的银行将要合并,假设两个银行使用相 同的E - R 数据库模式,即图6-25中的数据库模式。(当 然,这个假设很不实际,我们将在22.8节讨论更实际的情 况。)如果合并后的银行只有一个数据库,那么可能存在 如下问题: l合并前的两个银行可能有某些分支机构名称相同。 l可能有的客户同时是两个银行的客户。 l某些贷款号和账户号可能在两个银行中都使用(却代表不 同的贷款和账户)。 l对这些潜在的问题,说明为什么确实可能会存在困
28、难。提 出问题的一种解决办法,并说明采用这样的办法需要做的 修改以及这些修改对模式和数据的影响。 13 v 第六章 数据库设计和E-R模型 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.
29、 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Copyright by ECNU CS DBLAB All rights reserved. 数据库系统概念 14 参考解答: 在这个
30、例子中,我们假设两家银行共享客户的标识码,比如社会保障号一 样。我们在下一个练习中会得到通用的解决方式。 提到的每个问题都存在潜在的问题: A. branch-name是branch的主码,这样,在合并时,如发生重复的名字,只能 保留一条,丢失了另外的信息。 B.遇到客户名发生冲突(有重复),则customer中,重复的记录将丢失。而顾 客与cust-banker,borrower,depositor相关联,上述重复信息的丢失,会影 响这三者,须及时补救更新。 C.而loan与accounts有相同的帐号,也会引发类似于(A)中的情况。 要解决上述由于合并而引发的问题,模式不需要更改,对cus
31、tomer实体, 删去重复social-security的原则/元组;对branch,在合并时,在银行名前添 加旧银行的标识。employee/payment立即可以合并,而loan与account的 number添加一位作为区分,比如611,用1611表示bank1,而2611表示bank2。 v 第六章 数据库设计和E-R模型 返回 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NE
32、T 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copy
33、right 2004-2011 Aspose Pty Ltd. Copyright by ECNU CS DBLAB All rights reserved. 数据库系统概念 6.13假设一个银行在美国,而另一个在加拿大,重新考虑上 题所提出的问题。同样地,两个银行均采用图2 - 2 2 的 模式,只是加拿大的银行用由加拿大政府赋予的社会保险 号,而美国银行使用社会保障号。除了在习题2 . 2 4 中 指出的问题外,在这种多个国家的情况下还会有什么样的 问题?你将怎样解决?请注意既要考虑模式又要考虑数据 实际的值。 15 v 第六章 数据库设计和E-R模型 Evaluation only.Ev
34、aluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile
35、 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Copyright by ECNU CS DBLAB All rights reserved. 数据库系统概念 16 参考解答: 这是两个银行连模式都不同的情况,因此合并也变得更加复杂。美国的客 户标识码属性是社会保障号,而加拿大的是社会保险号。因此合并后的模式不 能使用任何一种模式。我们介绍一个新的属性per
36、son-id,在合并后的模式中用 这个属性来标识每个客户。除了这个,对模式方面不需要有其他的改变了。获 取person-id的值可以通过多种途径,一种方法就是在原来的社会保障号和社会 保险号之前架上国家标识,比如U表示美国,C表示加拿大,这样来的到新的 person-id的值。另一种方法就是增加1位,这一位来表示后面的数据是社会保 障号还是社会保险号。 一旦完成这项工作,后面的事情与前一道习题就比较类似了。如果一个特 定的联系集,比如borrower,仅仅包含美国的客户,在合并后的数据库中就通 过把实体集customer特殊化成us-customer和canada-customer,在合并后的
37、 borrower中仅有us-customer的部分。如果需要也可以用类似的方法来特殊化 employee。 v 第六章 数据库设计和E-R模型 返回 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pt
38、y Ltd. Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Copyright by ECNU CS DBLAB All rights reserved. 数据库系统概念 6.15为医
39、院设计一个E-R 图。医院有很多病人和很 多医生。同每个病人相关的是一系列检查和测试 的记录。 17 v 第六章 数据库设计和E-R模型 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.
40、Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Copyright by ECNU CS DBLAB All rights reserved. 数据库系统概念 6.15 18 v 第六章
41、 数据库设计和E-R模型 返回 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Evaluation only.Evaluation only. Created with Aspose.
42、Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Copyright by ECNU CS DBLAB All rights reserved. 数据库系统概念 6.21考虑图6-31中的E - R 图,它为一家网上书店建模。 la .列出实体集和它们的主码。 lb .假设书店增加了音乐磁带和
43、唱片。相同的音乐会出现在 磁带或唱片中,但具有不同的价格。扩展E - R 图来为 这个附加特点建模,忽略对购物的影响。 lc. 现在用一般化来扩展E - R 图,从而可对书、音乐磁带 和唱片的任一组合购物进行建模。 19 v 第六章 数据库设计和E-R模型 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2
44、004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Copyright
45、 by ECNU CS DBLAB All rights reserved. 数据库系统概念 20 v 第六章 数据库设计和E-R模型 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. E
46、valuation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Copyright by ECNU CS DBLAB All rights reserved. 数据库系统概念 21 参考解答:6.21.a
47、 v 第六章 数据库设计和E-R模型 实实体集主码码 authorname,address publishername bookISBN shopping-basketbasketID customeremail warehousecode Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011
48、 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Copyright by ECNU
49、 CS DBLAB All rights reserved. 数据库系统概念 22 参考解答:6.21.b v 第六章 数据库设计和E-R模型 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd. Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Lt
链接地址:https://www.31doc.com/p-3476728.html