SQLServer数据库设计的案例分析.docx
《SQLServer数据库设计的案例分析.docx》由会员分享，可在线阅读，更多相关《SQLServer数据库设计的案例分析.docx（19页珍藏版）》请在冰豆网上搜索。
SQLServer数据库设计的案例分析
数据库设计的案例分析
一、教学管理
1.基本需求
某学校设计学生教学管理系统。
学生实体包括学号、姓名、性别、生日、民族、籍贯、简历、登记照，每名学生选择一个主修专业，专业包括专业编号和名称，一个专业属于一个学院，一个学院可以有若干个专业。
学院信息要存储学院号、学院名、院长。
教学管理还要管理课程表和学生成绩。
课程表包括课程号、课程名、学分，每门课程由一个学院开设。
学生选修的每门课程获得一个成绩。
设计该教学管理的ER模型，然后转化为关系模型。
若上面的管理系统还要管理教师教学安排，教师包括编号、姓名、年龄、职称，一个教师只能属于一个学院，一名教师可以上若干门课程，一门课程可以有多名老师来上，每个教师所上的每门课都有一个课堂号和课时数。
试修改上题的ER模型，将教师教学信息管理增加进去。
2.参考设计：
图一教学管理ER图
由ER模型转换的关系模型是：
学生（学号，姓名，性别，生日，民族，籍贯，专业号，简历，登记照）
专业（专业号，专业，专业类别，学院号）
学院（学院号，学院，院长）
课程（课程号，课程名，学分，学院号）
成绩（学号，课程号，成绩）
（题目分析：
本题中有学生、专业、学院、课程四个实体。
一个学生只有一个主修专业，学生与专业有多对一的联系；一个专业只由一个学院开设，一门课程只由一个学院开设，学院与专业、学院与课程都是一对多的联系；学生与课程有多对多的联系。
在转换为关系模型时，一对多的联系都在相应的多方实体的关系中增加一个外键。
）
增加教师，ER图如下。
图二有教师实体的教学管理ER图
3.物理设计
基于Access的数据库结构设计如下。
指定数据库文件的名称，并为设计好的关系模型设计表结构。
数据库文件保存在“E:
\教学管理\”文件夹中，数据库文件名：
教学管理.MDB。
表包括：
学院、专业、学生、课程、成绩单。
对应表结构如表1-2至表1-6所示。
表1-1学院
字段名
类型
宽度
小数
主键/索引
参照表
约束
Null值
学院号
文本型
2
↑（主）
学院
文本型
16
院长
文本型
8
√
表1-2专业
字段名
类型
宽度
小数
主键/索引
参照表
约束
Null值
专业号
文本型
4
↑（主）
专业
文本型
16
专业类别
文本型
8
↑
学院号
文本型
2
学院
表1-3学生
字段名
类型
宽度
小数
主键/索引
参照表
约束
Null值
学号
文本型
8
↑（主）
姓名
文本型
8
性别
文本型
2
男or女
生日
日期型
民族
文本型
6
↑
籍贯
文本型
20
专业号
文本型
6
专业
√
简历
备注型
√
登记照
OLE对象
√
表1-4课程
字段名
类型
宽度
小数
主键/索引
参照表
约束
Null值
课程号
文本型
8
↑（主）
课程名
文本型
24
学分
字节型
学院号
字符型
2
学院
表1-5成绩单
字段名
类型
宽度
小数
主键/索引
参照表
约束
Null值
学号
文本型
8
↑
学生
课程号
文本型
6
↑
课程
成绩
单精度
4
1
>=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
汉
河北省保定市
0403
07045120
刘权利
男
10/20/1989
回
湖北省武汉市
0403
08055117
王燕
女
08/02/1990
回
河南省安阳市
0501
08045142
郝明星
女
11/27/1989
满
辽宁省大连市
0403
08053101
高猛
男
02/03/1990
汉
湖北生黄石市
0501
08053124
多桑
男
10/26/1988
藏
西藏
0501
08053160
郭政强
男
06/10/1989
土家
湖南省吉首
0501
08053116
陆敏
女
03/18/1990
汉
广东省东莞
0501
08053131
林惠萍
女
12/04/1989
壮
广西省柳州市
0501
07093305
郑家谋
男
03/24/1988
汉
上海市
0904
07093342
罗家艳
女
05/16/1988
满
北京市
0904
07093325
史玉磊
男
09/11/1988
汉
湖北省孝感市
0904
07093317
凌晨
女
06/28/1988
汉
浙江省温州
0904
08041136
徐栋梁
男
12/20/1989
回
陕西咸阳
0403
08041127
巴朗
男
09/25/1989
蒙古
内蒙古
0403
表1-2学院表
学院号
学院
院长
01
外国语学院
叶秋宜
02
人文学院
李容
03
金融学院
王汉生
04
法学院
乔亚
05
工商管理学院
张绪
06
会计学院
张一非
09
信息学院
杨新
07093317
09064049
78
07093317
01054010
87
07093325
01054010
76
07093325
09065050
81
07093325
09064049
82
07093325
04010002
75
08041136
01054010
88
08041136
09061050
85
二、图书销售
建立某中小型书店图书销售管理信息系统的数据库。
1.基本需求分析
1）组织结构
对组织结构的分析有助于分析业务范围与业务流程。
书店的组织结构如图三所示。
图三书店组织结构简图
其中，书库是保存图书的地方；购书/服务部负责采购计划、读者服务、图书预订等业务；售书部负责图书的销售。
财务部负责资金管理；人事部负责员工管理与业务考核。
2）业务分析
对于信息处理系统来说，划分系统边界很重要，即哪些功能由计算机来完成，哪些工作在计算机外完成。
这些要通过业务分析确定。
同时，业务流程中涉及的相关数据也通过业务分析得到归类和明确。
在业务分析的基础上，确定数据流图和数据字典。
本系统主要包含以下业务内容。
①进书业务。
事先采购员根据订书单采购图书。
然后将图书入库，同时登记相应的图书入库数据。
本项业务涉及的数据单据和表格有：
进书单（包括进书单编号、日期、金额、经手人等）和进书单细目（一个进书单可能有若干种图书。
进书单的细目数据包括每种图书的信息、定价、进价或折扣，数量），以及书库账本（图书信息、库存数量、价格等）。
②售书业务。
售书员根据读者所购图书填写售书单（如图四所示）。
同时，修改库存信息。
本项业务涉及和产生的数据表格有：
售书单（包括售书单编号、售书日期、金额、员工）、售书细目（一个售书单可能有若干种图书。
售书细目包括该次售书的书籍编号、售出数量、折扣、售出价格等），以及书库账本。
图四售书单样式
③图书查询服务业务。
根据读者要求，提供本书店特定的图书及库存信息。
本项业务涉及的主要数据是书库账本。
④综合管理业务。
包括进书信息、销售信息、库存信息的查询、汇总和报表输出。
本项业务涉及所有的进书数据、销售数据和库存数据等。
3）处理的数据
上面的分析将本系统的业务归纳为4项。
在业务分析的基础上，应该画出系统的数据流图。
整个系统的分层数据流图将揭示一个系统内全部的数据项、数据结构、数据存储以及对数据的加工处理功能。
在此基础上就可以建立系统的数据字典。
本书不讨论数据流图和完整的数据字典规范等内容，仅对最后建立数据库所需要的数据进行分析说明。
在上述4项业务中涉及到的业务数据包括：
进书数据、库存数据、销售数据。
在这些数据中又涉及到图书数据、员工数据等，而图书数据与出版社有关，员工与部门有关。
因此，将所有数据进行归类分析，书店销售管理信息系统要处理的数据应该包括：
企业部门信息（组成：
部门编号、部门名、办公电话）；
员工信息（组成：
工号、姓名、性别、生日、职务、所属部门、薪金）；
出版社信息（组成：
出版社编号、出版社名称、地址、联系电话、联系人）；
基本图书信息（组成：
图书编号、ISBN、书名、作者、出版社、版次、出版日期、定价、图书类别、备注）；
进书单及细目（组成：
进书单号、日期、{进书细目}、金额、业务员）；
售书单及细目（组成：
售书单号、日期、{售书细目}、金额、业务员）；
书库账本（组成：
图书编号、库存数量、平均进价折扣、备注）。
这些就是书店销售管理信息系统要处理的各种对象，每一种对象由括号内的属性组合在一起来描述。
这些属性有的是基本数据项，有的是数据项集合（由“{、}”括起来），数据项集合要做进一步的说明。
例如，“{进书细目}”由“序号、{基本图书信息}、进价或折扣、数量”等属性组成；“{售书细目}”由“序号、图书编号、售价或折扣、数量”等属性组成。
当所有数据对象都归纳完毕，就可以编制数据字典了。
在数据字典中，要对所有这些数据项、数据项集合等的命名、取值方式和范围、作用等进行明确而无异义说明。
4）处理功能分析
数据字典不仅记载所有数据的详情，也要详细记载所有对数据的处理功能。
①进书业务。
当进书业务发生时，将所进图书入书库，然后存储进书单及细目数据，同时根据进书单登记图书库存数据。
当登记图书库存数据时，可能有两种情况：
新图书或已有图书入库。
对于新图书，本业务要将图书的完整信息记载下来，然后记载图书进价和数量；
已有图书是指同一种书。
但同一种书可能有版本方面的区别。
为简单起见，规定：
“ISBN号”与“版次”相同的就是同一种书，图书编号相同。
对于已有图书，将本次进书数加到该图书的库存数中即可，但本次的进价折扣与以前库存的该书的折扣可能存在差异。
为了便于计算成本和售书收益，入库已有图书时，这里采用的方法是：
将已有图书占用的资金和本次入库的资金加在一起，然后重新计算一个平均价格折扣。
因此，书库中该图书的价格折扣是当前所有库存图书占用资金除以当前库存数量后计算的折扣。
②售书业务。
根据读者所购图书的售书单存储售书单及细目数据，这是售书的业务数据。
同时，修改图书的库存信息。
③图书查询服务业务。
查询服务的输入是读者所提要求，输出是相关图书的库存信息。
为方便读者，可以针对书名、ISBN、作者、版次、出版社提供单个或多条件组合查询。
④综合管理业务。
管理人员需要定期或不定期汇总统计或查询进书信息、销售信息、库存信息，并按照管理要求制作业务报表。
通过进书单及细目可以对进书业务进行查询、统计汇总和报表输出。
通过售书单及细目可以对售书业务进行查询、统计汇总和报表输出。
通过库存账本可以对图书库存情况进行查询、统计汇总和报表输出。
2.ER模型分析设计
（1）基本实体和联系
首先确定实体类别以及它们各自的属性构成，指出实体标识符，并尽量规范属性名，避免同名异义或异名同义。
确定实体后，就可以分析实体之间的联系。
可以很容易确定，部门、员工、出版社、图书、书库是不同的实体。
部门的属性：
部门号、部门名、办公电话；
员工的属性：
工号、姓名、性别、生日；
部门与员工发生聘用联系。
这里规定一个员工只能在一个部门任职，它们是1：
n联系。
当联系发生时，产生职务、薪金属性。
出版社属性：
出版社编号、名称、地址、联系电话、联系人；
图书属性：
图书编号、书名、作者；
出版社与图书发生“出版”联系。
一本图书只能在一家出版社出版。
这是1：
n联系。
当联系发生时，产生ISBN、版次、出版日期、定价、图书类别、备注等属性。
由员工购进图书，所以进书业务是员工与图书发生联系的结果。
一名员工可以进多种图书，一种图书可由多个业务员购进，所以它们是m：
n联系。
“进书”联系产生“进书单”属性，进书单本身又由“日期、图书细目、数量、金额”等多个属性构成，所以是多值的组合属性。
与进书业务类似，售书业务是员工将图书售给读者。
本系统不保存读者信息，所以售书是员工与图书发生联系，“售书单”是“售书”联系的属性。
当图书购进后，图书要入书库保存。
书库与图书发生“保存”联系。
这里假定图书是集中式保管，只有唯一一个书库，所以书库不需要标明属性。
书库与图书之间是1：
n联系。
“保存”联系的属性有数量、存书的价格折扣、存放备注。
（2）需要解决的问题—售书与进书
以售书为例，当员工在书店售书时，员工就与图书发生“售书”联系。
由于一个员工可以售出多种图书，一种图书可以从多名员工那里售出，因此员工与图书的“售书”联系是m：
n。
在实际售书时，由于一名读者可能购买多种图书，所有这些图书构成一张完整的售书单，所以“售书单”是售书联系的属性，ER图如图五所示，图中略去员工和图书的实体属性。
图五图书销售联系的ER图
仔细分析“售书单”属性，可以发现，售书单不是一个单一的数据，它是由多项内容构成，如日期、图书种类和数量、金额等属性。
对于属性来说，无论是实体属性还是联系属性，根据属性结构特点可以分为原子属性或组合属性。
原子属性就是属性是一个不可分割的整体，例如员工的“性别”、“年龄”等。
但有些属性是由几个子属性组合起来的。
例如，对于员工“薪金”，如果要分解为“基本工资”、“岗位工资”、“业绩提成”等，则成为组合属性。
因此，有些属性到底是原子属性还是组合属性，要根据设计的规定。
象“姓名”，我国一般是作为一个整体，但西方则分为“FirstName”和“LastName”。
而这里的“售书单”属性，很明显只能是组合属性。
从属性的取值情况可以分为单值属性或多值属性。
单值属性就是属性只有一种取值，如员工性别、生日等；而多值属性就是该属性可能有多种取值。
例如，如果允许员工兼职，则他的职务可能就不只一个值。
另外，若在员工中增加“学位”属性，有的员工可能就有几个学位。
假设在员工实体中增加一个“社会关系”属性，它由“姓名、年龄、关系、地址”组成，所以是组合属性，同时，由于一个员工可能有多个社会关系，则对员工来说，该属性又是多值属性。
前述的“售书单”属性，由于一个售书单内部可包含多种图书，所以它也是多值属性。
当实体或联系存在多值、组合属性时，对ER图的表述带来了一定的困难。
因为ER图将来将转化为关系模型，而关系中属性必须是原子的，因此在ER图中必须有专门的处理。
对于单值的组合属性，一般将组合属性的子属性分解为独立属性。
如“薪金”，若要了解其构成，就可变成。
而对于多值属性，一般会将这个属性变成实体来对待。
这样，它与原实体的关系就变成实体间的联系。
例如，将图三中的“售书单”当作实体，该实体分别与“员工”和“图书”实体发生联系。
一名员工可负责多份售书单，而一份售书单只由一名员工负责，他们之间是1：
n联系；一份售书单中可包含多种图书，一种图书可由不同的售书单售出，他们之间是m：
n联系。
这样，图五所示的ER图就变成图六的样子。
图六售书单ER图
在图四中，售书单的“金额”属性是本单中所有图书销售金额的合计，即：
金额=∑（数量×定价×折扣）
这样的属性称为“导出”属性，由于可以从其他属性导出，在数据库中一般可略去。
（3）完整的ER图
将“进书单”提升为实体来看待，这样，“进书”联系就分解为员工与进书单、图书与进书单两种联系。
而其中的“金额”是导出属性，略去。
进书单的属性有：
进书单号、日期。
员工与进书单发生“经手”联系。
一名员工可经手多张进书单，一张进书单只由一名员工负责，所以它们是1：
n联系。
进书单与图书发生“购进”联系。
一张进书单可以包含多种图书，一种图书可以由不同的进书单购进。
进书单与图书是m：
n联系。
“购进”联系属性：
购进的每种图书数量、进价折扣。
将“售书单”提升为实体，“售书”联系分解为员工和售书单、图书和售书单两个联系。
略去“金额”属性。
售书单的属性有：
售书单号、日期。
员工与售书单发生“负责”联系，一名员工可负责售出多张售书单，一张售书单只由一名员工负责，所以它们是1：
n联系。
图书与售书单发生“售出”联系。
一张售书单可以售出多种图书，一种图书可以由不同的售书单售出。
图书与售书单的联系是m：
n联系。
“售出”联系的属性有：
售出的每种图书的数量、售价折扣。
这样，根据以上的分析，可以画出图书销售的ER图。
为了清晰起见，将实体及属性、实体联系分别画出。
如图七所示。
实体及其属性
图七图书销售ER模型联系图
3.关系模型
首先，将每个实体型转化为一个关系模式，于是分别得到部门、出版社、员工、图书、进书单、售书单的关系模式，关系的属性就是实体图中的属性。
书库不需要单独列出。
然后，将ER图中的联系转化为关系模式。
ER图中有7个联系，因此，得到7个由联系转化得到的关系模式。
它们分别是：
1）聘用（部门号，工号，职务，薪金）
2）出版（出版社编号，图书编号，ISBN，版次，出版日期，定价，图书类别，备注）
3）保存（图书编号，数量，存书折扣，存放备注）
4）经手（工号，进书单号）
5）购进（进书单号，图书编号，数量，进价折扣）
6）负责（工号，售书单号）
7）售出（售书单号，图书编号，数量，售价折扣）
在这些联系中，由1：
n联系得到的关系模式可以考虑与n方实体合并，合并时注意属性的唯一性。
这样，聘用与员工合并；出版、保存与图书合并，合并时将出版的备注和存放备注也合为一个字段：
备注。
经手与进书单合并，负责与售书单合并。
合并时重名的不同属性要改名。
关系模式名和其他属性名也可酌情修改。
保留购进和售出联系的模式，并结合需求分析改名为“进书细目”和“售书细目”。
这样得到如下一组关系模式，这些就构成了图书销售数据库的关系结构模式。
①部门（部门号，部门名，办公电话）
②员工（工号，姓名，性别，生日，部门号，职务，薪金）
③出版社（出版社编号，出版社名，地址，联系电话，联系人）
④图书（图书编号，ISBN，书名，作者，出版社编号，版次，出版时间，图书类别，定价，折扣，数量，备注）
⑤进书单（进书单号，进书日期，工号）
⑥进书细目（进书单号，图书编号，数量，进价折扣）
⑦售书单（售书单号，售书日期，工号）
⑧售书细目（售书单号，图书编号，数量，售价折扣）
4.物理设计
基于Access的设计如下。
表2-1部门结构
字段名
类型
宽度
小数位
主键/索引
参照表
约束
Null值
部门号
文本型
2
↑（主）
部门名
文本型
20
办公电话
文本型
18
√
表2-2员工结构
字段名
类型
宽度
小数位
主键/索引
参照表
约束
Null值
工号
文本型
4
↑（主）
姓名
文本型
10
性别
文本型
2
男或女
生日
日期/时间型
部门号
文本型
2
↑
部门
√
职务
文本型
10
√
薪金
货币型
≥800
表2-3出版社结构
字段名
类型
宽度
小数位
主键/索引
参照表
约束
Null值
出版社编号
文本型
4
↑（主）
出版社名称
文本型
26
地址
文本型
40
联系电话
文本型
18
√
联系人
文本型
10
√
表2-4图书结构
字段名
类型
宽度
小数位
主键/索引
参照表
约束
Null值
图书编号
文本型
13
↑（主）
ISBN
文本型
22
图书名
文本型
60
作者
文本型
30
出版社编号
文本型
4
出版社
版次
字节型
≥1
出版时间
文本型
7
图书类别
文本型
12
定价
货币型
2
>0
折扣
单精度型
3
√
数量
整型
≥0
备注
备注型
√
表2-5进书单结构
字段名
类型
宽度
小数位
主键/索引
参照表
约束
Null值
进书单号
自动编号型
10
↑（主）
进书日期
日期/时间型
工号
文本型
4
员工
表2-6进书细目结构
字段名
类型
宽度
小数位
主键/索引
参照表
约束
Null值
进书单号
长整型
↑
进书单
图书编号
文本型
13
图书
数量
整型
进价折扣
单精度型
3
0.0～1
√
表2-7售书单结构
字段名
类型
宽度
小数位
主键/索引
参照表
约束
Null值
售书单号
自动编号型
10
↑（主）
售书日期
日期/时间型
工号
文本型
4
员工
表2-8售书细目结构
字段名
类型
宽度
小
升级会员 