大作业任务实验报告.docx

1、大作业任务实验报告武汉轻工大学2013-2014学年 第 2学期大型数据库应用期末考核报告专业：班级：学号：姓名：20132014年度第二学期 大型数据库应用期末考核任务1.任务内容1.1以自己的学号为用户名建立用户；1.2 完成以下数据库文件的创建工作：（1）创建一个永久性的表空间TEST，表空间采用自动分区管理方式，其对应的数据文件名称为USERTEST.DBF，大小为50M，并将该表空间设置为数据库默认的表空间。CREATE TABLESPACE TEST DATAFILE F:appwangluoradataorclUSERTEST.DBF SIZE 50M EXTENT MANAGE

2、MENT LOCAL AUTOALLOCATE;ALTER DATABASE DEFAULT TABLESPACE TEST;（2）为数据库添加一个重做日志文件组，组内包含三个成员文件，成员文件的大小为3M。ALTER DATABASE ADD LOGFILE GROUP 5 (F:appwangluoradataorclredo05a.log, F:appwangluoradataorclredo05b.log, F:appwangluoradataorclredo05c.log)SIZE 5M;1.3使用SQL语言创建下列关系表：（1）CHOICE表（选课表）字段名字段说明字段类型是否允许

3、为空S_NO学号字符串否COURSE_NO该学号学生选课课号字符串否SCORE该课程该学生的成绩整数是主键：(S_No, Course_no)CONNECT S110502221/S110502221;CREATE TABLE CHOICE( S_NO VARCHAR(10) NOT NULL , COURSE_NO VARCHAR(10) NOT NULL, SCORE NUMBER(2) , PRIMARY KEY(S_NO,COURSE_NO) );（2）CLASS表（班级表）字段名字段说明字段类型是否允许为空CLASS_NO班级编号字符串否CLASS_NAME班级名称字符串否DEPT_

4、NO班级所在系部编号字符串否主键：CLASS_NOCREATE TABLE CLASS( CLASS_NO VARCHAR(10) PRIMARY KEY , CLASS_NAME VARCHAR(20) NOT NULL, DEPT_NO VARCHAR(10) NOT NULL );（3）COURSE表（课程表）字段名字段说明字段类型是否允许为空COURSE_NO课程编号字符串否COURSE_NAME课程名称字符串否主键：COURSE_NOCREATE TABLE COURSE( COURSE_NO VARCHAR(10) PRIMARY KEY , COURSE_NAME VARCHAR

5、(20) NOT NULL );（4）DEPARTMENT表（系部表）字段名字段说明字段类型是否允许为空DEPT_NO系部编号字符串否DEPT_NAME系部名称字符串否主键：DEPT_NOCREATE TABLE DEPARTMENT( DEPT_NO VARCHAR(10) PRIMARY KEY , DEPT_NAME VARCHAR(20) NOT NULL );（5）STUDENT表（学生表）字段说明：字段名字段说明字段类型是否允许为空S_NO学号字符串否S_NAME学生姓名字符串否S_SEX性别字符串否S_BIRTHDAY出生日期日期是S_SCORE入学总分整数是S_ADDF入学附加

6、分整数是CLASS_NO所在班级编号字符串否主键：S_NOCREATE TABLE STUDENT( S_NO VARCHAR(10) PRIMARY KEY , S_NAME VARCHAR(20) NOT NULL , S_SEX VARCHAR (2) NOT NULL, S_BIRTHDAY DATE, S_SCORE NUMBER(3), S_ADDF NUMBER(2), CLASS_NO VARCHAR(10) NOT NULL); （6）TEACHER表（教师表）字段说明：字段名字段说明字段类型是否允许为空T_NO教师编号字符串否T_NAME教师姓名字符串否T_SEX性别字符串

