工厂数据库管理系统.doc

工厂数据库管理系统

课程设计说明书

题目 工厂数据库管理系统
系(部) 计算机科学与技术系
专业(班级)
姓名
学号
指导教师
起止日期

课程设计任务书

课程名称：数据库系统原理

设计题目：数据库系统课程设计
工厂数据库管理系统
1、某工厂需建立一个管理数据库存储以下信息：
工厂包括厂名和厂长名。
一个厂内有多个车间，每个车间有车间号、车间主任姓名、地址和电话。
一个车间有多个工人，每个工人有职工号、姓名、年龄、性别和工种。
一个车间生产多种产品，产品有产品号、产品名称和价格。
一个车间生产多种零件，一个零件也可能为多个车间制造。零件有零件号、重量和价格。
一个产品由多种零件组成，一种零件也可装配出多种产品。
产品与零件均存入仓库中。
厂内有多个仓库，仓库有仓库号、仓库主任姓名和电话。
2、系统功能的基本要求：
按照一定条件查询、统计工人和产品等基本信息，能模拟工厂生产过程中对原材料（零件）、产品检测、库存环节的管理。
各阶段具体要求：
1、需求分析阶段
l 定义数据项的含义和取值
l 定义目标系统的数据流
2、概念结构设计阶段
l 画出实体模型E-R图
3、逻辑结构设计阶段
l 将实体模型转化为关系模型
l 给出每个关系的主关键字和函数依赖集
l 分析你所设计的关系数据库模式是否属于3NF
4、物理设计阶段
l 确定所有字段的名称、类型、宽度、小数位数及完整性约束
l 确定数据库及表的名称及其组成
l 确定索引文件和索引关键字
5、数据库安全及维护设计阶段
l 设计一个适合的数据库安全策略（用户身份认证、访问权限、视图）
l 为了实现复杂的数据完整性约束，设计适当的触发器
l 设计一个适合的数据库备份策略
6、实施阶段
l 要求所有操作必须在查询分析器中用SQL语句或系统存储过程完成。

设计工作量：
（1）软件设计：完成问题陈述中所提到的所有需求功能。
（2）论文：要求撰写不少于3000个文字的文档，详细说明各阶段具体要求。

工作计划：
安排两周时间进行课程设计，软件开发步骤如下，第一周完成1～4，第二周完成5～8，论文同步进行；
1) 选定题目
2) 需求分析
3) 概念结构设计
4) 逻辑结构设计
5) 物理设计
6) 数据库安全及维护设计
7) 数据库上机实现
8) 答辩

设计工作量：
40课时

工作计划：
见课表

指导教师签名：　　　　　　　　　日期：　　　　　　　　

教研室主任签名：　　　　　　　 日期：　　　　　　　　

系主任签名：　　　　　　　　　 日期：　　　 　　　
长沙学院课程设计鉴定表
姓名

学号

专业

班级

设计题目
工厂数据库管理系统
指导教师

指导教师意见：

评定等级： 教师签名： 日期：
答辩小组意见：

评定等级：　　　　　答辩小组长签名：　　　　　日期：　　　　
教研室意见：

教研室主任签名： 日期： 　
系（部）意见：

系主任签名：　　　　　　　　日期：　　　　　
说明
课程设计成绩分“优秀”、“良好”、“及格”、“不及格”四类；

摘 要
根据而今信息时代，信息越来越多，越复杂，这时就需要一种管理数据的软件，本次课程设计运用SQL server 来实现数据的管理，本次试验经过SQL语句来实现工厂的流程，使工厂运行效率达到最大值，需要对其进行分析需求，逻辑设计，物理设计，安全性分析，权限设置等操作。

关键词：SQL 管理数据 工厂

目 录

一、引言 7
1.1 编写目的 7
1.2 参考资料 7
二、 需求规约 8
2.1 业务描述 8
2.2 需求分析 8
三、 数据库环境说明 9
四、 数据库的命名规则 9
4.1 数据库对象命名规则 9
4.2 数据项编码规则 9
五、 逻辑设计 10
5.1创立数据库系统的关系模型……………………………………………………………………………11
六、 物理设计 12
6.1 表汇总 12
6.2 表总汇 13
6.3 视图的设计 19
6.4 存储过程、函数及触发器的设计 21
七、 安全性设计 23
7.1 防止用户直接操作数据库的方法 24
7.2 用户帐号密码的加密方法 24
7.3 角色与权限 25
八、 数据库管理与维护说明 25
九、总结…………………………………………………………………………………………………………25
十．附录………………………………………………………………………………………………………..26

