SQL-Server数据库设计的案例分析1

数据库设计的案例分析

一、教学管理

1.基本需求

某学校设计学生教学管理系统。学生实体包括学号、姓名、性别、生日、民

族、籍贯、简历、登记照，每名学生选择一个主修专业，专业包括专业编号和名

称，一个专业属于一个学院，i个学院可以有若干个专业。学院信息要存储学院

号、学院名、院长。教学管理还要管理课程表和学生成绩。课程表包括课程号、

课程名、学分，每门课程由一个学院开设。学生选修的每门课程获得一个成绩。

设计该教学管理的ER模型，然后转化为关系模型。

若上面的管理系统还要管理教师教学安排，教师包括编号、姓名、年龄、职

称，一个教师只能属于一个学院，一名教师可以上若干门课程，一门课程可以有

多名老师来上，每个教师所上的每门课都有一个课堂号和课时数。试修改上题的

ER模型，将教师教学信息管理增加进去。

2.参考设计:

._____

一中n，十•｛屐11吉\|

m

,<g

n

------------------------------堂口

增加教师，ER图如下。

图二有教师实体的教学管理ER图

3.物理设计

基于Access的数据库结构设计如下。

指定数据库文件的名称，并为设计好的关系模型设计表结沟。

数据库文件保存在“E:\教学管理\”文件夹中，数据库文件名：教学管理.MDB.

表包括：学院、专业、学生、课程、成绩单。对应表结构如表1・2至表1-6所示。

表1-1学院

字段名类型宽度小数主键/索引参照表约束Null值

学院号文本型2t（主）

学院文本型16

院长文本型8

表1-2专业

字段名类型宽度小数主键/索引参照表约束Null值

专业号文本型4f（主）

专业文本型16

专业类别文本型8t

学院弓文本型2学院

表1-3学生

字段名类型宽度小数主键/索引参照表约束Null值

学号文本型8f（主）

姓名文本型8

性别文本型2男or女

生日日期型

民族文本型6t

籍贯文本型20

专业号文本型6专业

简历备注型

登记照OLE对象

表课程

字段名类型宽度小数主键/索引参照表约束Null值

课程号文本型8t<±>

课程名文本型24

学分字节型

学院号字符型2学院

表1-5成绩单

字段名类型宽度小数主键/索引参照表狗束Null值

学号文本型8t学生

课程号文本型6t课程

成绩单精度41>=0and<=100

思考题：

1.当我们进行物理设计时，如果将全校的学生放置在一个关系（表）中，势必

带来存储空间大、处理效率低的问题。怎么解决？

2.如果管理研究生，带来的设计影响是什么？如何解决？

3.在管理教师信息时，如果将教师分类：教师、研究生导师。研究生导师存储

“研究方向、学生人数”等信息，怎样设计。

附：教学管理数据库参考数据如表1-1〜表1-5所示。

表1-1学生表

学号姓名性别生日民族籍贯专业号简历登记照

06053113唐李生男04/19/1987汉湖北省麻城0501

07042219黄耀男01/02/1989汉黑龙江省牡丹江市0403

06041138华美女11/09/1987汉河北省保定市0103

07015120刘权利男10/20/1989回湖北省武汉市0403

08055117王燕女1)8/02/1990回河南省安阳市0501

08045142郝明星女11/27/1989满辽宁省大连市0403

08053101高猛男02/03/1990汉湖北生黄石市0501

08053124多桑男10/26/1988藏西藏0501

08053160郭政强男1)6/10/1989土家湖南省吉首0501

08053116陆敏女03/18/1990汉广东省东莞0501

08053131林惠萍女12/01/1989壮广西省柳州市0501

07093305郑家谋男03/24/1988汉上海市0904

07093342罗家艳女05/16/1988满北京市0904

07093325史玉磊男09/11/1988汉湖北省孝感市0904

07093317凌晨女06/28/1988汉浙江省温州0901

08041136徐栋梁男12/20/1989回陕西咸阳0103

08041127巴朗男09/25/1989蒙古内蒙古0403

