SQL程序设计图书管理系统样本.doc

SQL程序设计—图书管理系统

学院：计算机与信息工程学院
专业：计算机科学与技术
班级：2班
学号：10910521
姓名：张亮亮
日期：-12-3
分数级别：中

1.开发背景
数据库技术和Internet飞速发展，使它们已经成为当代信息技术重要构成某些，是当前计算机信息系统和计算机应用系统基本和核心。对于任何一种公司来说，数据是公司重要资产，如何有效运用这些数据，对于公司发展起着极其重要作用。随着国内市场经济迅速发展和人们生活水平不断提高，图书馆藏书树木逐渐增大，这也挑战了图书管理方面技术，此前人工管理方式已经不再适应当前环境，取而代之是先进图书管理系统，通过使用PowerBuilder创立图书管理系统可以让管理人员以便而快捷进行管理、查询、借阅、录入等工作。
2.需求分析
2.1系统目的
图书管理信息系统是典型信息管理系统(MIS),其开发重要涉及后台数据库建立和维护以及前端应用程序开发两个方面。对于前者规定建立起数据一致性和完整性强.数据安全性好库。而对于后者则规定应用程序功能完备,易使用等特点。
系统开发总体任务是实现各种信息系统化，规范化和自动化。
2.2需求定义
图书馆管理系统开发。系统开发总设计目的是实现图书管理系统化、规范化和自动化，实现对图书资料集中统一管理。
本系统重要实现对图书馆信息管理，重要功能为管理关于读者、图书、借阅、查询、删除和管理员信息等。本系统构造分为读者类别管理、读者档案管理、图书类型管理、图书档案管理以及图书流程管理，读者管理可以浏览读者信息，可以对读者信息进行维护。图书管理可以浏览图书信息，可以对图书信息进行维护。借阅管理可以显示当前数据库中书籍借阅状况，可以对借阅信息进行维护。本系统重要解决问题是运用核心字对数据库进行查询。
其系统功能模块图如下：
图书管理系统
图书管理
图书资料
投诉管理
系统管理
图书补偿
顾客管理
密码管理
退出系统
图书借阅
图书登记

图2-1 系统功能模块图

针对普通图书管理信息系统需求，通过对图书管理工作过程内容和数据流程分析，设计如下面所示数据项：

读者信息
属性：读者学号，读者姓名，读者性别，联系电话，所在院系，生效日期，失效日期，违章状况，合计借书
主键：读者学号
书籍信息
属性：ISBN，书名，作者，出版社，出版日期，简介
主键：ISBN
管理员信息
属性：工作号，姓名，性别，电话，家庭住址
主键：工作号
2.3数据流程
2.3.1 读者
作为学生对图书管理系统规定有：
1. 能按各种方式（例如书名、编号、作者）查询图书馆藏书状况。
2. 可以以便地借阅图书、续借图书、归还图书。
3. 可以查询自己基本资料、借阅图书状况。
4. 可以熟悉图书管理系统使用。
读者进入系统工作流程图为:
查询
修改密码
借阅续借
归还
注册
注销

2-1进入系统工作流程图
2.3.2 图书管理员
作为图书管理员，她们对图书管理系统规定有：
1. 能以便对图书进行录入登记，注销陈旧书籍。
2. 可以以便地对新生进行登记，或注销已经毕业学生信息（基本信息，借阅信息）。
3. 可以随时发布某些诸如各学院学生借阅图书超期状况、馆内藏书状况、借状况等信息，以便各学院可以随时获知本院学生某些借书信息。
图书管理员工作流程图为：

