大作业任务实验报告.docx

大作业任务实验报告.docx

《大作业任务实验报告.docx》由会员分享，可在线阅读，更多相关《大作业任务实验报告.docx（22页珍藏版）》请在冰豆网上搜索。

大作业任务实验报告

武汉轻工大学

2013-2014学年第2学期

《大型数据库应用》

期末考核报告

专业：

班级：

学号：

姓名：

2013－2014年度第二学期

《大型数据库应用》期末考核任务

1.任务内容

1.1以自己的学号为用户名建立用户；

1.2完成以下数据库文件的创建工作：

（1）创建一个永久性的表空间TEST，表空间采用自动分区管理方式，其对应的数据文件名称为USERTEST.DBF，大小为50M，并将该表空间设置为数据库默认的表空间。

CREATETABLESPACETESTDATAFILE

'F:

\app\wanglu\oradata\orcl\USERTEST.DBF'SIZE50M

EXTENTMANAGEMENTLOCALAUTOALLOCATE;

ALTERDATABASEDEFAULTTABLESPACETEST;

（2）为数据库添加一个重做日志文件组，组内包含三个成员文件，成员文件的大小为3M。

ALTERDATABASEADDLOGFILEGROUP5

（'F:

\app\wanglu\oradata\orcl\redo05a.log',

'F:

\app\wanglu\oradata\orcl\redo05b.log',

'F:

\app\wanglu\oradata\orcl\redo05c.log'

）SIZE5M;

1.3使用SQL语言创建下列关系表：

（1）CHOICE表（选课表）

字段名

字段说明

字段类型

是否允许为空

S_NO

学号

字符串

否

COURSE_NO

该学号学生选课课号

字符串

否

SCORE

该课程该学生的成绩

整数

是

主键：

（S_No,Course_no）

CONNECTS110502221/S110502221;

CREATETABLECHOICE

（

S_NOVARCHAR（10）NOTNULL,

COURSE_NOVARCHAR（10）NOTNULL,

SCORENUMBER

（2）,

PRIMARYKEY（S_NO,COURSE_NO）

）;

（2）CLASS表（班级表）

字段名

字段说明

字段类型

是否允许为空

CLASS_NO

班级编号

字符串

否

CLASS_NAME

班级名称

字符串

否

DEPT_NO

班级所在系部编号

字符串

否

主键：

CLASS_NO

CREATETABLECLASS

（

CLASS_NOVARCHAR（10）PRIMARYKEY,

CLASS_NAMEVARCHAR（20）NOTNULL,

DEPT_NOVARCHAR（10）NOTNULL

）;

（3）COURSE表（课程表）

字段名

字段说明

字段类型

是否允许为空

COURSE_NO

课程编号

字符串

否

COURSE_NAME

课程名称

字符串

否

主键：

COURSE_NO

CREATETABLECOURSE

（

COURSE_NOVARCHAR（10）PRIMARYKEY,

COURSE_NAMEVARCHAR（20）NOTNULL

）;

（4）DEPARTMENT表（系部表）

字段名

字段说明

字段类型

是否允许为空

DEPT_NO

系部编号

字符串

否

DEPT_NAME

系部名称

字符串

否

主键：

DEPT_NO

CREATETABLEDEPARTMENT

（

DEPT_NOVARCHAR（10）PRIMARYKEY,

DEPT_NAMEVARCHAR（20）NOTNULL

）;

（5）STUDENT表（学生表）

字段说明：

字段名

字段说明

字段类型

是否允许为空

S_NO

学号

字符串

否

S_NAME

学生姓名

字符串

否

S_SEX

性别

字符串

否

S_BIRTHDAY

出生日期

日期

是

S_SCORE

入学总分

整数

是

S_ADDF

入学附加分

整数

是

CLASS_NO

所在班级编号

字符串

否

主键：

S_NO

CREATETABLESTUDENT

（

S_NOVARCHAR（10）PRIMARYKEY,

S_NAMEVARCHAR（20）NOTNULL,

S_SEXVARCHAR

（2）NOTNULL,

S_BIRTHDAYDATE,

S_SCORENUMBER（3）,

S_ADDFNUMBER

（2）,

CLASS_NOVARCHAR（10）NOTNULL

）;

（6）TEACHER表（教师表）

字段说明：

字段名

字段说明

字段类型

是否允许为空

T_NO

教师编号

字符串

否

