数据库设计实验

实验一 数据库模型的设计和建立

实验目的：

1.         了解数据库设计概念设计中的基本知识：如实体、属性、联系集…

2.         能解决设计中常见问题：如用实体集还是用属性

3.         使学生能独立完成数据库模式的设计，包括各表的结构（属性名、类型、约束和关键字等）及表之间的关系以及其连接方式，在选定的DBMS上建立数据库表。

4.         熟悉数据库建模工具的使用（PowerDesign）

 

实验要求：

1.         认真阅读业务需求，作出E-R图，并依据设计原则对数据库结构的设计务必做到最优化

2.         在选定的DBMS上建立数据库，比较手工输入SQL建立数据库与用建模工具自动生成数据库的优缺点

 

实验环境及学时安排：

1.         OFFICE，POWERDESIGN7.5

2.        SQL SERVER7.0

3.        学时：3学时

 

实验内容：

假设条件

某学院有基本实体集：系、教师、学生和课程。

 

它们各有属性：
系：系编号、系名，位置
课程：课程号，课程名称，开课学期
学生：学生学号，学生姓名、性别、地址
教师：员工号，教师姓名，办公室

 

有如下语义设定

每个系有一位系主任，有多位教师；
一个教师仅在一个系任职；
每个系开设多门不同课程；
每门课程各由多位教师授课；

一位教师门可教多门课程
一个学生可以在不同的系选修多门课程。

 

存在联系有：
1对1：  系与系主任（系主任是教师）
1对多： 系与教师、系与课程

多对多：学生与课程,教师与课程

 

实验任务及步骤

l             画出上述关系的E-R图(word)

l             写出学院数据库的关系数据库模型并给出说明(建模工具PowerDesign建立phisical data model)

 

实验报告：(内容与要求)

1．E—R图 （.doc）

2．数据库模型（ .PDM文件）

3．以书面打印形式提交实验报告，且严格按附件1和附件2格式提交。

实验二  sqlserver数据库基本管理与数据定义语言（DDL）

实验目的：

1.        掌握数据库服务器的启动、登录

2.        了解使用Enterprise Manager作数据库操作

3.        掌握SQL SERVER的数据定义语言及数据类型

4.        了解使用工具（Enterprise Manager）作数据定义与用SQL语句数据定义的区别

5.        了解设定键码约束、外码约束、非空约束、唯一约束、取值约束等各种约束

实验要求：

1.        启动、登录SQL SERVER数据库服务器

2.        使用Enterprise Manager创建和修改和删除数据库、用户、基本表、索引

3.        直接使用SQL语句(Query Analyzer)创建和修改和删除用户、基本表、索引

实验环境及学时安排：

1.        SQL SERVER7.0

2.        学时：3学时

实验内容：

1、 启动SQL SERVER数据库服务器

2、 登录SQL SERVER数据库服务器

3、 使用Enterprise Manager创建你本机的应用数据库（取名为USERDB），并作修改

4、 使用Enterprise Manager创建你个人英文名的帐户（LOGIN）和用户（USER）

在实验一的基础上，我们进行一些基本的数据定义

 

数据字典

表
Name	中文名
TblCollege	院系
TblCourse	课程
TblSelCourse	选课
TblStudent	学生
TblTeacher	教师

 

 

院系 tblCollege
字段名	数据类型	是否可以为空	Is PK	Is FK	中文名
CollegeID	int	NOT NULL	Yes	No	院系ID
CollegeName	varchar(100)	NULL	No	No	院系名称
Address	varchar(200)	NULL	No	No	位置
TeacherNum	varchar(20)	NULL	No	Yes	系主任编号

 

课程 tblCourse
字段名	数据类型	是否可以为空	Is PK	Is FK	中文名
CourseNum	varchar(20)	NOT NULL	Yes	No	课程编号
CourseName	varchar(100)	NOT NULL	No	No	课程名称
Term	int	NULL	No	No	开课学期
CollegeID	int	NULL	No	No	院系ID
TeacherNum	varchar(20)	NOT NULL	No	No	授课教师编号

 

选课 tblSelCourse
字段名	数据类型	是否可以为空	Is PK	Is FK	中文名
StudetnNum	varchar(20)	NOT NULL	Yes	Yes	学生学号
CourseNum	varchar(20)	NOT NULL	Yes	Yes	课程编号
SelDate	Datetime	NULL	No	No	选课日期
PracticeMark	Decimal(4,2)	NULL	No	No	实践成绩
TestMark	Decimal(4,2)	NULL	No	No	考试成绩

 

