数据库设计实例 教务管理系统
数据库实验七 数据库设计
题目要求:
设计一个教务管理系统。要求管理包括:学生的档案,学生选课的情况,学生每学期的综合测评,教师档案,教师工作量等等。具体考核方法根据自己了解的实际情况处理。
假设此次设计的教务系统是整个学生管理系统数据库中的一部分,整个系统至少还包括学籍管理等其他子系统。
在初期设计时,先关注与教务系统有关的数据。在完成对教务管理系统的建模后再对整个系统进行合并,总终得到整个系统的设计方案。
一、需求分析
1、根据分析可以画出该教务管理系统的数据流图:
学生 选课 上课 考试 测评 选课单 允许考试 成绩单 老师档案 课程简介 教室安排 试卷 考场安排 测评报告单 学生名单 教师 选定教材 申报 课表
2、数据字典
数据结构:学生
含义说明:是教务管理系统的一个核心数据结构,定义了学生的有关信息
组成:学号、姓名
数据项:学号
含义说明:唯一标识每一个学生
别名:学生编号 类型:字符型 长度:9位
取值范围:190000000 – 999999999
取值含义:前四位表明学生所在年级,后五位安顺序编号
数据项:姓名
含义说明:表示学生的姓名 类型:字符型 长度:10位
取值范围:任意字符
数据结构:教师
含义说明:教务管理系统的核心数据结构之一,定义了教师的有关信息 组成:教师号,教师姓名
数据项:教师号
含义说明:教师的编号 类型:字符型 长度:5位
取值范围:10000 – 99999
数据项:教师姓名
含义说明:教师的姓名 类型:字符型 长度:10位
取值范围:任意合法的字符
数据结构:课程
含义说明:教务管理系统的核心数据结构之一,定义了课程的有关信息 组成:课程号,课程名,课程描述,教师,教室,教科书,学分
数据项:课程号
含义说明:课程的编号 类型:字符型 长度:4
取值范围:0001 – 9999
数据项:课序号
含义说明:对于同一课程,由不同老师教授,用课序号来区分 类型:字符型 长度:2
取值范围:01 – 99
数据项:课程名
含义说明:课程的名称 类型:字符型 长度:10
取值范围:任意合法的字符
数据项:课程描述
含义说明:课程内容的基本描述 类型:字符型 长度:200
取值范围:任意合法字符
数据项:教科书
含义说明:课程所用的教科书 类型:字符型 长度:20
取值范围:任意合法字符
数据项:学分
含义说明:课程的学分 类型:整型 长度:1
取值范围:1-9
数据结构:教室
含义说明:上课所用的教室
组成:教室编号,教室地址,教室容量
数据项:教室编号
含义说明:教室的编号 类型:字符 长度:5
取值范围:00001-99999
数据项:教室地址
含义说明:标明教室的地址 类型:字符 长度:30
取值范围:任意合法的字符
数据项:教室容量
含义说明:教室的容纳学生的数量 类型:整型
取值范围:正整数
数据结构:选课记录
含义说明:纪录学生选课的纪录 组成:学生,课程,成绩
数据项:成绩
含义说明:选课学生本学期的成绩
类型:浮点数
范围:0.0 – 100.0
处理过程:分配教室
说明:为所有课程分配上课教室 输入:课程,教室 输出:教室安排
处理:学期课程决定后,要为每个课程安排上课地点,要求上课的地点不能冲突且上课人数不能超过教室容量。
处理过程:分配考场
说明:在考试日期,为所有课程分配考场
输入:课程,教室
输出:考场安排
处理:为每个课程安排考场,考试时间和地点不能冲突且考试人数不能超过考场容量。
数据存储:成绩单
说明:记录学生本学期所有课程成绩 流入数据流:学生所有的选课记录 流出数据流:学生的成绩单 数据量:由学生的人数决定 存取方式:随机存取
二、概念结构设计
1、根据需求分析画出E-R图:
选修 学生 教师 课程 m n 讲授 1 n 教科书 n
成绩 教室 开设 1 n
2、根据E-R图写出各个实体的属性描述
学生:{学号,姓名}
课程:{课程号,课程名,课程描述,学分} 教师:{教师号,教师名,课程数} 教室:{教室编号,地址,容量}
3、视图的集成
假设在学生管理系统的学籍管理系统中存在另一个学生实体,其中还包括以下信息:性别,所属专业,所属班级。为了使两个实体保持一致,对两个实体取并集得到新的学生实体: 学生:{学号,姓名,性别,专业,班级}
三、逻辑结构设计
1、由E-R图向数据模型的转换
一个实体型转换为一个关系模式。实体的属性就是关系的属性。实体的码就是关系的码。所以,E-R图中的属性描述可直接转换为相应的关系模式。
2、对于剩余的一对多和多对多联系可以如下表示
学生-课程:{学号,课程号,课序号,成绩} 课程-教师:{课程号,课序号,教师号} 课程-教科书:{课程号,课序号,教科书}
教室-课程:{教室号,时间,课程号,课序号}
其中 课程-教师 与 课程-教科书 具有相同的主码,可以合并成一个关系 教学:{课程号,课序号,教师号,教科书}
最后得到的关系模式为
学生:{学号,姓名,性别,专业,班级} 课程:{课程号,课程名,课程描述,学分} 教师:{教师号,教师名,课程数} 教室:{教室号,地址,容量}
学生-课程:{学号,课程号,课序号,成绩} 教学:{课程号,课序号,教师号,教科书} 教室-课程:{教室号,时间,课程号,课序号}
3、用户外模式
根据需求,为计算综合测评增加一个外模式,因为综合测评只在学期末进行,查询次数不多,所以没有必要用冗余的方法来提高查询效率,只需要建立一个外模式视图即可。
测评{学号,学生姓名,平均成绩}
4、根据设计写出相应的SQL语句:
CREATE TABLE student (
Sno
VARCHAR(9)
CONSTRAINT pk_student PRIMARY KEY,
Sname
VARCHAR(10) NOT NULL,
Sex
VARCHAR(1)
NOT NULL,
Major
VARCHAR(10)
NOT NULL,
Cla
VARCHAR(10)
NOT NULL,
CHECK
(Sex = \'f\' OR Sex = \'m\') )
CREATE TABLE course (
Cno
VARCHAR(4)
CONSTRAINT pk_course PRIMARY KEY,
Cname
VARCHAR(10)
NOT NULL,
Describe
VARCHAR(200) ,
Mark
INTEGER
)
CREATE TABLE teacher (
Tno
VARCHAR(5)
CONSTRAINT teacher_pk PRIMARY KEY,
Tname
VARCHAR(10)
NOT NULL,
Ccnt
INTEGER
NOT NULL DEFAULT 0 )
CREATE TABLE claroom (
Rno
VARCHAR(5) CONSTRAINT pk_claroom PRIMARY KEY,
Locate
VARCHAR(30) NOT NULL,
Cap
INTEGER CHECK
(Cap > 0) )
CREATE TABLE tech (
Cno
VARCHAR(4) NOT NULL CONSTRAINT fk_course_cno FOREIGN KEY REFERENCES course(Cno) ON UPDATE CASCADE
--级联更新
ON DELETE CASCADE, --级联删除
SCno
VARCHAR(2) NOT NULL,
Tno
VARCHAR(5) CONSTRAINT fk_teacher_tno FOREIGN KEY REFERENCES teacher(Tno)
ON UPDATE CASCADE
ON DELETE CASCADE,
Book
VARCHAR(20)
CONSTRAINT pk_tech PRIMARY KEY (Cno,SCno) )
CREATE TABLE SC (
Sno
VARCHAR(9) NOT NULL CONSTRAINT fk_student_sno FOREIGN KEY REFERENCES student(Sno)
ON UPDATE CASCADE
ON DELETE CASCADE,
Cno
VARCHAR(4) NOT NULL CONSTRAINT fk_sc_course_cno FOREIGN KEY REFERENCES course(Cno)
ON UPDATE CASCADE
ON DELETE CASCADE,
SCno
VARCHAR(2) NOT NULL,
Score FLOAT
CONSTRAINT pk_sc PRIMARY KEY (Sno,Cno,SCno) )
CREATE TABLE RC (
Rno
VARCHAR(5) CONSTRAINT fk_claroom_rno FOREIGN KEY REFERENCES claroom(Rno)
ON UPDATE CASCADE
ON DELETE CASCADE,
Time
DATETIME,
Cno
VARCHAR(4) NOT NULL CONSTRAINT fk_rc_course_cno FOREIGN KEY REFERENCES course(Cno)
ON UPDATE CASCADE
ON DELETE CASCADE,
SCno
VARCHAR(2) NOT NULL
CONSTRAINT pk_rc PRIMARY KEY (Rno,Time)
)
为进行综合测评增加一个外模式 CREATE VIEW ae AS
SELECT SC.sno, sname, AVG(score) as average FROM SC,Student where SC.sno = student.sno
GROUP BY SC.sno,sname
为了符合数据的参照完整性约束,避免学生选课的时候选择一个不存在的课程号-课序号组,增加一个外键约束。 ALTER TABLE sc
ADD CONSTRAINT fk_sc_tech FOREIGN KEY (Cno,SCno) REFERENCES tech(Cno,SCno)
ON UPDATE CASCADE
ON DELETE CASCADE
为了在插入一个新课程时能级联更新教师的工作量,增加一个触发器。此任务也可以通过建立一个外模式视图来动态统计,为了练习的目的这里采用了触发器
CREATE TRIGGER workcnter On tech AFTER INSERT ,UPDATE, DELETE AS BEGIN
DECLARE @T VARCHAR(5)
IF EXISTS
(
SELECT TOP 1 Tno FROM INSERTED
)
BEGIN
SET @T = (SELECT TOP 1 Tno FROM INSERTED )
UPDATE teacher SET Ccnt = Ccnt + 1 WHERE Tno = @T
END
IF EXISTS
(
SELECT TOP 1 Tno FROM DELETED
)
BEGIN
SET @T = (SELECT TOP 1 Tno FROM DELETED)
UPDATE teacher SET Ccnt = Ccnt1 WHERE Tno = @T END END 关于课程容量:如何保证选课的学生数量不超过教室的容量?
初步解决办法是:在学生选课时建立暂时不考虑容量问题,都加渗透一个临时的SC表中。然后,在选课结束后将未超额的记录直接加渗透SC表,超额的课程进行抽签,将命中部分学生的选课记录插入SC表,直到人数符合课程容量的要求为止。
抽签的功能较适合用顶层的高级语言来实现。在选课结束后,选超额课程的学生需登陆选课系统进行抽签,抽签的过程可以使用概率算法,如果抽中,系统再将选课记录插入SC表,否则就从临时表中删除。 所以,在数据库系统中,没有对SC表的插渗入渗出操作进行约束。
本文来自CSDN博客,转载请标明出处: http://blog.csdn.net/lesky/archive/2006/12/25/1460 966.aspx
数据库设计---教务管理系统
2009年12月03日 星期四 11:04 P.M. 题目要求:
设计一个教务管理系统。要求管理包括:学生的档案,学生选课的情况,学生每学期的综合测评,教师档案,教师工作量等等。具体考核方法根据自己了解的实际情况处理。
假设此次设计的教务系统是整个学生管理系统数据库中的一部分,整个系统至少还包括学籍管理等其他子系统。
在初期设计时,先关注与教务系统有关的数据。在完成对教务管理系统的建模后再对整个系统进行合并,总终得到整个系统的设计方案。
一、需求分析
1、根据分析可以画出该教务管理系统的数据流图: 学生
选课
上课
考试
测评
选课单
允许考试
成绩单
老师档案
课程简介
教室安排
试卷
考场安排
测评报告单 学生名单
教师
选定教材
申报
课表
2、数据字典 数据结构:学生
含义说明:是教务管理系统的一个核心数据结构,定义了学生的有关信息 组成:学号、姓名 数据项:学号
含义说明:唯一标识每一个学生 别名:学生编号 类型:字符型 长度:9位
取值范围:190000000 – 999999999
取值含义:前四位表明学生所在年级,后五位安顺序编号 数据项:姓名
含义说明:表示学生的姓名 类型:字符型 长度:10位 取值范围:任意字符
数据结构:教师 含义说明:教务管理系统的核心数据结构之一,定义了教师的有关信息
组成:教师号,教师姓名
数据项:教师号
含义说明:教师的编号
类型:字符型
长度:5位
取值范围:10000 – 99999
数据项:教师姓名
含义说明:教师的姓名
类型:字符型
长度:10位
取值范围:任意合法的字符
数据结构:课程
含义说明:教务管理系统的核心数据结构之一,定义了课程的有关信息 组成:课程号,课程名,课程描述,教师,教室,教科书,学分
数据项:课程号 含义说明:课程的编号 类型:字符型 长度:4 取值范围:0001 – 9999 数据项:课序号
含义说明:对于同一课程,由不同老师教授,用课序号来区分 类型:字符型 长度:2
取值范围:01 – 99 数据项:课程名 含义说明:课程的名称 类型:字符型 长度:10
取值范围:任意合法的字符 数据项:课程描述
含义说明:课程内容的基本描述 类型:字符型 长度:200
取值范围:任意合法字符 数据项:教科书
含义说明:课程所用的教科书 类型:字符型 长度:20
取值范围:任意合法字符 数据项:学分 含义说明:课程的学分 类型:整型 长度:1 取值范围:1-9 数据结构:教室
含义说明:上课所用的教室
组成:教室编号,教室地址,教室容量 数据项:教室编号 含义说明:教室的编号 类型:字符 长度:5
取值范围:00001-99999 数据项:教室地址 含义说明:标明教室的地址 类型:字符 长度:30
取值范围:任意合法的字符 数据项:教室容量
含义说明:教室的容纳学生的数量 类型:整型 取值范围:正整数 数据结构:选课记录
含义说明:纪录学生选课的纪录 组成:学生,课程,成绩
数据项:成绩
含义说明:选课学生本学期的成绩
类型:浮点数 范围:0.0 – 100.0 处理过程:分配教室
说明:为所有课程分配上课教室 输入:课程,教室 输出:教室安排
处理:学期课程决定后,要为每个课程安排上课地点,要求上课的地点不能冲突且上课人数不能超过教室容量。
处理过程:分配考场
说明:在考试日期,为所有课程分配考场
输入:课程,教室
输出:考场安排
处理:为每个课程安排考场,考试时间和地点不能冲突且考试人数不能超过考场容量。
数据存储:成绩单
说明:记录学生本学期所有课程成绩 流入数据流:学生所有的选课记录 流出数据流:学生的成绩单 数据量:由学生的人数决定 存取方式:随机存取
二、概念结构设计
1、根据需求分析画出E-R图:
选修
学生
教师
课程
m n 讲授
1 n 教科书
n 成绩
教室
开设
1 n
2、根据E-R图写出各个实体的属性描述 学生:{学号,姓名}
课程:{课程号,课程名,课程描述,学分} 教师:{教师号,教师名,课程数} 教室:{教室编号,地址,容量}
3、视图的集成
假设在学生管理系统的学籍管理系统中存在另一个学生实体,其中还包括以下信息:性别,所属专业,所属班级。为了使两个实体保持一致,对两个实体取并集得到新的学生实体: 学生:{学号,姓名,性别,专业,班级}
三、逻辑结构设计
1、由E-R图向数据模型的转换
一个实体型转换为一个关系模式。实体的属性就是关系的属性。实体的码就是关系的码。所以,E-R图中的属性描述可直接转换为相应的关系模式。
2、对于剩余的一对多和多对多联系可以如下表示 学生-课程:{学号,课程号,课序号,成绩} 课程-教师:{课程号,课序号,教师号} 课程-教科书:{课程号,课序号,教科书} 教室-课程:{教室号,时间,课程号,课序号}
其中 课程-教师 与 课程-教科书 具有相同的主码,可以合并成一个关系 教学:{课程号,课序号,教师号,教科书} 最后得到的关系模式为
学生:{学号,姓名,性别,专业,班级} 课程:{课程号,课程名,课程描述,学分} 教师:{教师号,教师名,课程数} 教室:{教室号,地址,容量}
学生-课程:{学号,课程号,课序号,成绩} 教学:{课程号,课序号,教师号,教科书} 教室-课程:{教室号,时间,课程号,课序号}
3、用户外模式
根据需求,为计算综合测评增加一个外模式,因为综合测评只在学期末进行,查询次数不多,所以没有必要用冗余的方法来提高查询效率,只需要建立一个外模式视图即可。 测评{学号,学生姓名,平均成绩}
4、根据设计写出相应的SQL语句: CREATE TABLE student (
Sno VARCHAR(9) CONSTRAINT pk_student PRIMARY KEY, Sname VARCHAR(10) NOT NULL, Sex VARCHAR(1) NOT NULL, Major VARCHAR(10) NOT NULL, Cla VARCHAR(10) NOT NULL, CHECK (Sex = \'f\' OR Sex = \'m\') )
CREATE TABLE course (
Cno VARCHAR(4) CONSTRAINT pk_course PRIMARY KEY, Cname VARCHAR(10) NOT NULL, Describe VARCHAR(200) , Mark INTEGER )
CREATE TABLE teacher (
Tno VARCHAR(5) CONSTRAINT teacher_pk PRIMARY KEY, Tname VARCHAR(10) NOT NULL,
Ccnt INTEGER NOT NULL DEFAULT 0 )
CREATE TABLE claroom (
Rno VARCHAR(5) CONSTRAINT pk_claroom PRIMARY KEY, Locate VARCHAR(30) NOT NULL, Cap INTEGER CHECK (Cap > 0) )
CREATE TABLE tech (
Cno VARCHAR(4) NOT NULL CONSTRAINT fk_course_cno FOREIGN KEY REFERENCES course(Cno) ON UPDATE CASCADE --级联更新 ON DELETE CASCADE, --级联删除 SCno VARCHAR(2) NOT NULL,
Tno VARCHAR(5) CONSTRAINT fk_teacher_tno FOREIGN KEY REFERENCES teacher(Tno) ON UPDATE CASCADE ON DELETE CASCADE, Book VARCHAR(20)
CONSTRAINT pk_tech PRIMARY KEY (Cno,SCno) )
CREATE TABLE SC (
Sno VARCHAR(9) NOT NULL CONSTRAINT fk_student_sno FOREIGN KEY REFERENCES student(Sno) ON UPDATE CASCADE ON DELETE CASCADE,
Cno VARCHAR(4) NOT NULL CONSTRAINT fk_sc_course_cno FOREIGN KEY REFERENCES course(Cno) ON UPDATE CASCADE ON DELETE CASCADE, SCno VARCHAR(2) NOT NULL, Score FLOAT
CONSTRAINT pk_sc PRIMARY KEY (Sno,Cno,SCno) )
CREATE TABLE RC ( Rno VARCHAR(5) CONSTRAINT fk_claroom_rno FOREIGN KEY REFERENCES claroom(Rno) ON UPDATE CASCADE ON DELETE CASCADE, Time DATETIME,
Cno VARCHAR(4) NOT NULL CONSTRAINT fk_rc_course_cno FOREIGN KEY REFERENCES course(Cno) ON UPDATE CASCADE ON DELETE CASCADE, SCno VARCHAR(2) NOT NULL
CONSTRAINT pk_rc PRIMARY KEY (Rno,Time) )
为进行综合测评增加一个外模式 CREATE VIEW ae AS
SELECT SC.sno, sname, AVG(score) as average FROM SC,Student where SC.sno = student.sno GROUP BY SC.sno,sname
为了符合数据的参照完整性约束,避免学生选课的时候选择一个不存在的课程号-课序号组,增加一个外键约束。 ALTER TABLE sc
ADD CONSTRAINT fk_sc_tech FOREIGN KEY (Cno,SCno) REFERENCES tech(Cno,SCno) ON UPDATE CASCADE ON DELETE CASCADE
为了在插入一个新课程时能级联更新教师的工作量,增加一个触发器。此任务也可以通过建立一个外模式视图来动态统计,为了练习的目的这里采用了触发器 CREATE TRIGGER workcnter On tech AFTER INSERT ,UPDATE, DELETE AS BEGIN
DECLARE @T VARCHAR(5) IF EXISTS (
SELECT TOP 1 Tno FROM INSERTED ) BEGIN
SET @T = (SELECT TOP 1 Tno FROM INSERTED ) UPDATE teacher SET Ccnt = Ccnt + 1 WHERE Tno = @T END
IF EXISTS (
SELECT TOP 1 Tno FROM DELETED ) BEGIN
SET @T = (SELECT TOP 1 Tno FROM DELETED) UPDATE teacher SET Ccnt = Ccnt - 1 WHERE Tno = @T END END 关于课程容量:如何保证选课的学生数量不超过教室的容量?
初步解决办法是:在学生选课时建立暂时不考虑容量问题,都加入一个临时的SC表中。然后,在选课结束后将未超额的记录直接加入SC表,超额的课程进行抽签,将命中部分学生的选课记录插入SC表,直到人数符合课程容量的要求为止。
抽签的功能较适合用顶层的高级语言来实现。在选课结束后,选超额课程的学生需登陆选课系统进行抽签,抽签的过程可以使用概率算法,如果抽中,系统再将选课记录插入SC表,否则就从临时表中删除。
所以,在数据库系统中,没有对SC表的插入操作进行约束。