Oracle图书馆管理系统

1、学 号： 实 训 报 告教 学 院计算机学院课程名称Oracle数据库系统实训题 目高校图书馆管理信息系统的数据设计与实现专 业网络工程班 级姓 名同组人员指导教师2014年年6月月24日日一、 需求分析1.背景随着计算机科学日渐成熟，科学技术的不断提高，图书馆希望设计一个图书管理系统，用于管理读者的登记，图书的购入、借出、归还以及注销等。并且方便管理人员还查阅某位读者、某本图书的借阅情况。并可加快对当前借阅情况统计的效率，以全面掌握图书的流通情况、提高工作效率、加快信息反馈，为读者提供满意的借阅环境，减轻图书信息管理人员的劳动强度，提高工作效率和质量，从而使图书和读者的管理更加合理化和科学化

2、。2.实体与属性图书 : 每本图书的编号、价格、书名、作者、出版时间、采订时间。图书分类： 类别编号、类别名称、该类总数。书库：书库编号、书库名称、位置。地址：地区编号、地区名称、父编号。出版社：出版社编号、出版社名称 。读者：读者号、姓名、民族、性别、籍贯、身份证号、注册时间、读书证过期时间、借书数目。系别：系别名、系别编号。专业：入学时间、专业代码、专业名称、学年制。借阅记录：借出时间、归还时间、应还时间。3.实体之间在联系图书 n ：1 出版社图书 n ：1 分类图书 n ：1 书库图书 n : 1读者图书 1 ：n 借阅记录 借阅记录 n ：1 读者出版社 n ：1 地址读者（籍贯）

3、n ：1 地址读者 n : 1 系别读者 n : 1 学历读者 n ： 1 专业二、概念结构设计1.E-R图根据概念模型设计的E-R图，提供了实体型、属性和联系。2.数据项清单NameCodeDomainData TypeLengthPrecision专业代码majorIdCharacters (12)12专业名称majorNameCharacters (30)30书名bookNameCharacters (30)30书库位置storgaepositionCharacters (40)40书库名称storageNameCharacters (30)30书库编号storageIDCharacte

4、rs (12)12价格priceDecimal (6,1)61作者authorCharacters (20)20借阅时间borrowDateDate出版时间publishDateDate出版社代码publishCodeCharacters (12)12出版社名称publishNameCharacters (20)20出版社地址publishAddressCharacters (40)40出生年月readerBirthDate分类号categoryId1Characters (12)12分类号categoryIdCharacters (12)12单位代码unitIdCharacters (12)

5、12单位名称uintNameCharacters (30)30图书编号bookIDCharacters (12)12年级majorGradeCharacters (20)20归还时间returnTimeDate归还时间returnDateDate性别readerSexCharacters (2)2政治面貌readerPoliticsStatusCharacters (4)4民族readerNationlityCharacters (2)2类型名categoryNameCharacters (30)30类编号bcIDCharacters (12)12索引号bookindexCharacters

6、(12)12职位信息officePoistionCharacters (40)40职别代码positionIdNumber (10,0)10职别名称positionNameCharacters (30)30角色roleidInteger读者号readerIDCharacters (12)12读者号readerId2Integer读者姓名readerNameCharacters (20)20贯籍readerNativeCharacters (20)203.相关表信息表名属性主码BookInfo图书信息表bookID、bcID、storageID、price、bookName、author、pub

7、lishDate、publishCode1bookIDBookStorageInfo书库信息表storageID、storageName、storgaeposition、storageIDPublishInfo出版情况publishCode、bookID、publishName、publishAddresspublishCodeCategory图书分类bcID、categoryId、categoryNamebcIDmajorInfo专业majorId、majorGrade、majorNamemajorIdreaderInfo读者readerId、majorId、readerName、reade

