数据库系统原理及应用实验报告.doc

数据库系统原理及应用实验指导书 数据库系统原理及应用实验指导书学 号： 姓 名： 专业班级： 指导教师： 15一“教学管理系统” 实验数据库模式描述2关系模式描述定义Student (sno,sname,sbirthday,ssex,sclass,sremark,address,zipcode,phone,email) Primary key (sno)Course (cno, cname, cpno, ctime, credit) Primary key (cno) Foreign key (cpno)Score (sno, cno, score) Primary key (sno, cno) Foreign key (sno) Foreign key (cno)Teacher (tno, tname, tsex, tbirthday, position, department, tamount, experience) Primary key (tno)Teaching (tno, cno, tdate, classroom, sclass) Primary key (tno, cno) Foreign key (tno) Foreign key (cno) 二上机实验（一）实验目标通过“数据库系统概论”书本知识学习和“数据库原理与应用”课程教学所授知识对“教学管理系统”实验内容进行分析，建立相应的数据库、数据表、数据视图以及相应的界面程序，利用SQL结构化查询语言提供的数据操纵功能，编程设计实现对数据的定义、修改、删除、查询、更新以及控制等操作，并按照实验指导书的各项要求完成实验。 （三）实验内容实验一：设计数据库、数据表和数据视图并编程实现1. 目的要求（1）这是一个验证性实验。（2）利用所学E-R数据模型图概念，描述“学生管理” 系统中数据表之间以及各数据项之间的联系。并按照课程教学大纲要求，转化相关E-R图为关系模型中对应的二维表，指出每个数据表中的关键字、索引项、外码以及表中需要满足完整性约束条件；（3）对照关系模型中(1:1)、(1:n)、(m:n)实体联系，指出各数据表实体属于哪一类联系。（4）熟悉微软公司SQL Server的企业管理器（后台实验平台）和查询分析器（前台实验平台）内嵌的SQL编程环境，正确登录、退出和使用实验平台环境；（5）熟练掌握SQL环境下的程序编辑和运行命令；（6）建立一个教学管理数据库，即建立五个相关数据表。练习并掌握定义、删除和修改表的数据结构操作。建立数据库和数据表、数据视图并完成对各个数据表输入一定查询要求的模拟数据（模拟数据记录尽量多输一些且数据要能满足查询要求）的内容。2. 实验内容（1）根据实验建议所示，建立“学生管理”数据库，这里是以SQL Server 2000为例来（2）建立数据表的方法，建立五个相关的数据表。完成数据库的建立并定义各表的完整性约束条件。分别使用 企业管理器 和SQL 语言 来实现。（3）熟练掌握并应用SQL中的数据表定义、删除和修改等操作命令；（4）熟练掌握为各表常用字段建立索引，以提高数据检索效率。建表的代码如下：分别为student,course,score,teacher,teaching表use student_managment;create table student (sno char(9),sname char(10),sbirthday Datetime,ssex char(2) constraint c2 check(ssex in(男,女),sclass char(20),sremark char(100),address char(40),zipcode char (6),phone char(15),email char(40),constraint pk_student primary key (sno);create table course if not exists(cno char(6),cname char(20),cpno char(6),ctime numeric(2),credit numeric(2),score numeric(3),constraint pk_course primary key (cno),);create table score (sno char(9),cno char(6),score numeric(3),primary key (sno,cno),foreign key (sno) references student(sno),foreign key (cno) references course(cno);create table teacher (tno char(7),tname char(10),tsex char(2),tbirthday Datetime,position char(12),department char(16),tamount numeric(7,2),experience char(200),primary key (tno),);create table teaching (tno char(7) ,cno char(6) ,tdate datetime,classroom char(10),sclass char(20),primary key (tno,cno),foreign key (tno) references teacher (tno),foreign key (cno) references course (cno);实验二：设计数据插入、修改、删除、更新、查询和视图查询等操作并编程实现1. 目的要求（1）熟练掌握数据更新语句，灵活地操作插入数据、修改数据和删除数据；（2）熟练掌握关系数据库中的完整性概念的应用；（3）了解数据录入的方法和过程。（4）掌握单表查询的基本方法；（5）掌握连接查询的基本方法；（6）熟练掌握以下练习，并进行以下各类查询： 选择表中的若干列、查询全部列、查询经过计算的值； 选择表中的若干元组，即消除取值重复的行与查询满足指定条件的元组（包括：比较大小、确定范围、确定集合、字符匹配、涉及空值和多种条件查询）； 对查询结果排序； 使用集函数； 对查询结果分组（7）熟练掌握以下练习，并进行下列各类连接查询： 等值与非等值连接查询； 自身连接； 外连接； 复合条件连接；（8）掌握嵌套查询的基本方法；（9）掌握集合查询的基本方法；（10）熟练掌握查询视图操作（11）熟练掌握更新视图操作（12）熟练关系的完整性概念，领会视图的用途（13）练习以下各类查询： 带有IN谓词的子查询； 带有比较运算符的子查询； 带有ANY或ALL谓词的子查询； 带有EXISTS谓词的子查询； 视图查询与更新操作；2. 实验内容（1）根据以下给定的数据表信息分别对student, course, score, teacher, teching 表进行数据插入和修改，以完成给定的数据录入，同时要求输入班级同学名录模拟客观真实情况，给student、score表添加30条以上记录，以便以后查询应用方便。Student表：SnosnameSbirthdaySclass011110101章海潮1982.02.07信管系0101011111103王天力1982.03.04电商系0101011111204董丞悟1982.06.06电商系0102011112110马丽鹃1982.04.03工商系0101011113104杨乾坤1982.03.23会计系0101011113221刘抗日1983.01.16会计系0102011114111丘海棠1983.02.08旅游系0101011115208齐振国1982.07.22旅游系0102001011101王晓悦1982.01.12材料系0001001011212程伊莲1982.03.21材料系0002代码如下：插入数据(30条)：use student_managment;insert into student (sno,sname,sbirthday,sclass) values(011110101,章海潮,19820207,信管系),(011111103,王天力,19820304,电商系),(011111204,董丞悟,19820606,电商系),(011112110,马丽鹃,19820403,工商系),(011113104,杨乾坤,19820323,会计系),(011113221,刘抗日,19830116,会计系),(011114111,丘海棠,19830208,旅游系),(011115208,齐振国,19820722,旅游系),(001011101,王晓悦,19820112,材料系),(011111212,程伊莲,19820321,材料系),(011115210,娜迦,19820622,电商系0102),(011115211,大娜迦,19830512,电商系0101),(011115212,美杜莎,19830203,天灾系0102),(011115213,影魔sf,19820906,天灾系0102),(011115214,沙王,19850312,电商系0102),(011115215,剑圣,19830112,天灾系0102),(011115216,食人魔,19820521,天灾系0102),(011115217,恶魔巫师,19820813,近卫系0102),(011115218,潮汐猎人,19861012,近卫系0102),(011115219,赏金猎人,19861121,近卫系0102),(011115220,圣堂刺客,19840325,近卫系0102),(011115221,隐形刺客, 19820623,近卫系0102),(011115222,幻影刺客,19820721,物流系0102),(011115223,沉默术士,19870213,物流系0102),(011115224,遗忘法师,19820326,物流系0102),(011115225,狼人,19820506,敏捷系0102),(011115226,山岭巨人,19821013,力量系0102),(011115228,大魔导师,19850623,智力系0102),(011115229,秀逗导师,19820703,敏捷系0102),(011115227,暗影恶魔,19850722,敏捷系0102);course表：CnoCnameCpnoCreditCtimeC001数据库原理C005464C002高等数学472C003管理信息系统C001454C004操作系统C006332C005数据结构C007432C006计算机文化基础232C007C语言程序设计C006332代码如下：use student_managment;insert into course (cno,cname,credit,ctime) values(C002,高等数学,4,72),(C006,计算机文化基础,2,32);insert into course (cno,cname,cpno,credit,ctime) values(C003,管理信息系统,C001,4,54);insert into course (cno,cname,cpno,credit,ctime) values(C004,操作系统,C006,3,32),(C001,数据库原理,C005,4,64),(C007,C语言程序设计,C006,3,32),(C005,数据结构,C0074,32);score表：SnoCnoScore011110101C00190011111103C00185011111204C00173011112110C00198011113104C00196011113221C00197011114111C00183011115208C00185011110101C00792011111103C00797代码如下：use student_managment;insert into score (sno,cno,score) values(011111103,C003,85),(011111103,C002,97),(011111103,C004,85),(011111103,C005,97),(011111103,C006,85),(011111103,C008,97),(011111103,C009,85),(011111103,C010,97),(001011212,C001,65),(001011212,C008,92),(001011212,C003,80),(001011212,C002,85),(001011212,C004,89),(001011212,C005,77),(001011212,C006,66),(001011212,C009,73);teacher表：TnoTnameTsexDepartmentT001江承基男信息管理系T002梁其征男信息管理系T003徐茉莉女信息管理系T004吴雅云女信息管理系T005杨运铎男电子商务系T006张昌盛男电子商务系T007程力衡男电子商务系代码如下：use student_managment;insert into teacher (tno,tname,tsex,department) values(T001,江承基,男,信息管理系),(T002,梁其征,男,信息管理系),(T003,徐茉莉,女,信息管理系),(T004,吴雅云,女,信息管理系),(T005,杨运铎,男,电子商务系),(T006,张昌盛,男,电子商务系),(T007,程力衡,男,电子商务系);Teaching表TnocnotdateclassroomsclassT001C0051988-01-09西二405信管系0101T002C0071990-02-01西二406电商系0101T003C0011985-09-10西二504电商系0102T004C0061984-07-15主401工商系0101T005C0041989-11-12主402会计系0101T006C0021991-05-21西二506会计系0102代码如下：insert into teaching (tno,cno,tdate,classroom,sclass) values(T001,C005,19880109,西二405,信管系0101),(T002,C007,19900201,西二406,电商系0101),(T003,C001,19850910,西二504,电商系0102),(T004,C006,19840715,主401,工商系0101),(T005,C004,19891112,主402,会计系0101),(T006,C002,19910521,西二506,会计系0102);（2）将两个同学（数据自己临时设置，用后即删除）的两门课程的成绩以运行SQL语句插入的形式加入score表中。该题用以验证、理解和掌握关系模型的完整性规则；insert into score (sno,cno,score) values(011111993,C003,85),(011111993,C002,97);（3）求每门课的平均成绩，并把结果存入average表；insert into average(cno,av_score) select cno,AVG(score) from scoregroup by cno;（4）将学生“马丽鹃”的出生日期改为“1982.8.20”；USE appleUPDATE StudentSET sbirthday =1982-08-20WHERE sno =011112110;（5）将所有学生的zipcode属性列值填补上；INSERT INTO Student（zipcode） values（230009);（6）将average表中的所有课程的平均成绩置零； UPDATE average SET Avg_score=0;（7）删除average表中的课程号为c007的平均成绩记录； DELETE FROM average WHERE cno=c007;（8）删除所有average表中平均成绩记录； DELETE FROM average;（9）建立一个临时学生信息表（tstudent），删除该表中的学号前六位为001011的所有学生记录。 CREATE TABLE tstudent( sno Char(9),snameChar(10) constraint c1 not null,sbirthdayDatetime,ssexChar(2) constraint c2 check(ssex in (男,女),sclassChar(20),sremarkChar(100),addressChar(40),zipcodeChar(6),phoneChar(15),emailChar(40), constraint pk_student primary key(sno) );INSERT INTO tstudentSELECT * FROM Student;DELETE FROM tstudent（10）查询全体学生的学号与姓名；USE appleSELECT sno,snameFROM student1（11）查询全体学生的学号、姓名、所属班级；USE appleSELECT sno,sname,sclassFROM student1（12）查询全体学生的详细记录；USE appleSELECT *FROM student1（13）查询全体学生的姓名及其年龄；（14）查询全体学生的姓名、出生年份；USE appleSELETE sname,sbirthdayFROM student1（15）查询所有修过课的学生的学号；USE appleSELECT sno FROM score（16）查询“信管系0101”班全体学生名单；USE appleSELETE snameFROM student1WHERE sclass= 信管系0101（17）查询查询所有年龄在27岁以下的学生姓名及其年龄；select sname,2011-YEAR(sbirthday) from student where YEAR(sbirthday) 1984;-年龄小于27岁就是出生年份大于2011减去27即1984年就行了-（18）查询考试成绩有不及格的学生的学号；select sno from score where score 1984;-注释年龄小于岁就是出生年份大于减去即年就行了-（32）查找选修了“C001”号课程的学生学号及其成绩，查询结果按分数降序排序；select sno,scorefrom score where cno = C001 order by score desc;（33）查询全体学生情况，查询结果按所在班级升序排列，对同一班级中的学生按年龄降序排列；select *from studentorder by sclass,sbirthday desc;（34）查询学生总人数；select COUNT(*)from student;（35）查询选修了课程的学生人数；select COUNT(distinct sno) from score;（36）在所有课程中查询最高分的学生学号和成绩；select sno,max(score)from score group by sno;（37）查询学习“C001”课程的学生最高分数；select max(score)from score where cno = C001;（38）计算各个课程号与相应的选课人数；select cno,count(sno)from score group by cno（39）查询“电商系0102”班选修了两门课程以上的学生学号、姓名；select sname,sno from studentwhere sno in(select score.sno from student,scorewhere sclass = 电商系group by score.snohaving COUNT(*)2);（40）自然连接student和score表；Select student.sno, sname, sbirthday, sclass,ssex, sremark, phone, zipcode, email,cno,score from student,score where student.sno = score.sno;（41）使用自身连接查询每一门课程的间接先行课（即先行课的先行课）select o,second.cpno from course first,course secondwhere first.cpno = o;（42）使用复合条件连接查询选修“c001”号课程且成绩在90分以上的所有同学；select student.sno,sname from student,scorewhere cno = C001 and score 90（43）使用复合条件连接查询每个学生选修的课程名及其成绩；select cname,score.scorefrom course,score where o = o（44）查询选修了全部课程的学生；select sname,sno from student where not exists(select * from course where not exists(select * from score where sno = student.sno and cno =o);（45）查询至少选修全部学分数为4个学分的课程的学生的学号、姓名；select sno,sname from student where sno in (select sno from score,coursewhere o = ogroup by score.snohaving SUM(credit) = 4);（46）查询所有选修了C001号课程的学生学号、姓名；select sname,student.sno from student,scorewhere cno = C001 and student.sno = score.sno;（47）查询选修了课程C001或c007的学生学号、姓名；select sname,student.sno from student,scorewhere student.sno = score.sno and cno = C001 or cno = C007 ;（48）查询“会计系0102”班的学生及年龄不大于27岁（现有年龄）的学生；select sname,sno from studentwhere sclass = 会计系 and YEAR(sbirthday) = 1984;-年龄不大于岁就是出生年份大于减去即年就行了-（49）查询既选修了课程C001又选修了课程c007的所有学生学号、姓名；select sno from score where cno = C001 intersectselect sno from score where cno = C007-或者select sno from score where cno = C001 and sno in (select snofrom scorewhere cno = C007);（50）查询选修了课程名为“数据库原理”的学生的学号、姓名、性别、年龄；select student.sno,sname,ssex,2011-YEAR(sbirthday)from student,score,course where o = o and score.sno = student.sno and cname = 数据库原理;（51）查询其他班中比“信管系0101”班所有学生年龄都小的学生名单；select sno,sname,ssex,2011-YEAR(sbirthday) from studentwhere sclass = (select sclass from student where sname = 张振国)and sname 张振国;（52）查询与“齐振国”在同一个班学习的学生学号、姓名、性别、年龄；select sno,sname,2011-YEAR(sbirthday) from studentwhere year(sbirthday) (select YEAR(sbirthday) from student where sclass = 信管系)and sclass 信管系;-将年龄转化为出生日期的年份-（53）建立“信管系0101”班学生的视图，定义视图名为“info_student1”；create view info_student1 as select * from student where sclass = 近卫系;（54）建立“信管系0101”班学生的视图，定义视图名为“info_student2”，并要求进行修改与插入时，仍须保证该视图只有“信管系0101”班学生；create view info_student2 as select * from studentwhere sclass = 近卫系with check option;（55）建立“信管系0101”班选修了“C001”课程的学生的视图，定义视图名为“info_C001_student1”；create view info_C001_student1 as select student.sno,sname,sclass,sbirthday from student,scorewhere sclass = 近卫系 and cno = C001（56）建立“信管系0101”班选修了“C001”课程且成绩在90分以上的学生的视图，定义视图名为“info_c001_student2”；create view info_C001_student2 as select student.sno,sname,sclass,sbirthday from student,scorewhere sclass = 近卫系 and score 90;（57）定义一个反映学生年龄的视图，定义视图名为“vbirthday_student”；create view vbirthday_studentas select sno,sbirthday from student;（58）将学生表中所有女生记录定义为一个视图，视图名为“vfemale_student”；create view vfemale_student as select sno,sbirthday,sname,ssex from studentwhere ssex = 女;（59）将学生的学号及其平均成绩定义为一个视图，视图名为“vaverage_student”；create view vaverage_student (sno,average) as select sno,AVG(score) from scoregroup by sno;（60）删除视图“info_student1”，删除后即重建；drop view info_student1;（61）在“信管系0101”班学生视图中找出年龄小于27岁（现在的年龄）的学生；select sno,2011-YEAR(sbirthday) from info_student1where YEAR(sbirthday) 1984;-年龄小于岁就是出生年份大于减去即年就行了-（62）利用视图查询“信管系0101”班选修了“C001”课程的学生；select distinct sno,sname from info_C001_student1;（63）通过“信管系0101”班info_student2视图中学号“011111103”的学生姓名改为“潘长江”；update info_student2 set sname = 潘长江 where sno = 011115219;（64）向“信管系0101”班info_student1视图中插入一个新学生记录，其中：学号：011111136，姓名：张艺谋，性别：男，出生日期：1987.11.9；Insert into info_student1 (sno,sname,ssex,sbirthday) Values (011111136,张艺谋,男,19871109);（65）通过视图info_student1删除信管系0101班学号为“011111135”、姓名为“黄健中”的学生记录；Delete from info_student1 where sno = 011111135 and sname = 黄健中;三、实验心得有关于数据库实验的心得体会，总的来说，受益匪浅。在这短短的一天试验中，我们学到了很多东西，包括建表，导入数据，查询，插入。最重要的是我们有机会用电脑自己进行实践，没接触的时候总是觉得它比较深奥或是不可接近的新型语言，尽管自己对C语言非常感兴趣，但还是有些心理上的陌生感。学习数据库就和我们平时的其它科目学习一样感觉它有永无止境的知识，数据库是我在高中时候听过，到了大学渐渐了解了些，但就其原理性的内容还不知道，也就是根本就不清楚什么是数据库，只是知道一个所谓的中国字典里的名词。我认识它是从我接触实验运作开始的，刚开始就是建立数据库，两种验证模式，没什么东西但还觉得不错。进而就是操作语言了，紧接着就是触发器的使用，进而对数据库高级的使用，等等。 开始知道数据库的时候想学，不知道从何而起，不懂的话怎么问，从什么地方学起。后来到大三开学后有数据库原理必修课，非常高兴。当时感觉SQL Sever数据库管理既然是单独一门课程一定会讲的比较细，也能学到真正实用的内容。学了这门课以后发现和我想的基本是一样的，老师对学生也比较和蔼可亲