终结版数据库设计指导书.docx
《终结版数据库设计指导书.docx》由会员分享，可在线阅读，更多相关《终结版数据库设计指导书.docx（29页珍藏版）》请在冰豆网上搜索。
终结版数据库设计指导书
《数据库系统》课程设计指导书
一、课程设计的目的和意义
数据库系统课程设计是实践性教学环节之一，是《数据库系统》课程的辅助教学课程。
通过课程设计，使学生掌握数据库的基本概念，结合实际的操作和设计，巩固课堂教学内容，使学生掌握数据库系统的基本概念、原理和技术，将理论与实际相结合，应用现有的数据建模工具和数据库管理系统软件，规范、科学地完成一个小型数据库的设计与实现，把理论课与实验课所学内容做一综合，并在此基础上强化学生的实践意识、提高其实际动手能力和创新能力。
二、设计要求
通过设计一个完整的数据库，使学生掌握数据库设计各阶段的输入、输出、设计环境、目标和方法。
熟练掌握两个主要环节——概念结构设计与逻辑结构设计；熟练的使用SQL语言实现数据库的建立、应用和维护。
集中安排3周进行课程设计，教师讲解数据库的设计方法以及布置题目，要求学生根据题目的需求描述，进行实际调研，提出完整的需求分析报告。
要求如下：
1、要充分认识课程设计对培养自己的重要性，认真做好设计前的各项准备工作。
2、既要虚心接受老师的指导，又要充分发挥主观能动性。
结合课题，独立思考，努力钻研，勤于实践，勇于创新。
3、独立按时完成规定的工作任务，不得弄虚作假，不准抄袭他人内容，否则成绩以不及格计。
4、课程设计期间，无故缺席按旷课处理；缺席时间达四分之一以上者，其成绩按不及格处理。
5、在设计过程中，要严格要求自己，树立严肃、严密、严谨的科学态度，必须按时、按质、按量完成课程设计。
6、如分组，小组成员之间，分工明确，但要保持联系畅通，密切合作，培养良好的互相帮助和团队协作精神。
三、课程设计选题的原则
课程设计题目以选用学生相对比较熟悉的业务模型为宜，要求通过本实践性教学环节，能较好地巩固数据库的基本概念、基本原理、关系数据库的设计理论、设计方法等主要相关知识点，针对实际问题设计概念模型，并应用现有的工具完成小型数据库的设计与实现。
具体选题见附录1。
四、课程设计的一般步骤
课程设计大体分五个阶段：
1、选题与搜集资料：
根据分组，选择课题，在小组内进行分工，进行系统调查，搜集资料。
2、分析与设计：
根据搜集的资料，进行功能与数据分析，并进行数据库、系统功能等设计。
3、程序设计：
运用掌握的语言，编写程序，实现所设计的模块功能。
4、调试与测试：
自行调试程序，成员交叉测试程序，并记录测试情况。
5、验收与评分：
指导教师对每个小组的开发的系统，及每个成员开发的模块进行综合验收，结合设计报告，根据课程设计成绩的评定方法，评出成绩。
五、本课程设计内容与要求
掌握数据库的设计的每个步骤，以及提交各步骤所需图表和文档。
通过使用目前流行的DBMS（SQLServer2005或2008），建立所设计的数据库，并在此基础上实现数据库查询、连接，视图、游标、存储过程和触发器的创建等数据库的后台操作。
1、需求分析：
根据自己的选题，绘制相应的数据流图（DFD）以及书写相关的文字说明。
2、概念结构设计：
绘制所选题目详细的E-R图。
3、逻辑结构设计：
将E-R图转换成等价的关系模式；按需求对关系模式进行规范化；对规范化后的模式进行评价，调整模式，使其满足性能、存储等方面要求；根据局部应用需要设计外模式。
4、物理结构设计：
选定实施环境，存取方法等。
5、数据实施和维护：
用SQLServer2005建立数据库结构，加载数据，实现各种查询、链接应用程序，并能对数据库做简单的维护操作（视图、索引、游标、存储过程和触发器的创建）。
6、设计小结：
总结课程设计的过程、体会及建议。
7、其他：
参考文献等。
七、课程设计报告格式要求
1、课程设计要求不少于A4纸10页，5号字1.5倍行距。
2、结构要求：
一般要求有题目、序言、需求分析（用户需求分析、业务流程分析、信息需求分析、功能需求分析）、概念结构设计（数据库需求分析、设计E-R图）、逻辑结构设计（数据库关系模式、逻辑模型设计）、物理结构设计、数据实施和维护（创建数据库和表、设计表的索引和表间的关系、创建视图和游标、创建存储过程和触发器、数据库的备份还原及数据的导入/导出）、设计小结、参考文献、附录（核心代码）。
附录1：
课程设计题目
各选题的功能及数据库的参考关系如下：
一、学生成绩管理系统
学生成绩管理系统数据库总共包含4个数据表：
学生表、教师表、课程表、成绩表，其结构和有关数据可参考excel数据表“xscj.xls”或学生成绩管理系统数据库备份文件，要求使用SQL语句完成下列功能：
1、对上述数据库进行需求分析，然后进行概念结构设计和逻辑结构设计，画出详细的E-R图，并转换成等价的关系模式。
2、对上述4个表分别设定主键、外键，建立彼此的关联。
3、使用SQL语句或用SQLServerManagementStudio建立上述学生成绩数据库和它所包含的4个数据表。
4、查询数据库
（1）查询女教师的教师编号、姓名和出生日期。
select教师编号,姓名,出生日期
from教师表
where性别='女';
go
（2）根据教师的年龄，将教师分为：
老年（大于等于50岁）、中年（40岁至50岁之间）和青年（小于等于40岁）。
select教师编号,姓名,
年龄=case
when（2016-year（出生日期））between40and50then'中年'
when（2016-year（出生日期））<=40then'青年'
else'老年'
end
from教师表
go
（3）查询选修“大学英语”的学生的姓名，并按照分数从高到低输出前3名。
Selecttop3姓名
From学生表,成绩表,课程表
Where学生表.学号=成绩表.学号AND课程表.课程号=成绩表.课程号AND课程表.课程名='大学英语'
Orderby分数
desc;
（4）查询体育学院和外语学院，而且在1986年出生的学生信息。
select*
from学生表
where院系名称='体育学院'or
院系名称='外语学院'andyear（出生日期）=1986;
go
（5）查询计算机学院姓张，并且姓名是两个字的学生的信息。
select*
from学生表
where院系名称='计算机学院'andlen（姓名）=2
and姓名like'张%';
go
（6）查询所有学生的分数信息，如果分数大于等于80，则为“优秀”；大于等于60，则为“及格”；小于60，则为“不及格”。
使用CASE函数给每个学生的分数设定等级。
select学号,课程号,分数,等级=case
when分数>=80then'优秀'
when分数>=60then'及格'
else'不及格'
end
from成绩表
go
（7）统计选修了5号课程的学生的总分、平均分、最高分和最低分。
selectsum（分数）,avg（分数）,max（分数）,min（分数）
from成绩表
where课程号=5;
go
（8）统计每门课程的总分和平均分，并按平均分从高到低排序输出。
select课程号,总分=sum（分数）,平均分数=avg（分数）
from成绩表
groupby课程号
orderbyavg（分数）desc;
go
（9）统计每个学院学生的男女生人数。
select院系名称,性别,count（*）
from学生表
groupby院系名称,性别;
go
（10）查询选修了“数据库应用”课程的学生的学号和姓名。
USExscj
GO
SELECT学号,姓名FROM学生表
WHERE学号IN
（SELECT学号FROM成绩表
WHERE课程号IN
（SELECT课程号
FROM课程表
WHERE课程名='数据库应用'））
GO
（11）查询分数都大于王林分数的学生、课程名和分数。
usexscj
go
select姓名,课程名,分数
from学生表,课程表,成绩表
where学生表.学号=成绩表.学号
and课程表.课程号=成绩表.课程号
and分数>all（select成绩表.分数
from学生表,课程表,成绩表
where学生表.学号=成绩表.学号
and课程表.课程号=成绩表.课程号
and学生表.姓名='汤腾飞'）
and姓名<>'汤腾飞'
go
（12）定义一个函数，该函数用来查询每个学生每门课程的分数，并返回查询结果。
然后调用函数，求出某个学生的学号、姓名、课程名、课程号和分数。
usexscj
go
createviewscore
as
select姓名,课程名,分数,课程表.课程号,学生表.学号
from学生表,课程表,成绩表
where学生表.学号=成绩表.学号
and课程表.课程号=成绩表.课程号
go
createfunctionstu_score（@stu_namechar（6））
returnstable
as
return
（select*fromscore
where姓名=@stu_name）
select*
fromdbo.stu_score（'高黎明'）
go
（13）查询年龄最小的学生的学号和姓名。
（14）查询每个学生的平均分。
select姓名,平均分=
（selectavg（分数）
from成绩表
where学生表.学号=成绩表.学号）
from学生表;
go
（15）将学生表中计算机学院的学生的学号、姓名、性别数据添加到student表中。
select学号,姓名,性别
intostudent
from学生表
where院系名称='计算机学院';
go
5、创建视图
（1）创建“学生_课程_分数”视图，包括计算机学院的学生的学号、姓名，和他们选修的课程号、课程名、分数。
createview学生_课程_分数
as
select学生表.学号,姓名,课程表.课程号,课程名,分数
from学生表,课程表,成绩表
where学生表.学号=成绩表.学号and课程表.课程号=成绩表.课程号and学生表.院系名称='计算机学院'
（2）创建“不及格学生信息”视图，包括全校学生中有不及格成绩的姓名、课程名、分数。
usexscj
go
createview不及格学生
as
select姓名,课程名,分数
from学生表,课程表,成绩表
where学生表.学号=成绩表.学号and课程表.课程号=成绩表.课程号and分数<60
go
（3）创建“教师信息”视图，查看教师的所有信息资料。
usexscj
go
createview教师信息
as
select*
from教师表
go
（4）创建“课程信息”视图，包括课程号、课程名、学分、任课教师等信息。
CREATEVIEW课程信息
AS
SELECT课程表.课程号,课程表.课程名,课程表.学分,教师表.姓名,教师表.教师编号
FROM课程表CROSSJOIN教师表
GO
（5）查询“学生_课程_分数”视图，统计“数据库应用”课程的总分和平均分。
select总分=SUM（分数）,平均分=AVG（分数）
from学生_课程_分数
where课程名='数据库应用'
go
6、游标的创建和使用
（1）利用T-SQL扩展方式声明一个游标，查询学生表中的学号、姓名、性别和出生日期信息，并读取数据。
要求：
1）读取最后一条记录。
FETCHLASTFROM学生
2）读取第一条记录。
FETCHFIRSTFROM学生
3）读取第4条记录。
FETCHABSOLUTE4FROM学生
4）读取当前记录指针位置后第2条记录。
FETCHRELATIVE2FROM学生
5）读取当前记录指针位置前第2条记录。
FETCHRELATIVE-2FROM学生
usexscj
go
declare学生cursor
scroll
for
select学号,姓名,性别,出生日期
from学生表
open学生
fetchlastfrom学生
fetchfirstfrom学生
fetchabsolute4from学生
fetchrelative2from学生
fetchrelative-2from学生
go
（2）编写一个程序，采用游标方式输出所有学号、课程号和成绩等级。
USExscj
GO
DECLARE@no1char（5）,@no2char（6）,@fschar
（2）
DECLAREfs_cursorCURSOR
FORSELECT学号,课程号,
CASE
WHEN分数>=90THEN'A'
WHEN分数>=80THEN'B'
WHEN分数>=70THEN'C'
WHEN分数>=60THEN'D'
WHEN分数<60THEN'E'
END
FROM成绩表WHERE分数ISNOTNULL
ORDERBY学号
OPENfs_cursor
FETCHNEXTFROMfs_cursorINTO@no1,@no2,@fs
PRINT'学号 课程号 等级'
PRINT'-----------------'
WHILE@@FETCH_STATUS=0
BEGIN
PRINT@no1+''+@no2+''+@fs
FETCHNEXTFROMfs_cursorINTO@no1,@no2,@fs
END
CLOSEfs_cursor
DEALLOCATEfs_cursor
GO
（3）使用游标查看数据库XSCJ中“学生表”中计算机学院的记录个数。
usexscj
go
declare学生表_计算机cursor
for
select学号,姓名
from学生表
where院系名称='计算机学院'
open学生表_计算机
fetchnextfrom学生表_计算机
while@@fetch_status=0
begin
fetchnextfrom学生表_计算机
end
7、创建存储过程
（1）用T-SQL语句创建一个存储过程StuScoreInfo，完成的功能是在学生表、课程表和成绩表中查询以下字段：
院系、学号、姓名、性别、课程名称、考试分数。
usexscj
go
createprocedureStuScoreInfo
as
select院系名称,学生表.学号,姓名,性别,课程表.课程名,成绩表.分数
from学生表,课程表,成绩表
where学生表.学号=成绩表.学号and课程表.课程号=成绩表.课程号
go
（2）创建一个带有参数的存储过程Stu_Info，该存储过程根据输入的学号，在学生表中查询此学生的信息。
createprocedureStu_Info
@s_numberchar（8）
as
select*from学生表
where学号=@s_number
go
（3）对学生表建立存储过程，在表中插入一条记录。
然后执行该存储过程，验证插入一条学生记录的结果。
createprocedureStu_1
as
insertinto学生表（学号）
values（'10089'）;
go
（4）对课程表建立存储过程，根据课程号在课程表中删除某个课程记录。
（注意，要首先在成绩表中删除相关记录），然后执行存储过程，验证结果。
createprocedureStu_1
as
insertinto学生表（学号）
values（'10089'）;
go
（5）对成绩表建立存储过程，根据学号修改某个同学的某门课的成绩，然后执行存储过程。
usexscj
go
createproceduregra_1
@numberfloat（8）
as
update分数
set分数=88
where学号=@numberand课程号='5';
go
（6）对教师表创建存储过程，根据年龄划分等级：
老年（大于等于50岁）、中年（40岁至50岁之间）和青年（小于等于40岁）。
createproceduretec_1
@birthdaydatetime
as
select姓名,
年龄等级=case
when（2013-YEAR（出生日期））>=50then'老年'
when（2013-YEAR（出生日期））<=40then'青年'
when（2013-YEAR（出生日期））between40and50then'中年'
end
from教师表
go
（7）创建存储过程，查询所有学生的分数信息，如果分数大于等于80，则为“优秀”；大于等于60，则为“及格”；小于60，则为“不及格”。
使用CASE函数给每个学生的分数设定等级。
查询出的结果应尽量清晰。
createprocedurestu_2
@分数float
as
select姓名,课程号,
成绩等级=case
when分数>=80then'优秀'
when分数between60and80then'及格'
when分数<60then'不及格'
end
from成绩表,学生表
where学生表.学号=成绩表.学号
go
8、创建触发器
（1）创建一个AFTER触发器，要求实现以下功能：
在成绩表上创建一个插入、更新类型的触发器TR_ScoreCheck，当在成绩表字段中插入或修改考试分数后，触发该触发器，检查分数是否在0-100之间。
useStud
go
createtriggerTR_ScoreCheckonResult
forinsert,update
as
ifupdate（result）
print（'after触发器开始执行......'）
begin
declare@resultchar（3）
select@result=（selectresultfrominserted）
if@result>100or@result<0
print（'输入的成绩有误，请确认输入的考试分数！
'）
end
go
（2）创建一个AFTER触发器，要求如下：
在学生表上创建一个删除类型的触发器TR_Stu_Delete，当在学生表中删除某一条记录后，触发该触发器，在成绩表中删除与此学号对应的记录。
useStud
go
createtriggerTR_Stu_DeleteonStudent
fordelete
as
declare@StuNumchar（10）
select@StuNum=StuID
fromdeleted
deletefrom成绩表
whereStuID=@StuNum
go
（3）在学生表上创建一个触发器TR_SexCheck，当插入一条记录时，检查性别字段，判断只能是“男”或“女”，否则认为非法，重新输入。
useStud
go
createtriggerTR_SexCheckonStudent
forinsert
as
begin
declare@xingbiechar
（2）
select@xingbie=（selectStusexfrominserted）
if@xingbienotin（'男','女'）
rollbacktransaction
end
9、数据库的备份与还原
对xscj数据库进行备份和还原的操作，并将xscj数据库中的4个数据表与Excel进行数据的导入与导出操作，写出操作步骤。
二、学生管理信息系统
案例如下：
学校教务科对学生的所属院系及选课情况等进行管理。
学校中设置多个学院，学院下设系，班级。
学生以班级为单位。
学生选课信息通过系统进行记录，每个同学可以选修多门课程，每门课程可以被多个同学选修。
教师以系部为单位进行管理，一名教师可以讲授多门课程，一门课程只能有一个主讲教师。
设计任务如下。
1、对上述系统的数据库进行需求分析，然后进行概念结构设计和逻辑结构设计，画出详细的E-R图，并转换成等价的关系模式。
2、对上述物理设计中创建的表分别设定主键、外键，建立彼此的关联。
3、使用SQL语句或用SQLServerManagementStudio建立上述学生管理信息系统的数据库和它所包含的数据表。
附：
学生管理信息系统的数据库Stud中包含7个表，表的定义结构如下。
其结构和有关数据可参考数据库文件Student.mdf和Student.ldf。
表1学生信息表（Student）结构定义
列名
列类型（长度）
列属性
列描述
StuID
Char（13）
主键
学号
StuName
Nvarchar（8）
姓名
StuSex
Nvarchar
（2）
性别
StuBir
Smalldatetime
出生日期
StuAddr
Nvarchar（30）
家庭住址
ClaID
Char（10）
外键
所在班级
表2班级信息表（Class）结构定义
列名
列类型（长度）
列属性
列描述
ClaID
Char（10）
主键
班级号
ClaName
Nvarchar（20）
班级名称
MinName
Nvarchar（10）
班长姓名
Subject
Nvarchar（20）
专业
DepID
Char（10）
外键
所在系
表3系信息表（Department）结构定义
列名
列类型（长度）
列属性
列描述
DepID
Char（10）
主键
系编号
DepName
Nvarchar（30）
系名称
DepChairman
Nvarchar（8）
系主任姓名
Classnum
int
班级数量
Teachernum
int
教师数量
ColID
Char（10）
外键
所在学院
表4学院信息表（College）结构定义
列名
列类型（长度）
列属性
列描述
ColID
Char（10）
主键
学院编号
ColName
Nvarchar（20）
学院名称
ColChairman
Nvarchar（10）
院长姓名
ColPhone
Nvarchar（13）
电话号码
Depnum
int
系数量
表5教师信息表（Teacher）结构定义
列名
列类型（长度）
列属性
列描述
TeaID
Char（10）
主键
教师编号
TeaName
Nvarchar（8）
教师姓名
TeaSex
Nvarchar
（2）
性别
TeaDeg
Nvarchar（20）
升级会员 