数据库课程设计 《教育管理系统》数据库的设计与实现

1.需求分析

1.1课题背景

我的课题是一个教育管理系统，其核心内容是实现对学生信息、班级信息、专业信息、院系信息、课程信息、学生的课程成绩以及奖惩信息的管理和查询。该系统旨在帮助教育机构（如学校）高效地管理学生的教育历程，包括他们的个人信息、课程安排、学业表现和奖惩记录。

1.2应用环境

- 学校教务管理部门：用于管理学生档案、课程设置、成绩录入和查询等。
- 教师：用于查看教授课程的学生名单、录入和修改学生成绩。
- 学生：查询自己的课程安排、成绩和奖惩记录。
- 院系管理员：管理特定院系的信息，包括专业设置、学生和班级数据。

1.3开发环境

1.Windows 11系统

2.SQL Serve 2022

1.4数据字典

1. 学生:
   - 学号：id,varchar(20),学生的唯一识别码。
   - 姓名：name,varchar(20),学生的姓名。
   - 性别：sex,char(5),学生的性别。
   - 班级号：Cid,varchar(30),学生所属班级的编号。
   - 专业号：Mid,varchar(30),学生专业的编号。
   - 院系号：Did,varchar(30),学生所属院系的编号。
   - 民族：nation,varchar(20),学生的民族。
   - 年龄：age,int,学生的年龄。
   - 出生日期：birthday,date,学生的出生日期。
   - 家庭地址：location,varchar(20),学生的家庭住址。
   - 入学时间：enrol,date,学生入学的日期。
2. 班级:
   - 班级号：Cid,varchar(30),班级的唯一编号。
   - 专业号：Mid,varchar(30),班级所属专业的编号。
   - 院系号：Did,varchar(30),班级所属院系的编号。
   - 班级名称：Cname,varchar(30),班级的名称。
   - 人数：Cnumber,int,班级中学生的数量。
3. 专业:
   - 专业号：Mid,varchar(30),专业的编号。
   - 院系号：Did,varchar(30),专业所属院系的编号。
   - 专业名称：Mname,varchar(30),专业的名称。
4. 院系:
   - 院系号：Did,varchar(30),院系的编号。
   - 院系名称：Dname,varchar(30),院系的名称。
5. 课程:
   - 课程号：Kid,varchar(30),课程的编号。
   - 课程名：Kname,varchar(30),课程的名称。
   - 学分：Kcredit,int,完成课程可获得的学分。
   - 学时：Kperiod,int,完成课程需要的学时数。
6. 课程成绩:
   - 课程号：Kid,varchar(30),课程的编号。
   - 学号：id,varchar(20),学生的学号。
   - 成绩：Ggrade,int,学生在课程中的成绩。
7. 奖惩:
   - 奖惩号：Aid,varchar(20),奖惩记录的编号。
   - 学号：id,varchar(20),涉及的学生的学号。
   - 专业号：Mid,varchar(30),学生专业的编号。
   - 院系号：Did,varchar(30),学生所属院系的编号。
   - 奖惩名称：Aname,varchar(30),奖惩的名称。
   - 奖惩方案：Aproject,varchar(100),具体的奖惩措施。

1.5具体语义

1. 学生：记录每个学生的基础信息，如学号、姓名、性别等，以及他们所在的班级、专业和院系。
2. 班级：表示具体的教学班级，包含班级的编号、所属专业和院系，班级名称及人数。
3. 专业：代表学校提供的专业方向，记录专业的编号、所属院系和专业名称。
4. 院系：描述学校的组织结构，包含院系的编号和名称。
5. 课程：涉及教学计划中的课程信息，包括课程号、课程名、学分和学时。
6. 课程成绩：记录学生在其学习过程中各门课程的成绩，与特定学生和课程相关联。
7. 奖惩：描述学生在学习期间获得的奖励或惩罚，包括具体的奖惩方案。

1.6系统的业务流程

1. 学生信息管理：系统允许添加新学生、更新和查询学生信息。
2. 班级信息管理：可以创建新班级，更新或删除班级信息，以及查询特定班级的详细信息。
3. 专业和院系管理：允许添加、修改或删除专业和院系信息。
4. 课程管理：包括课程的添加、修改、删除和查询功能。
5. 成绩管理：教师可以输入或更新学生的课程成绩，学生和教务人员可以查询成绩。
6. 奖惩管理：记录并管理学生的奖励和惩罚信息。

1.7系统边界