一、引言
1.1 编写目的
本文档是数据库系统设计文档的组成部分，编写此数据库设计文档的目的是：根据工人数据库管理系统建立一个数据库存储信息，数据库表名包含工厂，车间，工人，产品，零件，仓库。其中工厂包括多个车间和多个仓库，车间有多个工人，一个车间生产多种产品和多种零件，一个零件也可能为多个车间制造，一个产品由多种零件组成，一种零件也可装配出多种产品，产品和零件均存入仓库中等信息,结合此信息经过sql语言来实现此管理系统的运用，模拟工厂生产过程中队原材料（零件）、产品检测、库存环节的管理。本文档遵循《《工人数据库管理系统和开发规范》》。本文档的读者对象是需求人员，系统设计人员，开发人员，测试人员。

1.2 参考资料
列出有关资料的名称、作者、文件编号或版本等。参考资料包括：
a．需求说明书、架构设计说明书等；
b．本项目的其它已发表的文件；
c．引用文件、资料、软件开发标准等。

资料名称
作者
文件编号、版本
《数据库系统概论》
王珊、萨师煊
5月第4版
《《数据库系统原理》实验指导书
徐长梅、汪祥
9月第一版

二、 需求规约
2.1 业务描述
（1）数据库系统创立的背景
运用数据库相关信息来实现工人管理系统的生产和运作。
（2）数据库系统要完成的业务流程及工作内容
根据工人管理系统模拟生产过程，实现对原材料、产品检测、库存的管理
（4）揭示该数据库的资源需求和设计约束
根据工人的各个生产所需的阶段，来建立不同的表，并将这些表连接起来，画出实体模型E-R图，确定所有字段的名称、类型、宽度、小数位数及完整性约束，并将实体模型转化为关系模型，并设计关键字，以及设计适当的触发器
2.2 需求分析
（1） 分析该业务流程的内在联系
分析业务流程后得出：工厂与车间为一对多关系, 车间与工人为一对多关系, 车间与产品为一对多关系，仓库与产品为一对多关系,仓库与零件为一对多关系, 车间与零件为多对多关系, 产品和零件为多对多关系, 工厂与仓库为一对多关系
（2） 对象处理如下：
对象处理：
工厂信息：厂名、厂长名。
车间信息：车间号、车间主任姓名、地址和电话。
职工信息：职工号、姓名、年龄、性别和工种。
产品信息：产品号、产品名称和价格。
零件信息：零件号、重量和价格。
仓库信息：仓库号、仓库主任姓名和电话。

（3） 实现功能与分析如下：
实现功能：实现功能其中需要注意的是，安全性需要根据其需要来给予其一定程度的安全性，再经过用户授权机制，经过用户登陆来识别用户级别，再根据这个级别来分配用户权限，从而实现更高层次的安全保密功能。完整性要求描述各信息间的关联关系和制约关系，需要根据各个值的实际情况来分析数据的数据范围及注意其是否为NULL（空），根据实际需要来满足要求。
分析：此系统首先画出了其E-R图，并用word文档插入表格并写入各个表的信息，在对work(工厂信息)进行给该和查询，包括：创立数据库信息，查询各表信息，更改element(零件表)信息，workman(工人表)信息，建立一个名为manage存储过程，实现修改element(零件表)信息，workman(工人表)信息。
对于安全性操作，建立了一个名为chenquanyu的登录名，名为cqy的用户，并赋予其表element(零件表)，product(产品表)和storage(仓库表)的privilege(因此权限)操作，对product（产品表）创立一个名为IS_product的视图，对workman(工人信息表) 创立一个名为IS_work的视图。并对product(pno)（产品号）和element(eno)创立一个索引，并删除其索引。创立一个名为tri_update_delete_product的触发器，来触发更新产品价格，并触发删除产品号为3的信息。最后创立一个磁盘备份，名字为work_full，地址为'd:beifen\work.bak'，并实现完全备份和日记备份。
三、 数据库环境说明
提示：
（1）说明所采用的数据库系统，设计工具，编程工具等
（2）详细配置
例如：
数据库实例
数据库系统
数据库部署环境
数据库设计工具
数据库存放位置
说明
工人数据库管理系统
SQL server
硬件:2GB内存,512M以上 软件:windows xp
Microsoft Office Visio 和SQL server
D盘根目录
查询、统计工人和产品基本信息,模拟工厂生产过程

