华南农业大学数据库综合性实验报告.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_num
varchar(12)
否
主键，唯一识别图书
书名
b_name
varchar(50)

存储书名
作者
author
varchar2(20)

存储作者名
出版社
pub_name
varchar2(20)

存储出版社名
数量
sum
number(5)

存储图书数量
学号
s_id
varchar2(25)
否
存储学号
学生姓名
s_name
varchar2(20)

存储学生姓名
学生联系电话
s_tel
varchar2(20)

存储学生联系电话
工号
a_id
varchar2(25)
否
存储工号
管理员姓名
a_name
varchar2(20)

存储管理员姓名
登陆密码
password
varchar2(25)

存储密码
管理员联系电话
a_tel
varchar2(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（图书）：
编号
字段名称
属性说明
属性
数据类型
约束
1
b_num
图书编号，非空，可自动按顺序生成
主码
varchar2(20)
Not null
2
b_name
书名
非主属性
varchar2(20)

3
author
作者
非主属性
varchar2(20)

4
pub_name
出版社
非主属性
varchar2(20)

5
sum
数量
非主属性
Number(5)

（2）表Student（学生）：
编号
字段名称
属性说明
属性
数据类型
约束
1
s_id
学号
主码
varchar2(25)
Not null
2
s_name
学生姓名
非主属性
varchar2(20)

3
s_tel
联系电话
非主属性
varchar2(20)

（3）表Admin（图书管理员）：
编号
字段名称
属性说明
属性
数据类型
约束
1
a_id
工号
主码
varchar2(25)
Not null
2
a_name
管理员姓名
非主属性
varchar2(20)

3
password
登陆密码
非主属性
varchar2(25)

4
a_tel
管理员联系电话
非主属性
varchar2(20)

2）联系集关系表：
（1）Admin与Book之间的联系：
编号
字段名称
属性说明
属性
数据类型
约束
1
a_id
工号
主码
varchar2(25)
Not null，参照Admin表的主码
2
b_num
书号
主码
varchar2(20)
Not null，参照Book表的主码
3
add_date
日期
主码
date
Not null
4
add_sum
新增数量
非主属性
Number(5)

（2）Reader与Book之间的联系：
编号
字段名称
属性说明
属性
数据类型
约束
1
b_num
图书编号
主码
varchar2(20)
Not null，参照Book表的主码
2
snum
学号
主码
varchar2(25)
Not null，参照Student表的主码
3
brr_date
借出日期
主码
date
Not null
4
re_date
归还日期
非主属性
date

5
remain
未还图书数量
非主属性
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 BOOK
SET pub_name = '呵呵哈哈出版社'
WHERE b_num =A10001;

2）修改学号为201330330205学生的联系电话：
UPDATE STUDENT
SET s_tel = '112'
WHERE s_id =201330330205;

3）修改工号为201330330305管理员的密码：
UPDATE ADMIN
SET password = a_id
WHERE 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 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,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 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 sum>1;
执行结果：

（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_COUNT
AS
V_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;
EXCEPTION
WHEN 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)
AS
BEGIN
P_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_LOG
BEFORE --触发时间为操作前
DELETE OR INSERT OR UPDATE -- 由三种事件触发
ON book
FOR EACH ROW -- 行级触发器
BEGIN
IF 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_SUM
BEFORE
UPDATE
ON BOOK
FOR EACH ROW
BEGIN
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_UPDATE
AFTER
UPDATE OF remain
ON READBOOKS
FOR EACH ROW
BEGIN
UPDATE BOOK SET BOOK.SUM=:NEW.REMAIN
WHERE 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_TIME
BEFORE
UPDATE OR INSERT OR DELETE
ON BOOK
BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN'))
OR TO_CHAR(SYSDATE,'HH24')< '08'
OR TO_CHAR(SYSDATE,'HH24')>='22' THEN
RAISE_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_BOOK
BEFORE
DROP ON SCHEMA
BEGIN
IF Sys.Dictionary_obj_name='BOOK' THEN
RAISE_APPLICATION_ERROR(-20005,'错误信息：不能删除BOOK表！');
END IF;
END;

验证触发器：
DROP TABLE book;
执行结果：

三、 设计总结
两个星期的时间非常快就过去了，这两个星期不敢说自己有多大的进步，获得了多少知识，但起码是了解了数据库设计流程。
通过这次课程设计发现我们在课堂上所学到的仅仅是皮毛，还有很多需要我们掌握的东西我根本不知道。同时也发现有很多已经学过的东西我们没有理解到位，不能很好的用来解决问题，这就需要我们不断的大量的实践，通过不断的自学，不断地发现问题，思考问题，进而解决问题。在这个过程中我们将深刻理解所学知识，同时也可以学到不少很实用的东西。
从开始的需求分析、概念结构设计、逻辑结构设计、物理结构设计。亲身体验了一回系统的设计过程。很多东西书上写的很清楚，貌似看着也很简单，思路非常清晰。但真正需要自己想办法去设计一个系统的时候才发现其中的难度。经常做到后面突然就发现自己一开始的设计有问题，然后又回去翻工，在各种反复中不