工厂数据库管理系统数据库课程设计

工厂数据库管理系统数据库课程设计 长 沙 学 院 课程设计说明书 题目 工厂数据库管理系统 系部 计算机科学与技术系 专业班级 11级计科01班 姓名 潘秋芳 学号 2011021141 指导教师 张肖霞 起止日期 2013.12.09-2013.12.20 课程设计任务书 课程名称:数据库基础课程设计 设计题目:工厂数据库管理系统、销售订单数据库管理系统、汽车租赁管理 系统(任选一题)。 已知技术参数和设计要求: 题目一:工厂数据库管理系统 1、某工厂需建立一个管理数据库存储以下信息: 工厂包括厂名和厂长名。 一个厂内有多个车间,每个车间有车间号、车间主任姓名、地址和电话。 一个车间有多个工人,每个工人有职工号、姓名、年龄、性别和工种。 一个车间生产多种产品,产品有产品号、产品名称和价格。 一个车间生产多种零件,一个零件也可能为多个车间制造。零件有零件号、 重量和价格。 一个产品由多种零件组成,一种零件也可装配出多种产品。 产品与零件均存入仓库中。 厂内有多个仓库,仓库有仓库号、仓库主任姓名和电话。 2、系统功能的基本要求: 按照一定条件查询、统计工人和产品等基本信息,能模拟工厂生产过程中对 原(零件)、产品、库存环节的管理。 题目二:销售订单数据库管理系统 1、某销售商的订单系统需要如下信息: 每个供应商包含供应商编号、名称、地址、等信息。 每种产品包含产品号、产品名称、产品类别等信息。 每个供应商可供应多种产品,每种产品可由多个供应商供应。 客户包含编号、姓名、通信地址、电话等信息。 雇员包括编号、姓名、等信息。 订单包括订单号等信息。 一个客户可下多个订单,每个订单只能由一个客户下。 一个雇员可管理多个订单,每个订单只能由一个雇员管理。 一个订单订购多种产品,每种产品可在不同的订单中订购 2、系统功能基本要求: 按照一定条件查询、统计订单信息,例如订单量最多的客户或者产品。能够 模拟完成一个订单交易全过程。 题目三:汽车租赁管理系统 某汽车租赁公司汽车租赁管理系统需要如下信息: 工作人员信息包括:工号、姓名、性别、等。 客户信息包括:身份证号、姓名、性别、所在单位、等。 车辆信息包括:车牌号、品牌、颜色、座位数、日租价格、日租超公里价格、月租价格、租赁状态、购入日期等。 车辆类别信息包括:分类号,库存数。 其业务规则描述如下: 一个工作人员可以对很多辆车辆进行管理,一辆车也可以被多个工作人员管理;一辆车只能属于一种车辆类别,而一种车辆类别可以包含多辆车;一个客户可以租多辆不同的车。 2、系统功能的基本要求: 可以实现对车辆、租赁客户的查询,可以查询汽车、客户租赁历史记录。可以按类别统计汽车的租赁金额和剩余的库存数,可以统计某一年龄客户群体对某类汽车的租赁喜好,能模拟客户对汽车的租借、归还业务。 各阶段具体要求: 1、需求分析阶段 定义数据项的含义和取值 定义目标系统的数据流 2、概念结构设计阶段 画出实体模型E-R图 3、逻辑结构设计阶段 将实体模型转化为关系模型 给出每个关系的主关键字和函数依赖集 分析你所设计的关系数据库模式是否属于3NF 4、物理设计阶段 确定所有字段的名称、类型、宽度、小数位数及完整性约束 确定数据库及表的名称及其组成确定索引文件和索引关键字 5、数据库安全及维护设计阶段 设计一个适合的数据库安全策略(用户身份认证、访问权限、视图) 为了实现复杂的数据完整性约束,设计适当的触发器 设计一个适合的数据库备份策略 6、实施阶段 要求所有操作必须在查询分析器中用SQL语句或系统存储过程完成。 设计工作量: (1)软件设计:完成问题陈述中所提到的所有需求功能。 (2)论文:要求撰写不少于3000个文字的文档,详细说明各阶段具体要求。 工作: 安排两周时间进行课程设计,软件开发步骤如下,第一周完成1~4,第二周完 成5~8,论文同步进行; 1 选定题目 2 需求分析 3 概念结构设计 4 逻辑结构设计 5 物理设计 6 数据库安全及维护设计 7 数据库上机实现 8 答辩 班级 日期 节次 地点 内容 指导老师 11计科1班 15周周一 1-4节 致远楼1402 布置任务、上机调试 张肖霞 15周周二 1-4节 致远楼1402 上机调试15周周四 1-4节 致远楼1402 上机调试15周周四 5-8节 致远楼1402 上机调试15周周五 5-8节 致远楼1402 上机调试16周周一 1-4节 致远楼1402 上机调试16周周二 1-4节 致远楼1402 上机调试16周周四 1-4节 致远楼1402 上机调试16周周四 5-8节 致远楼1402 上机调试16周周五 5-8节 致远楼1402 上机调试,答辩 注意事项 提交文档 长沙学院课程设计任务书 (每学生1份) 长沙学院课程设计论文(每学生1份) 长沙学院课程设计鉴定表 (每学生1份) 指导教师签名: 日期: 教研室主任签名: 日期: 系主任签名: 日期: 长沙学院课程设计鉴定表 姓名学号专业班级 设计题目指导教师 指导教师意见: 评定等级: 教师签名:日期: 答辩小组意见: 评定等级: 答辩小组长签名: 日期: 教研室意见: 教研室主任签名:日期: 系(部)意见: 系主任签名: 日期: 说明 课程设计成绩分“优秀”、“良好”、“及格”、“不及格”四类; 目 录 一、 引言 2 1.1编写目的 2 1.2参考资料 2 二、 需求规约 3 2.1 业务描述 3 2.2 需求分析 4 三、 数据库环境说明 7 四、 数据库的规则命名 7 4.1数据库对象命名规则 7 4.2数据项命名规则 7 五、 概念结构设计阶段 9 六、 逻辑设计 10 6.1将实体模型转化为关系模型 10 6.2函数依赖集 10 6.3关系模式图 11 七、 物理设计阶段 12 7.1表汇总 12 7.2表组成 12 7.3视图的设计 16 7.4存储过程、函数及触发器的设计 18 八、 安全设计阶段 28 8.1防止用户直接操作数据库的方法 28 8.2用户帐号密码的加密方法 28 8.3角色与权限 28 九、 总结 31 引言 1.1编写目的 本文档是工厂数据库管理系统设计文档的组成部分,编写数据库设计文 档的目的是:明确数据库的表名、字段名等数据信息。如:工厂包括厂名和厂长名;一个厂内有多个车间,每个车间有车间号、车间主任、地址和电话;一个车间有多个工人,每个工人有职工号、姓名、年龄、性别和工种;一个车间生产多种产品,产品有产品号、产品名称、质量和价格;一个车间生产多种零件,一个零件也可能为多个车间制造,零件有零件号、重量、质量和价格;一个产品由多种零件组成,一种零件也可装配出多种产品。此文档用来指导后期的数据库脚本的开发,文档的编写遵循《工厂数据库管理系统设计和开发规范》。通过SQL语言编写脚本,实现整个工厂数据库管理系统的运营,实现模拟工厂生产过程中对零件、产品、库存环节的管理。编写此文档更有利于加深开发人员掌握数据库的基础知识、基本理论、实现原理和技术。本文档的读者对象是需求人员、系统设计人员、开发人员、测试人员。 1.2参考资料 资料名称 作者 文件编号、版本 《数据库系统概论》 王珊、萨师煊 2006年5月第4版 《SQL Server数据库管理及应用教程》 雷超阳、陈献辉 2011年7月 《数据库系统原理》课程实验指导书 徐长梅、王祥 2006年2月 需求规约 业务描述 数据库系统创建的背景 通过学习了数据库系统原理这门课程,掌握并运用数据库相关的知识为工厂设计一个合理的数据库管理系统,实现工厂数据库管理系统实现对零件、产 品的生产和运作。 数据库系统要完成的业务及工作内容 依据工厂数据库管理系统实现模拟工厂生产过程中对零件、产品、库存等的管理,完成零件、产品的入库、出库等工作,并对数据库各表能够进行增、删、改、查工作。 揭示该数据库的资源需求和设计约束 工厂建立的数据库管理系统存储以下信息: 工厂包括厂名和厂长名。 一个厂内有多个车间,每个车间有车间号、车间主任、地址和电话。 一个车间有多个工人,每个工人有职工号、姓名、年龄、性别和工种。 一个车间生产多种产品,产品有产品号、产品名称,质量,价格。 一个车间生产多种零件,一个零件也可能为多个车间制造,零件有零件号、重量,质量和价格。 一个产品由多种零件组成,一种零件也可装配出多种产品。 产品与零件均存放在仓库中。 厂内有多个仓库,仓库有仓库号、仓库主任,库存容量和电话。 根据所学数据库知识对工厂数据库管理系统各个阶段的资源需求进行分析: 需求分析阶段:定义数据项的含义和取值; 概念结构设计阶段:画出实体模型E-R图; 逻辑结构设计阶段:将实体模型转化为关系模型、给出每个关系的主关键字和函数依赖集、分析你所设计的关系数据库模式是否属于3NF; 物理设计阶段:确定所有字段的名称、类型、宽度、小数位数及完整性约束、 确定数据库及表的名称及其组成 、确定索引文件和索引关键字; 数据库安全及维护设计阶段:设计一个适合的数据库安全策略(用户身份认证、访问权限、视图)、为了实现复杂的数据完整性约束,设计适当的触发器、设计一个适合的数据库备份策略; 实施阶段:要求所有操作必须在查询分析器中用SQL语句或系统存储过程完成。 需求分析 分析该业务流程的内在联系 根据工厂管理信息系统的需求,可以设计成以下数据结构: 工厂:存放厂名,厂长名; 车间:存放车间号,车间主任,地址,电话,厂名; 工人:存放职工号,姓名,年龄,性别,工种,车间号; 仓库:存放仓库号,仓库主任,电话,库存容量,厂名; 产品:存放产品号,产品名称,产品经理,价格,质量,车间号,仓库号; 零件:存放零件号,零件名称,重量,价格,质量,仓库号; 实体间的联系如下: 工厂与车间之间是属于关系,关系类型为1:m; 工厂与仓库之间是管理关系,关系类型为1:m; 车间与工人之间是管理关系,关系类型为1:m; 车间与产品之间是供应关系,关系类型为1:m; 车间与零件之间是生产关系,关系类型为m:n; 产品与零件之间是构成关系,关系类型为m:n; 产品与仓库之间是存放关系,关系类型为1:m; 零件与仓库是存放关系,关系类型为1:m。 实体间关系表如下: 生产关系 属性:车间号,零件号。 主键:车间号,零件号 产品_零件的构成关系 属性:产品号,零件号。 主键:产品号,零件号。 (2)建表、定义数据项的含义和取值。 表2.1工厂 数据项名 数据类型 长度 能否为空 键/索引 厂名 varchar 10 否 主键 厂长名 varchar 10 是 表2.2车间 数据项名 数据类型 长度 能否为空 键/索引 车间号 varchar 10 否 主键 车间主任 varchar 10 是 地址 varchar 10 是 电话 varchar 11 是 厂名 varchar 10 否 外码 表2.3工人 数据项名 数据类型 长度 能否为空 键/索引 职工号 varchar 10 否 主键 姓名 varchar 10 是 年龄 int是 性别 varchar 2 是 工种 varchar 10 是 车间号 varchar 10 否 外码 表2.4产品 数据项名 数据类型 长度 能否为空 键/索引 产品号 varchar 10 否 主键 产品名称 varchar 20 是 产品经理 varchar 10 是 价格 float是 质量 int否 仓库号 varchar 10 否 外码 车间号 varchar 10 否 外码 表2.5零件 数据项名 数据类型 长度 能否为空 键/索引 零件号 varchar 10 否 主键 零件名称 varchar 10 是 重量 float是 价格 float是 质量 int是 仓库号 varchar 10 否 外码 表2.6仓库 数据项名 数据类型 长度 能否为空 键/索引 仓库号 varchar 10 否 主键 仓库主任 varchar 20 否 电话 varchar 11 否 厂名 varchar 10 否 外码 库存容量 int否 表2.7生产 数据项名 数据类型 长度 能否为空 键/索引 车间号 varchar 10 否 主键,外码 零件号 varchar 10 否 主键,外码 表2.8产品_零件 数据项名 数据类型 长度 能否为空 键/索引 产品号 varchar 10 否 主键,外码 零件号 varchar 10 否 主键,外码 数据库环境说明 表3.1 数据库环境 数据库实例 数据库系统 数据库部署环境 数据库设计工具 数据库 存放位置 说明 工厂数据库管理系统 SQL server 2008 内存:4GB 硬盘:500G 系统:Windows Xp Microsoft Visual Studio 2008和SQL server 2008 D盘根目录 增加、删除、修改、查询工厂、工人、车间等基本信息,统计库 存容量,模拟生产过程。 数据库的规则命名 4.1数据库对象命名规则 表4.1数据库对对象命名规则 数据库对象 命名规则 表 Tbl_功能描述字符串 视图 View_功能描述字符串 索引 Index_功能描述字符串 存储过程 Procedure_功能描述字符串 触发器 Trigger_功能描述字符串 4.2数据项命名规则 表4.2数据库对对象命名规则 数据名称 数据类型 长度范围 完整性约束 厂名 varchar 10 主码,外码 厂长名 varchar 10 车间号 varchar 10 主码,外码 车间主任 varchar 10 地址 varchar 10 电话 varchar 11 产品号 varchar 10 主码,外码 产品名称 varchar 20 产品经理 varchar 10 价格 float 质量 int 职工号 varchar 10 主码 姓名 varchar 10 年龄 int 性别 varchar 2 Check性别 IN ‘男’, ‘女’ 工种 varchar 20 零件号 varchar 10 主码,外码 零件名称 varchar 20 重量 float 质量 int 价格 float 仓库号 varchar 10 主码,外码 仓库主任 varchar 10 电话 varchar 11 库存容量 intCheck库存容量0 and 库存容量100 概念结构设计阶段m1 1 m 1m 1 m m m n n 1m 1 m 图5.1E-R图 逻辑设计 6.1将实体模型转化为关系模型 数据库系统的关系模型:主码:;外码: 工厂(厂名,厂长名); 车间(车间号,车间主任,地址,电话,厂名); 工人(职工号,姓名,年龄,性别,工种,车间号); 产品(产品号,产品名称,产品经理,价格,质量,车间号,仓库号); 零件(零件号,零件名称,重量,质量,价格,仓库号); 产品_零件(产品号,零件号);外键:产品号,零件号; 仓库(仓库号,仓库主任,电话,库存容量,厂名); 生产(零件号,车间号);外键:零件号,车间号; 6.2函数依赖集 工厂存在的函数依赖有: 厂名F厂长名,工厂的函数依赖符合三范式; 车间存在的函数依赖有: 车间号 F 车间主任,车间号 F 地址,车间号 F 电话,车间号 F 厂 名, 车间的函数依赖符合三范式; 工人存在的函数依赖有: 职工号F 姓名,职工号 F 年龄,职工号 F 性别,职工号 F 工种, 职工号F 车间号,工人的函数依赖符合三范式; 产品存在的函数依赖有: 产品号F 产品名称,产品号 F 产品经理,产品号F 价格, 产品号F 质量,产品号 F 车间号,产品号 F 仓库号,产品的函数依赖 符合三范式; 零件存在的函数依赖有: 零件号F 重量,零件号 F 价格,零件号F 质量,零件号 F 仓库号 ,零件的函数依赖符合三范式; 仓库存在的函数依赖有: 仓库号F 仓库主任,仓库号F 电话,仓库号 F 厂名, 仓库号 F 库存容量,仓库的函数依赖符合三范式; 6.3关系模式图 图6.1关系模式图 物理设计阶段 7.1表汇总 表7.1表汇总 表名 功能说明 工厂 存储工厂信息 车间 存储车间信息 工人 存储工人信息 产品 存储产品信息 零件 存储零件信息 仓库 存储仓库信息 生产 存储生产信息 产品_零件 存储产品,零件信息 7.2表组成 表7.2工厂 表名 工厂 数据库用户 主键 厂名 其他排序字段 厂长名 索引字段 厂名 序号 字段名称 数据类型(精度范围) 允许为空Y/N 唯一Y/N 区 别度 默认值 约束条件/说明 1 厂名 Varchar10 N Y 主码 2 厂长名 Varchar10 Y N sql脚本 create table 工厂 厂名 varchar10 primary key not null, 厂长名 varchar10 , ; 表7.3车间 表名 车间 数据库用户 主键 车间号 其他排序字段 车间主任,地址,电话,厂名 索引字段 车间号,厂名 序号 字段名称 数据类型(精度范围) 允许为空Y/N 唯一Y/N 区 别度 默认值 约束条件/说明 1 车间号 Varchar10 N Y 主码 2 车间主任 Varchar10 Y N 3 地址 Varchar10 Y N 4 电话 Varchar11 Y N 5 厂名 Varchar10 N N 外码 sql脚本 create table 车间 车间号 varchar10 primary key not null, 车间主任 varchar10 , 地址 varchar10 , 电话 varchar11 , 厂名 varchar10 not null, foreign key厂名 references 工厂厂名 ; 表7.4工人 表名 工人 数据库用户 主键 职工号 其他排序字段 姓名,年龄,性别,工种,车间号 索引字段 职工号,车间号 序号 字段名称 数据类型(精度范围) 允许为空Y/N 唯一Y/N 区 别度 默认值 约束条件/说明 1 职工号 Varchar10 N Y 主码 2 姓名 Varchar10 Y N 3 年龄 Int Y N 4 性别 Varchar2 Y N check 性别'男' or 性别'女' 5 工种 Varchar10 Y N 6 车间号 Varchar10 N N 外码 sql脚本 create table 工人 职工号 varchar10 primary key not null, 姓名 varchar10 , 年龄 int, 性别 varchar2 check 性别'男' or 性别'女', 工种 varchar10, 车间号 varchar10 not null, foreign key车间号 references 车间车间号 ; 表7.5产品 表名 产品 数据库用户 主键 产品号 其他排序字段 产品名称,产品经理,价格,质量,车间号,仓库号 索引字段 产品号,车间号,仓库号 序号 字段名称 数据类型(精度范围) 允许为空Y/N 唯一Y/N 区 别度 默认值 约束条件/说明 1 产品号 varchar10 N Y 主码 2 产品名称 Varchar20 Y N 3 产品经理 varchar10 Y N 4 价格 float Y N 5 质量 int Y N 6 车间号 Varchar10 N N 外码 7 仓库号 Varchar10 N N 外码sql脚本 create table 产品 产品号 varchar10 primary key not null, 产品名称 varchar20 , 产品经理 varchar10 , 价格 float, 质量 int , 车间号 varchar10 not null, 仓库号 varchar10 not null, foreign key车间号 references 车间车间号, foreign key仓库号 references 仓库仓库号 ; 表7.6零件 表名 零件 数据库用户 主键 零件号 其他排序字段 重量,质量,价格,仓库号 索引字段 零件号,仓库号 序号 字段名称 数据类型(精度范围) 允许为空Y/N 唯一Y/N 区 别度 默认值 约束条件/说明 1 零件号 varchar10 N Y 主码 2 零件名称 varchar10 Y N 3 重量 float Y N 4 质量 int Y 5 价格 int Y N 6 仓库号 Varchar10 N N 外码 sql脚本 create table 零件 零件号 varchar10 primary key not null, 零件名称 varchar10 , 重量 float, 质量 int , 价格float, 仓库号 varchar10 not null, foreign key仓库号 references 仓库仓库号 ; 表7.7仓库 表名 仓库 数据库用户 主键 仓库号 其他排序字段 仓库主任,电话,厂名,库存容量 索引字段 仓库号,厂名 序号 字段名称 数据类型(精度范围) 允许为空Y/N 唯一Y/N 区 别度 默认值 约束条件/说明 1 仓库号 Varchar10 N Y 主码 2 仓库主任 Varchar10 Y N 3 电话 Varchar11 Y N 4 库存容量 int Y N 5 厂名 Varchar10 N N 外码 sql脚本 create table 仓库 仓库号 varchar10 primary key not null, 仓库主任 varchar10 , 电话varchar11, 库存容量 int check库存容量0 and 库存容量100, 厂名varchar10 not null, foreign key厂名 references 工厂厂名 ; 表7.8生产 表名 生产 数据库用户 主键 车间号,零件号 其他排序字段 索引字段 车间号,零件号 序号 字段名称 数据类型(精度范围) 允许为空Y/N 唯一Y/N 区 别度 默认值 约束条件/说明 1 车间号 Varchar10 N Y 主码,外码 2 零件号 Varchar10 N Y 主码,外码 sql脚本 create table 生产 零件号 varchar10 not null, 车间号 varchar10 not null, primary key 零件号,车间号, foreign key零件号 references 零件零件号, foreign key车间号 references 车间车间号 ; 表7.9产品_零件 表名 产品_零件 数据库用户 主键 产品号,零件号 其他排序字段 索引字段 产品号,零件号 序号 字段名称 数据类型(精度范围) 允许为空Y/N 唯一Y/N 区 别度 默认值 约束条件/说明 1 产品号 Varchar10 N Y 主码,外码 2 零件号 Varchar10 N Y 主码,外码 sql脚本 create table 产品_零件 产品号 varchar10 not null, 零件号 varchar10 not null, primary key 产品号,零件号, foreign key产品号 references 产品号产品号, foreign key零件号 references 零件零件号 ; 7.3视图的设计 --建立不同仓库的库存容量视图 create view v_store as select 仓库号,库存容量 from 仓库; 图7.1v_store运行结果 ---创建工人表中不同性别的职工人数 create view v_renshu as select 职工号,count* as 职工人数 from 工人 where 性别'男' group by 职工号; 图7.2 v_renshu运行结果 --定义一个反映工人出生年份的视图 create view v_worker职工号,姓名,出生年月 as select 职工号,姓名,2013-年龄 from 工人 图7.3 v_worker运行结果 --统计不同工种的员工人数 create view count_员工 as select 工种,COUNT* as 人数 from 工人 group by 工种 图7.4count_员工运行结果 --建立统计不同零件仓库使用情况 create view V_use as select 仓库号,零件名称, count* as 零件数量 from 零件 group by 仓 库号,零件名称; 图7.5V_use运行结果 --建立查询仓库产品质量1的视图 create view v_produce as select 仓库号,产品名称,count* as 产品数量 from 产品 where 质量 1 group by 仓库号,产品名称 图7.6v_produce运行结果 --建立生产的车间数量视图 create view v_shop as select 车间号,count* as 数量 from 生产 group by 车间号图7.7v_shop 运行结果 --统计产品跟零件的库存数量定义为一个视图 create view v_CNO as select 产品数量,零件数量 from select COUNT* as 产品数量 from 产 品 as 产品表,select COUNT* as 零件数量 from 零件 as 零件表 ; 图7.8v_CNO运行结果 7.4存储过程、函数及触发器的设计 运用存储过程 ----产品入库并插入数据 create Procedure proc_产品 @产品号 varchar10 , @产品名称 varchar20 , @产品经理 varchar10 , @价格 float , @质量 int , @车间号 varchar10 , @仓库号 varchar10 as begin if @质量 3 begininsert into 产品 values@产品号,@产品名称,@产品经 理,@价格,@质量,@车间号,@仓库号; print '成功入库'; end else begin print '检验不合格,不能入库'; end End exec proc_产品 'P001','杨梅','周展',3, 1,'C001','S001'; exec proc_产品 'P002','奥利奥','周展',10, 1,'C002','S001'; exec proc_产品 'P003','牛奶糖','周展',5, 2,'C003','S001'; exec proc_产品 'P004','金针菇','周展',4.5, 1,'C002','S001'; 执行语句:exec proc_产品 'P005','红枣','周展',3.5, 3,'C003','S002';--检验合格 执行前: 图7.9运行结果 执行后: 图7.10运行结果 成功入库产品:P005 图7.11运行结果 执行语句:exec proc_产品 'P006','面包','周展',4,5,'C004','S004'?检 验不合格图7.12运行结果 --选择仓库 --库存容量100,则选择仓库中库存容量数最少的仓库 create procedure proc_选择仓库 @store varchar20 output--@store是 仓库号 as begin if select 库存容量 from 仓库 where @store仓库号100 begin set @store select top 1 仓库号 from 仓库 order by 库存容量 ; end End 执行语句:exec proc_selecrStore 'S001' 图7.13运行结果 --产品出库存储过程 create Procedure proc_产品出库 @产品号 varchar10 as begin begin delete from 产品 where @产品号产品号; print @产品号 + '产品出库'; endEnd 执行语句:exec proc_产品出库 'P002'--检验 执行前产品与库存容量情况: 图7.14运行结果 执行后产品与库存容量情况: 图7.15运行结果 ----检验零件入库并插入数据存储过程 create Procedure proc_零件 @零件号 varchar10 , @零件名称 varchar10 , @重量 float, @价格 float , @质量 int, @仓库号 varchar10 as begin if @质量 3 begin insert into 零件 values@零件号,@零件名称,@重量,@ 价格,@质量,@仓库号; print '成功入库'; end else begin print '检验不合格,不能入库'; end end exec proc_零件 'L001','塑料袋',0.1,0.2,3,'S001'; exec proc_零件 'L002','包装盒',0.3,1.0,2,'S002'; exec proc_零件 'L003','罐头',0.5,2.0,1,'S002'; exec proc_零件 'L004','塑料袋',0.1,0.2,2,'S001'; 执行语句:exec proc_零件 'L005','纸质包装袋',0.2,2.5,1,'S002'; 执行前: 图7.16运行结果 执行后: 图7.17运行结果 图7.18运行结果 执行语句:exec proc_零件 'L005','罐头',1,2.5,4,'S003';--零件不合格 图7.19运行结果 -----零件出库 create Procedure proc_零件出库@零件号 varchar10 as beginbegin delete from 零件 where @零件号零件号; print @零件号 + '零件出库'; endend 执行语句:exec proc_零件出库 'L002'--检验 零件出库前:(零件、仓库情况) 图7.20运行结果 零件出库后:(零件、仓库情况) 图7.21运行结果 图7.22运行结果 ---通过零件的名称查询该零件的库存数量(存储过程) create procedure proc_productname@零件名称 varchar10 as select 零件号,count* as 零件数量 from 零件,仓库 where 仓库.仓库号零件.仓库号 and @零件名称零件名称 group by 零件号 执行语句:Exec proc_productname '塑料袋' 图7.23运行结果 ----通过职工号查询职工的信息(存储过程) create procedure pro_worker@职工号 varchar10 as select 姓名,年龄,性别,工种,车间号 from 工人 where @职工号职工号 执行语句: exec pro_worker 'W102' 图7.24运行结果 ------通过产品名称查询该产品所用的零件与零件数量(存储过程) create procedure proc_PLno@产品名称 varchar10 as select 产品.产品号,零件号,count* as 零件数量 from 产品,产品_零件 where 产品.产品号产品_零件.产品号 and @产品名称产品名称 group by 零件号,产品.产品号; 执行语句:exec proc_PLno 奥利奥 图7.25运行结果 运用函数 --用函数统计仓库中零件数量 declare @count int select @countcount* from 零件 where 零件.仓库号'S001' print @count 图7.26运行结果 ----用函数统计库存产品数量 declare @count int select @countcount* from 产品 ,仓库 where 仓库.仓库号产品.仓库号 and 产品.仓库号'S001' and 产品.质量2 print @count 图7.27运行结果 运用触发器 ----产品入库+1,则库存容量+1触发器 create trigger tri_产品入库 on 产品for insert asupdate 仓库 set 库存容量 库存容量 + select COUNT* from INSERTED where 仓库. 仓库号 INSERTED.仓库号; 执行语句:exec proc_产品 ‘P007’,‘蚕豆’,‘周展’,2,3,‘C001’, ‘S001’; exec proc_产品 ‘P006’,‘花生’,‘周展’,5,2,‘C002’,‘S003’; 产品入库前: 图7.28运行结果 产品入库后: 图7.29运行结果 ----产品出库-1,库存容量-1触发器 create trigger tri_产品出库 on 产品 for delete As update 仓库 set 库存容量 库存容量-select COUNT* from deleted where 仓库. 仓库号 deleted.仓库号; 执行语句:exec proc_产品出库 'P001'--检验 产品出库前:(产品、仓库情况) 图7.30运行结果 产品出库后:(产品、仓库情况) 图7.31运行结果 图7.32运行结果 ----零件入库+1,则库存容量+1(触发器) create trigger tri_零件入库 on 零件 for insert as update 仓库 set 库存容量 库存容量 + select COUNT* from INSERTED where 仓库. 仓库号 INSERTED.仓库号; 执行语句:exec proc_零件 'L001','塑料袋',0.1,0.2,3,'S001'; exec proc_零件 'L002','包装盒',0.3,1.0,2,'S002'; 零件入库前:(零件、仓库情况) 图7.33运行结果 零件入库后:(零件、仓库情况) 图7.34运行结果图7.35运行结果 ---- 一个零件出库,零件库存容量-1(触发器) create trigger tri_零件出库 on 零件 for delete as update 仓库 set 库存容量 库存容量 - select COUNT* from deleted where 仓库. 仓库号 deleted.仓库号; 执行语句:exec proc_零件出库 'L001'--检验 零件出库前:(零件、仓库情况) 图7.36运行结果 零件出库后:(零件、仓库情况) 图7.37运行结果 图7.38运行结果 安全设计阶段 8.1防止用户直接操作数据库的方法 给工厂数据库管理系统设置用户权限: 厂长是整个数据库的管理员,对数据库中的所有数据具有增加,删除,修 改,查询。 车间主任可以管理工人,对工人可以进行修改,增加,删除,查询。 产品经理可以管理产品,对产品可以进行修改,增加,删除,查询。 仓库主任可以管理仓库,对产品跟零件可以进行修改,增加,删除,查询。 8.2用户帐号密码的加密方法 用户身份认证 --创建登录名,添加角色 exec sp_addlogin 'panqiufang','114397','factory' exec sp_addsrvrolemember 'panqiufang','sysadmin' --在数据库中添加新用户 use factory exec sp_grantdbaccess 'panqiufang','qiuqiu' 8.3角色与权限 表8.1角色与权限 角色 可以访问的表与列 操作权限 厂长 工厂 增,删,改,查 车间 增,删,改,查 工人 增,删,改,查 产品 增,删,改,查 零件 增,删,改,查 仓库 增,删,改,查 生产 增,删,改,查 产品_零件 增,删,改,查 车间主任 车间 增,删,改,查 工人 增,删,改,查 工厂 查 产品经理 车间 查 产品 增,删,改,查 零件 增,删,改,查 工厂 查 生产 查 仓库 查 产品_零件 查 仓库主任 仓库 查 工厂 查 产品 增,删,改,查 零件 增,删,改,查 车间 查 创建角色并授予相应的权限: --创建厂长角色 create role 厂长 --授予权限 grant select,delete,update,insert on 工厂 to 厂长; grant select,delete,update,insert on 车间 to 厂长; grant select,delete,update,insert on 产品 to 厂长; grant select,delete,update,insert on 零件 to 厂长; grant select,delete,update,insert on 工人 to 厂长; grant select,delete,update,insert on 仓库 to 厂长; grant select,delete,update,insert on 生产 to 厂长; grant select,delete,update,insert on 产品_零件 to 厂长; --创建车间主任角色 create role 车间主任 --授予权限 grant select,delete,update,insert on 车间 to 车间主任; grant select,delete,update,insert on 工人 to 车间主任; grant select on 工厂 to 车间主任; --授予查看视图权限 grant select on v_renshu to 车间主任; grant select on v_worker to 车间主任; grant select on count_员工 to 车间主任; --创建产品经理角色 create role 产品经理 --授予权限 grant select on 车间 to 产品经理; grant select,delete,update,insert on 产品 to 产品经理; grant select,delete,update,insert on 零件 to 产品经理; grant select on 工厂 to 产品经理; grant select on 生产 to 产品经理; grant select on 仓库 to 产品经理; grant select on 产品_零件 to 产品经理; --授予查看视图权限 grant select on v_shop to 产品经理; grant select on v_CNO to 产品经理; --创建仓库主任角色 create role 仓库主任 --授予权限 grant select on 仓库 to 仓库主任; grant select on 工厂 to 仓库主任; grant select,delete,update,insert on 产品 to 仓库主任; grant select,delete,update,insert on 零件 to 仓库主任; grant select on 车间 to 仓库主任; --授予查看视图权限 grant select on v_store to 仓库主任; grant select on V_use to 仓库主任; grant select on v_produce to 仓库主任; 总结 关于数据库课程设计,总的来说,受益匪浅。此次课程设计主要是通过运用所学的数据库系统理论这门课程的相关知识设计工厂数据库管理系统,模拟工厂的生产过程。在这些天的课程设计中,我觉得自己还是学到了很多东西,包括建表、建存储过程、建触发器、查询语句、插入数据、更新数据以及数据库安全性等内容都有了更深层次的理解。 在这次课程设计过程中,我对建立基本表、视图、存储过程等,都比以前更加的熟练。在解决课程设计遇到的各种问题的过程中,学到了很多新的知识。比如在删除和更新数据时,由于外键约束的存在,经常会出现错误,这就要注意各 个表导入的顺序问题。一般是先导入主键表,后导入外键表,这样可以避免很多的错误。另外我还充分体会了从事软件开发工作需要特别严谨认真的态度和作风,一点都马虎不得。每一个细微的细节都必须十分的注意,如果不认真思考决策,就会出现或大或小的错误,如果早期的错误隐藏下来,对后面的工作影响就会很大,甚至有时要推倒很多前面做的工作重来。 SQL Sever数据库课程设计主要可分两大块,一块是开发,一块是管理。开发主要是编写存储过程、触发器等,有些类似于程序员,开发还需要有较强的逻辑思维和创造能力;管理则需要对SQL Sever数据库的原理有深刻的认识,有全局操纵的能力和紧密的思维,较大,因为一个小的失误就会弄掉整个数据库,相对前者来说,后者更看重经验。 数据库的课程设计学习使我对数据库的有了新的进步,顺利完成了整个工厂数据库管理系统的设计,收获颇多。同时,也便于我在以后学习或认识更多的内容能有新的方法和思维,也能更加有效和快速的去消化吸收新的东西。希望在今后中,SQL Server能给我更多帮助。最后,还要感谢数据库老师对我的帮助,给我提供了许多宝贵的意见。