华南农业大学数据库综合性实验报告.doc

华南农业大学综合性设计性实验报告 实验名称：数据库设计 项目性质：综合性设计性实验 所属课程：数据库系统概念 开设时间：2015-2016学年第1学期 学生班级：软件工程2班 指导教师： 2015年12月12日 华南农业大学数学与信息学院综合性、设计性实验成绩单 开设时间：2015-2016学第一学期学号姓名成绩 题 目 图书管理系统数据库自我评价 通过对本图书管理系统数据库的模拟设计，我大体上了解的数据库设计的基础要素。这次实验以数据库为基础，运用课堂上的理论知识，对数据库进行全面的梳理，包裹实体关系模型、函数依赖、1范式、2范式、3范式和BC范式、约束条件等等在数据库中的必要基础。 从开始的需求分析、概念结构设计、逻辑结构设计、物理结构设计。亲身体验了一回系统的设计过程。很多东西书上写的很清楚，貌似看着也很简单，思路非常清晰。但真正需要自己想办法去设计一个系统的时候才发现其中的难度。经常做到后面突然就发现自己一开始的设计有问题，然后又回去翻工，在各种反复过程中中不断完善自己的想法，受益匪浅。教 师 评 语评价指标：l 按时完成情况 优 良 中 差 l 数据库设计水平 优 良 中 差 l SQL查询设计 优 良 中 差 l 完整性设计 优 良 中 差 l 报告结构清晰 优 良 中 差 l 总结和分析详尽 优 良 中 差 其它评价教师签名一、设计部分1.需求分析1.1 用户需求概述 本系统为图书管理系统数据库，该系统开发的主要目的是对大学图书馆的图书信息进行记录统计管理。具体完成录入图书信息，录入借还书记录，录入读者信息等功能。需要完成的主要功能有：1）学生信息录入该功能供图书馆管理员使用，学生首次借阅图书前，必须登记并录入个人身份信息，内容包括：学号、姓名、联系电话。2）图书信息录入该功能供图书馆管理员使用，对于首次入库的新书，必须录入图书目录资料，内容包括：图书编号、书名、作者、出版社、简介。3）借书记录录入该功能供图书馆管理员使用，读者凭校园卡到柜台办理借书手续，由图书馆管理人员录入借书记录，内容包括：日期、学号、图书编号。对读者借阅图书的限制条件包括：读者一次可借阅1-3本图书，借出未还的图书，累计不得超出5本；没有逾期未归还的图书，从借出之日开始计算，超过90天尚未归还则视为逾期。4）还书记录录入该功能供图书馆管理员使用，读者凭校园卡到柜台办理归还借书手续，由图书馆管理人员录入还书记录，内容包括：日期、学号、图书编号。学生可以一次归还1至多册所借阅的图书。5）图书信息查询该功能供学生使用，学生自行输入书名、作者、出版社等信息作为查询条件，系统列出满足查询条件的图书目录。6）学生信息查询该功能供图书馆管理人员使用，操作人员输入学生学号，系统列出该学生的个人信息、以及历次的借阅/归还图书记录。1.2 数据需求列名（数据项）变量名数据类型与长度空否说明图书编号b_numvarchar(12)否主键，唯一识别图书书名b_namevarchar(50)存储书名作者authorvarchar2(20)存储作者名出版社pub_namevarchar2(20)存储出版社名数量sumnumber(5)存储图书数量学号s_idvarchar2(25)否存储学号学生姓名s_namevarchar2(20)存储学生姓名学生联系电话s_telvarchar2(20)存储学生联系电话工号a_idvarchar2(25)否存储工号管理员姓名a_namevarchar2(20)存储管理员姓名登陆密码passwordvarchar2(25)存储密码管理员联系电话a_telvarchar2(20)存储管理员联系电话2.概念设计内容2.1 概念设计综述以下用3个实体集和2个联系集实现图书管理系统的E-R图。2.2 概念模型完整的E-R图：3.数据库逻辑设计3.1 逻辑设计综述1）对关系表的设计进行规范化（1）范式的判断条件： 第一范式：如果一个关系模式R的所有属性域都是原子的，我们称关系模式R属于第一范式； 第二范式：若关系模式R满足第一范式，且每一个非主属性完全函数依赖于码，则关系模式R满足第二范式； BC范式：对于R中所有的函数依赖，下面至少有一个成立（是平凡的，是模式R的一个超码）；第三范式：在BC范式的判断条件上加多一个条件：-中每个属性A都包含在R的一个候选码中。（2） 本关系模型的判断：对于我设计的关系模式中所有实体集和关系集，每一个属性的属性域都是不可再分的，都具有原子性，因此由该关系模式转换而来的关系模型符合第一范式的要求。对于由关系模式中的联系集转换而来的关系模型，因为它们的属性都是参加关系的实体集的主码，都完全依赖于对应关系集的主码且都是非平凡的函数依赖，因此这些关系模型满足2NF，3NF，BCNF；对于由关系模式中的实体集转换而来的关系模型，其判断如下：对于关系模式的每一个非主属性完全函数依赖于对应实体集的主码且都是非平凡的函数依赖，因此这四个关系模式满足2范式，3范式和BC范式；2） E-R图包含3个实体和2个联系，最后可以转换成5个关系表：（1）实体集关系表：Book=(b_num,b_name,author,pub_name,sum)图书=图书编号+书名+作者+出版社+数量Student=(s_id,s_name,s_tel)学生=学号+姓名+联系电话Admin=(a_id,a_name,password,a_tel)图书管理员=工号+姓名+登陆密码+电话（2）联系集关系表：Admin与Book之间的联系：AddBooks=(a_id,b_num,add_date,add_sum,)图书信息录入=工号+书号+日期+新增数量Reader与Book之间的联系：ReadBooks=(b_num,s_id,brr_date,re_date,remain)借阅图书=图书编号+学号+借出日期+归还日期+未还图书数量3.2 关系模型1） 实体集关系表：（1）表Book（图书）：编号字段名称属性说明属性数据类型约束1b_num图书编号，非空，可自动按顺序生成主码varchar2(20)Not null2b_name书名非主属性varchar2(20)3author作者非主属性varchar2(20)4pub_name出版社非主属性varchar2(20)5sum数量非主属性Number(5)（2）表Student（学生）：编号字段名称属性说明属性数据类型约束1s_id学号主码varchar2(25)Not null2s_name学生姓名非主属性varchar2(20)3s_tel联系电话非主属性varchar2(20)（3）表Admin（图书管理员）：编号字段名称属性说明属性数据类型约束1a_id工号主码varchar2(25)Not null2a_name管理员姓名非主属性varchar2(20)3password登陆密码非主属性varchar2(25)4a_tel管理员联系电话非主属性varchar2(20)2）联系集关系表：（1）Admin与Book之间的联系：编号字段名称属性说明属性数据类型约束1a_id工号主码varchar2(25)Not null，参照Admin表的主码2b_num书号主码varchar2(20)Not null，参照Book表的主码3add_date日期主码dateNot null4add_sum新增数量非主属性Number(5)（2）Reader与Book之间的联系：编号字段名称属性说明属性数据类型约束1b_num图书编号主码varchar2(20)Not null，参照Book表的主码2snum学号主码varchar2(25)Not null，参照Student表的主码3brr_date借出日期主码dateNot null4re_date归还日期非主属性date5remain未还图书数量非主属性Number(5)二、 系统功能部分1建表1）数据库中所有关系模式的建表SQL语句：create table Book( b_num varchar2(20) not null, s_name varchar2(20) null, author varchar2(20) null, pub_name varchar2(20) null, sum Number(5) default 0 check(sum=0), constraint pk_b primary key (b_num);create table Student( s_id varchar2(25) not null, s_name varchar2(20) null, s_tel varchar2(20) null, constraint pk_r primary key (s_id); create table Admin( a_id varchar2(25) not null, a_name varchar2(20) null, password varchar2(25) default 123456, a_tel varchar2(20) null, constraint pk_a primary key (a_id);create table AddBooks( a_id varchar2(25) not null, b_num varchar2(20) not null, add_date date default sysdate, add_sum Number(5) default 0 check(add_sum=0), constraint pk_ab primary key (a_id,b_num,add_date), constraint fk_aba foreign key(a_id) references Admin(a_id), constraint fk_abb foreign key(b_num) references Book(b_num);create table ReadBooks( b_num varchar2(20) not null, s_id varchar2(25) not null, brr_date date default sysdate, re_date date null, remain Number(5) check(remain=0), constraint pk_rb primary key (b_num,s_id,brr_date), constraint fk_rbr foreign key(s_id) references Reader(s_id), constraint fk_rbb foreign key(b_num) references Book(b_num);2）显示当前用户的所有表：SELECT object_name FROM user_objects WHERE object_type=TABLE;执行结果：2. 修改表记录2.1 增加INSERT INTO BOOK VALUES (A10000,C语言,苏菲,哈哈呵呵出版社,5);INSERT INTO BOOK VALUES (A10001,数据库,玛索,哈哈呵呵出版社,5);INSERT INTO BOOK VALUES (B10000,大学语文,刘德华,人民教育出版社,5);INSERT INTO BOOK VALUES (C10000,自然与科学,王大陆,清华大学出版社,5);INSERT INTO BOOK VALUES (C10001,十万个为什么,陶敏敏,清华大学出版社,5);INSERT INTO STUDENT VALUES (201330330201,张三,110);INSERT INTO STUDENT VALUES (201330330202,李四,11010);INSERT INTO STUDENT VALUES (201330330203,王五,10086);INSERT INTO STUDENT VALUES (201330330204,欧阳,119);INSERT INTO STUDENT VALUES (201330330205,非凡,110);INSERT INTO ADMIN VALUES (201330330301,徐太宇,123456,120);INSERT INTO ADMIN VALUES (201330330302,林真心,123456,114);INSERT INTO ADMIN VALUES (201330330303,范冰冰,123456,12306);INSERT INTO ADMIN VALUES (201330330304,周迅,123456,360);INSERT INTO ADMIN VALUES (201330330305,李冰冰,123456,361);INSERT INTO ADDBOOKS VALUES (201330330301,A10000,15-12月-10,5);INSERT INTO ADDBOOKS VALUES (201330330302,A10001,15-12月-10,5);INSERT INTO ADDBOOKS VALUES (201330330303,B10000,15-12月-10,5);INSERT INTO ADDBOOKS VALUES (201330330204,C10000,15-12月-10,5);INSERT INTO READBOOKS VALUES (A10000,201330330201,15-12月-10,15-12月-10,4);INSERT INTO READBOOKS VALUES (A10001,201330330202,15-12月-10,15-12月-10,4);INSERT INTO READBOOKS VALUES (B10000,201330330203,15-12月-10,15-12月-10,4);INSERT INTO READBOOKS VALUES (C10000,201330330204,15-12月-10,15-12月-10,4);2.2 修改1）修改编号为A10001的图书出版社：UPDATE BOOKSET pub_name = 呵呵哈哈出版社WHERE b_num =A10001;2）修改学号为201330330205学生的联系电话：UPDATE STUDENTSET s_tel = 112WHERE s_id =201330330205;3）修改工号为201330330305管理员的密码：UPDATE ADMINSET password = a_idWHERE a_id =201330330305;4）根据其他表修改数据：UPDATE BOOKSET (sum) =sum+(SELECT add_sum FROM addbooks )WHERE book.b_num = addbooks.b_num;2.3 删除1）删除学号为201330330205的学生：DELETE FROM student WHERE s_id=201330330205;2）删除工号为201330330305的管理员：DELETE FROM admin WHERE a_id=201330330305;3）删除图书编号为C10001的图书：DELETE FROM book WHERE b_num=C10001;3. 视图1）创建图书作者视图：CREATE VIEW book_author(book,author) AS SELECT b_name,author FROM book; 查询视图：SELECT * FROM book_author; 执行结果：2）修改作者视图，加入出版社名称。CREATE OR REPLACE VIEW book_author(name,author,pub_com) AS SELECT b_name,author,pub_name FROM book,addbooksWHERE book.pub_name=addbooks.pub_name; 查询视图：SELECT * FROM book_author;执行结果：3）创建清华大学出版社的图书视图：CREATE VIEW tsinghua_book AS SELECT b_name,author FROM book WHERE pub_name= 清华大学出版社; 查询视图：SELECT * FROM tsinghua_book;执行结果：4）创建Book表的一个统计视图CREATE VIEW statistics(avg_sum)AS SELECT AVG(SUM) FROM BOOK 查询统计表：SELECT * FROM statistics; 执行结果：4 查询1） 基本查询分别执行以下语句： select * from Book; select * from Student; select * from Admin; select * from AddBooks;select * from ReadBooks;执行结果：BOOK:STUDENT:ADMIN:ADDBOOKS:READBOOKS:2）多表联合查询：（1）显示图书书名以及借该图书学生的学号和借书日期： SELECT book.b_name,readbooks.s_id,readbooks.brr_date FROM book ,readbooks WHERE book.b_num=readbooks.b_num; 执行结果：（2）显示图书书名录入借该图书信息的管理员工号和录入日期：SELECT book.b_name,addbooks.a_id,addbooks.add_date FROM book ,addbooks WHERE book.b_num=addbooks.b_num; 执行结果：（3）显示图书编号及录入借该图书信息的管理员姓名和密码：SELECT addbooks.b_num,admin.a_id,admin_password FROM book ,addbooks WHERE addbooks.a_id=admin.a_id;执行结果：3）多条件组合查询：（1）显示哈哈呵呵出版社和呵呵哈哈出版社图书数量大于1的图书信息：SELECT * FROM book WHERE (pub_name=哈哈呵呵出版社 OR pub_name=呵呵哈哈出版社) AND sum1;执行结果：（2）显示学生201330330203和201330330204在15年12月10日所借图书的编号和未还图书数量： SELECT * FROM readbooks WHERE (s_id=201330330203 OR s_id=201330330204) AND brr_date=15-12月-10 执行结果：（3）显示工号201330330303和201330330304在15年12月10日所录入图书的编号和添加数量：SELECT * FROM readbooks WHERE (s_id=201330330203 OR s_id=201330330204) AND brr_date=15-12月-10 执行结果：5. 存储过程（1） 存储过程：BOOK_LIST 显示图书信息并统计出共有多少种图书： CREATE OR REPLACE PROCEDURE BOOK_COUNTASV_TOTAL NUMBER(10);BEGIN SELECT COUNT(*) INTO V_TOTAL FROM BOOK; DBMS_OUTPUT.PUT_LINE(图书种类总数为：|V_TOTAL);END;CREATE OR REPLACE PROCEDURE BOOK_LIST AS CURSOR book_cursor IS SELECT b_num,b_name FROM book;BEGIN FOR book_record IN book_cursor LOOP DBMS_OUTPUT.PUT_LINE(book_record.b_num|book_record.b_name);END LOOP;DEPT_COUNT;-调用存储过程 END;执行语句：EXECUTE BOOK_LIST;显示结果：（2）存储过程：CHANGE_SALARY 给图书增加数量通过IN类型的参数传递要增加的图书编号和增加的数量： CREATE OR REPLACE PROCEDURE CHANGE_SUM(P_BNUM IN VARCHAR2 DEFAULT A10000,P_RAISE NUMBER DEFAULT 2) AS V_BNAME VARCHAR2(20); V_SUM NUMBER(5); BEGIN SELECT B_NAME,SUM INTO V_BNAME,V_SUM FROM BOOK WHERE B_NUM=P_BNUM;UPDATE BOOK SET SUM=SUM+P_RAISE WHERE B_NUM=P_BNUM;DBMS_OUTPUT.PUT_LINE(图书|V_BNAME|的数量改为|TO_CHAR(V_SUM+P_RAISE); COMMIT;EXCEPTIONWHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(发生错误，修改失败！); ROLLBACK; END;执行语句：EXECUTE CHANGE_SUM；执行结果：3）存储过程：ADD_REGION CHANGE_TEL 使用IN OUT类型的参数，给学生联系电话号增加区号： CREATE OR REPLACE PROCEDURE ADD_REGION(P_TEL IN OUT VARCHAR2)ASBEGINP_TEL:=024-|P_TEL;END; CREATE OR REPLACE PROCEDURE CHANGE_TEL(P_SID IN VARCHAR2 DEFAULT 201330330201) AS V_SNAME VARCHAR2(20); V_STEL VARCHAR2(20); BEGIN SELECT S_NAME,S_TEL INTO V_SNAME,V_STEL FROM STUDENT WHERE S_ID=P_SID; ADD_REGION(V_STEL);-调用存储过程 UPDATE STUDENT SET S_TEL=V_STEL WHERE S_ID=P_SID; DBMS_OUTPUT.PUT_LINE(学生|V_SNAME|的联系电话改为|V_STEL); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(发生错误，修改失败！); ROLLBACK; END;执行语句：EXECUTE CHANGE_TEL；执行结果：6. 触发器1）DML行级触发器：DML_LOG（行级触发器，创建包含插入、删除、修改多种触发事件的对BOOK表的操作进行记录）创建记录表LOGS：CREATE TABLE logs(LOG_ID NUMBER(10) PRIMARY KEY,LOG_TABLE VARCHAR2(10) NOT NULL,LOG_DML VARCHAR2(10),LOG_KEY_ID NUMBER(10),LOG_DATE DATE,LOG_USER VARCHAR2(15);创建LOGS表的主键序列LOGS_ID_SEQ：CREATE SEQUENCE logs_id_squ INCREMENT BY 1 START WITH 1 MAXVALUE 9999999 NOCYCLE NOCACHE;创建和编译触发器：CREATE OR REPLACE TRIGGER DML_LOGBEFORE -触发时间为操作前DELETE OR INSERT OR UPDATE - 由三种事件触发ON bookFOR EACH ROW - 行级触发器BEGINIF INSERTING THEN INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,BOOK,INSERT,:new.b_num,SYSDATE,USER); ELSIF DELETING THEN INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,BOOK,DELETE,:old.b_num,SYSDATE,USER); ELSE INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,EMP,UPDATE,:new.b_num,SYSDATE,USER); END IF;END;在BOOK表中插入记录：INSERT INTO book(b_num,b_name,author,pub_name,sum) VALUES(C10001,十万个为什么,陶敏敏,清华大学出版社，0);COMMIT;检查LOGS表中记录的信息：SELECT * FROM LOGS;执行结果：2）DML行级触发器：CHECK_SUM（行级触发器，限定对BOOK表的修改，只能图书编号A10000的图书数量。） 创建记录表LOGERR： CREATE TABLE logerr (NUM NUMBER(10) NOT NULL, MESSAGE VARCHAR2(50) NOT NULL-记录错误信息);创建和编译以下触发器：CREATE OR REPLACE TRIGGER CHECK_SUMBEFOREUPDATEON BOOKFOR EACH ROWBEGIN IF :new.B_NUM!=A10000 THEN RAISE_APPLICATION_ERROR(-20001, 工资修改超出范围,操作取消！); END IF;END;在BOOK表中更新记录： UPDATE book SET sum=5 WHERE b_num=A10000; UPDATE book SET sum=3 WHEREb_num=A10001; COMMIT;执行结果：3）DML行级触发器：CASCADE_UPDATE（行级触发器，当修改READBOOKS表的REMAIN时，BOOK表的SUM也自动修改。）创建和编译以下触发器：CREATE TRIGGER CASCADE_UPDATEAFTER UPDATE OF remainON READBOOKSFOR EACH ROWBEGINUPDATE BOOK SET BOOK.SUM=:NEW.REMAINWHERE BOOK.B_NUM=READBOOKS.B_NUM;END;验证触发器：UPDATE readbooks SET remain=3 WHERE b_num=A10001;COMMIT;执行查询： SELECT * FROM readbooks;SELECT * FROM book;执行结果：READBOOKS:BOOKS:4）DML语句级触发器：CHECK_TIME（限定对表BOOK表的修改时间为周一至周五的早8点至晚22点。）创建和编译触发器：CREATE OR REPLACE TRIGGER CHECK_TIMEBEFOREUPDATE OR INSERT OR DELETE ON BOOKBEGIN IF (TO_CHAR(SYSDATE,DY) IN (SAT,SUN) OR TO_CHAR(SYSDATE,HH24)=22 THENRAISE_APPLICATION_ERROR(-20500,非法时间修改表错误！);END IF; END;当前时间为18点50分，在BOOK表中修改记录：UPDATE BOOK SET SUM=5 WHERE B_NUM=A10000;执行结果：5）DDL触发器：NODROP_BOOK（通过触发器阻止对BOOK表的删除。）创建和编译触发器：CREATE OR REPLACE TRIGGER NODROP_BOOKBEFOREDROP ON SCHEMA BEGINIF Sys.Dictionary_obj_name=BOOK THENRAISE_APPLICATION_ERROR(-20005,错误信息：不能删除BOOK表！);END IF; END;验证触发器：DROP TABLE book;执行结果：三、 设计总结两个星期的时间非常快就过去了，这两个星期不敢说自己有多大的进步，获得了多少知识，但起码是了解了数据库设计流程。通过这次课程设计发现我们在课堂上所学到的仅仅是皮毛，还有很多需要我们掌握的东西我根本不知道。同时也发现有很多已经学过的东西我们没有理解到位，不能很好的用来解决问题，这就需要我们不断的大量的实践，通过不断的自学，不断地发现问题，思考问题，进而解决问题。在这个过程中我们将深刻理解所学知识，同时也可以学到不少很实用的东西。 从开始的需求分析、概念结构设计、逻辑结构设计、物理结构设计。亲身体验了一回系统的设计过程。很多东西书上写的很清楚，貌似看着也很简单，思路非常清晰。但真正需要自己想办法去设计一个系统的时候才发现其中的难度。经常做到后面突然就发现自己一开始的设计有问题，然后又回去翻工，在各种反复中不