数据库实验指导书1

1、数据库系统设计实验指导书II目 录一、实验教学目标2二、实验教学主要内容2三、实验要求2四、评分细则3五、实验项目3实验一 设计数据库4实验二 CDM转换成PDM并创建Table13实验三 通过PL/SQL Developer创建表33实验四 备份和恢复数据库37实验五 数据库查询与更新41实验六 创建视图、函数和过程46实验七 综合实验（1）48实验八 综合实验（2）49一、 实验教学目标数据库系统设计是一门需要理论和实际紧密相连的课程，需要动手、动手再动手。上机实验是课程教学的重要环节，实验指导书中给出的方法和技术就是现在的软件开发公司所使用的最新的技术，是掌握了这些技术后可以找到吃饭本事

2、的技术。通过实验，使学生掌握数据库的基本概念，结合实际的操作和实验，巩固课堂教学内容，使学生掌握数据库系统的基本概念、建模技术、设计方法，将理论与实际相结合，应用流行的数据建模工具和数据库管理系统软件，培养学生实际动手能力。二、 实验教学主要内容本课程的实验主要分成四大部分：其一是数据库应用系统的建模，通过实验，使同学们掌握PowerDesigner建模工具，其二是SQL和PL/SQL语句的熟悉，通过实验，一定要让同学门掌握最流行的Oracle数据库系统开发工具PL/SQL Developer的使用方法，其三是数据库的备份与恢复技术的掌握，这也是在数据库系统的开发、设计、运行便于维护过程中不可

3、以缺少的技术，其四是综合运行所学的知识，遍历数据库系统设计的过程。三、 实验要求每次实验前，教师需向学生讲清实验的整体要求及实现的目标任务，实验1人1组，在规定的时间内，由学生独立完成，出现问题时，教师应引导学生独立分析和解决，所有实验为一整体，有其延续性，每一次实验必须在实验课堂上认真完成，如果不能完成将会影响下次实验，如果确实不能在课堂上完成，必须在下次实验前的课后完成，任课教师每次课都会认真清点学生人数，并要学生在考勤本上签字，每次实验后，必须认真填写实验报告，当次的实验报告在下次实验课开始时提交给老师。四、 评分细则每次实验后，学生把实验的结果提交给老师，文件名的格式为班级学号姓名实验

4、次数，老师认真评阅每次实验的结果，给出以100分评阅的成绩，最后给出平均的实验成绩。实验内容将进行实际操抽查，并以检查情况作为实验成绩的主要来源，再结合每次实验报告情况得出最终实验成绩。五、 实验项目实验一 设计数据库目的和意义1. 掌握将现实世界的事物转换成E-R图的基本技术2. 掌握将E-R图转换成关系表的技术3. 能够正确判断给定表的关键词实验内容分别使用PowerDesigner和PL/SQL Developer工具，按下列要求创建和修改学生学籍管理系统用户数据库。各数据表的结构表1-1 student（学生信息表）字段名称类 型宽 度允许空值键说 明snochar8NOT NULLP

5、K学生学号snameVarchar28NOT NULL学生姓名sexchar2NULL学生性别nativeVarchar220NULL学生籍贯birthdaydateNULL学生出生日期dnochar6NULLFK学生所在院系编号spnochar8NULLFK专业代码classnochar4NULL学生所属班级entimedateNULL学生入校时间homeVarchar240NULL学生家庭住址telVarchar240NULL学生联系电话表1-2 course（课程信息表）字段名称类 型宽 度允许空值键说 明cnoVarchar210NOT NULLPK课程编号spnoVarchar28N

6、ULLFK专业代码cnameVarchar220NOT NULL课程名称ctnointegerNULLFK课程类型编号experimentintegerNULL实验时数lectureintegerNULL授课学时semesterintegerNULL开课学期creditnumber3,1NULL课程学分表1-3 student_course（学生选课成绩表）字段名称类 型宽 度允许空值键说 明snochar8NOT NULLPK FK学生学号cnoVarchar210NOT NULLPK FK课程编号scorenumberNULL学生成绩表1-4 department（院系信息表）字段名称类