2-2图书管理员工作流程图
3.功能描述
系统功能分析是在系统开发总体任务基本上完毕。本系统需要完毕功能重要有5某些：
(1) 图书基本状况录入，修改，删除等基本操作。
1．书籍类别原则制定，类别信息输入，涉及类别编号，类别名称，核心词，备注信息等。
2．书籍类别信息查询，修改，涉及类别编号，类别名称，核心词，备注信息等。
3．书籍信息输入，涉及书籍编号，书籍名称，书籍类别，作者名称，出版社名称，出版日期，书籍
页数，核心词，登记日期，备注信息等。
(2) 办理借书卡模块。
1．新生办理借书证。
2．丢失办理借书证。
(3) 实现借书功能。
1．借书信息输入，涉及借书信息编号，读书编号，读者姓名，书籍编号，书籍名称，借书日期，备注信息等。
2．借书信息查询，修改，涉及借书信息编号，读者编号，读者姓名，书籍编号，书籍名称，借书日期，备注信息等。
(4) 实现还书功能。
1．还书信息输入，涉及还书信息编号，读者编号，读者姓名，书籍编号，书籍名称，借书日期，还书日期，备注信息等。
2．还书信息查询和修改，涉及还书信息编号，读书编号，读者姓名，书籍编号，书籍名称，借书日期，还书日期，备注信息等。
(5) 能以便对图书进行查询。对超期状况能自动给出提示信息
4.概念模图书登记

注册

录入
修改个人信息

注销

超期解决
型设计
各某些E-R图如下：
(1)读者信息类别实体E-R图

读者编号
借书期限
借书数量
种类名称

读者类别实体

(2)书籍信息类别实体E-R图

图书编号
书籍类别
出版社
书籍名称

书籍类别实体

5.逻辑模型设计和优化
从理论‘E-R模型’到理论‘关系模型’整顿转换，通过E-R模型到关系模型转化，可以得到如下关系模式：
借阅关系
属性：工作号，读者学号，ISBN，与否续借，借书日期，还书日期，备注。
主键：工作号，读者学号，ISBN
管理员_书籍关系
属性：工作号，ISBN，添加时间，与否在馆
主键：工作号，ISBN
管理员_学生关系
属性：工作号，读者学号，确认借还
主键：工作号，读者学号
通过由概念模型设计得到CDM图powerdesigner转换成相应PDM图：

PDM图

5.1图书信息表
列名
数据类型
可否为空
ISBN
varchar
否
书名
varchar
否
作者
varchar
否
出版社
varchar
是
出版日期
datetime
是
简介
varchar
是

5.2 读者信息表
列名
数据类型
可否为空
读者学号
varchar
否
读者姓名
varchar
否
读者性别
varchar
否
联系电话
varchar
是
所在院系
varchar
否

5.3 管理员信息表
列名
数据类型
可否为空
工作号
varchar
否
姓名
varchar
否
性别
varchar
否
电话
varchar
是
家庭住址
varchar
是

5.4 借阅表
列名
数据类型
可否为空
工作号
char
否
ISBN
char
否
读者学号
char
否
借阅日期
datetime
否
还书日期
datetime
否
与否续借
char
否

 5.5 管理员_书籍
列名
数据类型
可否为空
工作号
char
否
ISBN
char
否
添加时间
datatime
是
与否在馆
char
是

5.6 管理员_学生
列名
数据类型
可否为空
工作号
char
否
读者学号
char
否
借还确认
char
否

6.物理设计和实行
从理论‘关系模型’到实现\实行‘数据库建立’，物理文献安排和建立索引
6.1 建立索引
为了提高在表中搜索元组速度，在实际实现时候应当基于键码建立索引是各表中建立索引表项：
(1)读者信息（读者学号）
(2)书籍信息（ISBN）
(3)管理员信息（工作号）
(4)借阅（工作号，读者学号，ISBN）
(5)管理员_书籍（工作号，ISBN）
(6)管理员_学生（工作号，读者学号）

6.2 SQL语句
如下所示：
/*==============================================================*/
/* Table：书籍信息 */
/*==============================================================*/
create table 书籍信息
(
ISBN char(20) not null,
书名 char(20),
作者 char(10),
出版社 char(15),
出版日期 char(30),
简介 char(160),
constraint PK_书籍信息 primary key (ISBN)
);

/*==============================================================*/
/* Index：书籍信息_PK */
/*==============================================================*/
create unique index 书籍信息_PK on 书籍信息 (
ISBN ASC
);

