图书管理系统数据库设计——SQL.doc

《数据库》课程设计

课设名称： 图书管理系统数据库设计与实现
年 级：
专 业： 网络工程
班 级：
姓 名：
学 号：
成 绩：
指导教师： 颜 颖

提交报告时间： 2015 年 1 月 14 日

数据需求
图书馆管理信息系统需要完成功能主要有：
1. 读者基本信息的输入，包括借书证编号、读者姓名、读者性别登记日期。
2．读者基本信息的查询、修改，包括读者借书证编号、读者姓名、性别等。
3．书籍类别标准的制定、类别信息的输入，包括类别编号、类别名称。
4．书籍类别信息的查询、修改，包括类别编号、类别名称。
5．书籍库存信息的输入，包括书籍编号、书籍名称、书籍类别编号、作者、出版社、出版日期、登记日期，价格，是否可借。
6．书籍库存信息的查询，修改，包括书籍编号、书籍名称、书籍类别编号、姓名、出版社、出版日期、登记日期、价格、是否可借等。
7．借书信息的输入，包括读者借书证号、书籍编号、借书日期，应还时间。
8．借书信息的查询、修改，包括借书证编号、读者编号、读者姓名、书籍编号、书籍名称、借书日期等。
9．还书信息的输入，包括借书证编号、书籍编号、还书日期。
10．还书信息的查询和修改，包括还书读者借书证编号、读者姓名、书籍编号、书籍名称、借书日期、还书日期等。
11．超期还书罚款输入，还书超出期限包括超出期限还书的读者借阅证号，书籍编号，罚款金额。
12.超期还书罚款查询，删除，包括读者借书证编号、读者姓名、书籍编号、书籍名称，应还时间，罚款金额，借阅时间，超期时间等

事物需求
(1)在读者信息管理部分,要求:
a.可以查询读者信息。
b.可以对读者信息进行添加及删除的操作。
(2 )在书籍信息管理部分,要求:
a.可以浏览书籍信息,要求:
b.可以对书籍信息进行维护,包括添加及删除的操作。
(3)在借阅信息管理部分,要求:。
a.可以浏览借阅信息。
b.可以对借阅信息进行维护操作。
(4)在归还信息管理部分，要求:
a.可以浏览归还信息
b.对归还信息可修改维护操作
(5)在管理者信息管理部分,要求:
a.显示当前数据库中管理者情况。
b.对管理者信息维护操作。
(6)在罚款信息管理部分,要求:
a.可以浏览罚款信息
b.对罚款信息可以更新
(7) 在书籍类别管理部分，要求：
A. 可以浏览书籍类别信息
B.对书籍类别信息可以更新
(8) 在系部信息管理部分，要求：
B. 可以系部信息
B.对系部信息可以进行增删改操作

关系模式

(一) 书籍类别（书籍类别编号，类别名称）
(二) 借阅者信息实体（借阅证号，姓名，性别，登记时期，读者类别）
(三) 学生实体（读者类别，学号，借阅数，专业，电话）
(四) 教师实体（读者类别，职位，工号，借阅数，电话）
(五) 书籍（书籍编号，书籍名称，书籍类别编号，作者，出版社，出版日期，价格，登记日期，是否可借）
(六) 借阅（借阅证号，书籍编号，借阅时间时间，应还时间）
(七) 还书（借阅证号，书籍编号，还书时间）
(八) 罚款（借阅证号，姓名，书籍名称，书籍编号，借阅时间，应还时间，还书时间，罚款金额）
(九) 系部（系部名称，系部编号）
(十) 读者类别表（读者类别编号，读者类别名称）

E/R图
总的信息实体E-R图

数据字典

表２-1　 book_sytle 书籍类别信息表
表中列名
数据类型
可否为空
说明
bookstyleno
Varchar(20)
not null(主键)
书籍类别编号
bookstyle
Varchar(30)
not null
种类名称
表２-2　　system_readers借阅者信息表格
表中列名
数据类型
可否为空
说明
readerid
Varchar(9)
not null(主键)
借阅证号
readername
Varchar(9)
not null
读者姓名
readersex
Varchar(2)
not null
读者性别
readertype
Varchar(10)
Not null
读者类别
regdate
datetime
null
登记日期
表2-3　system_books书籍信息表
表中列名
数据类型
可否为空
说明
bookid
Varchar(20)
Not null(主键)
书籍编号
price
Varchar(6)
Not null
价格
bookname
Varchar(30)
Not null
书籍名称
Bookstyleno
Varchar(20)
Not null
书籍类别编号
bookauthor
Varchar(30)
Not null
作者
isborrowed
Varchar(1)
Not null
是否可借
bookpub
Varchar(30)
Null
出版社
bookpubdate
Datetime
Null
出版日期
bookindate
Datetime
Null
登记日期