T_NAME

教师姓名

字符串

否

T_SEX

性别

字符串

否

S_BIRTHDAY

出生日期

日期

是

TECH_TITLE

职称

字符串

否

AGE

年龄

整数

是

SALARY

薪水

小数

是

主键：

T_NO

CREATETABLETEACHER

（

T_NOVARCHAR（10）PRIMARYKEY,

T_NAMEVARCHAR（20）NOTNULL,

T_SEXVARCHAR

（2）NOTNULL,

T_BIRTHDAYDATE,

TECH_TITLEVARCHAR（5）NOTNULL,

AGENUMBER

（2）,

SALARYNUMBER

）;

（7）TEACHING表（教师授课表）

字段说明：

字段名

字段说明

字段类型

是否允许为空

T_NO

教师编号

字符串

否

COURSE_NO

所带课程编号

字符串

否

主键：

（T_NO，COURSE_NO）

CREATETABLETEACHING

（

T_NOVARCHAR（10）NOTNULL,

COURSE_NOVARCHAR（10）NOTNULL,

PRIMARYKEY（T_NO,COURESE_NO）

）;

1.3建立约束：

各关系表的约束要求如下

外键约束：

（1）CHOICE——COURSE关系

表名

字段名

关系

COURSE

COURSE_NO

主键

CHOICE

COURSE_NO

外键

ALTERTABLECHOICE

ADDCONSTRAINTCS1FOREIGNKEY（COURSE_NO）

REFERENCESCOURSE（COURSE_NO）;

（2）CHOICE——STUDENT关系

表名

字段名

关系

STUDENT

S_NO

主键

CHOICE

S_NO

外键

ALTERTABLECHOICE

ADDCONSTRAINTCS2FOREIGNKEY（S_NO）REFERENCESSTUDENT（S_NO）;

（3）CLASS——DEPARTMENT关系

表名

字段名

关系

DEPARTMENT

DEPT_NO

主键

CLASS

DEPT_NO

外键

ALTERTABLECLASS

ADDCONSTRAINTCS3FOREIGNKEY（DEPT_NO）

REFERENCESDEPARTMENT（DEPT_NO）;

（4）STUDENT——CLASS关系

表名

字段名

关系

CLASS

CLASS_NO

主键

STUDENT

CLASS_NO

外键

ALTERTABLESTUDENT

ADDCONSTRAINTCS4FOREIGNKEY（CLASS_NO）

REFERENCESCLASS（CLASS_NO）;

（5）TEACHING——TEACHER关系

表名

字段名

关系

TEARCHER

T_NO

主键

TEARCHING

T_NO

外键

ALTERTABLETEACHING

ADDCONSTRAINTCS5FOREIGNKEY（T_NO）REFERENCESTEACHER（T_NO）;

（6）TEARCHING——COURSE关系

表名

字段名

关系

COURSE

T_NO

主键

TEARCHING

COURSE_NO

外键

ALTERTABLETEACHING

ADDCONSTRAINTCS6FOREIGNKEY（COURSE_NO）

REFERENCESCOURSE（COURSE_NO）;

检查性约束：

STUDENT表的S_SEX字段和TEACHER表的T_SEX字段取值均为’男’或’女’。

ALTERTABLESTUDENTADDCHECK（S_SEXIN（‘男’,’女’））;

ALTERTABLETEACHERADDCHECK（T_SEXIN（’男’,’女’））;

STUDENT表的S_SEX字段和TEACHER表的T_SEX字段取值均为’男’或’女’。

1.4使用SQL语言插入下列所示的数据

CHOICE表

CLASS表

COURSE表

TEARCHING表

DEPARTMENT表

STUDENT表de

TEARCHER表

表department

INSERTINTODEPARTMENTVALUES（'02','计算机'）;

INSERTINTODEPARTMENTVALUES（'03','数学系'）;

INSERTINTODEPARTMENTVALUES（'04','电气系'）;

表class

INSERTINTOCLASSVALUES（'0202','计算机2班','02'）;

INSERTINTOCLASSVALUES（'0302','数学3班','03'）;

INSERTINTOCLASSVALUES（'0303','数学3班','03'）;

INSERTINTOCLASSVALUES（'0401','电气1班','04'）;

课程表

INSERTINTOCOURSEVALUES（'01','数学分析'）;

INSERTINTOCOURSEVALUES（'02','模拟电路'）;

