第六次作业-数据库建模

具体要求：

1、作业组织：先在作业本完成每题的答题，再将作业本中的每页答题拍照贴到答题卡文件相应位置，答题卡文件命名：学号姓名-第6次作业.doc（例如2020011111张三-第6次作业.doc），注意排版格式。

2、递交方式：在【优学院】—【作业】相应栏目上传自己的作业答题卡文件供老师批改，批改后返回给大家再上传到网安学院作业系统的相关栏目。（注意：时间限制）。

 

第4章 数据库建模

P154-156    4.2、4.3、4.4 

答案如下：

4.2  某企业的供应商、商品、仓库之间存在多对多的三元联系集“供应-库存”， 如图4-75所示。请在调研企业应用需求的基础上，将该三元联系集转化为二元联系集。

供应商与仓库之间直接发生联系不符合业务语义要求。剩下2种建模情况如下所示：

1、如何理解“供应-库存”业务语义

    1）“供应”业务相当于“采购”业务，一个是站在供应商的角度命名，一个是站在企业员工的角度命名。

    2）“库存”管理包括“入库”和“出库”管理，但对应“供应”业务的就只有“入库”管理了。“出库”管理与“销售”业务相对应。为了便于理解，下面都将“库存”改为“入库”，“库存量”改为“入库量”。

2、图(a)建模方案的业务语义分析

1）联系集入库反映企业每一种商品在各仓库的入库业务，主码是{商品编号, 仓库号}，联系集入库的联系属性有入库量、单价、入库日期等。

2）联系集供应反映各供应商为企业每一次商品入库(同一种商品在同一个仓库的入库，对应联系实体集入库，主码是{商品编号, 仓库号})的供应业务，主码有{供应商号, 商品编号, 仓库号}，联系属性有供应量、单价、供应日期等。

3）由于一种商品在同一个仓库会发生多次入库业务，因此入库是一个多对多的多值联系集。可将多对多的多值联系集入库转化建模为依赖实体集入库单：

4）由于一个供应商可为每一次商品入库(同一种商品在同一个仓库的入库)发生多次供应业务，因此供应是一个多对多的多值联系集。可将多对多的多值联系集供应转化建模为依赖实体集供应单。

5）因此，图(a)建模方案的业务语义是：先有商品入库，再有商品供应；即在办理商品入库业务时，再明确每一种入库商品的商品供应情况。

    显然，该建模方案不是太符合实际业务语义。

 

 

 

3、图(b)建模方案的业务语义分析

1）联系集供应反映每一个供应商向企业提供每一种商品的供应业务，主码是{供应商号, 商品编号}，联系集供应的联系属性有供应量、单价、供应日期等。

2）联系集入库反映各仓库为每一次商品供应(同一个供应商对同一种商品的供应，对应联系实体集供应，主码是{供应商号, 商品编号})的入库业务，主码是{仓库号, 供应商号, 商品编号}，联系集入库的联系属性有入库量、单价、入库日期等。

3）由于一个供应商对同一种商品会发生多次供应业务，因此供应是一个多对多的多值联系集。可建模为依赖实体集供应单：

4）由于一个仓库可为每一次商品供应(同一个供应商对同一种商品的供应)发生多次入库业务，因此入库是一个多对多的多值联系集。可建模为：

5）因此，图(b)建模方案的业务语义是：先有商品供应，再有商品入库；即先办理商品供应业务，待每一种供应商品到货时再办理商品入库手续。

4、总之，图(b)的建模方案能更好地反映三元联系集“供应-库存”的业务语义。

4.3  请将图4-16所示的多值联系集“贷款”分别建模为一个“贷款单”依赖实体集或弱实体集，并画出相应的E-R图。

 

如果将图4-16所示的多值联系集贷款建模为一个弱实体集——贷款单，它的属性有贷款编号、贷款日期和贷款金额等，其中贷款编号为部分码。贷款单弱实体集同时依赖于客户实体集和银行实体集，对应的标识联系集分别是借贷和放贷。图1所示的是建模结果。

图1  将多值联系贷款建模为弱实体集贷款单

也可以考虑将图4-16所示的多值联系集贷款直接建模为一个依赖实体集——贷款单，它的属性有贷款编号、贷款日期和贷款金额等，其中贷款编号为主码，唯一标识一张贷款单。显然，贷款单实体集是同时依赖于客户的“借贷”行为和银行的“放贷”行为而存在的，因此可以引入实体集客户与贷款单之间的“借贷”联系集和实体集银行与贷款单之间的放贷联系集，且贷款单实体集是同时依赖于借贷和放贷联系集。建模结果如图2所示。

图2  将多值联系贷款建模为(强)实体集贷款单

如果允许多个客户联合借一笔贷款，则“借贷”可改为多对多联系集“借贷明细”，且需要联系属性客户借款额。

