教学管理数据库设计.docx
《教学管理数据库设计.docx》由会员分享，可在线阅读，更多相关《教学管理数据库设计.docx（42页珍藏版）》请在冰豆网上搜索。
教学管理数据库设计
教学管理数据库设计（总38页）
实验报告
课程名称：
数据库原理
实验项目名称：
数据库设计
班级与班级代码：
实验室名称（或课室）：
专业：
任课教师：
学号：
姓名：
实验日期：
年月日
广东商学院教务处制
姓名实验报告成绩
评语：
评分项目
评分内容
分值
评分结果
预习情况
是否完成预习工作。
要求按实验指导书进行预习、并完成实验报告初稿、编写程序。
20
实验方案
实验方案是否可行。
要求目的、任务明确、原理清楚、步骤可操作、对实验过程数据有记录方案、对可能的错误有预备方案。
30
实验过程
与结果
实验结果是否正确。
要求完成实验过程中的数据记录、记录要完整和准确、内容要详细，实验结果要与预期的正确结果一致。
35
实验结论
实验结论是否正确。
要求根据实验原理对实验过程中的数据、实验结果进行分析，得出实验结论。
15
总分
100
指导教师（签名）
年月日
说明：
指导教师评分后，实验报告交院（系）办公室保存。
实验六教学管理数据库设计
一、实验目的
1、数据库设计就是要使学生采用本课程中学习的数据库设计方法，运用其基本思路与主要图表工具完成一个自己所了解的业务的数据库应用系统信息需求分析与数据库的概念设计、逻辑设计、物理设计以及处理功能设计
2、熟悉数据库管理系统、SQL语言开发和实现该数据库，并测试、评价、优化改进。
3、通过本设计进一步弄懂数据库系统及其相关的基本概念，理解数据库系统的系统结构、主要特点，掌握数据库设计的原理、方法及其基本过程，初步具备数据库应用设计的能力，形成对数据库设计在处理实际问题的基本素质。
二、实验原理
根据数据库设计的方法和步骤，进行需求分析、概念结构设计、逻辑结构设计、物理设计以及进行规范化、无损连接和优化改进等处理，再根据数据操作的原理和语法测试数据库的功能。
三、实验要求
完成对教学管理数据库的以下操作
1、需求分析：
功能分析、总框架图、业务流程图、数据流图、数据字典
2、概念模型设计（E-R图）
3、逻辑设计（表、要验证函数依赖、规范化、无损连接）
4、物理设计
5、数据库实施和执行阶段（数据库的建立、数据录入、插入、删除、查询、更新、查询优化等）
四、实验设备
微型计算机一台、WinXP和MicrosoftSQLServer2000软件
五、结果预测
成功完成教学管理数据库的设计过程，各种操作顺利执行。
六、实验内容和步骤
1、需求分析阶段
1·1教学管理数据库的功能分析：
教学管理数据库要实现对学生基本信息、学生选课信息、课程基本信息、教师基本信息、学生课程成绩及教师教务信息的管理
1·2教学管理系统的总框架图如下图1-1所示：
图1-1总框架图
教学管理系统业务流程图如下图1-2所示：
统计数据库
学生信息记录
学生选课和课程信息
记录
教师信息记录
课表信息记录
课程信息记录
教师任课记录
图1-2业务流程图
1·4调查用户需求：
①教务处需求
学籍信息处理：
新生入学档案信息的录入
毕业学生档案信息的处理
学生档案的处理包括插入，删除以及修改
学生基本信息的查询
教师信息系统的管理：
教师档案的录入
教师基本信息的查询
教师基本信息的更新包括修改，添加和修改
制定教学计划：
根据有关部门提供的教师任课信息表制定教学计划
把教学计划输入课程管理数据库和教师任课数据库
排课：
根据教学计划为学生排课
每个学生可以查询自己的课程
教务处可以根据教学计划修改课程表
将课程表分发给老师，学生，教室管理员和院系
统计：
统计学生的成绩情况、男女人数、选课情况等
②学生
查询功能：
查询自己的基本信息
查询考试成绩及排名
查询课程信息
查询任课老师的信息
③教师
查询：
查询自己的基本信息
查询课程表
查询学生的总体成绩
1·5数据字典
经分析，该系统要用到六个基本表：
课程信息表、教学任务表、教师档案表、学生档案表、学生选课表、学生成绩表，数据结构定义如下表1-3所示：
数据结构名
含义说明
组成
课程信息表
定义了课程的有关信息
课程ID、课程名称、课程类别、学分、学时
教学任务表
定义了教师教授学生的有关信息
课程ID、教师ID、学期、年度
教师档案表
定义了教师的有关信息
教师ID、姓名、性别、职称、单位
学生档案表
定义了学生的有关信息
学号、姓名、性别、出生日期、籍贯、专业、班级
学生选课表
定义了选课的基本信息
学号、课程类别、课程ID
学生成绩表
定义了学生的成绩的相关信息
学号、课程ID、平时成绩、期末成绩、总成绩
表1-3数据结构说明
经分析，数据项定义如下表1-5所示：
数据项名
类别
长度
说明
课程ID
char
6
前2个字符大写字母，表示课程类别，后面4个字符为数字，表示课程编号
课程名称
varchar
40
表示课程的名称
课程类别
char
8
包含基础必修，专业必修，限选和任选
学分
real
4
<=学分<=
学时
int
4
8的整数倍，每16个学时为一个学分
教师ID
char
8
教师的号码，8位数字
学期
int
4
只能选择1或2
年度
char
10
1990<=年度<=2010
姓名
varchar
16
教师和学生的姓名
性别
char
1
教师和学生的性别，用大写字母M、F表示，M表示男，F表示女
职称
varchar
8
包含教授、副教授、讲师、助教
单位
varchar
20
可以是会计系、物流系、信管系、经济系、工商管理系
课程类别
char
8
包括正选、旁听、重修
学号
char
8
由八位数字组成
姓名
varchar
16
学生的名字
出生日期
char
8
出生日期>=1970
籍贯
datatime
8
学生出生地
专业
varchar
16
学生所在专业
班级
int
4
学生所在班级
平时成绩
real
4
小数点后一位
期末成绩
real
4
小数点后一位
总成绩
real
4
小数点后一位，计算公式：
总成绩=平时成绩*（1-a%）+期末成绩*a%
表1-4数据项说明
1·6数据流图
学生选课表
课程信息表
教师档案表
教学任务表
学生成绩表
学生档案表
2、概念结构设计阶段（E-R图设计）
经过分析数据字典和数据流图设计E—R图如下图2-1所示：
图2-1E—R图
3、逻辑结构设计阶段
3·1由E—R图向关系模型的转换如下：
学生（学号，姓名，性别，出生日期，籍贯，专业，班级）
课程（课程ID，课程名称，课程类别，学分，学时）
教师（教师ID，姓名，性别，职称，单位）
选课（学号，课程ID，课程类别）
成绩（学号，课程ID，平时成绩，期末成绩，总成绩）
教务（课程ID，年度，学期，教师ID）
3·2各张表的逻辑结构设计如下所示：
“课程信息表”结构
字段名
类型
长度
备注
课程ID
Char
6
主键，前2个字符大写字母，表示课程类别，后面4个字符为数字，表示课程编号
课程名称
Varchar
40
课程类别
Char
8
包含基础必修，专业必修，限选和任选
学分
Real
4
<=学分<=
学时
int
4
8的整数倍，每16个学时为一个学分
“教学任务表”结构
字段名
类型
长度
备注
课程ID
Char
6
主键，前2个字符大写字母，表示课程类别，后面4个字符为数字，表示课程编号
教师ID
Char
8
教师档案表的外键，8位数字
学期
Int
4
只能选择1或2
年度
char
10
1990<=年度<=2010
“教师档案表”结构
字段名
类型
长度
备注
教师ID
Char
8
主键，与教学任务表里的教师ID一致，也是八位数字
姓名
Varchar
16
教师的姓名
性别
Char
1
用大写字母M、F表示，M表示男，F表示女
职称
Varchar
8
包含教授、副教授、讲师、助教
单位
varchar
20
可以是会计系、物流系、信管系、经济系、工商管理系
“学生档案表”结构
字段名
类型
长度
备注
学号
char
8
主键，由八位数字组成
姓名
Varchar
16
学生的名字
性别
char
1
用大写字母M、F表示，M表示男，F表示女
出生日期
Char
8
出生日期>=1970
籍贯
Datatime
8
专业
Varchar
16
班级
Int
4
“学生选课表”结构
字段名
类型
长度
备注
学号
Char
8
主键，由八位数字组成
课程类别
Char
8
包括正选、旁听、重修
课程ID
char
6
课程信息表和教学任务表的外键，前2个字符大写字母，表示课程类别，后面4个字符为数字，表示课程编号
“学生成绩表“结构
字段名
类型
长度
备注
学号
Char
8
主键，由八位数字组成
课程ID
Char
6
前2个字符大写字母，表示课程类别，后面4个字符为数字，表示课程编号
平时成绩
Real
4
小数点后一位
期末成绩
Real
4
小数点后一位
总成绩
real
4
小数点后一位，计算公式：
总成绩=平时成绩*（1-a%）+期末成绩*a%
3·3以上六张表有如下逻辑依赖关系，表现出各表间主键与外键的关联关系，如图3-1所示：
学号
姓名
性别
出生日期
籍贯
专业
班级
课程ID
课程名称
课程类别
学分
学时
教师ID
姓名
性别
职称
单位
学号
课程ID
课程类别
课程ID
年度
学期
教师ID
学号
课程ID
平时成绩
期末成绩
总成绩
图3-1表的逻辑关系图
3·4根据需求分析和以上的逻辑关系图确定数据依赖。
分别写出每个关系内部属性的以及不同关系模式属性间的数据依赖如下：
a、课程信息表的数据依赖：
{课程ID->课程名称，课程ID->课程类别，课程ID->学分，课程ID->学时}
b、教学任务表的数据依赖：
{（课程ID，年度，学期）->教师ID}
c、教师档案表的数据依赖：
{教师ID->姓名，教师ID->性别，教师ID->职称，教师ID->单位}
d、学生档案表的数据依赖：
{学号->姓名，学号->性别，学号->出生日期，学号->籍贯，学号->专业，学号->班级}
e、学生选课表的数据依赖：
{（学号，课程ID）->课程类别}
f、学生成绩表的数据依赖：
{（学号，课程ID）->平时成绩，（学号，课程ID）->期末成绩，（学号，课程ID）->总成绩}
3·5证明以上关系模式均符合第三范式
以学生档案表这一关系模式的数据依赖为例证明（其他五个模式的证明同下）：
{学号->姓名，学号->性别，学号->出生日期，学号->籍贯，学号->专业，学号->班级}中不存在部分函数依赖和传递函数依赖，所以满足第三范式。
3·6证明模式分解具有无损连接性
以学生档案表、课程信息表、和学生选课表三个模式为例证明该分解具有无损连接性（其他模式分解的无损连接性的证明同下）：
a、课程信息表的数据依赖：
{课程ID->课程名称，课程ID->课程类别，课程ID->学分，课程ID->学时}
d、学生档案表的数据依赖：
{学号->姓名，学号->性别，学号->出生日期，学号->籍贯，学号->专业，学号->班级}
e、学生选课表的数据依赖：
{（学号，课程ID）->课程类别}
建立初始表如表3-2所示：
学号
姓名
性别
出生日期
籍贯
班级
专业
课程ID
课程类别
课程名称
学时
学分
A1
A2
A3
A4
A5
A6
A7
B18
B19
B110
B111
B112
B21
B22
B23
B24
B25
B26
B27
A8
A9
A10
A11
A12
B31
B32
B33
B34
B35
B36
B37
B38
A9
B310
B311
B312
表3-2无损连接初始表
由以上三个模式的函数依赖关系变换表后结果如下表3-3所示：
学号
姓名
性别
出生日期
籍贯
班级
专业
课程ID
课程类别
课程名称
学时
学分
A1
A2
A3
A4
A5
A6
A7
A8
A9
A10
A11
A12
B21
B22
B23
B24
B25
B26
B27
A8
A9
A10
A11
A12
B31
B32
B33
B34
B35
B36
B37
B38
A9
B310
B311
B312
表3-3经函数依赖变换后的结果
4、物理设计阶段
根据数据库系统文件的存储结构，系统文件可以包含一个数据文件及一个事务日志，也可以包含多个数据文件及多个事务日志或文件组，创建指定数据文件和事务日志文件的“教学管理”数据库，即指定一个数据主文件和一个事务日志文件，并把它们安排在不同的硬盘上，要求主文件大小为5MB，最大值为50MB，文件增量为2MB，日志文件大小为5MB，最大值为100MB，文件增量为2MB。
5、数据库的实施和执行阶段
5·1创建“教学管理”数据库
在“查询分析器”窗口中，编写如下Transact-SQL语句：
createdatabase教学管理
on
primary
（name=教学管理_dat,
filename=’F:
\数据库\教学管理.mdf’,
size=5MB,
maxsize=50MB,
filegrowth=2MB
）
logon
（name=教学管理_log,
filename=’H:
\数据库\教学管理.ldf’,
size=5MB,
maxsize=100MB,
filegrowth=2MB
）
单击工具栏中的“运行”按钮（绿色小三角形）后，系统自动为主数据文件和事务日志文件分配要求的磁盘空间，运行结果如下图5-1所示：
图5-1建立教学管理数据库
5·2在“教学管理”数据库中建立课程信息表、教学任务表、学生档案表、教师档案表、学生选课表和学生成绩表：
使用Transact-SQL语句创建“课程信息表”、“教学任务表”、“教师档案表”、“学生档案表”、“学生选课表”、“学生成绩表”，“学号”是学生档案表的关键字，“课程ID”是课程信息表的关键字，而“学号”和“课程ID”共同组成了学生选课表的关键字，“教师ID”是教师档案表的关键字，同时也是教师档案表的外键，而教学任务表的关键字则是由“课程ID””年度”和“学期”共同组成的。
同时，“学生成绩表”中的“学号”和“课程ID”与“学生选课表”中的“学号”和“课程ID”是一一对应关系。
现在中以“课程信息表”的创建为例（其他五张表的建立方法类似）：
使用Transact-SQL语句创建“课程信息表”，该表包含的字段“课程ID”、“课程名称”、“课程类别”、“学分”、“学时”代码如下：
use教学管理
createtable课程信息表
（课程IDchar（6）,
课程名称varchar（40）,
课程类别char（8）,
学分real,
学时int）
结果预测：
命令已成功完成
可能出现的错误：
real和int类型后面加上了长度限制
错误分析：
系统在real和int类型会自动默认其长度限制
解决方法：
去掉建表代码中real和int后面的长度限制
实验步骤：
⑴在“SQL查询分析器”窗口的上部，选择“教学管理”数据库，并在查询框中输入代码建立“课程信息表”，如图5-21所示：
图5-21编写代码
⑵单击“SQL查询分析器”菜单栏中的绿色三角形按钮“执行”，结果如下图2-22所示：
图5-22执行结果
⑶在企业管理其中打开表，结果如下图5-23所示：
图5-23表结果
⑷创建索引
（1）使用Transact—SQL命令创建索引，在学生档案表的“姓名”字段上建立普通升序索引，在学生档案表的“学号”上建立唯一所引，在学生档案表的“学号”（升序）、“姓名”（升序）和“籍贯”（降序）三个子段上建立一个普通索引，还在学生成绩表的“学号”字段上建立聚集索引，具体步骤如下：
createindex姓名索引
on学生档案表（姓名）
createuniqueindex学号索引
on学生档案表（学号）
createindex多列索引
on学生档案表（学号,姓名,籍贯desc）
createclusteredindex学号索引on学生成绩表（学号）
结果预测：
命令已成功完成
可能出现的错误：
重复创建索引系统就会报错
错误分析：
在建表时就已经创建了索引
解决方法：
可以删掉先前建立的索引或者不再自行创建索引，就不会重复了
实验步骤：
①在SQL查询分析器在查询对话框中输入创建索引的命令代码如下：
1单击查询分析器界面上的绿色三角状“执行”，结果如下所示：
⑸、创建数据库表之间的关系图，具体步骤如下：
①建立完各表间的关系后，在企业管理器中层层展开“控制台根目录”，最后点开“教学管理”数据库，看到如下所示窗口：
②在“关系图”子项上单击右键，此时将弹出一个快捷菜单，选择“新建数据库关系图”，弹出如下所示界面：
3点击“下一步”，此时屏幕出现“选择要添加的表”窗口，从左边“可用的表”中选择之前新建的六张表，添加到右边的文本框中，得到如下图所示的窗口：
4单击“下一步”按钮，得到如下所示的窗口：
5单击“完成”按钮，得到新的关系图如下所示：
5·3表中数据的录入
使用Transact——SQL语句向表中插入记录，编写Transact——SQL语句，向课程信息表中插入10条记录（其他五张表的数据录入同下），具体操作如下：
⑴打开“SQL查询分析器”，在其窗口中编写如下代码：
insertinto课程信息表（课程ID,课程名称,课程类别,学分,学时）
values（'JB0001','马克思主义哲学原理','基础必修',2,32）
insertinto课程信息表values（'JB0002','毛泽东思想理论','基础必修',2,32）
insertinto课程信息表values（'JB0003','大学英语A','基础必修',4,64）
insertinto课程信息表values（'JB0004','高等数学A','基础必修',4,64）
insertinto课程信息表values（'ZB0001','管理运筹学','专业必修',,56）
insertinto课程信息表values（'ZB0002','管理学','专业必修',3,48）
insertinto课程信息表values（'XX0001','审计学','限选',2,32）
insertinto课程信息表values（'XX0002','货币银行学','限选',2,32）
insertinto课程信息表values（'RX0001','公共关系学','任选',2,32）
insertinto课程信息表values（'RX0002','广告学','任选',2,32）
⑵单击系统中的菜单栏上的绿色按钮“运行”，结果如下如5-31所示：
图5-31插入记录
⑶在企业管理器中展开课程信息表的记录，结果如下图5-32所示：
图5-32课程信息表记录
5·4数据的更新
1更新“课程信息表”中的课程ID为“RX0002”的记录，编写Transact-SQL语句，将该课程ID置为空，如下图5-41所示：
update课程信息表
set课程ID=null
where课程ID=’RX0002’
图5-41更新课程ID
以上的更新出现错误信息的原因是“课程信息表”中的课程ID是主键，它与其他表如“学生选课表”等的外键有连接，而主键不能为空，不能破坏数据的完整性定义。
2编写Transact-SQL语句，更新“学生成绩表”中的总成绩，总成绩的计算公式为：
平时成绩*+期末成绩*（），具体代码如下：
update学生成绩表
set总成绩=（平时成绩*+期末成绩*）
点击“运行”按钮后，产生结果如下如5-42所示：
图5-42更新总成绩
5·5数据删除
⑴删除选择了“高等数学A”的所有学生该课程成绩，具体代码如下：
deletefrom学生成绩表
where学号in
（select学号
from学生选课表
where课程IDin
（select课程ID
from课程信息表
where课程名称=’高等数学A’））
点击“运行”按钮后，产生结果如下图5-51所示：
图5-51删除选修高等数学A的学生学号
利用企业管理器打开学生成绩表结果发现已删除选择了“高等数学A”的所有学生，结果如下图5-52所示：
图5-52删除后的结果
5·6数据查询
⑴利用存在量词EXISTS查询没有重修课程的学生姓名，在查询分析器中输入如下代码：
select姓名
from学生档案表
wherenotEXISTS
（select*
from学生选课表
where课程类别='重修'and学生档案表.学号=学生选课表.学号）
点击“运行”按钮后，产生结果如下图5-61所示：
图5-61查询后结果
5·7视图的建立、更新、删除、查询
⑴建立一个基于“学生档案表”和“学生成绩表”按照嵌套查询操作定义的视图“学生成绩优秀表”，使之包括学生的学号、姓名、性别、专业、班级、课程ID和总成绩字段。
具体步骤如下：
在查询分析器中编写如下代码：
createview学生成绩优秀表as
select学生档案表.学号,姓名,性别,专业,班级,学生成绩表.课程ID,总成绩
from学生档案
升级会员 