数据库原理及应用

1、数据库原理与应用实验指导书华中师范大学信息技术系二00七年九月项目一 数据库和表的管理2项目二 数据查询8项目三 数据库的完整性10项目四 存储过程和触发器13项目五 数据库系统设计16附录一：实验报告要求18附录二：实验成绩评分办法18项目一 数据库和表的管理一、实验学习目标1. SQL Server的基本数据类型；2.学会使用SQL Server企业管理器对数据库和表进行管理；3.学会使用Transact-SQL语句对数据库和表进行管理。二、实验设备微型计算机、SQL Server软件三、实验内容及步骤(一)使用SQL Server企业管理器对数据库进行管理1.通过SQL Server企业

2、管理器创建数据库(1)在SQL Server企业管理器控制台树型结构中, 选择数据库并单击鼠标右键, 随后再选择新建数据库；(2)利用下表中的信息创建数据库，对于该表没有指定的选项，请保留默认设置；数据库名称: SampleDBEM；排序规则名称: 使用服务器默认配置。文件  文件名位置初始尺寸 增长增量 最大文件尺寸 数据库 SampleDBEM_Data C:Program FilesMicrosoft SQL ServerMSSQLData 5 MB 25% 15 MB 日志SampleDBEM_Log C:Program FilesMicrosoft SQL ServerMS

3、SQLData 2 MB 50% 5 MB (3)为了验证是否已经正确创建SampleDBEM数据库，用户可以查看该数据库的属性。2.通过SQL Server企业管理器修改数据库修改数据库SampleDBEM的下述属性：数据库文件初始尺寸、增长增量和最大文件尺寸。(1)在控制台树型结构中, 首先展开数据库，随后选择SampleDBEM并单击鼠标右键，再从快捷菜单中选择属性；(2)利用下表所提供的信息修改SampleDBEM数据库的属性；文件名分配空间增长尺寸最大文件尺寸SampleDBEM_Data10 MB10%无限制SampleDBEM_Log5 MB20%15 MB(3)为了验证是否已经

4、正确修改SampleDBEM数据库，用户可以查看该数据库的属性。3.通过SQL Server企业管理器查看数据库信息通过SQL Server企业管理器查看数据库SampleDBEM的基本信息。(1)在控制台树型结构中, 首先展开数据库，随后选择SampleDBEM；(2)点击鼠标右键，选择查看，再选择任务板；(3)查看细节面板中所显示的数据库信息。4.通过 SQL Server 企业管理器删除数据库通过SQL Server企业管理器删除SampleDBEM数据库。(1)在企业管理器控制台树型结构中, 首先展开数据库，随后选择SampleDBEM并单击鼠标右键，再选择删除；(2)点击是删除数据库

5、，从而删除数据库SampleDBEM及其全部备份与恢复的历史记录；(3)验证是否已经删除SampleDBEM 数据库。 (二)使用Transact-SQL语句对数据库进行管理1.使用Transact-SQL 语句创建数据库利用下表所提供的信息，用 CREATE DATABASE 语句创建SampleDBTsql数据库。文件  文件名位置初始尺寸 最大文件尺寸增长增量数据库 SampleDBTsql_Data.mdf C:Program FilesMicrosoft SQL ServerMSSQLData 7 MB 无限制 3 MB 日志SampleDBTsql_Log.ldf C:P

6、rogram FilesMicrosoft SQL ServerMSSQLData 3 MB 10 MB 1 MB (1)打开SQL查询分析器；(2)编写创建SampleDBTsql 数据库的脚本，并执行该脚本；(3)切换到SQL Server企业管理器； (4)选择数据库并单击鼠标右键，随后再单击刷新；(5)为了验证是否已经正确创建SampleDBTsql数据库，用户可以查看该数据库的属性。2.使用Transact-SQL 语句修改数据库修改数据库SampleDBTsql的属性，将日志文件的最大尺寸增加到20MB。(1)切换到SQL查询分析器；(2)用ALTER DATABASE语句编写修改

