《数据库原理与应用44学时》实验指导书(实验1-4)

1、数据库系统概论An Introduction to Database System实验指导书(44课时版)中南民族大学计算机科学学院2015.5目 录概 述21. 实验简介及基本要求22. 基本知识2概念结构设计ER图3逻辑结构设计ER图向关系模型的转换4第一部分 基础实验5实验环境简介51.查看安装Microsoft SQL Server 2000的目录结构62.服务管理器的启动、暂停和停止73.注册/配置服务器7实验1 创建并管理数据库、数据表（必做）111-1使用企业管理器创建并管理数据库、数据表111-2使用T-SQL语句创建并管理数据库、数据表16实验2 数据检索（必做）18概 述数

2、据库系统概论上机实验是学习本门课程的重要环节之一，实验的设置是为了配合课堂教学，使学生熟练掌握和深入理解课堂教学内容，进一步强化对数据库系统原理的理解。同时，通过上机练习，也可以检验用关系数据库标准语言SQL对数据库进行操作的结果是否正确，使关系数据库的基本概念更加具体、丰富。数据库系统概论的实验分为两大部分：第一部分是与理论课同步进行的课程基础实验，是非独立开设的实验；第二部分是继理论课后开设，需要独立开设的课程设计或综合性大实验。本书给出了这两种实验课的实验标准，供参考。1. 实验简介及基本要求 上机实验是数据库课程的重要环节，它贯穿于整个“数据库原理”课程教学过程中。本课程的实验分为前期

3、准备阶段、基本操作阶段和技术提高阶段三个阶段，其主要内容和基本要求为：1）前期准备阶段数据库课程实验的第一阶段为前期准备阶段。前期准备阶段的主要任务是理解数据库、数据模型和数据库系统的基本概念；掌握数据库的概念模型、数据模型及数据库系统的设计方法；根据这些方法自己设计一个数据库系统的实际应用项目，写出系统分析和系统设计报告，提出在系统中要解决的问题。2）基本操作阶段数据库课程实验的第二阶段为基本操作阶段。基本操作阶段的主要任务是掌握数据库系统的基本操作，包括T-SQL语言的应用和利用DBMS的工具进行数据库定义、维护、查询及掌握数据安全性、数据完整性等基本操作，并能够针对实际问题提出解决方法，

4、得出正确的实验结果。3）技术提高阶段数据库课程实验的第三个阶段为技术提高阶段。技术提高阶段的实验，要求学生不仅要把课本上的内容掌握好，同时还需要自学一些相关的知识，例如，软件工程、计算机网络技术、C#语言、Java语言及SQL Server2000的深入技术。技术提高阶段的主要任务是要掌握有关数据库备份和恢复技术、并发控制技术及数据库系统的编程技术等。2. 基本知识数据库设计与实现的基本过程包括需求分析、概念结构设计、逻辑结构设计、数据库的物理设计以及数据库的实施和维护等内容。需求分析确定系统的数据需求和处理需求；概念结构设计通过对用户需求的综合、归纳和抽象，形成一个独立的概念模型；逻辑结构设

5、计则将概念模型转换为具体的数据库管理系统所支持的数据模型；数据库的物理设计结合应用确定数据模型的存储结构和存取方式。在这些工作完成之后，就可以建立数据库并开始调试运行了，在数据库的运行过程中必须对其进行维护包括数据库的备份与恢复、性能分析与改进等等。对于整个过程的详细描述超出了本实验指导的范围，请参见教材，下面简单介绍设计过程的中两个关键步骤概念结构设计和逻辑结构设计。概念结构设计ER图概念模型是用户需求的抽象，通常用实体关系图（Entity Relationship Diagram，即ER图）来表示。设计ER图的出发点是数据流图层次的选择，一般选择中层的数据流图作为分析设计的依据。此时得到的

6、是局部ER图，对于局部ER图而言，实体和属性区分是比较关键的，但两者之间没有绝对的界限，对于属性而言可以参考的两条准则是：（1）作为属性，不能再具有需要描述的性质。属性必须是不可分的数据项，不能再包含其他属性。（2）属性不能与其他实体具有联系，即ER图中所表示的联系是实体之间的关系。在得到各个局部ER图后，需要将它们合并成一张总的ER图。首先是简单的合并，协调各个局部ER之间不一致的地方；然后再对得到总ER图消除冗余。冗余会造成存储效率低下和更新异常等问题，可通过分析数据字典中数据项之间的逻辑关系以及规范化理论来加以消除，当然并不是所有的冗余都需要消除的。事实上必要的冗余是提高查询效率的关键，

