数据库设计指导书解答.docx
《数据库设计指导书解答.docx》由会员分享，可在线阅读，更多相关《数据库设计指导书解答.docx（15页珍藏版）》请在冰豆网上搜索。
数据库设计指导书解答
《数据库系统》课程设计指导书
一、课程设计的目的和意义
数据库系统课程设计是实践性教学环节之一，是《数据库系统》课程的辅助教学课程。
通过课程设计，使学生掌握数据库的基本概念，结合实际的操作和设计，巩固课堂教学内容，使学生掌握数据库系统的基本概念、原理和技术，将理论与实际相结合，应用现有的数据建模工具和数据库管理系统软件，规范、科学地完成一个小型数据库的设计与实现，把理论课与实验课所学内容做一综合，并在此基础上强化学生的实践意识、提高其实际动手能力和创新能力。
二、设计要求：
通过设计一完整的数据库，使学生掌握数据库设计各阶段的输入、输出、设计环境、目标和方法。
熟练掌握两个主要环节——概念结构设计与逻辑结构设计；熟练的使用SQL语言实现数据库的建立、应用和维护。
集中安排3周进行课程设计，教师讲解数据库的设计方法以及布置题目，要求学生根据题目的需求描述，进行实际调研，提出完整的需求分析报告。
要求如下：
1、要充分认识课程设计对培养自己的重要性，认真做好设计前的各项准备工作。
2、既要虚心接受老师的指导，又要充分发挥主观能动性。
结合课题，独立思考，努力钻研，勤于实践，勇于创新。
3、独立按时完成规定的工作任务，不得弄虚作假，不准抄袭他人内容，否则成绩以不及格计。
4、课程设计期间，无故缺席按旷课处理；缺席时间达四分之一以上者，其成绩按不及格处理。
5、在设计过程中，要严格要求自己，树立严肃、严密、严谨的科学态度，必须按时、按质、按量完成课程设计。
6、如分组，小组成员之间，分工明确，但要保持联系畅通，密切合作，培养良好的互相帮助和团队协作精神。
三、课程设计选题的原则
课程设计题目以选用学生相对比较熟悉的业务模型为宜，要求通过本实践性教学环节，能较好地巩固数据库的基本概念、基本原理、关系数据库的设计理论、设计方法等主要相关知识点，针对实际问题设计概念模型，并应用现有的工具完成小型数据库的设计与实现。
具体选题见附录1。
四、课程设计的一般步骤
课程设计大体分五个阶段：
1、选题与搜集资料：
根据分组，选择课题，在小组内进行分工，进行系统调查，搜集资料。
2、分析与设计：
根据搜集的资料，进行功能与数据分析，并进行数据库、系统功能等设计。
3、程序设计：
运用掌握的语言，编写程序，实现所设计的模块功能。
4、调试与测试：
自行调试程序，成员交叉测试程序，并记录测试情况。
5、验收与评分：
指导教师对每个小组的开发的系统，及每个成员开发的模块进行综合验收，结合设计报告，根据课程设计成绩的评定方法，评出成绩。
五、本课程设计内容与要求
掌握数据库的设计的每个步骤，以及提交各步骤所需图表和文档。
通过使用目前流行的DBMS（SQLServer2005），建立所设计的数据库，并在此基础上实现数据库查询、连接，视图、游标、存储过程和触发器的创建等数据库的后台操作。
1、需求分析：
根据自己的选题，绘制相应的数据流图（DFD）以及书写相关的文字说明。
2、概念结构设计：
绘制所选题目详细的E-R图。
3、逻辑结构设计：
将E-R图转换成等价的关系模式；按需求对关系模式进行规范化；对规范化后的模式进行评价，调整模式，使其满足性能、存储等方面要求；根据局部应用需要设计外模式。
4、物理结构设计：
选定实施环境，存取方法等。
5、数据实施和维护：
用SQLServer2005建立数据库结构，加载数据，实现各种查询、链接应用程序，并能对数据库做简单的维护操作（视图、索引、游标、存储过程和触发器的创建）。
6、设计小结：
总结课程设计的过程、体会及建议。
7、其他：
参考文献等。
七、课程设计报告格式要求
1、课程设计要求不少于A4纸10页，5号字1.5倍行距。
2、结构要求：
一般要求有题目、序言、需求分析（用户需求分析、业务流程分析、信息需求分析、功能需求分析）、概念结构设计（数据库需求分析、设计E-R图）、逻辑结构设计（数据库关系模式、逻辑模型设计）、物理结构设计、数据实施和维护（创建数据库和表、设计表的索引和表间的关系、创建视图和游标、创建存储过程和触发器、数据库的备份还原及数据的导入/导出）、设计小结、参考文献、附录（核心代码）。
2011年12月10日
附录1：
课程设计题目
各选题的功能及数据库的参考关系如下：
一、学生成绩管理系统
学生成绩管理系统数据库总共包含4个数据表：
学生表、教师表、课程表、成绩表，其结构和有关数据可参考excel数据表“xscj.xls”，要求使用SQL语句完成下列功能：
1、对上述数据库进行需求分析，然后进行概念结构设计和逻辑结构设计，画出详细的E-R图，并转换成等价的关系模式。
2、对上述4个表分别设定主键、外键，建立彼此的关联。
3、使用SQL语句或用SQLServerManagementStudio建立上述学生成绩数据库和它所包含的4个数据表。
4、查询数据库
（1）查询女教师的教师编号、姓名和出生日期。
Select编号,姓名,出生日期From教师表Where性别='女'
（2）根据教师的年龄，将教师分为：
老年（大于等于50岁）、中年（40岁至50岁之间）和青年（小于等于40岁）。
Select姓名,性别,年龄=
Case
Whendatediff（year,出生日期,getdate（））>=50then'老年'
Whendatediff（year,出生日期,getdate（））>=40anddatediff（year,出生日期,getdate（））<50then'中年'
Whendatediff（year,出生日期,getdate（））<40then'青年'
End
From教师表
（3）查询选修“大学英语”的学生的姓名，并按照分数从高到低输出前3名。
Selecttop3姓名
From学生表,成绩表,课程表
Where学生表.学号=成绩表.学号AND课程表.课程号=成绩表.课程号
AND课程表.课程名='大学英语'
Orderby分数desc
GO
（4）查询体育学院和外语学院，而且在1986年出生的学生信息。
Select*From学生表
Where（院系名称='体育学院'or院系名称='外语学院'）andyear（出生日期）=1986
（5）查询计算机学院姓张，并且姓名是两个字的学生的信息。
Select*From学生表
Where院系名称='计算机学院'and姓名like'张_'
（6）查询所有学生的分数信息，如果分数大于等于80，则为“优秀”；大于等于60，则为“及格”；小于60，则为“不及格”。
使用CASE函数给每个学生的分数设定等级。
SELECT学号,课程号,分数,等级=
CASE
WHEN分数>=80THEN'优秀'
WHEN分数>=60THEN'及格'
ELSE'不及格'
END
FROM成绩表
GO
（7）统计选修了5号课程的学生的总分、平均分、最高分和最低分。
SELECTSUM（分数）,AVG（分数）,MAX（分数）,MIN（分数）
FROM成绩表
WHERE课程号=5
（8）统计每门课程的总分和平均分，并按平均分从高到低排序输出。
SELECT课程号,总分=SUM（分数）,平均分=AVG（分数）
FROM成绩表
GROUPBY课程号
ORDERBYAVG（分数）DESC
（9）统计每个学院学生的男女生人数。
SELECT院系名称,性别,COUNT（*）
FROM学生表
GROUPBY院系名称,性别
（10）查询选修了“数据库应用”课程的学生的学号和姓名。
SELECT学号,姓名FROM学生表
WHERE学号IN
（SELECT学号FROM成绩表
WHERE课程号IN
（SELECT课程号FROM课程表
WHERE课程名='数据库应用'））
（11）查询分数都大于王萌分数的学生、课程名和分数。
SELECT姓名,课程名,分数
FROM学生表,课程表,成绩表
WHERE学生表.学号=成绩表.学号AND课程表.课程号=成绩表.课程号
AND分数>ALL
（SELECTc.分数FROM学生表a,课程表b,成绩表c
WHEREa.学号=c.学号ANDb.课程号=c.课程号ANDa.姓名='王萌'）
AND姓名<>'王萌'
（12）定义一个函数，该函数用来查询每个学生每门课程的分数，并返回查询结果。
然后调用函数，求出某个学生的学号、姓名、课程名、课程号和分数。
CREATEFUNCTIONstudent_score（@s_idINT）
RETURNS@scoreTABLE
（stu_idINT,
stu_nameCHAR（6）,
cou_idINT,
cou_nameCHAR（20）,
cou_scoreDECIMAL（5,1））
AS
BEGIN
INSERT@score
SELECT学生表.学号,姓名,课程表.课程号,课程名,分数
FROM学生表,课程表,成绩表
WHERE学生表.学号=成绩表.学号AND课程表.课程号=成绩表.课程号
AND学生表.学号=@s_id
RETURN
END
调用函数：
SELECT*FROMstudent_score（10005）
（13）查询年龄最小的学生的学号和姓名。
SELECT学号,姓名FROM学生表
WHERE出生日期IN
（SELECTMAX（出生日期）FROM学生表）
（14）查询每个学生的平均分。
SELECT姓名,平均分=
（SELECTAVG（分数）FROM成绩表bWHEREa.学号=b.学号）
FROM学生表a
（15）将学生表中计算机学院的学生的学号、姓名、性别数据添加到student表中。
SELECT学号,姓名,性别INTOstudentFROM学生表
WHERE院系名称=’计算机学院’
5、创建视图
（1）创建“学生_课程_分数”视图，包括计算机学院的学生的学号、姓名，和他们选修的课程号、课程名、分数。
CREATEVIEW学生_课程_分数
AS
SELECT学生表.学号,姓名,课程表.课程号,课程名,分数
FROM学生表,课程表,成绩表
WHERE学生表.学号=成绩表.学号
AND课程表.课程号=成绩表.课程号
AND院系名称='计算机学院'
（2）创建“不及格学生信息”视图，包括全校学生中有不及格成绩的姓名、课程名、分数。
CREATEVIEW不及格学生信息
AS
SELECT学生表.学号,姓名,课程名,分数
FROM学生表,课程表,成绩表
WHERE学生表.学号=成绩表.学号
AND课程表.课程号=成绩表.课程号
AND分数<60
（3）创建“教师信息”视图，查看教师的所有信息资料。
CREATEVIEW教师信息
AS
SELECT*FROM教师表
（4）创建“课程信息”视图，包括课程号、课程名、学分、任课教师等信息。
CREATEVIEW课程信息
AS
SELECT课程表.课程号,课程名,学分,姓名
FROM课程表,成绩表,教师表
WHERE课程表.课程号=成绩表.课程号
AND教师表.教师编号=成绩表.教师编号
（5）查询“学生_课程_分数”视图，统计“数据库应用”课程的总分和平均分。
SELECT总分=SUM（分数）,平均分=AVG（分数）
FROM学生_课程_分数
WHERE课程名='数据库应用'
5、游标的创建和使用
（1）利用T-SQL扩展方式声明一个游标，查询学生表中的学号、姓名、性别和出生日期信息，并读取数据。
要求：
1）读取最后一条记录。
FETCHLASTFROM学生
2）读取第一条记录。
FETCHFIRSTFROM学生
3）读取第4条记录。
FETCHABSOLUTE4FROM学生
4）读取当前记录指针位置后第2条记录。
FETCHRELATIVE2FROM学生
5）读取当前记录指针位置前第2条记录。
FETCHRELATIVE-2FROM学生
DECLARE学生CURSOR
DYNAMIC
FOR
SELECT学号,姓名,性别,出生日期
FROM学生表
GO
OPEN学生
GO
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
--声明游标
DECLAREjbxxb_CursorCURSORFOR
SELECT学号,姓名
FROMxscj.dbo.学生表
WHERE院系名称=‘计算机学院'
--打开游标
OPENjbxxb_Cursor
--提取第一行数据
FETCHNEXTFROMjbxxb_Cursor
WHILE@@FETCH_STATUS=0
BEGIN
--提取下一行数据
FETCHNEXTFROMjbxxb_Cursor
END
--关闭游标
CLOSEjbxxb_Cursor
--释放游标
DEALLOCATEjbxxb_Cursor
6、创建存储过程
（1）用T-SQL语句创建一个存储过程StuScoreInfo，完成的功能是在学生表、课程表和成绩表中查询以下字段：
院系、学号、姓名、性别、课程名称、考试分数。
CREATEPROCEDUREStuScoreInfo
AS
Select院系名称,学号,姓名,性别,课程表.课程名称,成绩表.分数
FROM学生表,课程表,成绩表
WHERE学生表.学号=成绩表.学号
AND课程表.课程号=成绩表.课程号
GO
（2）创建一个带有参数的存储过程Stu_Info，该存储过程根据输入的学号，在学生表中查询此学生的信息。
USExscj
GO
CREATEPROCEDUREStu_Info
@S_NUMBERvarchar（10）
AS
Select学号,姓名,性别,出生日期,政治面貌
FROM学生表
WHERE学号=@S_NUMBER
GO
执行：
EXECUTEStu_Info‘10018'
（3）对学生表建立存储过程，在表中插入一条记录。
然后执行该存储过程，验证插入一条学生记录的结果。
（4）对课程表建立存储过程，根据课程号在课程表中删除某个课程记录。
（注意，要首先在成绩表中删除相关记录），然后执行存储过程，验证结果。
（5）对成绩表建立存储过程，根据学号修改某个同学的某门课的成绩，然后执行存储过程。
（6）对教师表创建存储过程，根据年龄划分等级：
老年（大于等于50岁）、中年（40岁至50岁之间）和青年（小于等于40岁）。
（7）创建存储过程，查询所有学生的分数信息，如果分数大于等于80，则为“优秀”；大于等于60，则为“及格”；小于60，则为“不及格”。
使用CASE函数给每个学生的分数设定等级。
查询出的结果应尽量清晰。
7、创建触发器
（1）创建一个AFTER触发器，要求实现以下功能：
在成绩表上创建一个插入、更新类型的触发器TR_ScoreCheck，当在成绩表字段中插入或修改考试分数后，触发该触发器，检查分数是否在0-100之间。
USEXSCJ
GO
CREATETRIGGERTR_ScoreCheck
ON成绩表
FORINSERT,UPDATE
AS
IFUPDATE（成绩表）
PRINT'AFTER触发器开始执行……'
BEGIN
DECLARE@ScoreValuereal
SELECT@ScoreValue=（SELECT分数FROMinserted）
IF@ScoreValue>100OR@ScoreValue<0
PRINT'输入的分数有误，请确认输入的考试分数！
'
END
GO
（2）创建一个AFTER触发器，要求如下：
在学生表上创建一个删除类型的触发器TR_Stu_Delete，当在学生表中删除某一条记录后，触发该触发器，在成绩表中删除与此学号对应的记录。
CREATETRIGGERTR_Stu_Delete
ON学生表
FORDELETE
AS
PRINT'删除触发器开始执行……'
DECLARE@StuNumchar（10）
PRINT‘把在学生表中删除的记录的学号赋值给局部变量@StuNum。
'
SELECT@StuNum=学号
FROMdeleted
PRINT‘开始查找并删除成绩表中的相关记录……'
DELETEFROM成绩表
WHERE学号=@StuNum
PRINT‘删除了成绩表中的学号为'+RTRIM（@StuNum）+'的记录。
'
GO
（3）在学生表上创建一个触发器TR_SexCheck，当插入一条记录时，检查性别字段，判断只能是“男”或“女”，否则认为非法，重新输入。
CREATETRIGGERTR_SexCheckON学生表
FORINSERT,UPDATE
AS
ifexists（select*from学生表where性别notin（‘男’,’女’））
begin
raiserror（性别只能是男或女，请重新输入性别’,16,1）
rollbacktransaction
end
8、数据库的备份与还原
对xscj数据库进行备份和还原的操作，并将xscj数据库中的4个数据表与Excel进行数据的导入与导出操作，写出操作步骤。
二、某单位欲开发一职工工资信息管理系统。
其中部分内容有：
职工数据库（ZGSJK），该库中有若干表：
职工简表（ZGJB），基本工资表（JBGZB），奖惩工资表（JCGZB）……,其余略。
具体内容是：
职工简表（ZGJB）
字段
（说明）
BH
（编号）
XM
（姓名）
XB
（性别）
CSRQ
（出生日期）
JG
（籍贯）
LXDH
（联系电话）
ZZ
（住址）
JCZK
（奖惩状况）
BZ
（备注）
类型
Char
Char
Char
datetime
Varchar
Varchar
Varchar
Varchar
Varchar
长度
6
8
2
8
30
20
32
12
50
允许空
√
√
√
√
√
√
√
基本工资表（JBGZB）
字段
（说明）
BH
（编号）
XM
（姓名）
ZW
（职务）
JB
（级别）
JBGZ
（基本工资）
ZWGZ
（职务工资）
BT1
（补贴1）
BT2
（补贴2）
YF
（应发）
类型
Char
Char
Char
Char
decimal
decimal
decimal
decimal
decimal
长度
6
8
16
8
6,2
6,2
6,2
6,2
7,2
允许空
√
√
√
√
√
√
√
奖惩工资表（JCGZB）
字段
（说明）
BH
（编号）
XM
（姓名）
ZW
（职务）
JB
（级别）
JL1
（奖励1）
JL2
（奖励2）
KC1
（扣除1）
KC2
（扣除2）
XJ
（小计）
SF
（实发）
类型
Char
Char
Char
Char
decimal
decimal
decimal
decimal
decimal
decimal
长度
6
8
16
8
6,2
6,2
6,2
6,2
7,2
7,2
允许空
√
√
√
√
√
√
√
√
（1）创建一个200MB的职工数据库：
ZGSJK，该数据库的主数据文件的逻辑名称是ZGSJK4_data，主数据文件是位于F:
\MSSQL\data下的ZGSJK4_data.mdf，大小是150MB，最大是600MB，以15%的速度增长；该数据库的日志文件的逻辑名是ZGSJK4_log，日志文件是位于F:
\MSSQL\data下的ZGSJK4_log.ldf，大小是50MB，最大是200MB，以10MB的速度增加。
（2）为了扩大ZGSJK的容量，需要添加一个次要数据文件ZGSJK_data2，该文件的大小是80MB，最大值是180MB，以2MB的速度增长。
（3）分别创建表：
ZGJB、JBGZB、JCGZB。
（4）在ZGJB中增加字段JB（级别），类型是Char，长度为8，允许空；在JCGZB中删除ZW（职务）字段。
（5）为JBGZB表插入数据，数据来自ZGJB表中的BH（编号），XM（姓名）列的所有数据行。
更新JBGZB、JCGZB表中所有职工的YF（应发）、XJ（小计）和SF（实发），计算公式为：
YF=JBGZ+ZWGZ+BT1+BT2，XJ=JL1+JL2-KC1-KC2，SF=YF+XJ。
删除表JBGZB中JB为“初级”职工名单。
（6）检索表ZGJB中的全部信息；检索表JBGZB中“副处级”职务的职工信息；检索职工数据库（ZGSJK）中的信息，分别来自ZGJB、JBGZB和JCGZB
升级会员 