同理，如果允许多个银行联合发放一笔贷款，则“放贷”可改为多对多联系集“放贷明细”，且需要联系属性银行贷款额。

 

4.4  假定一个销售公司的业务涉及如下基本实体：

(1) 职工：职工号、姓名、性别、电话、住址；

(2) 商品：商品编号、商品名称、型号、计量单位、供货商、进货单价、库存数量、销售单价；    ——库存数量为派生属性

——供货商是独立的实体集，E-R模型中需要通过联系集(可转化为关系表的外码)来反映；

——进货单价、销售单价分别与进货、销售业务有关，应该是联系集的属性。

(3) 供货商：供货商编号、供货商名称、联系电话、通信地址；

(4) 客户：客户编号、客户名称、联系电话、通信地址。

假设每种商品可从多个供货商采购，每个供货商可供应多种商品；每个供货商的每种商品可销售给多个客户，每个客户可购买多个供货商提供的多种商品。请根据你对销售公司业务的理解进行数据库设计，要求：

(1) 定义必要的实体集及其属性。

(2) 设计该销售系统的E-R模型，E-R图重点反映实体集之间的联系和联系属性，需标出联系的映射基数。

(3) 将E-R模型转化为关系数据库模式，并指出每一个关系模式的主码和外码。

1) 实体集及其属性

(1) 商品：商品编号、商品名称、型号、计量单位、库存数量；

(2) 客户：客户编号、客户名称、联系电话、通信地址；

(3) 职工：职工号、姓名、性别、电话、住址；

(4) 供货商：供货商编号、供货商名称、联系电话、通信地址；

(5) 销货单：销货单编号、销售日期、销售总金额，销货单是依赖实体集，它同时依赖于商品、客户、职工实体集；

(6) 进货单：进货单编号、进货日期、进货总金额，进货单是依赖实体集，它同时依赖于商品、供货商、职工实体集。

2) 联系集及E-R图

(1) 销货单与商品实体集之间存在多对多联系集销售，联系属性有：销售数量、销售单价、销售金额；

(2) 销货单与客户实体集之间存在多对一联系集购买，无联系属性；

(3) 销货单与职工(销售员)实体集之间存在多对一联系集出售，无联系属性；

(4) 进货单与商品实体集之间存在多对多联系集进货，联系属性有：进货数量、进货单价、进货金额；

(5) 进货单与供货商实体集之间存在多对一联系集供货，无联系属性；

(6) 进货单与职工(采购员)实体集之间存在多对一联系集采购，无联系属性。

——商品实体集中的库存数量是派生属性，它是根据进货联系集中的进货数量和销售联系集中的销售数量计算而得到，反映每一种商品的当前库存情况。无法反映每个会计核算期的期初库存、本期入库、本期出库、期末库存情况。

4.5  为本章4.7节的大学选课系统安排期末考试考场，供学生和教师查询考试信息。要求如下：

(1) 一门课程的所有开课班应安排在相同时间进行考试，不同课程的开课班可以安排在相同或不同的时间进行考试；

(2) 一个开课班的学生可能安排在多个考场参加考试，一个考场也可以包含同一门课程的多个开课班的学生，但不允许将选修不同课程的学生安排在同一考场考试(该语义也可以进行修改)；

(3) 一个考场根据参加考试的学生人数安排2至4名监考老师，其中指定一名老师为主监考老师；

(4) 一个学生选修的多门课程不能安排在同一时间进行考试；

(5) 一个老师不能安排在同一时间参加多个考场的监考；

(6) 一个教室在同一时间不能安排多场考试；

(7) 安排在同一考场参加考试的学生人数不能超过该教室的考试容量(通常情况下，一个教室的考试容量不会超过其上课容量的一半)。

请你在对教务处进行调研的基础上进行数据库设计，要求：

(1) 定义必要的实体集及其属性。

(2) 设计该考试安排的E-R模型，E-R图反映实体集之间的联系和联系属性，需标出联系的映射基数；并通过数据字典定义E-R图中的每一个实体集的属性。

(3) 将E-R模型转化为关系数据库模式，并指出每一个关系模式的主码和外码。

(1) 学生排考联系集反映的是学生考场安排情况，即一个考场安排了哪些学生参加考试？某课程所有考场的学生排考联系集来自于该课程的所有选课学生(即该课程所有开课班的选课学生)。考试人数为派生属性，可从学生排考联系集中统计得到。

(2) 座位号反映的是一个学生在某个考场的座位序号——该考场内的流水编号。

(3) 监考角色的取值为主监考或辅监考。

(4) 一门课程的所有考场的考试时间要求相同，不同课程可以安排在相同或不同的时间进行考试。要求满足：一个学生选修的多门课程不能安排在同一时间进行考试；一个老师不能安排在同一时间参加多个考场的监考，一个教室在同一时间不能安排多场考试。

