软考：软件设计师 — 4.数据库设计

四. 数据库设计

数据库设计部分也是数据库系统的案例应用，是下午场考试中的第二个题目，分值 15 分。通常题目会给出需求说明等文字描述，然后要求补充 E-R 图、关系模式，或者根据规范化理论出题。

整个题目都是依据数据库设计的流程，牢记下图：

• E-R 图
• 联系类型判断
• E-R 图转关系模式
• 主键、外键判断
• 规范化理论
• ……

1. 例题 1

说明：

某宾馆为了有效地管理客房资源，满足不用客户需求，拟构建一套宾馆信息管理系统，以方便宾馆管理及客房预定等业务活动。

需求分析结果：

该系统的部分功能及初步需求分析的结果如下：

（1）宾馆有多个部门，部门信息包括部门号、部门名称、电话、经理。每个部门可以有多名员工，每名员工只属于一个部门；每个部门只有一名经理，负责管理本部门。

（2）员工信息包括员工号、姓名、岗位、电话、工资。其中，员工号唯一标识员工关系中的一个元组，岗位有经理、业务员。

（3）客房信息包括客房号（如 1301、1302 等）、客房类型、收费标准、入住状态（已入住 / 未入住），其中客房号唯一标识客房关系中的一个元组，不同客房类型具有不同的收费标准。

（4）客户信息包括客户号、单位名称、联系人、联系电话、联系地址。其中，客户号唯一标识客户关系中的一个元组。

（5）客户预定客房时，需要填写预定申请。预定申请信息包括申请号、客户号、入住时间、入住天数、客房类型、客房数量。其中，一个申请号唯一标识预定申请中的一个元组；一位客户可以有多个预定申请，但一个预定申请对应唯一的一名客户。

（6）当客户入住时，业务员根据客户的预定申请负责安排入住客房事宜。安排信息包括客房号、姓名、性别、身份证号、入住时间、天数、电话。其中，客房号、身份证号、入住时间唯一标识一次安排。一名业务员可以安排多个预定申请，一个预定申请只由一名业务员安排，而且可安排多间同类型的客房。

概念模型设计：

根据需求阶段收集的信息，设计的实体联系图如下图所示。

关系模式设计：

部门（部门号，部门名称，经理，电话）

员工（员工号，(a)，姓名，岗位，电话，工资）

客户（(b)，联系人，联系电话，联系地址）

客房（客房号，客房类型，收费标准，入住状态）

预定申请（(c)，入住时间，天数，客房类型，客房数量）

安排（申请号，客房号，姓名，性别，(d)，天数，电话，业务员）

问题1：

根据问题描述，补充四个联系，完善 E-R 图，联系名可用联系1、联系2、联系3 和联系4 代替，联系的类型为 1:1、1:n 和 m:n（或者用 * 表示多端）。

问题2：

（1）根据题意，将关系模式中的空 (a)~(d) 补充完整。

（2）给出预定申请和安排关系模式中的主键和外键。

问题3：

关系模式设计中的客房关系模式是否存在规范性问题，请用 100 字以内文字解释你的观点（若存在问题，应说明如何修改客房关系模式）。

解析1：

根据（1）（5）（6）的描述，补充完整 E-R 图：

注意，当客户入住时，业务员根据客户的预定申请负责安排入住客房事宜，这句描述设计到三个属性，所以此处存在三元联系。

解析2：

根据（2）可得，员工信息包括员工号、姓名、岗位、电话、工资，题目中关系模式也已经给出了这些属性，所以员工关系中包括归并的属性，由于部门和员工存在一对多的关系，所以存在向多端归并，（a）处填部门关系的主键，部门号。根据（4）可得，客户信息包括客户号、单位名称、联系人、联系电话、联系地址。其中，客户号唯一标识客户关系中的一个元组。所以（b）处填客户号、单位名称。根据（5）可得，预定申请信息包括申请号、客户号、入住时间、入住天数、客房类型、客房数量。所以（c）处填申请号、客户号。注意，客户与预定申请之间存在一对多的联系，所以要考虑归并问题，并且向多端归并，因此预定申请关系中的属性要归并客户号，由于题目中已经给出，所以直接填写即可。根据（6）可得，安排信息包括客房号、姓名、性别、身份证号、入住时间、天数、电话。其中，客房号、身份证号、入住时间唯一标识一次安排。因此（d）处填写身份证号、入住时间。注意，由于此处是三元联系，所以安排这个联系中应当把业务员、客房、预定申请的主键向其并入，因此多了业务员、客房号、申请号。

