SQLserver数据库专业课程设计范例.doc

1 概述
1.1课题介绍
书店书目书种繁多，起源多样，购置者众多，图书信息、供给商信息、用户信息、销售信息庞大，不易管理。所以，很有必需创建一个小型书店管理系统，方便于书店对图书管理。
1.2设计目标
应用对数据库系统原理理论学习，经过上机实践方法将理论知识和实践愈加好结合起来，巩固所学知识。
数据库应用 课程实践：实践和巩固在课堂教学中学习相关知识，熟练掌握对于给定结构数据库创建、基础操作、程序系统建立和调试和系统评价。
数据库原理 软件设计实践：实践和巩固在课堂教学中学习相关关系数据库原理相关知识和数据库系统建立方法，熟练掌握对于给定实际问题，为了建立一个关系数据库信息管理系统，必需得经过系统调研、需求分析、概念设计、逻辑设计、物理设计、系统调试、维护和系统评价通常过程，为毕业设计打下基础。
1.3设计内容
利用基于E-R 模型数据库设计方法和关系规范化理论做指导完成从系统分析到设计直至系统最终实现，开发小型书店管理系统，完成小型书店管理系统全部功效。
首先做好需求分析，并完成数据流图和数据字典。
其次做概念分析，利用实体联络方法将需求分析用户需求抽象为信息结构，得到E-R 图。
然后就是逻辑结构设计，将E-R 图转换为计算机系统所支持逻辑模型

2 需求分析
2.1功效分析
首先，建立部分基础表（尽可能满足3N），对大部分基础信息组合、存放；其次经过建立视图实现对冗余数据有必需保留（查询并计算基础表属性得到新作为视图属性）并实现对以下基础信息显示。
图书信息：图书名称、订购数量、订购时间、订购单价、金额、出版社名称、作者名称；供给商名称等；
供给商信息：供给商名称、地址、电话，联络人；
用户信息：用户编号、名称、年纪、性别、累计购书金额等；
销售信息：时间、销售名称、数量、销售单价、用户编号、用户名称、金额等。
在此基础上进行以下目标查询，因为有些查询常见且较复杂，为了简化其应用，所以将它们定义为存放过程。
查询当月书店销售金额、营业金额；（存放过程）
查询某种图书库存数量；（存放过程）
查询当月销量最好图书信息；（存放过程）
按供给商名称查询订购信息；（一般查询）
查询购置次数超出3次用户信息。（一般查询）
接着依据需要建立触发器、存放过程、索引，实现对数据库优化。最终，进行过程功效验证。许可含有权限者对数据库进行数据更新和查询等正当操作。
数据流图
用步骤图来描述处理过程和数据关系
数据流图以下：

图2,1 数据流图
数据字典
用数据字典来描述数据
数据字典以下：
数据项：
表2.1 数据字典
数据
项名
含义
说明
别名
数据
类型
长度
取值范围
取值含义
和其它数据项
逻辑关系
图书编号
唯一标识
每本书

char
8
00000000~
99999999
依据入库时间
前后编号
有时和客
户编号作
为主码
图书名称
唯一标识
每种书
书名
varchar
30

有时和供给商
组成主码
作者名称
每种书
作者姓名

char
8

出版社名称
每种书
出版社名称

char
12

供给商名称
唯一标识
供给商

char
12

地址
供给商地址

varchar
50

电话
供给商电话

char
7
0000000~
9999999

联络人
经过联络人
书店和供给
商进行沟通

char
8

订购时间
每种书
订购时间

datetime

应和图书称
和供给商
名称
作为主码
直接关联
订购单价
从供给商处
进货每种书
每本价格

int

用户编号
唯一标识
用户

char
8
~
99999999
按购书前后
编号
有时和图
书编号作
为主码
用户名称
用户姓名

char
8

性别
用户性别

char
2
女或男

年纪
用户年纪

int

每十二个月全部要
增加一岁

销售时间
每本书
销售时间

datetime

和图书编号
和用户编号
作为主码
直接关联
销售单价
每本书
销售价格

int

数据结构：

数据结构名称
含义说明
组成
每种书
指含有相同书名书集
合基础信息
图书名称、作者名称、出版社名称
每本书
指图书编号唯一标识个体归属
图书编号、图书名称
供给商
供给商基础信息
供给商名称、地址、电话、联络人
供给基表
每种书在何处订购具体情况
图书名称、供给商名称、
订购单价、订购时间
用户
用户基础信息
用户编号、用户名称、性别、年纪
销售基表
每本书被谁买具体请况
图书编号、用户编号、
销售单价、销售时间