/*==============================================================*/
/* Table：投诉 */
/*==============================================================*/
create table 投诉
(
工作号 char(10) not null,
学号 char(10) not null,
投书意见 char(100),
投诉日期 char(30),
受诉日期 char(30),
constraint PK_投诉 primary key (工作号，学号)
);

/*==============================================================*/
/* Index：投诉_PK */
/*==============================================================*/
create unique index 投诉_PK on 投诉 (
工作号 ASC,
学号 ASC
);
/*==============================================================*/
/* Index：投诉_FK */
/*==============================================================*/
create index 投诉_FK on 投诉 (
工作号 ASC
);
/*==============================================================*/
/* Index：投诉2_FK */
/*==============================================================*/
create index 投诉2_FK on 投诉 (
学号 ASC
);

/*==============================================================*/
/* Table：登记 */
/*==============================================================*/
create table 登记
(
工作号 char(10) not null,
学号 char(10) not null,
ISBN char(20) not null,
证书日期 char(30) not null,
还书日期 char(30) not null,
违章状况 char(160),
合计借书 char(160),
备注 char(160),
constraint PK_登记 primary key (工作号，学号，ISBN)
);

/*==============================================================*/
/* Index：登记_PK */
/*==============================================================*/
create unique index 登记_PK on 登记 (
工作号 ASC,
学号 ASC,
ISBN ASC
);

/*==============================================================*/
/* Index：登记_FK */
/*==============================================================*/
create index 登记_FK on 登记 (
工作号 ASC
);

/*==============================================================*/
/* Index：登记2_FK */
/*==============================================================*/
create index 登记2_FK on 登记 (
学号 ASC
);

/*==============================================================*/
/* Index：登记3_FK */
/*==============================================================*/
create index 登记3_FK on 登记 (
ISBN ASC
);

/*==============================================================*/
/* Table：管理员信息 */
/*==============================================================*/
create table 管理员信息
(
工作号 char(10) not null,
姓名 char(10),
性别 char(5),
电话 char(15),
所在院系 char(25),
constraint PK_管理员信息 primary key (工作号)
);

/*==============================================================*/
/* Index：管理员信息_PK */
/*==============================================================*/
create unique index 管理员信息_PK on 管理员信息 (
工作号 ASC
);

/*==============================================================*/
/* Table：读者信息 */
/*==============================================================*/
create table 读者信息
(
学号 char(10) not null,
姓名 char(10),
性别 char(5),
电话 char(15),
所在院系 char(25),
constraint PK_读者信息 primary key (学号)
);