INSERTINTOCOURSEVALUES（'03','数字电路'）;

INSERTINTOCOURSEVALUES（'04','计算机文化'）;

INSERTINTOCOURSEVALUES（'05','离散数学'）;

INSERTINTOCOURSEVALUES（'13','线性代数'）;

INSERTINTOCOURSEVALUES（'06','高等数学'）;

INSERTINTOCOURSEVALUES（'07','邓小平理论'）;

INSERTINTOCOURSEVALUES（'08','马克思哲学'）;

INSERTINTOCOURSEVALUES（'09','大学物理'）;

INSERTINTOCOURSEVALUES（'10','大学语文'）;

INSERTINTOCOURSEVALUES（'11','数据结构'）;

INSERTINTOCOURSEVALUES（'12','C语言'）;

学生表

INSERTINTOSTUDENTVALUES（'020201','张三','男','',560,0,'0202'）;

INSERTINTOSTUDENTVALUES（'020202','李四','男','',578,0,'0202'）;

INSERTINTOSTUDENTVALUES（'030201','王五','女','',545,10,'0302'）;

INSERTINTOSTUDENTVALUES（'030202','丁一','女','',589,0,'0302'）;

INSERTINTOSTUDENTVALUES（'030301','周一','女','',570,0,'0303'）;

INSERTINTOSTUDENTVALUES（'030302','牛二','男','',609,0,'0303'）;

INSERTINTOSTUDENTVALUES（'030303','阮七','女','',603,0,'0303'）;

INSERTINTOSTUDENTVALUES（'040101','苏山','女','',548,0,'0401'）;

老师表

INSERTINTOTEACHERVALUES（'000001','教师一','男','','教授',56,4800）;

INSERTINTOTEACHERVALUES（'000002','教师二','女','','教授',55,4333）;

INSERTINTOTEACHERVALUES（'000003','教师三','男','','副教授',43,3342）;

INSERTINTOTEACHERVALUES（'000005','教师四','女','','讲师',29,2234）;

INSERTINTOTEACHERVALUES（'000006','教师五','男','','讲师',30,2312）;

INSERTINTOTEACHERVALUES（'000007','教师六','女','','讲师',45,2313）;

授课表

INSERTINTOTEACHINGVALUES（'000001','01'）;

INSERTINTOTEACHINGVALUES（'000001','06'）;

INSERTINTOTEACHINGVALUES（'000001','13'）;

INSERTINTOTEACHINGVALUES（'000002','02'）;

INSERTINTOTEACHINGVALUES（'000002','03'）;

INSERTINTOTEACHINGVALUES（'000003','07'）;

INSERTINTOTEACHINGVALUES（'000003','08'）;

INSERTINTOTEACHINGVALUES（'000005','05'）;

INSERTINTOTEACHINGVALUES（'000006','04'）;

INSERTINTOTEACHINGVALUES（'000006','12'）;

INSERTINTOTEACHINGVALUES（'000007','11'）;

选课表

INSERTINTOCHOICEVALUES（'020201','07',83）;

INSERTINTOCHOICEVALUES（'020201','08',79）;

INSERTINTOCHOICEVALUES（'020201','09',78）;

INSERTINTOCHOICEVALUES（'020201','11',92）;

INSERTINTOCHOICEVALUES（'020201','12',95）;

INSERTINTOCHOICEVALUES（'020201','04',86）;

INSERTINTOCHOICEVALUES（'020202','13',67）;

INSERTINTOCHOICEVALUES（'020202','07',89）;

INSERTINTOCHOICEVALUES（'020202','08',77）;

INSERTINTOCHOICEVALUES（'020202','09',80）;

INSERTINTOCHOICEVALUES（'020202','11',90）;

INSERTINTOCHOICEVALUES（'020202','12',93）;

INSERTINTOCHOICEVALUES（'020202','04',88）;

INSERTINTOCHOICEVALUES（'030201','01',79）;

INSERTINTOCHOICEVALUES（'030201','04',77）;

INSERTINTOCHOICEVALUES（'030201','07',88）;

INSERTINTOCHOICEVALUES（'030201','08',86）;

INSERTINTOCHOICEVALUES（'030201','10',77）;

INSERTINTOCHOICEVALUES（'030202','01',55）;

INSERTINTOCHOICEVALUES（'030202','04',54）;

INSERTINTOCHOICEVALUES（'030202','07',67）;