7、当然此时需要满足完整性约束。本实验要求采用的ER图图例如下所示：图1 ER图图例逻辑结构设计ER图向关系模型的转换关系数据库的数据模型（即关系模型）由一组关系模式组成，因而ER图向关系模型的转换实际上就是将实体（包括实体的属性）和联系转换为关系模式，一般遵循以下基本原则：（1）一个实体型转换为一个关系模式。实体的属性就是关系的属性，实体的码就是关系的码。（2）一个1：X（X为1或n）联系通常与某一端的关系模式合并，其中1：n的联系需要与n端对应的关系模式合并。需要在合并的关系模式的属性中加入另一个关系模式的码和联系本身的属性。（3）一个m：n联系转换为一个关系模式。与该关系相连的各实体的码以及

8、联系本身的属性均转换为关系的属性，而关系的码为各实体码的组合。（4）三个或三个以上实体间的一个多元联系可以转换为一个关系模式。与该多元联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为各实体的码的组合。（5）具有相同码的关系模式可合并。第一部分 基础实验实验环境简介数据库管理系统也称为DBMS，它是对数据库中数据进行管理的一个中间层软件，主要具有以下功能：（1）数据定义功能DBMS提供数据定义语言（Date Definition Language，简称DDL），用户通过它可以方便地对数据库中的数据对象进行定义。（2）数据操纵功能DBMS提供数据操纵语言（Date Manip

9、ulation Language，简称DML），用户通过它实现对数据库的基本操作。（3）数据库的运行功能数据库在建立、运用和维护时由数据库管理系统统一管理、统一控制，以保证数据的安全性、完整性、多用户对数据的并发使用及发生故障后的系统恢复。（4）数据库的建立和维护功能它包括数据库初始数据的输入、转换功能，数据库的转储、恢复功能，数据库的重组织功能和性能监视、分析功能等。这些功能通常由一些实用程序完成。SQL Server 2005主要有三个服务，即：SQL Server服务、SQL Server Agent服务和Microsoft Distribute Transaction Coordina

10、tor (MS DTC)服务。Ø SQL Server服务的功能是处理所有Transact-SQL语句、管理服务器上数据库的所有文件；在多个并发用户之间分配计算机资源，防止产生逻辑问题，如控制并要求更新服务器上相同数据的用户请求的时序；保证数据的安全性、一致性、完整性。Ø SQL Server Agent服务器代理是独立于SQL Server的一个服务，需要和SQL Server分开启动。SQL Server Agent服务的主要功能是创建和管理作业、创建和管理报警、创建和管理操作员。Ø MS DTC服务也是独立于SQL Server的一个服务，它允许应用程序在一

11、个事务中包括几个不同的数据源。MS DTC协调分布式事务的正确完成，保证服务器上所有数据的更新都是一致的，或者在发生错误的情况下，取消所有的数据更新。1.查看安装Microsoft SQL Server 2000的目录结构找到自己电脑上安装数据库程序的路径，如图：主要文件夹含义如下：1）BACKUP：这个目录最初为空，它是SQL Server创建磁盘备份设备的缺省存储位置。2）Binn：这是NT客户和服务器的可执行文件、在线帮助文件和扩展存储过程的DLL文件所在的存储位置。3）Data：这是所有数据库的数据文件和日志文件的缺省存储位置。4）LOG：是SQL Server存储日志文件的位置。所有

12、提示、警告和错误信息存储在这里。5）Install：包括安装SQL Server时所使用的安装脚本和输出文件。6）JOBS：这是临时作业的输出文件所在的存储位置。2.服务管理器的启动、暂停和停止在图上选择“开始”，启动服务器。3.注册/配置服务器使用SQL Server之前，必须注册和配置服务器。在注册服务器之前应先创建一个服务器组，注册的服务器放在这个服务器组中。（1）注册服务器进入企业管理器，默认已经建立了一个本地（local）服务器。（2）配置服务器在SQL Server注册上单击右键，选择“属性”，如下图所示。1）在“常规”页中可以设定启动策略、网络配置等。单击网络配置，如下图通常至少

13、应启用TCP/IP协议。2）另一项常用的配置选项是“安全性”，可进行身份验证等设定。实验1 创建并管理数据库、数据表（必做）1-1使用企业管理器创建并管理数据库、数据表一、实验目的（1）熟悉Microsoft SQL Server 2000的基本操作。（2）理解数据库的概念。（3）掌握使用企业管理器创建数据库的方法。（4）掌握查看、修改数据库属性的方法。（5）了解由已经生成的数据库产生SQL Server脚本的方法。二、实验内容1、使用企业管理器创建数据库。2、创建并管理数据库users该数据库的主数据文件的逻辑名称是usersbin，操作系统文件是users.mdf，大小是4MB，最大是30