表1-2学院表表1-5成绩单

学院号学院院长学号课程号成绩

01外国语学院叶秋宜060531139105401085

02人文学院容容060531130209101080

03金融学院王汉生060531130906404975

04法学院乔亚060531130502003090

05工商管理学院张绪060531130906105082

06会计学院张一非070422190209101085

09信息学院杨新070422199105401078

U7042219jyOblObU72

08055117D105401092

表1-2专业表

080551170906404985

专业号专业专业类别学院号

080551179906105088

0201新闻学人文02

070933059906404992

0301金融学经济学03

070933059105401086

0302投资学经济学03

070933050502003070

0403国际法法学04

070933050906505090

0501工商管理管理学05

060411380209101074

0503市场营销管理学05

060411SR04010002HR

0602会计学管理学06

080531310105401077

0902信息管理管理学09

080531310906105066

0901计算机科工学09

070933170906404978

学

07093317D105401087

表课程表070933250105401076

课程号课程名学分学院号070933250906505081

82

09064049高等数学6.0090709332509064049

04010002

09065050数据结构4.0090709332575

02091010大学语文3.001080411360105401088

09006050线性代数3.009080411360906105085

01054010大学英语4.001

02000032美术设计2.002

09023040运筹学5.009

09061050数据库及应用3.009

05020030管理学原理3.005

05020051市场营销学3.005

04010002法学概论3.004

04020021合同法实务2.004

二、图书销售

建立某中小型书店图书销售管理信息系统的数据库。

1.基本需求分析

1）组织结构

对组织结构的分析有助于分析业务范围与业务流程。书店的组织结构如图三所

/JKo

图三书店组织结构简图

其中，书库是保存图书的地方；购书/服务部负责采购计划、读者服务、图书预

订等业务；售书部负责图书的销售。财务部负责资金管理；人事部负责员工管理与

业务考核。

2）业务分析

对于信息处理系统来说，划分系统边界很重要，即哪些功能由计算机来完成，

哪些工作在计算机外完成。这些要通过业务分析确定。同时，业务流程中涉及的相

关数据也通过业务分析得到归类和明确。在业务分析的基础上，确定数据流图和数

据字典。

本系统主要包含以下业务内容。

①进书业务。事先采购员根据订书单采购图书。然后将图书入库，同时登记相

应的图书入库数据。

本项业务涉及的数据单据和表格有：进书单（包括进书单编号、日期、金额、

经手人等）和进书单细目（一个进书单可能有若干种图书。进书单的细目数据包括

每种图书的信息、定价、进价或折扣，数量），以及书库账本（图书信息、库存数量、

价格等）。

②售书业务。售书员根据读者所购图书填写售书单（如图四所示）。同时，修

改库存信息。

本项业务涉及和产生的数据表格有：售书单（包括售书单编号、售书日期、金

额、员工）、售书细目（一个售书单可能有若干种图书。售书细目包括该次售书的书

籍编号、售出数量、折扣、售出价格等），以及书库账本。

武汉市新华书店

南湖路门市

征票）1

销售41号1XS00B0806220000U02交易时闾।2008-06-2210:48:10

序号潴码书名我fit价格折扣金霏

19787801601483面中窃理商中力学（下）214.0080%¥22.40

2780160428C002面中化学实抬215.0080%¥24.00

319203004^553英语词汇的典鸵12850100%¥28.50

总品和3总册数码洋¥8650

＜EZ收＞¥7490本收〉*7490〈让利＞¥11.60

收款〉¥10000俄萼）¥25.10

收银员：OOBO24机号：03

联系地址：武江田洪山区南称S110号

哀系电话/p>

如有质量问期，话荏3日内弟小票进行兑换.

图四售书单样式

③图书查询服务业务。根据读者要求，提供本书店特定的图书及库存信息。

本项业务涉及的主要数据是书库账本。

④综合管理业务。包括进书信息、销售信息、库存信息的查询、汇总和报表输

出。

本项业务涉及所有的进书数据、销售数据和库存数据等。

3）处理的数据