四、 数据库的命名规则
4.1 数据库对象命名规则
数据库对象
命名规则
备注
表
功能描述字符串
例如: factory 工厂表
视图
view_功能描述字符串
例如: view_factory 工厂视图
索引
index_功能描述字符串
例如:index_factory 工厂索引
存储过程
procedure_功能描述字符串
例如: procedure_factory 工厂存储过程
触发器
trigger_功能描述字符串
例如: trigger_factory 工厂触发器

4.2 数据项编码规则
数据项
命名规则
数据类型
长度范围
备注
车间号
车间序号（两位整数00-99）
定长字符串
10位
workshop_no车间号
职工姓名
姓名（不定长）
不定长字符串
4位
wname 职工姓名
产品价格
价格(不定长)
浮点型
10
pprice 产品价格

五、 逻辑设计

一个m：n联系转换为一个关系模式。与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为各实体码的组合。
一个1：n联系能够转换为一个独立的关系模式，也能够与n端对应的关系模式合并。如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为n端实体的码。
一个1：1联系能够转换为一个独立的关系模式，也能够与任意一端对应的关系模式合并。三个或三个以上实体间的一个多元联系能够转换为一个关系模式。与该多元联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为各实体码的组合
逻辑结构设计的任务是将概念结构设计的E-R图，转化为与选用的 DBMS所支持的数据模型相符的逻辑结构，形成逻辑模型。
给表信息包含如下：
工厂(厂名, 厂长名)
车间(车间号,车间主任姓名,地址,电话)
工人(职工号,姓名,年龄,性别,工种)
产品(产品号,产品名称,价格)
零件(零件号,重量,价格)
仓库(仓库号,仓库主任姓名,电话)
生产(车间号,零件号,生产日期)
组装(产品号,零件号,组装数)
存入(仓库号, 产品号,零件号,存储量)

5.1创立数据库系统的关系模型如下

工厂信息表：factory
字段中文名
字段名
数据类型
是否为空
约束
厂名
fno
varchar(10)
NOT NULL
Primary key
厂长名
fdname
vachar(10)

//工厂与车间一对多
车间信息表：workshop
字段中文名
字段名
数据类型
是否为空
约束
车间号
workshop_no
char(10)
NOT NULL
Primary key
车间主任姓名
wdname
varchar(10)

地址
wAddress
varchar(10)

电话
wtel
varchar(20)

厂名
fno
varchar(10)
NOT NULL
Foreign key（外键）

//车间与工人一对多
工人信息表： workman
字段中文名
字段名
数据类型
是否为空
约束
职工号
wno
char(10)
NOT NULL
Primary key
姓名
wname
varchar(10)

年龄
wage
int

工种
wtype
varchar(10)

车间号
workshop_no
char(10)
NOT NULL
Foreign key（外键）

//车间与产品一对多，仓库与产品一对多
产品信息表：product
字段中文名
字段名
数据类型
是否为空
约束
产品号
pno
varchar(10)
NOT NULL
Primary key
名称
pname
varchar(10)

价格
pprice
float

车间号
workshop_no
char(10)
NOT NULL
Foreign key（外键）
仓库号
sno
char(10)
NOT NULL
Foreign key（外键）

//仓库与零件一对多
零件信息表：element
字段中文名
字段名
数据类型
是否为空
约束
零件号
eno
char(10)
NOT NULL
Primary key
重量
eweight
float

价格
eprice
float

仓库号
sno
char(10)
NOT NULL
Foreign key（外键）

//车间与零件多对多
车间与零件信息表: workshop_element
字段中文名
字段名
数据类型
是否为空
约束
车间号
workshop_no
char(10)
NOT NULL
Primary key
零件号
eno
char(10)
NOT NULL
Primary key

