《数据库系统概论》实验指导书.docx

数据库系统概论实验指导书实验一 数据库服务器的连接及数据库的建立.1实验二简单SQL查询及数据库多表查询12实验三 视图、索引、存储过程和触发器的使用.18实验四E-R模型与关系模型的转换27实验一 数据库服务器的连接及数据库的建立1.1实验名称：数据库服务器的连接及数据库的建立1.2实验类型：验证型1.3实验学时：3h1.4实验目的：了解连接数据库服务器的身份验证模式，熟悉样例数据库。掌握DBMS中利 用界面进行建库建表操作。1.5实验准备：1.5.1数据模型数据模型由三个要素组成：数据结构、数据操作和完整性约束。1、数据结构数据结构用于描述系统的静态特性，是所研究的对象类型的集合。数据模型 按其数据结构分为层次模型、网状模型和关系模型。2、数据操作数据操作用于描述系统的动态特性，是指对数据库中各种对象的实例允许执 行的操作的集合，包括操作及有关的操作集合。3、数据的约束条件数据的约束条件是一组完整性规则的集合。完整性规则是给定的数据及其联 系所具有的制约和存储规则，用以限定符合数据库状态以及状态的变化，以保证 数据的正确、有效和相容。1.5.2数据库系统的三级模式结构数据库系统的三级模式结构是指数据库系统是由外模式、模式和内模式三级 组成。1、外模式。外模式也称子模式或用户模式，它是数据库用户（包括应用程 序员和最终用户）看见和使用的局部数据的逻辑结构和特征的描述，是数据库用 户的数据视图，是与某一应用有关的数据的逻辑表示。一个数据库可以有多个外 模式。2、模式。模式也称逻辑模式，是数据库中全体数据的逻辑结构和特征的描 述，是所有用户的公用数据视图。一个数据库只有一个模式。3、内模式。内模式也称存储模式，它是数据物理和存储结构的描述，是数 据在数据库内部的表示方式。一个数据库只有一个内模式。1.5.3 DBMS的功能1、数据定义数据定义包括定义构成数据库结构的外模式、模式和内模式，定义各个外模 式与模式之间的映射，定义模式与内模式之间的映射，定义有关的约束条件（例 如，为保证数据库中数据具有正确语义而定义的完整性规则，为保证数据库安全 而定义的用户口令和存取权限等）。2、数据操纵数据操纵包括对数据库数据的检索、插入、修改和删除等基本操作。3、数据库运行管理对数据库的运行进行管理是DBMS运行时的核心部分，包括对数据库进行并 发控制、安全性检查、完整性约束条件的检查和执行、数据库的内部维护（如索 引、数据字典的自动维护）等。所有访问数据库的操作都要在这些控制程序的统 一管理下进行，以保证数据的安全性、完整性、一致性以及多用户对数据库的并 发使用。4、数据组织、存储和管理数据库中需要存放多种数据，如数据字典、用户数据、存取路径等，DBMS 负责分门别类地组织、存储和管理这些数据，确定以何种文件结构和存取方式物 理地组织这些数据，如何实现数据之间的联系，以便提高存储空间利用率以及提 高随机查找、顺序查找、增、删、改等操作的时间效率。5、数据库的建立和维护建立数据库包括数据库初始数据的输入与数据转换等。维护数据库包括数据 库的转储与恢复、数据库的重组织与重构造、性能的监视与分析等。6、数据通信接口DBMS需要提供与其他软件系统进行通信的功能。例如，提供与其他DBMS或 文件系统的接口，从而能够将数据转换为另一个DBMS或文件系统能够接受的格 式，或者接收其他DBMS或文件系统的数据。1.6实验内容1.6.1熟悉网络环境和数据库环境数据库服务器：操作系统：Windows 2000 Advanced ServerIP 地址：192.168.0.99数据库管理系统：Microsoft SQL Server 2000服务器名称：servernet客户机：操作系统：Windows 2000 ProfessionalIP 地址：192.168.0.XMicrosoft SQL Server 2000 客户端启动 Microsoft SQL Server 2000 查询分析器：1、开始-> 程序->Microsoft SQL Server 2000->查询分析器出现如下画面2、连接到 Microsoft SQL Server2000SQL Server： servernet(或 192.168.0.99)选择SQL Server身份验证；登录名：sa 密码：无2、创建数据库sql语句:.r执行结果:3、身份验证模式：在安装Sql Server 2000过程中，出现身份验证对诂框， 提示选择身份验证模式。Windows身份验证模式或混合模式（Windows身份验证 和SqlServer身份验证）并提示添加sa用户登录密码（一般选中空密码）。安 装完SQL后，打开企业管理器，逐级展开节点到安全性|登录，在右边视图中 可以看到登录的用户名称，右击sa用户，打开属性，弹出属性对话框，可以看到当前用户的身份验证模式。4、体会DBMS的三层模式结构：（以理解为主）从数据库管理系统角度看， 数据库描述由三级抽象模式组成：概念模式（逻辑模式）、物理模式（内模式） 和外模式；从数据库最终用户角度看，数据库系统的结构分为单用户结构、主从 式结构、分布式结构和客户/服务器结构。概念模式体会：打开任意所建立数据 库，选择一个表，右击设计表，打开设计表框。这里描述了存储的数据 的属性和实体及实体关系。物理模式：描述存储细节。外模式：视图和来自概念 模式的关系组成。5、安装Sql Server 2000后，安装程序将在所创建的数据库实例中创建数据 库和日志文件。Master、Model、Msdb和Tempdb都是系统数据库。其中Master 主要用于通过跟踪诸如用户帐户、可配置环境变量和系统错误信息，控制用户数 据库和SQL Server的整体运行情况；Model主要用于为新的用户数据库提供模 板或原型；Msdb主要用于为调度信息和作业历史提供存储区域；Tempdb主要用 于为临时表或其他临时工作区提供存储区域。Pubs和Northwind示例数据库作 为学习工具提供。我们提供基于样例数据库Northwind的一些操作。打开企业 管理器|数据库Northwind。Northwind示例数据库包含了一个名为 Northwind Traders的虚构公司的销售数据，打开表，可以看到该数据库包括 了 Categories、Customers 等 13 张用户表和 Alphabetical list product、 Category Scale for 1997等16张视图。可以任意打开用户表查看信息。1.6.2数据库、表的建立1.6.2.1数据字典（一）表名（具体建表时，首先创建自己的数据库（数据库已经建立按班级分别kt773-1和kt773-2），表名用班级号学号姓名表名的方式命名，如kt1_01_张三.department，学生学号一律使用2位数）表名含义Department院系Class班级Student学生Course课程Grade成绩（二）表结构1、 Department表 1-1 Department 表结构字段名数据类型字段含义约束Dept_IDDecimal院系编号Primary KeyDepartmentvarChar(50)院系名称Not NullAbbreviationvarChar(10)院系简称PhonevarChar(50)联系电话DirectorvarChar(50)院系主任No_ClassDecimal班级数Not Null Default 02、Class表1-2 Class表结构字段名数据类型字段含义约束Class_IDChar(7)班级编号Primary Key字段名数据类型字段含义约束Dept_IDDecimal院系编号Foreign KeyClassvarChar(30)班级名称Not NullAbbreviationvarChar(20)班级简称MonitorvarChar(24)班长DegreevarChar(4)学历层次Not Null大专、本科、硕士、博士Enroll_DateDecimal入学年度Not NullNo_StudentDecimal学生数Not Null Default 03、Student表1-3 Student表结构字段名数据类型字段含义约束Student_IDvarChar(9)学号Primary KeyClass_IDChar(7)班级编号Foreign KeyNamevarChar(24)姓名Not NullGendervarChar(2)性别男，女BirthdayDatetime出生日期AddressvarChar(50)家庭地址Zip_CodeDecimal家庭邮编HouseholderVarchar(50家长姓名4、 Course表1-4 Course表结构字段名数据类型字段含义约束Course_IDvarChar(8)课程编号Primary KeyCoursevarChar(50)课程Not NullCreditDecimal学分PeriodDecimal学期Practice_periodDecimal实习5、 Grade表1-5 Grade表结构字段名数据类型字段含义约束Student_IDvarChar(9)学号Foreign KeyPrimary KeyCourse_IDvarChar(8)课程编号Foreign KeyGradeDecimal成绩Not Null1. 6. 2. 2创建数据库1.打开企业管理器：开始-程序-Microsoft SQL Server 2000-企业管理器出现如下画面:Microsoft Excel打开Office文档Microsoft Visia TrialMicrosoft Word活华TH-OCR 2000千禧专业版设定程序访问和默认值文档仍设置搜索(Q帮助(由运行(£)，I i Microsoft 5QL Server关机,History SweeperInternet ExplorerMicrosoft AccessMicrosoft FrontpageMicrosoft PowerPointOutlook ExpressRealPlayerWindows Media Player附件Adobe Reader 6.0IE汽车工 美院专IE汽车工 美院专快捷方式BitComet唠 Microsoft SQL Server 版本切换 Windows Update新建Office文档5053-日-4 高国庆毕业查询分析嚣导入和导出数据服备管理嚣服务器网络实用工具客户浦网络实用工具 联机丛书备企业管理器?事件探查器I位置:C:WI理丽；在115中配图 1-1 启动 Microsoft SQL Server 2000 企业管理器图 1-2 打开 Microsoft SQL Server 2000 企业管理器2.添加新表:(1)方法一新建表l=l sysindexes sysindexke JsysmemberII所有任务(厘查看俄从这里创建窗口 (如刷新旧导出列表帮助ssirtict;jm图1-3-1添加新表(2)方法二< SQL查询分析器文件貌辑查询顶）TM （T）窗口地）帮助但） 由，渗口回|S昭鑫舀#4|E 口，操作（必查看（W 工具|七 1囱函| X曾函医留44 1Ck ® ®备2:遗学管理呻C在"Seal广上，的新表表2。个项目树|一控制台4白卸Mien白寸白£create table kt l_pl_弓长三_d巳口artmerrt (.Depaid cliar (40.) primary key .);3 .定义表结构:耐国|蹑|E着史| ¥ |断窖叫融I确或回列名数据类型允许空1描述默认值精度小数位数标识标识种子标识递增量是 RowGai d公式排序规则图1-4定义表结构4. 定义表之间的关系和关系图Tiii SQL Server Enterprise Manager - 2:"况湘玲'仲(在''SERVERNETU )的新关系图雀控制台© 窗口(必帮助(M9旨昌|鱼堕里里也)Q眉|早alb留| 卧上图1-5定义关系图5.输入表中的记录：(每个表最少5条记录，其中一条是与自己相关的真实记录)控制台根目录MicrosoFt SQL Servers-'J SQL Server 蛆 白务(local) (Windows NT) a邈据库E- (J masterE-国 modelE- Q msdbE- Q Northwindj E-目 pubsE- (j tempdbI & (j教学首理瑁关系圈二表"阳' 视图存储过程I-®用户京角色.!规则-s默认1最用尸定受的数拒L-用户定义的函娄亩数据转换服荟E O管理E U复制s-Q安全性由支持服芸由白 Meta Data Services5.添加记录:名称/|所有者I创建日期ClassEH dtproperties syscolumns sys 匚 口11巳止5 sysdepends sysfilegroups冒 sysFiles sysfilesl sysforeiqnkeys sysfulltextcataloqs sysfulltextnotify sysindexes sysindexkeys sysmembers sysobiects syspermissions sysproperties sysprotects sysreFerences systypes sysusers新建表(蜀设计表(公打开表(0)全史索引表® »所有任务(於复制©删除(Q)重命名(吵尾性冒帮助(U)dbodbodbodbodbodbodbo用户2006-9-19系毓2006-9-19返回首行(!).，.查询(0)0-8-6 10-8-6 10-8-6 116:43:13统统统统统统统统统统统统统统统 系系系系系系系系系系系系系系系2000-8-6 12000-8-6 12000-8-6 12000-8-6 12000-8-6 12000-8-6 12000-8-6 12000-8-6 12000-8-6 12000-8-6 12000-8-6 12000-8-6 12000-8-6 12000-8-6 12000-8-6 116:42:0929:1229:1229:1229:1229:1229:1229:1229:1229:1229:1229:1229:1229:1229:1229:1229:1229:1229:1229:12图1-6打开表1.7思考题：1、从用户角度看，数据库系统都有哪些体系结构?2、数据库管理系统通常由哪几部分组成？3、如何用SQL语句完成上述的工作？实验二简单SQL查询及数据库多表查询2.1实验名称：简单SQL查询及数据库多表查询2.2实验类型：验证型2.3实验学时：3h2.4实验目的：通过本章的学习，用户将能够：1. 掌握向表中添加数据的方法2. 掌握如何快速的把一个表中的多行数据插入到另一个表中3. 掌握更新表中多行数据的方法。4. 学会如何删除表中的一行数据5. 学会如何删除表中的所有行6. 掌握SELECT语句的基本用法7. 使用WHERE子句进行有条件的查询8. 掌握使用IN, NOT IN, BETWEEN来缩小查询范围的方法9. 利用LIKE子句实现模糊查询，利用ORDER子句为结果排序10. 学会用SQL SERVER的统计函数进行统计计算11. 掌握用GROUP BY子句实现分组查询的方法。12 .掌握多表查询的技术和嵌套查询的方法。2.5实验准备：1、常用的统计函数：sum()返回一个数字或计算列的总和avg()对一个数字列或计算求列平均值min()返回一个数字或一个数字表达式的最小值max()返回一个数字或一个数字表达式的最大值count()返回满足SELECT语句中指定条件的记录的值 count(*)返回找到的行数注意：对于以下类型的数据values值不用使用引号1、整型数据类型：bit,int,smallint,tinyint2、货币数据类型：money,smallmoney3、数字数据类型：decimal,numeric4、浮点类型数据：real,float5、简单的日期/时间类型数据：datetime,smalldatetime对于以下数据类型就要对values值加单引号1、字符数据类型：char,varchar,text 2、复杂的日期/时间类型数据：datetime,smalldatetime2、常用SQL语句语法添加语句：INSERT INTO table_or_view (column_list) data_values删除语句：DELETE FROM table_name WHERE search_condition 更新语句：UPDATE table_nameSET column_name = ( expression | DEFAULT | NULL ,.n FROM table_name ,.n WHERE searchcondition查询语句：SELECT select_listFROM table_sourceWHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC 2.6实验内容：2.6.1 操作 一 :INSERT（插入）1、首先检查表结构：sp_help Class sp_help department sp_help grade sp_help student sp_help course|图2-1输入检查表结构的语句注意:（1）表名称不能以数字开头（2）sp_help语句只能一次执行一句，不能批量执行2、单条语句插入样例:表dep art ment插入样例insert into departmentvalues （11 J计簸机及信息工程','计算机','8762898?,'张太红',22表cl a即插入样例fj r十 m十门 c 1日耳gvalues 0446312?, 11,'信管044111,11, ,本科?, 2004 22 表st udent插入J羊例 insert into studentvalues 04463120T/ 0446312?/王泊男1981-03-1 T , null, null, null） 表course插入样例 insert into coursevalues 261 10033?/fff, 3 2 5>表gr a如插入样例insert into gradevalues 044631201,/ 26110033 85请同学们自行练习使用单条SQL语句给自己的各个表进行插入图2-2各表插入语句样例2.6.2 操作二：UPDATE （更新）1、在 student 表中，GENDER （性别），BIRTHDAY （出生日期），ADDRESS （家庭地 址），ZIP_CODE （家庭邮编），HOUSEHOLDER （家长姓名）均不正确或为空值， 请将自己以上的准确信息更新到自己的数据库中，同时更新到同班同学的其 它数据库中。注意更新前要看相关表中该条数据是否正确添加。updat studentset 写晶讪r=男BIRTHDAY , 820204?, ADDRESS 山东省flP_CODE=, 899999', HOUSEHOLDER :'王from student where student_id 994631227,"1STUDENT.IDCLASS_IDNAMEGENDERBIRTHDAYADDRESSZIP_CODEHO.19946312279946312 堇征男1983 山东省899999 王图2-3用update更新student表中自己的信息2、查询你的数据库中同班其他同学的信息是否已更新。3、执行以下更新在department表中，no_class （班级数）均不正确或为空值，请根据class 表中每个学院实有班级数目更新department表中的no_class，例如：Iupdate departmentset no_class=32 from department where dept_id=l1|select * from department where dept_id 114、执行以下更新在class表中，no_student （学生数）均不正确或为空值，请根据student表中 每个班级实有学生人数更新class表中的no_student。注意：请将class表中 的abbreviation属性各记录内容补充完整。update classset no_student 班级人数select abtirgiaticm'班级班级人数二口口皿七（*）from student as s , class as cwhere c class_id s class_id group by abbreviation as dwhere class abbreviation d 班级2.6.3操作三：DELETE （删除）请从student表中删除一个student_id='994631225'的记录（注意：确保student表和grade表中有与学生994631225有关的数据）：为什么会出现以下的结果？delete student where student id=? 994631225,服务器：消息级别16,状态1，行1DELETE 语句与 COLUMN REFERENCE 约束FK_GRADE_STUDENT'冲突。 该冲突发生干数据库STUDENT',表GRADE', column ' STUDENT. ID'° 倍句已终止。一图2-4删除student表中的一条记录假若删除的是你自己的学号可以吗？为什么？2.6.4操作四：SELECT （查询）1.掌握SELECT语句的基本用法select * frem select * f select * frem select * from select * fromilspartHieri't class student course grade图2-5 SELECT语句的基本用法2、查询你的各个表中所有的记录数目selectselectselectselectselectcount (*) count (*) count (*) count (*) count (*)from department from class from student from course from grade图2-6 SELECT语句中count （）函数的用法3、查询平均成绩(用avg()函数)I SELECT avg grade) ?平均成绩，FROM grade WHERE student_id=, 994631225,图2-7 SELECT语句中avg ()函数的用法4、查询某班级所有同学的平均成绩，查询输出以别名：学号，姓名，平均成绩;并按平均成绩由高到低排序(用到order by)SELECT student student_id student. namej avg 1 grade '平均或绩'FROM stTident gradewhere student student_id grade student_id and student class_id=, 9946312, group by student student_id student, nameorder by avg grade desc图2-8 SELECT语句中函数以及复杂子句的用法5、查询没有成绩记录的学生，查询输出：学号，姓名、的在班级简称。| SELECT student student_id student, namej class abbreviationFROM student class where student class_id class class_idand student class_id=? 9946312,and student_id not in (select student_id from grade图2-9 SELECT语句中not in的用法6、查询某学生的各课成绩(注意确保该学生有课程成绩)，结果格式如下：I SELECT grade course_id, course course grade FROM course grade where course course_id=grade course_id and grade student_id=, 994631227?图2-10用SELECT语句查询自己的成绩7、查询某班两门及两门以上课程不及格的学生，查询输出：学号，姓名、不及 格课程数量(注意保证有满足条件的记录)。I SELECT studeYit student_id student namej b cn FROM student select student_id count(*)as cn from grade where grade<60 group by student_id having count (*)； 1) b where student student_id b student_id and student class_id :' 9946312,图2-11用SELECT语句查询多门课程不及格的学生8、查询某班男生人数。select count(*) from student where student class_id：, 0446312, and student gender 男图2-12用SELECT语句查询男生人数9、查询某班女生人数。select count(*) from student where student. class_id=, 0446312, and student gender=,图2-13用SELECT语句查询女生人数10、查询班级Class_ID like '04%'的班级。select * from class where class_id like ' 04%,图2-14用SELECT语句查询班级编号11、请查询输出：班级编号，班级名称，男生，女生。SELECT C Class_id as 班级编号 C ABBREVIATION as 班级名称 SUM (CASE S GENDER WHEN '勇'THEN 1 ELSE 0 END) AS 勇 SUMtCAi S GENDER WHEN，女，THEN 1 ELSE 0 END) AS 女 FROM Student AS s Class AS cWERE S Class_ID C Class_IDGROUP BY C Class_ID C ABBREVIATION图2-15 SELECT语句中CASE语句的用法112、请查询输出：班级编号，班级名称，男生，女生，合计。SELECT sc me 男 + me 女)AS 合计FROM (SELECT C Class_id as 班级编号 C ABBREVIATION as 班级名称SUM (CASE S GENDER WHEN '男THEN 1 ELSE 0 END) AS 男，SUM (CASE S GENDER WHEN '女THEN 1 ELSE 0 END) AS 女FROM Student AS s Clas? AS c WHERE S Class_ID C Class,ID GROUP BY C Class_ID C ABBREVIATION ) AS SC图2-16 SELECT语句中CASE语句的用法213、执行以下查询，解释该查询的含义I select department nc_class c na_class fram department select dept_id count (*) as no_cLa3s from class group by dept_id c wh已r已 department dept_id c dept_id and d已partnient d已pt_id 11图2-17解释SELECT语句2.7思考题1. 如何使用UNION子句，它必须符合哪两条基本准则？2. 怎样把STUDENT表中前10个数据ADDRESS列的数据更改为新疆。实验三 视图、索引、存储过程和触发器的使用3.1实验名称：视图、索引、存储过程和触发器的使用3.2实验类型：验证型3.3实验学时：3h3.4实验目的：1. 掌握视图的概念和使用方法；2. 掌握索引的概念和使用方法；2. 掌握存储过程的概念和使用方法；3. 掌握触发器的概念和使用方法；3.5实验准备：1. 了解视图的概念和作用；2. 掌握视图相关的命令；3. 了解索引的作用与分类；4. 掌握索引的创建方法；5. 理解数据完整性的概念及分类；6. 了解各种数据完整性的实现方法；7. 了解存储过程的使用方法；8. 掌握存储过程的调用方法；9. 了解触发器的使用方法；3.6实验内容：3.6.1视图1. 创建视图(1)在STUDENT表中，为信息管理及信息系统022班建立视图V1_STU,在查询分析 器中输入下列语句：图3-1创建视图V1_STU思考与练习：1) 在STUDENT表中，为计算机科学与技术021建立视图SV1_STU。2) 在TUITION表中，为第五学年缴全额学费(3500)的学生建立视图SV2_TUI。(2)创建V3_STU_COU视图，包括学号，课程号,课程名，成绩，要保证对该视图的修 改都要符合班为信息管理及信息系统022班这个条件：ni：" Sii 202.201.224.123.student.sa 1 +CREATE VIEW V3_STU_C0U WITH ENCRYPTION卷加密"ZASSELECT STUDENT STUDENT_ID COURSE COURSE_ID COURSE GRADEFROM STUDENT GRADE, COURSEWHERE STUDENT STUDENT_ID GRADE STUDENTD ANDGRADE COURSE_ID COURSE COURSE ID AND CLASS_ID =J 024631/WITH CHECK OPTION春修改时符合为信息管理及信息系统。黑班条件计"I"令已成功完成n图3-2创建视图V3_STU_COU思考与练习：在STUDENT和CLASS两表中，建立视图SV3_STU_CLA,包含学号，班号，学院号，姓 名，性别。2. 查询视图(1)查找信息管理及信息系统021班女生的学号和出生日期：图3-3创建并查询视图V2_STU思考与练习：1）查找SV3_STU_CLA视图中学号为024631416所在的班号和学院号。2）查找信管992班平均成绩在80分以上的学生的学号和平均分数：图3-4创建并查询视图V4_AVG 思考与练习：统计信管992班平均成绩在80分以上的学生的个数。3. 更新视图可更新视图满足以