数据库大作业实验报告

Oracle大作业设计报告

题目：高校运动会分数统计系统学生姓名：黄国栋

学号：22、20

专业班级：计科14101

同组姓名: 胡君阳

指导教师：汤海蓉

设计时间： 2016 年下学期

一.需求分析

本文按照数据库系统设计的基本步骤，采取了事先进行需求分析，然后进行数据库的概念设计和逻辑结构设计，最后进行数据库详细设计的方法，完成了一个高校运动会分数统计系统的设计。

1.1、系统功能实现

（1）运动员信息管理、运动项目管理、成绩统计等功能，包括运动员信

息的添加、修改、删除及查询；

（2）运动项目的添加、修改、删除及查询；

（3）各项目分数查询等功能

1.2、系统功能要求

1) 概念结构设计：在需求分析和总体功能设计的基础上，设计 e-r 模型，

详细描述实体的属性和实体之间的联系。

2) 逻辑结构设计：实现 e-r 图向关系模型的转换，详细列表显示完整的数

据库表结构。

2、创建表空间

3、在表空间中创建表

在 sql*plus 环境中输入 pl/sql 代码，创建数据库中的各个表，至少包括运动员信息表、运动项目表、成绩表等 5张表（自行设计用到的表，注意表中字

段的设置及类型）；

向表中插入数据，每张表至少插入 10条数据，数据要合理。

4、索引和完整性

在表上建立相应索引。

实现列、行及参照完整性（必须建立主键、外键、检查约束等）。

5、数据查询和视图

使用 select 语句实现运动员信息、项目信息、成绩信息等的查询（要求查询要和系统的功能联系起来，做单条件的查询、组合条件的查询、多表查询等，

至少完成 10个查询）。

创建视图查询各个院系的总分。

6、存储过程和触发器的使用

创建存储过程实现表中数据的修改和删除。

创建函数统计教工的参赛人数。

创建触发器实现如下功能：一个运动员至多参加四个项目。

7、用户、角色

创建用户并授权，将其赋予某个角色成为其成员。

不同的用户授予不同的权限，管理员的权限最高，可以对各种信息进行修改删除等操作。普通用户只能进行查询操作。

8、系统数据维护

导出数据库数据

导入数据库数据

二.总体功能设计

根据系统功能的需求分析和高校运动会的特点，经过模块化的分析得到如下图2-1所示的系统功能模块结构图。

图2-1

三.数据库结构设计

3.1、概念结构设计

将高校运动会分数统计系统转换为关系数据库的数据模型，其关系模式为：1）院系表（学院+总成绩）其中学院为主码，总成绩默认值为0。

2）运动员信息表（运动员编号+姓名+性别+年龄+学院+参加项目数）其中运动员编号为主码，学院为外码。

3）运动项目表（项目编号+项目名称+举行时间+举行地点）其中项目编号为主码。

4）参加记录表（记录编号+运动员编号+项目编号）其中记录编号为主码，运动员编号，项目编号为外码。

5）成绩表（成绩编号+运动员编号+姓名+项目编号+项目名称+学院+成绩）其中成绩编号为主码，运动员编号和项目编号为外码。

3.2、数据库结构详细设计

经过检查，此数据库的详细逻辑设计满足第3范式的要求。高校运动成绩管理系统中各表的属性设计情况如下：

表3-1 院系表

表3-2 运动员信息表

表3-3 运动项目表

表3-4 参加记录表

表3-5 成绩表

四.数据库的建立

具体SQL代码如下：

1、创建表空间和用户

create user HGD identified by 123456 //创建用户

grant all privileges to HGD //授权

create tablespace myspace 'G:\myspace.ora' size 100M // 创建表空间alter user HGD default tablespace myspace //连接表空间

2、创建表

院系表

create table 院系表(

学院 varchar(40),

总成绩 number (3) default 0,

primary key (学院)

)

运动员信息表

create table 运动员信息表(

运动员编号 number(5),

姓名 varchar(20),

性别 varchar(2) check(性别 in('男','女')),

年龄 number(3),

学院 varchar(40),

参加项目数 number(1) default 0,

primary key(运动员编号),

foreign key(学院) references 院系表 (学院)

)

运动项目表

