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'),'530347830','13550399250','hubei');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 char,authorno in char,zncode in char)asbegininsert into Booksvalues(isbn,title,pubname,author,authorno,zncode);end BooksAdd;执行过程：execute BooksAdd('Booo1','遮天','辰东','未来出版社',11,11);2.触发器的定义删除Books书籍信息表中的一条记录，删除记录isbn的字段值在Copies副本信息表中对应的记录create or replace trigger BooksDelete after delete on Books for each rowbegindelete from Copieswhere isbn=:OLD.isbn;end BooksDelete;3.4.1 用户权限创建用户create user reader identified by reader;create user manager identified by manager;授予：grant all to manager;grant select on books to reader;grant select on publishers to reader;grant select on authors to reader;grant select on copies to reader;grant select on writers to reader;grant select on categories to reader;