7、否S_BIRTHDAY出生日期日期是TECH_TITLE职称字符串否AGE年龄整数是SALARY薪水小数是主键：T_NOCREATE TABLE TEACHER( T_NO VARCHAR(10) PRIMARY KEY , T_NAME VARCHAR(20) NOT NULL , T_SEX VARCHAR (2) NOT NULL, T_BIRTHDAY DATE, TECH_TITLE VARCHAR(5) NOT NULL , AGE NUMBER(2), SALARY NUMBER);（7）TEACHING表（教师授课表）字段说明：字段名字段说明字段类型是否允许为空T_NO教师编号

8、字符串否COURSE_NO所带课程编号字符串否主键：（T_NO，COURSE_NO）CREATE TABLE TEACHING(T_NO VARCHAR(10) NOT NULL , COURSE_NO VARCHAR(10) NOT NULL ,PRIMARY KEY(T_NO, COURESE_NO);1.3建立约束：各关系表的约束要求如下外键约束：（1）CHOICECOURSE关系表名字段名关系COURSECOURSE_NO主键CHOICECOURSE_NO外键ALTER TABLE CHOICEADD CONSTRAINT CS1 FOREIGN KEY(COURSE_NO) REFE

9、RENCES COURSE(COURSE_NO);（2）CHOICESTUDENT关系表名字段名关系STUDENTS_NO主键CHOICES_NO外键ALTER TABLE CHOICEADD CONSTRAINT CS2 FOREIGN KEY(S_NO) REFERENCES STUDENT (S_NO);（3）CLASS DEPARTMENT关系表名字段名关系DEPARTMENTDEPT_NO主键CLASSDEPT_NO外键ALTER TABLE CLASSADD CONSTRAINT CS3 FOREIGN KEY(DEPT_NO) REFERENCES DEPARTMENT (DEP

10、T_NO);（4）STUDENTCLASS关系表名字段名关系CLASSCLASS_NO主键STUDENTCLASS_NO外键ALTER TABLE STUDENTADD CONSTRAINT CS4 FOREIGN KEY(CLASS_NO) REFERENCES CLASS(CLASS_NO);（5）TEACHINGTEACHER关系表名字段名关系TEARCHERT_NO主键TEARCHINGT_NO外键ALTER TABLE TEACHINGADD CONSTRAINT CS5 FOREIGN KEY(T_NO) REFERENCES TEACHER (T_NO);（6）TEARCHING

11、COURSE关系表名字段名关系COURSET_NO主键TEARCHINGCOURSE_NO外键ALTER TABLE TEACHINGADD CONSTRAINT CS6 FOREIGN KEY(COURSE_NO) REFERENCES COURSE (COURSE_NO);检查性约束： STUDENT表的S_SEX字段和TEACHER表的T_SEX字段取值均为男或女。ALTER TABLE STUDENT ADD CHECK (S_SEX IN(男,女);ALTER TABLE TEACHER ADD CHECK (T_SEX IN(男,女);STUDENT表的S_SEX字段和TEACHE

12、R表的T_SEX字段取值均为男或女。1.4使用SQL语言插入下列所示的数据CHOICE表CLASS表COURSE表TEARCHING表DEPARTMENT表STUDENT表deTEARCHER表表departmentINSERT INTO DEPARTMENT VALUES(02,计算机);INSERT INTO DEPARTMENT VALUES(03,数学系);INSERT INTO DEPARTMENT VALUES(04,电气系);表classINSERT INTO CLASS VALUES(0202,计算机2班,02);INSERT INTO CLASS VALUES(0302,数学

13、3班,03);INSERT INTO CLASS VALUES(0303,数学3班,03);INSERT INTO CLASS VALUES(0401,电气1班,04);课程表INSERT INTO COURSE VALUES(01,数学分析);INSERT INTO COURSE VALUES(02,模拟电路);INSERT INTO COURSE VALUES(03,数字电路);INSERT INTO COURSE VALUES(04,计算机文化);INSERT INTO COURSE VALUES(05,离散数学);INSERT INTO COURSE VALUES(13,线性代数);I

14、NSERT INTO COURSE VALUES(06,高等数学);INSERT INTO COURSE VALUES(07,邓小平理论);INSERT INTO COURSE VALUES(08,马克思哲学);INSERT INTO COURSE VALUES(09,大学物理);INSERT INTO COURSE VALUES(10,大学语文);INSERT INTO COURSE VALUES(11,数据结构);INSERT INTO COURSE VALUES(12,C语言);学生表INSERT INTO STUDENT VALUES(020201,张三,男,560,0,0202);I

15、NSERT INTO STUDENT VALUES(020202,李四,男,578,0,0202);INSERT INTO STUDENT VALUES(030201,王五,女,545,10,0302);INSERT INTO STUDENT VALUES(030202,丁一,女,589,0,0302);INSERT INTO STUDENT VALUES(030301,周一,女,570,0,0303);INSERT INTO STUDENT VALUES(030302,牛二,男,609,0,0303);INSERT INTO STUDENT VALUES(030303,阮七,女,603,0,

16、0303);INSERT INTO STUDENT VALUES(040101,苏山,女,548,0,0401);老师表INSERT INTO TEACHER VALUES(000001,教师一,男,教授,56,4800);INSERT INTO TEACHER VALUES(000002,教师二,女,教授,55,4333);INSERT INTO TEACHER VALUES(000003,教师三,男,副教授,43,3342);INSERT INTO TEACHER VALUES(000005,教师四,女,讲师,29,2234);INSERT INTO TEACHER VALUES(0000

17、06,教师五,男,讲师,30,2312);INSERT INTO TEACHER VALUES(000007,教师六,女,讲师,45,2313);授课表INSERT INTO TEACHING VALUES(000001,01);INSERT INTO TEACHING VALUES(000001,06);INSERT INTO TEACHING VALUES(000001,13);INSERT INTO TEACHING VALUES(000002,02);INSERT INTO TEACHING VALUES(000002,03);INSERT INTO TEACHING VALUES(0

18、00003,07);INSERT INTO TEACHING VALUES(000003,08);INSERT INTO TEACHING VALUES(000005,05);INSERT INTO TEACHING VALUES(000006,04);INSERT INTO TEACHING VALUES(000006,12);INSERT INTO TEACHING VALUES(000007,11);选课表INSERT INTO CHOICE VALUES(020201,07,83);INSERT INTO CHOICE VALUES(020201,08,79);INSERT INTO

19、CHOICE VALUES(020201,09,78);INSERT INTO CHOICE VALUES(020201,11,92);INSERT INTO CHOICE VALUES(020201,12,95);INSERT INTO CHOICE VALUES(020201,04,86);INSERT INTO CHOICE VALUES(020202,13,67);INSERT INTO CHOICE VALUES(020202,07,89);INSERT INTO CHOICE VALUES(020202,08,77);INSERT INTO CHOICE VALUES(020202

20、,09,80);INSERT INTO CHOICE VALUES(020202,11,90);INSERT INTO CHOICE VALUES(020202,12,93);INSERT INTO CHOICE VALUES(020202,04,88);INSERT INTO CHOICE VALUES(030201,01,79);INSERT INTO CHOICE VALUES(030201,04,77);INSERT INTO CHOICE VALUES(030201,07,88);INSERT INTO CHOICE VALUES(030201,08,86);INSERT INTO

21、CHOICE VALUES(030201,10,77);INSERT INTO CHOICE VALUES(030202,01,55);INSERT INTO CHOICE VALUES(030202,04,54);INSERT INTO CHOICE VALUES(030202,07,67);INSERT INTO CHOICE VALUES(030202,08,65);INSERT INTO CHOICE VALUES(030202,10,70);INSERT INTO CHOICE VALUES(040101,02,78);INSERT INTO CHOICE VALUES(040101

22、,03,82);INSERT INTO CHOICE VALUES(040101,04,78);INSERT INTO CHOICE VALUES(040101,07,67);INSERT INTO CHOICE VALUES(040101,08,76);INSERT INTO CHOICE VALUES(040101,09,86);INSERT INTO CHOICE VALUES(030202,13,78);INSERT INTO CHOICE VALUES(030202,05,80);INSERT INTO CHOICE VALUES(030201,13,86);INSERT INTO

23、CHOICE VALUES(030201,05,79);1.5 完成以下查询要求，给出实现该要求的SQL语句（1）查询入学总分高于平均总分的学生的学号、姓名、总分、班级编号、班级名信息，并将显示结果按班级编号、学号排序。select s_no,s_name,s_score,class.class_no,class_name,dept_nofrom student,classwhere student.class_no=class.class_no and student.s_score(select avg(s_score) from student) order by student.s_n

24、o,class.class_no;（2）创建一个名为STUDENT_NEW的新表，将STUDENT表中的记录复制到该新表中。（3）将STUDENT中叫“丁一”的学生的学号修改为“040102”，班级编号修改为“0401”。 （4）列出计算机文化课程的成绩高于“王五”的学生名单。（5）分组统计选修各门课程的学生人数。1.6 完成以下PL/SQL编程的相关要求 （1）设计一个存储过程，用来接受用户输入的学号，然后显示出该学号对应学生的所有选修课程名及课程成绩。CREATE OR REPLACE PROCEDURE show_choice(sno CHOICE.S_NO%TYPE)IS cursor

25、 c_choice is SELECT COURSE_NAME,SCOREFROM CHOICE,COURSE WHERE CHOICE.S_NO=sno and CHOICE.COURSE_NO=COURSE.COURSE_NO ;p_cname COURSE. COURSE_NAME%TYPE;p_scoure CHOICE.SCORE %TYPE;BEGIN OPEN c_choice; LOOP FETCH c_choice INTO p_cname,p_scoure;EXIT WHEN c_choice%NOTFOUND;DBMS_OUTPUT.PUT_LINE(p_cname| |

26、p_scoure);END LOOP; END ;（2）设计一个函数，用来接受用户输入的课程名，然后显示出所有选修了该课程学生的成绩的平均成绩。CREATE OR REPLACE PROCEDURE show_avgrade(cname COURSE.COURSE_NAME%TYPE)IS cursor c_choice is SELECT S_NO,AVG(SCORE)FROM CHOICE ,COURSE WHERE CHOICE.COURSE_NO=COURSE.COURSE_NOAND COURSE_NAME=cname GROUP BY S_NO;p_choice CHOICE%RO

27、WTYPE;BEGIN OPEN c_choice; LOOP FETCH c_choice INTO p_choice.s_no,p_choice.score;DBMS_OUTPUT.PUT_LINE(p_choice.S_NO| |p_choice.score);EXIT WHEN c_choice%NOTFOUND;END LOOP; END ;（3）设计一个触发器，用于在更新了课程表内容后，更新与课程表有关联的表的内容。1.7 将创建的表进行逻辑备份 exp S110502221/S110502221 grants=y tables=(DEPARTMENT,CLASS,STUDENT,

28、TEACHER,CHOICE,TEACHING,COURSE) file=d:s110502221 实验总结通过这次课程设计，我学到很多东西，包括表的创建，表约束的管理，表参数的设置，删除表，修改表等操作有了一定的了解，能使用sql语句创建表。主键与外键的约束也很重要，主关键字约束指定表的一列或几列的组合的值在表中具有惟一性，即能惟一地指定一行记录。每个表中只能有一列被指定为主关键字，且IMAGE和TEXT类型的列不能被指定为主关键字，也不允许指定主关键字列有NULL属性。外关键字约束定义了表之间的关系。当一个表中的一个列或多个列的组合和其它表中的主关键字定义相同时，就可以将这些列或列的组合定