7、SampleDBTsql 数据库的脚本，并执行该脚本； (3)切换到SQL Server企业管理器，为了验证是否已经正确修改SampleDBEM数据库，用户可以查看该数据库的属性。3.使用Transact-SQL语句删除单个或多个数据库 利用Transact-SQL语句删除先前创建的两个数据库。(1)打开SQL 查询分析器，用DROP DATABASE语句编写删除SampleDBTsql 和 SampleDBWizard 数据库的脚本，随后执行该脚本。(2)为了验证是否已经删除数据库SampleDBTsql，用户可以查看结果面板中的输出结果。(三)数据库表的创建、修改与删除1在企业管理器中创建

8、数据库表建立学生-课程数据库（StudentCourse）及三个表（学生表:Student、课程表:Course和学生选修表:SC）,各表的结构如表1-1表1-3所示。表1-1  学生信息表(Student)表结构列名数 据 类 型长度是否允许为空值说   明SnoChar5×学号，主键SnameVarChar20×姓名SsexChar2×性别SageSmallint默认年龄SdeptVarChar20×系别表1-2  课程信息(Course)表结构列名数据类型长度是否允许为空值说   

9、; 明CnoChar1×课程号，主键CnameVarChar20×课程名称CpnoChar1 先行课CreditSmallint默认学分表1-3  选课信息(SC)表结构列名数据类型长度是否允许为空值说    明SnoChar5×学号，主键CnoChar1×课程号，主键GradeDecimal4,1成绩(1) 在企业管理器中分别创建Student表、Course表和SC表在企业管理器中选择数据库StudentCourse在Student表上单击鼠标右键新建表输入Student表各字段信息将Sno设为主键单击保存图标

10、输入表名Student，即创建了表Student表。按同样的操作过程创建Course表和SC表。(2)企业管理器中删除Student表、Course表和SC表在企业管理器中选择数据库StudentCourse的表Student在Student上单击鼠标右键删除，即删除了表Student。按同样的操作过程删除Course表和SC表。2.使用T-SQL语句创建Student表、Course表和SC表自己写出创建Student表、Course表和SC表的SQL语句，并在企业管理器中查看结果。(1)启动查询分析器在“查询”窗口中输入T-SQL语句：(2)单击快捷工具栏的执行图标，执行T-SQL语句，即

11、可创建Student表、Course表和SC表。(四)数据库表数据的输入、修改与删除分别使用企业管理器和T-SQL语句，在数据库StudentCourse的3个表:Student表、Course表和SC表中插入多行数据记录，然后修改和删除一些记录。1在企业管理器中向数据库StudentCourse表输入数据(1)在企业管理器中向表Student中加入如表1-4所示的记录。在企业管理器中选择表Student在其上单击鼠标右键选择“返回所有行”逐字段输入各记录值，输入完后，关闭表窗口。表1-4 Student表数据SnoSnameSsexSageSdept95001李敏勇男20CS95002刘晨女

12、19IS95003王敏女18MA95004张立男18IS(2)在企业管理器向表Course中插入如表1-5所示的记录：表1-5  Course表数据CnoCnameCpnoCredit1数据库542数学NULL23信息系统144操作系统635数据结构746数据处理NULL27Pascal语言64 (3)在企业管理器中向表SC中插入如表1-6所示的记录：表1-6  SC表数据SnoCnoGrade95001192950012859500138895002290950023802使用T-SQL命令向数据库Student表插入数据使用T-SQL命令分别向Student表插入一行记

13、录。(1)启动查询分析器在“查询”窗口中输入T-SQL语句(2)单击快捷工具栏的执行图标，执行上述语句。(3)在企业管理器中观察数据的变化。（五）数据库表数据的修改与删除1在企业管理器中删除、修改数据库表数据(1)在企业管理器中选择表Student在其上单击鼠标右键选择“返回所有行”选择要删除的行单击鼠标右键删除关闭表窗口。(2)在企业管理器中选择表Student在其上单击鼠标右键选择“返回所有行”将光标定位至Sno为95002的记录的Sage字段，将值19改为22。2使用T-SQL命令修改表Student中的某个记录的字段值。利用T-SQL语句将上述的修改还原。(1)启动查询分析器在“查询”