其中，预定申请关系模式中的主键是申请号，外键是客户号。安排关系模式中的主键是（客房号、身份证号、入住时间），注意用括号包括，代表三个属性组成属性组是主键。外键是三端的主键，客房号、业务员、申请号。

解析3：

客房（客房号，客房类型，收费标准，入住状态），根据（3）可得，主键是客房号，主属性也是客房号，其余都是非主属性，并且存在依赖客房类型 -> 收费标准。因此有客房号 -> 客房类型 -> 收费标准的传递依赖关系，满足 2NF，但不满足 3NF，因此存在规范性问题。

修改：客房1（客房类型、收费标准）；客房2（客房号，客房类型，入住状态）

总结：

• 补充 E-R 图时注意一对多联系的方向性，以及三元联系。
• 补充关系模式时注意一对多联系中存在向多端归并的问题。
• 三元联系中三端的主键要向联系关系中归并。
• 规范性问题中主要注意第二三范式是否满足，并且在拆分时记得要满足可以还原成之前的依赖关系。

2. 例题 2

说明：

某海外代购公司为扩展公司业务，需要开发一个信息化管理系统。请根据公司现有业务及需求完成该系统的数据库设计。

需求描述：

（1）记录公司员工信息。员工信息包括工号、身份证号、姓名、性别和一个手机号，工号唯一标识每位员工，员工分为代购员和配送员。

（2）记录采购的商品信息。商品信息包括商品名称、所在超市名称、采购价格、销售价格和商品介绍，系统内部用商品条码唯一标识每种商品。一种商品只在一家超市代购。

（3）记录顾客信息。顾客信息包括顾客真实姓名、身份证号、一个手机号和一个收货地址，系统自动生成唯一的顾客编号。

（4）记录托运公司信息。托运公司信息包括托运公司名称、电话和地址，系统自动生成唯一的托运公司编号。

（5）顾客登录系统后，可以下订单购买商品。订单支付成功后，系统记录唯一的支付凭证编号，顾客需要在订单里指定运送方式：空运或海运。

（6）代购员根据顾客的订单在超市采购对应商品，一份订单所含的多个商品可能由多名代购员从不同超市采购。

（7）采购完的商品交由配送员根据顾客订单组合装箱，然后交给托运公司运送。托运公司按顾客订单核对商品名称和数量，然后按顾客的地址进行运送。

概念模型设计：

逻辑结构设计：

员工（工号，身份证号，姓名，性别，手机号）

商品（条码，商品名称，所在超市名称，采购价格，销售价格，商品介绍）

顾客（编号，姓名，身份证号，手机号，收货地址）

托运公司（托运公司编号，托运公司名称，电话，地址）

订单（订单ID，(a)，商品数量，运送方式，支付凭证编号）

代购（代购ID，代购员工号，(b)）

运送（运送ID，配送员工号，托运公司编号，订单ID，发运时间）

问题1：

根据问题描述，补充完整 E-R 图。

问题2：

补充逻辑结构设计结果中(a)、(b)两空。

问题3：

为方便顾客，允许顾客在系统中保存多组收获地址。请根据此需求，增加顾客地址弱实体，对 E-R 图进行补充，并修改运送关系模式。

解析1：

根据（5）（6）（7）的描述，补充完整 E-R图：

注意，虽然在（6）中提到了在超市采购商品，但题目中没有给出任何关于超市的属性，所以不需要增加超市实体。 

解析2：

商品与顾客存在多对多的联系，因此在订单关系中需要并入两端的主键，所以 (a) 处填商品条码、顾客编号。在代购关系中，代购员与订单存在多对多的联系，并且其中一端是一个联系，因此是聚集模型，此外 (b) 处不应该只填支付凭证编号，也就是订单关系的主键，因为代购是由代购员根据顾客的订单在超市采购对应商品，还需要商品的信息，因此还有商品条码。所以 (b) 处填支付凭证编号和商品条码。

解析3：

新增一个顾客的弱实体顾客地址，弱实体采用嵌套的双菱形表示联系，嵌套的双矩形表示实体。并且题目中已给出一个顾客可以有多个收货地址，所以顾客与顾客地址之间是一对多的联系。此外，运送公司按顾客地址进行运送，也应是多对多的联系。补充的 E-R 图为：