上面的分析将本系统的业务归纳为4项。在业务分析的基础匕应该画出系统

的数据流图。整个系统的分层数据流图将揭示一个系统内全部的数据项、数据结构、

数据存储以及对数据的加工处理功能。在此基础上就可以建立系统的数据字典。本

书不讨论数据流图和完整的数据字典规范等内容，仅对豉后建立数据库所需要的数

据进行分析说明。

在上述4项业务中涉及到的业务数据包括：进书数据、库存数据、销售数据。

在这些数据中又涉及到图书数据、员工数据等，而图书数据与出版社有关，员工与

部门有关。

因此，将所有数据进行归类分析，书店销售管理信息系统要处理的数据应该包

括:

企业部门信息（组成：部门编号、部门名、办公电话）；

员工信息（组成：工号、姓名、性别、生日、职务、所属部门、薪金）；

出版社信息（组成：出版社编号、出版社名称、地址、联系电话、联系人）；

基本图书信息（组成：图书编号、ISBN、书名、作者、出版社、版次、出版日

期、定价、图书类别、备注）；

进书单及细目（组成：进书单号、日期、｛进书细目;、金额、业务员）；

售书单及细目（组成：售书单号、日期、｛售书细目;、金额、业务员）；

书库账本（组成：图书编号、库存数量、平均进价折扣、备注）。

这些就是书店销售管理信息系统要处理的各种对象，每一种对象由括号内的属

性组合在一起来描述。这些属性有的是基本数据项，有的是数据项集合（由“｛、｝”

括起来），数据项集合要做进一步的说明。

例如，“｛进书细目｝”由“序号、｛基本图书信息｝、进价或折扣、数量”等属性

组成；“｛售书细目｝”由“序号、图书编号、售价或折扣、数量”等属性组成。

当所有数据对象都归纳完毕，就可以编制数据字典了。在数据字典中，要对所

有这些数据项、数据项集合等的命名、取值方式和范围、作用等进行明确而无异义

说明。

4）处理功能分析

数据字典不仅记载所有数据的详情，也要详细记载所有对数据的处理功能。

①进书业务。当进书业务发生时，将所进图书入书库，然后存储进书单及细目

数据，同时根据进书单登记图书库存数据。

当登记图书库存数据时.，可能有两种情况：新图书或已有图书入库。

对于新图书，本业务要将图书的完整信息记载下来，然后记载图书进价和数量；

已有图书是指同一种书。但同一种书可能有版本方面的区别。为简单起见，规

定：“ISBN号”与“版次”相同的就是同一种书，图书编号相同。对于已有图书，将

本次进书数加到该图书的库存数中即可，但本次的进价折扣与以前库存的该书的折

扣可能存在差异。为了便于计算成本和售书收益，入库已有图书时，这里采用的方

法是：将已有图书占用的资金和本次入库的资金加在一起，然后重新计算一个平均

价格折扣。因此，书库中该图书的价格折扣是当前所有库存图书占用资金除以当前

库存数量后计算的折扣。

②售书业务。根据读者所购图书的售书单存储售书单及细目数据，这是售书的

业务数据。同时，修改图书的库存信息。

③图书查询服务业务。查询服务的输入是读者所提要求，输出是相关图书的库

存信息。为方便读者，可以针对书名、ISBN、作者、版次、出版社提供单个或多条

件组合查询。

④综合管理业务。管理人员需要定期或不定期汇总统计或查询进书信息、销售

信息、库存信息，并按照管理要求制作业务报表。

通过进书单及细目可以对进书业务进行查询、统计汇总和报表输出。

通过售书单及细目可以对售书业务进行咨询、统计汇总和报表输出。

通过库存账本可以对图书库存情况进行查询、统计汇总和报表输出。

2.ER模型分析设计

（1）基本实体和联系

首先确定实体类别以及它们各自的属性构成，指出实体标识符，并尽量规范属

性名，避免同名异义或异名司义。确定实体后，就可以分析实体之间的联系。

可以很容易确定，部门、员工、出版社、图书、书库是不同的实体。

