《数据库原理与应用》综合性实验报告-学生成绩管理系统数据库设计方案

《数据库原理与应用》综合性实验报告学生成绩管理系统数据库设计方案学号：201130560210姓名：柯俊展目录一、系统开发目标 (2)二、数据库需求分析 (2)三、数据流图 (2)数据流图： (2)四、数据库设计 (3)E—R图： (3)数据字典： (3)五、逻辑结构设计 (4)关系模式： (4)六、数据库实施 (5)创建表： (5)权限设置： (10)创建触发器： (13)教师输入并查询数据： (13)创建函数： (14)插入数据到score: (15)学生登陆查询信息： (16)教务员登陆查询信息： (16)成绩分析： (17)总结： (18)附录： (19)参考文献： (27)1一、系统开发目标建立一个学生成绩管理数据库系统，用于数据的输入、修改和查询，满足任课教师、学生、教务员等用户的需求。

二、数据库需求分析明确系统的用户，收集和分析用户对系统的信息需求和处理需求：1.任课教师可以对所讲授课程进行成绩输入、修改和查看。

2.学生可以按学号查询自己各门功课的成绩、总成绩、平均分、在班级中的排名等。

3.教务员可以查询全体学生的成绩、对学生成绩进行排名等。

问题假设：4.此问题中只有一个班，四位同学，三位教师授课（即三门课，分别是英语、高等数学、数据库应用），一位教务员。

5.查看平台是ORACLE SQL/Plus平台。

三、数据流图数据流图：2学生查询处理：四、数据库设计根据以上信息，得出实体——联系图E—R图：数据字典：（1）数据项描述数据项名称：学生依据号码查询成绩别名：学号含义：唯一标识一个学生身份类型：字符型长度：20（2）数据结构描述名称：学生含义说明：定义一个学生有关信息组成结构：学号+姓名名称：教师3含义说明：定义一个教师有关信息组成结构：教师号+教师名名称：课程含义说明：定义一个课程有关信息组成结构：课程号+课程名名称：成绩含义说明：定义一个学生成绩有关信息组成结构：学号+课程号+分数（3）数据流（非数据项）说明数据流名称：输入成绩含义：教师给学生输入成绩来源：教师组成：学号+课程号+分数（4）数据存储说明数据存储名称：学生信息表含义说明：存放学生有关信息组成结构：学号+学生名字数据存储名称：教师信息表含义说明：存放教师有关信息组成结构：教师号+教师名数据存储名称：课程信息表含义说明：存放课程有关信息组成结构：课程号+课程名数据存储名称：成绩信息表含义说明：存放学生成绩有关信息组成结构：学号+课程号+分数（5）处理过程说明处理过程名称：审核教师号，课程号输入：教师号，课程号输出：认定合理的学生成绩处理过程名称：学号输入：学号输出：认定教师给学生成绩以上只给出学生成绩查询系统数据字典部分数据项、数据结构、数据流、数据存储和处理过程的描述。

五、逻辑结构设计关系模式：学生（学号，学生姓名）4课程（课程号,课程名）教师（教师号，教师名）教授（学号，教师号）成绩（学号，课程号，分数）根据分析，此上的关系模式符合第三范式（3NF），不包含部分函数依赖和传递函数依赖。

是可行的学生成绩查询系统。

六、数据库实施创建表：第一个student表：5第二个project表：第三个grade表：67第四个teacher表：第五个teaching表：8第六个score表：9权限设置：以上是创建角色teacher_role,student_role分别授予用户teacher_user1, teacher_user2 ,eacher_user3,student_user;创建headteacher_user授予管理员权限。

这样便于管理。

不足之处：不能授予用户teacher_user1, teacher_user2,teacher_user3只能更新行的权限。

创建触发器：教师输入并查询数据：教师一更新数据：教师二更新数据：教师三更新数据：教师更新数据如下：创建函数：创建getsunscore函数：创建getaveragescore函数：插入数据到score:学生登陆查询信息：教务员登陆查询信息：成绩分析：总结：实验并不简单，但我真的能学习了很多东西。

以上全部由我参考《数据库系统原理与应用——Oracle版》这本书所做的。

花了三天时间，学到了很多东西，值了。

虽是考试前所做的，但实际在考试的时候由于紧张又忘记得一干二净，很伤心很伤心。