修改后的运送关系模式为：运送（运送ID，配送员工号，托运公司编号，订单ID，发运时间，顾客地址），增加了一个顾客地址的属性。

总结：

• 题目中没有特别标注属性的实体可以不用画在 E-R 图中。
• 注意聚集模型中，作为联系整体的那一端的主键不一定只有联系的那一个，还要考虑是否有其它实体的主键。比如题目中的订单和商品，代购员根据顾客的订单在超市采购对应商品，不仅需要支付凭证编号，还需要商品条码。
• 注意弱实体的表达方式与联系类型。

3. 例题 3

说明：

某省针对每年举行的足球联赛，拟开发一套信息管理系统，以方便管理球队、球员、主教练、主裁判、比赛等信息。

需求分析：

（1）系统需要维护球队、球员、主教练、主裁判、比赛等信息。

球队信息主要包括：球队编号、名称、成立时间、人数、主场地址、球队主教练。

球员信息主要包括：姓名、身份证号、出生日期、身高、家庭住址。

主教练信息主要包括：姓名、身份证号、出生日期、资格证书号、级别。

主裁判信息主要包括：姓名、身份证号、出生日期、资格证书号、获取证书时间、级别。

（2）每支球队有一名主教练和若干名球员。一名主教练只能受聘于一支球队，一名球员只能效力于一支球队。每支球队都有自己的唯一主场场地，且场地不能共用。

（3）足球联赛采用主客场循环制，一周进行一轮比赛，一轮的所有比赛同时进行。

（4）一场比赛有两支球队参加，一支球队作为主队身份，另一支作为客队身份。一场比赛只能有一名主裁判，每场比赛有唯一的比赛编码，每场比赛都记录比分和日期。

概念结构设计：

逻辑结构设计：

球队（球队编号，名称，成立时间，人数，主场地址）

球员（姓名，身份证号，出生日期，身高，家庭住址，(1)）

主教练（姓名，身份证号，出生日期，资格证书号，级别，(2)）

主裁判（姓名，身份证号，出生日期，资格证书号，获取证书时间，级别） 

比赛（比赛编码，主队编号，客队编号，主裁判身份证号，比分，日期）

问题1：

补充完整 E-R 图。联系比赛应具有哪些属性？

问题2：

填写逻辑结构设计阶段生成的关系模式中 (1)、(2) 两空。

问题3：

现在系统要增加赞助商信息，赞助商信息主要包括赞助商名称和赞助商编号。赞助商可以赞助某支球队，一支球队只能有一个赞助商，但赞助商可以赞助多支球队。赞助商也可以单独赞助某些球员，一名球员可以为多个赞助商代言。根据新的需求，修改 E-R 图。

解析1：

根据（2）（3）（4）可得，补充完整的 E-R 图如下：

注意，一场比赛有两支球队参加，一支主队一支客队，并且一周内的比赛是同时进行的，所以会有多支主队和多支客队，也有多个主裁判，所以球队、比赛与主裁判之间是多对多对多的三元关系，由于主队和客队都是球队，所以主队与客队又是同一实体内的二元联系。

联系比赛的属性，是指比赛这个联系自身所具有的属性，即比赛（比赛编码，比分，日期）。而在逻辑结构设计中给出的比赛（比赛编码，主队编号，客队编号，主裁判身份证号，比分，日期）关系，是归并后的比赛关系所具有的属性。

解析2：

球队与球员之间存在一对多的联系，所以需要向多端归并，因此 (1) 处填写球队编号；球队与主教练之间存在一对一的联系，理论上可以将任一端主键进行归并，此处问主教练关系内的 (2) 处应填写什么，所以将球队关系的主键球队编号向主教练关系进行归并，因此 (2) 处也填写球队编号。

解析3：

增加了一个新的实体赞助商，并且与球队之间存在一对多的联系，与球员之间存在多对多的联系，因此补充 E-R 图如下：

总结：

• 注意同一实体间二元联系的表示，如主队客队。
• 注意区分是联系自身所具有的属性，还行联系作为整个关系归并后的所具有的属性。
• 题目怎么描述的，就怎么画 E-R 图，如赞助商可以赞助某支球队，一支球队只能有一个赞助商，但赞助商可以赞助多支球队，赞助商也可以单独赞助某些球员，一名球员可以为多个赞助商代言。赞助商与球队和球员之间是两个赞助关系，分别是一对多和多对多。

数据库设计部分的内容至此结束，后续如果有补充或修改会直接添加。