表2-4　borrow_record 借阅记录信息表
表中列名
数据类型
可否为空
说明
readerid
Varchar(9)
Not null(外主键)
借阅证号
bookid
Varchar(20)
Not null(外主键)
书籍编号
borrowdate
datetime
Not null
借阅时间
shouldreturn
datetime
?
应还时间

表2-5　return_record 还书记录信息表
表中列名
数据类型
可否为空
说明
readerid
Varchar(9)
Not null(外主键)
借阅证号
bookid
Varchar(20)
Not null(外主键)
书籍编号
returndate
datetime
Not null
读者还书时间

表2-6　reader_fee 罚款记录信息表
readerid
Varchar（9）
Not null（外主键）
借阅证号
readername
Varchar(9)
Not null
读者姓名
bookid
Varchar(20)
Not null(外主键)
书籍编号
bookname
Varchar(30)
Not null
书籍名称
bookfee
Varchar(10)
Not Null
罚款金额
borrowdate
datetime
Not Null
借阅时间
shouldreturn
datetime
null
应还时间
exceeddate
datetime
null
超期时间
表2-7 system_student 学生实体信息表
表中列名
数据类型
可否为空
说明
studentno
Varchar(20)
not null(主键)
学号
major
Varchar(30)
not null
专业
borrownum
Int
not null
借阅数
readertype
Varchar(10)
Not null
读者类别
phone
Varchar(20)
null
电话
表2-8 system_teacher 教师实体信息表

表中列名
数据类型
可否为空
说明
teachertno
Varchar（20）
not null(主键)
工号
Profession
Varchar（20）
null
职位
borrownum
Int
not null
借阅数
readertype
Varchar（20）
Not null
读者类别
phone
Varchar（20）
null
电话
表2-9 System_department 系部信息表
表中列名
数据类型
可否为空
说明
departmentno
Varchar(20)
not null(主键)
系部编号
deparmentname
Varchar（20）
not null
系部名称

表2-10system_resdertype读者类别表
表中列名
数据类型
可否为空
说明
readertypeno
Varchar(20)
Not null(主键)
读者类别编号
Readertype
Varchar(20)
Not null
读者类别名称