部门的属性：部门号、部门名、办公电话；

员工的属性：工弓、姓名、性别、生口；

部门与员工发生聘用联系。这里规定一个员工只能在一个部门任职，它们是1：

n联系。当联系发生时，产生职务、薪金属性。

出版社属性：出版社编号、名称、地址、联系电话、联系人；

图书属性：图书编号、书名、作者；

出版社与图书发生“出版”联系。一本图书只能在一家出版社出版。这是1：n

联系。当联系发生时，产生ISBN、版次、出版日期、定价、图书类别、备注等属性。

由员工购进图书，所以进书业务是员工与图书发生联系的结果。一名员工可以

进多种图书，一种图书可由多个业务员购进，所以它们是m：n联系。“进书”联系

产生“进书单”属性，进书单本身又由“日期、图书细目、数量、金额”等多个属

性构成，所以是多值的组合属性。

与进书业务类似，售书业务是员工将图书售给读者。本系统不保存读者信息，

所以售书是员工与图书发生我系，“售书单”是“售书”联系的属性。

当图书购进后，图书要入书库保存。书库与图书发生“保存”联系。这里假定

图书是集中式保管，只有唯一一个书库，所以书库不需要标明属性。书库与图书之

间是1：n联系。“保存”联系的属性有数量、存书的价格折扣、存放备注。

(2)需要解决的问题一售书与进书

以售书为例，当员工在书店售书时，员工就与图书发生“售书”联系。由于一

个员工可以售出多种图书，一种图书可以从多名员工那里售出，因此员工与图书的

“售书”联系是m：no在实际售书时，由于一名读者可能购买多种图书，所有这些

图书构成一张完整的售书单.，所以“售书单”是售书联系的属性，ER图如图五所示,

图中略去员工和图书的实体属性。

图五图书销售联系的ER图

仔细分析“售书单”属性，可以发现，售书单不是一个单一的数据，它是由多

项内容构成,，如日期、图书种类和数量、金额等属性。

对于属性来说，无论是实体属性还是联系属性，根据属性结构特点可以分为原

子属性或组合属性。原子属性就是属性是一个不可分割的整体，例如员工的“性别”、

“年龄”等。但有些属性是由几个子属性组合起来的。例如，对于员工“薪金”，如

果要分解为“基本工资”、“岗位工资”、“业绩提成”等，则成为组合属性。因此，

有些属性到底是原子属性还是组合属性，要根据设计的规定。象“姓名”，我国一般

是作为一个整体,但西方则分为"FirstName”和“LastName”。而这里的“售书

单”属性，很明显只能是组合属性。

从属性的取值情况可以分为单值属性或多值属性。且值属性就是属性只有一种

取值，如员工性别、生日等；而多值属性就是该属性可能有多种取值。例如，如果

允许员工兼职，则他的职务可能就不只一个值。另外，若在员工中增加“学位”属

性，有的员工可能就有几个学位。

假设在员工实体中增加一个“社会关系”属性，它日“姓名、年龄、关系、地

址”组成，所以是组合属性，同时，由于一个员工可能有多个社会关系，则对员工

来说，该属性又是多值属性。前述的“售书单”属性，由于一个售书单内部可包含

多种图书，所以它也是多值属性。

当实体或联系存在多值、组合属性时，对ER图的表述带来了一定的困难。因为

ER图将来将转化为关系模型，而关系中属性必须是原子的，因此在ER图中必须有专

门的处理。

对于单值的组合属性，一般将组合属性的子属性分解为独立属性。如“薪金”,

若要了解其构成，就可变成,

而对丁•多值屈性，般会将这个属性变成实体来对待。这样，它与原实体的关

系就变成实体间的联系。例如，将图三中的“售书单当作实体，该实体分别与“员

工，，和“图书”实体发生联系。一名员工可负责多份售书单，而一份售书单只由一

名员工负责，他们之间是1：n联系；一份售书单中可包含多种图书，一种图书可由

不同的售书单售出，他们之间是m：n联系。这样，图五所示的ER图就变成图六的

样子。

图六售书单ER图