14、窗口中输入以下T-SQL语句：(2)单击快捷工具栏的执行图标，执行上述语句。(3)在企业管理器中观察数据的变化。四、实验思考与总结1在SQL Server企业管理器对数据库中创建数据库应注意哪些问题？2利用SQL Server的企业管理器和T-SQL语句对数据库的表进行管理各有什么优势？项目二 数据查询一、实验学习目标1掌握使用SELECT语句进行查询的基本方法；2掌握嵌套查询和连接查询的表示和使用方法； 3掌握SELECT语句的统计函数的作用和使用方法；4掌握SELECT语句的GROUP BY和ORDER BY子句的作用和使用方法。二、实验设备微型计算机、SQL Server软件三、实验内容

15、及步骤在项目一建立的StudentCourse数据库中完成下列查询要求，写出SQL语句。 （一）简单查询1.查询全体学生的学号与姓名；2.查询全体学生的姓名及年龄；3.查询全体学生的姓名、年龄及所在系（要用小写字母表示系名，并用“系名”来表示列名，提示：使用字符串函数lower函数）；4.查询选修了课程的学生学号。（二）条件查询1.查询计算机系全体学生的姓名；2.查询所有年龄在20岁以下的学生姓名及其年龄；3.查询年龄在20到23间的学生的姓名,系别及年龄；4.查询年龄不在20到23间的学生的姓名,系别及年龄；5.查询不是信息系(IS)和计算机系(CS)学生的姓名和性别；6.查询所有

16、姓刘的学生的姓名,学号和性别；7.查询姓“李”且名为一个汉字的学生的姓名；8.查询名字中第2个字为“敏”字的学生姓名和学号；9.查询所有不姓刘的学生姓名；10.查询全体学生情况,结果按所在系的号升序排列,同一系中的学生按年龄降序。（三）使用统计函数1.查询学生的总人数；2.查询选修了课程的学生人数；3.计算选2号课程的学生平均成绩；4.查询选修3号课程的学生最高分数；5.求各个课程号及相应的选课人数；6.查询选修了2门以上的课程的学生学号。（四）连接查询1查询每个学生及其选修课程的情况；2查询每一门课的间接先修课(即先修课的先修课)。（五）复合条件连接1.查询选修2号课程且成绩在90分以上（包

17、括90分）的所有学生。（六）嵌套查询1.带有谓词in的子查询<1>查询与“刘晨”在同一个系学习的学生；<2>查询选修了课程名为“信息系统”的学生学号和姓名。2带有any 或all谓词的子查询<1>查询其他系中比信息系中某一学生年龄小的学生姓名和年龄；<2>查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。3带有exist谓词的子查询<1>查询所有选修了1号课程的学生姓名；<2>查询没有选修1号课程的学生姓名。四、实验思考与总结试比较嵌套查询与连接查询的差异。项目三 数据库的完整性 一、实验学习目标1掌握约束的定义及其删

18、除方法。2掌握规则的创建、使用和删除方法。3掌握默认对象的创建、使用和删除方法。二、实验设备微型计算机、SQL Server软件三、实验内容及步骤(一)在StudentCourse数据库的表结构说明中加入完整性约束说明，如表3-1至3-3所示。请用SQL语句完成表中约束的说明。表3-1 加了约束说明后的Student表属性名数据类型可否为空完整性约束SnoChar(5)否主键SnameVarChar(20)否SsexChar(2)否男或女SageSmallint可大于14，小于65SdeptVarChar(20)否表3-2加了约束说明后的Course表属性名数据类型可否为空完整性约束CnoCh