7、型宽 度允许空值键说 明dnochar6NOT NULLPK院系编号dnameVarchar220NOT NULL院系名称headerVarchar28NULL院系负责人addrvarchar240NULL院系办公地址telvarchar240NULL联系电话表1-5 speciality（专业信息表）字段名称类 型宽 度允许空值键说 明spnoVarchar28NOT NULLPK专业编号dnochar6NOT NULLFK院系编号spnameVarchar220NOT NULL专业名称表1-6 coursetype（课程类型表）字段名称类 型宽 度允许空值键说 明ctnonumber1NO

8、T NULLPK课程类型编号ctnameVarchar216NOT NULL课程类型名称各数据表之间的关系(物理模型图)各数据表的数据实例表1-7 coursetype表（课程类型表）ctnoctname1公共课2专业基础课3专业课4选修课表1-8 course（课程信息表）cnospnoctnocnameexperimentlecturesemestercreditH610300060806052数据结构246444H610300070806052计算器组成原理06454H610300080806052数据库系统246454H610300090806052操作系统246444H6103001

9、20806052软件工程04863H610300130806052计算机网络164864T530300010806051毛泽东思想概论122462T530300020806051邓小平理论326212X610300050806054面向对象程序设计语言243252X610300120806054Web程序设计163262X610300190806054数据挖掘323272X610300220806054网络安全概论323272X800020020806051软件产品市场营销03282X800020030806051知识管理03282表1-8 department（院系信息表）dnodnameh

10、eaderaddrtel500000人文学院刘东妮长沙市8305424510000外国语学院万世平长沙市8304551520000艺术与设计学院周曼玲长沙市8304530530000法学院李平长沙市8304578540000经济与管理学院刘小明长沙市8304517550000理学院朱喜长沙市8304534560000生命科学学院王林林长沙市8305204590000软件学院张中华长沙市8305682600000建筑学院马力长沙市8304501610000资讯工程学院陈浪长沙市8305697表1-9 speciality（专业信息表）spnodnospname050408520000艺术设计07

11、0102550000信息与计算数学080303520000工业设计080601610000电气工程及自动化080602610000自动化080603610000电子资讯工程080604610000通信工程080605610000软件工程110201540000工商管理110202540000市场营销表1-7 student（学生表）snospnosnamesexNativebirthdayclassnoentimehomeTel02080001080605李毅男北京1985-1-212004-9-1长沙1397311000102080002080605厉超男湖南1984-3-812004-9-

12、1武汉1397311000202080003080605梁冰龙男湖北1984-7-912004-9-1北京1397311000302080004080605罗煦男广东1985-10-1212004-9-1广州1397311000402080005080605马晓睿男北京1983-11-112004-9-1南京1397311000502080006080605聂立业男湖南1984-6-322004-9-1上海1397311000602080007080605王实男湖北1984-2-1122004-9-1长沙1397311000702080008080605伍卉女广东1985-1-322004-9

13、-1武汉1397311000802080009080605伍琼男北京1985-12-1122004-9-1北京1397311000902080010080605曾文超男湖南1984-7-522004-9-1广州1397311001002080011080605黄文杰男湖北1984-7-522004-9-1南京1397311001102080012080605龙淼男广东1985-12-1122004-9-1上海1397311001202080013080605何晓明男北京1984-8-1322004-9-1长沙1397311001302080014080605刘寅男湖南1984-5-722004

14、-9-1武汉1397311001402081001080605邢晓静女湖北1984-8-1332004-9-1北京1397311001502080016080605李明伟女广东1985-3-932004-9-1广州1397311001603080001080605童熙女湖北1985-10-1632004-9-1南京1397311001702080018080605朱俊华男广东1985-12-332004-9-1上1-9 student_course（学生选课成绩表）cnosnoscoreH610300060208000192H610300070208000188H61

15、0300080208000178H610300090208000195H610300120208000193H610300130208000198T530300010208000290T530300020208000285X610300050208000282X610300120208000390X610300190208000383H610300130208000389T530300010208000598T530300020208000586X610300050208000596X610300120208000683X610300190208000686H61030006020800067

16、5H610300070208000687H610300080208000688H610300090208100190H610300120308000150设计数据库的E-R模型(概念模型图)实现步骤一、打开PowerDesigner工具，选择文件菜单中的NEW子菜单，选CDM数据库模型：模型名选择在Model name 出输入要建立的CDM模型的名字CDMStudentCourse（推荐概念模型的命名用CDM开始），按确定按钮，系统出现如下的屏幕(如果没有出现palette面板,则点击【Tools】à【Customize Toolbars】选中palette)：palette二、点击