概念结构设计
概念结构设计方法
设计概念结构通常有四类方法：自顶向下，自底向上，逐步扩张和混合策略。本系统采取是自底向上方法。即首先定义全局概念结构框架，然后逐步细化。依据自顶向下地进行需求分析然后再自底向上地进行概念设计。
概念结构设计步骤
第一步：抽象数据；
第二步：设计局部E-R图；
第三步：把各分E-R图综合成总体E-R图，消除多种冲突；
第四步：对总体E-R图进行优化，消除冗余数据和冗余联络；

抽象初步数据：（此处和优化后E-R图得出实体联络属性很有可能不一样）
实体：图书、供给商、用户
联络：图书订购关系、图书销售关系

图书和供给商关系是多对一关系；
图书和用户关系是多对一关系。
属性：
图书属性：图书编号、图书名称、出版社名称、作者名称、供给商名称；
主码为图书编号

供给商属性：供给商名称、供给商地址、电话、联络人；
主码为供给商名称

用户属性：用户编号、姓名、性别、年纪；
主码为用户编号

图书订购关系属性：供给商名称、图书编号、订购时间、订购单价；
主码为供给商名称和图书编号

图书销售关系属性：用户编号、图书编号、销售时间、销售单价；
主码为用户编号和图书编号

分E-R图：
图书E-R图：

供给商E-R图：

用户E-R图：

初E-R图：

优化后总E-R图：

逻辑设计阶段
逻辑设计阶段：将系统E-R模型转换为关系模式，并对给出关系模式进行关系规范化处理（尽可能满足3NF），得到系统全部关系模式，并标明主键、外键此部分一律采取英文命名。然后进行用户外模式设计，针对中国用户，此处用汉字命名。

