ORACLE大型数据库技术图书管理系统

ORACLE大型数据库技术题目： 图书管理系统学生姓名： 雷丰悦学号： 专业班级： 计科14101同组姓名: 指导教师： 设计时间：指导老师意见:评定成绩:签名:日期:目录一、 需求分析11.系统功能实现：1二、 数据库设计21. 概念结构设计：22. 逻辑结构设计：3三、 创建表空间和用户5四、 在表空间中创建表且插入数据51. 创建表5五、 索引9六、 数据查询和视图91.数据查询：92.视图：9七、 存储过程的使用9八、 用户、角色11九、 实验心得11一、 需求分析读者到图书馆电子查询处查询图书，查找读者需要的图书。如果有满足读者要求的图书，查看该图书是否存在馆藏，如存在，读者凭借索书号进入馆藏室取书交给管理员，管理员在系统记录读者信息并记录借出图书信息，如果不在馆，则无法提供图书。1.系统功能实现：实现图书的查询、借出、归还、交罚款功能。1) 流程图根据系统功能的需求分析，经过模块化的分析得到如下所示系统功能模块结构图。读者借阅图书，系统查询图书是否在馆。图书已借出，显示已借出；未借出显示下一步，图书管理员管理图书，登记读者信息，借出图书。读者借阅图书判断图书是否在馆管理员管理所借图书借阅图 1-1 借阅图书模块流程图二、 数据库设计 1. 概念结构设计：1) 局部ER图图 书读 者借 阅编号姓名性别系别违章状况借书证号还书日期借书日期书名出版社出版日期ISBN简介作者nn数量读者借阅图书借阅关系E-R图2) 全局ER图全局完整E-R图2. 逻辑结构设计：数据库的逻辑设计过程就是把ER图转化为关系模式的过程。1)将E-R图转化为关系模式 读者（借书证号，姓名，性别，联系电话，系别，违章状况） 图书（ISBN，索书号，书名，作者，出版社，出版日期，简介，数量） 管理员（工作编号，姓名，性别，电话，工资） 借阅（读者编号，ISBN，工作编号，是否续借，借书日期，还书日期） 管理员_书籍（工作编号，ISBN，添加时间，是否在馆）2)设计数据表读者信息表READER字段名数据类型长度约束属性姓名VARCHAR30NOT NULL非主属性性别CHAR4NOT NULL非主属性系别VARCHAR30NOT NULL非主属性借书证号VARCHAR20NOT NULL主码联系电话CHAR11非主属性违章状况CHAR4非主属性图书信息表BOOK字段名数据类型长度约束属性ISBNVARCHAR30NOT NULL主码索书号VARCHAR30NOT NULL非主属性书名VARCHAR50NOT NULL非主属性作者VARCHAR30NOT NULL非主属性出版社VARCHAR50非主属性出版日期DATE非主属性简介VARCHAR50非主属性数量INT非主属性管理员信息表MASTER字段名数据类型长度约束属性工作编号VARCHAR20NOT NULL主码姓名VARCHAR30NOT NULL非主属性性别VARCHAR2NOT NULL非主属性电话CHAR11非主属性借阅关系表R-B字段名数据类型长度约束属性ISBNVARCHAR30NOT NULL外码借书证号VARCHAR20NOT NULL外码工作编号VARCHAR20NOT NULL外码借书日期DATENOT NULL非主属性还书日期DATENOT NULL非主属性是否续借CAHR4NOT NULL非主属性确认归还CHAR4NOT NULL非主属性管理员_书籍表M-B字段名数据类型长度约束属性工作编号VARCHAR20NOT NULL外码ISBNVARCHAR30NOT NULL外码添加时间DATE非主属性是否在馆CHAR4非主属性三、 创建表空间和用户CREATE TABLESPACE TEST DATAFILE D:TEST.ORA SIZE 100M;/创建表空间DROP TABLESPACE TEST; /删除表空间CREATE USER TEST IDENTIFIED BY DEFAULT TABLESPACE TEST QUOTA 100M ON USERS;/创建用户赋予密码及分配表空间GRANT ALL PRIVILEGES TO TEST;/赋予用户全部权限四、 在表空间中创建表且插入数据1. 创建表创建读者-READER表：CREATE TABLE READER( 借书证号 VARCHAR(20) PRIMARY KEY NOT NULL,姓名 VARCHAR(30) NOT NULL, 性别 CHAR(4) NOT NULL,系别 VARCHAR(30) NOT NULL, 联系电话 CHAR(11), 违章状况 CHAR(4);INSERT INTO READER VALUES (, 杰伦, 男, 计算机科学与技术, , 否);INSERT INTO READER VALUES (, 张华, 男, 地理科学, , 否);INSERT INTO READER VALUES (, 蔡依林,女, 音乐与艺术, , 否);INSERT INTO READER VALUES (, 李明,男,数学与应用数学, , 否);INSERT INTO READER VALUES (, 那英,女,土木工程, , 否);INSERT INTO READER VALUES (, 哈伦,男,酒店管理, , 否);INSERT INTO READER VALUES (, 罗志祥,男,旅游管理, , 否);INSERT INTO READER VALUES (, 黄渤,男,生物科学, , 否);INSERT INTO READER VALUES (, 唐颖,女,国际经济与贸易, , 否);INSERT INTO READER VALUES (, 雷龙,男,汉语言文学, , 否);创建管理员-MASTER表：CREATE TABLE MASTER ( 工作编号 VARCHAR(20) PRIMARY KEY NOT NULL, 姓名 VARCHAR(30) NOT NULL, 性别 CHAR(4) NOT NULL, 电话 CHAR(11) );INSERT INTO MASTER VALUES (1, 韩东, 男, );INSERT INTO MASTER VALUES (2, 王晓丽, 女, );INSERT INTO MASTER VALUES (3, 杨自鸣, 男, );INSERT INTO MASTER VALUES (4, 冯冰, 女, );INSERT INTO MASTER VALUES (5, 杜南珍, 女, );INSERT INTO MASTER VALUES (6, 蒋华, 男, );INSERT INTO MASTER VALUES (7, 刘翔, 男, );INSERT INTO MASTER VALUES (8, 童诗珊, 女, );INSERT INTO MASTER VALUES (9, 陈诗诗, 女, );INSERT INTO MASTER VALUES (0, 夏蓉, 男, );创建图书-BOOK表：CREATE TABLE BOOK ( ISBN VARCHAR(30) PRIMARY KEY NOT NULL,索书号 VARCHAR(30) NOT NULL, 书名 VARCHAR(50) NOT NULL, 作者 VARCHAR(30) NOT NULL, 出版社 VARCHAR(50), 出版日期 DATE, 简介 VARCHAR(50),数量 INT );INSERT INTO TEST.BOOK VALUES (978-7-111-21382-6, TP, JAVA编程思想, BRUCE ECKEL, 机械工业出版社, TO_DATE(2007-08-21 00:00:00, YYYY-MM-DD HH24:MI:SS), 最好的JAVA学习丛书, 5);INSERT INTO TEST.BOOK VALUES (978-7-121-05414-3, TP, PHP 5项目开发实战详解, PHPCHINA, 电子工业出版社, TO_DATE(2008-08-08 00:00:00, YYYY-MM-DD HH24:MI:SS), PHP项目实战丛书, 4);INSERT INTO TEST.BOOK VALUES (978-7-115-21678-6, TP, PHP基础教程, LARRY ULLMAN, 电子工业出版社, TO_DATE(2010-10-01 00:00:00, YYYY-MM-DD HH24:MI:SS), PHP丛书, 7);INSERT INTO TEST.BOOK VALUES (978-7-122-05978-9, TP, PHP从入门到精通, 陈超, 化学工业出版社, TO_DATE(2009-09-22 00:00:00, YYYY-MM-DD HH24:MI:SS), PHP丛书, 10);INSERT INTO TEST.BOOK VALUES (978-7-121-06490-6, TP, C#程序设计教程, 刘甫迎，刘光会，王蓉, 电子工业出版社, TO_DATE(2008-08-14 00:00:00, YYYY-MM-DD HH24:MI:SS), C#丛书, 2);INSERT INTO TEST.BOOK VALUES (978-7-302-17882-8, TP, C#从入门到精通, 王小科，吕双, 清华大学出版社, TO_DATE(2008-10-09 00:00:00, YYYY-MM-DD HH24:MI:SS), C#丛书, 3);INSERT INTO TEST.BOOK VALUES (7-121-03179-5, TN911.73, 数字图像处理, 胡学龙，许开宇, 电子工业出版社, TO_DATE(2006-07-18 00:00:00, YYYY-MM-DD HH24:MI:SS), 图像处理丛书, 5);INSERT INTO TEST.BOOK VALUES (978-7-300-10800-1, TP, 大学音乐鉴赏, 李迎春, 中国人民大学出版社, TO_DATE(2009-08-11 00:00:00, YYYY-MM-DD HH24:MI:SS), 音乐丛书, 5);INSERT INTO TEST.BOOK VALUES (7-03-9, F590.7, 旅游环境学, 颜文洪，张朝枝, 科学出版社, TO_DATE(2005-09-21 00:00:00, YYYY-MM-DD HH24:MI:SS), 旅游丛书, 9);INSERT INTO TEST.BOOK VALUES (7-5338-4487-4, D920.0, 法律教程, 孙笑侠, 浙江教育出版社, TO_DATE(2004-07-23 00:00:00, YYYY-MM-DD HH24:MI:SS), 法律-中国-高等学校-教材, 8);创建管理员_书籍表M-B表：CREATE TABLE MB( ISBN VARCHAR(30) NOT NULL,工作编号 VARCHAR(20) NOT NULL,添加时间 DATE,是否在馆 CHAR(4),PRIMARY KEY(工作编号,ISBN),FOREIGN KEY(工作编号)REFERENCES MASTER(工作编号),FOREIGN KEY(ISBN)REFERENCES BOOK(ISBN);INSERT INTO TEST.MASTER VALUES (1, 韩东, 男 , );INSERT INTO TEST.MASTER VALUES (2, 王晓丽, 女 , );INSERT INTO TEST.MASTER VALUES (3, 杨自鸣, 男 , );INSERT INTO TEST.MASTER VALUES (4, 冯冰, 女 , );INSERT INTO TEST.MASTER VALUES (5, 杜南珍, 女 , );INSERT INTO TEST.MASTER VALUES (6, 蒋华, 男 , );INSERT INTO TEST.MASTER VALUES (7, 刘翔, 男 , );INSERT INTO TEST.MASTER VALUES (8, 童诗珊, 女 , );INSERT INTO TEST.MASTER VALUES (9, 陈诗诗, 女 , );INSERT INTO TEST.MASTER VALUES (0, 夏蓉, 男 , );创建借阅关系表R-B表：CREATE TABLE RB( ISBN VARCHAR(30) NOT NULL, 借书证号 VARCHAR(20) NOT NULL,工作编号 VARCHAR(20) NOT NULL, 借书日期 DATE NOT NULL, 还书日期 DATE NOT NULL, 是否续借 CHAR(4) NOT NULL,确认归还 CHAR(4) NOT NULL,PRIMARY KEY(ISBN,借书证号,工作编号),FOREIGN KEY(ISBN) REFERENCES BOOK(ISBN),FOREIGN KEY(借书证号)REFERENCES READER(借书证号),FOREIGN KEY(工作编号)REFERENCES MASTER(工作编号);INSERT INTO RB VALUES (978-7-111-21382-6, , 1, TO_DATE(2016-12-24 17:27:54, YYYY-MM-DD HH24:MI:SS), TO_DATE(2017-01-23 17:27:57, YYYY-MM-DD HH24:MI:SS), N ,N );INSERT INTO RB VALUES (978-7-121-05414-3, , 1, TO_DATE(2016-12-24 17:29:00, YYYY-MM-DD HH24:MI:SS), TO_DATE(2017-01-23 17:29:03, YYYY-MM-DD HH24:MI:SS), N ,N );INSERT INTO RB VALUES (978-7-115-21678-6, , 2, TO_DATE(2016-12-23 17:29:25, YYYY-MM-DD HH24:MI:SS), TO_DATE(2017-01-22 17:29:29, YYYY-MM-DD HH24:MI:SS), N ,N );INSERT INTO RB VALUES (978-7-121-06490-6, , 2, TO_DATE(2016-12-20 17:30:56, YYYY-MM-DD HH24:MI:SS), TO_DATE(2016-12-19 17:30:59, YYYY-MM-DD HH24:MI:SS), N ,N );INSERT INTO RB VALUES (7-03-9, , 3, TO_DATE(2016-12-24 17:31:19, YYYY-MM-DD HH24:MI:SS), TO_DATE(2017-01-25 17:31:23, YYYY-MM-DD HH24:MI:SS), N ,N );INSERT INTO RB VALUES (7-121-03179-5, , 8, TO_DATE(2016-12-20 17:31:40, YYYY-MM-DD HH24:MI:SS), TO_DATE(2017-01-19 17:31:52, YYYY-MM-DD HH24:MI:SS), N ,N );INSERT INTO RB VALUES (978-7-300-10800-1, , 3, TO_DATE(2016-12-28 17:32:26, YYYY-MM-DD HH24:MI:SS), TO_DATE(2017-01-27 17:32:30, YYYY-MM-DD HH24:MI:SS), N ,N );INSERT INTO RB VALUES (7-121-03179-5, , 5, TO_DATE(2016-12-24 17:32:56, YYYY-MM-DD HH24:MI:SS), TO_DATE(2017-01-23 17:32:58, YYYY-MM-DD HH24:MI:SS), N ,N );INSERT INTO RB VALUES (978-7-302-17882-8, , 5, TO_DATE(2016-12-24 17:33:27, YYYY-MM-DD HH24:MI:SS), TO_DATE(2017-01-23 17:33:33, YYYY-MM-DD HH24:MI:SS), N ,N );INSERT INTO RB VALUES (978-7-122-05978-9, , 5, TO_DATE(2016-12-24 17:33:57, YYYY-MM-DD HH24:MI:SS), TO_DATE(2017-01-23 17:34:02, YYYY-MM-DD HH24:MI:SS), N ,N );五、 索引 1. 建立索引： CREATE INDEX READER_NAME ON READER(姓名);SELECT * FROM READER R WHERE R.姓名 = 杰伦六、 数据查询和视图 1.数据查询：1. SELECT * FROM READER /查询读者信息表2. SELECT * FROM RB WHERE 工作编号=13. SELECT * FROM RB WHERE 借书证号= AND ISBN=978-7-111-21382-6/查询借书证号为所借的书名4. SELECT BOOK.书名 FROM RB,BOOK WHERE RB.借书证号= AND BOOK.ISBN=RB.ISBN 5. SELECT * FROM RB WHERE 是否续借=N AND SYSDATE-借书日期30 AND 确认归还=N;6. SELECT 出版社,SUM(数量) 总本数 FROM BOOK GROUP BY 出版社/查询图书馆内各出版社 各有多少本书7. SELECT * FROM READER WHERE READER.借书证号 IN(SELECT 借书证号 FROM RB WHERE 是否续借=N AND SYSDATE-借书日期30 AND 确认归还=N )2.视图：1. CREATE OR REPLACE VIEW 欠费学生信息 ASSELECT READER.借书证号,READER.姓名,READER.性别,READER.系别,READER.联系电话,TO_CHAR(0.2*TO_NUMBER(SYSDATE-RB.借书日期),999.0) 欠费 FROM READER,RB WHERE 是否续借=N AND SYSDATE-借书日期30 AND 确认归还=N AND READER.借书证号=RB.借书证号SELECT * FROM 欠费学生信息;/创建视图查询未按期归还图书的学生信息及欠费情况。 七、 存储过程的使用 学生借阅信息删除 存储过程CREATE OR REPLACE PROCEDURE P_DELETE(V_ID VARCHAR2)-定义变量IDISBEGINDELETE FROM RB WHERE 借书证号=V_ID; -执行删除操作COMMIT;END;-执行BEGINP_DELETE();END;学生续借书籍 存储过程 实现续借日期加一个月 续借值改为YCREATE OR REPLACE PROCEDURE P_XJ(V_ID VARCHAR2,XUJIE CHAR)-定义变量IDISBEGIN-DELETE FROM RB WHERE 借书证号=V_ID; -执行删除操作UPDATE RB SET 是否续借=XUJIE,还书日期 = ADD_MONTHS( 还书日期, 1) WHERE 借书证号=V_ID;-执行修改操作COMMIT;END;-执行BEGINP_XJ(,Y);END;-执行完上边后执行SELECT * FROM RB;创建函数统计各出版社图书的库存量-函数CREATE OR REPLACE FUNCTION GETSCOUNT(IN1 IN VARCHAR2) RETURN NUMBER AS COUNTBOOKNUM NUMBER; BEGIN SELECT 数量 INTO COUNTBOOKNUM FROM BOOK WHERE 出版社 = IN1; RETURN(COUNTBOO