数据库系统概论》课程设计-工厂数据库管理系统.doc

一、课程设计的目的掌握数据库的基础知识、基本理论、原理和实现技术。二、设计题目：工厂数据库管理系统已知技术参数和设计要求：需求说明及要求题目：工厂数据库管理系统某工厂需建立一个管理数据库存储以下信息：1、 工厂包括厂名和厂长名。2、 一个厂内有多个车间，每个车间有车间号、车间主任姓名、地址和电话。3、 一个车间有多个工人，每个工人有职工号、姓名、年龄、性别和工种。4、 一个车间生产多种产品，产品有产品号、产品名称和价格。5、 一个车间生产多种零件，一个零件也可能为多个车间制造。零件有零件号、重量和价格。6、 一个产品由多种零件组成，一种零件也可装配出多种产品。7、 产品与零件均存入仓库中。8、 厂内有多个仓库，仓库有仓库号、仓库主任姓名和电话。各阶段具体要求：1、需求分析阶段l 定义数据项的含义和取值l 定义目标系统的数据流2、概念结构设计阶段l 画出实体模型e-r图3、逻辑结构设计阶段l 将实体模型转化为关系模型l 给出每个关系的主关键字和函数依赖集l 分析你所设计的关系数据库模式是否属于3nf4、物理设计阶段l 确定所有字段的名称、类型、宽度、小数位数及完整性约束 l 确定数据库及表的名称及其组成 l 确定索引文件和索引关键字5、数据库安全及维护设计阶段l 设计一个适合的数据库安全策略（用户身份认证、访问权限、视图）l 为了实现复杂的数据完整性约束，设计适当的触发器l 设计一个适合的数据库备份策略6、实施阶段l 要求所有操作必须在查询分析器中用sql语句或系统存储过程完成。三、设计内容1将概念模型转化为关系模型从e-r图中可以看到有6个实体，所以我建立了6个实体关系表，并且把实体键作为表的关键字，4个一对多的联系表，在建立关系模型的过程中，我是把一这边的实体键放到多这边做为外键，4个多对多的联系，所以我建立了4个联系表，并把相互联系的两实体的实体键，分别作为联系表的外键。在处理仓库与零件多对多的联系和仓库与产品多对多的联系过程中有以下两种方法：（1） 建立两个存储表，仓库零件存储表和仓库产品存储表，并把仓库号，零件号和仓库号，产品号分别作为这两个联系表的外键，这样就保持了参照完整性的约束条件，而且建立两个联系表清晰明了，更容易使人明白。（2） 建立一个存储表，仓库物品存储表，它包含仓库号，物品号（零件号和产品号），库存量3个属性，建立一个存储表更简洁，但是在建表的过程中丢失的参照完整性。综合考虑以上两种方法，我觉得第（1）中更好。四、设计步骤4.1需求分析阶段(1)定义数据项的含义和取值数据项名含义 类型 宽度 小数位数facname工厂名char 10facmname厂长名 char 8cno车间主任编号 char 8workno车间号char 4workaddress车间地址 char 12workphone车间电话 char 5workerno职工号 char 5workername职工名 char 8woerkerage职工年龄 int 4workersex性别 char 2workertype工种 char 10productno产品号 char 5productname产品名称 char 12productprice产品价格 numeric 5 1comno零件号 char 5comweight零件重量 int 4comprice零件价格 numeric 51wareno仓库号 char 5wno仓库主任编号 char 8warephone仓库电话 char 12(2)定义数据结构数据结构含义组成工厂 工厂情况表facname+facmname车间车间情况表workname+cno+ workno+workaddress+ workphone+facname 职工 职工情况表workerno+workername+workersex+ workertype+workerage+workno 产品 产品情况表 productno+productname+ productprice+snum1 零件零件情况表 comno+comweight+comprice+snum2 仓库仓库情况表wareno+wno+warephone+facname(3)定义数据流数据流名 含义 组成 factory 工厂情况表 facname+facmname workshop 车间情况表workno+cname+workaddress+ workphone+facname workerqk职工情况表workerno+ workername+ workersex+ workertype productqk产品情况表productno+ productname+ productprice+workno compentsqk零件情况表comno + comweight+ compricewareqk仓库情况表wareno+wname+warephone+facname4.2概念结构设计阶段画出实体模型e-r图仓库工厂产品车间零件工人仓库主任编号仓库号电话工厂名厂长名车间主任编号地址电话工种车间号姓名年龄性别职工号产品号产品名称价格数量零件号重量价格有存入存入组装拥有有有有有生产库存量库存量生产数量生产日期4.3逻辑结构设计阶段（1）将实体模型转化为关系模型实体转化成关系表factoryfacnamefacmnameworkshop worknocnoworkaddressworkphonefacnameworkerworkernoworkertypeworkersexworkernameworkerageproductproductnoproductnameproductpriceworknocompoentscomnocomweightcompricewarehouse warenownowarephonefacname多对多的联系转化成实体表w-c（生产）worknocomnocomnum1p-c（组装）productnocomnopronumcomnum2wac（仓库-零件）存储warenocomnosnum2wap（仓库-产品）存储warenoproductnosnum1(2)每个关系的主关键字和函数依赖集factory（facname，facmname）facname facmnameworkshop（workno，cno，workaddress，workphone，facname）worknoworkaddress, worknocno,worknoworkphone, worknofacnameworker（workerno，workertype，workersex，workername，workerage，workno）workernoworkertype, workernoworkersex, workernoworkernameworkernoworkerage, workernoworknoproduct（productno，productname，productprice，workno）productno productname, productno productprice, productno worknocomponents（comno，comweight，comprice）comnocomweight, comnocompricewarehouse（wareno，facname，warephone，wno）wareno facname, wareno warephone, wareno wnowc（comno，workno, comnum1）(comno，workno)comnum1pc（productno，comno，pronum，comnum2）（productno，comno）pronum, （productno，comno）comnum2wac（comno，wareno，sum2）（comno，wareno）sum2wap（wareno，productno，sum1）（wareno，productno）sum1以上的10个关系模式没有非主属性对主关键字的部分依赖和传递依赖，也不存在属于主属性对主关键字的部分依赖和传递依赖，所以以上的10个关系模式都属于3nf4.4物理设计阶段（1）确定所有字段的名称、类型、宽度、小数位数及完整性约束数据项名含义 类型 宽度 小数位数 完整性要求facname工厂名varchar 30primary keyfacmname厂长名 char 8not nullcno车间主任编号 varchar 8参照职工号workno车间号varchar 10primary keyworkaddress车间地址 varchar 20workphone车间电话 varchar 12workerno职工号 char 5primary keyworkername职工名 char 8woerkerage职工年龄 int 4workersex性别 char 2（男，女）workertype工种 char 10productno产品号 char 5primary keyproductname产品名称 char 12productprice产品价格 numeric 51 =0comno零件号 char 5primary keycomweight零件重量 int 4 =0comprice零件价格 numeric 51=0number数量int 4 =0wareno仓库号 varchar 10primary keywno仓库主任编号 varchar 8参照职工号warephone仓库电话 varchar 12snum1仓库存储产品数量 int 4 =0snum2仓库存储零件数量int 4 =0(2)确定数据库及表的名称及其组成 关系名table数据结构含义组成字段工厂factory工厂基本信息facname，fmname车间workshop车间基本信息workno,workaddress,workphone,cno,facname职工worker工人基本信息workerno,workername,workersex,workertype, workage,workno产品product产品基本信息productno，productname， productprice，workno零件compents零件基本信息comno，comweight， comprice仓库warehous仓库基本信息wareno，wname，warephone，facname生产wc零件与车间的联系workno ，comno , comnum1组装pc零件与产品的联系productno，comno，comnum2 仓库-零件wac零件与仓库的联系wareno，comno，snum2仓库-产品wap产品与仓库的联系wareno，productno，snum1工厂表 列名数据类型能否空值默认值键/索引说明facnamevarchar(30)否主键、索引厂名fmnamevarchar(8)否厂长名车间表 列名数据类型能否空值默认值键/索引说明workno varchar（10）否主键、索引 车间号cno varchar（8）否车间主任编号workaddress varchar（20）否 车间地址workphonevarchar(12)否 电话fcanamevarchar(30)否工厂名零件表 列名数据类型能否空值默认值键/索引说明comno varchar（10）否主键、索引零件号comweight int否重量compricenumeric(5,1) 否价格职工表 列名数据类型能否空值默认值键/索引说明workerno varchar（10）否主键、索引 职工号workername varchar（8）否 姓名workerage int否年龄workersex varchar（2）否性别workertype varchar（20）否 工种 worknovarchar(10)否 车间号产品表 列名数据类型能否空值默认值键/索引 说明productno varchar（10）否主键、索引产品号productname varchar（20）否产品名称productpricenumeric(5，1) 否价格worknovarchar(10)否车间号仓库表 列名数据类型能否空值默认值键/索引说明wareno varchar（10）否主键、索引仓库号wname varchar（8）否仓库主任姓名warephonevarchar(12)否仓库电话facnamevarchar(30)否工厂名生产表 列名数据类型能否空值默认值键/索引说明worknovarchar（10）否主键车间号 comnovarchar（10）否主键零件号 comnum1int否生产零件数组装表 列名数据类型能否空值默认值键/索引说明 productnovarchar（10）否主键产品号 comno varchar（10）否主键零件号 comnum2int否组装零件数 pronumint否组装产品数产品存储表 列名数据类型能否空值默认值键/索引说明 warenovarchar（10）否主键仓库号 productnovarchar（10）否主键产品号 snum2int否产品存放量零件存储表 列名数据类型能否空值默认值键/索引说明 warenovarchar（10）否主键仓库号 comnovarchar（10）否主键零件号 snum1int否零件存放量创建数据库和关系表1.创建factory数据库2创建factory（工厂）表3创建workshop（车间）表4创建worker（职工）表5创建product（产品）表6创建component（零件）表7创建warehouse（仓库）表8创建wc（生产）表9创建pc（组装）表10创建wap（存储表1）仓库-产品11创建wac（存储表2）仓库-零件(3)确定索引文件和索引关键字table索引文件索引关键字factoryfactory_idxfacnoworkshopworkshop_idxworknoworkerworker_idxworkernoproductproduct_idxproductnocompoentcompents_idxcomnowarehousewarehouse_idxwarenowcwc_idxprotime+workno+comnopcpc_idxproductno+comnowacwac_idxwareno+comnowapwap_idxwareno+productno4.5数据库安全及维护设计阶段(1)设计一个适合的数据库安全策略（用户身份认证、访问权限、视图）添加新用户hh (2)添加角色(3)授权1把factory表的所有权限授给hh2把workshop表的所有权限授给hh3把worker表的所有权限授给hh4把product表的所有权限授给hh5把component表的所有权限授给hh6把warehouse表的所有权限授给hh（4）创建索引1为工厂表在工厂名facname上建立索引2为车间表在车间号workno上建立索引3为职工表在职工编号workerno上建立索引4为产品表在产品号productno上建立索引5为零件表在零件号comno上建立索引6为仓库表在仓库号wareno上建立索引（5）创建视图（6）创建存储过程1在工厂表上创建存储过程2在车间表上创建存储过程3在职工表上创建存储过程4在产品表上创建存储过程5在零件表上创建存储过程6在仓库表上创建存储过程7在生产表上创建存储过程8在组装表上创建存储过程9在零件存储表上创建存储过程10在产品存储表上创建存储过程执行存储过程1向工厂表中插入数据2向车间表中插入数据3向职工表中插入数据4向产品表中插入数据5向零件表中插入数据6向仓库表中插入数据7向生产表中插入数据8向组装表中插入数据9向零件存储表中插入数据 10向产品存储表中插入数据 4.6.2查询存储过程1功能是通过某一职工的姓名查询他的全部信息2功能是通过某一产品的名称查询该产品的基本信息3功能是通过某一零件的名称查询该零件的基本信息4功能是通过某一产品的名称查询该产品所需要的零件种类与数量5通过某一车间号查询改车间的所有信息6通过输入某一日期查询该日生产零件的种类和零件的数目以及生产该零件的车间号4.6.3创建触发器1在生产表上建立触发器，当上生产表中插入一行数据时，零件存储表中零件的库存量就会增加，零件的库存量等于先前的零件库存量加上生产表中生产零件的数量触发前触发后2在产品存储表上建立触发器，当向产品存储表中插入数据，即产品的库存量增加时，零件的库存量就会减少，某种零件的库存量等于先前的库存量减去该种产品所需要的该零件数乘以生产产品的数量五设计一个适合的数据库备份策略创建一个磁盘备份的设备use factorygoexec sp_addumpdevice disk,factory_full,f:haofactory.bakgo完全备份use factory gobackup database factory to factory_full with initgo1完全备份工厂数据库增量备份use factory gobackup database factory to disk=f:hufactory_diff.bskwith noinit,differentialgo还原整个university数据库restore database factory from factory_full还原差异备份restore database factory from factory_differ with norecovery还原日志备份restore database log from factory_log with recovery总结这次课程设计加深了我对