学生 tblStudent
字段名	数据类型	是否可以为空	Is PK	Is FK	中文名
StudetnNum	varchar(20)	NOT NULL	Yes	No	学号
StudentName	varchar(20)	NULL	No	No	姓名
Sex	char(2)	NULL	No	No	性别
Address	varchar(200)	NULL	No	No	住址

 

教师 tblTeacher
字段名	数据类型	是否可以为空	Is PK	Is FK	中文名
TeacherNum	varchar(20)	NOT NULL	Yes	No	教师编号
TeacherName	varchar(20)	NULL	No	No	教师姓名
Office	varchar(20)	NULL	No	No	办公室号
CollegeID	Int	NOT NULL	No	Yes	所属院系ID

 

5、 分别用Enterprise Manager和SQL语句（Query Analyzer）建立如上基本表（注意主键、外键、约束等的建立）

6、 分别用Enterprise Manager和SQL语句（Query Analyzer）对每一张表建立索引（可自由选择字段）

实验报告：(内容与要求)

1．Enterprise Manager使用报告

2．5、6两项实验的SQL教本（.Doc）

3．以书面打印形式提交实验报告，且严格按附件1和附件2格式提交。

实验三 数据库中数据的插入和更新

实验目的：

1.         掌握基本的插入、更新、删除SQL语句

2.         了解键码约束、外码约束、非空约束、唯一约束、取值约束等各种约束在输入数据及数据的删除、修改时的作用。

3.         练习索引、视图的建立与删除

实验要求：

1.         熟练掌握插入、更新、删除的SQL语句

2.         体会键码约束、外码约束、非空约束、唯一约束、取值约束等各种约束在输入数据及数据的删除、修改时的作用。

3.         学会索引、视图的建立与删除

实验环境及学时安排：

1.        SQL SERVER7.0

2.         学时：2学时

实验内容：

在实验二的基础上，我们进行一些基本的数据插入、更新、删除

1、 插入数据

院系

CollegeID	CollegeName	Address	TeacherNum
1	计算机与通讯学院	麓山南路184号	11001
2	化学化工学院	求实路24号	12001
3	数学系	北京路35号

由于CollegeID的类型为IDENTITY，所以系统会自动编号；对于TeacherNum，它对应了教师表中的一条数据，由于为非强制性约束关系，可以为NULL

 

教师

TeacherNum	TeacherName	Office	CollegeID
11001	松江	计算机大楼302	1
11002	武松	计算机大楼303	1
12001	王三	化工楼101	2
13001	张五		3

CollegeID对应了院系表中的一条记录，由于为强制性约束关系，所以不能为空

 

课程

CourseNum	CourseName	Term	CollegeID	TeacherNum
1110001	计算机体系结构	7	1	11002
1330002	组合数学	6		13001

 

学生：

StudetnNum	StudentName	Sex	Address
111001	六小灵通	男	学生公寓1栋507
111002	翠花	女

对于字段Sex，我们创建规则只允许输入字符 ‘男’ 或 ‘女’

 

选课

StudentNum	CourseNum	SelDate	PracticeMark	TestMark
111001	1110001	2003-03-01	85	90
111001	1330002	2002-09-01	75	75
111002	1110001	2003-03-01		75

选课表定义了学生与课程间多对多的关系，是有联系集演化而来的表，其字段StudentNum、CourseNum为强制约束字段，分别对应表tblStudent和tblCourse中的一条记录，不能为空，它们一起构成了选课表的候选关键字

2、 更新数据

l         数学系的系主任为张五

l         修改翠花的组合数学考试成绩为90分

3、 建立索引

l         对学生表（tblStudent）以学生姓名建立索引

l         以课程编号为候选关键字对课程表（tblCourse）建立索引

4、 建立视图

l         建立视图，显示所有学生的所有科目的实践成绩和考试成绩，要求字段

（StudentName，CourseName，PracticeMark，TestMark）

 

l         建立视图，显示所有科目考试成绩的平均值，要求字段（CourseNum，CourseName，AvgMark）

实验报告：(内容与要求)

1．实验内容的SQL脚本（.Doc）

2．以书面打印形式提交实验报告，且严格按附件1和附件2格式提交。

实验四 数据库中数据的查询

实验目的：

1、  掌握基本的查询、嵌套子查询及连接查询

2、  学习数据库的部分保留字符的使用

3、  学习部分统计函数的使用。

实验要求：

1、  熟练掌握基本的查询、嵌套子查询及连接查询

2、  体会各种查询的异同及相互之间的转换，体会各种查询的执行过程，为简单综合应用打下良好的基础。

 

