广工工业大学数据库实验指导及报告(共24页).doc

精选优质文档-倾情为你奉上编号： 2012版数据库原理及应用实验报告实验时间： 2011-2012春季 实验班级： 自动化电气09级 班 实验报告总份（片）数： 份（片）实验教师： 鲍芳 实验中心 508 实验室广东工业大学实验报告（范本，非设计性实验）要求采用学校的实验报告书（16开）。如打印也应采用实验报告书的版头（A4纸），不接受电子版!每份实验报告必须包含以下5项：一、 实验目的与要求（按实验指导书“实验目的”填写）二、 实验方案（按实验指导书“实验步骤”及本文档的实验内容要求填写）三、 实验结果和数据处理（按实验操作结果记录填写）四、 结论（可根据实验内容给出所对应数据库理论要点）五、 问题与讨论（可回答思考题或实验中遇到的问题及解决方法）若在报告中描述你在实验中遇到的问题及解决方案，将有机会获得额外加分！专心-专注-专业广东工业大学实验报告_自动化_学院_11电气_专业_2_班 成绩评定_学号_姓名_张锦淇_(合作者_号_ 教师签名_预习情况操作情况考勤情况数据处理情况实验 一 题目 数据库系统设计 第_周星期_第_节一、 实验目的与要求掌握数据库的基本设计技术，熟悉数据库概念模型设计的每个任务步骤，并加深对关系数据库概念和特点的理解。要求学生根据周围的实际情况自选一个小型的数据库应用项目并深入到应用项目的现实世界中进行系统分析和数据库设计，例如选择学生成绩管理系统、图书管理系统或仓库管理系统等。学生成绩管理数据库的基本要求：1. 能够记录学生的基本信息，包括学号、姓名、性别、出生日期及所属学院；2. 能够记录课程的基本信息，包括课程号，课程名，先行课号；3. 能够记录学生选课情况及选修课程的成绩；（学生成绩管理系统及图书管理系统数据库应用项目介绍可参见蔡延光数据库原理及应用第11章或苗雪兰数据库原理及应用第5章）。给出各表的关系结构和表级约束、给出各属性域的描述（包括物理描述和语义描述）；二、 实验方案利用数据库设计的5个步骤（识别实体、确定关系、列出属性、确定键及属性的域属性的域），对学生成绩管理系统（必选）、图书管理系统（可选）使用鸭脚模型对学生选课系统（必选）及图书管理系统进行相关数据库的数据建模及设计。l 使用ChenERD或鸭脚模型表示所设计的概念模型；l 给出各表的关系结构和表级约束；l 给出各属性域的描述，包括物理描述和语义描述；三、 数据库应用项目进行概念模型设计实验结果和数据处理1. 识别实体，确定关系：学生（学号、姓名、性别、出生日期、学院）课程（课程号、课程名、现行课号）选课（学号、课程号、成绩）说明：作为主键，学号必须存在学生表中、课程号必须存在课程表中。2. 列出属性及属性的域1) 学生表属性类型约束学号Char(20)主键姓名Char(20)NULL性别Char(20)男 或 女出生日期smalldatetimeNULL学院VarcharNOT NULL2) 课程表属性类型约束课程号Char(20)主键课程名Char(20)NOT NULL先行课号Char(20)NULL3) 选课表学号Char(20)外键课程号Char(20)外键成绩int01003. 鸭脚模型 课程课程号（PK）课程名先行课程号 学生 学号（PK）姓名性别出生日期学院 选课学号（FK）课程号（FK）成绩 4. 关系结构和表级约束最大基数：学生与选课为M：N课程与选课为1：N最小基数：学生与选课为1-0关系课程与选课为1-0关系表级约束：1）性别只可以取男或女2）选课成绩依赖于课程号和学生号四、 结论答：数据库不仅仅是保存数据，而且保存了数据之间的关系。多表之间存在约束条件，由业务的规则设定，多表保证了信息的完整性，每个表都有自己的主键，表中的外键要参照完整性约束。五、 问题与讨论1. 关系数据库中如何保存实体之间的关系？2. 学生选课系统中属性学号、课程号及成绩采用哪种数据类型更好，数值型还是字符型？为什么？3. 在所设计的数据模型中体现的业务规则是什么？1.答：通过表和表中放置外键来保存关联表之间的联系。2.答：学号，课程号采用数值型的数据更好，可以防止学生在输入学号的时候出错。3.答：体现出来的业务规则是：首先要有学生号和课程号，才能在选课表中生成一个记录（参照完整性约束）。其次，一个学生可以选择多门课，也可以不选课；一门课可以被多个学生选择，也可以没有学生选择。广东工业大学实验报告_自动化_学院 _11电气_专业_2_班 成绩评定_学号_ 姓名_张锦淇 (合作者_号_)教师签名_预习情况操作情况考勤情况数据处理情况实验 二 题目 数据库的定义实验 第_周星期_第_节一、 实验目的与要求要求熟练掌握和使用SQL、SQL Server企业管理器创建数据库、表、索引和修改表结构，并学会使用SQL Server查询分析器接受SQL语句和进行结果分析。二、 实验方案1、 在企业管理器中创建及打开数据库，在“新建数据库”时指明数据文件及事务日志的位置；2、 在SQL Server查询分析器中用SQL语句实现以下各表：学生（学号，姓名，性别，出生日期，学院）；课程（课程号，课程名，先行课号）；选课（学号，课程号，成绩）；3、建立库、表和表间的联系，选择合适的数据类型，定义必要的列级约束（包括性别约束和成绩约束）和表级约束。创建数据库中的表后，在查询分析器的对象浏览器中可见该表及其所拥有的约束：学生：2课程：1选课：4三、 实验结果和数据处理create table 学生( 学号 char(20) primary key , 姓名 varchar(20) , 性别 char(20) check (性别 in ('男' ,'女') ), 出生日期 smalldatetime, 学院 varchar(20)create table 课程(课程号 char (20) primary key , 课程名 char (20), 先行课号 char(20) )create table 选课( 学号 char (20) foreign key (学号) references 学生 (学号) , 课程号 char(20) foreign key (课程号) references 课程 (课程号), 成绩 int check (成绩>=0 and 成绩 <=100) constraint C1 primary key (学号 , 课程号)四、 结论SQL的数据库定义的功能包括定于数据库、基本表、视图和索引，SQL一般不提供修改试图定义及修改索引定义的操作。在定义基本表的同时，还可以定义与该表相关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中。五、 问题与讨论1、举例说明创建外键的SQL语句中的参照表和被参照表各指什么？ON DELETE CASCADE 关键字对删除记录有何影响？答：学生表（学号，姓名）、成绩表（学号，成绩），这两个表在学号上建立外键关系的话， 学生表是被参照表， 成绩表是参照表。ON DELETE CASCADE 是级联删除，如果删除学生表里的b学号的记录，那成绩表里所有学号为b的记录也会同时被删除。2、描述实验中遇到的问题及解决方法在实验中，我create了一个table学生，但是没设定学号是主键等，需要修改表，可以通过alter语法进行修改或者删除表再重新输入。需要注意的是，学生表中学生姓名不需要用unique类型，因为用学号做主键，学生姓名可以重复，并且如果搜索姓名相同的不同学号的学生的记录时，是不成功的。另外出生年月需要用smalldatetime类型而不用char类型。广东工业大学实验报告_自动化_学院 _11电气_专业_2_班 成绩评定_学号_姓名_张锦淇(合作者_号_)教师签名_预习情况操作情况考勤情况数据处理情况实验 三 题目 数据库的建立和维护实验 第_周星期_第_节一、 实验目的与要求要求熟练掌握和使用SQL、SQL Server企业管理器向数据库输入数据、修改数据和删除数据操作。二、 实验方案通过SQL Server企业管理器或查询分析器实现对学生成绩管理数据库的数据增加、数据删除和数据修改操作。要求每个表的记录在10行以上，可根据查询要求增加或完善表中记录；输入数据检验各数据约束的限制；数据更改和数据删除时检验外码约束；三、 实验结果和数据处理数据输入：insert into 学生 (学号,姓名,性别,出生日期,学院) values ('001','王鹏亮','男', '','自动化')insert into 学生 (学号,姓名,性别,出生日期,学院) values ('002','苏晓伟','男', '','自动化') .insert into 课程( 课程号,课程名,先行课号) values ('001','高等数学','1')insert into 课程( 课程号,课程名,先行课号) values ('002','大学英语','2')insert into 选课(学号,课程号,成绩) values ('001','001','88')insert into 选课(学号,课程号,成绩) values ('001','002','83')insert into 选课(学号,课程号,成绩) values ('001','003','75') .学生表课程表选课表数据更新update 选课 set 成绩='93'where 学号='001'数据删除Delete from 选课 where 学号='001'检验各数据约束的限制insert into 学生(学号,姓名,性别,出生日期,学院) values ('011','王晓晓','男性', '','自动化')insert into 选课(学号,课程号,成绩) values ('010','15','108')四、 结论在输入数据时，数据必须按照前面所指定的数据类型进行输入，超出数据类型输入无效。可以通过用插入数据指令来实现对表格数据的输入，还可以通过使用查询分析器来达到显示和修改数据的目的。五、 问题与讨论1、 举例说明如何在输入数据时检验各数据约束的限制?比如一个学生信息表中，会有性别列，而且性别只能是男或者女，这时候就可以使用检查约束，检查输入的值是否为男或者女，如果不是这两个值则会报错！2、 举例说明如何在数据更改和数据删除时检验外码约束？举例说明创建外键时有无ON DELETE CASCADE 关键字对删除记录有何影响？如果输入学生信息时，输入性别不是男或女，按执行后，信息不能成功输入，系统会自动报错，这是因为外码约束的作用，性别的值只能是男或者女。学生表（学号，姓名）、成绩表（学号，成绩），如果把学生表中的学号删除了，那么成绩表中的成绩也会删除。ON DELETE CASCADE指定如果试图删除某行，而该行含有由其它表的现有行中的外键所引用的键，则也将删除所有包含那些外键的行。如果在目标表上也定义了级联引用操作，则对从那些表中删除的行同样采取指定的级联操作。3、 描述实验中遇到的问题及解决方法输入数据时要注意表的名字，格式，外码约束等。广东工业大学实验报告_自动化_学院 _11电气_专业_2_班 成绩评定_学号_ 姓名_张锦淇_(合作者_号_) 教师签名_预习情况操作情况考勤情况数据处理情况实验 四 题目数据库的简单查询和连接查询实验 第_周星期_第_节一、 实验目的与要求要求熟练掌握和使用SQL、SQL Server企业管理器向数据库输入数据、修改数据和删除数据操作。二、 实验方案通过SQL Server查询分析器实现对学生成绩管理数据库的以下简单查询和连接查询：1. 求数学系学生的学号和姓名；2. 求选修了课程的学生学号；3. 求选修课程C1的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列；4. 求选修课程C1且成绩在8090分之间的学生学号和成绩，并将成绩乘以系数0.8输出；5. 求数学系或计算机系姓张的学生的信息；6. 求缺少了成绩的学生的学号和课程号；7. 查询每门课程的先行课程名；三、 实验结果和数据处理1. 求数学系学生的学号和姓名select 姓名,学号from 学生where 学院='数学系'2. 求选修了课程的学生学号select distinct 学号from 选课where 课程号is not null3. 求选修课程C1的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列select 学号,成绩from 选课where 课程号='001'order by 成绩desc,学号4. 求选修课程C1且成绩在8090分之间的学生学号和成绩，并将成绩乘以系数0.8输出select 学号,0.8*成绩from 选课where (课程号='001')and(成绩between 80 and 90)5. 求数学系或计算机系姓张的学生的信息select * from 学生where 学院in ('数学系','计算机系')6. 求缺少了成绩的学生的学号和课程号select 学号,课程号from 选课where 成绩is null7. 查询每门课程的先行课程名select 课程名,先行课号from 课程四、 结论关系数据库方法的主要优点之一就是它能够十分灵活地检索数据。基本上能够以任何方式查看数据库中的数据。关键在于能够正确写出SQL语言。在关系数据库中有三个核心的检索操作：选择、投影和连接。五、 问题与讨论1、如何提高数据查询和连接速度？对索引优化，建索引的选择必须结合SQL查询、修改、删除语句的需要，一般的说法是在WHERE里经常出现的字段建索引。如果在WHERE经常是几个字段一起出现而且是用AND连接的，那就应该建这几个字段一起的联合索引，而且次序也需要考虑，一般是最常出现的放前面。2、描述实验中遇到的问题及解决方法在进行第二个实验任务时，如果不在学号前面加distinct，会出现，每选修一次课，学号会 出现一次，若一个人选修了几门，他的学号会重复出现。加上distinct后，就能实现凡是选修了课学号就出现一次。广东工业大学实验报告_自动化_学院 _11电气_专业_2_班 成绩评定_学号_ 姓名_张锦淇_(合作者_号_) 教师签名_预习情况操作情况考勤情况数据处理情况实验 五 题目数据库的嵌套查询实验 第_周星期_第_节一、 实验目的与要求进一步掌握SQL Server查询分析器的使用方法，加深SQL语言的嵌套查询语句的理解。二、 实验方案通过SQL Server查询分析器实现对数据库的以下嵌套查询，并尝试用多种形式表示实验中的查询语句，并进行比较。1、求选修了高等数学的学生学号和姓名；2、求C1课程的成绩高于张三的C1课程成绩的学生学号和成绩；3、求选修C2课程的学生姓名；4、求没有选修C2课程的学生姓名；5、求选修C1课程而没有选修C2课程的学生姓名；三、 实验结果和数据处理1. 求选修了高等数学的学生学号和姓名select 学号,姓名from 学生where 学号in(select 学号from 选课where 课程号in(select 课程号from 课程where 课程名='高等数学')2. 求C1课程的成绩高于张三的C1课程成绩的学生学号和成绩select 学号,成绩from 选课where 课程号='C1' and 成绩>(select 成绩from 选课where 课程号='C1' and 学号in(select 学号from 学生where 姓名='张三')3. 求选修C2课程的学生姓名select 姓名from 学生where 学号in (select 学号from 选课where 课程号='C2')4. 求没有选修C2课程的学生姓名select 姓名from 学生where 学号not in (select 学号from 选课where 课程号='C2')5. 求选修C1课程而没有选修C2课程的学生姓名select 姓名from 学生where 学号in(select 学号from 选课where 课程号='C1')and 学号not in(select 学号from 选课where 课程号='C2')四、 结论 对索引优化，建索引的选择必须结合SQL查询、修改、删除语句的需要，一般的说法是在WHERE里经常出现的字段建索引。如果在WHERE经常是几个字段一起出现而且是用AND连接的，那就应该建这几个字段一起的联合索引，而且次序也需要考虑，一般是最常出现的放前面。五、 问题与讨论1、 嵌套查询和连接查询有何区别？在本实验的查询任务中，哪些是可以由嵌套查询和连接查询实现？当查询同时涉及两个以上的表时，称为连接查询。连接查询包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件查询。在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另外一个查询块的WHERE字句或HAVING短语的条件中的查询称为嵌套查询。连接查询：求选修了高等数学的学生学号和姓名，求选修C2课程的学生姓名。嵌套查询：求C1课程的成绩高于张三的C1课程成绩的学生学号和成绩，求选修C1课程而没有选修C2课程的学生姓名2、 描述实验中遇到的问题及解决方法在写SQL语句使因为空格，半角标点等问题系统经常出错，经过排查后，知道以后写SQL时格式要注意的内容。广东工业大学实验报告_自动化_学院 _11电气_专业_2_班 成绩评定_学号_姓名_张锦淇_ (合作者_号_) 教师签名_预习情况操作情况考勤情况数据处理情况实验 六 数据库统计查询实验 第_周星期_第_节一、 实验目的与要求熟悉掌握SQL Server查询分析器的使用方法，加深对SQL语言查询语句的理解，熟悉掌握数据查询中的分组和统计的操作方法。二、 实验方案通过SQL Server查询分析器实现以下对数据库的统计查询；1. 用SQL语句实现学生人数的统计；2. 用SQL语句实现计算机系学生人数的统计；3. 用SQL语句实现各系学生人数的统计；4. 用SQL语句统计选修某课程学生的人数；5. 用SQL语句统计选修了课程的学生的人数；6. 用SQL语句统计被选课程及选修该课程的人数；7. 用SQL语句统计每个学生全部课程的平均成绩；8. 用SQL语句统计选修课超过3门课的学生学号；（可选）三、实验结果和数据处理1. 用SQL语句实现学生人数的统计select count(*) as 学生人数from 学生2. 用SQL语句实现计算机系学生人数的统计select count(*) as 计算机系人数from 学生where 学院='计算机系'3. 用SQL语句实现各系学生人数的统计select 学院, count(*) as 学生人数from 学生group by 学院4. 用SQL语句统计选修某课程学生的人数select count(*) as 微机原理课程人数from 学生where 学号in (select 学号from 选课where 课程号in (select 课程号from 课程where 课程名='微机原理')5. 用SQL语句统计选修了课程的学生的人数select count(*) as 已选修课程人数from 学生where 学号in (select 学号from 选课where 课程号is not null)6. 用SQL语句统计被选课程及选修该课程的人数select 课程号,count(学号) as 学生人数from 选课group by 课程号7. 用SQL语句统计每个学生全部课程的平均成绩select 学号, avg(成绩) as 平均成绩from 选课group by 学号8. 用SQL语句统计选修课超过3门课的学生学号select 学号from 选课group by 学号 having count(*)>3四、结论SQL Server包括很多内置的函数。它们中的一些被称为聚集函数，聚集函数在结果列表中提供汇总值。分组查询使用GROUP BY字句。HAVING字句用于筛选分组的最终结果，只有满足HAVING字句指定条件的组才输出。五、问题与讨论1、试用GROUP BY（分组条件）字句后，语句中的统计函数的运行结果有什么不同？运行结果会根据GROUP BY字句中的条件进行一定顺序的排列。2、描述实验中遇到的问题及解决方法对group by 语句还不太了解如何使用，经过本实验，知道此语句是使结果按照指定的顺序排列。广东工业大学实验报告_自动化_学院 _11电气_专业_2_班 成绩评定_学号_张锦淇_姓名_张锦淇_(合作者_号_) 教师签名_预习情况操作情况考勤情况数据处理情况实验七题目 数据库的视图和图标的定义及使用实验 第_周星期_第_节一、 实验目的与要求掌握SQL Server中视图的创建和使用，加深对视图和SQL Server图表作用的理解。二、 实验方案通过SQL Server实现数据库的视图的创建及使用，完成以下任务：1、建立计算机系学生的视图；2、由学生、课程和选课三个表，定义一个计算机系的学生成绩视图，其属性包括学号、姓名、课程名和成绩；3、将学生的学号、总成绩、平均成绩定义成一个视图；4、统计学生的学号、姓名、总成绩及平均成绩（可利用3的视图或group by 学生.学号,学生.姓名）；三、实验结果和数据处理1、建立计算机系学生的视图create view 计算机学生信息as select 学号,姓名,性别,出生日期,学院from 学生where 学院='计算机系'2、由学生、课程和选课三个表，定义一个计算机系的学生成绩视图，其属性包括学号、姓名、课程名和成绩create view 计算机系学生成绩as select 学生.学号,姓名,课程名,成绩from 学生,课程,选课where 学生.学号=选课.学号and 选课.课程号=课程.课程号and 学生.学院='计算机系'3、将学生的学号、总成绩、平均成绩定义成一个视图create view 学生个人成绩as select 学号, sum (成绩) as 总成绩,avg(成绩) as 平均成绩from 选课group by 学号4、统计学生的学号、姓名、总成绩及平均成绩（可利用3的视图或group by 学生.学号,学生.姓名）create view 学生个人成绩 as select 学生.学号,学生.姓名,sum (成绩) as 总成绩,avg(成绩) as 平均成绩from 选课,学生where 学生.学号=选课.学号group by 学生.学号,学生.姓名四、结论视图是关系数据库系统提供给用户以多角度观察数据库中数据的一种重要方法。视图就是从一个或几个基本表（或视图）导出的虚表。视图在概念上与基本表等同，视图一经定义，则可以和基本表一样进行查询、删除、更新等操作，而且用户可以在视图上再定义视图。五、问题与讨论1、为什么要建立视图？视图和基本表有什么不同？答：视图能够简化用户的操作，视图能够以多角度观察数据库中的数据，视图对重构数据库提供了一定程度的逻辑独立性，视图能够对机密数据提供安全保护，适当的利用视图可以更加清晰的表达查询。能够让用户对感兴趣的项目一目了然。2、group by语句在使用时需要注意什么事项？GROUP BY 语句中的表达式必须与选择列表表达式完全匹配。3、描述实验中遇到的问题及解决方法在进行第一个任务时，是要求查询 “计算机”学生的信息，但是我的数据库的属性是“计算机系”，一开始where语句后面我写的是学院=计算机，出来的视图全为NULL，后来改为学院=计算机系，就能将计算机的学生的信息视图出来了。在进行第4个任务时，select语句中的表达式没有和group by 中的表达式完全一致，系统报错，将它们改为一致后，命令就能够成功执行。2011数据库原理及应用实验安排（实验一至实验七）一、实验注意事项：1. 本实验使用MS SQL SERVER 2000的企业管理器及查询分析器；2. 在企业管理器中创建及打开数据库，在“新建数据库”时要指明数据文件及事务日志的位置（可指向U盘），不要使用缺省设置；3. 在查询分析器中输入SQL语句，注意先选择对应的数据库，而不是缺省的master数据库；4. 注意每次输入新的SQL语句时先清空查询分析器的输入界面；5. 创建数据库中的表后（注意刷新），在查询分析器的对象浏览器中可见该表及其所拥有的约束：学生：2课程：1选课：46. 企业管理器在数据库子菜单使用“附加数据库”命令选中.mdf文件（对应的.log文件自动加入，注意两者名称一致），可打开自己的数据库。“分离数据库”命令可关闭该数据库；7. 注意成绩的属性选择，要求能够进行数值计算；8. 出生日期设为datetime类型（输入格式为1970-06-01）后，查询时在select语句中可使用convert(varchar(10),出生日期,111) as出生日期，将其截短为年月日；二、上机步骤：1. 打开SQL SERVER 2000-企业管理器：2. 如果展开SQLServer 为“无项目”，鼠标右键选择“新建SQLServer注册”，选择Local及Windows身份验证，展开树形目录直到可见Northwind及Pubs等；3. 建立自己的数据库，名称为“电气X班XXX学生成绩管理数据库”；4. 打开查询分析器，选择Local及Windows身份验证;5. 在查询分析器中输入SQL语句；6. 约束的图形化界面实现方法:性别约束: ( 性别=女 OR 性别=男)成绩约束: ( 成绩between 0 and 100 )7. 实验七中SQL语句不能以分号;结束,否则语法错误。8. 保存查询结果：在“查询分析器”的“查询”菜单下选择“文本显示结果”后，可通过复制、粘贴等方式将结果存为.txt文件