系统的边界在于它主要处理与学生教育相关的数据。这包括学生、班级、专业、院系、课程和成绩数据的管理。系统不涉及财务管理、人力资源管理等其他非教学管理方面。系统还应确保数据的一致性和安全性，例如，确保只有授权用户才能访问和修改信息。

2概念设计

2.1E-R模型

3逻辑设计

3.1关系模型

将E-R图转换为关系模型

（1）学生（学号，姓名，性别，班级号，专业号，院系号，民族，年龄，出生日期，家庭地址，入学时间）

         学号为主键，班级号、院系号、专业号为外键。

（2）班级（班级号，专业号，院系号，班级名称，人数）

         班级号为主键，院系号、专业号为外键。

（3）专业（专业号，院系号，专业名称）

         专业号为主键，院系号为外键。

（4）院系（院系号，院系名称）

         院系号为主键。

（5）课程（课程号，课程名，学分，学时）

         课程号为主键。

（6）课程成绩（课程号，学号，成绩）

         课程号和学号为主键，课程号、学号为外键。

（7）奖惩（奖惩号，学号，专业号，院系号，奖惩名称，奖惩方案）

     奖惩号为主键，学号、院系号、专业号为外键。

4数据库的实现

4.1数据库代码

create database course;
 
use course;

create table Department( 
   Did varchar(30) primary key, 
   Dname varchar(30) 
);

create table Major( 
   Mid varchar(30) primary key , 
   Did varchar(30) , 
   Mname varchar(30) , 
   foreign key (Did) references Department (Did) 
      on delete cascade 
      on update cascade
);

create table Class( 
   Cid varchar(30) primary key , 
   Mid varchar(30) , 
   Did varchar(30) , 
   Cname varchar(30) , 
   Cnumber int not null, 
   foreign key (Did) references Department (Did) 
      on delete cascade 
      on update cascade,
   foreign key (Mid) references Major (Mid)
      on delete no action 
      on update no action 
);

CREATE TABLE Student( 
   id VARCHAR(20) PRIMARY KEY , 
   name VARCHAR(20) , 
   sex CHAR(5) CHECK(sex IN('男','女')),  
   Cid VARCHAR(30) ,  
   Mid VARCHAR(30) ,  
   Did VARCHAR(30) ,  
   nation VARCHAR(20) ,  
   age INT ,  
   birthday DATE , 
   location  VARCHAR(20) ,  
   enrol DATE ,  
   FOREIGN KEY (Cid) REFERENCES Class (Cid) 
     ON DELETE CASCADE 
     ON UPDATE CASCADE,  
   FOREIGN KEY (Mid) REFERENCES Major (Mid) 
     ON DELETE NO ACTION 
     ON UPDATE NO ACTION,  
   FOREIGN KEY (Did) REFERENCES Department (Did) 
     ON DELETE NO ACTION 
     ON UPDATE NO ACTION  
);

create table Course( 
   Kid varchar(30) primary key , 
   Kname varchar(30) ,
   Kcredit int , 
   Kperiod int 
);

create table Grade( 
  Kid varchar(30) , 
  id varchar(20) , 
  Ggrade int , 
  primary key(Kid,id), 
  foreign key (Kid) references Course (Kid) 
     on delete cascade 
     on update cascade, 
  foreign key (id) references Student (id)
     on delete cascade 
     on update cascade 
);

create table Award_punish( 
  Aid varchar(20) primary key , 
  id varchar(20) , 
  Mid varchar(30) , 
  Did varchar(30) , 
  Aname varchar(30) , 
  Aproject varchar(100) , 
  foreign key (id) references Student (id) 
     on delete cascade 
     on update cascade, 
  foreign key (Mid) references Major (Mid) 
     on delete no action 
     on update no action, 
  foreign key (Did) references Department (Did)
     on delete no action 
     on update no action
);

4.2视图

/*学生基本信息视图*/
create view A_Student(id,name,Cname,Mname,Dname) 
as 
select id,name,Cname,Mname,Dname 
from Student,Class,Major,Department 
where Class.Did = Department.Did 
and Class.Mid = Major.Mid 
and Student.Did = Department.Did 
and Student.Mid = Major.Mid 
with check option;

/*学生成绩视图*/
create view B_Grade(id,name,Kname,Ggrade)   
as 
select Student.id,name,Kname,Ggrade 
from Student,Grade,Course 
where Student.id = Grade.id 
and Grade.Kid = Course.Kid 
with check option;

/*奖惩信息视图*/
Create view C_Award_punish 
(id,name,Cname,Mname,Dname,Aid,Aname,Aproject)   
 as 
