工厂数据库管理系统.doc

工厂数据库管理系统课程设计说明书题目 工厂数据库管理系统 系(部) 计算机科学与技术系 专业(班级) 姓名 学号 指导教师 起止日期 课程设计任务书课程名称：数据库系统原理设计题目：数据库系统课程设计工厂数据库管理系统1、某工厂需建立一个管理数据库存储以下信息：工厂包括厂名和厂长名。一个厂内有多个车间，每个车间有车间号、车间主任姓名、地址和电话。一个车间有多个工人，每个工人有职工号、姓名、年龄、性别和工种。一个车间生产多种产品，产品有产品号、产品名称和价格。一个车间生产多种零件，一个零件也可能为多个车间制造。零件有零件号、重量和价格。一个产品由多种零件组成，一种零件也可装配出多种产品。产品与零件均存入仓库中。厂内有多个仓库，仓库有仓库号、仓库主任姓名和电话。2、系统功能的基本要求：按照一定条件查询、统计工人和产品等基本信息，能模拟工厂生产过程中对原材料（零件）、产品检测、库存环节的管理。各阶段具体要求：1、需求分析阶段l 定义数据项的含义和取值l 定义目标系统的数据流2、概念结构设计阶段l 画出实体模型E-R图3、逻辑结构设计阶段l 将实体模型转化为关系模型l 给出每个关系的主关键字和函数依赖集l 分析你所设计的关系数据库模式是否属于3NF4、物理设计阶段l 确定所有字段的名称、类型、宽度、小数位数及完整性约束 l 确定数据库及表的名称及其组成 l 确定索引文件和索引关键字5、数据库安全及维护设计阶段l 设计一个适合的数据库安全策略（用户身份认证、访问权限、视图）l 为了实现复杂的数据完整性约束，设计适当的触发器l 设计一个适合的数据库备份策略6、实施阶段l 要求所有操作必须在查询分析器中用SQL语句或系统存储过程完成。设计工作量：（1）软件设计：完成问题陈述中所提到的所有需求功能。（2）论文：要求撰写不少于3000个文字的文档，详细说明各阶段具体要求。工作计划：安排两周时间进行课程设计，软件开发步骤如下，第一周完成14，第二周完成58，论文同步进行；1)选定题目2)需求分析3)概念结构设计4)逻辑结构设计5)物理设计6)数据库安全及维护设计7)数据库上机实现8)答辩设计工作量：40课时工作计划：见课表指导教师签名：日期：教研室主任签名： 日期：系主任签名： 日期： 长沙学院课程设计鉴定表姓名学号专业班级设计题目工厂数据库管理系统指导教师指导教师意见：评定等级： 教师签名： 日期： 答辩小组意见：评定等级：答辩小组长签名：日期：教研室意见：教研室主任签名： 日期： 系（部）意见：系主任签名：日期：说明课程设计成绩分“优秀”、“良好”、“及格”、“不及格”四类；摘 要根据而今信息时代，信息越来越多，越复杂，这时就需要一种管理数据的软件，本次课程设计运用SQL server 来实现数据的管理，本次试验经过SQL语句来实现工厂的流程，使工厂运行效率达到最大值，需要对其进行分析需求，逻辑设计，物理设计，安全性分析，权限设置等操作。关键词：SQL 管理数据 工厂目 录一、引言71.1编写目的71.2参考资料7二、需求规约82.1 业务描述82.2 需求分析8三、数据库环境说明9四、数据库的命名规则94.1 数据库对象命名规则94.2 数据项编码规则9五、逻辑设计105.1创立数据库系统的关系模型11六、物理设计126.1表汇总126.2表总汇136.3视图的设计196.4存储过程、函数及触发器的设计21七、安全性设计237.1防止用户直接操作数据库的方法247.2用户帐号密码的加密方法247.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:beifenwork.bak，并实现完全备份和日记备份。三、 数据库环境说明提示： （1）说明所采用的数据库系统，设计工具，编程工具等（2）详细配置例如：数据库实例数据库系统数据库部署环境数据库设计工具数据库存放位置说明工人数据库管理系统SQL server 硬件:2GB内存,512M以上 软件:windows xpMicrosoft 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 职工姓名产品价格价格(不定长)浮点型10pprice 产品价格五、 逻辑设计一个m：n联系转换为一个关系模式。与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为各实体码的组合。一个1：n联系能够转换为一个独立的关系模式，也能够与n端对应的关系模式合并。如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为n端实体的码。 一个1：1联系能够转换为一个独立的关系模式，也能够与任意一端对应的关系模式合并。三个或三个以上实体间的一个多元联系能够转换为一个关系模式。与该多元联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为各实体码的组合 逻辑结构设计的任务是将概念结构设计的E-R图，转化为与选用的DBMS所支持的数据模型相符的逻辑结构，形成逻辑模型。给表信息包含如下：工厂(厂名, 厂长名)车间(车间号,车间主任姓名,地址,电话)工人(职工号,姓名,年龄,性别,工种)产品(产品号,产品名称,价格)零件(零件号,重量,价格)仓库(仓库号,仓库主任姓名,电话)生产(车间号,零件号,生产日期)组装(产品号,零件号,组装数)存入(仓库号, 产品号,零件号,存储量)5.1创立数据库系统的关系模型如下工厂信息表：factory字段中文名字段名数据类型是否为空约束厂名fnovarchar(10)NOT NULLPrimary key厂长名fdnamevachar(10)/工厂与车间一对多车间信息表：workshop字段中文名字段名数据类型是否为空约束车间号workshop_nochar(10)NOT NULLPrimary key车间主任姓名wdnamevarchar(10)地址wAddressvarchar(10)电话wtelvarchar(20)厂名fnovarchar(10)NOT NULLForeign key（外键）/车间与工人一对多工人信息表： workman字段中文名字段名数据类型是否为空约束职工号wnochar(10)NOT NULLPrimary key姓名wnamevarchar(10)年龄wageint工种wtypevarchar(10)车间号workshop_nochar(10)NOT NULLForeign key（外键）/车间与产品一对多，仓库与产品一对多产品信息表：product字段中文名字段名数据类型是否为空约束产品号pnovarchar(10)NOT NULLPrimary key名称pnamevarchar(10)价格ppricefloat车间号workshop_nochar(10)NOT NULLForeign key（外键）仓库号snochar(10)NOT NULLForeign key（外键）/仓库与零件一对多零件信息表：element字段中文名字段名数据类型是否为空约束零件号enochar(10)NOT NULLPrimary key重量eweightfloat价格epricefloat仓库号snochar(10)NOT NULLForeign key（外键）/车间与零件多对多车间与零件信息表: workshop_element字段中文名字段名数据类型是否为空约束车间号workshop_nochar(10)NOT NULLPrimary key零件号enochar(10)NOT NULLPrimary key/产品和零件多对多产品与零件信息表：product_element字段中文名字段名数据类型是否为空约束产品号pnovarchar(10)NOT NULLPrimary key零件号enochar(10)NOT NULLPrimary key/工厂与仓库一对多仓库信息表：storage字段中文名字段名数据类型是否为空约束仓库号snochar(10)NOT NULLPrimary key仓库主任姓名sdname电话stel厂名fnovarchar(10)NOT NULLForeign key（外键）六、 物理设计6.1表汇总表名功能说明表element查询，插入，索引，存储过程表product查询，插入，修改，视图，索引，权限设置，创立存储过程表storage查询，更新，删除，权限设置，存储过程6.2工厂表如下：1. 工厂信息表：factory表名工厂信息表：factory数据库用户work主键fno其它排序字段索引字段序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1fnoVarchar(10)N主键2fdnameVarchar(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区别度默认值约束条件/说明1workshop_nochar(10)N主键2wdnamevarchar(10)3waddressvarchar(10)fnovarchar(10)NForeign 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区别度默认值约束条件/说明1wnochar(10)N主键2wnamevarchar(10)3wageint4wtypevarchar(10)5workshop_nochar(10)NForeign 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区别度默认值约束条件/说明1pnovarchar(10)N主键2pnamevarchar(10)3ppricefloat4workshop_nochar(10)NForeign key（外键）5snochar(10)NForeign 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区别度默认值约束条件/说明1enochar(10)N主键2eweightfloat3epricefloat4snochar(10)NForeign 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区别度默认值约束条件/说明1fnoworkshop_nochar(10)N主键2fdnameenochar(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区别度默认值约束条件/说明1pnovarchar(10)N主键2enochar(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区别度默认值约束条件/说明1snochar(10)N主键2sdnamevarchar(10)stelvarchar(20)fnovarchar(10)NForeign 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_productasselect sno,pname,ppricefrom product效果图如下： 图6.3.1(2)给工人信息表创立一个包含有workman(生产表)的所有信息的视图create view IS_workmanasselect *from workman效果图如下:图6.3.26.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 -申明一个变量eweight1if exists(select eweight from element where eweight eweight1)update elementset eweight=80.4 -将element(零件)表中的eweight都定义为80.4select *from elementselect epricefrom elementwhere eno=eno update workmanset wage=10 where wname = wnameselect *from workmanupdate storageset sdname=sdname where sno=snoselect *from storageprint修改后的零件表exec manage eno=e03, -当eno中为e03时，再查询此时此刻的pprice(价格)wname=贺江平,sdname=张攀攀,sno=B2首先创立了一个名字为manage的存储过程，定义一些能够用到得相关参数，如eno，wname，sdname，sno，经过这些参数传递确定的数据，利用SQL语句结合在存储过程从而实现想要更改，删除，插入等相关的操作.存储过程执行后的效果图为：图6.4.16.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 productfor update,deleteasif update(pprice) -更新价格beginselect inserted.pno,deleted.pprice as 原价格,inserted.pprice as 新价格from deleted,insertedwhere deleted.pno=inserted.pnoendelse if columns_updated()=0beginselect deleted.pno as 被删除的产品号,deleted.pname,deleted.pprice as 原价格from deletedend（2）触发触发器1在查询命令窗口中输入以下update语句，修改产品号为01的价格，激发触发器update productset pprice=pprice+1where pno=01视图如下：2在查询命令窗口中输入以下delete语句，删除产品为号为03的记录，激发触发器delete productwhere 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，默认数据库为workexec sp_addsrvrolemember chenquanyu,sysadmin/授予用户名chenquanyu一些数据库操作的权限GRANT ALL privileges on productto cqyGRANT ALL privileges on elementto cqygrant update on storageto cqy/将product产品表和零件表的所有权限授予给cqy用户，仓库信息表的更新权限授予给cqy用户使用查询分析器管理备份设备和备份数据库/创立一个磁盘备份设备，名字为work_full，地址为d:beifenwork.bakexec sp_addumpdevice disk,work_full,d:beifenwork.bakbackup database work to work_full with init -完全备份备份执行如下：backup log work to work_full with noinit -日记备份，使用备份语句backup来备份数据库日记备份执行如下：7.1防止用户直接操作数据库的方法不授予用户操作的权限。用户只能用帐号登陆到应用软件，经过应用软件访问数据库，而没有其它途径操作数据库。7.2用户帐号密码的加密方法用户账户密码加密是在程序里实现的，将用户密码经过某种算法加密转换后再存入数据库。对用户帐号的密码进行加密处理，确保在任何地方都不会出现密码的明文。用户帐号采用MD5进行数据加密后再录入数据库，以防止任何地方密码的安全性要求。7.3角色与权限确定每个角色对数据库表的操作权限，如创立、检索、更新、删除等。每个角色拥有刚好能够完成任务的权限，不多也不少。在应用时再为用户分配角色，则每个用户的权限等于她所兼角色的权限之和。角色能够访问的表与列操作权限例如：管理员可访问所有表完全控制权限角色BProduct表可操作此表全部信息element表可操作此表全部信息Storage表只能操作update权限八、 数据库管理与维护说明提示：在设计数据库的时候，及时给出管理与维护本数据库的方法，有助于将来撰写出正确完备的用户手册。在运行数据库的同时先要在脑海里想好需求分析，然后再根据要求画出E-R图，然后再根据逻辑结构在word文档下根据表的信息，依次完成各信息表，然后根据表与表对应的,1:1,1：n或n:m关系确定好表的主外键，注:多对多的表还要另外创立一表来表示两表之间的关系。完成表的建立后，接下来的操作就是往各个表中插入相关实际信息，以及利用SQL语句来实现表的插入，插入完毕后，可运用建立一个存储过程来完成表的更新，删除，插入等操作，操作完毕后可，用SQL语句建立视图，好让操作员更能体会表与表之间的关系，而且看不出关键隐私代码，这样就能很好的维护数据库的管理和专利。在设置安全性的时候，注意用户必须要授权才能对各表进行操作,备份需要用语句实现。九、 总结 本次数据库课程设计让我深刻体会到了，书本前后的衔接性，这次课程设计涉及到了书本几乎全部知识点，而在本次课程设计之前，我一直都没搞懂很多知识点，而且运用起来很生疏，尽管刚