数据库技术基础

实验七 数据库编程

• 实验目的
  1. 理解并运用数据库设计的常见步骤来设计满足给定需求的概念模型（ER模型）和关系数据模型。
  2. 结合有关的指南对模型进行优化处理。
  3. 培养学生的系统思维，提升解决复杂工程问题所需的业务问题分析和系统设计能力。
• 实验内容

每4人分成1个小组，以小组内成员较为熟悉的业务场景为题目，进行数据库系统开发。实验报告要求至少包含需求分析文档、概念设计文档、逻辑设计文档和物理数据库设计文档。按照数据库设计步骤，分别完成如下内容：

1. 1. 需求分析：理解具体问题场景下的用户需求，分析并详细列出需求“源数据”表。
   2. 概念设计（ER模型），给出满足需求的最终全局ER图，要求模型尽量精简，消除不必要的冗余，并给出理由或说明。
   3. 逻辑设计（关系模型），把ER模型转换成适当的关系模式，并进行适当地规范化，设计相关完整性约束。
   4. 物理设计，结合具体的DBMS系统，实现一个完整的数据库系统涉及的各个要素；
   5. 结合具体的高级语言，并运用所学软件，参考教材第八章内容，练习试做一简单的数据库应用系统，进行前台应用开发与后台数据库的连接（可选）。

要求：每4人分成1个小组，每组交一份实验报告，并在实验报告中列出该组的成员及任务分工。

• 实验要求

1. 以小组内成员较为熟悉的业务场景为题目，数据库设计场景自定，包含4个及以上实体，SCT数据库除外。
2. 每4人分成1个小组，每组交一份实验报告，并在实验报告中列出该组的成员及任务分工。
3. 每位成员认真总结自己实验中遇到的问题、解决方法和心得体会，展示到第四部分心得体会中。

• 实验过程及结果

目  录

一．需求规约.........................................3

1.1   业务描述............................................3

1.2   需求分析............................................3

二．概念设计.........................................5

三．逻辑设计.........................................6

四．物理设计.........................................8

• 需求规约

1.1业务描述