//产品和零件多对多
产品与零件信息表：product_element
字段中文名
字段名
数据类型
是否为空
约束
产品号
pno
varchar(10)
NOT NULL
Primary key
零件号
eno
char(10)
NOT NULL
Primary key

//工厂与仓库一对多
仓库信息表：storage
字段中文名
字段名
数据类型
是否为空
约束
仓库号
sno
char(10)
NOT NULL
Primary key
仓库主任姓名
sdname

电话
stel

厂名
fno
varchar(10)
NOT NULL
Foreign key（外键）

六、 物理设计
6.1表汇总
表名
功能说明
表element
查询，插入，索引，存储过程
表product
查询，插入，修改，视图，索引，权限设置，创立存储过程
表storage
查询，更新，删除，权限设置，存储过程

6.2工厂表如下：
1. 工厂信息表：factory
表名
工厂信息表：factory
数据库用户
work
主键
fno
其它排序字段

索引字段

序号
字段名称
数据类型（精度范围）
允许为空Y/N
唯一Y/N
区别度
默认值
约束条件/说明
1
fno
Varchar(10)
N

主键
2
fdname
Varchar(10)

sql脚本
create table factory
(
fno varchar(10) NOT NULL primary key,
fdname varchar(10)
)
备注
[备注信息]

2. 车间信息表：workshop
表名
车间信息表：workshop
数据库用户
work
主键
workshop_no
其它排序字段

索引字段

序号
字段名称
数据类型（精度范围）
允许为空Y/N
唯一Y/N
区别度
默认值
约束条件/说明
1
workshop_no
char(10)
N

主键
2
wdname
varchar(10)

3
waddress
varchar(10)

fno
varchar(10)
N

Foreign key（外键）
sql脚本
create table workshop
(
workshop_no char(10) NOT NULL primary key,
wdname varchar(10),
waddress varchar(10),
wtel varchar(20),
fno varchar(10) NOT NULL,
foreign key (fno) references factory(fno)
)
备注
工厂与车间一对多

3. 工人信息表： workman
表名
工人信息表： workman
数据库用户
work
主键
wno
其它排序字段

索引字段

序号
字段名称
数据类型（精度范围）
允许为空Y/N
唯一Y/N
区别度
默认值
约束条件/说明
1
wno
char(10)
N

主键
2
wname
varchar(10)

3
wage
int

4
wtype
varchar(10)

5
workshop_no
char(10)
N

Foreign key（外键）
sql脚本
create table workman
(
wno char(10) NOT NULL primary key,
wname varchar(10),
wage int,
wtype varchar(10),
workshop_no char(10) NOT NULL,
foreign key (workshop_no) references workshop(workshop_no)
)
备注
车间与工人一对多

4. 产品信息表：product
表名
产品信息表：product
数据库用户
work
主键
pno
其它排序字段

索引字段

序号
字段名称
数据类型（精度范围）
允许为空Y/N
唯一Y/N
区别度
默认值
约束条件/说明
1
pno
varchar(10)
N

主键
2
pname
varchar(10)

3
pprice
float

4
workshop_no
char(10)
N

Foreign key（外键）
5
sno
char(10)
N

Foreign key（外键）
sql脚本
create table product
(
pno varchar(10) NOT NULL primary key,
pname varchar(10),
pprice float,
workshop_no char(10) NOT NULL,
sno char(10) NOT NULL,
foreign key (workshop_no) references workshop(workshop_no),
foreign key (sno) references storage(sno)
)
备注
车间与产品一对多，仓库与产品一对多

5. 零件信息表：element
表名
零件信息表：element
数据库用户
work
主键
eno
其它排序字段

索引字段

序号
字段名称
数据类型（精度范围）
允许为空Y/N
唯一Y/N
区别度
默认值
约束条件/说明
1
eno
char(10)
N

主键
2
eweight
float

3
eprice
float

4
sno
char(10)
N

Foreign key（外键）
sql脚本
create table element
(
eno char(10) NOT NULL primary key,
eweight float,
eprice float,
sno char(10) NOT NULL,
foreign key (sno) references storage(sno)
)
备注
仓库与零件一对多

