数据库图书管理简单实例

1、精选优质文档-倾情为你奉上 图书数据库设计-一 需求分析近年来，随着图书馆规模的不断扩大，图书数量也相应的增加，有关图书的各种信息量也成倍的增加，面对着庞大的信息量，传统的人工方式管理的记录中我们可以发现一系列的问题。因此必须制定一套合理、有效、规范和实用的图书管理系统，对图书资料进行集中统一的管理。系统开发的总的设计目标是实现图书管理的系统化、规范化和自动化，实现对图书资料的集中统一的管理。使图书管理者便于对图书和读者的管理。本系统主要实现对图书馆信息的管理，主要功能为管理有关读者、图书、借阅、查询、删除和管理员的信息等。使用该系统之后，图书馆管理人员可以管理读者的登记、图书的购入、借出、归

2、还以及注销等；还可以查询某位读者、某本图书的借阅情况，对当前借阅情况给出一些统计，给出统计表格，以全面掌握图书的流通情况 。二 概念设计 系统E-R 图 ER图三 逻辑设计关系模式图四 系统功能分析1） 建表#新建borrowPerson表create table borrowPerson(ID varchar(15) PRIMARY key,name varchar(15) not null,sex varchar(5) check(sex in ('男','女'),identity varchar(10) check(identity in ('学生

3、','教师','其他'),phone varchar(20),location varchar(50)#新建bookNumber 表create table booknumber(ISBN varchar(30) primary key check(ISBN like '%-%-%-%'),count int check(count>=0)#新建book表create table book(B_ID varchar(20) primary key,ISBN varchar(30) not null references booknu

4、mber,B_name varchar(50) not null,author varchar(50) not null,family varchar(20) not null,price float(2) default 0.00)#新建borrow 表create table borrow(B_ID varchar(20) primary key references book,ID varchar(15) not null references borrowPerson,date Datetime not null)#新建publisher表create table publisher(

5、P_name varchar(50) primary key,location varchar(50) not null,telenumber varchar(20)#新建publish 表create table publish(P_name varchar(50) not null references publisher,B_ID varchar(20) not null references book,date datetime,primary key(P_name,B_ID)2） 建视图 #新建各种数的借阅情况视图create view borrow_of_details(book_

6、name,borrowed_num,nbrrow_num,total_num)as select B_name,count(B_ID)-count,count,count(B_ID)from book natural join booknumbergroup by B_name,count#视图2，书的详情create view book_details(Book_name,Book_publisher,Book_price,ISBN) as select B_name,P_name,price,ISBNfrom book natural join publish#视图3，借书人的详情crea

7、te view borrowperson_details(b_ID,b_name,b_sex,b_identity) as select ID,name,sex,identityfrom borrowperson#视图4，出版社create view publish_details(pu_name,pu_number,pu_location) as select P_name,telenumber,locationfrom publisher3） 查询 #找出所有在2015年前借了书的人的名字select name from borrowperson natural join borrow n

8、atural join bookwhere date<2015#找出所有刚好借走了同一种书的最后一本数的人的名字和身份select name,identityfrom borrowperson natural join borrowwhere B_ID in (select B_ID from book natural join booknumber where count=1)#找出所有被借完关于数学的书的名字，书号(select B_name,B_IDfrom bookwhere ISBN in(select ISBN from booknumber where count=0)in

