oracle课程设计实验.doc

学号武汉理工大学华夏学院ORACLE应用期末考查报告题 目：高校学生课程成绩据库设计专 业 软件工程 班 级 1111 姓 名 成 绩 教 师 oracle应用课程考试要求 考核拟采用大作业报告形式，将学生的学习成果心得体会以文档的形式提交。请批准。 本次考核要求每个学生完成的内容如下：选定某一主题，创建一个oracle数据库，对其进行日常管理及应用（全部用SQL语句实现）：创建数据库实例，数据库表空间，创建管理员，普通用户，并分别授予相应权限；至少建立5个表，以及表间关系，使用到序列，索引，视图，同义词，约束，每个表至少插入相关信息10条记录；要求使用sqlplus，pl/sql建立存储过程，触发器和程序包等代码，其中要求用到游标，异常，各种语句等对表中数据进行处理，建立报表；有实力的同学加入界面，连接数据库；要有操作截图展示，字数要求4000字左右；对 oracle数据库的认识及心得体会，要求字数1000字以上。【3】 以班级体刻盘，每人以自己的名字命名文件夹；成绩评定方法：1、平时成绩占30%，大作业及报告占70%。ORACLE应用期末考查报告11 功能需求分析42概要设计43逻辑设计53.163.263.363.473.573.674实现85测试数据105.1105.2115.3125.4136数据库备份147心得体会148参考资料151 功能需求分析高校学生课程成绩管理系统主要实现课程开设，教师授课，学生选课管理。每学期学校可以开设若干门课程，每门课程可以有多个教学班组成，每个老师可以选择若干个教学班进行授课，每个学生只能参加相同课程的一个教学班选课，但可以选择多个不同课程教学班。高校学生课程成绩管理系统主要功能如下：（1） 教师管理。主要实现教师信息的管理，包括课程信息添加，修改，删除和查询等任务。（2） 课程管理。主要实现按教学计划开设课程的管理。包括课程信息添加，修改，删除和查询等任务。（3） 学生管理。主要实现在校学生信息管理。包括学生信息添加，修改，删除和查询等任务。（4） 课程开设管理。主要实现每学期的教学任务。每门课程将分成若干个教学班进行教学。（5） 教师选择教学任务管理。主要实现教师选择教学班，实现教学任务的分配。（6） 学生选课管理。主要实现学生选择教学班。达到学生选课目的。（7） 学生成绩管理。主要实现学生选修课程成绩录入，修改，删除和查询等功能。（8） 统计管理。按教师，课程，时间统计教师在一个时间所授课程的平均成绩；按课程时间统计学校在一个时间该课程所有学生的平均成绩。2概要设计 根据高校学生课程成绩管理系统的功能需求分析，设计4个实体对象分别是：教师，学生，课程和开设课程。教师对象描述教师的基本信息，包括教师号，姓名，性别和联系电话；课程对象描述课程的基本信息，包括课程号，课程名，学分，系别；学生对象描述学生的基本信息，包括学号，姓名，性别，出生年月，系别和联系电话。实体之间存在3个关系，分别是讲授，开设和选课。讲授描述教师和开设课程之间的关系，开设描述课程和开设课程的关系，选课描述学生和开设课程之间的关系。高校学生课程成绩管理系统E-R图如图（1） 。教师讲授开设可程选课学生姓名性别教师号联系电话时间成绩姓名性别学号系别出生年月联系电话开设课程系别课程名课程号学分1M1Mn图13逻辑设计根据高校学生课程成绩管理系统概要设计。设计关系模型数据库。该系统设计4个实体对象表，1个关系表和2个视图，分别是teacher教师表，student学生表，course课程表，KC开设课程表，SC学科表，avg_v视图和cou_v视图。 3.1 teacher教师表包含4个字段。教师号为主键，性别字段只能为“男”或“女”。如下表teacher教师表所示.teacher教师表字段名属性类型约束sname姓名Char(20)Not nullsex性别Char(2)Check(sex in(n男女)tno教师号Char(12)Primary keytphone联系电话Char(20)Not null3.2 student学生表包含6个字段。学生对象学号作为主键，性别字段只能为“男”或“女”。如下表student学生表所示.student学生表字段名属性类型约束sname姓名Char(20)Not nullsex性别Char(2)Check(sexin(n男女)sno学号Char(12)Primary keysdept系别Char(12)sphone联系电话Char(11)brithday出生年月Date3.3 course课程表包含4个字段。课程对象用课程号作为主键，且课程名唯一。如下表course课程表所示。course课程表字段名属性类型约束cno课程号Number(6)Primary keycname课程名Char(20)uniquecredit学分Number(2,1)sdept系别Char(12)3.4 KC开设课程表包含3个字段。KC开设课程表用shijian,tno和cno作为主键。如下表KC开设课程表所示。KC开设课程表字段名属性类型约束shijian时间char(20)tno教师号foreign key,reference teacher(tno)cno课程号foreign key,reference course(cno)3.5 SC选课表包含5个字段。SC开设课程表用sno,tno,cno和shijian作为表的主键。如下表SC选课表所示。SC选课表字段名属性类型约束grade成绩number（2）not nullsno学号foreign key,reference student(cno)tno教师号foreign key,reference KC(tno,cnoshijian)cno课程号shijian时间3.6 为了实现高校学生课程成绩管理系统的统计管理功能，设计avg_v视图和cou_v视图。Avg_v视图包含教师名，课程名，时间和平均成绩4个字段，通过查询teacher教师表，course课程表和SC选课，在教师号和课程号字段上等值链接，并且按照教师名，课程名和时间分组，实现教师所授课程的平均成绩。Cou_v视图包含时间，课程和平均成绩3个字段，通过查询course课程表和SC选课表，在课程号字段上等值链接，并且按照时间和课程名分组，实现统计学习在某个时间课程的平均成绩。 在SC选课表上创建索引，索引建立在SC选课表的学号，课程号，教师号和时间字段上。课程号和教师号按升序排列，学号和时间按降序排列。4实现-创建表空间testCREATE TABLESPACE test DATAFILEc:appjohnoradataorcltest01.dbf SIZE 10M;-创建用户managerCREATE USER manager IDENTIFIED BY abc123DEFAULT TABLESPACE testQUOTA 5M ON test;-授权系统权限GRANT CREATE SESSION,CREATE TABLE TO manager;-用manager账号登陆CONNECT manager/abc123-创建teacher教师表CREATE TABLE teacher(tname CHAR(20) NOT NULL,sex CHAR(2) CHECK(sex in (男,女)，tno CHAR(12) PRIMARY KEY,tphone CHAR(20) NOT NULL);-创建course课程表CREATE TABLE course (cname CHAR(20) UNIQUE,cno NUMBER(6) PRIMARY KEY,credit NUMBER(2,1)sdept CHAR(12);-创建student学生表CREATE TABLE student (sname CHAR(20) NOT NULL,sex CHAR(2) CHECK(sex in (男,女)，sno CHAR(12) PRIMARY KEY,sdept CHAR(12),sphone CHAR(11),birthday DATA);-创建KC开设课程表CREATE TABLE KC (tno CHAR(12) REFERENCES teacher(tno) ON DELETE CASCADE,cno NUMBER(2) REFERENCES course(cno) ON DELETE CASCADE,shijian CHAR(20)PRIMARY KEY(shijian, tno, cno);-创建SC表CREATE TABLE SC (sno NUMBER(12) REFERENCES student(sno) ON DELETE CASCADE,cno NUMBER(6),tno CHAR(12),shijian CHAR(20),grade NUMBER(2) NOT NULL,FOREIGN KEY(shijian, tno, cno) REFERENCES KC(shijian, tno, cno) ON DELETE CASCADE,PRIMARY KEY(shijian, tno, cno,sno);-创建视图avg_vCREATE VIEW avg_v(tname,cname,shijian,Gavg)AS SELECT tname,cname,shijian,avg(grade)FROM teacher a,course b,SC cWHERE a.tno=c.tno AND o=oGROUP BY (tname,cname,shijian);-创建视图cou_vCREATE VIEW cou_v (shijian,cname,Gavg)AS SELECT shijian, cname,avg(grade)FROM course a,SC bWHERE o= oGROUP BY (shijian, cname);-创建索引CREATE UNIQUE INDEX SCno on SC(sno DESC,tno ASC,cno ASC,shijian DESC);-用户登录的存储过程create or replace procedure denglu(flag out number,username varchar2,upwd number)-登录as i varchar2(20);p number;begin flag:=0; select t.ename into i from scott.yonghu t where t.ename=username; if i is not null then flag:=1; select t.eno into p from scott.yonghu t where t.ename=username and t.eno=upwd; if upwd is not null then flag := 2;-登录成功 else flag := 1;-密码不正确 end if; else flag := 0;-用户不存在 end if; commit; exception when no_data_found then rollback; end;5测试数据5.1 teacher教师表测试数据如下：tnamesextnotphone教师一男20121126011310000001教师二男20121126021310000002教师三女20121126031310000003教师四男20121126041310000004教师五男20121126051310000005代码如下：INSERT INTO teacher（tname,sex,tno,tphone） VALUES(教师一, 男, 2012112601,1310000001);INSERT INTO teacher（tname,sex,tno,tphone） VALUES(教师二, 男, 2012112602,1310000002);INSERT INTO teacher（tname,sex,tno,tphone） VALUES(教师三, 女, 2012112603,1310000003);INSERT INTO teacher（tname,sex,tno,tphone） VALUES(教师四, 男, 2012112604,1310000004);INSERT INTO teacher（tname,sex,tno,tphone） VALUES(教师五, 男, 2012112605,1310000005);5.2 student表测试数据如下：snamesexsnosdeptsphonebirthday学生一男200901010121计算机1510000011989-07-15学生二男200901010148网络工程1510000021990-04-09学生三男200901010114电子信息1510000031988-10-12学生四男200901010141自动化1510000041990-07-27学生五男200901010125数学1510000051990-08-24学生六男200901010144计算机1510000061988-08-25代码如下：INSERT INTO student（sname,sex,sno,sdept,sphone,birthday）VALUES(学生一, 男, 200901010121, 计算机,151000001, to_data( 1989-07-15,yyyy-mm-dd);INSERT INTO student（sname,sex,sno,sdept,sphone,birthday）VALUES(学生二, 男, 200901010148, 网络工程,151000001,to_data( 1990-04-09,yyyy-mm-dd);INSERT INTO student（sname,sex,sno,sdept,sphone,birthday）VALUES(学生三, 男, 200901010114, 电子信息,151000003, to_data( 1988-10-12,yyyy-mm-dd);INSERT INTO student（sname,sex,sno,sdept,sphone,birthday）VALUES(学生四, 男, 200901010141, 自动化,151000004, to_data( 1990-07-27,yyyy-mm-dd);INSERT INTO student（sname,sex,sno,sdept,sphone,birthday）VALUES(学生五, 男, 200901010144, 计算机,151000006, to_data( 1988-08-25, yyyy-mm-dd);INSERT INTO student（sname,sex,sno,sdept,sphone,birthday）VALUES(学生一, 男, 200901010144, 计算机,151000001, to_data( 1989-07-15, yyyy-mm-dd);5.3 KC开设课程表数据如下：tnocnotime20121126011126012011-2012第二学期20121126011126012011-2013第一学期20121126021126022011-2012第二学期20121126021126022011-2013第一学期20121126031126032011-2012第二学期20121126031126032011-2013第一学期20121126041126042012-2013第一学期20121126041126042010-2011第一学期20121126051126052010-2011第一学期20121126051126052011-2012第二学期代码如下：INSERT INTO KC(tno,cno,shijian)VALUES(2012112601, 112601, 2011-2012第二学期);INSERT INTO KC(tno,cno,shijian)VALUES(2012112601, 112601, 2011-2013第一学期);INSERT INTO KC(tno,cno,shijian)VALUES(2012112602, 112602, 2011-2012第二学期);INSERT INTO KC(tno,cno,shijian)VALUES(2012112602, 112602, 2011-2012第一学期);INSERT INTO KC(tno,cno,shijian)VALUES(2012112603, 112603, 2011-2012第二学期);INSERT INTO KC(tno,cno,shijian)VALUES(2012112603, 112603, 2011-2012第一学期);INSERT INTO KC(tno,cno,shijian)VALUES(2012112604, 112604, 2012-2013第一学期);INSERT INTO KC(tno,cno,shijian)VALUES(2012112604, 112604, 2010-2011第一学期);INSERT INTO KC(tno,cno,shijian)VALUES(2012112605, 112605, 2010-2011第一学期);INSERT INTO KC(tno,cno,shijian)VALUES(2012112605, 112605, 2011-2012第二学期);5.4 SC选课表中的数据如下：snocnotnotimegrade20090101012111260120121126012012-2013第一学期9620090101012111260220121126022011-2012第二学期9820090101012111260520121126052010-2011第一学期9920090101014811260120121126012012-2013第一学期8520090101014811260520121126052011-2012第二学期9220090101011411260220121126022011-2012第二学期7520090101011411260320121126032012-2013第一学期8820090101014111260320121126032012-2013第一学期8220090101014111260420121126042012-2013第一学期8020090101012511260420121126042012-2013第一学期9420090101012511260120121126012011-2012第二学期6820090101014411260120121126012012-2013第一学期7720090101014411260220121126022011-2012第二学期8720090101014411260520121126052010-2011第一学期89代码如下：INSERT INTO SC(sno,cno,tno,shijian,grade)VALUES(200901010121, 112601, 2012112601, 2012-2013第一学期, 96);INSERT INTO SC(sno,cno,tno,shijian,grade)VALUES(200901010121, 112602, 2012112602, 2011-2012第二学期, 98);INSERT INTO SC(sno,cno,tno,shijian,grade)VALUES(200901010121, 112605, 2012112605, 2010-2011第一学期,95);INSERT INTO SC(sno,cno,tno,shijian,grade)VALUES(200901010148, 112601, 2012112601, 2012-2013第一学期,85);INSERT INTO SC(sno,cno,tno,shijian,grade)VALUES(200901010148, 112605, 2012112605, 2011-2012第二学期, 92);INSERT INTO SC(sno,cno,tno,shijian,grade)VALUES(200901010114, 112602, 2012112602, 2011-2012第二学期,75);INSERT INTO SC(sno,cno,tno,shijian,grade)VALUES(200901010114, 112603, 2012112603, 2012-2013第一学期, 88);INSERT INTO SC(sno,cno,tno,shijian,grade)VALUES(200901010141, 112603, 2012112603, 2012-2013第一学期, 82);INSERT INTO SC(sno,cno,tno,shijian,grade)VALUES(200901010141, 112604, 2012112604, 2012-2013第一学期,80);INSERT INTO SC(sno,cno,tno,shijian,grade)VALUES(200901010125, 112604, 2012112604, 2012-2013第一学期, 94);INSERT INTO SC(sno,cno,tno,shijian,grade)VALUES(200901010125, 112601, 2012112601, 2011-2012第二学期, 68);INSERT INTO SC(sno,cno,tno,shijian,grade)VALUES(200901010144, 112601, 2012112601, 2012-2013第一学期, 77);INSERT INTO SC(sno,cno,tno,shijian,grade)VALUES(200901010144, 112602, 2012112602, 2011-2012第二学期, 87);INSERT INTO SC(sno,cno,tno,shijian,grade)VALUES(200901010144, 112605, 2012112605, 2010-2011第一学期, 89);6数据库备份alter tablespace test begin backup;host copyc:appjohnoraddtaorcltest01.dbfc:usersjohndocumentstest01.dbfalter tablespace test end backup;7心得体会经过一个星期的设计和开发，对高校学生课程成绩管理系统基本开发完毕。通过不断地对程序及各模块进