14、MB，以10的速度增长；该数据库的日志文件的逻辑名称是userslog，操作系统文件名是users.ldf，大小是5MB，最大是25MB，以1MB的速度增加。具体创建步骤如下：1）进入SQL Server企业管理器2）展开目录树，在“数据库”选项上单击右键，选择“新建数据库”，该窗口有3个选项卡：常规、数据文件和事务日志。注意：数据库的名称必须遵循SQL Server的命名规范，并且不能与已有的数据库名重复。常规标识符的命名规则：标识符长度可以为1128个字符首字符必须为字母、下划线、或#第一个字符后面的字符可以是字母、数字、下划线、或#等符号标识符不能与Transact-SQL中的保留字同名

15、保留字与标识符等都不区分大小写定界标识符：需要使用双引号” ”或方括号 等定界符进行分隔的标识符。在定义的标识符中用到数据库保留字、空格等特殊字符时，必须使用定界标识符。数据文件的类型：数据类型文件扩展名主要数据文件.mdf次要数据文件.ndf事务日志文件.ldf3、由已经存在的数据库生成创建数据库的TSQL脚本步骤：1）鼠标右键单击已经存在的数据库JWGL，选择“所有任务”；2）选择“生成SQL脚本”如下图所示，选择“全部显示”，可以选择数据库中想要生成SQL脚本的数据对象3）单击“选项”页，如下图所示，至少选择“编写数据库脚本”练习生成数据库脚本“users.sql”4.创建表（1）使用企

16、业管理器创建表步骤：1）展开数据库目录树2）选择“表”，鼠标右键单击，并选择“新建表”练习创建表student，表结构如下：列名称类型宽度允许空值默认值主键说明sidchar8否是学号snamevarchar8否学生姓名sexchar2否性别birthsmalldatetime是出生年月classidchar6是班级号entrancedatesmalldatetime是入学日期addrvarchar40是家庭住址5.管理表使用企业管理器步骤：1）选择一张已经建立好的表2）右键单击，选择“设计表”，如下图所示，进入与创建表相同的界面3）可进行表列的增加、删除和修改操作对表添加、修改、删除数据在出

17、现的窗口中可进行表中数据的添加、修改和删除操作。 练习在学生表中增加一列，字段名：stature（身高），字段类型：numeric，字段长度4.2，允许为空；创建后再删除。请写出增加列和删除列的SQL语句。练习向表中添加数据sidsnamesexbirthclassidentranceaddr9940201张红男1984-9-1994021999-9-1南京市鼓楼区9940202林丽女1984-6-1994021999-9-1扬州市五庭街9940203曹雨男1983-12-1994021999-9-1南京市玄武区9940204芮华男1983-9-9994021999-9-1江苏仪征市青年路99

18、40205李红女1984-9-8994021999-9-1北京市中关村9940206张海国男1984-4-5994021999-9-1江苏仪征市青年路9940207李红女1984-5-23994021999-9-1南京市中山北路9940210赵青男1985-1-9994031999-9-1南京市中山北路1-2使用T-SQL语句创建并管理数据库、数据表一、实验目的（1）了解查询分析器的使用方法。（2）掌握TSQL语句的基本使用。（3）熟练掌握DDL语句。（4）熟练掌握DML(Insert, Delete, Update)语句。注意：在做删除操作前，请先备份数据库，否则已删除的数据库和数据表都将不

19、可再恢复二、实验内容1、创建并管理数据库users要求：（1）用T-SQL语言创建数据库users该数据库的主数据文件的逻辑名称是usersbin，操作系统文件是users.mdf，大小是4MB，最大是30MB，以10的速度增长；该数据库的日志文件的逻辑名称是userslog，操作系统文件名是users.ldf，大小是5MB，最大是25MB，以1MB的速度增加。（2）使用SQL语句将users数据库中的主文件扩大到40MB2、用TSQL语句创建并管理数据库“学生管理数据库”要求：（1）将该数据库存放在D盘的SQL目录下，数据库文件初始大小为1MB，最大容量为50MB，文件增展率为5MB（2）建

