大作业任务实验报告.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 属性。
外关键字约束定义了表之间的关系。
当一个表中的一个列或多个列的组合和其它表中的主关键字定义相同时，就可以将这些列或列的组合定
升级会员 