《数据库系统》实验大纲 .doc

数据库系统实验指导书(本实验指导书尚未定稿，因此会经常改进，建议大家每次上机前重新下载，由于尚未定稿，可能存在错误，发现后请标注颜色发给lbd，非常感谢)总体介绍一、 实验学时数：9*2二、 实验环境数据库系统：oracle 11Oracle控制台地址：https:/211.87.226.10:1158/emoracle监听地址端口：211.87.226.10:1521oracle SERVICE_NAME: orclOracle实例名：orclFtp服务器地址：211.87.226.10，用户密码：db/dbsystem主用户/密码：userID/123 (ID为本人学号,以下相同,例如user1)备用用户/密码：userbID/123 三、 课程的任务 数据库系统是计算机科学类各专业的专业基础课，通过本课程的实验，使学生将课堂讲解地内容通过实际操作，进一步了解其工作原理，加深、巩固课堂所学内容，主要实验的内容有表的建立/修改/删除/查询、主键的建立/修改/删除、视图的建立/删除、触发器的建立/删除、嵌入SQL。四、 实验要求1、 准时到达指定机房上机，不迟到早退。2、 每次上机尽量固定使用同一台机器。3、 独立完成实验作业。4、 本验大纲所有ID都要填写自己的的学号。5、 每一节实验内容完成后，提交实验并检查你的完成情况。五、 特别要求1、 要把实验当做实际开发工作一样对待，严格按设计要求执行，表名、列名、类型、长度、以及数据等严格按要求，不能够有省略或者简化。实际工作中对数据库的操作不仅仅是一门技术，更是一种技能，为什么是一种技能？你虽然会操作，别人一个小时完成的工作，你需要一天才能够完成，或者完成以后被发现和要求的不一致，还需要重新返工，而且有的时候操作失误可能带来无法挽回的损失。因此，本实验要求大家严格按大纲要求正确输入表名、列名及数据。2、 学习计算机经常出现的现象就是，“一学就全会了，一做就全完成，一验收全错了（张冠李戴、缺斤少两），一指点全改了”。所以，从开始就要严格要求自己，实际工作中，一点点也不能够有错误，从开始就养成一个严谨的好习惯。有时候前面错误会给后来工作带来很大的麻烦，例如：表名、列名错了，往往在发现错误的时候，数据已经输入很多，此时修改，需要先备份、再改表、再恢复数据。六、 实验案例本次课程实验中，使用教师上课及学生选课的案例来贯穿整个实验阶段。其中的实体包括：学生信息、教师信息、课程信息、院系信息，联系包括：学生选课信息、教师授课信息、学生属于一个院系，教师在一个院系中工作。部分E-R图如下：七、 Oracle相关知识简介1、 伪列：所谓伪列就是表中不存的列，可是像使用其他存在的列一样访问这些列。常用的伪列有today、now、rownum、rowid、sysdate。2、 常用的几个函数：to_char()、to_date(,yyyymmdd) 、求子串substr()。3、 Create table 表名 as select 语句将查询结果自动创建一个新表，当select查询语句存在表达式的，可以通过属性更名来指定列名。例如：create table student_avg_score as select sno,sname,avg(score) avg_score from 来指定列名。4、 查询当前用户的所有表select * from tab，查询所有人的所有表select * from all_tables5、 Oracle中没有except关键词，与其等价的是minus关键词。在使用minus时，select语句不能够使用括号，例如select * from student where minus select * from student ,错误写法是（select * from student where ） minus （select * from student ）。6、 表别名的定义不能够有As，例如正确的写法select * from student s，错误的写法是 select * from student as s。7、 查询表的列属性的命令desc pub.student八、 数据库空间配额1、 userID用户可用空间200m，userbID用户可用空间50m。2、 如果出现错误：“ORA-01536: 超出表空间 'USERS' 的空间限额”，请删除已经提交并有成绩的相关作业的表或者数据。九、 提交实验、查询成绩 学生按照实验要求完成当天要求的作业后，首先执行commit提交所有操作(如果选中自动提交，相当于每条语句执行完成后自动执行commit)，然后通过执行update dbtest set 实验号=1（1代表实验一，2代表实验二）及commit提交本周实验，后台系统的数据库实验平台系统会在5秒之内自动检查你的作业完成情况，并形成你的本次实验成绩，你可以随时查询你的作业完成情况，只需要通过执行select *from dbscore。如果验证没有通过，你可以根据错误描述进行修改，再重复上面的提交。十、 评分标准1、 评分标准，实验按时完成计全分，超时完成计60%。2、 最后按总分数折算计入数据库系统考试成绩。十一、 测试数据由于学生不可能在实验期间插入大量的数据，因此实验课之前老师已经在数据库中建了一个公用用户pub，在这个用户下建立了实验用表，并且插入了大量的实验数据，本实验主要内容就是根据这些表进行操作。这些表已经授权给所有用户可以进行查询，但是不能够修改里面的数据。表名类型数据行数说明COURSE表140课程信息DEPARTMENT表7院系信息DEPARTMENT_31表20院系信息1-实验三专用STUDENT表4000学生信息STUDENT_31表4000学生信息-实验三专用STUDENT_32表3000学生信息-实验三专用STUDENT_41表4000学生信息-实验四专用STUDENT_COURSE表14000学生选课STUDENT_COURSE_42表14000学生选课-实验四专用STUDENT_TRIGGER表0学生信息-实验七专用TEACHER表200教师信息TEACHER_COURSE表40教师授课信息TEST8_INSERT视图嵌入SQL实验专用TEST8_SELECT视图嵌入SQL实验专用TEST8_UPDATE视图嵌入SQL实验专用实验一 熟悉环境、建立/删除用户、建立/删除表、插入数据(2学时) 一、 实验内容利用oracle管理平台登入本人主用户userID，例如user1，在主用户下，创建如下5个表，合理确定每一个表的主键并建立主键，为表建立必要的索引，并准确输入表格中的3行数据。表名采用英文、列名采用中文，有not null的列代表不允许为空。1 教师信息（表名：teacher）：教师编号 char 6 not null、教师姓名 varchar 10 not null、性别 char 2、年龄 int、院系名称 varchar 10。根据教师名称建立一个索引。教师编号教师姓名性别年龄院系名称张老师李老师马老师男女男444546计算机学院软件学院计算机学院2 学生信息（表名：student）：学号 char 12 not null、姓名varchar 10 not null、性别 char 2、年龄 int、出生日期 date（oracle的date类型是包含时间信息的，时间信息全部为零）、院系名称 varchar 10、班级 varchar(10)。根据姓名建立一个索引。学号姓名性别年龄出生日期院系名称班级123王欣李华赵岩女女男1994-2-21995-3-31996-4-4计算机学院软件学院软件学院2010200920093 课程信息（表名course）：课程号char 6 not null、课程名 varchar 10 not null、先行课程号char 6、学分 numeric 2，1。根据课程名建立一个索引。课程号课程名先行课程号学分数据结构数据库操作系统22.544 学生选课信息（表名：student_course）：学号 char 12 not null、课程号 char 6 not null、成绩 numeric 5，1、教师编号char 6。学号课程号成绩教师编号11191.592.693.75 教师授课信息（表名：teacher_course）：教师编号 char 6 not null，课程号char 6 not null。教师编号课程号二、 重点提醒1 登入号为userID，ID为本人学号，例如user1，密码123。2 Oracle管理平台一次只能够执行一条语句，如果输入了多条，可以通过选中一条执行。3 执行非select前取消“只执行SELECT”。4 执行插入删除修改语句前选中“自动提交”,相当于每句话后自动执行了commit。5 执行完一句话后，一定要看执行结果信息。6 输入日期类型数据：date2001-2-2格式例如insert into t1 values(date2012-02-02)或者to_date(,yyyymmdd)函数例如：insert into t1 values(to_date(,yyyymmdd)7 完成作业后，提交本次实验，如果有错误，修改以后重新提交实验。8 如果建表错误，可以通过drop table tablename删除对应表后重建。9 将执行完成语句，拷贝到记事本中保存备用。三、 实验步骤1 启动oracle管理平台，浏览器地址输入：https:/211.87.226.10:1158/em2 显示登入画面，使用本人账号/密码：userID/123（ID为本人学号）登入系统。3 点击SQL工作表，系统打开sql工作表窗口，就可以执行SQL命令了。4 创建上面要求的5个表student，course，teacher，student_course，teacher_course，实际工作中建表不能够有一点偏差，因此从现在开始就养成严谨的工作习惯，要求学生严格按上面设计正确输入表名、列名、类型、长度。5 为5个表插入表格中要求的3行数据，每张表最少插入此3行数据，插入的数据可以多于3行。如果没有选中自动提交，插入语句执行完成后，只有通过执行commit完成提交，数据才会真的写入表中。（如果上课还没有讲到插入语句，请参考图上的例子完成，注意空值的插入使用null），插入日期类型数据时如果出错，可以通过使用to_date函数实现日期类型数据输入，如果to_date(,yyyymmdd)。6 特别提醒：不要因为插入数据不是程序员的工作，实际工作中正确插入测试数据是完全必要的，而且测试数据也应该随意输入，本实验要求插入的3行数据必须和要求的绝对一致，也不能够有多余的空格，否则作业将无法通过验证。7 执行“update dbtest set 实验号=1” 及commit提交本次实验，5秒钟后执行“select * from dbscore”查询本次实验完成情况，如果有错误修正后，重新提交实验。8 本实验总得分为10分，代表本实验全部通过，可以下机了。实验二 检索查询(2学时) 一、 实验内容在oracle管理平台，针对公共用户pub下的表，完成下面的查询，你认为查询语句正确以后，将查询得出的数据创建成一个新表，新表表名为test2_(题号，题号长度两位，前面补零),例如test2_03。例如：找出所有有选课学生的学号、总成绩。答案：select 学号,sum(成绩) from pub.student_course group by 学号执行（注意别名的写法和用途）：create table test2_01 as select 学号,sum(成绩) 总成绩 from pub.student_course group by 学号二、 重点提醒1 首先要执行查询语句，根据结果判断你的语句是否正确，正确以后才建表。2三、 实验题目1 找出没有选修任何课程的学生的学号、姓名。2 找出至少选修了学号为“7”的学生所选修的一门课的学生的学号、姓名。3 找出至少选修了一门其先行课程号为“”号课程的学生的学号、姓名。4 求至少选修了“操作系统”以及“数据结构”课程的学生的学号、姓名。5 查询20岁的所有有选课的学生的学号、姓名、平均成绩、总成绩（平均成绩四舍五入到个位）Test2_05有四个列，并且列名必须是：学号、姓名、平均成绩、总成绩。通过下面方式实现列名定义：create table test2_05 as select 学号,姓名，（表达式） 平均成绩,（表达式） 总成绩 from 6 查询所有课以及这门课的最高成绩，test2_06有两个列：课程号、最高成绩7 查询所有不姓张、不姓李、也不姓王的学生的学号、姓名8 查询学生表中每一个姓氏及其人数（不考虑复姓），test2_08有两个列：姓氏、人数9 查询选修了号课程的学生的学号、姓名及成绩10 查所有有成绩记录的学生学号和课程号四、 实验步骤1 登入主用户2 利用pub用户的下6个表，完成相应的查询语句，检查得到的结果正确性，直到你认为完全正确。3 通过create table test2_01 as select将查询结果创建到对应的表。4 完成所有的题目。5 要求所有需要创建的表以及数据，只能通过一条create table as语句完成，如果你发现结果不对，通过drop table删除已经创建的表后重新重建，不要通过delete等语句或者其他办法来修正test表中的数据。6 执行“update dbtest set 实验号=2”提交本次实验，执行“select * from dbscore”查询本次实验完成情况，如果有错误修正后，重新提交实验。实验三 复制表、修改表结构、修改数据(2学时)一、 实验内容利用oracle管理平台完成对表的结构、数据进行修改，每一个问题可以通过多个SQL语句完成。二、 实验步骤1 将pub用户下表student_31及数据复制到主用户的表test3_01中，使用alter table语句为test3_01增加五个列：“总成绩”、 “平均成绩”（四舍五入到个位）、“总学分”、“排名”、“院系编号” varchar(2)。（1） 使用update语句,利用pub.student_course、pub.course，统计 “总成绩”；（2） 使用update语句,利用pub.student_course、pub.course，统计“平均成绩”；（3） 使用update语句,利用pub.student_course、pub.course，统计 “总学分”；（4） 将总成绩的名次放入“排名”列中，总成绩相同的学生的排名也相同（即一个名次可以多个人），没有成绩的排名置空值。（提醒：可是使用oracle的rownum伪列以及distinct实现）；（5） 利用pub.department,pub.department_31两个表，填写院系编号内容，没有对应的院系编号填写为00。2 将pub用户下的Student_32及数据复制到主用户的表test3_02,对test3_02中的数据进行整理，修复那些不规范的数据：（6） 剔除姓名列中的所有空格；（7） 剔除院系名称列中的所有空格； （8） 对性别列进行规范（需要先确定哪些性别数据不规范，也就是那些和大多数不一样的就是不规范的）；（9） 对班级列进行规范（需要先确定哪些班级不规范）。（10） 年龄为空值的根据出生日期设置年龄列，年龄不为空值的数据不要改变。3 在学生表pub.student中统计名字（姓名的第一位是姓氏，其余为名字，不考虑复姓）的使用的频率，将统计结果放入test3_11中，表结构如下。名字 varchar(4)频率 numeric(4)国强1034红1232卫东23234 在学生表pub.student中统计名字（姓名的第一位是姓氏，不作统计，名字指姓名的第二个之后的汉字）的每个字使用的频率，将统计结果放入test3_12中，表结构如下。字 varchar(2)频率 numeric(4)锋1034红1232鹏23235 创建“学院班级学分达标情况统计表1”test3_13，依据pub.student， pub.course，pub.student_course统计形成表中各项数据，成绩>=60为及格计入学分，总学分>=10为达标，院系为空值的数据不统计在下表中，表结构如下：院系名称 varchar(30)班级varchar(10)学分达标人数int学分未达标人数int总人数int计算机学院2006计算机学院2007软件学院20066 创建“学院班级学分达标情况统计表2”test3_14，依据pub.student， pub.course，pub.student_course统计形成表中各项数据，成绩>=60为及格计入学分，2008级及之前的班级总学分>=8为达标，2008级之后的班级学分>=10未达标，院系为空值的数据不统计在下表中，表结构如下：院系名称 varchar(30)班级varchar(10)学分达标人数int学分未达标人数int总人数int计算机学院2006计算机学院2007软件学院20067 执行“update dbtest set 实验号=3”提交本次实验，执行“select * from dbscore”查询本次实验完成情况，如果有错误修正后，重新提交实验。实验四 复制表、删除数据 (2学时) 一、 实验内容利用oracle管理平台，将pub用户的表及数据复制到主用户下，对不符合要求的数据进行删除。二、 实验步骤1 将pub用户下的Student_41及数据复制到主用户的表test4_01，删除test4_01中的错误数据，不规范的数据也被认为是错误的数据。（1） 学号不是12位数字的；（2） 出生日期和年龄不一致的；用到的函数extract()（3） 姓名有空格的；（4） 姓名长度小于2个字的；用到的函数length()（5） 性别有错误的（只能够是“男”、“女”、空值）；（6） 院系名称有空格的、院系名称为空值的；（7） 院系名称小于3个字的；（8） 班级数据有错误的（需要先找到班级里面的错误,）。保留最后正确的数据。2 将pub用户下的Student_course_42及数据复制到主用户的表test4_02，删除其中的错误数据，错误指如下情况：（1） 学号在学生信息pub.student中不存在的；（2） 课程号在课程信息pub.course中不存在的；（3） 教师编号在教师信息pub.teacher中不存在的；（4） 课程号和教师编号在教师授课表pub.teacher_course中不存在的；（5） 成绩数据有错误（需要先找到成绩里面的错误）。保留最后正确的数据。3 在学生表pub.student中去除重复姓名后统计名字（姓名的第一位是姓氏，其余为名字，不考虑复姓）的使用的频率，将统计结果放入test4_03中，表结构如下。不同于实验三的是对于姓名完全一样的，只统计一次，例如“王国强”出现三次但是只统计一次到“国强”中。名字 varchar(4)频率 numeric(4)国强1034红1232卫东23234 在学生表pub.student中去除重复姓名后统计名字（姓名的第一位是姓氏，不作统计，名字指姓名的第二个之后的汉字）的每个字使用的频率，将统计结果放入test4_04中，表结构如下。不同于实验三的是对于姓名完全一样的，只统计一次，例如“王国强”出现三次但是只统计一次到“国”，统计一次到“强”中。字 varchar(2)频率 numeric(4)锋1034红1232鹏23235 执行“update dbtest set 实验号=4”提交本次实验，执行“select * from dbscore”查询本次实验完成情况，如果有错误修正后，重新提交实验。实验五 数据修改的提交和回退、实体授权(2学时)一、 实验内容oracle管理平台，启动两个数据库连接，测试提交和回退的作用。二、 配置oracle客户端、启动sqlplus1 配置oracle客户端可以通过下面步骤实现，或者直接修改tnsnames.ora2 启动sqlplus输入账号、密码、主机自付款3 Oracle11g客户端下启动sqlplus方式：进入cmd 模式，键入sqlplus,输入账号/密码服务器,启动sqlplus，这样才具有粘贴、复制功能。4 在不配置客户的情况下，直接登入服务器的方法：Sqlplus 用户/密码服务器ip地址/实例名例如：Sqlplus user4/123211.87.226.10/orcl三、 实验步骤1 启动两个不同的浏览器（例如一个微软的浏览器、一个360的浏览器）或者在两个电脑上启动两个浏览器或者启动一个sqlplus，一个浏览器使用主用户登入后进入sql工作表，关闭自动提交，简称主窗口，另外一个使用备用用户登入后进入sql工作表，关闭自动提交，简称备用窗口。2 在备用账号下将pub.teacher复制到test5_00中，然后将其所有权限给主用户。3 按表中序号在相应窗口执行对应的命令（主用户访问备用用户表需要授权）。执行序号窗口执行语句1备用窗口Update test5_00 set 年龄=年龄+12备用窗口Create table test5_01 as select * from test5_003主窗口Create table test5_02 as select * from userb_ID.test5_004主窗口Create table test5_03 as select * from userb_ID. test5_015备用窗口Rollback6备用窗口Create table test5_04 as select * from test5_007备用窗口Create table test5_05 as select * from test5_018备用窗口Update test5_00 set 年龄=年龄-29备用窗口Commit10备用窗口Create table test5_06 as select * from test5_0011主窗口Create table test5_07 as select * from userb_ID.test5_0012主窗口Rollback13备用窗口Rollback14备用窗口Create table test5_08 as select * from test5_0015备用窗口rollback4 假设张老师的年龄为88岁，通过上面的操作以后，他在各个表中的年龄应该是多少？根据你的判断或者实验，在主用户下创建一个表test5_10(表名 varchar(20),年龄 numeric (3),插入9行数据，分表存放9个表名、张老师在对9个表中的年龄输入到test5_10中。表名年龄TEST5_0088TEST5_01TEST5_02TEST5_03TEST5_04TEST5_05TEST5_06TEST5_07TEST5_085 执行主用户下“update dbtest set 实验号=5”提交本次实验，执行“select * from dbscore”查询本次实验完成情况，如果有错误修正后，重新提交实验。实验六 创建视图、删除视图(2学时) 一、 实验内容oracle管理平台，针对公共用户pub下的表，完成创建视图、查询验证视图、删除视图。视图名为test6_(题号，题号长度两位，前面补零),例如test6_01。二、 实验题目例如：找出年龄小于20岁的所有学生的学号、姓名、年龄正确执行:create view test6_00 as select 学号、姓名、年龄 from pub.student where 年龄>201 找出年龄小于20岁且是“物理学院”的学生的学号、姓名、院系名称。2 查询统计2009级、软件学院学生总成绩表，内容有学号、姓名、总成绩。3 查询2010级、计算机科学与技术学院、操作系统的学生成绩表，内容有学号、姓名、成绩。4 找出选修“数据库系统”课程，且成绩大于90的学生的学号、姓名5 找出姓名叫“王成”的学生的学号及其选修全部课程的课程号、课程名和成绩。6 找出选修了所有课程的学生的学号7 找出有选课的学生的学号、姓名8 检索先行课的学分为2的课程号、课程名。9 查询统计2010级、化学与化工学院的学生总学分表，内容有学号、姓名、总学分。10 找出有间接先行课的所有课程的课程编号、课程名称。三、 实验步骤1 登入主用户2 创建视图，通过查询视图数据验证是否正确。3 执行“update dbtest set 实验号=6”提交本次实验，执行“select * from dbscore”查询本次实验完成情况，如果有错误修正后，重新提交实验。实验七 建立触发器、测试触发器(2学时) 一、 实验内容创建触发器需要启动sqlplus完成创建触发器、测试触发器，oracle管理平台没有测试通过。二、 实验步骤1 登入主用户。2 通过语句“create table test7_01 as select * from pub.student where 1>2”创建一个空表test7_01。3 在表test7_01上创建一个触发器trigger_student，当表test7_01中插入一行数据后，自动在表pub.student_trigger中插入一行同样数据，其中列“操作类型”值为“INSERT”； 当test7_01删除一行数据后，自动在pub.student_trigger插入一行同样数据，其中列“操作类型”值为“DELETE”。4 在表test7_01中增加一行本人学号信息的数据，提交，查询pub.student_trigger中是否插入的响应的数据；如果成功插入且数据正确，说明触发器部分正确。再删除这一行数据，查看pub.student_trigger是否正确。5 如果创建的触发器无法正确形成响应数据，请删除触发器后重新建立。6 执行“update dbtest set 实验号=7”提交本次实验，执行“select * from dbscore”查询本次实验完成情况，如果有错误修正后，重新提交实验。实验八 嵌入SQL /动态SQL(4学时)一、 实验内容利用客户端开发工具powerbuilder、java、VC等，采用嵌入SQL/动态SQL，开发一个客户端程序，给pub.test8_insert插入一行数据，立刻循环等待数据库后台系统给pub.test8_select插入数据，后台系统每5秒钟定时执行一次，会在1秒钟之内分组形成500个随机数（每组数据行数不固定，但是不会超过10行），且形成下一组数据的之前，自动删除上一租数据，客户端程序的就是要及时读取pub.test8_select中的数据，然后将读取到的数据写入pub.test8_update中。实验的另外一个目的，学会如何与其他系统协同工作的技巧。二、 Pub用户相关视图说明1 视图的权限视图权限TEST8_INSERTselect,insert,deleteTEST8_SELECTselect,deleteTEST8_UPDATEselect,update2 视图的结构视图列TEST8_INSERT学号 varchar(12)姓名 varchar(10)TEST8_SELECT学号 varchar(12)序号 numeric(3)随机数 numeric (10)TEST8_UPDATE学号 varchar(12)序号 numeric(3)随机数 numeric (10)三、 功能详细要求1 向pub.test8_insert中插入本人学号、姓名。实验平台系统检测到你插入test8_insert数据后，开始在pub.test8_select中分多组（每组数据行数不固定，但是不超过10行）插入500行学号是你本人的，序号从1到500的数据，每一组插入新的数据前，后台会先删除上一组的数据，整个过程不超过1秒钟。2 客户在插入数据后立刻循环查询pub.test8_select中数据，将里面的数据读出来，根据序号将随机数写入视图pub.test8_update中。读取到序号为501的数据后，结束循环。3 由于实验平台系统是oracle系统系统内部程序，运行速度很快，产生每租数据前都会删除上一组插入的数据，这需要你的程序尽可能在没有删除前读出来，开发的客户端程序能够做到最少10行的随机数被复制到pub.test8_update中。4 等写入超过10个数后，在oracle管理平台上执行“update dbtest set 实验号=8”提交本次实验，执行“select * from dbscore”查询本次实验完成情况，如果有错误修正后，重新提交本次实验。5 每次开始进行运行测试时，都需要删除上次的数据，然后才能够进行下一次的测试。6 系统流程简述：删除上次的数据、插入test8_insert数据、循环检测读取test8_select中数据直至遇到序号为501结束，写入test8_update，如果正确读取写入超过10条，就可以提交本次试验。7 应用程序例子四、 后台系统工作流程图