9、tersect (select B_name,B_IDfrom bookwhere B_ID in(select B_ID from borrow )#找出所有在广州出版社出版的数的名字select B_namefrom book natural join publishwhere P_name='广州出版社'#找出所有同种类型的书的数量，书名并按照价格的升序排序select B_name,price,count( distinct ISBN) as numfrom bookgroup by price ASC#找出所有在北京的出版社或被不同身份的人接走的书的名字(select

10、 B_namefrom bookwhere B_ID in(select B_ID from publish natural join publisher where location like '%北京%')UNION(select B_name from book where B_ID in(select B_ID from borrow where ID in(select ba.ID from borrowPerson as ba,borrowPerson as bb where ba.identity!=bb.identity)#找出所有同一时间内多于5人借阅书的名字

11、select B_name,ISBN,count(ISBN)as numfrom book natural join borrowgroup by B_name,ISBNhaving count(ISBN)>54） 修改1. 新增 insert into borrowpersonvalues('8','何树源','男','学生','67585','华山4栋201');insert into borrowpersonvalues('9','黄德宝','男&

12、#39;,'学生','68578','华山4栋201');insert into borrowpersonvalues('4','单汶进','男','学生','66582','华山4栋201');insert into borrowpersonvalues('5','董永钊','男','学生','6897','华山4栋201');insert into bo

13、rrowpersonvalues('1','黄友生','男','学生','65265','华山4栋202');insert into booknumbervalues('978-7-111-4086-8',5);insert into booknumbervalues('978-7-111-4086-9',1);insert into booknumbervalues('978-7-111-4089-8',2);insert into publishe

14、rvalues('广州出版社','020-','广州天河区');insert into bookvalues('02001','978-7-111-4086-8','数据库','杨冬青','科学',59.00);insert into bookvalues('02002','978-7-111-4086-8','数据库','杨冬青','科学',59);insert into bookval

15、ues('02003','978-7-111-4086-8','数据库','杨冬青','科学',59);insert into bookvalues('02004','978-7-111-4086-8','数据库','杨冬青','科学',59);insert into bookvalues('02005','978-7-111-4086-8','数据库','杨冬青',

16、9;科学',59);insert into bookvalues('02006','978-7-111-4086-9','狗蛋日记','黄狗蛋','日记',9);insert into bookvalues('02007','978-7-111-4089-8','计算机图形学','张静','科学',39);insert into bookvalues('02008','978-7-111-4089-8&#

17、39;,'计算机概论','不详','科学',89);insert into publishvalues('02001','广州出版社','2016-01-25');insert into publishvalues('02002','广州出版社','2016-01-25');insert into publishvalues('02003','广州出版社','2016-01-25');insert int

18、o publishvalues('02004','广州出版社','2016-01-25');insert into publishvalues('02005','广州出版社','2016-01-25');insert into publishvalues('02006','广州出版社','2016-01-25');insert into publishvalues('02007','广州出版社','2016-01

19、-25');insert into publishvalues('02008','广州出版社','2016-01-25');insert into borrow values('02001','8','2016-01-26');2. 修改update bookset price=99where B_ID='02001'update publisherset telenumber='020-'where P_name='广州出版社'update

20、borrowpersonset phone='67586'where ID='8'update borrowpersonset location='泰山区7栋608'where ID='8'3. 删除delete from borrowpersonwhere ID='7'delete from borrowwhere B_ID='02001'5） 存储过程或函数 #函数，返回书的种数create function different_book()returns integerbegin declar

21、e num integer ; select count(*) into num from booknumber;return num;end#函数，查询书的属性create function book_deta(B_name varchar(50)returns table(B_ID varchar(20),ISBN varchar(30),B_name varchar(50),author varchar(50),family varchar(20),price float(2)return table( select B_ID,ISBN,B_name,author,family,pric

22、e from bookwhere book.B_name=book_deta.B_name);#过程，查询书名create procedure find_book(in book_ID varchar(20),out book_name varchar(50)begin select B_name into book_name from bookwhere ID=book_ID;end6） 触发器#触发器1，当borrow被新增后出发booknumbercreate trigger booknumberup after insert on borrow#referencing new row

23、as nrowfor each row begin update booknumberset count=count-1where ISBN in (select ISBN from book where B_ID=new.B_ID);end#触发器2，当borrow被删除后booknumbercreate trigger booknumberdown after delete on borrow#referencing new row as nrowfor each row begin update booknumberset count=count+1where ISBN in (sele

24、ct ISBN from book where B_ID=old.B_ID);end#触发器3，当book被增加后booknumbercreate trigger bookndown after insert on book#referencing new row as nrowfor each row begin update booknumberset count=count+1where ISBN in (select ISBN from book where B_ID=new.B_ID);end#触发器4，当book被删除后booknumbercreate trigger booknu

25、p after delete on book#referencing new row as nrowfor each row begin update booknumberset count=count-1where ISBN in (select ISBN from book where B_ID=old.B_ID);end#触发器5，book价格更新create trigger changePrice after update of book on pricereferencing new row as nrowfor each row begin update bookset price=nrow.pricewhere ISBN=new.ISBNend五 结果SQLinsert into publishvalues('02002','广州出版社','2016-01-25');受影响的行: 1时间: 0.041sSQLinsert into publishvalues('02003','广州出版社','2016-01-25');受影响的行: 1时间: 0.037sSQLinsert into publishvalues(&