oracle图书管理信息系统.doc

图书管理信息系统 班 级： 09网编3班 学 号： 0104090917 姓 名： 黄振雄 一、1用户身份 用户、管理员。2图书管理模块对图书进行添加、查询、修改、删除、添加。3副本信息模块 对图书的副本信息进行添加、修改、删除。4. 用户管理模块对用户进行添加、删除、修改。 二、2.1图书管理信息系统ER图2数据库的设计 Books表字段数据类型数据长度能否为空中文名备注ISBNChar20否图书编码主码TitleChar30是书名PubnameChar30是出版者名AuthorChar30是作者名AuthornoNumber30是作者号ZNCodeNumber30是中南分校码 Copies表字段数据类型数据长度能否为空中文名备注CopyNoNumber10否副本编号主码ISBNChar20是图书编码 Authors表字段数据类型数据长度能否为空中文名备注AuthorNoNumber10否作者号主码AuthorNameChar20是作者名 Categories表字段数据类型数据长度能否为空中文名备注ZNCodeNumber20否中南分校码主码CateNameChar20是目录名 Writers表字段数据类型数据长度能否为空中文名备注ISBNChar20否书编码主码AuthorNoNumber20否作者号主码 Publishers表字段数据类型数据长度能否为空中文名备注PubNameChar30否出版者名主码AddressChar50是地址 Users表字段数据类型数据长度能否为空中文名备注UsersNoNumber12否用户编号主码UserNameChar20否用户名UserPwdChar20否用户密码QuanxianNumber20否权限BirthdayDate否用户生日EmailChar30是用户的邮箱TelChar20是用户的联系电话AddressChar20是籍贯三、3.1 表空间的创建 create tablespace datalogging datafile D:Oracleproduct10.2.0oradatalibrarydata01.dbf size 50m reuse autoextend off;3.2 数据表1.数据表的创建 （1）创建Books表create table Books (isbn char(20) not null primary key,title char(30),pubname char(30),author char(30),authorno number(30),zncode number(30);（2）创建Copies表create table copies (copyno number(10) not null primary key,isbn char(20),constraint fk_copyno foreign key(isbn) references Books(isbn);（3）创建Authors表create table Authors (authorno number(10) not null primary key,authorname char(20);（4）创建Categories表create table Categories (zncode number(20)not null primary key,catename char(20);（5）创建Writers表create table Writers (isbn char(20)not null,authorno number(20)not null,constraint pk_writers primary key(isbn,authorno);（6）创建Publishers表create table Publishers (pubname char(30)not null primary key,address char(50);（7）创建Users表create table Users (UserName char(20) not null,UserPwd char(20) not null,UserNo number(12) primary key,Birth date not null, Quanxian number(20),Email char(30),TEL char(20),Address char(20);2.表中数据的插入（1）Books表insert into Books(ISBN,Title,PubName,ZNCode,author,authorno) values(A0001,草样年华,长江文艺出版社,1,孙睿,1);Insert intoBooks(ISBN,Title,PubName,ZNCode,author,authorno)values(A0002,莲花,武汉大学出版社,2,安妮宝贝,2);Insert intoBooks(ISBN,Title,PubName,ZNCode,author,authorno) values(A0003,小王子,作家出版社,3,黄葒,3);Insert intoBooks(ISBN,Title,PubName,ZNCode,author,authorno)values(A0004,彼岸花,北京大学出版社,4,安妮宝贝,2);Insert intoBooks(ISBN,Title,PubName,ZNCode,author,authorno) values(A0005,二三事,武汉大学出版社,5,安妮宝贝,2);Insert intoBooks(ISBN,Title,PubName,ZNCode,author,authorno) values(A0006,霜冷长河,作家出版社,6,余秋雨,4);Insert intoBooks(ISBN,Title,PubName,ZNCode,author,authorno)values(A0007,笛声何处,作家出版社,7,余秋雨,4);insert into Books(ISBN,Title,PubName,ZNCode,author,authorno) values(A0008,梦里花落知多少,长江文艺出版社,8,郭敬明,5);insert into Books(ISBN,Title,PubName,ZNCode,author,authorno) values(A0009,小时代,长江文艺出版社,9,郭敬明,5);insert into Books(ISBN,Title,PubName,ZNCode,author,authorno) values(A00010,三重门,作家出版社,10,韩寒,6);（2）Copies表insert into copies(copyno,isbn) values(1001,A0001);insert into copies(copyno,isbn) values(1002,A0002);insert into copies(copyno,isbn) values(1003,A0003);insert into copies(copyno,isbn) values(1004,A0004);insert into copies(copyno,isbn) values(1005,A0005);insert into copies(copyno,isbn) values(1006,A0006);insert into copies(copyno,isbn) values(1007,A0007);insert into copies(copyno,isbn) values(1008,A0008);insert into copies(copyno,isbn) values(1009,A0009);insert into copies(copyno,isbn) values(10010,A00010);（3）Users表 insert into Users(UserName,UserPwd,UserNo,QuanXian,Birth,Email,TEL,Address) Values(冯美,123, 1,1,TO_DATE(1986-09-01,YYYY-MM-DD),530347830hubei);Insert into Users(UserName,UserPwd,UserNo,QuanXian,Birth,Email,TEL,Address) values(王灿,456,2,2,TO_DATE(1998-10-09,YYYY-MM-DD),297074969,1355041230,hubei);Insert intoUsers(UserName,UserPwd,UserNo,QuanXian,Birth,Email,TEL,Address) values(杨攀,789,3,2,TO_DATE(1984-10-01,YYYY-MM-DD),503381172,1355041316,hubei);Insert intoUsers(UserName,UserPwd,UserNo,QuanXian,Birth,Email,TEL,Address) values(张楠楠,111,4,2,TO_DATE(1978-02-09,YYYY-MM-DD),502881172,1355041720,hubei);Insert intoUsers(UserName,UserPwd,UserNo,QuanXian,Birth,Email,TEL,Address) values(王子莫,222,5,2,TO_DATE(1989-01-09,YYYY-MM-DD),503381171,1355041136,hubei);Insert intoUsers(UserName,UserPwd,UserNo,QuanXian,Birth,Email,TEL,Address) values(李丽,333,6,2,TO_DATE(1989-01-09,YYYY-MM-DD),503386472,1355041510,hubei);Insert intoUsers(UserName,UserPwd,UserNo,QuanXian,Birth,Email,TEL,Address) values(张婷,444,7,2,TO_DATE(1988-05-09,YYYY-MM-DD),403386472,1355041732,hubei);Insert intoUsers(UserName,UserPwd,UserNo,QuanXian,Birth,Email,TEL,Address) values(王维,555,8,2,TO_DATE(1986-07-19,YYYY-MM-DD),503122347,1355041110,hubei);Insert intoUsers(UserName,UserPwd,UserNo,QuanXian,Birth,Email,TEL,Address) values(刘晓林,666,9,2,TO_DATE(1989-11-16,YYYY-MM-DD),503391412,1355041903,hubei);Insert intoUsers(UserName,UserPwd,UserNo,QuanXian,Birth,Email,TEL,Address) values(罗莎,777,10,2,TO_DATE(1989-12-12,YYYY-MM-DD),103366472,1355041168,hubei);3.数据的修改和删除 （1）数据的修改将用户为9的用户的联系电话修改为1355041906update Usersset TEL=1355041906 where userno=9; （2）数据的删除将用户罗莎删除delete from Users where username=罗莎;3.3索引和视图1索引的创建（1）在Books表中的书名创建索引。create index Books_title_idxon Books(title);（2）为Users表的姓名创建索引。create index Users_username_idxon Users(username); 2.视图的创建 （1）创建视图“cx_books”,查看图书的图书编码，书名，出版社名，中南分校编码，作者名，作者号。create or replace view cx_booksasselect ISBN,Title,PubName,ZNCode,author,authornofrom Books;（2）创建视图“ cx_zj”，各用户只能查找作家出版社的图书名称，作者，副本编号。create or replace view cx_zjas select title,author,copynofrom Books,Copies where Copies.isbn=Books.isbn and PubName=作家出版社;（3）创建视图“cx_anni”，查看作者为安妮宝贝所有的图书信息的视图。create or replace view cx_anniasselect * from Books where author=安妮宝贝;查看视图： select * from cx_books;select * from cx_zj;select * from cx_anni;3.数据的查询（1）查询图书表中作者号为2的作者所有作品的书名和出版社。select title,pubname from Books where authorno=2;（2）查找所有姓王的用户的编号，姓名，联系电话，地址。select userno 用户编号,username 姓名,tel 联系电话,address 地址 from users where username like 王%;（3）查找由作家出版社出版的所有图书的副本编号。 select copyno from copies where isbn in(select isbn from books where pubname=作家出版社);3.4 PL/SQL 1.存储过程添加书籍信息的存储过程BooksAddcreate or replace procedure BooksAdd (isbn in char,title in char,pubname in char,author in cha