17、【Tools】à【Model Options】菜单，出现如下界面设置在Model Setting中的Notation选择中设置为E/R+Merise设置在Naming Convention中设置 Code为Uppercase在面板中选取Entity，然后在画布中放置，在实体中输入实体的名称、属性、指定主属性，等。（所有的code都请输入英文字母,name请输入中文）确定实体之间的联系。对实体和属性增加comment（建议给所有的实体和他们的属性添加注释）。建立好的E-R图应该是下面类似于下面的样子：实验完成后请妥善保存CDM文件至你的U盘或你自己的邮箱中，后期实验将使用该文

18、件。实验二 CDM转换成PDM并创建Table目的和意义熟悉把概念模型转换成物理模型的方法，能够熟练地使用PowerDesigner工具，利于此工具完成数据库表的创建和修改等工作，并了解主键、外键以及约束的创建和应用，熟练掌握使用PL/SQL、SQL*PLUS等工具的操作方法。实验内容把实验一产生的CDM图转换成PDM图，然后生成数据库的SQl脚本文件，建立一个Oracle数据库用户Dblab，在Dblab用户下建立学生学籍管理系统用户数据库表。 实现步骤（1） 启动Oracle数据库先启动oracle服务(选择【控制面板】à【管理工具】à【服务】)启动这一个服务就可以了启

19、动Oracle例程()在命令行提示中输入（启动sqlplus）：sqlplus /nolog在Sqlplus命令提示符下输入(用数据库管理员用户连接Oracle数据库)：conn / as sysdba在Sqlplus命令提示符下输入(装载数据库,如果提示Oracle已经启动则表示数据库已经装载。你可以使用 shutdown命令卸载数据库，再使用startup命令装载数据库)：Startup（2） 创建数据库用户Dblab,并使用Dblab用户登录数据库完成数据库启动后，在Sqlplus命令提示符下输入以下命令(创建学生个人的数据库用户)：Drop user Dblab cascade; -创

20、建用户前先删除原有用户信息Create user Dblab identified by Dblab; -创建用户（用户名和口令都是Dblab）Grant connect,resource to Dblab; -给Dblab用户授予连接数据库和获取资源的权限 Conn Dblab/Dblab;-所以用Dblab用户登录数据库（3） 修改CDM并把CDM转换成PDM打开实验一种完成的CDM文件：点击Powerdesigner菜单【Tools】à【Generate Phisical Data Model】：修改物理模型名选择数据库种类点击【确认】生成物理模型（4） 产生数据库脚本点击Po

21、werdesigner菜单【database】à【database generation】设置脚本文件存储目录和文件名查看脚本内容本文件中的内容是生成数据库的sql脚本（5） 直接使用脚本创建数据库直接把SQL脚本粘贴至sqlplus中即可完成数据库的创建；运行 show user; 可以获知当前登录用户名；运行 select * from tab; 可以获取已经创建的表的基本信息；运行desc 表名; 可以获取特定表的表结构;（6） 配置数据库连接参数（配置数据源，如果需要直接进行数据库的创建）配置好了参数后一定要Test一下：如果出现下面的错误，表示监听程序没有启动。监听程序启动

22、的方法是启动它的服务，在服务启动后需要等待大约10秒钟才能再去测试上面的连接。连接到数据源后，输入数据库的用户名和密码，点击connect按钮。（7） 在PowerDesigner中建立数据库选择前面生成的脚本Dblab.sql，特别注意在脚本档中不能出现双引号“”，如果出现了双引号，一定要把它去调。方法是方法提到了的设置参数。在上面的窗口中按“Run”按钮，在下面的窗口中按“Ignore All”按钮。再“Run”一遍，看看还有没有问题！（8） 在PL/SQL中建立数据库转到Command窗口执行数据库的脚本语言（Dblab.sql）（9） Reengineering，从数据库产生PDM和C

