数据库原理实验指导书-本科.doc

数据库原理及应用实验指导材料（本科） 温州大学城市学院一、实验内容：实验共7次，每次2学时，期末一次实验考试1、 数据库的可视化操作2、 单表查询3、 连接查询和嵌套查询4、 综合查询5、 用SQL实现数据库的建立与维护6、 存储过程和触发器7、 数据库保护实验考试题目由学生抽签决定二、实验成绩实验成绩由实验操作成绩、实验报告成绩及最后的实验考试组成三、实验要求实验准备：每次实验的内容提前公布（但不提供完整的答案及具体的实验操作步骤），因此要求学生在做实验之前提前准备实验内容。实验：实验时所有的任务必须当场完成，不能将事先完成的数据库拷贝过来作为实验成果，也不能将其他同学的数据库拷贝过来作为实验成果，更不能请其他同学代为完成。实验测评：学生觉得实验各项内容均已完成，而且自信也比较熟练，可以请老师测评。老师主要测评以下几方面：各项任务是否当场独立完成；熟练程度如何，对实验中观察到的现象能否合理解释。老师测评后将当场给出本次实验的成绩。实验报告：在实验做完的下一周交主讲老师，实验报告成绩由主讲老师或实验老师根据评分标准给出。实验一 数据库的可视化操作一、实验目的：1、 了解SQL Server2000的主要功能，并熟悉SQL Server的启动方法2、 掌握SQL Server2000的企业管理器的基本使用方法3、 掌握用可视化的方法实现数据库建立、建表、定义约束，修改表结构等操作4、 掌握用可视化的方法实现数据库表中数据的增加、删除、修改等操作5、 进一步理解数据库的实体完整性、参照完整性、自定义完整性约束条件的作用二、实验内容： 1. 启动SQL Server 2000，进入企业管理器2. 建立图书读者数据库，并存于D盘自建的目录3. 建立图书表、读者表、借阅表，表结构如下： 图书（书号，类别，出版社，书名）读者（读者编号，姓名，单位，性别，电话）借阅（书号，读者编号，借阅日期，归还日期）4. 上述各表属性类型及宽度自定（要求为属性选择合适的数据类型，长度，是否为空和缺省值等内容），定义每个表的主码（上述表中划线的属性）。5. 给“图书”表增加“价格，作者，出版日期”3个属性，其中价格类型为数值型，小数位数是2位，作者为字符型，出版日期为日期型。6. 定义约束：实现读者性别只能是”男”或”女”的检查约束，实现图书的价格不能为负数的检查约束，借阅表与图书表、借阅表与读者表的外码约束，即实现借阅表中的参照完整性约束。7. 分别在三个表中输入不少于5条记录的数据，内容自定，输入数据时观察实体完整性、参照完整性、自定义完整性约束的效果。8. 分别对三个表中的数据进行删除，修改操作，修改数据时观察约束的效果。三、思考题：1、 图书表中的书号和读者表中的读者编号能否为数值型、能否为空值，为什么？2、 对图书表中的数据分别进行增加、删除、修改操作时要注意什么？3、 对借阅表中的数据分别进行增加、删除、修改操作时要注意什么？4、 定义外码的作用是什么？要求以上问题回答在实验报告中实验二 单表查询一、实验目的：1、掌握用SQL对数据库中的数据进行简单查询操作2、掌握SQL Server Query Analyzer（查询分析器）的使用方法二、实验内容： 1、将教师机上的两个文件（工程零件_Data.MDF和工程零件_Log.LDF）拷贝至自己机器的D盘自建的目录中该数据库的表结构如下：供应商（供应商代号，姓名，所在城市，联系电话）零件（零件代号，零件名，规格，产地，颜色）工程（工程代号，工程名，负责人，预算）供应零件（供应商代号，工程代号，零件代号，数量，供货日期）2、 将两个物理文件联接到数据库管理系统中，方法如下：1）进入MS-SQL SERVER 2000企业管理器2）在数据库栏右击鼠标，选择所有任务，选择附加数据库3）在本机自建目录下选择工程零件_Data.MDF文件4）确定（开始附加数据库），即可3、 查询供应商王平的基本信息4、 找出天津供应商的姓名和电话5、 查询所有姓王的供应商的姓名，电话，所在城市6、 查找预算在50000-100000元之间的工程的信息，并要求查询结果按预算的降序排列7、 找出使用了供应商代号为s1的供应商所供应的零件代号为p3的工程的工程代号8、 查询供应商代号为s1的供应商在2001年以后的供货情况，包括零件代号、数量、供货日期9、 查询供应商总人数10、 查询供应商代号为s1供应商的供货次数11、 找出工程代号为j2使用的各种零件的零件代号及数量三、实验要求 要求记录运行的SQL语句及运行结果，并能分析和解释实验现象及结果四、思考题：1、 第5题如果改为：查询所有姓王的且为单名的供应商的姓名，电话，所在城市，则如何操作？2、 第10题如改为：查询供货的供应商总人数，则该如何操作？要求以上问题回答在实验报告中实验三 连接查询和嵌套查询一、实验目的：1、 掌握用SQL对数据库多表数据查询2、 进一步理解连接查询方法和嵌套查询方法的实际意义2、进一步熟悉SQL Server2000的查询分析器和企业管理器的使用方法二、实验内容： 1、 启动SQL Server 2000的企业管理器，按实验二的实验步骤1-2将”工程零件”附加到SQL Server中该数据库的表结构如下：供应商（供应商代号，姓名，所在城市，联系电话）零件（零件代号，零件名，规格，产地，颜色）工程（工程代号，工程名，负责人，预算）供应零件（供应商代号，工程代号，零件代号，数量，供货日期）2、 查询使用了零件代号为p3的零件的工程代号、工程名3、 查询使用了上海供应商供应的零件的工程代号4、 查询使用了蓝色零件的工程的工程代号（要求分别用连接查询和嵌套查询两种方法）5、 查询姓”王”的供应商的供货信息，包括零件代号、零件名、数量、供货日期6、 查询没有使用天津产的零件的工程代号7、 找出供应商代号为s1的供应商所供应的零件的工程的负责人8、 查询工程代号为j3所使用的零件代号、零件名9、 查询使用了全部零件的工程的工程代号、工程名10、 查询使用零件数量最多的工程的工程代号、工程名三、实验要求1、 以上各题除有说明，查询方法可以任选2、 要求记录运行的SQL语句及运行结果，并能分析和解释实验现象及结果四、思考题：（要求以下问题回答在实验报告中）1、 第3题如果用连接查询是否需要加distinct ,为什么？2、 第6题能否用如下语句实现？为什么？Select distinct 工程代号 from 供应零件,零件 where 零件.零件代号=供应零件.零件代号and 产地天津2、第6题能否用如下语句实现？为什么？Select distinct工程代号 from 供应零件 where 零件代号 not in (Select 零件代号 from 零件 where 产地=天津)实验四 综合查询一、实验目的：1、掌握用SQL对数据库中的数据进行各种不同要求的查询2、进一步理解SELECT语句各子句的含义及用法3、提高用SQL的查询语句解决实际问题的能力二、实验内容：1、启动SQL Server 2000的企业管理器，按实验二的实验步骤1-2将”工程零件”附加到SQL Server中该数据库的表结构如下：供应商（供应商代号，姓名，所在城市，联系电话）零件（零件代号，零件名，规格，产地，颜色）工程（工程代号，工程名，负责人，预算）供应零件（供应商代号，工程代号，零件代号，数量，供货日期）2、用SQL语句进行操作，解决以下问题：（1） 列出每个供应商代号以及分别给几个工程提供了零件，结果用视图的形式给出。（2） 查询所在地在“上海”的供应商姓名及电话，结果用视图的形式给出。（3） 哪些工程使用了蓝色的零件？列出工程名，结果用视图的形式给出。（4） 供应商S3发现已供应的所有零件存在质量问题，想找各工程负责人商议补救办法。请列出相关的工程负责人名单。（5） 哪些供应商没有给工程J2供应零件？请列出供应商名。（6） 每一个工程总共用了多少个零件？请列出工程代号，零件总数量。（7） “胡胜利”（供应商）最近一次供货的时间是什么时候？是供应给哪个工程号？（8） 哪个供应商给全部工程提供了零件，请列出供应商代号（9） 哪一个供应商供应的零件数最多？请列出供应商名（10） 哪一个供应商一次供应的零件数最多？请列出供应商名（11） 哪一个供应商供应的零件种类最多？请列出供应商名三、实验要求：1、要求记录运行的SQL语句及运行结果，并能分析和解释实验现象四、思考题：（要求以下问题回答在实验报告中）1、第6题如果改为：每一个工程总共用了多少种零件？该如何操作？2、请描述where和having的不同点 实验五 用SQL实现数据库的建立与维护一、实验目的：1、掌握SQL的数据定义功能2、进一步掌握SQL Server Query Analyzer（查询分析器）的使用方法二、实验内容： 1、启动SQL Server 2000，进入企业管理器2、建立一个数据库，数据库名为自己的姓名，并存于自建的目录中（此操作可用可视化操作）3、进入查询分析器，用SQL建立学生表、课程表、选课表，表结构如下：学生（学号，姓名，性别，出生日期）课程（课程号，课程名称，学分）选课（学号，课程号，成绩）上述各表的属性类型及宽度自定（要求为属性选择合适的数据类型，长度，并且姓名不能为空，性别的缺省值为”男”，成绩为0-100），定义每个表的主码，主码为划线的属性，并将选课表中的学号与学生表中的学号建立关联，选课表中的课程号与课程表中的课程号建立关联。3、 用SQL语句在“学生”表中增加“电话”属性，类型、长度自定。4、 将课程表中的课程名称属性改为字符型，长度为20（原来长度不是20）。5、 用语句分别在三个表中各输入不少于3条记录的数据，内容为自已及同学的信息。6、 用语句实现每门课的学分加1分。7、 按学生的出生日期的降序对学生表建立索引，索引文件名为学生_出生日期。8、 建立平均成绩表（学号，平均成绩），内容为每个学生的平均成绩。9、 删除自己的所有信息（包括选课信息）。三、实验要求1、要求记录运行的SQL语句及运行结果，并能分析和解释实验现象四、思考题：（要求以下问题回答在实验报告中）1、 第9题能否先删除学生表中的信息，再删除选课表中的信息？如果不能，请问为什么不能？2、 修改表中属性的类型和长度时要注意什么问题？实验六 存储过程和触发器一、实验目的：1、 进一步理解存储过程和触发器的概念、作用，并且理解两者的异同点2、 掌握存储过程和触发器的建立和使用方法3、 掌握通过应用存储过程和触发器解决实际问题的基本方法二、实验内容：1、启动SQL Server 2000的企业管理器，按实验二的实验步骤1-2将”工程零件”附加到SQL Server中该数据库的表结构如下：供应商（供应商代号，姓名，所在城市，联系电话）零件（零件代号，零件名，规格，产地，颜色）工程（工程代号，工程名，负责人，预算）供应零件（供应商代号，工程代号，零件代号，数量，供货日期）2、建立如下存储过程并执行所创建的存储过程：(1) 按姓名查询供应商信息参考语句create procedure proc_供应商信息_姓名 name char(8)asbegin print name + 的供应商信息if not exists( select * from 供应商 where 姓名=name)print 没有符合条件的信息else select * from 供应商 where 姓名=nameend(2) 查询预算在x元至y元之间的工程信息(3) 将零件代号为参数1的供应零件表中的供应商代号更新为参数2，并且在更新之前检查供应商表中是否存在供应商代号为参数2的供应商，如果不存在则提示不能修改。3、用触发器完成下列操作：（1）将对供应零件表中的供应商代号进行更新，并且在更新之前检查供应商表中是否存在此供应商，若不存在则取消更新操作，若存在则执行更新。并观察触发器的作用。参考语句create trigger trig_供应零件_修改供应商on 供应零件 for updateasbeginif not exists(select * from 供应商 where 供应商代号 in (select 供应商代号 from inserted)beginprint 不能更新 rollback end end（2）在供应零件表中增加新数据时进行完整性检查，并观察触发器的作用。三、实验要求：1、要求记录运行的SQL语句及运行结果，并能分析和解释实验现象四、思考题：（要求以下问题回答在实验报告中）1、比较存储过程和触发器的异同点 实验七 数据库保护一、实验目的：1、掌握SQL Server 2000中有关用户、权限的管理方法。2、了解SQL Server2000的数据备份机制3、加深对数据库安全性的理解二、实验内容：1、以sa登录，在MS SQL SERVER2000企业管理中，创建一个数据库，数据库名为DB1，在该数据库中创建一个学生表（学号，姓名，性别，年龄，所在系）。2、以sa登录，在MS SQL SERVER的安全性中创建一个登录(Login)，登录名为Login1，密码为123456。以Login1登录，观察可访问的数据库情况，并记录。3、在DB1数据库中创建数据库用户，即使登录名Login1成为DB1的数据库用户，再次以Login1登录，观察可访问的数据库情况，并记录。请在查询分析器中执行如下SQL，观察并记录结果。1）select * from 学生表2）用SQL在学生表中插入一条记录3）create table 课程表(课号 char(10) primary key,课程名称 char(30) not null,学分 smallint not null)4、新建一个查询分析器窗口，以sa登录，用授权语句赋于Login1用户创建表的权力，查询学生表的权力，向学生表中添加、修改、删除记录的权力。5、重新执行3中的SQL，观察并记录结果。6、新建一个查询分析器窗口，以sa登录，用收权语句收回Login1用户创建表的权力，查询学生表的权力，向学生表中添加、修改、删除记录的权力。7、重新执行3中的SQL，观察并记录结果。8、创建