实验环境及学时安排：

1、SQL SERVER7.0

2、  学时：3学时

实验内容：

首先我们复习一下前两个实验，简单的建立一个数据库Book；接着练习一些基本的查询、嵌套子查询及连接查询，并学习部分统计函数的使用

1、 建立数据库

Book的模型图如下：

实体及属性

书：（编号，所属类别，书名，出版社，作者，价格，总量，库存）

借书卡：（卡号，持卡者姓名，单位，卡类型）

注：编号、卡号为主键；库存（stock）有检查约束total>=stock>=0）；卡类型（type）必须为以下字符('T'，'G'，'U'，'O')

联系集

借书：（编号＃，卡号＃，借书日期，还书日期）

注：编号、卡号为候选关键字且存在外键约束；还书日期（return_date）要么为NULL，要么大于借书日期

2、 查询

l         求藏书种数、总册数、最高价、最低价。

l         列出藏书在十本以上的书（书名、作者、出版社、年份）。

l         哪些出版社的总藏书超过100种。

l         目前已借出多少册书？

 

l         年份最久远的书。

l         “数据库系统原理教程，王珊编，清华大学出版社，1998年出版”还有几本？

l         哪一年出版的图书册数最多？（提示：分组加子查询）

l         平均每本借书证的借书册数。(提示：要考虑有未借过书的借书证)

l         本年(2003)未借过书的借书证。

l         哪个系的同学平均借书最多？（提示：连接加分组加子查询）

l         最近两年(2002、2003)都未被借过的书。

l         今年(2003)那种书出借最多？

 

实验报告： (内容与要求)

1．建表的SQL脚本（.Doc）

2．复习实验三的内容，在表中用SQL插入部分测试数据

3．提交相关查询的SQL（.Doc）

4．以书面打印形式提交实验报告，且严格按附件1和附件2格式提交。

实验五 SQL函数与表达式

实验目的：

1、    掌握Transact-SQL中的主要函数

2、    学习SQL表达式的使用

实验要求：

1、                熟练掌握集合函数、数据转换函数、日期函数的使用

2、                学会使用SQL表达式

3、                了解标识符、通配符的使用

实验环境及学时安排：

1、SQL SERVER7.0

2、  学时：3学时

实验内容：

1、         集合函数

l         查询各类书籍的平均预付款和本年度迄今为止的销售额。

（提示：使用GROUP BY）

l         根据条件 type = ‘psychology’ 生成 titles 表中 price 及 advance 的求和总计

（提示：使用COMPUTE）

l         统计1992年、1993年、1994年销售书籍（表sales）的数量

（提示：使用CASE子句）

比较GROUP BY、COMPUTE和CASE的区别

2、         数据类型转换函数

l         从titles表中查询条件为type =’trage_cook’、ytd_sales数量的前两位为15的title和ytd_sales字段内容

（提示：由于ytd_sales为int类型，要进行匹配比较先要把它转换成字符类型）

l         把当前日期转换为样式 101

3、         日期函数

l         确定数据据库中（Title表）标题发布日期和当前日期间的天数

l         查询销售表（sales）中前10天销售的销售量

l         分别获取数据库当前时间的年、月、日、小时

4、         数学函数

SQL中的数学函数很多，类似于通常所用的数学函数，这里我们仅介绍两个函数

RAND（）、ROUND（），以期抛砖引玉。希望大家在实际中借助于帮助。

l         以2为随机数种子产生一随机数Random_Number

l         对数字1223.75进行整数的四舍五入和取整

5、         字符串函数

l         求在字符串’ They call me the hunter’中从17个字符开始，’h’出现的位置

l         返回 authors 表中的姓氏，在另一列中返回 authors 表中的名字首字母。

l         用 ZZZ 替换 abcdefghi 中的字符串 cde。

l         组合字符串’hello’和’join’，中间加入一空格

6、         表达式

l         显示author表中作者姓名全称

l         查询一组特定出版商（它是加利福尼亚州 (CA) 的出版商；出版的书的平均价格超过 10.00 美元）的名称

（提示：首先我们来看数据字段的来源，出版商名称来自publishers表；再看条件，出版商所再州字段来自publishers表，而它出版的书的平均价格取自Titles表，所以先要联接 titles 和 publishers 表以创建一个查询，再加入查询条件和分组信息）

思考：where和having 的区别

HAVING 子句与 WHERE 子句类似，但只应用于作为一个整体的组（即应用于在结果集中表示组的行），而 WHERE 子句应用于个别的行。

l         查询书的题头标识符，这些书的年度至今单位销售额是从 4,095 到 12,000。