可转化为下图（由于考试时间实体集只有主码属性考试时间，没有更多的属性，因此，考试时间没有必要作为实体集建模，而是直接建模为考场实体集的属性）：

试比较上下图差别

弱实体集和排监考联系集产生的关系模式如下：

ExamRoom (courseNo, examRoomNo , examTime, examCount, classroomNo)

Invigilate (courseNo, examRoomNo , teacherNo, invigiRole)

说明：带下划线的属性为主码，斜体属性为外码。

对于上图，学生排考联系集和选课联系集都是多对多的，将分别单独产生如下关系模式：

StudentExam (studentNo, courseNo, examRoomNo , seatNo)

Enroll (studentNo, courseNo, cClassNo , score)   -- 选课系统中的关系表

对于下图，学生排考联系集是多对一的，不单独产生关系模式，学生排考联系集的信息将加入到选课联系集产生的关系模式中：

  Enroll (studentNo, courseNo , cClassNo , score, examRoomNo, seatNo)

 

其他关系模式略去。

 

说明：

对于关系模式ExamRoom(courseNo, examRoomNo, examTime, examCount, classroomNo)，主码是{ courseNo, examRoomNo }，但courseNo→examTime，因此，存在部分函数依赖。为了减少连接运算，允许该部分函数依赖的存在。

另一种建模方案是：将examTime作为与课程实体集Course相关联的属性。由于一门课程每学期(或学年)都要安排考试，如何保留历史排考信息呢？

 

学院是否作为实体集建模？

 

4.6 试根据图4-62的内容，设计交通违章处罚数据库的E-R图并转化为关系模式。注意，一张违章单可能有多种处罚。

E-R图可转化为以下关系模式：

(1) 车主/司机OwnerDriver：(包括有车无照、有照无车、有照有车三种情况)

    OwnerDriver (ownerDriverID, driverName, address, telephone)

(2) 驾照DrivingLicence：

    DrivingLicence (licenceNo, ownerDriverID, licenceTime, punishPointSum)

(3) 机动车Vehicle：

    Vehicle (vehicleNo, ownerDriverID, model, producterName, madeTime, getcardTime)

(4) 警察实体集Police：

    Police (policeNo, policeName, department)

(5) 违章分类Violation：

    Violation (violationNo, violationName, punishPointLower, punishPointUpper, punishMoneyLower, punishMoneyUpper)  -- 罚分、罚款的范围

(6) 处罚单Ticket：-- 还可以进一步反映处罚单的执行情况(如在何时、何银行交纳了罚款)

    Ticket (ticketNo, violationTime, violationSite, description, punishTime, punishPointSum, punishMoneySum, licenceNo, vehicleNo, policeNo)

 (7) 违章处罚明细TicketViolation：

    TicketViolation (ticketNo, violationNo, punishPoint, punishMoney)

说明：——以下反映的是警察在执法时的处罚量罪权力

(1) 违章处罚明细TicketViolation中的punishPoint取值只能在违章分类Violation中的[punishPointLower, punishPointUpper]范围内，punishMoney类似。

(2) 处罚单Ticket中的punishPointSum, punishMoneySum表示数罪并罚的结果，因此，它的语义分别是：可以等于、也可以小于违章处罚明细TicketViolation中的punishPoint, punishMoney的合计。

 

 

4.8  假定一个医院门诊系统的开处方和缴费开发票业务涉及如下基本实体：

(1) 职工：职工号、姓名、性别、电话、住址；

(2) 药品：药品号、药品名、计量单位、进货单价、库存数量、销售单价；

(3) 发票：发票号、开票日期、业务摘要、发票金额、开票职工；

(4) 病人：病人号、姓名、性别、出生日期、联系电话。

业务语义如下：

(1) 假设每个处方可以开具多种药品，每种药品可在多个处方中开具，且每个处方中需要记录处方号、开具日期、处方金额、开具处方的医生、对应的病人以及药品明细(即每种药品的数量、单价、金额、每天的用药次数、每次的用量等)；

(2) 每个处方可分多次缴费，且每次缴费对应一张发票，但一次缴费不允许跨多个处方；每次缴费(即每张发票)需要明确该次缴费(该张发票)所对应的药品明细，且一个处方中开具的某种药品只能一次缴费，即允许将一个处方中开具的所有药品分多次缴费，但不允许将某种药品开具的数量分拆缴费。

请根据上述对开处方和缴费开发票业务的描述进行局部数据库设计，要求：

(1) 定义必要的实体集及其属性。

(2) 设计开处方和缴费开发票业务的局部E-R模型，E-R图重点反映实体集之间的联系和联系属性，需标出联系的映射基数。

(3) 将局部E-R模型转化为关系数据库模式，并指出每一个关系模式的主码和外码。

缴费业务的消费明细，则应该如何建模？

——如果允许将每种药品开具的数量分拆开发票/缴费，则应该如何建模？