将E——R模式转换为以下关系模式：
1) book(#bname, writer_name, press_name);
主键：#bname
2.) everybook (# bno, #bname) ;
主键：#bno
外键：#bname
3) supplier (#supplier_name, address, telephone, contact) ;
主键：#supplier_name
4) customer (#cno, cname, sex, age);
主键：#cno
5) book_order(#bname, #supplier_name, #order_time, order_price, order_count);
主键： #bname, #supplier_name, #order_time
6) book_sell(#bno, #cno, sell_time, sell_price);
主键：#bno

发觉这六个关系模式3N全部满足。因为元组中全部是不可分割数据，且全部非主属性完全依靠其主码，且它任何一个非主属性全部不传输于任何主关键字。

用户外模式设计：
基础信息视图建立：
1. 单本销售情况（图书编号，图书名称用户编号，用户名称，销售单价，销售时间）；
2. 同种销售情况（书名，销售数量，销售金额）；
3. 用户信息（用户编号，用户名称、性别，年纪，累计购书金额）；
4. 供给商信息（供给商名称，地址，电话，联络人）；
5. 图书订购信息（图书名称，供给商名称，订购时间，订购数量，订购单价，订购金额，出版社名称，作者名称）；

物理设计阶段
物理设计阶段：为一个逻辑数据模型选择一个最适合应用环境物理结构（存放结构和存取方法），数据库物理设计通常分为两步：A、确定数据库物理结构；B、对物理结构进行时间和空间效率评价。
建立小型书店管理系统
创建数据库 book_shop_management
--创建小型书店管理系统
create database book_shop_management
on primary(
name='book_shop_management',
filename='d:\program files\microsoft sql server\mssql.1\mssql\data\
book_shop_management.mdf',
size=10mb,
maxsize=unlimited,
filegrowth=10%)

log on(
name='book_shop_management_log',
filename='d:\program files\microsoft sql server\mssql.1\mssql\data\book_shop_management_log.ldf',
size=1mb,
maxsize=5mb,
filegrowth=3%)
go
截图得：

对各个基础表建立（包含了多种完整性约束）
建立表 book
--建立表book
create table book(
#bname varchar(30) primary key,
writer_name char(8),
press_name char(12))
建立表 everybook
--建立表everybook
create table everybook(
#bno char(8) primary key,
#bname varchar(30) not null
constraint fk_everybook_bname foreign key references book(#bname))
建立表 supplier
--建立表supplier
create table supplier(
#supplier_name char(12) primary key,
address varchar(50)not null,
telephone char(7)not null,
contact char(8) not null)
建立表 customer
--建立表customer
create table customer(
#cno char(8) primary key,
cname char(8)not null,
sex char(2),
age int,
constraint ck_customer_sex check (sex in('男','女')))
建立表 book_order
--建立表book_order
create table book_order(
#bname varchar(30),
#supplier_name char(12),
#order_time datetime,
order_count int not null,
order_price int not null,
constraint pk_book_order primary key (#bname,#supplier_name,#order_time))
建立表 book_sell
--建立表book_sell
create table book_sell(
#bno char(8) primary key,
#cno char(8) not null,
sell_time datetime,
sell_price int)
建立视图
建立基础信息视图：
建立单本销售情况视图
--建立单本销售情况视图
create view view_单本销售情况
as
select book_sell.#bno as 图书编号,#bname as 图书名称,
book_sell.#cno as 用户编号,cname as 用户名称,
sell_time as 销售时间,sell_price as 销售单价
from book_sell,customer,everybook
where everybook.#bno=book_sell.#bno
and customer.#cno=book_sell.#cno
建立同种销售情况视图
--建立同种销售情况视图
create view view_同种销售情况
as
select 图书名称 as 书名,
count(图书编号) as 销售数量,
sum(销售单价) as 销售金额
from view_单本销售情况
group by 图书名称
建立用户信息视图
--建立用户信息情况
create view view_用户信息
select customer.#cno as 用户编号,
cname as 用户名称,
sex as 性别,
age as 年纪,
(select sum(sell_price) from book_sell
group by #cno) as 累计购书金额
from book_sell,customer
where book_sell.#cno=customer.#cno
建立供给商视图
--建立供给商视图
create view view_供给商信息
as
select #supplier_name as 供给商名称,
address as 地址,
telephone as 电话,
contact as 联络人
from supplier
建立订购信息视图
--建立订购信息视图
create view view_图书订购信息
as
select book_order.#bname as 图书名称,
#supplier_name as 供给商名称,
#order_time as 订购时间,
order_price as 订购单价,
order_count as 订购数量,
(order_price*order_count) as 订购金额,
press_name as 出版社名称,
writer_name as 作者名称
from book,book_order
where book.#bname=book_order.#bname
建立存放过程
某种图书库存查询存放过程
create procedure sp_某种图书库存查询
--@p1为要查询图书名称
@p1 varchar(30)
as
begin
select 订购数-销售数量as 库存
from (select sum(order_count) as 订购数,#bname as 书名
from book_order
group by #bname) as x,view_同种销售情况
where x.书名=@p1 and view_同种销售情况.书名=@p1
end
go
某段时间内销售业绩查询存放过程
----建立本月业绩查询存放过程
-----因为不知其它信息，此处将销售金额和营业金额等同
-----营业金额=营业额=成本+利润
create procedure sp_查询某段时间内业绩
--@p1为起始时间,@p2为结束时间
@p1 datetime,@p2 datetime
as
begin
select sum(销售单价) as 销售金额from view_单本销售情况
where 销售时间>=@p1 and 销售时间<=@p2
end
go
某段时间内销售最好图书信息查询存放过程
----建立某段时间内销售最好图书信息查询存放过程
create procedure sp_查询某段时间内销售最好图书信息
@p1 datetime,@p2 datetime
as
begin
select #bname as 图书名称,
writer_name as 作者名称,
press_name as 出版社名称
from (select 图书名称as 书名,
count(图书编号) as 销售数量
from view_单本销售情况
where 销售时间>=@p1
and 销售时间<=@p2
group by 图书名称) as x,book
where #bname=书名
and 销售数量>=all(select count(图书编号)
from view_单本销售情况
where 销售时间>=@p1
and 销售时间<=@p2
group by 图书名称)
end
go
建立触发器（另外5个表也建立对应曾删改触发器，不过此处从略，详情见附件源代码）
建立一个customer操作审核表，并建立相关触发器，当向customer中插入、更新或删除统计时，生成一条操作统计插入到该操作审核表中，内容包含用户编号、用户名称、操作方法（插入/更新/删除）和操作时间
----建一个操作审核表
create table customer_operate
(用户编号char(8)not null,
用户名称char(8)not null,
操作方法char(4)not null,
操作时间datetime not null)
----建立相关触发器
create trigger trigger_customer_insert
on customer
for insert
as
begin
declare @p1 char(8),@p2 char(8)
set @p1=(select #cno from inserted)
set @p2=(select cname from inserted)
print'insert:'
select *from inserted
insert into customer_operate values(@p1,@p2,'插入',getdate())
end
go

create trigger trigger_customer_delete
on customer
for delete
as
begin
declare @p1 char(8),@p2 char(8)
set @p1=(select #cno from deleted)
set @p2=(select cname from deleted)
print'deleted:'
select * from deleted
insert into customer_operate values(@p1,@p2,'删除',getdate())
end
go

create trigger trigger_customer_update
on customer
for update
as
begin
declare @p1 char(8),@p2 char(8)
set @p1=(select #cno from deleted)
set @p2=(select cname from deleted)
print 'inserted:'
select *from inserted
print 'deleted:'
select *from deleted
insert into customer_operate values (@p1,@p2,'修改',getdate())
end
go
建立索引（因为数据库比较小，没有必需建立索引）
实现数据库（有附件源代码和该数据库源文件）
过程功效验证
向基础表插入适量以下数据并显示基础表信息
everybook book
#bno
#bname
Writer_name
Press_name
何以笙箫默
顾漫
晋江出版社
原来你还在这里
辛夷坞
起点出版社
烈火如歌
明晓溪
晋江出版社
微微一笑很倾城
顾漫
阅读出版社

#bname
00000001
何以笙箫默
00000002
何以笙箫默
00000003
原来你还在这里
00000004
烈火如歌
00000005
烈火如歌
00000006
烈火如歌
00000007
微微一笑很倾城

book_sell
#bno
#cno
Sell_time
Sell_price
00000001
00000001
-6-16
21
00000003
00000002
-6-25
19
00000007
00000002
-7-9
20
00000004
00000003
-7-9
23
00000005
00000004
-7-19
18

customer
#cno
cname
sex
Age
00000001
吕鹏
女
18
00000002
孙鲁鲁
女
18
00000003
王肖雨
女
19
00000004
邵丽君
女
19
Book_order
#bname
#supplier_name
Order_time
Order_count
Order_price
何以笙箫默
大旗
-6-10
2
12
原来你还在这里
大旗
-6-20
1
10
烈火如歌
红方
-7-2
1
10
烈火如歌
长城
-7-3
2
9
微微一笑很倾城
高原
-7-3
1
11
Suppilier
#suppiler
address
telephone
contact
大旗
上海
1573234
王平
红方
上海
1734272
李景
长城
江苏
1564323
张艳丽
高原
郑州
1242356
殷风

注：此处插入数据应该book在everybook 之前插入数据（外键）
截图得：

视图显示基础信息
---用显示视图基础信息
select * from view_单本销售情况
select * from view_供给商信息
select * from view_用户信息
select * from view_同种销售信息
select * from view_图书订购信息
截图得：

实施存放过程完成目标查询
某种图书库存查询
exec sp_某种图书库存查询'烈火如歌'
本月业绩查询
exec sp_查询某段时间内业绩'-7-1','-7-31'
本月销售最好图书信息查询
exec sp_查询某段时间内销售最好图书信息'-7-1','-7-31'
截图得：

用一般查询完成目标查询（两次查询同时显示）
订购超3次用户信息查询
---订购超次用户信息查询
select * from view_用户信息
where 用户编号=any(
select #cno from book_sell
group by #cno
having count(*)>3)
按供给商名称查询订购信息
---按供给商名称查询订购信息
select * from view_图书订购信息
order by 供给商名称
截图得：

注：数据检验得，确实没有订购超3次用户。
验证触发器
--验证触发器
insert into customer values('00000005','陈利','女','21')
go
update customer
set cname='陈丽'
where cname='陈利'
go
delete from customer
where cname='陈丽'
select * from customer_operate
go
截图得：

总结
经过快要一个星期努力，最终设计出了一个基础符合设计要求数据库系统。本系统功效强大，用户界面良好，然而，学生能力有限，该系统中仍存在一些小小缺点。

学生经过一段时间努力，“功夫不负有心人”准期完成了设计任务，即使不是很完善，不过，“麻雀虽小，五脏俱全”，在这个课程设计练习中，学生学到了很多东西。

其中最关键是，在这次练习中，学生了解了数据库设计通常过程。在接收一个项目标时候，首先是进行系统分析，即分析使用情况和技术可行性，有些东西是不可行，在设计时候就要绕过去。整体框架要依据要求反复斟酌，切不可轻易做出决定，不然话，一旦犯错，再改数据库那就很麻烦了。

再有就是在创建数据库过程中要注意部分小细节了，比如说有一下几点：（1）对表建立时，应预防本许可插入反复统计（部分反复）却因为约束不妥而无法正常插入。不要想当然名一样就约束也一样；（2）每次对前文修改后全部必需保留一次，同时对全部文件全部进行修改，以保持数据和结构一致性；（3）建好基础表以后插入数据适量，以验证功效。建数据库边验证边修改；（4）对于有不确定原因应该建立存放过程进行查询。等等。

总体来说，经过这次练习，学生技术能力全部有了一个技术层面上提升了。