19、ar(1)否主键CnameVarChar(20)否唯一性CpnoChar(1)可CreditSmallint可表3-3加了约束说明后的SC表属性名数据类型可否为空完整性约束SnoChar(5)否主键，外键（参照Student表的Sno），删除级联CnoChar(1)否主键，外键（参照Course表的Cno）GradeDecimal(4,1)可在0和100之间（二）理解规则的概念和作用1创建规则“rule_credit”，用以限制插入该规则所绑定的列中的值只能取1、2、3、4、5、62将“rule_credit”规则绑定到Course表的Credit属性上，并执行以下语句，看看能否正常执行，为什

20、么？Insert into Course Values(8,'数据库课程设计','1',7)3解除Course表的credit属性上的规则绑定。 （三）理解默认值的概念和作用1创建字符默认值“男”，并将该默认值绑定到Student表的Ssex属性上。2解除Student表的Ssex属性上的默认值绑定。3删除该默认值。（四）理解Identity列，复习Insert语句1使用以下语句创建表：Create  table  test( id int  identity(10,10),name char(20) 

21、 Not null,  age tinyint  Null)2执行以下Insert语句，看看哪些能成功执行，哪些不能，为什么？能成功执行的，看看表中生成的数据。 insert  into  test  values(10,'aaa',41) insert  into  test  values('bbb',42) insert  into  test (id,name,age)   values(20,'ccc',43) ins

22、ert  into  test (name,age)   values('ddd',44) insert  into  test (name)   values('eee') insert  into  test (age)   values(46) insert  into  test (name,age)   values('ggg',80)四、实验思考与总结 在Studen

23、tCourse数据库的表中，如果定义好了所有的主键和外键，在学生表中删除一个学生信息，将会发生什么情况？在课程表中删除一门课程信息，会发生什么情况？为什么？ 项目四 存储过程和触发器一、实验学习目标1掌握创建存储过程的方法和步骤；2掌握存储过程的使用方法；3掌握创建触发器的方法和步骤；4掌握触发器的使用方法。二、实验设备微型计算机、SQL Server软件三、实验内容及步骤建立数据库及其如下的表，按要求完成存储过程和触发器的创建。学生表（Student）SnoSnameSsexSageClno95001李勇男209531195002刘晨女199531195003王敏女18953129

24、5004张立男1995312课程表（Course）CnoCnameCpnoCredit1数据库542数学NULL23信息系统144操作系统635数据结构746数据处理NULL27Pascal语言64学生选课成绩表（SC）SnoCnoGrade950011929500128595001388.595002290950023100班级表（Class）ClnoSpecialityNumber95311计算机软件295312计算机应用2(一)创建如下不带参数的简单存储过程查询成绩在60至80分之间的学生的学号和课程号。(二)创建如下的带输入参数的存储过程1、根据用户输入的学号，删除该学生选课成绩记录；

25、2、向SC表中插入数据。(三)创建如下的带输出参数的存储过程，并写出一个执行该过程的例子根据用户输入的学号，课程号，输出其成绩。(四)创建一个名为stu_insert的触发器，当向学生表student中插入记录时，自动更新班级表class中的学生人数number。使用insert into student  values (95005,'李勇','男',20,'95311') 语句验证触发器。四、实验思考与总结存储过程和触发器的作用主要体现在哪些方面？项目五 数据库系统设计一、实验学习目标1了解常用数据库系统的设计内容和方法。2掌握数据

26、库系统设计过程，综合运用所学数据库与程序开发知识，开发一个可用的数据库应用系统3使用Visual Basic编写SQL Server数据库应用程序，完成自设计的数据库应用项目的程序设计、编程和调试工作。二、实验设备微型计算机、SQL Server软件、Visual Basic软件三、实验内容根据周围的实际情况自选一个小型的数据库应用项目并深入到应用项目的现实世界中，进行系统分析和数据库设计。例如选择学籍管理系统、图书管理系统、材料管理系统或仓库管理系统等。四、实验要求及评价标准（一）实验要求要求完成以下设计内容，包括：1系统分析与设计 设计目标，开发设计思想，系统功能分析，系统功能模块设计。2

