1、华南农业大学数据库综合性实验报告华南农业大学综合性设计性实验报告 实验名称：数据库设计 项目性质：综合性设计性实验 所属课程：数据库系统概念 开设时间：2015-2016学年第1学期 学生班级：软件工程2班 指导教师： 2015年12月12日 华南农业大学数学与信息学院综合性、设计性实验成绩单 开设时间：2015-2016学第一学期学号姓名成绩 题 目 图书管理系统数据库自我评价 通过对本图书管理系统数据库的模拟设计，我大体上了解的数据库设计的基础要素。这次实验以数据库为基础，运用课堂上的理论知识，对数据库进行全面的梳理，包裹实体关系模型、函数依赖、1范式、2范式、3范式和BC范式、约束条件等
2、等在数据库中的必要基础。 从开始的需求分析、概念结构设计、逻辑结构设计、物理结构设计。亲身体验了一回系统的设计过程。很多东西书上写的很清楚，貌似看着也很简单，思路非常清晰。但真正需要自己想办法去设计一个系统的时候才发现其中的难度。经常做到后面突然就发现自己一开始的设计有问题，然后又回去翻工，在各种反复过程中中不断完善自己的想法，受益匪浅。教 师 评 语评价指标：按时完成情况 优 良 中 差 数据库设计水平 优 良 中 差 SQL查询设计 优 良 中 差 完整性设计 优 良 中 差 报告结构清晰 优 良 中 差 总结和分析详尽 优 良 中 差 其它评价教师签名一、设计部分1.需求分析1.1 用户
3、需求概述 本系统为图书管理系统数据库，该系统开发的主要目的是对大学图书馆的图书信息进行记录统计管理。具体完成录入图书信息，录入借还书记录，录入读者信息等功能。需要完成的主要功能有：1）学生信息录入该功能供图书馆管理员使用，学生首次借阅图书前，必须登记并录入个人身份信息，内容包括：学号、姓名、联系电话。2）图书信息录入该功能供图书馆管理员使用，对于首次入库的新书，必须录入图书目录资料，内容包括：图书编号、书名、作者、出版社、简介。3）借书记录录入该功能供图书馆管理员使用，读者凭校园卡到柜台办理借书手续，由图书馆管理人员录入借书记录，内容包括：日期、学号、图书编号。对读者借阅图书的限制条件包括：读
4、者一次可借阅1-3本图书，借出未还的图书，累计不得超出5本；没有逾期未归还的图书，从借出之日开始计算，超过90天尚未归还则视为逾期。4）还书记录录入该功能供图书馆管理员使用，读者凭校园卡到柜台办理归还借书手续，由图书馆管理人员录入还书记录，内容包括：日期、学号、图书编号。学生可以一次归还1至多册所借阅的图书。5）图书信息查询该功能供学生使用，学生自行输入书名、作者、出版社等信息作为查询条件，系统列出满足查询条件的图书目录。6）学生信息查询该功能供图书馆管理人员使用，操作人员输入学生学号，系统列出该学生的个人信息、以及历次的借阅/归还图书记录。1.2 数据需求列名（数据项）变量名数据类型与长度空
5、否说明图书编号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)存储密码管理员联系电话
6、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都包含
7、在R的一个候选码中。（2）本关系模型的判断：对于我设计的关系模式中所有实体集和关系集，每一个属性的属性域都是不可再分的，都具有原子性，因此由该关系模式转换而来的关系模型符合第一范式的要求。对于由关系模式中的联系集转换而来的关系模型，因为它们的属性都是参加关系的实体集的主码，都完全依赖于对应关系集的主码且都是非平凡的函数依赖，因此这些关系模型满足2NF，3NF，BCNF；对于由关系模式中的实体集转换而来的关系模型，其判断如下：对于关系模式的每一个非主属性完全函数依赖于对应实体集的主码且都是非平凡的函数依赖，因此这四个关系模式满足2范式，3范式和BC范式；2）E-R图包含3个实体和2个联系，最后可
8、以转换成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_
9、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 nul
10、l2s_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书号主
11、码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）数据库中所有关系
12、模式的建表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 varch
13、ar2(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
14、, 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 nu
15、ll, 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）显示当前用户的所有
16、表：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 BO
17、OK 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
18、 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 (201330
19、330301,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 RE
20、ADBOOKS 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）修改学号为201330330
21、205学生的联系电话：UPDATE STUDENTSET s_tel = 112WHERE s_id =201330330205;3）修改工号为201330330305管理员的密码：UPDATE ADMINSET password = a_idWHERE a_id =201330330305;4）根据其他表修改数据： UPDATE BOOK SET (sum) =sum+(SELECT add_sum FROM addbooks ) WHERE book.b_num = addbooks.b_num;2.3 删除1）删除学号为201330330205的学生： DELETE FROM stude
22、nt 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
23、OR REPLACE VIEW book_author(name,author,pub_com) AS SELECT b_name,author,pub_name FROM book,addbooks WHERE 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
24、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:RE
25、ADBOOKS: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）显
26、示图书编号及录入借该图书信息的管理员姓名和密码： 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日所借图书的编号
27、和未还图书数量： 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 显示图书信息并统计出共
28、有多少种图书： 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 给图书