8、rSex、readerNative、readerBirth、readerNationlity、majorCode、roleid、officePoistionreaderIdunitInfo单位unitId、readerId2、uintNameunitId三、逻辑、物理结构设计为减少数据冗余，避免更新异常、插入异常和删除异常，该系统的关系模式被规范到3NF。系统数据库设计如下图所示。四、数据库实施1.创建表create table "Books" ( "Bno" CHAR(10) not null, "Cno" CHAR(10), &q

9、uot;Sno" CHAR(10), "Pnumber" CHAR(10), "Bprice" INTEGER, "Bname" CHAR(20), "Bauthor" CHAR(20), "Bptime" DATE, "Bctime" DATE, constraint PK_BOOKS primary key ("Bno")create table "Borrowing" ( "Rnumber" CHAR

10、(10) not null, "Bno" CHAR(10) not null, "BOtime" DATE, "BOretime" DATE, "BOshtime" DATE, "BOfine" CHAR(2), "BOyesorno" CHAR(2), constraint PK_BORROWING primary key ("Rnumber", "Bno")create table "Major" ( "

11、;Mno" CHAR(10) not null, "Mname" CHAR(20), "Myears" CHAR(1), constraint PK_MAJOR primary key ("Mno")create table "Publisher" ( "Pnumber" CHAR(10) not null, "Adminid" CHAR(10), "Pname" CHAR(20), constraint PK_PUBLISHER primary

12、 key ("Pnumber")create table "Readers" ( "Rnumber" CHAR(10) not null, "Dno" CHAR(5), "Adminid" CHAR(10), "DEno" CHAR(5), "Mno" CHAR(10), "Rname" CHAR(20), "Rnation" CHAR(20), "Rsex" CHAR(2), "Rpr

13、ovence" CHAR(10), "RIDcard" CHAR(18), "Rretime" DATE, "Rcount" CHAR(2), "Rintime" DATE, constraint PK_READERS primary key ("Rnumber")2.创建索引create index "Relationship_1_FK" on "Books" ( "Pnumber" ASC)create index &qu

14、ot;Relationship_2_FK" on "Books" ( "Cno" ASC)create index "Index_readerno" on "Borrowing" ( "Rnumber" ASC)create index "Index_majorno" on "Readers" ( "Mno" ASC)create index "Index_readername" on "Read

15、ers" ( "Rname" ASC)3.创建视图create or replace view "Rnumber_Department" asselect Readers.Rnumber,Major.Mname,Department.Department from Readers,Major,Departmentwhere Readers.Mno=Major.Mno and Readers.DEno=Department.DEno;with read onlycreate or replace view unit_reader asselect

16、 readerInfo.readerName, readerInfo.readerPoliticsStatus, readerInfo.officePoistion, unitInfo.uintNamefrom unitInfo, readerInfowhere readerInfo.readerId2 = unitInfo.readerId2with read only4.创建储存过程create or replace procedure "AddBooks"(<arg> in out <type>) as(name Classification.

17、Cname%TYPE)asbeginif(exists(select * from Classification where Cname=name) update Classification set Ccount=Ccount+1 where Cname=name;elseinsert into Classification(Cname) values(name);update Classification set Ccount=Ccount+1 where Cname=name;end if;end AddBooks;create or replace procedure "Ad

18、dorUpdateMajor"(<arg> in out <type>) as(mno Major.Mno%TYPE,mname Major.Mname%TYPE,myear Major.Myears%TYPE)asbeginif(exists(select * from Major where Mno=mno)update Major set Mname=mname,Myears=myear where Mno=mno;elseinsert into Major(Mno,Mname,Myears) values(mno,mname,myear);end if

19、;end AddorUpdateMajor;create or replace procedure "DeleteBook"(<arg> in out <type>) as(no Books.Bno%TYPE)asbegin delete from Books where Bno=no;end DeleteBook;create or replace procedure "DeleteBorrowing"(<arg> in out <type>) as(rno Borrowing.Rnumber%TYPE,

20、bno Borrowing.Bno%TYPE,btime Borrowing.BOtime%TYPE)asbegindelete Borrowing where Rnumber=rno and Bno=bno and BOtime=btime;end DeleteBorrowing;create or replace procedure "SelectBook"(<arg> in out <type>) as(no Bname_Saddress.Bno%TYPE)asbegin select Sname,Saddress,Bname,Bauthor from Bname_Saddress where Bno=no;end SelectBook;create or replace procedure "SelectBorrowing"(<arg> in out <type>) as(rno Borrowing.Rnumber%TYPE,bno Bor