create table 运动项目表(

项目编号 number(2),

primary key(项目编号),

项目名称 varchar(20),

举行时间 varchar(20),

举行地点 varchar(20),

)

参加记录表

create table 参加记录表 (

记录编号 number(2),

运动员编号 number(2),

项目编号 number(2),

primary key (记录编号),

foreign key (运动员编号) references 运动员信息表 (运动员编号), foreign key(项目编号) references 运动项目表 (项目编号)

)

成绩表

create table 成绩表(

成绩编号 number (5),

运动员编号 number(5),

姓名 varchar(20),

项目编号 number(2),

项目名称 varchar(20),

学院 varchar(40) not null,

成绩 number(5) default 0,

primary key(运动员编号,项目编号),

foreign key(运动员编号) references 运动员信息表 (运动员编号), foreign key(项目编号) references 运动项目表 (项目编号)

)

3、插入数据

院系表

insert into 院系表(学院) values('计算机');

insert into 院系表(学院) values('生科');

insert into 院系表(学院) values('电气');

insert into 院系表(学院) values('机械');

insert into 院系表(学院) values('芙蓉');

insert into 院系表(学院) values('美术');

insert into 院系表(学院) values('体育');

insert into 院系表(学院) values('艺传');

insert into 院系表(学院) values('物电');

insert into 院系表(学院) values('外国语');

运动员信息表

insert into 运动员信息表 values(1,'张一','男',18,'计算机',0); insert into 运动员信息表 values(2,'张二','男',18,'生科',0); insert into 运动员信息表 values(3,'张三','男',18,'电气',0); insert into 运动员信息表 values(4,'张四','男',18,'机械',0); insert into 运动员信息表 values(5,'张五','男',18,'芙蓉',0); insert into 运动员信息表 values(6,'张六','男',18,'美术',0); insert into 运动员信息表 values(7,'张七','男',18,'体育',0); insert into 运动员信息表 values(8,'张八','男',18,'艺传',0); insert into 运动员信息表 values(9,'张九','男',18,'物电',0); insert into 运动员信息表 values(10,'张十','男',18,'外国语',0);

运动项目表

insert into 运动项目表 values(1,'100米','1:00','场地1'); insert into 运动项目表 values(2,'200米','2:00','场地2'); insert into 运动项目表 values(3,'300米','3:00','场地3'); insert into 运动项目表 values(4,'400米','4:00','场地4'); insert into 运动项目表 values(5,'500米','5:00','场地5'); insert into 运动项目表 values(6,'600米','6:00','场地6'); insert into 运动项目表 values(7,'700米','7:00','场地7'); insert into 运动项目表 values(8,'800米','8:00','场地8'); insert into 运动项目表 values(9,'900米','9:00','场地9'); insert into 运动项目表 values(10,'1000米','10:00','场地10');

参加记录表

insert into 参加记录表 values（1,1,1）；

insert into 参加记录表 values（2,2,2）；

insert into 参加记录表 values（3,3,3）；

insert into 参加记录表 values（4,4,4）；

insert into 参加记录表 values（5,5,5）；

insert into 参加记录表 values（6,6,6）；

insert into 参加记录表 values（7,7,7）；

insert into 参加记录表 values（8,8,8）；

insert into 参加记录表 values（9,9,9）；

insert into 参加记录表 values（10,10,10）；

成绩表

insert into 成绩表 values（1,1,'张一',1,'100米','计算机',68）

insert into 成绩表 values（2,2,'张二',2,'200米','生科',50）

insert into 成绩表 values（3,3,'张三',3,'300米','电气',85）

insert into 成绩表 values（4,4,'张四',4,'400米','机械',90）

insert into 成绩表 values（5,5,'张五',5,'500米','芙蓉',75）

insert into 成绩表 values（6,6,'张六',6,'600米','美术',63）

insert into 成绩表 values（7,7,'张七',7,'700米','体育',95）

insert into 成绩表 values（8,8,'张八',8,'800米','艺传',61）

insert into 成绩表 values（9,9,'张九',9,'900米','物电',76）

insert into 成绩表 values（10,10,'张十',10,'1000米','外国语',48）

4、索引的建立

create index 成绩表_成绩_index on 成绩表(成绩)

