数据库大作业实验报告

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 院系表字段名数据类型长度允许空主码或索引约束条件学院Char20No主码总成绩Number5 表3-2 运动员信息表字段名数据类型长度允许空主码或索引约束条件运动员编号Number5No主码姓名Char20年龄Number3性别Char2“男”或“女”学院Char40参加项目数Number1默认为0 表3-3 运动项目表字段名数据类型长度允许空主码或索引约束条件项目编号Number5No主码项目名称Char20项目时间Char20项目地点Char20表3-4 参加记录表字段名数据类型长度允许空主码或索引约束条件记录编号Number5No主码运动员编号Number5外码项目编号Number5外码 表3-5 成绩表字段名数据类型长度允许空主码或索引约束条件成绩编号Number5No主码运动员编号Number5外码姓名Char20项目编号Number3外码项目名称Char20成绩Number40默认为0学院Char40No 四.数据库的建立具体SQL代码如下：1、 创建表空间和用户create user HGD identified by /创建用户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) asbegin update 运动员信息表 set 学院 = in1 where 姓名 = in2; commit; end;/执行begin 修改运动员信息表数据(生科,张三); end;/创建删除_存储过程create procedure 删除运动员信息表一条记录 (in1 varchar) asbegin delete from 运动员信息表 where 姓名 = in1; commit; end;/执行 begin 删除运动员信息表一条记录(张三); end;/创建函数统计运动员人数create or replace function function_getcountreturn numberissumcount 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 afterinsert on 参加记录表for each rowdeclare 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 总成绩