数据库实验八综合性实验报告

华南农业大学综合性设计性

实验报告

实验名称：数据库设计

项目性质：综合性设计性实验

所属课程：数据库系统概念

开设时间：2015-2016学年第1学期学生班级：

指导教师：

2015年12月12日

华南农业大学数学与信息学院

综合性、设计性实验成绩单

开设时间：2015-2016学第一学期

一、设计部分

1.需求分析

1.1 用户需求概述

本系统为图书管理系统数据库，该系统开发的主要目的是对大学图书馆的图书信息进行记录统计管理。具体完成录入图书信息，录入借还书记录，录入读者信息等功能。需要完成的主要功能有：

1）学生信息录入

该功能供图书馆管理员使用，学生首次借阅图书前，必须登记并录入个人身份信息，内容包括：学号、姓名、联系电话。

2）图书信息录入

该功能供图书馆管理员使用，对于首次入库的新书，必须录入图书目录资料，内容包括：图书编号、书名、作者、出版社、简介。

3）借书记录录入

该功能供图书馆管理员使用，读者凭校园卡到柜台办理借书手续，由图书馆管理人员录入借书记录，内容包括：日期、学号、图书编号。

对读者借阅图书的限制条件包括：

①读者一次可借阅1-3本图书，

②借出未还的图书，累计不得超出5本；

③没有逾期未归还的图书，从借出之日开始计算，超过90天尚未归还则视为逾期。

4）还书记录录入

该功能供图书馆管理员使用，读者凭校园卡到柜台办理归还借书手续，由图书馆管理人员录入还书记录，内容包括：日期、学号、图书编号。学生可以一次归还1至多册所借阅的图书。

5）图书信息查询

该功能供学生使用，学生自行输入书名、作者、出版社等信息作为查询条件，

系统列出满足查询条件的图书目录。

6）学生信息查询

该功能供图书馆管理人员使用，操作人员输入学生学号，系统列出该学生的个人信息、以及历次的借阅/归还图书记录。

1.2 数据需求

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（图书）：

（2）表Student（学生）：

（3）表Admin（图书管理员）：

2）联系集关系表：

（1）Admin与Book之间的联系：

（2）Reader与Book之间的联系：

二、系统功能部分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 V ALUES (‘A10000’,'C语言','苏菲','哈哈呵呵出版社',5); INSERT INTO BOOK V ALUES (‘A10001’,'数据库','玛索','哈哈呵呵出版社',5); INSERT INTO BOOK V ALUES (‘B10000’,'大学语文','刘德华','人民教育出版社',5); INSERT INTO BOOK V ALUES (‘C10000’,'自然与科学','王大陆','清华大学出版社',5); INSERT INTO BOOK V ALUES (‘C10001’,'十万个为什么','陶敏敏','清华大学出版社',5);

INSERT INTO STUDENT V ALUES ('201330330201','张三','110');

INSERT INTO STUDENT V ALUES ('201330330202','李四','11010');

INSERT INTO STUDENT V ALUES ('201330330203','王五','10086');

INSERT INTO STUDENT V ALUES ('201330330204','欧阳','119');

INSERT INTO STUDENT V ALUES ('201330330205','非凡','110');

INSERT INTO ADMIN V ALUES ('201330330301','徐太宇','123456','120');

INSERT INTO ADMIN V ALUES ('201330330302','林真心','123456','114');

INSERT INTO ADMIN V ALUES ('201330330303','范冰冰','123456','12306');

INSERT INTO ADMIN V ALUES ('201330330304','周迅','123456','360');

INSERT INTO ADMIN V ALUES ('201330330305','李冰冰','123456','361');

INSERT INTO ADDBOOKS V ALUES ('201330330301','A10000','15-12月-10',5);

INSERT INTO ADDBOOKS V ALUES ('201330330302','A10001','15-12月-10',5);

INSERT INTO ADDBOOKS V ALUES ('201330330303','B10000','15-12月-10',5);

INSERT INTO ADDBOOKS V ALUES ('201330330204','C10000','15-12月-10',5);

INSERT INTO READBOOKS V ALUES ('A10000','201330330201','15-12月-10','15-12月-10',4); INSERT INTO READBOOKS V ALUES ('A10001','201330330202','15-12月-10','15-12月-10',4); INSERT INTO READBOOKS V ALUES ('B10000','201330330203','15-12月-10','15-12月-10',4); INSERT INTO READBOOKS V ALUES ('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 A VG(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 V ARCHAR2(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 V ARCHAR2) AS

BEGIN