20、立日志文件“学生管理日志”，将该数据库存放在D盘的SQL目录下，日志文件初始大小为1MB，最大容量为5MB，并按数据文件的10增长。（3）修改数据库：将“学生管理数据库”的数据库最大容量更改为无限制（UNLIMITED），然后将“学生管理数据库”的日志文件增长方式改为1MB（4）在“学生管理数据库”中创建一张“学生情况表”，结构如下学号 整型NOT NULL 主键姓名 字符串，8字节NULL性别 字符串，2字节NULL年龄 短整型NULL专业 字符串，30字节NULL家庭地址 字符串，50字节NULL（5）修改表结构：将“学生情况表”中，家庭地址字段的长度改为80（6）向表中添加记录学号姓名性

21、别年龄专业家庭地址200009412汪小丽女22计算机上海市邯郸路220号200108215南键男21计算机青岛市高雄路1号200207121曲竹非女21会计学厦门市屯溪路291号（7）修改表中记录：将“曲竹非”同学的专业修改为“计算机”（8）删除记录：删除表中性别为“男”的学生记录（9）删除表：将“学生情况表”从“学生管理数据库”中删除实验2 数据检索（必做）一、实验目的（1）理解T-SQL语言的使用。（2）熟练掌握数据查询语句。SELECT ALL|DISTINCT *|<表达式>,<表达式>FROM <表名>, <表名>WHERE <

22、条件>GROUP BY <列名>, <列名>HAVING <谓词>ORDER BY <列名> ASC|DESC , <列名> ASC|DESC二、实验内容在“学生管理数据库”中建立四张表，分别如下：学生表：（主键：学号）学号姓名性别年龄所在院系班级入学日期20009001葛文卿女22国际贸易国贸2班200082920014019郑秀丽女21会计学会计1班20019220023001刘成铠男18计算机软件2班200282720026002李涛女19电子学电子1班200282720023002沈香娜女18计算机软件2班2002827

23、20026003李涛男19计算机软件1班200282720023003肖一竹女19计算机软件2班2002827课程表：（主键：课程号）课程号课程名先修课学分C801高等数学4C802C+语言C8073C803数据结构C8024C804数据库原理C8034C805操作系统C8074C806编译原理C8034C807离散数学4成绩表：（主键：学号、课程号）学号课程号成绩学分20023001C80198420023002C80470420026001C80185420023001C80299320026002C803824授课表：（主键：课程号、班级名）教师名课程号学时数班级名苏亚步C80172软件

24、2班王文山C80264软件2班张珊C80372软件2班王文山C80464软件2班苏亚步C80172软件1班（1） 列出软件2班女同学的名单；（2） 列出2002年元旦前注册的学生名单；（3） 列出所有年龄在19岁以下，或者女同学的名单；（4） 列出没有先修课的课程名；（5） 列出既不是“电子系”，也不是“会计系”的学生的学号、姓名和所在院系；（6） 查询选修“C801”课程的学生的最高分；（7） 统计男、女学生各自的人数和平均年龄；（8） 列出选修了一门以上课程的学生学号，及其选修门数；（9） 查询“沈香娜”同学所在班级的所有学生的学号和姓名；（10） 统计每一年龄选修课程的学生人数；（11）

25、 在基本表“成绩表”中检索平均成绩最高的学生学号；（12） 求表S中男同学的每一年龄组（超过2人）有多少人？要求查询结果按人数升序排列，人数相同按年龄降序排列；难题：1) 列出选修了全部课程的学生学号和姓名；2) 查询这样的学生，该生至少选修了学生20026001所选修的全部课程；实验3 视图的创建与使用（必做）一、实验目的（1）理解视图的概念；（2）掌握创建视图的方法；（3）掌握用视图管理数据的方法；二、实验内容1.创建一个名为stud_view1的视图，从学生管理数据库数据库的学生表中查询出性别为男的所有学生的学号，姓名，性别，班级，入学时间和出生年。2创建一个名为stud_view2的视

26、图，查询出选修了数据库原理的所有同学的选课信息，包括学号，姓名，成绩。3从视图stud_view1中查询出软件2班同学的资料。4向视图stud_view2中插入一行数据：学号为20023001，姓名为刘成铠，成绩为71，结果是什么？5修改视图stud_view1，将20026003号同学的性别更改为女，会发生什么？然后再次使用SQL语句查询学生表和视图stud_view1，看结果有何改变，为什么？如何防止将视图stud_view1中，学生的性别改成“女”？实验4 数据完整性（必做）一、实验目的（1）理解数据完整性的概念与分类；（2）掌握规则、默认的使用方法；（3）掌握用户自定义完整性的使用；二、实验内容1、创建规则（会用图形和语句方法创建）（1）创建入学日期规则“Enter_Univ