select Student.id,name,Cname,Mname,Dname,Aid,Aname,Aproject 
from Student,Class,Major,Department,Award_punish 
where Student.id = Award_punish.id 
and Award_punish.Mid = Major.Mid 
and Award_punish.Did = Department.Did 
and Class.Did = Department.Did 
and Class.Mid = Major.Mid 
with check option;

4.3触发器

/*触发器，添加学生班级信息时自动修改相应班级学生人数*/
CREATE TRIGGER AddClassStudentCount
ON Student
AFTER INSERT
AS
BEGIN
    UPDATE Class
    SET Cnumber = Cnumber + 1
    WHERE Cid IN (SELECT Cid FROM inserted);
END;

insert into Student values('229020117','孙思','男','03',
'080902','160001','汉族',22,'2002-12-08','山东济南','2022-09-02');  
select * from Class where Cname = '软件2班';


/*触发器，删除学生班级信息时自动修改相应班级学生人数*/
CREATE TRIGGER DeleteClassStudentCount
ON Student
AFTER DELETE
AS
BEGIN
    UPDATE Class
    SET Cnumber = Cnumber - 1
    WHERE Cid IN (SELECT Cid FROM deleted);
END;

delete from Student where name = '孙思'; 
select * from Class where Cname = '软件2班';

4.4存储过程

/*创建存储过程，查询指定学生的成绩单*/
CREATE PROCEDURE GetStudentGrades
    @id INT
AS
BEGIN
    SELECT Course.Kid, Grade.Ggrade
    FROM Grade
    JOIN Course ON Grade.Kid = Course.Kid
    WHERE Grade.id = @id;
END;

EXEC GetStudentGrades 219010101;


/*创建存储过程，查询指定学生的成绩单*/
CREATE PROCEDURE SecondGetStudentGrades
    @id INT
AS
BEGIN
     SELECT Student.id, name, Kname, Ggrade 
     FROM Student, Grade, Course 
     WHERE Student.id = Grade.id 
     AND Grade.Kid = Course.Kid
	 AND Grade.id = @id;
END;

EXEC SecondGetStudentGrades 219010101; 

4.5增删改查

select * from Student where age < 22 and Cid = '01';

select Student.id,name,Ggrade from Student,Grade 
where Student.id = Grade.id and Grade.Kid = '81002' 
order by Ggrade desc;

select Student.id '学号',name '姓名',Mname '专业名称',Dname '院系名称' 
from Student,Major,Department,Award_punish 
where Student.id = Award_punish.id 
and Major.Mid = Award_punish.Mid 
and Department.Did = Award_punish.Did 
and Aname = '见义勇为';

update Student set location = '天津津南' where location = '天津河西';

update Course set Kperiod = 90 where Kname = '数据库管理与应用' ;

  insert into Student values('219010104','刘帅','男','01','080901','160001','汉族',20,'2004-11-03','辽宁沈阳','2021-09-01');

 delete from student where id='219010104';

5小结

对设计过程中遇到的问题以及收获做总结

通过这次课程设计，我深入地了解了数据库有关知识的基本概念和一些基本的操作并体会到了流程和数据的重要性。进行数据库设计之前，需要对开发的系统进行分析，明确数据的需求，并进行可行性研究。只有这样，才能设计出合理、高效的数据库结构。

遇到的问题和解决方法：在课程设计过程中，我遇到了许多问题。例如，本应该打开数据库course，但却还是默认打开master,导致代码运行不了，显示“对象不存在或对此操作无效”，问过老师后才解决，感谢老师的指导。在画E-R图时，我总是实体与实体之间的关系搞混，但在练习和查阅资料后，我逐渐掌握了画E-R图的技巧和方法；也是在课程老师的指导下，逐步将细小的错误改掉，在此深刻地感谢老师。我在创建基本表时，先创建了student表导致出错，院系号、专业号、班级号是学生表（student）的外键，因此应该先创建院系表（department）、专业表（major）、班级表（calss）再创建学生表（student）；通过这个错误，我意识到了在数据库设计中，外键的约束是非常重要的。“ON DELETE CASCADE   ON UPDATE CASCADE,”在级联删除时，前面的ON DELETE CASCADE加上了逗号。

在课程设计过程中，我遇到了许多不懂的问题。但是，通过上网查阅资料、查看参考书，询问老师和同学等方式，我逐渐解决了这些问题。同时，这次课设也使我对常用画图软件和Word文档编辑等操作掌握得更加熟练。总之，这次课程设计让我巩固了数据库的基础知识，锻炼了自己的动手能力！