数据库图书管理简单实例.docx

图书数据库设计-一 需求分析近年来，随着图书馆规模的不断扩大，图书数量也相应的增加，有关图书的各种信息量也成倍的增加，面对着庞大的信息量，传统的人工方式管理的记录中我们可以发现一系列的问题。因此必须制定一套合理、有效、规范和实用的图书管理系统，对图书资料进行集中统一的管理。系统开发的总的设计目标是实现图书管理的系统化、规范化和自动化，实现对图书资料的集中统一的管理。使图书管理者便于对图书和读者的管理。本系统主要实现对图书馆信息的管理，主要功能为管理有关读者、图书、借阅、查询、删除和管理员的信息等。使用该系统之后，图书馆管理人员可以管理读者的登记、图书的购入、借出、归还以及注销等；还可以查询某位读者、某本图书的借阅情况，对当前借阅情况给出一些统计，给出统计表格，以全面掌握图书的流通情况 。二 概念设计 系统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 (学生,教师,其他),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 booknumber,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(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_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，借书人的详情create 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 natural join bookwhere date54） 修改1. 新增 insert into borrowpersonvalues(201330330308,何树源,男,学生,67585,华山4栋201);insert into borrowpersonvalues(201330330309,黄德宝,男,学生,68578,华山4栋201);insert into borrowpersonvalues(201330330304,单汶进,男,学生,66582,华山4栋201);insert into borrowpersonvalues(201330330305,董永钊,男,学生,6897,华山4栋201);insert into borrowpersonvalues(201330330311,黄友生,男,学生,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 publishervalues(广州出版社广州天河区);insert into bookvalues(02001,978-7-111-4086-8,数据库,杨冬青,科学,59.00);insert into bookvalues(02002,978-7-111-4086-8,数据库,杨冬青,科学,59);insert into bookvalues(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,数据库,杨冬青,科学,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,计算机概论,不详,科学,89);insert into publishvalues(02001,广州出版社,2016-01-25);insert into publishvalues(02002,广州出版社,2016-01-25);insert into publishvalues(02003,广州出版社,2016-01-25);insert into 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-25);insert into publishvalues(02008,广州出版社,2016-01-25);insert into borrow values(02001,201330330308,2016-01-26);2. 修改update bookset price=99where B_ID=02001update publisherset telenumberhere P_name=广州出版社;update borrowpersonset phone=67586where ID=201330330308;update borrowpersonset location=泰山区7栋608where ID=201330330308;3. 删除delete from borrowpersonwhere ID=201330330307;delete from borrowwhere B_ID=02001;5） 存储过程或函数 #函数，返回书的种数create function different_book()returns integerbegin declare 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,price 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 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 (select 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 booknup 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(02004,广州出版社,2016-01-25);受影响的行: 1时间: 0.068sSQLinsert into publishvalues(02005,广州出版社,2016-01-25);受影响