INSERTINTOCHOICEVALUES（'030202','08',65）;

INSERTINTOCHOICEVALUES（'030202','10',70）;

INSERTINTOCHOICEVALUES（'040101','02',78）;

INSERTINTOCHOICEVALUES（'040101','03',82）;

INSERTINTOCHOICEVALUES（'040101','04',78）;

INSERTINTOCHOICEVALUES（'040101','07',67）;

INSERTINTOCHOICEVALUES（'040101','08',76）;

INSERTINTOCHOICEVALUES（'040101','09',86）;

INSERTINTOCHOICEVALUES（'030202','13',78）;

INSERTINTOCHOICEVALUES（'030202','05',80）;

INSERTINTOCHOICEVALUES（'030201','13',86）;

INSERTINTOCHOICEVALUES（'030201','05',79）;

1.5完成以下查询要求，给出实现该要求的SQL语句

（1）查询入学总分高于平均总分的学生的学号、姓名、总分、班级编号、班级名信息，并将显示结果按班级编号、学号排序。

selects_no,s_name,s_score,class.class_no,class_name,dept_no

fromstudent,class

wherestudent.class_no=class.class_noandstudent.s_score>（selectavg（s_score）

fromstudent）

orderbystudent.s_no,class.class_no;

（2）创建一个名为STUDENT_NEW的新表，将STUDENT表中的记录复制到该新表中。

（3）将STUDENT中叫“丁一”的学生的学号修改为“040102”，班级编号修改为“0401”。

（4）列出《计算机文化》课程的成绩高于“王五”的学生名单。

（5）分组统计选修各门课程的学生人数。

1.6完成以下PL/SQL编程的相关要求

（1）设计一个存储过程，用来接受用户输入的学号，然后显示出该学号对应学生的所有选修课程名及课程成绩。

CREATEORREPLACEPROCEDUREshow_choice

（snoCHOICE.S_NO%TYPE）

IScursorc_choiceis

SELECTCOURSE_NAME,SCORE

FROMCHOICE,COURSEWHERECHOICE.S_NO=snoandCHOICE.COURSE_NO=COURSE.COURSE_NO;

p_cnameCOURSE.COURSE_NAME%TYPE;

p_scoureCHOICE.SCORE%TYPE;

BEGIN

OPENc_choice;

LOOP

FETCHc_choiceINTOp_cname,p_scoure;

EXITWHENc_choice%NOTFOUND;

DBMS_OUTPUT.PUT_LINE（p_cname||''||p_scoure）;

ENDLOOP;

END;

（2）设计一个函数，用来接受用户输入的课程名，然后显示出所有选修了该课程学生的成绩的平均成绩。

CREATEORREPLACEPROCEDUREshow_avgrade

（cnameCOURSE.COURSE_NAME%TYPE）

IScursorc_choiceis

SELECTS_NO,AVG（SCORE）

FROMCHOICE,COURSEWHERECHOICE.COURSE_NO=COURSE.COURSE_NO

ANDCOURSE_NAME=cnameGROUPBYS_NO;

p_choiceCHOICE%ROWTYPE;

BEGIN

OPENc_choice;

LOOP

FETCHc_choiceINTOp_choice.s_no,p_choice.score;

DBMS_OUTPUT.PUT_LINE（p_choice.S_NO||''||p_choice.score）;

EXITWHENc_choice%NOTFOUND;

ENDLOOP;

END;

（3）设计一个触发器，用于在更新了课程表内容后，更新与课程表有关联的表的内容。

1.7将创建的表进行逻辑备份

expS110502221/S110502221grants=ytables=（DEPARTMENT,CLASS,STUDENT,TEACHER,CHOICE,TEACHING,COURSE）file='d:

\s110502221'

实验总结

通过这次课程设计，我学到很多东西，包括表的创建，表约束的管理，表参数的设置，删除表，修改表等操作有了一定的了解，能使用sql语句创建表。

主键与外键的约束也很重要，主关键字约束指定表的一列或几列的组合的值在表中具有惟一性，即能惟一地指定一行记录。

每个表中只能有一列被指定为主关键字，且IMAGE 和TEXT 类型的列不能被指定为主关键字，也不允许指定主关键字列有NULL 属性。

外关键字约束定义了表之间的关系。

当一个表中的一个列或多个列的组合和其它表中的主关键字定义相同时，就可以将这些列或列的组合定