数据库实验七 数据库设计

 

题目要求：

       设计一个教务管理系统。要求管理包括：学生的档案，学生选课的情况，学生每学期的综合测评，教师档案，教师工作量等等。具体考核方法根据自己了解的实际情况处理。

       假设此次设计的教务系统是整个学生管理系统数据库中的一部分，整个系统至少还包括学籍管理等其他子系统。

在初期设计时，先关注与教务系统有关的数据。在完成对教务管理系统的建模后再对整个系统进行合并，总终得到整个系统的设计方案。

 

一、需求分析

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,

Class       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 classroom

(

Rno      VARCHAR(5) CONSTRAINT pk_classroom 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_classroom_rno FOREIGN KEY REFERENCES classroom(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 assess 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表的插入操作进行约束。