《SQLserver数据库》课程设计范例 D
1/34 4/27/2021
1/34 4/27/2021
4/27/2021
1/34
对大部分基本信息组合、存储；其次通过建立视图实现对冗余数据的有必要保留（查询并计算基本表属性得到新的作为视图属性）并实现对以下基本信息的显示。
图书信息：图书名称、订购数量、订购时间、订购单价、金额、出版社名称、作者名称；供应商名称等；
供应商信息：供应商名称、地址、电话，联系人； 客户信息：客户编号、名称、年龄、性别、累计购书金额等；
销售信息：时间、销售名称、数量、销售单价、客户编号、客户名称、金额等。
在此基础上进行以下目标查询，由于有些查询常用且较复杂，为了简化其应用，所以将它们定义为存储过程。
查询当月书店销售金额、营业金额；（存储过程） 查询某种图书库存数量；（存储过程） 查询当月销量最好的图书信息；（存储过程） 按供应商名称查询订购信息；（普通查询） 查询购买次数超过3次的客户信息。（普通查询） 接着根据需要建立触发器、存储过程、索引，实现对数据库的优化。最后，进行过程功能的验
1/34
4/27/2021
证。允许具有权限者对数据库进行数据更新和查询等合法操作。
数据流图
用流程图来描述处理过程与数据的关系 数据流图如下：
图书目录文件购买信息文件出版社档案文件用户购买信息用户图书需求信息处理图书供应信息处理供应商供应信息供应商顾客购书档案文件购买供应汇总文件 图2,1 数据流图
数据字典
用数据字典来描述数据 数据字典如下：
数据项：
表2.1 数据字典
数含义 别据 说明 名
数据 类型 长度 2/34
取值范围 取值与含义 其4/27/2021
项名 他数据项 的逻辑关系 有时与根据客 char 8 00000000~ 99999999 入库户时间 编先后号编号 作 为主码 图唯一书标识 编每本号 书 图唯一书书标识 名
varchar 30 3/34
有时4/27/2021
名每种称 书 与供应商 组成主码 作每种者书的 名作者称 姓名 出每种版书的 社出版名社名称 称 供应商名称
char 8 char 12 唯一标识 供应商 4/34
4/27/2021
char 12
地址 电话 供应商的地址 供应商的电话 通过联系人 char 7 0000000~ 9999999 varchar 50 联书店系与供人 应 商进行沟通 订每种购书的 时订购间 时间 订从供购应商单
char 8 应 datetime 与图书称 int 与供5/34
4/27/2021
处
价 进货每种书 的每本价格 应商 名称 作为的主码 直接关联 有时与图 书编号作 为客户编号 唯一标识 客户 char 8 按购000000000~ 书先99999999 后 编号 6/34 4/27/2021
主码 客户客户名姓名 称 性客户别 性别 char 2 女或男 每年年客户龄 年龄 都要 int 增加一岁的 销每本售书的 时销售间 时间 销每本售书的 单销售价 价格
7/34
char 8 与 datetime 图书编号 和 int 客户编4/27/2021
号 作为的主码 直接关联 数据结构：
数据结构名称 每种书 含义说明 指具有相同书名的书的集 合的基本信息 指图书编号唯一组成 图书名称、作者名称、出版社名称 图书编号、图书名称 每本书 标识的个体的归属 供应商
供应商的基本信供应商名称、地址、息 8/34
电话、联系人 4/27/2021
图书名称、供应商供应基表 每种书在何处订购的具体情况 名称、 订购单价、订购时间 客户 客户的基本信息 客户编号、客户名称、性别、年龄 图书编号、客户编销售基表
每本书被谁买的具体请况 号、 销售单价、销售时间 概念结构设计 概念结构设计的方法
设计概念结构通常有四类方法：自顶向下，自底向上，逐渐扩张以及混合策略。本系统采用的是自底向上的方法。即首先定义全局的概念结构的框架，然后逐步细化。根据自顶向下地进行需求分析然后再自底向上地进行概念设计。
9/34 4/27/2021
概念结构设计的步骤
第一步：抽象数据； 第二步：设计局部E-R图；
第三步：把各分E-R图综合成总体E-R图，消除各种冲突；
第四步：对总体E-R图进行优化，消除冗余数据和冗余联系；
抽象初步数据：（此处与优化后E-R图得出的实体联系属性很有可能不同）
实体：图书、供应商、客户
联系：图书订购关系、图书销售关系
图书与供应商的关系是多对一的关系； 图书与客户的关系是多对一的关系。 属性：
图书的属性：图书编号、图书名称、出版社名称、作者名称、供应商名称； 主码为图书编号
供应商的属性：供应商名称、供应商地址、电话、
10/34
4/27/2021
联系人；
主码为供应商名称
客户的属性：客户编号、姓名、性别、年龄； 主码为客户编号
图书订购关系属性：供应商名称、图书编号、订购时间、订购单价；
主码为供应商名称和图书编号
图书销售关系属性：客户编号、图书编号、销售时间、销售单价；
主码为客户编号和图书编号
分E-R图：
图书E-R图：
11/34 4/27/2021
图书编号图书名称图书出版社名称作者名称供应商名称 供应商E-R图：
供应商名称供应商电话供应商地址联系人 客户E-R图：
客户编号姓名性别客户年龄 12/34 4/27/2021
初E-R图：
图书编号图书名称出版社名称作者名称供应商名称供应商名称订购时间订购单价图书编号图书N图书订购N图书销售客户编号图书编号销售时间销售单价1供应商名称1客户联系人年龄性别客户编号供应商电话供应商地址
姓名 13/34 4/27/2021
优化后的总E-R图：
图书编号图书名称每本书1图书编号销售基表客户编号N客户编号客户客户名称
图书名称M作者名称每种书N出版社名称1归属销售时间图书名称订购基表订购时间销售单价供应商名称M订购单价订购数量联系人供应商年龄供应商名称性别地址电话 逻辑设计阶段
逻辑设计阶段：将系统E-R模型转换为关系模式，并对给出的关系模式进行关系规范化处理（尽量满足3NF），得到系统全部的关系模式，并标明主键、外键此部分一律采用英文命名。然后进行用户外模式的设计，针对中国用户，此处用中文命名。
14/34
4/27/2021
将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都满足。因为元组中都是不可分割的数据，且所有非主属性完全依赖其主码，且它的任何一个非主属性都不传递于任何主关键字。
15/34
4/27/2021
用户外模式设计：
基本信息视图的建立：
1. 单本销售情况（图书编号，图书名称客户编号，客户名称，销售单价，销售时间）；
2. 同种销售情况（书名，销售数量，销售金额）； 3. 客户信息（客户编号，客户名称、性别，年龄，累计购书金额）；
4. 供应商信息（供应商名称，地址，电话，联系人）；
5. 图书订购信息（图书名称，供应商名称，订购时间，订购数量，订购单价，订购金额，出版社名称，作者名称）；
物理设计阶段
物理设计阶段：为一个逻辑数据模型选取一个最适合应用环境的物理结构（存储结构与存取方法），数据库的物理设计通常分为两步：A、确定数据库的物理结构；B、对物理结构进行时间和空间效率评价。
16/34 4/27/2021
建立小型书店管理系统
创建数据库 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
截图得：
17/34 4/27/2021
对各个基本表的建立（包括了各种完整性约束）
建立表 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,
18/34
4/27/2021
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 图书名称
19/34
4/27/2021
建立客户信息视图
--建立客户信息情况
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
20/34
4/27/2021
建立存储过程
某种图书库存查询存储过程
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 书名,
21/34
4/27/2021
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:'
22/34
4/27/2021
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
建立索引（由于数据库比较小，没有必要建立索引） 实现数据库（有附件源代码和该数据库源文件）
过程功能验证
23/34 4/27/2021
向基本表插入适量如下数据并显示基本表信息
everybook #bno 00000001 00000002 00000004 00000005 00000006 book_sell #bno 00000001 00000003 00000007 00000004 00000005 customer #cno 00000001 00000002 00000003 00000004 Book_order #bname 何以笙箫默 原来你还在这里 烈火如歌 烈火如歌 微微一笑很倾城 Suppilier #suppiler 大旗 红方 长城 高原 注：此处插入数据应当book在everybook 之前插入数据（外键）
address 上海 上海 江苏 郑州 telephone 1573234 1734272 1564323 1242356 contact 王平 李景 张艳丽 殷风 #supplier_name Order_time Order_count Order_price 大旗 大旗 红方 长城 高原 2013-6-10 2013-6-20 2013-7-2 2013-7-3 2013-7-3 2 1 1 2 1 12 10 10 9 11 cname 吕鹏 孙鲁鲁 王肖雨 邵丽君 sex 女 女 女 女 Age 18 18 19 19 #cno 00000001 00000002 00000002 00000003 00000004 Sell_time 2013-6-16 2013-6-25 2013-7-9 2013-7-9 2013-7-19 Sell_price 21 19 20 23 18
book
#bname 何以笙箫默 何以笙箫默 烈火如歌 烈火如歌 烈火如歌 #bname Writer_name Press何以笙箫默 原来你顾漫 晋江00000003 原来你还在这里 00000007 微微一笑很倾城 截图得：
24/34
4/27/2021
25/34 4/27/2021
视图显示基本信息
---用显示视图基本信息
select * from view_单本销售情况 select * from view_供应商信息 select * from view_客户信息 select * from view_同种销售信息 select * from view_图书订购信息
截图得：
26/34 4/27/2021
执行存储过程完成目标查询
某种图书库存查询
exec sp_某种图书库存查询'烈火如歌'
本月业绩查询
exec sp_查询某段时间内的业绩'2013-7-1','2013-7-31'
本月销售最好图书信息查询
exec sp_查询某段时间内销售最好的图书信息'2013-7-1','2013-7-31'
截图得：
用普通查询完成目标查询（两次查询同时显示）
订购超3次客户信息查询
---订购超次客户信息查询 select * from view_客户信息 where 客户编号=any(
select #cno from book_sell group by #cno having count(*)>3)
27/34 4/27/2021
按供应商名称查询订购信息
---按供应商名称查询订购信息 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
截图得：
28/34 4/27/2021
总结
经过将近一个星期的努力，终于设计出了一个基本符合设计要求的数据库系统。本系统功能强大，用户界面良好，然而，学生能力有限，该系统中仍存在某些小小的缺陷。
学生经过一段时间的努力，“功夫不负有心人”如期完成了设计任务，虽然不是很完善，但是，“麻雀虽小，五脏俱全”，在这个课程设计的练习中，学生学到了很多的东西。
29/34
4/27/2021
其中最重要的是，在这次练习中，学生了解了数据库设计的一般过程。在接受一个项目的时候，首先是进行系统分析，即分析使用的情况以及技术的可行性，有些东西是不可行的，在设计的时候就要绕过去。整体的框架要根据要求反复斟酌，切不可轻易做出决定，不然的话，一旦出错，再改数据库那就很麻烦了。
再有就是在创建数据库的过程中要注意的一些小细节了，比如说有一下几点：（1）对表建立时，应防止本允许插入重复记录（部分重复）却由于约束不当而无法正常插入。不要想当然的名一样就约束也一样；（2）每次对前文修改后都必须保存一次，同时对所有文件都进行修改，以保持数据与结构的一致性；（3）建好基本表之后插入数据适量，以验证功能。建数据库边验证边修改；（4）对于有不确定因素的应该建立存储过程进行查询。等等。
总体来说，通过这次的练习，学生的技术能力都有了一个技术层面上的提高了。
30/34
4/27/2021
因篇幅问题不能全部显示，请点此查看更多更全内容