create index 运动员信息表_运动员编号_index on 运动员信息表(运动员编号)

create index 运动项目表_项目编号_index on 运动项目表(项目编号) create index 院系表_总成绩_index on 院系表(总成绩)

5、数据查询和创建视图

查询数据

select * from 院系表;

select * from 运动员信息表;

select * from 运动项目表;

select * from 参加记录表;

select * from 成绩表;

select * from 成绩表 order by 成绩;

select 学院,姓名,成绩 from 成绩表;

select * from 运动员信息表,运动项目表 where 项目名称 = '800米'; select * from 运动项目表,成绩表 where 运动项目表.项目编号 = 成绩表.项目编号 and 成绩 between 60 and 75;

select * from 参加记录表,运动员信息表 where 运动员信息表.运动员编号 = 参加记录表.运动员编号 and 学院 = '计算机';

select * from 运动项目表,成绩表

where 运动项目表.项目编号 = 成绩表.项目编号 and 成绩 between 60 to 75; select * from 参加记录表,运动员信息表

where 运动员信息表.运动员编号 = 参加记录表.运动员编号 and 学院 = '计算机';

//创建视图

create or replace view 学院排行榜 as

select 学院,总成绩 from 院系表

select 总成绩 from 学院排行榜;

6、存储过程和触发器的使用

//创建修改_存储过程

create procedure 修改运动员信息表数据 (in1 varchar,in2 varchar) as

begin

update 运动员信息表 set 学院 = in1 where 姓名 = in2;

commit;

end;

//执行

begin

修改运动员信息表数据('生科','张三');

end;

//创建删除_存储过程

create procedure 删除运动员信息表一条记录 (in1 varchar) as begin

delete from 运动员信息表 where 姓名 = in1;

commit;

end;

//执行

begin

删除运动员信息表一条记录('张三');

end;

//创建函数—统计运动员人数

create or replace function function_getcount

return number

is

sumcount number;

begin

select count(*) into sumcount from 运动员信息表;

dbms_output.put_line('运动员总数为:');

dbms_output.put_line(sumcount);

return(sumcount);

end;

//执行

select function_getcount from dual;

//创建触发器_一个运动员最多报4个项目

create or replace trigger test1 after

insert on 参加记录表

for each row

declare

n number;

exc exception;

begin

select 参加项目数 into n from 运动员信息表 where 运动员编号=:new.运动员编号;

if n>=3 then

raise exc;

else

update 运动员信息表 set 参加项目数=n+1 where 运动员编号=:new.运动员编号;

dbms_output.put_line('插入成功！');

end if;

exception

when exc then

dbms_output.put_line('运动员已经报了4门了，超出上线！');

rollback;

end;

//创建触发器_更新院系表总成绩

create or replace trigger addgarde

before insert on 成绩表

for each row

declare

m number(3);

begin

select 总成绩 into m from 院系表 where 学院=:new.学院;

update 院系表 set 总成绩=m+:new.成绩 where 学院=:new.学院;

end;

7、用户、角色

//创建一个管理员用户

create user manager identified by 123456 default tablespace myspace quota 500m on myspace;

授权给管理员

grant all privileges to manager;

//创建角色

create role onetest8

grant create session to onetest8;

grant select any table to onetest8;

//创建用户并将角色onetest8的权利给它

create user onetest9 identified by 123456 default tablespace myspace quota 500m on myspace;

grant onetest8 to onetest9

五.心得体会

由于在数据库的设计初期，没有对高校运动成绩管理系统进行准确地分析，导致后期设计过程中的数据库的触发器阶段设计无法进行，而因此浪费了许多时间。所以，通过此次数据库课程设计使我们认识到了分析对于软件系统设计的重要性。也认识到规范化的设计流程对于后期的设计来说是十分重要的。前面的设计是后面的铺垫，只有对前面的设计过程进行准确地分析，才能节省整个系统的开发时间。

六.参考文献

[1]刘世峰.数据库基础与应用.中央广播电视大学出版社.2010

[2] 李铃.数据库原理与应用[M].北京中国经济出版社.2001

[3] 王珊、陈红.《数据库系统原理教程》.清华大学出版社.1998年7月