CSDN博客

img windspeed

数据库设计实验

发表于2004/6/27 15:53:00  6373人阅读

分类: 课程实验

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

实验目的:

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

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

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

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

 

实验要求:

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

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

 

实验环境及学时安排:

1.         OFFICEPOWERDESIGN7.5

2.        SQL SERVER7.0

3.        学时:3学时

 

实验内容:

假设条件

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

 

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

 

有如下语义设定

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

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

 

存在联系有:
1
1  系与系主任(系主任是教师)
1
对多: 系与教师、系与课程

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

 

实验任务及步骤

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

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

 

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

1ER .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 ManagerSQL语句(Query Analyzer)建立如上基本表(注意主键、外键、约束等的建立)

6、 分别用Enterprise ManagerSQL语句(Query Analyzer)对每一张表建立索引(可自由选择字段)

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

1Enterprise Manager使用报告

256两项实验的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

六小灵通

学生公寓1507

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

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

2、 更新数据

l         数学系的系主任为张五

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

3、 建立索引

l         对学生表(tblStudent)以学生姓名建立索引

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

4、 建立视图

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

StudentNameCourseNamePracticeMarkTestMark

 

l         建立视图,显示所有科目考试成绩的平均值,要求字段(CourseNumCourseNameAvgMark

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

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         最近两年(20022003)都未被借过的书。

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 BYCOMPUTECASE的区别

2、         数据类型转换函数

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

(提示:由于ytd_salesint类型,要进行匹配比较先要把它转换成字符类型)

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 表以创建一个查询,再加入查询条件和分组信息)

思考:wherehaving 的区别

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_lnameau_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.              实验六中的存储过程 delStudentsalequa包括两条以上的sql语句,请在其中加入事务处理和出错处理

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

1.建立光标与事务的SQL脚本(.Doc

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

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

0 0

相关博文

我的热门文章

img
取 消
img