27、数据库设计 数据库需求分析，数据库概念结构设计，数据库逻辑结构设计 3系统各模块设计 各模块的窗口设计，功能设计 4应用程序实现 运行结果：主要的界面窗口 5设计的改进 对系统中的不足及下一步的改进提出自己的想法和实施的方法（二）实验评价1、系统需求分析报告（30分）系统作出明确、详细的需求分析报告。需求分析为一个项目指明了方向，以后的工作都会根据需求分析进行。需求分析报告至少应该包含以下几部分：(1)系统的名称；(2)系统概述；(3)系统要实现那些功能，每个功能的具体描述。2、概要设计（40分）根据需求分析报告，做出系统概要设计报告。概要设计是承上启下的工作，它根据需求分析，具体设计每个功能

28、的如何实现、数据库结构、确定使用的数据库及开发语言，指导后面的开发工作。概要设计报告至少应该包含一下几部分：(1)系统概述（可参照需求分析的系统概述）；(2)系统各功能模块如何实现（流程，调用的数据库表）；(3)数据库设计（E-R图，数据库表结构）；(4)开发的平台，使用的数据库及开发工具等。 3、系统开发与实现（30分）开发是将概要设计付诸实施，将设计变成一个可演示的数据库应用系统。开发人员要详细阅读概要设计，按照概要设计描述实现每个功能模块，最终形成一个完整的可以演示的数据库应用系统。五、实验思考与总结设计报告实例：学生成绩管理系统的设计与实现一、开发背景当今时代是飞速发展的信息时代。在各

29、行各业中离不开信息处理，这正是计算机被广泛应用于信息管理系统的环境。计算机的最大好处在于利用它能够进行信息管理。使用计算机进行信息控制，不仅提高了工作效率，而且大大的提高了其安全性。尤其对于复杂的信息管理，计算机能够充分发挥它的优越性。管理信息系统是进行信息的采集、存储、加工、维护和使用的系统。它是随着管理科学和技术科学的发展而形成的。学生档案管理系统是一个教育单位不可缺少的部分,它的内容对于学校的决策者和管理者来说都至关重要,所以学生成绩管理系统应该能够为用户提供充足的信息和快捷的查询手段。但一直以来人们使用传统人工的方式管理文件档案，这种管理方式存在着许多缺点,如:效率低、保密性差,另外时

30、间一长,将产生大量的文件和数据,这对于查找、更新和维护都带来了不少的困难。随着科学技术的不断提高,计算机科学日渐成熟,其强大的功能已为人们深刻认识,它已进入人类社会的各个领域并发挥着越来越重要的作用。作为计算机应用的一部分,使用计算机对学生成绩信息进行管理，具有着手工管理所无法比拟的优点.例如:检索迅速、查找方便、可靠性高、存储量大、保密性好、寿命长、成本低等。这些优点能够极大地提高学生档案管理的效率,也是企业的科学化、正规化管理,与世界接轨的重要条件。因此，开发这样一套管理软件成为很有必要的事情,学生成绩管理系统提供了强大的学生成绩管理管理功能，方便管理员对学生成绩等信息的添加.修改.删除.

31、查询.汇总.统计等操作。更适合大型数据库的管理。具体要求：1. 管理员能够方便的对信息进行添加.修改.删除.查询.汇总.统计等操作。2. 可以将数据库发布到网上，进行资源共享。3. 学生可以在自己的权限内对信息进行访问，即使查询相关信息。二、需求分析（一）系统需求 信息社会的高科技，商品经济化的高效益，使计算机的应用已普及到经济和社会生活的各个领域。计算机虽然与人类的关系愈来愈密切，还有人由于计算机操作不方便继续用手工劳动。为了适应现代社会人们高度强烈的时间观念，学籍管理系统软件为教学办公室带来了极大的方便。该软件是以汉语编程语言为实现语言，其功能在系统内部有源代码直接完成。通过操作手册，使用