在图四中，售书单的“金额”属性是本单中所有图书销售金额的合计，即：

金额=£（数量x定价x折扣）

这样的属性称为“导出”属性，由于可以从其他属性导出，在数据库中一般可

略去。

（3）完整的ER图

将“进书单”提升为实体来看待，这样，“进书”联系就分解为员工与进书单、

图书与进书单两种联系。而其中的“金额”是导出属性，略去。

进书单的属性有：进书单号、日期。

员工与进书单发生“经手”联系。一名员工可经手多张进书单，一张进书单只

由一名员工负责，所以它们是1：n联系。

进书单与图书发生“购进”联系。一张进书单可以包含多种图书，一种图书可

以由不同的进书单购进。进书单与图书是m：n联系。“购进”联系属性：购进的每

种图书数量、进价折扣。

将“售书单，，提升为实体，“售书”联系分解为员工司售书单、图书和售书单两

个联系。略去“金额”属性，

售书单的属性有：售书单号、日期。

员工与售书单发生“负责”联系，一名员工可负责售出多张售书单，一张售书

单只由一名员工负责，所以它们是1：n联系。

图书与售书单发生“售出”联系。-一张售书单可以售出多种图书，一种图书可

以由不同的售书单售出。图书与售书单的联系是m：n联系。“售出”联系的属性有:

售出的每种图书的数量、售价折扣。

这样，根据以上的分析.可以画出图书销售的ER图。为了清晰起见，将实体及

实体及其属性

3.关系模型

首先，将每个实体型转化为一个关系模式，于是分别得到部门、出版社、员工、

图书、进书单、售书单的关系模式，关系的属性就是实体图中的属性。书库不需要

单独列出。

然后，将ER图中的联系转化为关系模式。

ER图中有7个联系，因此，得到7个由联系转化得到的关系模式。它们分别是:

1）聘用（部门号，工号，职务，薪金）

2）出版（出版社编号，图书编号，ISBN,版次，出版日期，定价，图书类别，备注）

3）保存（图书编号，数量，存书折扣，存放备注）

4）经手（工号，进书单号）

5）购进（进书单号，图书编号，数量，进价折扣）

6）负责（工号，售书单号）

7）售出（售书单号，图书编号，数量，售价折扣）

在这些联系中，由1：n联系得到的关系模式可以考虑与n方实体合并，合并时

注意属性的唯一性。这样，聘用与员工合并；出版、保存与图书合并，合并时将出

版的备注和存放备注也合为一个字段：备注。经手与进书单合并，负责与售书单合

并。合并时重名的不同属性要改名。关系模式名和其他属性名也可酌情修改。

保留购进和售出联系的模式，并结合需求分析改名为“进书细目”和“售书细

目”。

这样得到如下一组关系模式，这些就构成了图书销售数据库的关系结构模式。

①部门（部门号，部门名，办公电话）

②员工（工号，姓名，性别，生日，部门号，职务，薪金）

③出版社（出版社编号，出版社名，地址，联系电话，联系人）

④图书（图书编号，ISBN,书名，作者，出版社编号，版次，出版时间，图书类别,

定价，折扣，数量，备注）

⑤进书单（进书单号，进书日期，工号）

⑥进书细目（进书单号，图书编号，数量，进价折扣）

⑦售书单（售书单号，售书日期，工号）

⑧售书细目（售书单号，图书编号，数量，售价折扣）

4.物理设计

基于Access的设计如下。

表2-1部门结构

字段名类型宽度小数位主键/索引参照表约束Null值

部门号文本型2t（主）

部门名文本型20

办公电话文本型18

表2-2员工结构

字段名类型宽度小数位主键/索引参照表约束Null值

工号文本型4t（主）

姓名文本型10

性别文本型2男或女

生日日期/时间里

部门号文木型2t部门J

职务文木型1()

薪金货币型2800

表2-3出版社结构

字段名类型宽度小数位主键/索引参照表约束Null值

出版社编号文本型4t（主）

出版社名称文本型26

地址文本型40

联系电话文本型18

联系人文本型