建表语句：
1.书本类别表建立
create table book_style
(
bookstyleno varchar(20) primary key,
bookstyle varchar(30) not null
)
2.书籍表建立
create table system_books
(
bookid varchar(20) primary key,
bookname varchar(20) Not null,
bookstyleno varchar(30) Not null,
price varchar(6) not null,
bookauthor varchar(30) not null,
isborrowed varchar(1) not null,
bookpub varchar(30) ,
bookpubdate datetime,
bookindate datetime ,
foreign key (bookstyleno) references book_style (bookstyleno),
)
3.借阅者表建立
create table system_readers
( readerid varchar(9) primary key,
readername varchar(9) not null ,
readersex varchar(2) not null,
readertype varchar(10) not null,
regdate datetime
)
4. 借书记录表建立
create table borrow_record
( bookid varchar(20) primary key,
readerid varchar(9) not null,
borrowdate datetime not null ,
shouldreturn datetime,

foreign key (bookid) references system_books(bookid),
foreign key (readerid) references system_readers(readerid),
)
5.还书记录表建立
create table return_record
( bookid varchar(20) primary key,
readerid varchar(9)not null,
returndate datetime not null,
foreign key (bookid) references system_books(bookid),
foreign key (readerid) references system_readers(readerid)
)
6. 罚款单表建立*/
create table reader_fee
( readerid varchar(9)not null,
readername varchar(9)not null ,
bookid varchar(20) primary key,
bookname varchar(30) Not null,
bookfee varchar(10) not null,
borrowdate datetime not null,
shouldreturn datetime,
exceeddate varchar(5) ,
foreign key (bookid) references system_books(bookid),
foreign key (readerid) references system_readers(readerid)
)
7.学生表建立
create table system_student
(
studentno varchar(20) primary key,
major varchar (30) not null,
borrownum int not null,
readertype varchar(20) ,
phone varchar(20)
)
8. 读者类别表建立
create table system_readertype
(
readertypeno varchar(20) primary key,
readertype varchar (20) not null
）
9.系别表建立
create table system_department
(
departmentno varchar(20) primary key,
departmentname varchar (20) not null
)
10．教师表建立
create table system_teacher
(
readerid varchar(9)not null,

1、焚烧处理垃圾的优缺点是什么？teacherno varchar(20) primary key,
borrownum int not null,
profession varchar(20),
readertype varchar(20) not null,
phone varchar(20),
foreign key (readerid) references system_readers (readerid)

13、清洁的自来水被用来洗脸、刷牙、洗衣、拖地后就成了污水。)

16、在北部天空的小熊座上有著名的北极星，可以借助大熊座比较容易地找到北极星。黑夜可以用北极星辨认方向。

4、“我迈出了一小步，但人类迈出了一大步。”这句话是阿姆斯特朗说的。

1、月相的变化有什么规律？（P49）

19、夏季是观察星座的好季节，天空中有许多亮星，其中人们称之为“夏季大三角”的是天津四、织女星和牛郎星。它们分别属于天鹅座、天琴座、天鹰座。

答：如蚂蚁、蝗虫、蚕蛾、蚜虫、蟋蟀、蝉、蝴蝶、蜜蜂、七星瓢虫等。
数据初始化及表更新、查询
1.向Book_style表中插入数据

2、你知道日食的形成过程吗？insert into book_style(bookstyleno,bookstyle)values('1','人文艺术类')
insert into book_style(bookstyleno,bookstyle)values('2','自然科学类')
insert into book_style(bookstyleno,bookstyle)values('3','社会科学类')

14、在显微镜下观察物体有一定的要求。物体必须制成玻片标本，才能在显微镜下观察它的精细结构。insert into book_style(bookstyleno,bookstyle)values('4','图片艺术类')
insert into book_style(bookstyleno,bookstyle)values('5','政治经济类')

5、在咀嚼米饭过程中，米饭出现了甜味，说明了什么？insert into book_style(bookstyleno,bookstyle)values('6','工程技术类')
insert into book_style(bookstyleno,bookstyle)values('7','语言技能类')
表单查询：select * from book_style

2.向system_books 表中插入数据：
insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )
values('00125415152','计算机组成原理','6','王爱英','清华大学出版社','2001-01-03','2003-11-15','35.5','3','10');
insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )
values('00456456','数据库原理','6','萨师煊','高等教育出版社','2007-07-02','2007-09-15','40','4','10');
insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )
values('12215121','C程序设计','6','谭浩强','清华大学出版社','2002-04-02','2004-03-14','60','5','8');
insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )
values('9787308020558','计算机体系结构','6','石教英','浙江大学出版社','2004-10-03','2006-11-15','60','5','8');
insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )
values('45456141414','数据结构（C语言版）','6','吴伟民，严蔚敏','清华大学出版社','2002-06-28','2004-01-21','40','5','10');
insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )
values('545551523','中华历史年','1','吴强','北京大学出版社','2005-04-03','2006-05-15','56','0','10');
insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )
values('151451424','日本文化','1','吴小鹏','北京大学出版社','2002-04-02','2004-03-14','35','0','10');
insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )
values('151546564','微观经济学','5','李小刚','北京大学出版社','2000-10-03','2001-11-15','35','0','10');
insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )
values('565833422','影视文学','4','苏庆东','北京大学出版社','1999-02-28','2000-01-21','35','0','10');
insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )
values('565800020','探索宇宙奥秘','2','苏庆东','北京大学出版社','1999-02-28','2000-01-21','35','0','10');

表单查询：select * from system_books

3.向读者表中插入数据：
insert into system_readers(readerid,readername,readersex,readertype,regdate)
values('X05620207','陈飞','男','1','2005-9-23 14:23:56')
insert into system_readers(readerid,readername,readersex,readertype,regdate)
values('X05620206','张三','男','1','2005-09-30 13:24:54.623')
insert into system_readers(readerid,readername,readersex,readertype,regdate)
values('X05620204','赵静','女','1','2005-09-27 11:24:54.123')
insert into system_readers(readerid,readername,readersex,readertype,regdate)
values('X05620202','潘小虹','女','1','2005-09-30 13:24:54.473')
insert into system_readers(readerid,readername,readersex,readertype,regdate)
values('008415','蒋伟','男','2','2004-04-30 09:24:54.478')
insert into system_readers(readerid,readername,readersex,readertype,regdate)
values('001456','李风','女','2','2004-04-30 09:24:54.478')

表单查询：select * from system_readers

4.（insert，update ,set）向借书记录表中加入数据：
insert into borrow_record(bookid,readerid,borrowdate,shouldreturn)
values('545551523','X05620207','2007-09-27 11:24:54.123','2007-10-27 11:24:54.123')
update system_books
set isborrowed=0
where bookid='545551523' and isborrowed='1'

insert into borrow_record(bookid,readerid,borrowdate,shouldreturn)
values('151546564','X05620204','2014-09-03 10:24:54.123','2014-10-03 10:24:54.123')
update system_books
set isborrowed=0
where bookid='151546564' and isborrowed='1'