23、DM把PDM转换成CDM：（10） PowerDesigner部分技巧a. 由pdm生成建表脚本时，字段超过15字符就发生错误（oracle）：解决办法是打开PDM后，会出现Database的菜单栏，进入Database Edit Current DBMS scriptobjectscolumnmaxlen，把value值调大，比如改成60。出现表或者其它对象的长度也有这种错误的话都可以选择对应的objects照此种方法更改！b. 生成的脚本中如何把对象的双引号去掉？打开cdm的情况下，进入ToolsModel OptionsNaming Convention，把Name和Code的卷标的Ch

24、arcter case选项设置成Uppercase或者Lowercase，只要不是MixedCase就行！或者选择Database->Edit current database->Script->Sql->Format，有一项CaseSensitivityUsingQuote，它的comment为“Determines if the casesensitivity for identifiers is managed using double quotes”，表示是否适用双引号来规定标识符的大小写，可以看到右边的values默认值为“YES”,改为“No”即可！或者在打

25、开pdm的情况下，进入ToolsModel OptionsNaming Convention，把Name和Code的卷标的Charcter case选项设置成Uppercase就可以！c. 建立一个表后,为何检测出现Existence of index的警告A table should contain at least one column, one index, one key, and onereference.可以不检查 Existence of index 这项，也就没有这个警告错误了！意思是说没有给表建立索引，而一个表一般至少要有一个索引，这是一个警告，不用管也没有关系！d. Pow

26、erDesigner 12和Office Word 2003有冲突，会导致Word 2003鼠标在文文件中无效，并且不能打开两个文檔的问题。将$:Program FilesSybasePowerDesigner 12Add-ins目录下的Microsoft Word Import Export目录删除实验三 通过PL/SQL Developer创建表目的和意义熟练掌握通过PL/SQL Developer创立表的方法。实验内容向数据库Soft5增加教师和教师上课2个数据库表：表3-1 teacher表（教师信息表）字段名称类 型宽 度允许空值主 键说 明tnovarchar28NOT NULL是

27、教师编号tnamevarchar28NOT NULL教师姓名sexvarchar22NULL教师性别birthdayDate4NULL教师出生日期dnovarchar26NULL教师所在院系编号pnoNumber1NULL教师职务或职称编号homevarchar240NULL教师家庭住址zipcodechar6NULL邮政编码telvarchar240NULL联系电话emailvarchar240NULL电子邮件表3-2 teacher_course表（教师上课课表）字段名称类 型宽 度允许空值主 键说 明tcidNumber2NOT NULL是上课编号tnovarchar28NULL教师编号

28、spnovarchar28NULL专业代码classnovarchar24NULL班级号cnovarchar210NOT NULL课程编号semestervarchar26NULL学期schoolyearvarchar210NULL学年classtimeDateNULL上课时间classroomvarchar240NULL上课地点weektimeNumberNULL每周课时数（11） 基本的sql命令drop table teacher;CREATE TABLE teacher( tno varchar2 (8) PRIMARY KEY, -教师编号 tname varchar2(8) NOT

29、 NULL, -教师姓名 sex varchar2 (2), -教师性别 birthday date, -出生日期 dno varchar2 (6), -所在院系编号 pno number, -职务或职称编号 home varchar2(40), -家庭住址 zipcode char (6), -邮政编码 tel varchar2 (40), -联系电话 email varchar2 (40) -电子邮件);drop table teacher_course;CREATE TABLE teacher_course ( tcid integer PRIMARY KEY, -上课编号(主键) tn

30、o varchar2 (8), -教师编号 spno varchar2 (8), -专业代码 classno varchar2 (4), -班级号 cno varchar2 (10) NOT NULL, -课程编号 semester varchar2 (6), -学期 schoolyear varchar2 (10), -学年 classtime varchar2(40), -上课时间 classroom varchar2(40), -上课地点 weektime number, -每周课时数 constraint fk_tno foreign key(tno) references teach

31、er(tno), constraint fk_spno foreign key(spno) references speciality(spno), constraint fk_cno foreign key(cno) references course(cno) );查看垃圾箱中的表格SQL> select * from tab where like 'BIN%'或者SQL> show recyclebin清理垃圾表格1）清除某个特定垃圾表格SQL>purge table tbtest; #原始表名或者SQL>purge table tbtest; 垃

