教学管理 数据库设计

实验报告课程名称： 数据库原理 实验项目名称： 数据库设计 班级与班级代码： 实验室名称（或课室）： 专 业： 任课教师： 学 号： 姓 名： 实验日期： 年 月 日广东商学院教务处 制 姓名 实验报告成绩 评语：评分项目评分内容分值评分结果预习情况是否完成预习工作。要求按实验指导书进行预习、并完成实验报告初稿、编写程序。20实验方案实验方案是否可行。要求目的、任务明确、原理清楚、步骤可操作、对实验过程数据有记录方案、对可能的错误有预备方案。30实验过程与结果实验结果是否正确。要求完成实验过程中的数据记录、记录要完整和准确、内容要详细，实验结果要与预期的正确结果一致。35实验结论实验结论是否正确。要求根据实验原理对实验过程中的数据、实验结果进行分析，得出实验结论。15总 分100 指导教师（签名） 年 月 日说明：指导教师评分后，实验报告交院（系）办公室保存。 实验六 教学管理数据库设计一、实验目的1、数据库设计就是要使学生采用本课程中学习的数据库设计方法，运用其基本思路与主要图表工具完成一个自己所了解的业务的数据库应用系统信息需求分析与数据库的概念设计、逻辑设计、物理设计以及处理功能设计2、熟悉数据库管理系统、SQL语言开发和实现该数据库，并测试、评价、优化改进。3、通过本设计进一步弄懂数据库系统及其相关的基本概念，理解数据库系统的系统结构、主要特点，掌握数据库设计的原理、方法及其基本过程，初步具备数据库应用设计的能力，形成对数据库设计在处理实际问题的基本素质。二、实验原理根据数据库设计的方法和步骤，进行需求分析、概念结构设计、逻辑结构设计、物理设计以及进行规范化、无损连接和优化改进等处理，再根据数据操作的原理和语法测试数据库的功能。三、实验要求完成对教学管理数据库的以下操作1、需求分析：功能分析、总框架图、业务流程图、数据流图、数据字典2、概念模型设计(E-R图)3、逻辑设计（表、要验证函数依赖、规范化、无损连接）4、物理设计5、数据库实施和执行阶段（数据库的建立、数据录入、插入、删除、查询、更新、查询优化等）四、实验设备微型计算机一台、WinXP和Microsoft SQL Server 2000软件五、结果预测成功完成教学管理数据库的设计过程，各种操作顺利执行。六、实验内容和步骤1、需求分析阶段11教学管理数据库的功能分析：教学管理数据库要实现对学生基本信息、学生选课信息、课程基本信息、教师基本信息、学生课程成绩及教师教务信息的管理12教学管理系统的总框架图如下图1-1所示： 图1-1 总框架图1.3教学管理系统业务流程图如下图1-2所示： 7统计处 理教务处统计数据库 1学 籍处 理教务处学生信息记录学生选课和课程信息记录学生学生登记表 5选课学生 6成绩管 理教务处 2教师基本信息管理教务处教师信息表学校部门教师信息记录 4排课教务处课表信息记录学校组织课程信息记录教师计划 3制定教学计划教务处教师任课信息表院系教师任课记录学生课表教师任课课表教师学生 图1-2 业务流程图14 调查用户需求： 教务处需求 学籍信息处理： 新生入学档案信息的录入 毕业学生档案信息的处理 学生档案的处理包括插入，删除以及修改 学生基本信息的查询教师信息系统的管理：教师档案的录入教师基本信息的查询教师基本信息的更新包括修改，添加和修改制定教学计划：根据有关部门提供的教师任课信息表制定教学计划把教学计划输入课程管理数据库和教师任课数据库排课：根据教学计划为学生排课每个学生可以查询自己的课程教务处可以根据教学计划修改课程表将课程表分发给老师，学生，教室管理员和院系统计：统计学生的成绩情况、男女人数、选课情况等 学生查询功能：查询自己的基本信息查询考试成绩及排名查询课程信息查询任课老师的信息教师查询：查询自己的基本信息查询课程表查询学生的总体成绩15数据字典经分析，该系统要用到六个基本表：课程信息表、教学任务表、教师档案表、学生档案表、学生选课表、学生成绩表，数据结构定义如下表1-3所示：数据结构名含义说明组成课程信息表定义了课程的有关信息课程ID、课程名称、课程类别、学分、学时教学任务表定义了教师教授学生的有关信息课程ID、教师ID、学期、年度教师档案表定义了教师的有关信息教师ID、姓名、性别、职称、单位学生档案表定义了学生的有关信息学号、姓名、性别、出生日期、籍贯、专业、班级学生选课表定义了选课的基本信息学号、课程类别、课程ID学生成绩表定义了学生的成绩的相关信息学号、课程ID、平时成绩、期末成绩、总成绩 表1-3 数据结构说明经分析，数据项定义如下表1-5所示：数据项名类别长度说明课程IDchar6前2个字符大写字母，表示课程类别，后面4个字符为数字，表示课程编号课程名称varchar40表示课程的名称课程类别char8包含基础必修，专业必修，限选和任选学分real40.5=学分=5.0学时int48的整数倍，每16个学时为一个学分教师IDchar8教师的号码，8位数字学期int4只能选择1或2年度char101990=年度=1970籍贯datatime8学生出生地专业varchar16学生所在专业班级int4学生所在班级平时成绩real 4小数点后一位期末成绩real4小数点后一位总成绩real4小数点后一位，计算公式：总成绩=平时成绩*（1-a%）+期末成绩*a% 表1-4 数据项说明16数据流图学生选课表学生选课验证信息身份信息更新、修改、删除、增加信息信息维 护基本信息处理身份信息课程信息表验证信息教师教师档案表教学任务表查询需求学生成绩表信 息查 询学生档案表2、概念结构设计阶段（E-R图设计）经过分析数据字典和数据流图设计ER图如下图2-1所示：学期姓名教师ID教学m教师年度单位职称性别课程类别n课程ID学分课程学时课程名称b学号姓名性别出生日期1班级选修学生a课程类别专业籍贯总成绩1成绩统计期末成绩平时成绩 图2-1 ER图3、逻辑结构设计阶段31 由ER图向关系模型的转换如下：学生（学号，姓名，性别，出生日期，籍贯，专业，班级）课程（课程ID，课程名称，课程类别，学分，学时）教师（教师ID，姓名，性别，职称，单位）选课（学号，课程ID，课程类别）成绩（学号，课程ID，平时成绩，期末成绩，总成绩）教务（课程ID，年度，学期，教师ID）32 各张表的逻辑结构设计如下所示：“课程信息表”结构字段名类型长度备注课程IDChar6 主键，前2个字符大写字母，表示课程类别，后面4个字符为数字，表示课程编号课程名称Varchar40课程类别Char8包含基础必修，专业必修，限选和任选学分Real40.5=学分=5.0学时int48的整数倍，每16个学时为一个学分“教学任务表”结构字段名类型长度备注课程IDChar6主键，前2个字符大写字母，表示课程类别，后面4个字符为数字，表示课程编号教师IDChar8教师档案表的外键，8位数字学期Int4只能选择1或2年度char101990=年度=1970籍贯Datatime8专业Varchar16班级Int4“学生选课表”结构字段名类型长度备注学号Char8主键，由八位数字组成课程类别Char8包括正选、旁听、重修课程IDchar6课程信息表和教学任务表的外键，前2个字符大写字母，表示课程类别，后面4个字符为数字，表示课程编号“学生成绩表“结构字段名类型长度备注学号Char8主键，由八位数字组成课程IDChar6前2个字符大写字母，表示课程类别，后面4个字符为数字，表示课程编号平时成绩Real 4小数点后一位期末成绩Real4小数点后一位总成绩real4小数点后一位，计算公式：总成绩=平时成绩*（1-a%）+期末成绩*a%33 以上六张表有如下逻辑依赖关系，表现出各表间主键与外键的关联关系，如图3-1所示：学号姓名性别出生日期籍贯专业班级课程ID课程名称课程类别学分 学时 教师ID姓名性别职称单位学号课程ID课程类别 课程ID年度学期教师ID学号课程ID平时成绩期末成绩总成绩 图3-1 表的逻辑关系图34 根据需求分析和以上的逻辑关系图确定数据依赖。分别写出每个关系内部属性的以及不同关系模式属性间的数据依赖如下：a、课程信息表的数据依赖：课程ID-课程名称，课程ID-课程类别，课程ID-学分，课程ID-学时b、教学任务表的数据依赖：（课程ID，年度，学期）-教师IDc、教师档案表的数据依赖：教师ID-姓名，教师ID-性别，教师ID-职称，教师ID-单位d、学生档案表的数据依赖：学号-姓名，学号-性别，学号-出生日期，学号-籍贯，学号-专业，学号-班级e、学生选课表的数据依赖：（学号，课程ID）-课程类别f、学生成绩表的数据依赖：（学号，课程ID）-平时成绩，（学号，课程ID）-期末成绩，（学号，课程ID）-总成绩35 证明以上关系模式均符合第三范式以学生档案表这一关系模式的数据依赖为例证明(其他五个模式的证明同下)：学号-姓名，学号-性别，学号-出生日期，学号-籍贯，学号-专业，学号-班级中不存在部分函数依赖和传递函数依赖，所以满足第三范式。36 证明模式分解具有无损连接性以学生档案表、课程信息表、和学生选课表三个模式为例证明该分解具有无损连接性（其他模式分解的无损连接性的证明同下）：a、课程信息表的数据依赖：课程ID-课程名称，课程ID-课程类别，课程ID-学分，课程ID-学时d、学生档案表的数据依赖：学号-姓名，学号-性别，学号-出生日期，学号-籍贯，学号-专业，学号-班级e、学生选课表的数据依赖：（学号，课程ID）-课程类别建立初始表如表3-2所示：学号姓名性别出生日期籍贯班级专业课程ID课程类别课程名称学时学分A1A2A3A4A5A6A7B18B19B110B111B112B21B22B23B24B25B26B27A8A9A10A11A12B31B32B33B34B35B36B37B38A9B310B311B312 表3-2 无损连接初始表由以上三个模式的函数依赖关系变换表后结果如下表3-3所示：学号姓名性别出生日期籍贯班级专业课程ID课程类别课程名称学时学分A1A2A3A4A5A6A7A8A9A10A11A12B21B22B23B24B25B26B27A8A9A10A11A12B31B32B33B34B35B36B37B38A9B310B311B312 表3-3 经函数依赖变换后的结果4、物理设计阶段根据数据库系统文件的存储结构，系统文件可以包含一个数据文件及一个事务日志，也可以包含多个数据文件及多个事务日志或文件组，创建指定数据文件和事务日志文件的“教学管理”数据库，即指定一个数据主文件和一个事务日志文件，并把它们安排在不同的硬盘上，要求主文件大小为5MB，最大值为50MB，文件增量为2MB，日志文件大小为5MB，最大值为100MB，文件增量为2MB。5、数据库的实施和执行阶段51 创建“教学管理”数据库在“查询分析器”窗口中，编写如下Transact-SQL语句：create database 教学管理onprimary(name=教学管理_dat,filename= F:数据库教学管理.mdf,size=5MB,maxsize=50MB,filegrowth=2MB)log on(name=教学管理_log,filename= H:数据库教学管理.ldf,size=5MB,maxsize=100MB,filegrowth=2MB)单击工具栏中的“运行”按钮（绿色小三角形）后，系统自动为主数据文件和事务日志文件分配要求的磁盘空间，运行结果如下图5-1所示： 图5-1 建立教学管理数据库52 在“教学管理”数据库中建立课程信息表、教学任务表、学生档案表、教师档案表、学生选课表和学生成绩表：使用Transact-SQL语句创建“课程信息表”、“教学任务表”、“教师档案表”、“学生档案表”、“学生选课表”、“学生成绩表”，“学号”是学生档案表的关键字，“课程ID”是课程信息表的关键字，而“学号”和“课程ID”共同组成了学生选课表的关键字，“教师ID”是教师档案表的关键字，同时也是教师档案表的外键，而教学任务表的关键字则是由“课程ID”年度”和“学期”共同组成的。同时，“学生成绩表”中的“学号”和“课程ID”与“学生选课表”中的“学号”和“课程ID”是一一对应关系。现在中以“课程信息表”的创建为例（其他五张表的建立方法类似）：使用Transact-SQL语句创建“课程信息表”，该表包含的字段“课程ID”、“课程名称”、“课程类别”、“学分”、“学时”代码如下：use 教学管理create table 课程信息表(课程ID char(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)使用TransactSQL命令创建索引，在学生档案表的“姓名”字段上建立普通升序索引，在学生档案表的“学号”上建立唯一所引，在学生档案表的“学号”（升序）、“姓名”（升序）和“籍贯”（降序）三个子段上建立一个普通索引，还在学生成绩表的“学号”字段上建立聚集索引，具体步骤如下：create index 姓名索引 on 学生档案表(姓名)create unique index 学号索引 on 学生档案表(学号)create index 多列索引 on 学生档案表(学号,姓名,籍贯 desc)create clustered index 学号索引 on 学生成绩表(学号)结果预测：命令已成功完成可能出现的错误：重复创建索引系统就会报错 错误分析：在建表时就已经创建了索引解决方法：可以删掉先前建立的索引或者不再自行创建索引，就不会重复了实验步骤：在SQL查询分析器在查询对话框中输入创建索引的命令代码如下： 单击查询分析器界面上的绿色三角状“执行”，结果如下所示：、创建数据库表之间的关系图，具体步骤如下：建立完各表间的关系后，在企业管理器中层层展开“控制台根目录”，最后点开“教学管理”数据库，看到如下所示窗口：在“关系图”子项上单击右键，此时将弹出一个快捷菜单，选择“新建数据库关系图”，弹出如下所示界面： 点击“下一步”，此时屏幕出现“选择要添加的表”窗口，从左边“可用的表”中选择之前新建的六张表，添加到右边的文本框中，得到如下图所示的窗口： 单击“下一步”按钮，得到如下所示的窗口： 单击“完成”按钮，得到新的关系图如下所示：53 表中数据的录入使用TransactSQL语句向表中插入记录，编写TransactSQL语句，向课程信息表中插入10条记录（其他五张表的数据录入同下），具体操作如下：打开“SQL查询分析器”，在其窗口中编写如下代码：insert into 课程信息表 (课程ID,课程名称,课程类别,学分,学时)values(JB0001,马克思主义哲学原理,基础必修,2,32)insert into 课程信息表values(JB0002,毛泽东思想理论,基础必修,2,32)insert into 课程信息表values(JB0003,大学英语A,基础必修,4,64)insert into 课程信息表values(JB0004,高等数学A,基础必修,4,64)insert into 课程信息表values(ZB0001,管理运筹学,专业必修,3.5,56)insert into 课程信息表values(ZB0002,管理学,专业必修,3,48)insert into 课程信息表values(XX0001,审计学,限选,2,32)insert into 课程信息表values(XX0002,货币银行学,限选,2,32)insert into 课程信息表values(RX0001,公共关系学,任选,2,32)insert into 课程信息表values(RX0002,广告学,任选,2,32)单击系统中的菜单栏上的绿色按钮“运行”，结果如下如5-31所示： 图5-31 插入记录在企业管理器中展开课程信息表的记录，结果如下图5-32所示： 图5-32 课程信息表记录54 数据的更新1 更新“课程信息表”中的课程ID为“RX0002”的记录，编写Transact-SQL语句，将该课程ID置为空，如下图5-41所示：update 课程信息表set 课程ID=nullwhere 课程ID=RX0002 图5-41 更新课程ID以上的更新出现错误信息的原因是“课程信息表”中的课程ID是主键，它与其他表如“学生选课表”等的外键有连接，而主键不能为空，不能破坏数据的完整性定义。2 编写Transact-SQL语句，更新“学生成绩表”中的总成绩，总成绩的计算公式为：平时成绩*0.3+期末成绩*（1-0.3），具体代码如下：update 学生成绩表set 总成绩=(平时成绩*0.3+期末成绩*(1-0.3)点击“运行”按钮后，产生结果如下如5-42所示： 图5-42 更新总成绩55 数据删除删除选择了“高等数学A”的所有学生该课程成绩，具体代码如下：delete from 学生成绩表where 学号 in(select 学号from 学生选课表where 课程ID in(select 课程ID from 课程信息表where 课程名称=高等数学A)点击“运行”按钮后，产生结果如下图5-51所示： 图5-51 删除选修高等数学A的学生学号利用企业管理器打开学生成绩表结果发现已删除选择了“高等数学A”的所有学生，结果如下图5-52所示： 图5-52 删除后的结果56 数据查询利用存在量词EXISTS查询没有重修课程的学生姓名，在查询分析器中输入如下代码：select 姓名from 学生档案表where not EXISTS (select *from 学生选课表where 课程类别=重修 and 学生档案表.学号=学生选课表.学号)点击“运行”按钮后，产生结果如下图5-61所示： 图5-61 查询后结果57 视图的建立、更新、删除、查询建立一个基于“学生档案表”和“学生成绩表”按照嵌套查询操作定义的视图“学生成绩优秀表”，使之包括学生的学号、姓名、性别、专业、班级、课程ID和总成绩字段。具体步骤如下：在查询分析器中编写如下代码：create view 学生成绩优秀表 asselect 学生档案表.学号,姓名,性别,专业,班级,学生成绩表.课程ID,总成绩from 学生档案表,学生成绩表where 学生档案表.学号=学生成绩表.学号 and 学生档案表.学号 in(select 学号from 学生成绩表where 总成绩=85)然后，单击系统中的运行按钮，结果如图5-71所示： 图5-71 建立视图视图的更新，将原来建立的“课程信息简表”视图中的“课程ID”为“RX0002”的“广告学”改为“逻辑学”。具体步骤如下：在查询分析器中编写如下代码：update 课程信息简表set 课程名称=逻辑学where 课程ID=RX0002然后，单击系统中的运行按钮，结果如图5-72所示： 图5-72 更新视图视图的删除，删除视图“课程信息简表”，具体步骤如下：在查询分析器中编写如下代码：drop view 课程信息简表然后，单击系统中的运行按钮，结果如图5-73所示： 图5-73 删除视图视图的查询，查询视图“学生成绩优秀表”中的所有记录，具体步骤如下：在查询分析器中编写如下代码：select * from 学生成绩优秀表然后，单击系统中的运行按钮，结果如图5-74所示： 图5-74 视图查询58 存储过程的创建和执行在企业管理器中找到“教学管理”数据库，打开其存储过程新建存储过程，在新建窗口中编写代码如下图5-81所示： 图5-81然后单击确定，再在查询分析器中执行该存储过程，在查询分析器中编写如下代码：execute pro_select_score1单击系统中的运行按钮，结果如图5-82所示： 图5-82 执行存储过程结果59 触发器的创建创建触发器trigger_ins_score，使得当“平时成绩”和“期末成绩”更新后就能自动更新总成绩，具体步骤如下：在企业管理器中找到“教学管理”数据库中的“学生成绩表”，选中表单击右键，打开“所有任务”的子菜单，选中“管理触发器”，打开后，建立插入触发器“trigger_ins_score”,编写相应代码后单击确定保存，如图5-91所示： 图5-91 触发器建立510 创建用户和权限管理在查询分析器中编写程序，创建一个新的登录用户，登录名为“MyUser3”,密码为空，登录后连接的数据库为“教学管理”数据库。编写代码运行后结果如下图5-101所示：sp_addlogin MyUser3,null,教学管理 图5-101 创建新登录用户在查询分析器中编写程序，给以上用户授予创建表和查询表的权限。运行后结果如图5-102所示：grant create table to MyUser3grant select on 教师档案表 to MyUser3 图5-102 授权给新建用户七、实验分析1、在需求分析阶段通过对教学管理系统的功能分析、总框架图、业务流程图、数据流图和数据字典的设计，其中在功能分析阶段不能凭空想象一下系统的功能就可以的了，要通过统计调查客户的需要，这样所得到的功能概况才更加详细，才能做到满足更多的用户需求。在总框架图设计时，结合功能分析和顾客需求来设计总的框架图，这样才能为接下来的数据库设计途径提供总体的依据。接着根据总框架图逐步细分系统的功能模块，设计系统的业务流程图，在此时必须仔细分析每个流程的步骤和衔接关系，不然对接下来的数据流图的设计会产生影响，所以每一步都至关重要。然后根据业务流程图分析和得出数据字典，接着根据数据字典和业务流程图设计数据流图，更进一步抽象出外部实体的关系流图。在整个设计阶段，每一步的联系都是非常紧密的，一步除错了都随时可能对下面的设计造成很大的影响，所以要特别仔细和详细地做好需求分析。2、在数据流图设计好后，接着进行概念模型的设计(E-R图)，在数据流图中抽象出实体和联系，分析各实体间的关系，是一对一、多对多还是一对多关系，并通过细分数据流图来设计分E_R图，然后再将分E-R图组合成完整的E-R图，消除其中的冲突和不必要的冗余。3、在逻辑结构设计阶段，根据E-R图将概念结构转换为一般的关系模型，然后将关系模型想特定的数据模型转换，在对数据模型进行优化。在E-R图的基础上将实体型和实体间的联系转换为关系模式，再确定他们的属性和码，这是比较难的一个问题。对于一对一模式有两种对应模式：一是独立模式，即与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，每个实体的码均是该关系的候选码；二是与某一端的实体对应的关系模式合并，就需要在该关系模式的属性中加入另一个关系模式的码和联系本身的属性。对于一对多的联系，也同样有两个方案，一是独立模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为N端实体的码（如课程和成绩的联系）。而对于多对多联系，只有一种方案，与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，各实体的码组成关系的码或关系码的一部分，例如教学管理数据库中的学生和课程间的选修关系，其关系模式为：选课（学号，课程ID，课程类别）。然后根据关系模式和数据字典设计逻辑关系表结构，并验证函数的依赖，可以根据函数依赖的定义来证明。接着在函数依赖的基础上根据判定关系模式符合第几范式的定理来证明各个关系模式是第几范式。最后根据证明模式分解的无损连接性的图表算法来证明模式的分解是无损连接的，如实验中的关系模式的分解例子：a、课程信息表的数据依赖：课程ID-课程名称，课程ID-课程类别，课程ID-学分，课程ID-学时d、学生档案表的数据依赖：学号-姓名，学号-性别，学号-出生日期，学号-籍贯，学号-专业，学号-班级e、学生选课表的数据依赖：（学号，课程ID）-课程类别4、在物理设计阶段，要通过计算需求用户和数据类型中总的存储数据的大小来设计数据库物理设计的存储结构和存取方法，在本数据库设计中对于关于模式采取的是索引的存取方法。5、在本实验中，在数据库实施和维护阶段中：、使用Transact-SQL语句创建数据库时，要注意数据主文件、日志事务文件和文件组间的关系，且在创建的过程中不仅可以按照实验中那样采用规定数值增长数据文件的存储空间，还可以采用另一种方式以百分比的形式来增长数据文件的存储空间，具体Transact-SQL语句如下红色部分所示：create database 教学管理onprimary(name=教学管理_dat,filename= F:数据库教学管理.mdf,size=5MB,maxsize=50MB,filegrowth=10%)log on(name=教学管理_log,filename= H:数据库教学管理.ldf,size=5MB,maxsize=100MB,filegrowth=15%)、还有在创建数据库前要根据实际需要做到准确估算数据库的存储空间的大小，以防太大造成浪费或太小而导致存储空间不足。、实验中