(1）数据库系统创建的背景

运用数据库相关信息来实现工人管理系统的生产和运作。

(2)数据库系统要完成的业务流程及工作内容

根据工人管理系统模拟生产过程，实现对原材料、产品检测、库存的管理

(4)揭示该数据库的资源需求和设计约束

根据工人的各个生产所需的阶段，来建立不同的表，并将这些表连接起来，画出实体模型图，确定所有字段的名称、类型及完整性约束，并将实体模型转化为关系模型，并设计关键字，以及设计适当的触发器

1.2需求分析

(1）分析该业务流程的内在联系

分析业务流程后得出:

工厂与车间为一对多关系，

车间与工人为一对多关系，

车间与产品为一对多关系，

仓库与产品为一对多关系,

工厂与仓库为一对多关系

(2)对象处理如下:

对象处理:

工厂信息:厂名、厂长名。

车间信息:车间号、车间主任姓名、地址和电话。

职工信息:职工号、姓名、年龄、性别和工种。

产品信息:产品号、产品名称和价格。

仓库信息:仓库号、仓库主任姓名和电话。

(3）实现功能与分析如下:

实现功能:实现功能其中需要注意的是，安全性需要根据其需要来给予其一定程度的安全性，再通过用户授权机制，通过用户登陆来识别用户级别，再根据这个级别来分配用户权限，从而实现更高层次的安全保密功能。完整性要求描述各信息间的关联关系和制约关系，需要根据各个值的实际情况来分沂数据的数据范围及注意其是否为(空），根据实际需要来满足要求。

分析:如下：

 

• 概念设计（E-R全局模型）

• 逻辑设计（关系模型）

逻辑模型：

工厂（厂名，厂长名)

车间（车间号，车间主任姓名，地址，电话，厂名）

工人（职工号，姓名，年龄，性别，工种，车间号）

产品（产品号，产品名称，价格，车间号，仓库号）

仓库（仓库号，仓库主任姓名，电话，厂名）

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

工厂信息表
字段中文名	字段名	数据类型	是否为空	约束
厂名	厂名	Varchar(50)	否	Primarykey
厂长名	厂长名	Varchar(50)	否	Not null

工厂与车间一对多

车间信息表
字段中文名	字段名	数据类型	是否为空	约束
车间号	车间号	Char(10)	否	Primarykey
车间主任姓名	车间主任姓名	Varchar(50)	否	Not null
地址	地址	Varchar(50)	否	Not null
电话	电话	Char（10）	否	Not null
厂名	厂名	Varchar(50)	否	（外键）

车间与工人一对多

职工信息表
字段中文名	字段名	数据类型	是否为空	约束
职工号	职工号	Char（10）	否	Primarykey
姓名	姓名	Varchar(50)	否	Not null
年龄	年龄	Char（10）	否	Not null
工种	工种	Varchar(50)	否	Not null
车间号	车间号	Char（10）	否	（外键）

车间产品一对多，仓库产品一对多

产品信息表
字段中文名	字段名	数据类型	是否为空	约束
产品号	产品名	Char（10）	否	Primarykey
产品名	名称	Varchar(50)	否	Not null
价格	价格	Money（10）	否	Not null
车间号	车间号	Char（10）	否	（外键）
仓库号	仓库号	Char（10）	否	（外键）

工厂与仓库一对多

仓库信息表
字段中文名	字段名	数据类型	是否为空	约束
仓库号	仓库号	Char（10）	否	Primarykey
仓库主任姓名	仓库主任姓名	Varchar(50)	否	Not null
电话	电话	Char（10）	否	Not null
厂名	厂名	Varchar(50)	否	（外键）

四．物理设计

建表

4.1创建数据库

create database 工厂管理系统;

 

4.2创建数据表

创建工厂信息表

create table 工厂信息表

(

厂名  varchar(50) primary key,

厂长名 varchar(50) not NULL,

)

 

创建车间信息表

create table 车间信息表

(

车间号 char(10) primary key,

车间主任姓名 varchar(50) not NULL,

地址 varchar(50) not NULL,

电话 char(10)  not NULL,

厂名 varchar(50) not NULL,

foreign key(厂名) references 工厂信息表(厂名),

)

 

创建职工信息表

create table 职工信息表

(

职工号 char(10) primary key,

姓名 varchar(50) not NULL,

年龄 char(10) not NULL,

工种 varchar(50) not NULL,

车间号 char(10) not NULL,

foreign key(车间号) references 车间信息表(车间号),

)

 

仓库信息表

create table 仓库信息表

(

仓库号 char(10) primary key,

仓库主任名 varchar(50) NOT NULL,

电话 char(10) NOT NULL,

厂名 varchar(50) NOT NULL,

foreign key (厂名) references 工厂信息表(厂名),

)

 

产品信息表

create table 产品信息表

(

产品号 char(10) primary key,

产品名 varchar(50) NOT NULL,

价格 money NOT NULL,

车间号 char(10) NOT NULL,

仓库号 char(10) NOT NULL,

foreign key (车间号) references 车间信息表(车间号),

foreign key (仓库号) references 仓库信息表(仓库号),

)

 

4.3插入数据

4.4查询数据

1：查询工厂信息表所有信息

select * from 工厂信息表

 

2：创建一个存储过程：工人信息表_save

当查询工人信息表中职工号为：“1”的员工信息

①存储过程的创建

USE 工厂管理系统

GO

if exists(select * from sys.all_parameters where name='工人信息表_save')

drop procedure 工人信息表_save

go

create procedure 工人信息表_save

as

select * from 职工信息表

where 职工号=1

Go

 

②执行存储过程

exec 工人信息表_save

 

3：查询仓库信息表中厂名为“富士康工厂”的所有仓库号

select 仓库号

from 仓库信息表

where 厂名='富士康工厂';

 

4：查询产品信息表中价格大于等于“100”的产品名称

select 产品名

from 产品信息表

where 价格>=100

 

4.5数据修改

1：创建触发器

当在产品信息表中来触发更新产品价格并显示更新后的表的全部内容。

use 工厂管理系统

go

create trigger 产品信息表_UPDATE

on 产品信息表

with encryption      /*采取加密方式*/

after update

as

begin

select*from 产品信息表

end;

update 产品信息表 set 价格= 100

where 价格=10;

 

 

4.6数据增加

insert into 工厂信息表 values('卫龙工厂','宋大哥');

 

 

 

4.7数据删除

1：创建触发器

当在工人信息表中删除工人职工号，删除的记录会被添加到deleted表中，原来表的相应记录被删除，所以在deleted表中查看删除的记录。

use 工厂管理系统

go

create trigger 职工信息表_delect

on 职工信息表

with encryption      /*采取加密方式*/

after delete 

as

begin

select 职工号 as 已删除的职工号,姓名,年龄,工种,车间号

from deleted

end;

delete from 职工信息表 where 职工号=10;

 

2：创建触发器

当在产品信息表中删除产品号时，删除的记录会被添加到deleted表中，原来表的相应记录被删除，所以在deleted表中查看删除的记录。

use 工厂管理系统

go

create trigger 产品信息表_delect

on 产品信息表

with encryption      /*采取加密方式*/

after delete 

as

begin

select 产品号 as 已删除的产品号,产品名,价格,车间号,仓库号

from deleted

end;

delete from 产品信息表 where 产品号=109;

 

4.7为数据创建视图

为仓库信息表创建视图：warehouse_view

create view warehouse_view

as

 

• 实验中的问题及心得

数据库的安全性需要根据其需要来给予其一定程度的安全性，再通过用户授权机制，通过用户登陆来识别用户级别，再根据这个级别来分配用户权限，从而实现更高层次的安全保密功能。完整性要求描述各信息间的关联关系和制约关系，需要根据各个值的实际情况来分沂数据的数据范围及注意其是否为(空），根据实际需要来满足要求。

另外由于思维上的漏洞，考虑不够周全导致数据库中后期处理和查询带来--些不便。设计过程中我也遇到了一些问题，例如框架搭建不完善，进行了多次修改，而且再向其他同学请教的过程中我收获了很多。

(1)在设计表时应注意字符的使用，尤其是标点符号，很容易搞错。

(2)正确定义数据类型，字符型要加单引号。

总的来讲，对课本上的主要知识进行了汇总和运用，对数据库的基础知识进一步掌握，达到了预期的效果。通过这次的数据库课程设计锻炼了我们的操作能力和团结协作能力，增强团队合作意识。让我们从实际上去分析一件事比较全面和对数据库设计有了更深的了解。

select*from 仓库信息表;