（提示：要求对条件的限制要用between和> <两种方式实现）

l         查询没有标明价格的所有书籍

思考：体会NULL值与空值的区别

l         查询所有书的书名与价格，如果还没有标价，则把价格设置为0

7、         标识符和通配符

学习 %、_、[]、[^] 等通配符的用法，学习转义符[ ]的用法

l         查询电话号码以 415开头的所有作者的姓名与电话

l         查询所有以 ‘hery1’ 结尾的六字母的作者全名

l         查找以 ‘M’ 开头且第二个字母不是 ‘c’的作者姓名

l         查找以 ‘_n’开头的作者姓名

实验报告：(内容与要求)

1．提交相关练习的SQL（.Doc）

2．总结实验中提及的函数（以列表形式提供）

3．以书面打印形式提交实验报告，且严格按附件1和附件2格式提交。

实验六 触发器与存储过程

实验目的：

1、  学习SQL Server触发器的创建

2、  学习存储过程的创建与调用

实验要求：

1、  熟练掌握触发器、存储过程的创建

2、  体会触发器的作用

实验环境及学时安排：

1、SQL SERVER7.0

2、  学时：2学时

实验内容：

本实验以实验一、二为基础，再已经建立好的数据库上进行触发器和存储过程的实验

1、 触发器

l         创建教师表的触发器，使教师表插入一条系领导记录时更新院系表中的TeacherNum字段，来标识系主任

l         禁用教师表的触发器Leader_Insert

l         启用教师表的触发器

2、 存储过程

l         创建存储过程delStudent，删除学生编号等于传入参数的学生记录，并删除该学生的选课记录

l         再查询分析器中利用存储过程delStudent删除学号为 ‘111001’ 的学生记录

l         在范例数据库中pubs中如果存在存储过程 ‘titles_sum’，则删除它

l         在范例数据库中pubs中创建存储过程 salequa ，查询某一库存ID为@Stor_id的所有销售记录，并返回一销售量参数@sum

l         执行上一步创建的存储过程 salequa,输入参数@stor_id=’7131’，并根据输出参数产生销售信息，格式如下：

销售等级为【等级】, 销售量为【销售量】如果销售量<=50, 则【等级】为3；如果销售量>50 且如果销售量<100, 则【等级】为2：如果销售量>100, 则【等级】为1。

实验报告：(内容与要求)

1．  创建触发器、存储过程的SQL脚本（.Doc）

2．  以书面打印形式提交实验报告，且严格按附件1和附件2格式提交。

 

实验七 数据库高级技巧（光标、事务与错误处理）

实验目的：

1、  掌握光标的应用

2、  学习事务的定义与错误处理

实验要求：

1、  熟练掌握光标的定义，并在实践中体会其用处

2、  领会事务的作用，并会定义事务，在sql编程中运用事务

3、  掌握基本的出错处理

实验环境及学时安排：

1、SQL SERVER7.0

2、  学时：4学时

实验内容：

a)        光标

                         i.              在范例数据库Pubs上定义一个只读光标，它查询作者（authors）表的 au_id, au_lname、au_fname字段。

 

                       ii.              把上一个只读光标分别定义成标准和更新光标

 

                      iii.              利用更新光标 cur_authors_update 将au_id等于’172-32-1176’的记录的au_lname 和au_fname 分别更改为’Smith’和’Jake’

 

                     iv.              分页存储过程

在实践中我们经常碰到这样的问题：我们从一个表中读取数据显示在界面上，由于该表有大量的数据，一次性读出来显然响应太慢且占用了大量的网络带宽，应此我们用一个存储过程分页把数据读出来，这里我们举一例（使用范例数据库pubs）：

撰写一存储过程proTitlesPages 以pubdate字段为序，从titles表中分页读出数据，每页10条记录

（提示：很显然proTitlesPages需要几个参数 当前页、每页的多少条记录、共多少页）

b)        事务与错误处理

                         i.              运行下列sql语句，写出运行结果，体会事务的作用

select top 5 title_id, stor_id from sales order by title_id, stor_id

begin tran

delete sales

select top 5 title_id, stor_id from sales order by title_id, stor_id

go

rollback tran

select top 5 title_id, stor_id from sales order by title_id, stor_id

                       ii.              实验六中的存储过程 delStudent、salequa包括两条以上的sql语句，请在其中加入事务处理和出错处理

实验报告：(内容与要求)

1．建立光标与事务的SQL脚本（.Doc）

2．事务处理中的查询结果

3．以书面打印形式提交实验报告，且严格按附件1和附件2格式提交。