数据库原理实验指导书(13级).doc

数据库原理实验指导书(13级) 结合数据库课程的内容，首先描述教学管理系统的数据库模式内容，它分为两部分，首先是数据项的描述，是数据库结构的说明。 另一部分是数据表构成说明，其中的数据项详细信息由前面的描述给出了。 1数据项描述定义教学管理系统的数据项如下表所示。 序号数据项名缩写名称类型长度，位数1学号Sno Char92姓名sname Char103出生年月sbirthday DateTime84性别ssex Char25专业班级sclass Char206评语sremark Char1007家庭住址address Char408邮编zipcode Char69电话phone Char1510电子邮箱email Char4011课程号o Char612课程名称ame Charxx先行课cpno Char614学时数ctime Numeric215学分credit Numeric216成绩score Numeric317教工号Tno Char718教师名称tname Char1019性别tsex Char220教师生日tbirthday DateTime821职称position Char1222系别department Char1623工作量tamount Numeric7，224简历experience Charxx5授课时间tdate DataTime826教室classroom Char102关系模式描述定义教学管理系统涉及到的关系模式总共有5个，分别为学生(Student)、课程(Course)、成绩(Score)、教师(Teacher)和授课(Teaching)，各关系的属性描述如下所示。 （1）Student(sno,sname,sbirthday,ssex,sclass,sremark,address,zipcode,phone,email)Primary key(sno) （2）Course(o,ame,cpno,ctime,credit)Primary key(o)Foreign key(cpno) （3）Score(sno,o,score)Primary key(sno,o)Foreign key(sno)Foreign key(o) （4）Teacher(tno,tname,tsex,tbirthday,position,department,tamount,experience)Primary key(tno)数据库系统原理及应用实验指导书合肥工业大学管理学院2 （5）Teaching(tno,o,tdate,classroom,sclass)Primary key(tno,o)Foreign key(tno)Foreign key(o)二上机实验目标和说明1.实验目标通过“数据库系统概论”书本知识学习和“数据库原理与应用”课程教学所授知识对“教学管理系统”的实验内容进行分析，通过建立数据库、数据表、数据视图以及触发器，利用SQL结构化查询语言提供的数据操纵功能，设计实现对数据的定义、修改、删除、查询、更新以及控制等操作，并按照实验指导书的各项要求完成实验内容。 2.实验说明本课程的前两次实验是内容相关的，为保证实验的顺利开展，特作如下说明1.在实验计算机的硬盘D盘符下，请同学们建立以自己名字命名的子目录。 再在此子目录下，存放上机实验建立的相关文件。 本次实验的的S DBMS环境可以是MySQL环境或SQL Server5xx环境。 注意（1).考虑到前两次上机的内容相关，第一次的内容下机时要带走。 如果是SQL Server5xx环境的，建立数据库是会形成22个文件，下机的时候两个文件都要复制带走。 如果是是MyL SQL的环境，下机时也要通过备份或转储来实现数据的带走。 （2).在实验 一、实验二中，只需要将验证的SQL语句保存起来，可以建立一个文本文件，然后用编号把每题的SQL语句分开。 实验报告不需要提供执行截图。 （3).实验三验证触发器部分，需要把执行结果进行屏幕截图保存。 2.本课程实验要利用数据库管理系统进行实验，每台计算机上都已经安装好了微软公司的SQL Serverxx数据库管理系统和MySQL Server数据库管理系统，各位同学可自己选择实验的DBMS。 根据提供的客户端编程界面按照实验要求来完成实验。 （SQL Serverxx的安装过程见附录，建议同学们可在自己的电脑上进行安装，熟悉安装过程及安全性设置。 由于SQL Server不是往上兼容的，因此，8SQL Serverxx中的数据文件无法附加到SQL5Serverxx中。 MyL SQL的安装过程这里就没有列，大家可以自己去熟悉安装。 ）三实验内容上机实验总共分为3次，这里列了四个实验内容，其中前三个实验内容为必做内容，实验四为选做内容。 1.实验一设计数据库、数据表并编程实现11.目的要求 （1）这是一个验证性实验。 （2）利用所学的E-R数据模型概念，描述“教学管理系统”中各数据表之间的联系。 能够给出系统的E-R图，注意E E-R R图中的实体、联系与关系模型中对应的二维表的关系。 能够指出每个数据表的关键字、外码以及表中需要满足的完整性约束条件； （3）SQL Server5xx环境熟悉微软公司SQL Server的企业管理器（后台实验平台）和查询分析器（前台实验平台），熟悉SQL Server中内嵌的SQL编程环境，正确登录、退出和使用实验平台环境；数据库系统原理及应用实验指导书合肥工业大学管理学院3熟练掌握SQL环境下的程序和运行命令；My SQL Server环境熟悉SQLyog/Navicat的企业管理器（后台实验平台）和查询分析器（前台实验平台），熟悉两种软件内嵌的SQL编程环境，熟练掌握SQL环境下的程序和运行命令； （4）首先建立一个数据库（学生管理），然后再建立五张数据表。 练习并掌握定义、删除和修改数据表中数据结构的操作。 建立数据库和数据表，并完成对各个数据表输入一定的模拟数据，以满足查询要求。 （模拟数据记录尽量多输一些且数据要能满足查询要求）22.实验建议 （1）数据库服务器的安装及启动。 （安装过程在课外自己完成） （2）建立数据库对象，数据库名称统一命名为“学生管理”。 学会利用两种方式在实验环境中建立数据库对象。 下面对两种方式加以说明a.使用提供的集成图形界面。 （如SQL Server的企业管理器，下图所示）。 b.通过编程实现，使用SQL语言。 （这是SQL Server2000操作界面，SQL Serverxx操作界面见附录最后图片） （3）建立数据表及索引对象。 这里以学生表为例来说明，其他四张表请自己来建立。 建立方法也可以使用上面所说的两种方式。 create tablestudent(sno Char (9),sname Char (10)constraint c1not null,sbirthday Datetime,ssex Char (2)constraint c2check(ssex in(男,女),sclass Char (20),sremark Char (100),address Char (40),数据库系统原理及应用实验指导书合肥工业大学管理学院4zipcode Char (6),phone Char (15),email Char (40),constraint pk_student primarykey(sno); （44）在系统中，如果要将日期格式转变成日常习惯的方式“年/月/日”，请在s Windows的控制面版中，找到“日期、时间显示格式”选项，再将“-”修改为“.”即可。 33.实验内容 （1）根据实验建议所示，建立“学生管理”数据库，这里是以SQL Serverxx5为例来进行建立。 （2）建立数据表的方法，建立五个相关的数据表。 完成数据库的建立并定义各表的完整性约束条件。 分别使用企业管理器和SQL语言来实现。 （3）熟练掌握并应用SQL中的数据表定义、删除和修改等操作命令； （4）熟练掌握为各表常用字段建立索引，以提高数据检索效率。 * （5）了解域关系演算语言(QBE)的操作形式。 无论是SQL Serverxx还是Aess均没有提供基于QBE的更新操作。 只提供了检索操作。 我们也以检索为例来说明。 （*号为选做内容）a.SQL Serverxx中的QBE实现。 在建立视图时使用，它的属性是纵向排列的。 b.Aess中使用QBE的实现。 也是在建立查询(视图)时使用，它的属性是同书上一样行向排列的。 2.实验二设计数据插入、修改、删除、查询和视图等操作并编程实现11.目的要求 （1）熟练掌握数据更新语句，灵活地操作插入数据、修改数据和删除数据；数据库系统原理及应用实验指导书合肥工业大学管理学院5 （2）熟练掌握关系数据库中的完整性概念的应用； （3）掌握单表查询的基本方法； （4）掌握多表连接查询的基本方法； （5）熟练掌握以下练习，并进行以下各类查询选择表中的若干列、查询全部列、查询经过计算的值；选择表中的若干元组，即消除取值重复的行与查询满足指定条件的元组（包括比较大小、确定范围、确定集合、字符匹配、涉及空值和多种条件查询）；对查询结果排序；使用集函数；对查询结果分组 （6）熟练掌握以下练习，并进行下列各类连接查询等值与非等值连接查询；自身连接；外连接；复合条件连接； （7）掌握嵌套查询的基本方法； （8）掌握集合查询的基本方法； （9）熟练掌握查询视图操作 （10）熟练掌握更新视图操作 （11）熟练关系的完整性概念，领会视图的用途 （12）练习以下各类查询带有IN谓词的子查询；带有比较运算符的子查询；带有ANY或ALL谓词的子查询；带有EXISTS谓词的子查询；视图查询与更新操作；22.实验建议 （1）为了便于程序正确性测试，需要先对自己建立起来的数据表录入一些模拟数据，然后利用SQL SQL语句来进行设计验证。 （2）设计的SQL程序应该涵盖数据更新操作的全部内容，包括插入、修改、删除； （3）按照SQL语言编程要求，实现各类查询和检索操作； （4）掌握和理解关系模型的完整性规则；特别是Course表的数据录入。 （5）利用SQL Server的平台环境，验证数据库的控制保护功能。 （6）将查询视图命令等价改写为对数据表的查询操作命令，利用SQL编程设计完成并进行效率分析。 33.实验内容 （1）根据以下给定的部分数据表信息，分别对student,course,score,teacher,teching表进行数据插入。 建议给student、score等表添加30条以上的记录，以方便后面的查询。 数据插入可以使用SQL语句和界面输入两种方式，实验报告中只需提供L SQL语句，每张表提供33条语句。 Student表Sno snameSbirthday Sclass011110101章海潮1982.02.07信管系0101011111103王天力1982.03.04电商系0101011111204董丞悟1982.06.06电商系010xx112110马丽鹃1982.04.03工商系0101011113104杨乾坤1982.03.23会计系0101011113221刘抗齐1983.01.16会计系010xx114111丘海棠1983.02.08旅游系0101011115208齐振国1982.07.22旅游系0102数据库系统原理及应用实验指导书合肥工业大学管理学院6011115209李晓1985.08.12旅游系010xx011101王晓悦1982.01.12材料系0001001011212程伊莲1982.03.21材料系0002course表Cno CnameCpno CreditCtime C001数据库原理C005464C002高等数学472C003管理信息系统C001454C004操作系统C006332C005数据结构C007432C006计算机文化基础232C007C语言程序设计C006332C008管理学_原理与方法232score表Sno CnoScore011110101C00190011111103C00185011111204C00173011112110C00198011113104C00196011113221C00197011114111C00183011115208C00185011113221C006NULL011110101C0079xx111103C00797teacher表Tno TnameTsex Departmenttbirthday T001江承基男信息管理系1964.03.21T002梁其征男信息管理系1973.12.12T003徐茉莉女信息管理系1980.02.16T004吴雅云女信息管理系1970.03.18T005杨运铎男电子商务系1973.10.01T006张昌盛男电子商务系1976.07.03T007程力衡男电子商务系1958.10.03Teaching表Tno otdate classroomsclass T001C005xx-01-09西二405信管系0101T001C002xx-04-10主401会计系0102T002C007xx-02-01西二406电商系0101T003C001xx-09-10西二504电商系0102T004C006xx-07-15主401工商系0101T005C004xx-11-12主402会计系0101T006C002xx-05-21西二506会计系0102 （2）查询全体学生的详细记录； （3）查询全体学生的学号与姓名； （4）查询全体学生的学号、姓名、所属班级，相同班级列在一起； （5）查询全体学生的姓名、出生年份；（函数year()） （6）查询全体学生的姓名及其年龄；（Getdate()或Curdate()） （7）查询“信管系0101”班全体学生名单； （8）查询查询所有年龄在27岁以下的学生姓名及其年龄； （9）查询考试成绩有不及格的学生的学号；数据库系统原理及应用实验指导书合肥工业大学管理学院7 （10）某些学生选修某门课程后没有参加考试，所以有选修课记录，但没有考试成绩，试查找缺少考试成绩的学生和相应的课程号； （11）查询年龄在15至28岁之间的学生姓名、班级和年龄； （12）查询年龄不在15至28岁之间的学生姓名、班级和年龄； （13）查询“信管系0101”和“电商系0102”班的学生的姓名、出生日期和班级信息； （14）查询既不是“信管系0101”也不是“电商系0102”班的学生的姓名、出生日期和班级信息； （15）查询学号为“011113104”的学生的详细情况； （16）查询学号以“0111”打头的学生信息； （17）查询所有姓“张”学生的学号、姓名、性别、年龄； （18）查询名字中第二个字有“海”字的学生的学号、姓名、性别、年龄； （19）查询所有不姓“刘”学生的姓名； （20）查询名字中有“齐”字的学生的姓名和班级； （21）查询所有修过课的学生的学号； （22）查询课程号以“C”开头的最后两个字母为“05”的课程号和课程名； （23）查询课程名中包含有“_”的课程号和课程名； （24）查找全部有成绩记录的学生学号、课程号； （25）查询所修课程都及格的同学的学号、最低分、最高分和平均成绩； （26）求每门课的平均成绩，并把结果存入average表； （27）将学生“马丽鹃”的出生日期改为“1982.8.20”； （28）将所有学生的zipcode属性列的值填改为“230009”； （29）将average表中的所有课程的平均成绩置零； （30）删除average表中的课程号为c007的平均成绩记录； （31）删除所有average表中平均成绩记录； （32）建立一个临时学生信息表（tstudent），删除该表中的学号前六位为001011的学生记录。 （33）查找“电商系0101”班年龄在27岁以下的学生学号、姓名； （34）查最低分大于60且最高分又小于90的学生的学号 （35）查找选修了“C001”号课程的学生学号及其成绩，查询结果按分数降序排序； （36）查询全体学生情况，查询结果按所在班级升序排列，对同一班级中的学生按年龄降序排列； （37）查询学生总人数； （38）查询选修了课程的学生人数； （39）查询学习“C001”课程的学生最高分数； （40）在所有课程中查询最高分的学生学号和成绩； （41）计算各个课程号与相应的选课人数； （42）查询“电商系0102”班选修了两门课程以上的学生学号、姓名； （43）使用自身连接查询每一门课程的间接先行课（即先行课的先行课） （44）使用复合条件连接查询选修“c001”号课程且成绩在90分以上的所有同学； （45）使用复合条件连接查询每个学生选修的课程名及其成绩； （46）查询选修了全部课程的学生； （47）查询至少选修全部学分数为4个学分的课程的学生的学号、姓名； （48）查选修C005号课程且成绩至少高于选修C003课程的同学的Sno,按sno从高到低排序； （49）查询选修了课程C001或c007的学生学号、姓名； （50）查询既选修了课程C001又选修了课程c007的所有学生学号、姓名； （51）查询“会计系0102”班的学生及年龄不大于27岁（现有年龄）的学生； （52）查询选修了课程名为“数据库原理”的学生的学号、姓名、性别、年龄； （53）查询其他班中比“信管系0101”班所有学生年龄都小的学生名单； （54）查询与“齐振国”在同一个班学习的学生学号、姓名、性别、年龄； （55）建立“信管系0101”班学生的视图，定义视图名为“info_student1”； （56）建立“信管系0101”班学生的视图，定义视图名为“info_student2”，并要求进行修改与插入时，仍须保证该视图只有“信管系0101”班学生； （57）建立“信管系0101”班选修了“C001”课程的学生的视图，定义视图名为“info_C001_student1”； （58）建立“信管系0101”班选修了“C001”课程且成绩在90分以上的学生的视图，定义视图名为“info_c001_student2”；数据库系统原理及应用实验指导书合肥工业大学管理学院8 （59）定义一个反映学生年龄的视图，定义视图名为“vbirthday_student”； （60）将学生表中所有女生记录定义为一个视图，视图名为“vfemale_student”； （61）将学生的学号及其平均成绩定义为一个视图，视图名为“vaverage_student”； （62）删除视图“info_student1”，删除后即重建； （63）在“信管系0101”班学生视图中找出年龄小于27岁（现在的年龄）的学生； （64）利用视图查询“信管系0101”班选修了“C001”课程的学生； （65）通过“信管系0101”班info_student2视图中学号“011111103”的学生姓名改为“潘长江”； （66）向“信管系0101”班info_student1视图中插入一个新学生记录，其中学号011111136，姓名张艺谋，性别男，出生日期1987.11.9； （67）通过视图info_student1删除信管系0101班学号为“011111135”、姓名为“黄健中”的学生记录；3.实验三数据库存储过程、触发器的建立及编程操作的实现11.目的要求 （1）理解掌握数据库中存储过程、触发器的原理、使用场合及方法； （2）掌握触发器在关系数据库的完整性实现中的作用； （3）掌握数据库中存储过程的建立方法和调用过程； （4）掌握数据库中触发器的建立方法；* （5）理解事务的作用及定义；* （6）熟练游标的作用、定义及作用22.实验建议 （1）为了便于程序正确性测试，需要结合教材中的知识点，对数据库管理系统中的存储过程、触发器、事务、游标、并发处理等内容进行强化 （2）对存储过程和触发器的建立可以参照数据库的建立方法，采用两种方式实现； （3）建立过程中，注意命令和函数的书写方法，不要简单复制代码。 （4）得用触发器来实现数据表的相关操作功能；33.实验内容 （1）创建两个临时表Table1,Table2。 两表的结构均为字段名类型F1Char (40)F2Int （2）创建触发器a)在在Table1上建立Insert触发器T1，触发器内容为begin declareF1char (40),F2int,T_F1Char (40)DeclareSlen int SelectF1=F1,F2=F2,T_F1=F1From insertedIfF2=1begin SelectF2=0while Len(RTrim(F1)0begin SelectF2=F2+Convert(int,left(RTrim(F1),1)SelectF1=Right(RTrim(F1),Len(Rtrim(F1)-1)end endElse begin数据库系统原理及应用实验指导书合肥工业大学管理学院9SelectF2=Ceiling(Len(RTrim(F1)/2.0)end Insertinto Table2(F1,F2)values(T_F1,F2)end b)在在Table2上建立删除触发器T2,触发器内容为begin declareF1char (40),F2int,T_F2intSelectF1=a.F1,F2=a.F2,T_F2=b.F2From table1a,deleted bwhere a.F1=b.F1IfF2=1begin UpdateTable1set F1=LTrim(RTrim(F1)+LTrim(Str(T_F2)+Right(RTrim(Str(T_F2),1)where F1=F1end IfF2=2begin UpdateTable1Set F1=Ltrim(Rtrim(F1)+Ltrim(Str(T_F2)+Ltrim(Str(Unicode(SubString(Ltrim(F1),T_F2,1)where F1=F1end IfF2=3begin UpdateTable1Set F1=LTrim(RTrim(F1)+LTrim(Str(T_F2)+RTrim(LTrim(Char(64+T_F2)where F1=F1end IfF2=4begin UpdateTable1Set F1=LTrim(RTrim(F1)+LTrim(Str(T_F2)+RTrim(LTrim(Str(Connections)where F1=F1end end （3）创建存储过程Exam_Proc存储过程的两个入口参数分别为学号(10位),姓名执行语句格式为Exec Exam_Proc1234567890,张三CREATE PROCEDUREExam_ProcStudent_ID char (10),Student_Name char (10)AS beginIf Len(LTrim(Rtrim(Student_ID)10begin Print学号长度不对end elsebegin Delete From Table2数据库系统原理及应用实验指导书合肥工业大学管理学院10DeleteFrom Table1Insert IntoTable1(F1,F2)Values(Right(Student_ID,6),1)Insert IntoTable1(F1,F2)Values(LTrim(RTrim(Student_Name),2)Insert IntoTable1(F1,F2)Values(LTrim(RTrim(ServerName),3)Insert IntoTable1(F1,F2)Values(LTrim(RTrim(ServiceName),4)declareExam_Str Char (80),C_F1Char (20)Declare Table1_Cursor Cursorfor SelectF1From Table2Open Table1_Cursor Fetch Next from Table1_Cursor intoC_F1WhileFetch_Status=0begin DeleteFrom Table2where F1=C_F1Fetch Next from Table1_Cursor intoC_F1end CloseTable1_Cursor DEALLOCATETable1_Cursor SelectExam_Str=Declare Table1_Cursor Cursorfor SelectF1From Table1Open Table1_Cursor FetchNextfromTable1_Cursor intoC_F1WhileFetch_Status=0begin SelectExam_Str=RTrim(LTrim(Exam_Str)+RTrim(LTrim(C_F1)FetchNextfromTable1_Cursor intoC_F1end CloseTable1_Cursor DEALLOCATETable1_Cursor Selectaa=Exam_Str DeleteFromTable1end end; （4）执行所创建的存储过程。 (将语句中的学号和姓名替换为自己的学号和姓名)如Exec Exam_Proc1234567890,张三的结果为34567890422张三124352HIS_SERVER5EMSSQLSERVER6159 （55）利用触发器实现对仓库货物的管理。 有四张表货物信息表（*货物号(C4)，货物名(C20)，规格(C20)，型号C (20)，说明(C20)）库存表(*货物号(C4),更新日期(D),库存量(N(9,2)入库表(*入库时间(D),*货物号(C4),入库数量(N(9,2),经办人(C10)出库表(*出库时间(D),*货物号(C4),出库数据(N(9,2),经办人(C10)要求用触发器完成以下两项任务。 （1).当在入库表中增加记录时，会自动增加此货物的库存量。 (2).当在出库表中增加记录时，会自动减少此货物的库存量。 *4.实验四分析、设计、实现教学管理系统数据库应用系统(由于教学课时调整，本实验改为选做实验，有兴趣时间许可的同学可选做。 )11.目的要求数据库系统原理及应用实验指导书合肥工业大学管理学院11 （1）综合性实验； （2）掌握数据库系统设计方法； （3）掌握面向对象的程序设计方法； （4）综合运用SQL和编程环境实现具体教学管理数据库应用系统。 22.实验建议按照教学管理系统功能划分的要求，同学们可以尝试自己建立应用程序。 运用自己所学的编程语言，完成程序的结构设计及程序实现。 33.实验内容 （1）熟悉前后台数据库管理系统应用环境；本次实验使用微软最新的协同办公软件WSS(Windows SharePointServices)3.0。 此软件采用的数据库管理系统是SQL Serverxx。 所建立的数据列表均存在数据库管理系统中。 （2）客户端软件统一使用IE浏览器。 WSS软件采用B/S架构，客户端无需安装专用软件，统一使用IE浏览器来访问应用服务器即可。 （3）实现过程说明；首先以系统管理员帐户登录到系统中，通过操作网站操作-网站设置-网站和工作区，进入如下图的操作界面。 点击创建按钮，创建自己的网站。 进入如下图所示的创建界面，在标题和URL名称处输入自己的学号，在模板处选择空白网站，然后进行网站创建。 网站创建完成后，进入自己的网站界面。 再通过网站操作-创建，进入如下的操作界面。 选择创建自定义列表。 数据库系统原理及应用实验指导书合肥工业大学管理学院12在如下的界面中分别创建实验一所列的五个数据表。 进入所建列中进行列表设置。 选择创建栏。 数据库系统原理及应用实验指导书合肥工业大学管理学院13分别进行字段的设置。 （4）重复上面步骤，对教学管理系统进行实现。 四实验注意事项1.实验准备1按照数据库原理与应用教学大纲的要求，运用所学的数据库原理中“规范化”理论知识，对“教学管理系统”中所用到的数据表进行规范化处理，并给出规范化后的最终范式，进而形成能够建立数据表的定义描述。 2实验一和实验二内容完成以后，同学们按照数据库系统原理与应用教学大纲的要求，运用所学的“数据库系统设计”理论知识，系统地掌握数据库系统开发过程中所经历的阶段以及实现步骤的具体内容，仔细理解每个阶段需完成工作任务的实质是什么，做到学以致用温故知新。 3实验指导书中规定的实验内容，将在课程教学结束后在卷面考试中，以试题的面目出现，成绩占到2200%-35%。 数据库原理与应用的实验报告和平时的作业将作为本课程的平时考核成绩，是课程总成绩的重要组成部分。 实验进度总体是每位同学三次。 原则上每次完成一个以上的实验，但是同学们可以根据自己的学习进度情况作适当地调整。 实验内容非常多，需要在每次实验前认真准备实验提纲。 4实验大纲中规定要完成的内容，也是数据库原理与应用课程相