6. 车间与零件信息表: workshop_element
表名
车间与零件信息表: workshop_element
数据库用户
work
主键
workshop_no,eno
其它排序字段

索引字段

序号
字段名称
数据类型（精度范围）
允许为空Y/N
唯一Y/N
区别度
默认值
约束条件/说明
1
fno
workshop_no
char(10)
N

主键
2
fdname
eno
char(10)
N

主键
sql脚本
create table workshop_element
(
workshop_no char(10) NOT NULL ,
eno char(10) NOT NULL ,
primary key(workshop_no,eno),
foreign key (workshop_no) references workshop(workshop_no),
foreign key (eno) references element(eno)
)

备注
车间与零件多对多

7. 产品与零件信息表：product_element
表名
产品与零件信息表：product_element
数据库用户
work
主键
pno,eno
其它排序字段

索引字段

序号
字段名称
数据类型（精度范围）
允许为空Y/N
唯一Y/N
区别度
默认值
约束条件/说明
1
pno
varchar(10)
N

主键
2
eno
char(10)
N

主键
sql脚本
create table product_element
(
pno varchar(10) NOT NULL ,
eno char(10) NOT NULL ,
primary key(pno,eno),
foreign key (pno) references product(pno),
foreign key (eno) references element(eno)
)
备注
产品和零件多对多

8. 仓库信息表：storage
表名
仓库信息表：storage
数据库用户
work
主键
sno
其它排序字段

索引字段

序号
字段名称
数据类型（精度范围）
允许为空Y/N
唯一Y/N
区别度
默认值
约束条件/说明
1
sno
char(10)
N

主键
2
sdname
varchar(10)

stel
varchar(20)

fno
varchar(10)
N

Foreign key（外键）
sql脚本
create table storage
(
sno char(10) NOT NULL primary key,
sdname varchar(10),
stel varchar(20),
fno varchar(10) NOT NULL,
foreign key (fno) references factory(fno)
)
备注
工厂与仓库一对多

6.3视图的设计

各表关系图如下:

图6.3.1

创立视图过程如下
(1)给产品表创立一个拥有paname(产品名称)，price(产品价格)，以及外键的sno(仓库号)的视图
create view IS_product
as
select sno,pname,pprice
from product
效果图如下：

图6.3.1

(2)给工人信息表创立一个包含有workman(生产表)的所有信息的视图
create view IS_workman
as
select *
from workman
效果图如下:

图6.3.2
6.4存储过程、函数及触发器的设计
6.4.1存储过程代码如下：
create procedure manage --创立一个名字为manage的存储过程
(@eno char(10), --定义一些参数
@wname varchar(10),
@sdname char(10),
@sno char(10)
)
as
declare @eweight1 float=80.4 --申明一个变量@eweight1
if exists(select eweight from element where eweight< @eweight1)
update element
set eweight=80.4 --将element(零件)表中的eweight都定义为80.4

select *
from element
select eprice

from element
where eno=@eno

update workman
set wage=10 where wname = @wname

select *
from workman

update storage
set sdname=@sdname where sno=@sno
select *
from storage
print'修改后的零件表'

exec manage
@eno='e03', --当eno中为e03时，再查询此时此刻的pprice(价格)
@wname='贺江平',
@sdname='张攀攀',
@sno='B2'
首先创立了一个名字为manage的存储过程，定义一些能够用到得相关参数，如@eno，@wname，@sdname，@sno，经过这些参数传递确定的数据，利用SQL语句结合在存储过程从而实现想要更改，删除，插入等相关的操作.

存储过程执行后的效果图为：

图6.4.1
6.4.2索引的建立
建立索引使查询的时候按顺序排列
create unique index propno on product(pno) --为product表的pno(产品号)建立索引
create unique index eeno on element(eno) --为element表的eno(零件号)建立索引

6.4.3触发器的建立与实现操作
为product表创立一个基于update操作和delete操作的复合型触发器，当修改了该表的pprice(价格)或者删除了pno(产品号)时，触发器被激活生效，显示相关的操作信息
（1）创立触发器
create trigger tri_update_delete_product --创立触发器
on product
for update,delete
as
if update(pprice) --更新价格
begin
select inserted.pno,deleted.pprice as 原价格,inserted.pprice as 新价格
from deleted,inserted
where deleted.pno=inserted.pno
end
else if columns_updated()=0
begin
select deleted.pno as 被删除的产品号,deleted.pname,deleted.pprice as 原价格
from deleted
end