32、圾名称2）清空用户的垃圾箱SQL>purge recyclebin;2）清空某个表空间的垃圾箱SQL>purge tablespace ts01;恢复垃圾表SQL>flashback table tbtest to before drop rename to tbtest0;使用SQL语句修改表修改teacher中的email列为varchar2(50)ALTER TABLE teacher modify email varchar(50)；重命名表student_course为screname student_course to sc;使用SQL语句添加、修改和删除表数据（

33、以下程序为示例语句，可能运行失败）向sutdent表中插入一条记录insert into student (sno, sname, sex, native, birthday, spno, dno, classno, entime, home, tel)VALUES('02080019','李平','男','湖南', to_date('1985-1-2','yyyy-mm-dd'),'080605','610000', '0201', to_date(

34、'2002-9-1','yyyy-mm-dd'),'上海路11','8305670'); 把籍贯为“湖南”的学生的籍贯修改为”北京”UPDATE studentSET native='北京'WHERE native='湖南'产出所有籍贯为“北京”的学生DELETE studentWHERE native='北京'查询student表中的所有记录select * from student;使用约束实现数据的完整性设置检查约束增加一个约束，使student表中的sex列只允许输入男、女a

35、lter table student add constraint chk_sex check (sex in ('男','女');增加一个约束，使sc表中的score列只允许输入0到100之间的成绩alter table sc add constraint chk_score check (score>=0 AND score<=100);设置默认值约束alter table STUDENT modify SEX default '男'使用SQL语句创建触发器在删除学生信息的同时，利用触发器删除该学生的所有成绩create or r

36、eplace trigger tri_del_student after delete on student for each rowdeclare - local variables herebegin DELETE FROM SCWHERE sno = :old.sno;end tri_del_student;/通过向导创建表和数据库完整行约束Export你工作的成果：要求手动编写sql语句把实验一中出现的全部模拟数据插入到数据库中。实验四 备份和恢复数据库目的和意义理解数据库备份的过程，掌握使用PL/sql Developer备份Oracle数据库的方法。实验内容使用PL/sql Dev

37、eloper备份和恢复数据库Soft5。实现步骤Export user objectTest_bkp.sql檔中的内容- Export file for user TEST - Created by Dmh on 2007-10-10, 7:38:25 -spool test_bkp.logpromptprompt Creating table COURSETYPEprompt =promptcreate table COURSETYPE( CTNO NUMBER not null, CTNAME VARCHAR2(16);comment on table COURSETYPE is '

38、;课程类型'comment on column COURSETYPE.CTNO is '类型编号'comment on column COURSETYPE.CTNAME is '类型名称'alter table COURSETYPE add constraint PK_COURSETYPE primary key (CTNO);Export user tableD:test_bkp.pde 数据库备份文件。Import user tableselect * from sc;drop table sc;恢复数据库表 实验五 数据库查询与更新目的和意义掌握基

39、本的命令和语法，熟练运行子查询、连接查询、嵌套查询、组函数等技术查询出需要的结果。执行下面的SQL语句，分析结果，并理解和注释出每个语句的目的。SELECT * FROM student/ *SELECT sno,sname,sex,birthday FROM student/SELECT sno,sname,sex,birthdayFROM studentWHERE spno='080605'/SELECT sno,sname,birthday FROM student WHERE spno='080605' AND classno='2'/S

40、ELECT sno,sname,sex,birthdayFROM studentWHERE to_char(birthday,'yyyy/mm/dd')>'1985/01/01'SELECT sno,sname,trunc(sysdate-birthday)/365) AS age FROM student;SELECT sno,snameFROM studentWHERE sname LIKE '李%'SELECT sno,sname FROM studentWHERE sname LIKE '李_'SELECT sno,

41、sname,sex,birthday FROM student WHERE (spno=080605) AND (to_char(birthday,'yyyy/mm/dd')>'1985/01/01');SELECT *FROM studentWHERE native is null;SELECT sno,sname,sex,spno FROM student WHERE spno='080604' OR spno='080605' OR spno='080602'SELECT sno,sname,sex,s

42、pnoFROM studentWHERE spno IN ('080604','080605','080602');SELECT sno,sname,sex,birthdayFROM studentORDER BY birthday DESC,sname;SELECT sno,sname,sex,birthdayFROM studentORDER BY birthday ASC;SELECT sno,sname,sex,birthday FROM student ORDER BY 4 DESC;SELECT DISTINCT sexFROM st

43、udent;SELECT sno AS 学号,sname AS 姓名,sex AS 性别 FROM student ;SELECT sno 学号,sname 姓名,sex 性别 FROM student ;SELECT sno,round(avg(score) ,1)FROM sc group by snoorder by avg(score) desc ;select sno,avg_score from (SELECT sno,round(avg(score) ,1) avg_scoreFROM STUDENT_COURSEgroup by snoorder by avg(score) d

44、esc )where rownum<4;SELECT spno 专业, COUNT(*) 学生人数 FROM student GROUP BY spno;select AVG(score) 平均分,MAX(score) 最高分,MIN(score) AS 最低分 FROM STUDENT_COURSE ;连接查询SELECT sno,sname,sex,dname,spname FROM student st INNER JOIN speciality sp ON st.spno=sp.spnoINNER JOIN department dt ON sp.dno=dt.dno ;SELE

45、CT sno,sname,sex,dname,spname FROM student st , department dt ,speciality sp where sp.dno=dt.dno and st.spno=sp.spno;SELECT sc.sno,sname,spname,score FROM student st,speciality sp, STUDENT_COURSE scWHERE sc.sno=st.sno AND st.spno=sp.spno;SELECT sno,sname,dname FROM student st,department dp,specialit

46、y sp WHERE st.sno IN (SELECT DISTINCT sno FROM STUDENT_COURSE sc WHERE score<60)AND st.spno=sp.spno;SELECT sc.sno 学号,sname 姓名,COUNT(*) 没有及格的门数 FROM STUDENT_COURSE sc,student st WHERE score<60 AND st.sno=sc.sno GROUP BY sc.sno,sname;SELECT sc.sno 学号,sname 姓名,COUNT(*) 没有及格的门数 FROM STUDENT_COURSE

47、 sc sc,student st WHERE score<60 AND st.sno=sc.sno GROUP BY sc.sno,sname HAVING COUNT(*)>1;SELECT sp.spno, COUNT(*) 人数FROM student st,speciality spWHERE sp.spname='软件工程' AND st.spno=sp.spnoGROUP BY sp.spno;SELECT st.sno,sname,SUM(credit) 总学分 FROM student st, STUDENT_COURSE sc,course cs

48、 WHERE score>60 AND st.sno=sc.sno GROUP BY st.sno,sname ;嵌套查询SELECT sno,sname,birthday FROM student WHERE spno = (SELECT spno FROM student WHERE sname = '刘寅');SELECT sc.sno 学号,st.sname 姓名 ,AVG(score) 平均分 FROM STUDENT_COURSE sc, student st WHERE sc.sno IN (SELECT sno FROM student WHERE dno

49、='590000') AND sc.sno=st.sno GROUP BY sc.sno ,st.sname ORDER BY 平均分 DESC;SELECT sno,sname FROM student WHERE EXISTS (SELECT * FROM sc WHERE sc.sno=student.sno) ;SELECT sno,sname FROM student WHERE not EXISTS (SELECT * FROM sc WHERE sc.sno=student.sno) ;SELECT DISTINCT st.sno,sname FROM stude

50、nt st,sc WHERE st.sno=sc.sno AND sc.sno NOT IN (SELECT DISTINCT sno FROM sc sc WHERE score<80 );习题在student , course , sc中 添加元组1)从student , course , sc表中选择出当年年龄在22岁以上的学生的学号和姓名。2)统计平均年龄。3)“张三”同学要退学，请删除他的所有信息。实验六 创建视图、函数和过程目的和意义掌握使用创建视图的方法，包括视图的建立、删除；了解如何应用视图有选择地查看所需数据，并熟悉通过视图更改数据表中数据的方法。了解函数和存储过程的概念和作用，掌握创建函数和存储过程的方法。使用SQL语句创建的视图，并理解和注释出每个视图的目的。CREATE or replace view v_student (sno, sname, sex , age) AS SELECT sno,sname,sex,trunc(sysdate-birthday) /365) FROM student ;select * from v_student;CREATE or replace view v_course_student (sno,sname,speciality,score) AS SELECT sc.sno,sname,sp