32、者可以了解本软件的基本工作原理。操作人员只需输入一些简单的汉字、数字，即可达到自己的目标。（二）功能需求学生成绩管理系统主要提供方便高效的管理功能以及网上的信息查阅平台，学生可以通过该系统查阅相关信息，管理员可以管理所有信息。对于本系统，我们需要实现以下一些基本功能：1学生查询功能：为了方便学生查找成绩等信息，将所有信息按照需要进行分类。这样学生就能很方便的找到自己需要的信息。2添加功能：管理员可以通过填写表格的形式输入学生成绩等相关信息。系统可以自动避免重复信息。3修改功能：管理员可以对数据库中的信息进行修改。系统能够通过管理员给出的条件查找出所要修改的信息，对修改后的信息进行保存，并自动查

33、找是否是重复信息。4删除功能：管理员可以对数据进行删除操作。系统能够通过管理员给出的条件查找出要删除的信息，并提示是否确定删除，如果确定删除，则把相关信息从数据库中删除掉。 5管理员查询功能：管理员可以通过条件选择查询所有信息，并进行排序。6汇总功能：管理员可以通过此功能对信息进行汇总。7统计功能：管理员可以通过此功能对信息进行统计。如统计不及格名单等。（三）业务流程图系统对信息进行处理 输入信 息用户获取信息三、总体设计（一）系统数据流程图(1)顶层图2处理后的信 息 1信息处 理管 理 员或 学 生管理员 信息 信息 信息 信息 D 学生成绩信息 (2) 1层图12处理信 息11接收信 息

34、管 理员 信息 信息 信息 D 学生成绩信息 信息 22显示输 出21接收处理后的信 息管理员 或 学 生 信息 信息 (二)系统层次模块图步骤1：学 生 成 绩 管理 系 统接 收 输 入 信 息对 信 息 进 行 处理输 出 处 理后 的 信 息步骤2：接 收 输 入 信 息根 据 用 户 的 需 求 调 用 相 应 的 功 能 模 块对 信 息 进 行 处 理汇总相应的信息统计相应的信息删除相应的信 息修改相应的信 息将 输 入的 数 据添 加 到数 据 库 中输 出 处 理 后 的 信 息显示数据库中的相关 信 息显示操作完成 的 情况步骤3：输 出 处 理后 的 信 息对 信 息 进

35、 行 处理学 生 成 绩 管理 系 统接 收 输 入 信 息显示数据库中的相关 信 息显示操作完成 的 情况根 据 用 户 的 需 求 调 用 相 应 的 功 能 模 块将 输 入的 数 据添 加 到数 据 库 中修改相应的信 息汇总相应的信息统计相应的信息删除相应的信 息“学生成绩管理信息系统”主要分为学生浏览和后台管理两个子系统，其功能模块划分如下图所示.显 示查 询添 加学生成绩管理系统修 改学生浏览后台管理删 除统 计汇 总 “学生成绩管理系统”系统主模块功能图（四）模块设计1. 用户登录模块：填写已分配的用户名称，填写正确的密码，进入主控制页面。2. 显示模块：显示要求的内容。3. 查询模块：提供多种查询条件，可按需要进行查询。4. 添加模块：向数据库中添加记录。5. 修改模块：可以找到指定信息并对其进行修改。6. 删除模块：找到要删除的记录，并将其删除。7. 统计模块：对数据库中的信息进行统计。8汇总模块：对数据库中的信息进行汇总。四、数据库设计(一)数据库的逻辑设计根据系统功能设计的要求以及功能模块的划分，对于系统信息数据库，可以列出以下数据项和数据结构：1 用户信息表用户信息表包括用户名、用户密码、用户类型四个字段。用户信息表如表5-1所示。表5-1 用户信息表名称字段名称数据类型主键非空用户名name文本YesYes用户密码passwd文本NoNo用户级别typ