附录：权限设置：set serveroutput on教师角色：create role teacher_role;grant update(tograde) on grade to teacher_role;grant select on grade to teacher_role;学生角色：create role student_role;grant select on score to student_role;教务员：create user headteacheridentified by headdefault tablespace userstemporary tablespace tempquota unlimited on users;grant DBA to headteacher;教师用户：create user teacher_user1identified by tea1default tablespace userstemporary tablespace tempquota unlimited on users;grant update(tograde) on grade where(projectid='20122') to teacher_user1; grant update(tograde) on grade.projectid='20121' to teacher_user1 ;grant teacher_role to teacher_user1;create user teacher_user2identified by tea2default tablespace userstemporary tablespace tempquota unlimited on users;grant teacher_role to teacher_user2;grant update(tograde) on grade to teacher_user2 where projectid='20122'; grant teacher_role to teacher_user2;create user teacher_user3identified by tea3default tablespace userstemporary tablespace tempquota unlimited on users;grant teacher_role to teacher_user3;grant update(tograde) on grade to teacher_user3 where projectid='20123'; grant teacher_role to teacher_user3;学生用户：create user student_useridentified by studdefault tablespace userstemporary tablespace tempquota unlimited on users;grant student_role to student_user;grant create session to student_user,teacher_user1,输入：connect teacher_user1/tea1;connect teacher_user2/tea2;connect teacher_user3/tea3;update system.grade set tograde='85' where studentid='201130560211' and projectid='20121';update system.grade set tograde='89' where studentid='201130560212' and projectid='20121';update system.grade set tograde='98' where studentid='201130560213' and projectid='20121';update system.grade set tograde='79' where studentid='201130560214' and projectid='20121';update system.grade set tograde='84' where studentid='201130560211' and projectid='20122';update system.grade set tograde='88' where studentid='201130560212' and projectid='20122';update system.grade set tograde='75' where studentid='201130560213' and projectid='20122';update system.grade set tograde='94' where studentid='201130560214' and projectid='20122';update system.grade set tograde='87' where studentid='201130560211' andprojectid='20123';update system.grade set tograde='92' where studentid='201130560212' and projectid='20123';update system.grade set tograde='93' where studentid='201130560213' and projectid='20123';update system.grade set tograde='90' where studentid='201130560214' and projectid='20123';select * from system.score;select * from system.grade;创建表：1.create table student(studentid varchar2(12) primary key,stuname varchar2(8));插入数据：insert into student values('201130560211','刘伟'); insert into student values('201130560212','张三'); insert into student values('201130560213','李四'); insert into student values('201130560214','王菲');2.create table project(projectid varchar2(5) primary key,proname varchar2(12));插入数据：insert into project values('20121','高等数学'); insert into project values('20122','英语');insert into project values('20123','数据库应用'); 3.create table grade(studentid varchar2(12),projectid varchar2(5),tograde number(3,1),primary key(studentid,projectid),constraint k_studentid foreign key(studentid)references student(studentid),constraint k_projectid foreign key(projectid)references project(projectid));插入数据：insert into grade values('201130560211','20121',''); insert into grade values('201130560211','20122','');insert into grade values('201130560211','20123',''); insert into grade values('201130560212','20121',''); insert into grade values('201130560212','20122',null); insert into grade values('201130560212','20123',''); insert into grade values('201130560213','20121',''); insert into grade values('201130560213','20122',''); insert into grade values('201130560213','20123',''); insert into grade values('201130560214','20121',''); insert into grade values('201130560214','20122',''); insert into grade values('201130560214','20123','');4.create table teacher(teacherid varchar2(9) primary key,teaname varchar2(8));插入数据：insert into teacher values('201130561','林小盼');insert into teacher values('201130562','王金雅');insert into teacher values('201130563','黄顺得');5.create table teaching(studentid varchar2(12) ,teacherid varchar2(9),constraint j_studentid foreign key(studentid)references student(studentid),constraint j_teacherid foreign key(teacherid)references teacher(teacherid));插入数据：insert into teaching values('201130560211','201130561'); insert into teaching values('201130560211','201130562'); insert into teaching values('201130560211','201130563'); insert into teaching values('201130560212','201130561'); insert into teaching values('201130560212','201130562'); insert into teaching values('201130560212','201130563'); insert into teaching values('201130560213','201130561'); insert into teaching values('201130560213','201130562'); insert into teaching values('201130560213','201130563'); insert into teaching values('201130560214','201130561'); insert into teaching values('201130560214','201130562'); insert into teaching values('201130560214','201130563');6.create table score(studentid varchar2(12) primary key,english number(3,1),math number(3,1),oracleproject number(3,1),sumscore number,averscore number,constraint z_studentid foreign key(studentid)references student(studentid));插入数据：insert into score values('201130560211',0,0,0,0,0);insert into score values('201130560212',0,0,0,0,0);insert into score values('201130560213',0,0,0,0,0);insert into score values('201130560214',0,0,0,0,0);插入数据：insert into score select studentid from student;update score set math=(select tograde from grade wherestudentid='201130560211' and projectid='20121')wherestudentid='201130560211'; update score set english=(select tograde from grade wherestudentid='201130560211' and projectid='20122')wherestudentid='201130560211'; update score set oracleproject=(select tograde from grade wherestudentid='201130560211' and projectid='20123')wherestudentid='201130560211'; update score set math=(select tograde from grade wherestudentid='201130560212' and projectid='20121')wherestudentid='201130560212'; update score set english=(select tograde from grade wherestudentid='201130560212' and projectid='20122')wherestudentid='201130560212'; update score set oracleproject=(select tograde from grade wherestudentid='201130560212' and projectid='20123')wherestudentid='201130560212'; update score set math=(select tograde from grade wherestudentid='201130560213' and projectid='20121')wherestudentid='201130560213'; update score set english=(select tograde from grade wherestudentid='201130560213' and projectid='20122')wherestudentid='201130560213'; update score set oracleproject=(select tograde from grade wherestudentid='201130560213' and projectid='20123')wherestudentid='201130560213'; update score set math=(select tograde from grade wherestudentid='201130560214' and projectid='20121')wherestudentid='201130560214'; update score set english=(select tograde from grade wherestudentid='201130560214' and projectid='20122')wherestudentid='201130560214'; update score set oracleproject=(select tograde from grade wherestudentid='201130560214' and projectid='20123')wherestudentid='201130560214';select * from system.score;update score set sumscore=(getsumscore('201130560211'))where studentid='201130560211';update score set sumscore=(getsumscore('201130560212'))where studentid='201130560212';update score set sumscore=(getsumscore('201130560213'))where studentid='201130560213';update score set sumscore=(getsumscore('201130560214'))where studentid='201130560214';update score set averscore=(getaveragescore('201130560211'))where studentid='201130560211';update score set averscore=(getaveragescore('201130560212'))where studentid='201130560212';update score set averscore=(getaveragescore('201130560213'))where studentid='201130560213';update score set averscore=(getaveragescore('201130560214'))where studentid='201130560214';修改表的方法：alter table score add(linenum number);select * from system.score order by sumscore desc;执行函数：declarebegingetsumscore('201130560211');end;/函数：create or replace function getsumscore(p_studentid char)return number assum_num number;i number;j number;beginsum_num:=0;select tograde into i from grade where studentid=p_studentid and projectid='20121';select tograde into j from grade where studentid=p_studentid and projectid='20122';select tograde into k from grade where studentid=p_studentid and projectid='20123';sum_num:=i+j+k;return sum_num;end getsumscore;/create or replace function getaveragescore(p_studentid char)return number assum_num number;aver_num number;i number;j number;k number;beginsum_num:=0;select tograde into i from grade where studentid=p_studentid and projectid='20121';select tograde into j from grade where studentid=p_studentid and projectid='20122';select tograde into k from grade where studentid=p_studentid and projectid='20123';sum_num:=i+j+k;aver_num:=sum_num/3;return aver_num;end getaveragescore;/过程：create or replace procedure getstudentnum()asdeclarecursor cursorgetaverscore isselect averscore from system.score;kjz_averscore number;i1 number;i2 number;i4 number;i5 number;begini1:=0;i2:=0;i3:=0;i4:=0;i5:=0;open cursorgetaverscore;fetch cursorgetaverscore into kjz_averscore;while cursorgetaverscore%found loopif kjz_averscore>=90 theni1:=i1+1;elsif kjz_averscore>=80 theni2:=i2+1;elsif kjz_averscore>=70 theni2:=i3+1;elsif kjz_averscore>=60 theni2:=i4+1;elsei5:=i5+1;end if;fetch cursorgetaverscore into kjz_averscore;end loop;close cursorgetaverscore;dbms_output.put_line('分析成绩分布：');dbms_output.put_line('平均分大于90分有'||i1||'个');dbms_output.put_line('平均分小于90大于80分有'||i2||'个');dbms_output.put_line('平均分小于80大于70分有'||i3||'个');dbms_output.put_line('平均分小于70大于60分有'||i4||'个');dbms_output.put_line('平均分小于60分有'||i5||'个');end;/触发器：create or replace trigger tri_gradebefore insert or update or delete on system.gradedeclareerr_constant constant char(30):='不能在休息日改变学生的成绩。