insert into borrow_record(bookid,readerid,borrowdate,shouldreturn)
values('151451424','001456','2014-09-03 10:24:54.123','2014-12-03 10:24:54.123')
update system_books
set isborrowed=0.
where bookid='151451424' and isborrowed='1'

5．向学生表中加入数据
insert into system_student (readerid , studentno,readertype ,major ,borrownum ,phone )
values('X05620207','1125111001','1','计算机','30','1234567890')

insert into system_student (readerid ,studentno ,readertype ,major ,borrownum ,phone )
values('X05620206','1125111002','1','计算机','30','1234567890')

insert into system_student (readerid ,studentno ,readertype ,major ,borrownum ,phone )
values('X05620202','1122111001','1','哲学与社会','30','1234567890')

insert into system_student (readerid ,studentno ,readertype ,major ,borrownum ,phone )
values('X05620204','112011100','1','国际经济与贸易','30','1234567890')

(DELETE)删除学生操作：

delete from system_student
where readerid= 'X05620204'

系部信息表：

6.向教师表中加入数据：
insert into system_teacher(readerid , teacherno,readertype , profession ,borrownum ,phone )
values('001456','12022301','2','数据库教师','40','1234567890')
insert into system_teacher(readerid , teacherno,readertype ,profession ,borrownum ,phone )
values('008415','12022302','2','C语言教师','40','1234567890')

7. (DISTINCT,多表查询)查询所有书所对应的类别：

8.向罚款表中添加数据操作(超期1天罚款0.3元)：
【INSERT,多表查询，DATEDIFF(),GETDATE(),CONVERT(),ADD】
insert into reader_fee(readerid,readername,bookid,bookname,bookfee,borrowdate,shouldreturn ,returndate )
select system_readers.readerid 读者借书证编号 ,readername 读者姓名,
system_books.bookid 书籍编号,bookname 书名,
0.3*(Datediff(day,convert(smalldatetime,borrowdate),getdate())-30) 超过时间天数,
borrowdate 借书时间, shouldreturn 应还时间 , returndate 还书时间
from borrow_record ,system_readers ,system_books ,return_record
where system_readers.readerid=borrow_record.readerid
and system_books.bookid=borrow_record.bookid
and Datediff(day,convert(smalldatetime,borrowdate),getdate())>=30

9.创建索引：【INDEX】

create index keyindex on borrow_record (bookid,readerid)
删除索引：
DROP INDEX keyindex on borrow_record

10．应用distinct查询表

select distinct readerid
from borrow_record

11.应用COUNT统计表单数据:

select COUNT (readerid)
from borrow_record
group by readerid

12.应用count统计某学生节约的书籍总数
select COUNT (readerid)
from borrow_record
where readerid ='X05620201'

13.多表查询（查询有借书的学生的学号，姓名，读者类型，可借数，专业以及所借书籍的编号）
select system_readers.readerid,studentno,readername,system_readers.readertype,borrownum,major
from system_readers ,system_student
where system_readers.readerid=system_student.readerid

14. 使用GROUP BY , HAVING子句（查询有借过书的同学的学号和剩余可借数）
select distinct studentno , borrownum
from system_student
group by studentno ,borrownum
having borrownum <30

15. 使用ORDER BY子句（查询书籍，以价格从低到高排序）
select *
from system_books
order by price

16.嵌套查询，引入IN的用法：

select * from system_books
where bookpub in ('北京大学出版社','清华大学出版社')

查询出版社为“清华大学出版社”的书籍信息：
select *
from system_books
where bookid in (
select bookid from system_books
where bookpub='清华大学出版社'
)

17.创建视图（查询已借书籍的编号、名称以及借阅者信息）
create view bookprod as
select system_books.bookid ,bookname,system_readers.readerid,readername
from system_books ,system_readers,borrow_record
where system_books.bookid =borrow_record.bookid and borrow_record .readerid =system_readers .readerid

18. 使用SELECT语句，从视图表和基本表中查询并显示数据
select * from bookprod

19.查询谁借了什么书：
select readername 读者姓名,bookname 书籍名称
from borrow_record,system_books,system_readers
where system_readers.readerid=borrow_record.readerid
and system_books.bookid=borrow_record.bookid
截图：

若指定查询姓名为“刘群”借的书
在此基础上添加：and readername='刘群'
如图：

20.添加还书记录操作：
1．首先还书要在还书纪录中添加一条还书纪录
2．其次删除相应书本的借阅纪录
3．最后在书库中标记该本书为1，表示归还了可供其他读者借阅
【delete，update，set】
insert into return_record(bookid,readerid,returndate)
select bookid,readerid,getdate()
from borrow_record
where bookid=’111111111’
delete
from borrow_record
where bookid=’111111111’

update system_books
set isborrowed=1
where bookid=’111111111’

21.查询超期情况：
select di