數(shù)據(jù)庫圖書管理簡單實例

1、精選優(yōu)質文檔-傾情為你奉上 圖書數(shù)據(jù)庫設計-一 需求分析近年來，隨著圖書館規(guī)模的不斷擴大，圖書數(shù)量也相應的增加，有關圖書的各種信息量也成倍的增加，面對著龐大的信息量，傳統(tǒng)的人工方式管理的記錄中我們可以發(fā)現(xiàn)一系列的問題。因此必須制定一套合理、有效、規(guī)范和實用的圖書管理系統(tǒng)，對圖書資料進行集中統(tǒng)一的管理。系統(tǒng)開發(fā)的總的設計目標是實現(xiàn)圖書管理的系統(tǒng)化、規(guī)范化和自動化，實現(xiàn)對圖書資料的集中統(tǒng)一的管理。使圖書管理者便于對圖書和讀者的管理。本系統(tǒng)主要實現(xiàn)對圖書館信息的管理，主要功能為管理有關讀者、圖書、借閱、查詢、刪除和管理員的信息等。使用該系統(tǒng)之后，圖書館管理人員可以管理讀者的登記、圖書的購入、借出、歸

2、還以及注銷等；還可以查詢某位讀者、某本圖書的借閱情況，對當前借閱情況給出一些統(tǒng)計，給出統(tǒng)計表格，以全面掌握圖書的流通情況 。二 概念設計 系統(tǒng)E-R 圖 ER圖三 邏輯設計關系模式圖四 系統(tǒng)功能分析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） 建視圖 #新建各種數(shù)的借閱情況視圖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#找出所有剛好借走了同一種書的最后一本數(shù)的人的名字和身份select name,identityfrom borrowperson natural join borrowwhere B_ID in (select B_ID from book natural join booknumber where count=1)#找出所有被借完關于數(shù)學的書的名字，書號(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 )#找出所有在廣州出版社出版的數(shù)的名字select B_namefrom book natural join publishwhere P_name='廣州出版社'#找出所有同種類型的書的數(shù)量，書名并按照價格的升序排序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)#找出所有同一時間內(nèi)多于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-','廣州天河區(qū)');insert into bookvalues('02001','978-7-111-4086-8','數(shù)據(jù)庫','楊冬青','科學',59.00);insert into bookvalues('02002','978-7-111-4086-8','數(shù)據(jù)庫','楊冬青','科學',59);insert into bookval

15、ues('02003','978-7-111-4086-8','數(shù)據(jù)庫','楊冬青','科學',59);insert into bookvalues('02004','978-7-111-4086-8','數(shù)據(jù)庫','楊冬青','科學',59);insert into bookvalues('02005','978-7-111-4086-8','數(shù)據(jù)庫','楊冬青',

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='泰山區(qū)7棟608'where ID='8'3. 刪除delete from borrowpersonwhere ID='7'delete from borrowwhere B_ID='02001'5） 存儲過程或函數(shù) #函數(shù)，返回書的種數(shù)create function different_book()returns integerbegin declar

21、e num integer ; select count(*) into num from booknumber;return num;end#函數(shù)，查詢書的屬性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） 觸發(fā)器#觸發(fā)器1，當borrow被新增后出發(fā)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#觸發(fā)器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#觸發(fā)器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#觸發(fā)器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#觸發(fā)器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(&