/*==============================================================*/
/* Index：读者信息_PK */
/*==============================================================*/
create unique index 读者信息_PK on 读者信息 (
学号 ASC
);
7.重要数据操纵语句
7.1管理员操作
(1)注册(register)
INSERT INTO 管理员 (工作号,姓名,性别,电话,家庭住址,备注)
VALUES(#工作号,#姓名,#性别,#电话,#家庭住址,#备注)
阐明：在登记操作后,管理员得到一种唯一工作号,可以依照这个工作号采查询和修改数据。
(2)注销(unregister)
DELETE
FROM Provider
WHERE(工作号=#工作号)；
(3)修改个人信息(update)
UPdate 管理员
Set (工作号=#工作号,姓名=#姓名,性别=#性别,电话=#电话,家庭住址=#家庭住址)
WHERE(工作号＝#工作号)
(4)增长书籍（addbooks）
INSERT INTO 图书 (ISBN,书名,作者,出版社,出版日期,简介,)
VALUES(#ISBN,#书名,#作者,#出版社,#出版日期,#简介,#备注) INSERT INTO
管理员_书籍表(工作号,ISBN,添加时间,与否在馆)
VALUES(#工作号,#ISBN,#添加时间,#与否在馆）
(5)删除书籍(deletebooks)
DELETE 图书
WHERE(ISBN =# ISBN)
(6)修改书籍(updatebooks)
UPDATE 图书(书名=#书名，作者=#作者，出版社=#出版社，出版日期=
#出版日期，简介=#简介)
WHERE(ISBN =# ISBN)

7.2读者操作
(1)注册(register)
INSERT INTO 读者(读者学号,读者姓名,读者性别,联系电话,所在系，
生效日期,失效日期,违章状况,合计借书,备注)
VALUES(#读者学号,#读者姓名,#读者性别,#联系电话,#所在系，
#生效日期,#失效日期,#违章状况,#合计借书,#备注)
阐明：在登记操作后,读者得到一种唯一ID,可以依照这个ID来查询和修改自己信息。
(2)注销(unregister)
DELETE 读者
WHERE(读者学号=#读者学号)
(3)修改个人信息(update)
UPDATE 读者 Set(读者姓名=#读者姓名,联系电话=#联系电话，
所在系=#所在系,生效日期=#生效日期,失效日期=#失效日期，
违章状况=#违章状况,合计借书=#合计借书,备注=#备注)
WHERE(读者学号=#读者学号)
(4)查询（select）
SELECT ISBN,书名,作者,出版社
FROM图书
WHERE ISBN=#ISBN OR书名=#书名

7.3管理员对借阅关系操作
(1)插入读者信息（insert）
INSERT INTO 借阅(工作号,读者学号,ISBN,与否续借,借书日期,还书日期,备注)
VALUES(#工作号,#读者学号,#ISBN,#与否续借,#借书日期，
#还书日期,#备注)
(2)更新信息（update）
①更新借出信息
UPDATE借阅
SET(借书日期=#借书日期,还书日期=借书日期+30,与否续借=0)
WHERE(工作号=#工作号 AND 读者学号=#读者学号 AND ISBN =#ISBN)
UPDATE管理员_图书
SET(与否在馆=0)
WHERE(ISBN=#ISBN)
UPDATA 学生
SET (累积借书=累积借书+1)
WHERE(读者学号=#读者学号)
INSERT INTO管理员_学生(工作号,读者学号,确认借还,ISBN)
VALUES(#工作号,#读者学号,”0”，#ISBN))
②更新续借信息
UPDATE 借阅
SET(与否续借=#与否续借)
WHERE(工作号=#工作号 AND 读者学号=#读者学号 AND ISBN =#ISBN)
③更新还书信息
UPDATE管理员_图书
SET(与否在馆=1)
WHERE(ISBN=#ISBN)
UPDATE管理员_学生
SET(确认借还=”1”)
WHERE(工作号=#工作号 AND 读者学号=#读者学号 AND ISBN=#ISBN)
任务1

借书卡表：CARD (CNO，CNAME，CLASS)，其中CNO表达卡号，CNAME表达姓名，CLASS表达班级。
--------------------------------------------------
CNO CNAME CLASS
--------------------------------------------------
C01 张三 计算机1班
C02 李四 计算机1班
C03 王五 计算机2班
--------------------------------------------------
图书表：BOOKS(BNO，BNAME，AUTHOR，PRICE，QUANTITY)，其中BNO表达书号，BNAME表达书名，AUTHOR表达作者，PRICE表达单价，QUANTITY表达库存册数。
---------------------------------------------------------------------
BNO BNAME AUTHOR PRICE QUANTITY
---------------------------------------------------------------------
B01 水浒 施耐庵 30 5
B02 西游记 吴承恩 20 2
B03 三国演义 罗贯中 50 3
B04 红楼梦 曹雪芹 40 6
---------------------------------------------------------------------
借书登记表：BORROW(CNO，BNO，RDATE)，其中CNO表达借书卡号，BNO表达书号，RDATE表达还书日期。
-----------------------------------------------
CNO BNO RDATE
-----------------------------------------------
C01 B01 -12-1
C01 B04 -12-5
C02 B02 -12-12
C02 B03 -12-4
C03 B01 -12-1
C03 B02 -12-15
C03 B03 -12-1
C03 B04 -12-6
-----------------------------------------------
备注：限定每人每种书只能借一本；库存册数随借书、还书而变化。
实现如下15个解决：
1．写出建立表CARD、BOOKS和BORROWSQL语句，定义主码完整性约束和引用完整性约束，将每条记录插入表中。
2．找出借书超过5本读者，输出借书卡号及所
借图书册数。
3．查询借阅了《水浒》一书读者，输出姓名及班级。
4．查询过期未还图书，输出借阅者（卡号）、书号及还书日期。
5．查询书名涉及“国”核心词图书，输出书号、书名、作者。
6．查询既有图书中价格最高图书，输出书名及作者。
7．查询当前借了《红楼梦》但没有借《三国演义》读者，输出其借书卡号，并按卡号降序排序输出。
8．将“计算机1班”同窗所借图书还期都延长一周。
9．从BOOKS表中删除当前无人借阅图书记录。
10．如果经常按书名查询图书信息，请建立适当索引。
11．在BORROW表上建立一种插入触发器，完毕如下功能：如果读者借阅书名是《三国演义》，就将该读者借阅记录保存在BORROW_SAVE表中（注BORROW_SAVE表构造同BORROW表）。
12．建立一种视图，显示计算机1班学生借书信息（只规定显示姓名和书名）。
13．查询当前同步借有《红楼梦》和《水浒》两本书读者，输出其借书卡号，并按卡号升序排序输出。
14．假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码语句。
15．对CARD表做如下修改：
a. 将NAME最大列宽增长到10个字符（假定原为6个字符）。
b. 为该表增长1列DNAME（系名），可变长，最大20个字符。
代码：
create database book
use book
create table card
(
cno char(10) primary key,
cname varchar(50) not null,
class varchar(20) not null);
insert into card(cno,cname,class)
values('c01','张三','计算机班');
insert into card(cno,cname,class)
values('c02','李四','计算机班');
insert into card(cno,cname,class)
values('c03','王五','计算机班');
select * from card
create table books
(
bno char(10) primary key,
bname varchar(20) not null,
author varchar(20) not null,
price float(20) not null,
quantity int not null
);
insert into books(bno,bname,author,price,quantity)
values('b01','水浒','施耐庵','30','5');
insert into books(bno,bname,author,price,quantity)
values('b02','西游记','吴承恩','20','2');
insert into books(bno,bname,author,price,quantity)
values('b03','三国演义','施耐庵','50','3');
insert into books(bno,bname,author,price,quantity)
values('b04','红楼梦','曹雪芹','40','6');
select * from books
create table c1
(
cno char(10),
bno char(10),
rdate datetime,
primary key(cno,bno)
)
insert into c1
values('c01','b01','-12-1');
insert into c1
values('c01','b04','-12-5');
insert into c1
values('c02','b02','-12-12');
insert into c1
values('c02','b03','-12-4');
insert into c1
values('c03','b01','-12-1');
select * from c1
where
group by cno
having count(*) >=2
select * from c1,card,books /*第三题*/
where o=o and
c1.bno=books.bno and bname='水浒'
select * /*第四题*/
from c1
where rdate<getdate()
select getdate();
select * from books /*第五题*/
where bname like '%国%'
select * from books /*第六题*/
where price >=
(select max(price) from books)
select * from c1,books /*第七题*/
where c1.bno=books.bno and
bname='红楼梦' and cno
not in(
select cno from c1,books
where c1.bno=books.bno and
bname = '三国演义')

update c1 /*第八题*/
set rdate=rdate+7
where cno in
(select cno from card where class='计算机班')

delete from books /*第九题*/
where bno not in
(select bno
from c1)

create index indexbname /*第十题*/
on book.dbo.books(bname);
go
create trigger tr_save on c1 /*第十一题*/
for insert
as
if @@rowcount > 0
insert into borrow_save
select i.*from inserted i,books b
where i.bno=b.bno and
b.bname= N'三国演义'
create table borrow_save
(
cno char(3),
bno char(3),
rdate datetime
)
insert into c1
values('')

create view viewBorrow /*