《数据库技术与开发》工程实训指导书
内科大信息学院计算机系
2014年2月15日
目录
1、实验一：SQL SERVER安装及环境配置 (4)
一、课后作业 (4)
二、上机作业 (4)
2、实验二：数据库操作 (5)
一、课后作业 (5)
二、上机作业 (5)
1、创建NewDB数据库 (5)
2、NEWDB数据库操作 (5)
3、实验三：数据表操作 (6)
一、课后作业 (6)
二、上机作业 (6)
1、创建数据表customers（必做） (6)
2、在Market中创建数据表orders（必做） (6)
3、EduAdmin数据库创建与管理 (6)
4、实验四：T-SQL基础及T-SQL语句 (8)
一、课后作业 (8)
二、上机作业 (8)
1、T-SQL编程1：CASE语句 (8)
2、T-SQL编程2：统计被3整除的数和总和 (8)
3、T-SQL编程3：输出素数 (8)
4、调试DML综合示例程序 (8)
5、实验五：T-SQL函数 (10)
一、课后作业 (10)
二、上机作业 (10)
6、实验六：T-SQL基本查询 (11)
一、上机作业 (11)
7、实验七：SQL SERVER高级查询及更新 (12)
一、上机作业 (12)
8、实验八：SQL SERVER高级查询及更新 (13)
一、上机作业 (13)
1、建立约束 (13)
2、建立索引 (13)
9、实验九：事务、锁和游标 (14)
一、课后作业 (14)
二、上机作业 (14)
1、我的租房网 (14)
10、实验十：存储过程、自定义函数和视图 (15)
一、课后作业 (15)
二、上机作业 (15)
1、我的租房网 (15)
11、实验十一：触发器 (16)
一、课后作业 (16)
二、上机作业 (16)
1、我的租房网 (16)
12、实验十二：SQL SERVER安全机制 (17)
一、课后作业 (17)
二、上机作业 (17)
1、我的租房网 (17)
实训项目一：《我的租房网设计与实现》 (18)
一、项目背景 (18)
1、项目任务 (18)
2、项目技能目标 (18)
3、需求概述 (18)
4、开发环境 (19)
5、实训进度安排 (19)
6、成绩考核 (20)
二、项目实训内容 (20)
1、实训一：建立数据库结构 (20)
2、实训二：添加测试数据 (21)
3、实训三：综合查询 (23)
4、实训四：业务统计 (24)
实训项目二：《银行ATM存取款机系统设计与实现》 (26)
一、项目背景 (26)
1、项目任务 (26)
2、项目技能目标 (26)
3、需求概述 (26)
4、开发环境 (26)
5、问题分析 (26)
6、实训进度安排 (28)
7、成绩考核 (29)
二、项目实训内容 (29)
1、实训一：制定《数据库设计与编程规范》 (29)
2、实训二：数据库分析设计与建模 (29)
3、实训三：创建数据库 (31)
4、实训四：创建触发器和插入测试数据 (34)
5、实训五：模拟常规业务 (37)
6、实训六：创建、使用视图 (39)
7、实训七：存储过程实现业务处理 (40)
8、实训八：利用事务实现转账 (49)
附录 (52)
1、项目实训报告要求 (52)
(1) 设计报告及书写内容要求 (52)
(2) 提交资料 (52)
2、实训项目设计报告排版、装订格式要求 (52)
(1) 页面设置及装订格式 (52)
(2) 字体字号 (52)
1、实验一：SQL SERVER安装及环境配置
一、课后作业
1、什么是数据库实例？默认实例和命名实例的区别？
2、简述SQL SERVER由哪4部分构成？每部分的作用是是什么？
3、简述SQL SERVER2012常见有哪些安装版本，各个版本有什么特点？
4、常见的主流数据库有哪些？它们之间的区别是什么？
二、上机作业
1、安装SQL SERVER2008开发版。
2、熟悉SQL SERVER集成管理工具及环境配置。
一、课后作业
1、SQL SERVER有哪些系统数据库，各自作用是什么？
2、SQL SERVER数据库文件和日志文件各自的特点及作用是什么？
3、为什么要使用辅助数据文件？数据库可以不用自动增长吗？
二、上机作业
1、创建NewDB数据库
分别使用SSMS和T-SQL语句创建名称为NewDB的新数据库，数据库参数如下：
逻辑数据文件名：newDBdata。
操作系统数据文件名：D:\newDBdata.mdf。
数据文件的初始大小2MB，最大大小20MB，增长幅度10%。
日志逻辑文件名：newDBlog。
操作系统日志文件名：D:\newDBlog.ldf。
日志文件初始大小1MB，最大大小5MB，增长幅度1M。
2、NEWDB数据库操作
(1) 使用T-SQL语句修改数据库NewDB现有数据文件的属性，将主数据文件的
最大大小改为50MB，增长方式改为每次2MB增长。
(2)使用T-SQL语句对数据库NewDB增加数据文件newDBdata1.ndf，初始大小
为10MB，最大大小为50MB，按10%增长。
(3) 使用T-SQL语句删除新增的数据文件newDBdata1.ndf
(4) 使用T-SQL语句将数据库NewDB的名字改为NewDB1。
一、课后作业
1、常见数据库约束有哪些及其各自特点？
2、char、varchar、nchar、nvarchar之间区别？
3、date和datetime之间区别？money和float区别？
二、上机作业
1、创建数据表customers（必做）
分别使用SSMS和T-SQL语句分别完成下述操作：
创建数据库Marker，在Market中创建数据表customers，customers表结构如下表所示，按要求进行操作。
表1．customers表结构
字段名数据类型主键非空唯一
c_id Int 是是是
c_name Varchar(50) 否否否
c_contact Varchar(50) 否否否
c_city Varchar(50) 否否否
c_birth DateTime 否是否
(1) 创建数据库Market
(2) 创建数据表customers，在c_id字段上添加主键约束，在c_birth字段上添加非空约束。
(3) 将c_name字段数据类型改为varchar(70)。
(4) 将c_contact字段改名为c_phone。
(5) 增加c_gender字段，数据类型为Char(1)。
(6) 将表名修改为customers_info。
(7) 删除字段c_city。
2、在Market中创建数据表orders（必做）
orders表结构如下表所示，按要求进行操作：
(1) 创建数据表orders，在o_num字段上添加主键约束，在c_id字段上添加外键约束，关联customers表中的主键c_id。
(2) 删除orders表的外键约束，然后删除表customers。
表2．orders表结构
字段名数据类型主键非空唯一
o_num Int(11) 是是是
o_date date 否否否
c_id Varchar(50) 否否否
3、EduAdmin数据库创建与管理
请分别使用Management Stuio及T-SQL完成以下内容：
(1) 创建表
创建一个数据库：教务管理数据库EduAdmin，依据数据表的结构创建相对应的数据表，表结构如下所示；
表3．学生信息表（student）
字段名称字段类型及长度说明备注
Sno Char（9）学生学号主关键字
Sname Char（6）学生姓名非空
Ssex Char（2）学生性别可为空
Sage Int 学生年龄可为空
Sdept VarChar（8）学生所在院系可为空
表4．课程信息表（course）
字段名称字段类型及长度说明备注
Cno Char（4）课程编号主关键字
Cname VarChar（20）课程名称非空
Cpno Char（4）先行课可为空
Ccredit Int 学分可为空
表5．选课信息表（sc）
字段名称字段类型及长度说明备注
Sno Char（9）学生学号主关键字
Cno Char（4）课程编号主关键字
Grade Int 成绩可为空
（2) 修改表结构
在表student中增加新字段“班级名称（sclass）”字符类型为varchar(10)； 在表student中删除字段“班级名称（sclass）”；
修改表student中字段名为“sname”的字段长度由原来的6改为8；
修改表student中ssex字段默认值为‘男’；
修改表course中cname字段为强制唯一性字段；
修改表sc中grade字段的值域为0-100；
删除数据表course的唯一性约束；
4、实验四：T-SQL基础及T-SQL语句
一、课后作业
1、简述T-SQL语句分类及其作用？
2、简述什么是架构Schema？
3、简述SQL SERVER中最常用的3个全局变量以及全局变量和局部变量的区别？
4、简述什么是批处理及其特点？什么是T-SQL脚本及其特点？
二、上机作业
1、T-SQL编程1：CASE语句
声明整数变量@var，使用CASE流程控制语句判断@var值等于1，等于2或者两者都不等。当@var值为1时，输出字符串”var is 1”；当@var值为2时，输出字符串”var is 2”；否则输出字符串”var is not 1 or 2”。
2、T-SQL编程2：统计被3整除的数和总和
编程计算1~100之间所有能被3整除的数的个数@count和总和@sum，并分别用Select语句和Print语句两种方法输出个数@count和总和@sum。
3、T-SQL编程3：输出素数
用T-SQL语言编程输出3～100之内的素数。
4、调试DML综合示例程序
将代码段的作用及个人心得体会写到实验报告中
(1) 代码段1
use student
go
--创建Teacher表
create table teacher
(
id int not null primary key,
name varchar(20) not null,
birthday date,
sex char(4),
phone varchar(15)
);
go
--插入单条记录
insert into teacher(id,name,birthday,sex,phone)
values(1,'张三','1977-1-2','男','0472-*******')
insert into teacher
values(2,'李四','1978-10-2','男','130********')
--插入多条记录
insert into teacher values
(3,'孙二娘','1978-09-12','女','0474-*******'),
(4,'张三丰','19600402','男','0479-*******'),
(5,'李元霸','1969-10-2','男','0472*******')
go
--查询记录
select * from teacher
(2) 代码段2
use student
select * from teacher
--修改表中指定记录
update teacher set name='萧十一郎',phone='130********' where ID=1
select * from teacher
(3) 代码段3
use student
select * from teacher
delete from teacher where name like '李%'
select * from teacher
(4) 代码段4
create table stu_info
(
s_id int primary key,
s_name char(20) not null,
s_score int,
s_sex char(2),
s_age int
);
insert into stu_info values
(1,'王登',98,'男',21),
(2,'李敏',88,'女',20),
(3,'张林',76,'男',21),
(4,'余蒙',90,'女',21),
(5,'王登',65,'男',19);
select * from stu_info
(5) 代码段5
--基本Select查询
select * from stu_info;
--查找记录中指定字段
select s_id,s_name,s_score from stu_info;
--查询结果中使用表达式
select s_id,s_name,s_score-10 as newscore from stu_info;
--显示部分数据
select top 3 * from stu_info;
select Top(80) percent * from stu_info;
--带限定条件的查询
select * from stu_info where s_age>20 and s_sex='男'
select * from stu_info where s_score between 60 and 90 select * from stu_info where s_name like '余%'
select * from stu_info order by s_score desc
5、实验五：T-SQL函数
一、课后作业
1、简述T-SQL语句分类及其作用？
二、上机作业
1、使用数学函数进行如下运算
(1) 计算18除以5的商和余数
(2) 将弧度值PI()/4转换为角度值。
(3) 计算9的4次方值。
(4) 保留浮点值3.14159小数点后面2位。
2、使用字符串函数进行如下运算
(1) 分别计算字符串“Hello World！”和“University”的长度。
(2) 从字符串“Nice to meet you”中获取子字符串“meet”。
(3) 除去字符串“H e llo”中的空格。
(4) 将字符串“SQLSERVERE”逆序输出。
(5) 在字符串“SQLSERVERSQLSERVER”中，从第4个字母开始查找字母Q
第一次出现的位置。
3、使用日期和时间函数进行如下运算
(1)计算当前日期是一年的第几天。
(2)计算当前日期是一周中的第几个工作日。
(3)计算“1929-02-14”与当前日期之间相差的年份。
6、实验六：T-SQL基本查询
一、上机作业
1、Employee数据库基本查询
分别创建下面的Employee和Dept数据表，表结构如下表所示，在数据表中分别插入下表记录。
表6．Employee表结构
字段名字段说明数据类型主键外键非空唯一
e_no 员工编号INT 是否是是
e_name 员工姓名V ARCHAR(50) 否否是否
e_gender 员工性别CHAR(2) 否否是否dept_no 部门编号INT 否否是否
e_job 职位V ARCHAR(50) 否否是否
e_salary 薪水INT 否否是否hiredate 入职日期DATE 否否是否
表7．Dept表结构
字段名字段说明数据类型主键外键非空唯一
d_no 部门编号INT 是是是是
d_name 部门名称V ARCHAR(50) 否否是否
d_location 部门地址V ARCHAR(100) 否否否否
P229-P230页：第1题（增补文字）。
1、计算所有女员工（F）的工龄。
2、使用Top查询或者Rank函数查询第3条到第6条记录。
3、查询销售人员的最低工资及编号、姓名、雇佣日期。
6、分别使用左连接、右连接、全连接方式查询员工表和部门表，显示员工详细信息及部门名称、部门地址。
9、分组统计并显示各个部门员工的最高工资、最低工资、平均工资，并且按平均工资升序排序。
7、实验七：SQL SERVER高级查询及更新
一、上机作业
P241页，要求所有建库、建表、增加测试数据等均用T-SQL语句完成。附加作业（第12周公布，课外时间完成）
安装Visio，并学习Visio专题压缩包相关内容。
第12周会布置一些Visio上机实践及T-SQL高级查询上机实践。
8、实验八：SQL SERVER高级查询及更新
一、上机作业
1、建立约束
P257页第1、2题，建立Market数据库和数据表Customers、Orders可以用向导来完成，但添加约束要求必须用T-SQL语句实现。
2、建立索引
P271-P272页，建库建表可以用向导完成，其他均要求使用T-SQL语句完成。
9、实验九：事务、锁和游标
一、课后作业
1、事务和锁在应用上的区别是什么？
2、事务和锁有什么关系？
3、简述事务原理，事务有哪些类型？
4、常用的锁类型有哪些？为什么会产生死锁？如何理解锁的相容性？
5、游标的含义及分类？
6、使用游标的基本步骤有哪些？
二、上机作业
1、我的租房网
下载班级群共享里的《优化MySchool数据库设计》，完成第5章《我的租房网》。
实验要求具体见后续。
10、实验十：存储过程、自定义函数和视图
一、课后作业
1、事务和锁在应用上的区别是什么？
2、事务和锁有什么关系？
3、简述事务原理，事务有哪些类型？
4、常用的锁类型有哪些？为什么会产生死锁？如何理解锁的相容性？
5、游标的含义及分类？
6、使用游标的基本步骤有哪些？
二、上机作业
1、我的租房网
下载班级群共享里的《优化MySchool数据库设计》，完成第5章《我的租房网》。
实验要求具体见后续。
11、实验十一：触发器
一、课后作业
1、事务和锁在应用上的区别是什么？
2、事务和锁有什么关系？
3、简述事务原理，事务有哪些类型？
4、常用的锁类型有哪些？为什么会产生死锁？如何理解锁的相容性？
5、游标的含义及分类？
6、使用游标的基本步骤有哪些？
二、上机作业
1、我的租房网
下载班级群共享里的《优化MySchool数据库设计》，完成第5章《我的租房网》。
实验要求具体见后续。
12、实验十二：SQL SERVER安全机制
一、课后作业
1、事务和锁在应用上的区别是什么？
2、事务和锁有什么关系？
3、简述事务原理，事务有哪些类型？
4、常用的锁类型有哪些？为什么会产生死锁？如何理解锁的相容性？
5、游标的含义及分类？
6、使用游标的基本步骤有哪些？
二、上机作业
1、我的租房网
下载班级群共享里的《优化MySchool数据库设计》，完成第5章《我的租房网》。
实验要求具体见后续。
实训项目一：《我的租房网设计与实现》
一、项目背景
1、项目任务
T-SQL编程创建事务产生随机测试数据
实现各种业务查询功能
2、项目技能目标
使用临时表保存临时查询结果
使用子查询、联接查询、联合查询
使用事务和视图
使用T-SQL编程批量插入测试数据
3、需求概述
项目组接受开发“我的租房网”软件任务，现在项目组接受数据库设计工作，“我的租房网”数据库House包括客户信息表、区县信息表、街道信息表、房屋类型表和出租房屋信息表共5个表，各表结构如下表1-5所示：
表8．客户信息表sys_user结构
列名称数据类型说明
UserId int 客户编号，主键，标识列从1开始，递增值为1 UserName varchar 客户姓名，该栏必填
UserPwd varchar 密码，至少6个字符
表9．区县信息表hos_district结构
列名称数据类型说明
DId int 区县编号，主键，标识列从1开始，递增值为1 DName varchar 区县名称，该栏必填
表10．街道信息表hos_street结构
列名称数据类型说明
StreetId int 街道编号，主键，标识列从1开始，递增值为1 SName varchar 街道名称，该栏必填
SDId varchar 区县编号，表hos_district的外键
表11．房屋类型表hos_type结构
列名称数据类型说明
HTId int 房屋类型编号，主键，标识列从1开始，递增值为1 HTName varchar 房屋类型名称，该栏必填
表12．出租房屋信息表hos_house结构
列名称数据类型说明
HMID int 出租房屋编号，主键，标识列从1开始，递增值为1 UserId varchar 客户编号，该栏必填，外键
StreetID int 街道编号，该栏必填，外键
HTId int 房屋类型编号，该栏必填，外键
Price decimal 月租金，该栏必填，默认值为0，要求大于等于0
Topic varchar 标题，该栏必填
Contents varchar 描述，该栏必填
HTime datetime 发布时间，该栏必填，默认值为当前日期，要求不大于当
前日期
Copy varchar 备注
4、开发环境
数据库：SQL SERVER 2008开发版
5、实训进度安排
实训进度安排如下表所示：
表13．实训进度安排
实训内容所需学时提交文档
实训一：建立数据库结构 4 T-SQL源文件
实训二：添加测试数据 6 T-SQL源文件
实训三：综合查询 6 T-SQL源文件
实训四：业务统计8 T-SQL源文件
6、成绩考核
实训项目最终成绩由3部分构成：平日考勤20%+实训文档30%+实训项目完成质量50%，
凡是报优的同学需参加公开答辩，讲解答辩10-15分钟。
凡实训项目雷同，或者考勤不合格直接视同不及格。
二、项目实训内容
实训内容由4个实训项目构成，建议在参考代码和实现步骤基础上进行改进，每个实训子项目的T-SQL语句写成1个T-SQL源文件，如item1.sql。
1、实训一：建立数据库结构
(1) 创建数据库House
使用SSMS向导创建数据库House，如下图所示：
图1．创建数据库House
扩展内容：要求用T-SQL语句建立house1数据库。