（2）触发触发器
1．在查询命令窗口中输入以下update语句，修改产品号为’01’的价格，激发触发器
update product
set pprice=pprice+1
where pno='01'
视图如下：

2．在查询命令窗口中输入以下delete语句，删除产品为号为’03’的记录，激发触发器
delete product
where pno='03'
视图如下：

（3）删除新创立的触发器
drop trigger tri_update_delete_product
视图如下：

七、 安全性设计
SQL包括了windows认证和sql server混合认证,其中sql server混合认证提供了用户登录方式，而用户登录后想更改操作的话，必须分配给用户名权限，才能对数据库的表进行相关操作。
以下是用SQL语句实现的用户名登录，和受权操作：
exec sp_grantdbaccess 'chenquanyu','cqy'
//允许新建登录名和用户，其中chenquanyu是登录名,cqy是用户
exec sp_addlogin 'chenquanyu','123456','work'
//用户名登录,密码为123456，默认数据库为work
exec sp_addsrvrolemember 'chenquanyu','sysadmin'
//授予用户名chenquanyu一些数据库操作的权限
GRANT ALL privileges on product
to cqy

GRANT ALL privileges on element
to cqy

grant update on storage
to cqy
//将product产品表和零件表的所有权限授予给cqy用户，仓库信息表的更新权限授予给cqy用户

使用查询分析器管理备份设备和备份数据库

//创立一个磁盘备份设备，名字为work_full，地址为'd:beifen\work.bak'
exec sp_addumpdevice 'disk','work_full','d:\beifen\work.bak'

backup database work to work_full with init --完全备份
备份执行如下：

backup log work to work_full with noinit --日记备份，使用备份语句backup来备份数据库
日记备份执行如下：

7.1防止用户直接操作数据库的方法
不授予用户操作的权限。
用户只能用帐号登陆到应用软件，经过应用软件访问数据库，而没有其它途径操作数据库。

7.2用户帐号密码的加密方法
用户账户密码加密是在程序里实现的，将用户密码经过某种算法加密转换后再存入数据库。
对用户帐号的密码进行加密处理，确保在任何地方都不会出现密码的明文。用户帐号采用MD5进行数据加密后再录入数据库，以防止任何地方密码的安全性要求。

7.3角色与权限
确定每个角色对数据库表的操作权限，如创立、检索、更新、删除等。每个角色拥有刚好能够完成任务的权限，不多也不少。在应用时再为用户分配角色，则每个用户的权限等于她所兼角色的权限之和。
角色
能够访问的表与列
操作权限

例如：管理员
可访问所有表
完全控制权限

角色B
Product表
可操作此表全部信息
element表
可操作此表全部信息
Storage表
只能操作update权限

八、 数据库管理与维护说明
提示：在设计数据库的时候，及时给出管理与维护本数据库的方法，有助于将来撰写出正确完备的用户手册。
在运行数据库的同时先要在脑海里想好需求分析，然后再根据要求画出E-R图，然后再根据逻辑结构在word文档下根据表的信息，依次完成各信息表，然后根据表与表对应的,1:1,1：n或n:m关系确定好表的主外键，注:多对多的表还要另外创立一表来表示两表之间的关系。完成表的建立后，接下来的操作就是往各个表中插入相关实际信息，以及利用SQL语句来实现表的插入，插入完毕后，可运用建立一个存储过程来完成表的更新，删除，插入等操作，操作完毕后可，用SQL语句建立视图，好让操作员更能体会表与表之间的关系，而且看不出关键隐私代码，这样就能很好的维护数据库的管理和专利。在设置安全性的时候，注意用户必须要授权才能对各表进行操作,备份需要用语句实现。

九、 总结
本次数据库课程设计让我深刻体会到了，书本前后的衔接性，这次课程设计涉及到了书本几乎全部知识点，而在本次课程设计之前，我一直都没搞懂很多知识点，而且运用起来很生疏，尽管刚