SQL 数据基础原理 5

成绩管理系统数据库设计学习目标

了解成绩管理系统的需求。

熟悉成绩管理系统的常见功能。

掌握成绩管理系统的概念设计。

掌握成绩管理系统中表的设计。

掌握成绩管理系统中索引的设计。

掌握成绩管理系统中视图的设计。

掌握成绩管理系统中触发器的设计。

掌握成绩管理系统中存储过程的设计。章节内容14.1需求分析14.2系统功能14.3数据库概念设计14.4逻辑结构设计与物理结构设计14.5存储过程14.1需求分析

14.1需求分析设计一个简易成绩管理系统，用户角色只有教务，教师和学生。其主要功能如下：

教务负责课程管理，学生信息管理，教师管理；

教师负责学生成绩管理；

学生能够查询成绩；

除了基本功能，要求系统具有良好的运行效率，且数据库的安全性高。

14.2系统功能通过对需求的分析和总结，可将成绩管理系统分为学生成绩管理和教师管理模块，课程管理模块，其功能结构如下图所示。各模块的详细描述如下：（1）

学生成绩管理模块：实现按学号、课程和班级查询成绩，以及成绩的录入、删除和修改的功能。（2）教师管理模块：实现教师信息的录入、修改、查询等功能。（3）课程管理模块：实现课程信息的录入、修改、查询等功能。

14.3数据库概念设计数据库概念设计是数据库设计过程中的一个关键阶段，它涉及将现实世界的业务需求转化为抽象的数据模型。这个阶段的主要目标是定义数据库的结构，而不考虑具体的实现细节。数据库概念设计的结果是一个高层次的、抽象的数据模型，它为后续的逻辑设计和物理设计提供了基础。这个模型通常以E-R图的形式呈现，直观地展示了实体、属性和关系。1.实体（Entity）实体表示现实世界中可以独立存在且有意义的个体或对象。在E-R图中，实体通常用矩形框表示。2.属性（Attribute）属性是实体的特征或描述性信息。在E-R图中，属性通常用椭圆形表示，并通过线条连接到相应的实体。3.关系（Relationship）关系表示实体之间的关联或交互。在E-R图中，关系通常用菱形表示，并通过线条连接到相关的实体。

14.3数据库概念设计4.主键（primarykey）主键是唯一标识实体实例的属性。在E-R图中，主键属性通常用下划线标注。5.外键（ForeignKey）外键是引用另一个实体主键的属性，用于建立实体之间的关系。在E-R图中，外键通常没有特殊标记，但可以通过关系连接来识别。6.基数（Cardinality）：基数表示实体之间关系的数量约束，如一对一（1:1）、一对多（1:N）、多对多（M:N）。在E-R图中，基数通常在关系连接线上用标签表示。

14.3.1确定实体及联系实体间的联系:（1）学生和班级：一个班由多个学生组成，一个学生只能归属于某个班，存在“归属”的关系，即1

:

N。（2）学生和课程：一个学生可以选修多门课程，一门课程提供给多个学生选修，存在“选修”的关系：N

:

M。（3）学院和教师存在1

:

N的归属关系。（4）教师和课程之间存在N:

M的讲授关系。（5）学院和班级之间存在1：N的归属关系。（6）学生和成绩之间存在M：N的关系。根据前面对系统进行的分析，本系统规划出学生实体（student）、班级实体（class）、课程实体（course）、教师实体（teacher）、学院实体（department）。

14.3.2系统ER图14.4逻辑结构设计与物理结构设计数据库的逻辑结构设计，就是根据已经建立的概念数据模型，以及所采用的数据库管理系统的数据模型特性，按照一定的转换规则，把概念模型转换为该数据库管理系统所能接受的逻辑结构模型。数据库物理结构设计，就是为一个确定的逻辑结构模型选择一个最适合应用要求的物理结构的过程。此处就是根据逻辑表结构在数据库中创建具体的数据表。14.4.1关系模式计及表设计根据E-R图，得到如下关系模式：（1）学院(学院号，学院名，负责人)，学院号为主键。（2）班级(班级编号，班级名称，所属学院，班级人数)，班级编号为主键。（3）学生(学号，姓名，性别，出生日期，籍贯，所属班级)，学号为主键。（4）课程(课程号，课程名，学分，学时数，课程性质)，课程号为主键。（5）成绩(id，学号，课程号，开课学期，成绩)，id为主键。（6）教师(教师号,姓名,性别,出生日期,民族,所属学院)，教师号为主键。（7）教师授课情况表（id，课程号，教师号，上课学期），id为主键，课程号和教师号分别为外键。根据需求分析及数据库概念设计，可以得出本系统的数据库student_score_db中共存放7张表，分别是student表、course表、score表、class表、teacher表、courseteacher表和department表。表设计表设计表设计表14-7

courseteacher表结构表设计14.4.2设计索引索引是对数据表中一列或多列值进行排序的一种结构，使用它可以有效提高访问数据表中特定信息的速度。在成绩管理系统中需要查询学生和成绩信息，为提高查询速度就需要在某些特定字段上建立索引。本节使用createindex语句创建索引。定义了主键和外键系统会自动创建主键索引和外键索引。用户可以按实际需要创建索引。例：成绩管理系统需要根据

studentNo

和

courseNo查询学生创建，可以创建一个复合索引。具体创建索引的语句如下：createindexindex_studentNo_courseNoonscore(studentNo,courseNo);需要注意的是，索引可以提高查询性能，但同时也会增加插入、更新和删除操作的开销，因为每次这些操作都需要更新索引。因此，在创建索引时，需要权衡查询和写操作的频率，选择合适的字段进行索引。14.4.3设计视图视图是基于数据库中一个或多个表而导出的虚拟表。数据库中只存储视图的定义，对视图所对应的数据并不进行实际存储，在对视图中数据进行操作时，系统可根据视图的定义去操作与视图相关联的基表。本系统中设计了三个视图来改善查询操作。在本系统中查看学生选课成绩，如果直接使用score表中studentNo为条件查询表，显示信息时则只能显示studentNo、courseNo和score。这种显示对用户而言是不友好的，用户并不知道课程名和该学号对应的学生姓名等信息，因此可以创建一个视图score_view，以显示studentNo、studentName、classNo、teacherNo、teacherName、courseNo、courseName、score等信息。14.4.3设计视图CREATEVIEWscore_viewASSELECTs.studentNo,s.studentName,s.classNo,t.teacherNo,t.teacherName,c.courseNo,c.courseName,sc.scoreFROMstudents,coursec,scoresc,teachertWHEREs.studentNo=sc.studentNoandc.courseNo=sc.courseNoandt.teacherNo=c.teacherNo;14.4.3设计视图定义了视图teacher_course_view，实现从teacher,course,courseteacher这三个表中取出相应的字段，获取教师的所授课程信息。Createviewteacher_course_viewasSELECT teacher.teacherNo, teacher.teacherName, course.courseName, courseteacher.term, course.courseTypeFROM teacher INNERJOINcourseteacher ONteacher.teacherNo=courseteacher.teacherNo INNERJOINcourse ON courseteacher.courseNo=course.courseNo;14.4.3设计视图定义了视图student_course_view，实现从teacher，course，student，courseteacher这四个表中取出相应的字段，获取学生的选课信息。Createviewstudent_course_viewasSELECT student.studentNo, student.studentName, course.courseNo, course.courseName, course.credit, course.creditUnits, course.courseType, teacher.teacherNameFROMcourseINNERJOINscoreONcourse.courseNo=score.courseNo INNERJOINstudentONstudent.studentNo=score.studentNo INNERJOINcourseteacherONcourse.courseNo=courseteacher.courseNo INNERJOINteacherON courseteacher.teacherNo=teacher.teacherNoAND score.teacherNo=teacher.teacherNo14.4.4设计触发器触发器的执行既不是由程序调用，也不是手工启动的，而是由INSERT、UPDATE和DELETE等事件来触发的某种特定的操作。当满足触发器的触发条件时，数据库就会执行触发器中定义的程序语句。这样做可以保证数据的一致性。本系统设计了两个触发器来改善删除操作。如果从student表中删除一个学生信息时，那么该学生在score表中的信息也要同时被删除，但由于在score表中存在studentNO外键约束，因此需要在删除student信息之前先删除score中的信息。又如从course表中删除一门课程信息之前，该门课在score表，courseteacher中的信息需要先被删除，也是在之前删除，因为外键约束的缘故。此时就需要用触发器来实现自动删除功能。为此，在student表和course表中创建了相应触发器，只要执行DELETE操作，就会删除score表中的相关信息记录。14.4.4设计触发器CREATETRIGGERdelete_studentBEFOREDELETEONstudentFOREACHROWBEGINDELETEFROMscoreWHEREstudentNo=OLD.studentNo;END;14.4.4设计触发器CREATETRIGGERdelete_courseBEFOREDELETEONcourseFOREACHROWBEGINDELETEFROMscoreWHEREcourseNo=OLD.courseNo;DELETEFROMcourseteacherWHEREcourseNO=OLD.courseNO;END;14.4.5存储过程存储过程是一组为了执行一个或多个相关的数据库操作而编写的SQL语句集合，它可以提高效率、保证数据的安全性和完整性，并且可以简化复杂的操作。根据系统功能需求，可以设计一些存储过程来实现成绩管理系统中的常用功能。1.增加学生信息-该存储过程用于向student表中添加一个新的学生记录。CREATEPROCEDUREAddStudent(IN_studentNoCHAR(10),IN_studentNameVARCHAR(30),IN_sexCHAR(2),IN_birthdayDATE,IN_nativePlaceVARCHAR(30),IN_classNoCHAR(10))BEGIN--插入学生信息到Student表INSERTINTOStudent(studentNo,studentName,sex,birthday,native,classNo)VALUES(_studentNo,_studentName,_sex,_birthday,_nativePlace,_classNo);END14.4.5存储过程2.增加课程信息CREATEPROCEDUREAddCourse(IN_courseNoCHAR(10),IN_courseNameVARCHAR(100),IN_creditFLOAT,IN_creditUnitsTINYINT,IN_courseTypeVARCHAR(30))BEGIN--首先检查课程号是否已经存在，避免重复添加IFNOTEXISTS(SELECT1FROMCourseWHEREcourseNo=_courseNo)THEN--插入课程信息到Course表INSERTINTOCourse(courseNo,courseName,credit,creditUnits,courseType)VALUES(_courseNo,_courseName,_credit,_creditUnits,_courseType);ELSE--如果课程号已存在，可以选择抛出一个错误或者返回一个消息SIGNALSQLSTATE'45000'SETMESSAGE_TEXT='Coursenumberalreadyexists.';ENDIF;END 这个存储过程允许用户向course表中添加新的课程记录。在实际插入数据之前，存储过程首先检查Course表中是否已存在相同的课程编号。如果已存在，则通过SIGNAL语句抛出一个错误，防止数据重复如果课程编号不存在，则执行INSERT语句将新课程信息添加到Course表中。3.增加教师信息CREATEPROCEDUREAddTeacher(IN_teacherNoCHAR(10),IN_teacherNameVARCHAR(30),IN_sexCHAR(2),IN_birthdayDATETIME,IN_nativeVARCHAR(30),IN_instituteVARCHAR(30))BEGIN--首先检查教师编号是否已经存在，避免重复添加IFNOTEXISTS(SELECT1FROMteacherWHEREteacherNo=_teacherNo)THEN--插入教师信息到teacher表INSERTINTOteacher(teacherNo,teacherName,sex,birthday,native,institute)VALUES(_teacherNo,_teacherName,_sex,_birthday,_native,_institute);ELSE--如果教师编号已存在，抛出一个错误SIGNALSQLSTATE'45000'SETMESSAGE_TEXT='teachernumberalreadyexists.';ENDIF;END这个存储过程允许用户向teacher表中添加新的教师记录。在实际插入数据之前，存储过程首先检查teacher表中是否已存在相同的教师编号。如果已存在，则通过SIGNAL语句抛出一个错误，防止数据重复。如果教师编号不存在，则执行INSERT语句将新教师信息添加到teacher表中。4.添加成绩

CREATEPROCEDUREAddscore(IN_studentNoCHAR(10),IN_courseNoCHAR(10),IN_scorefloat,IN_termVARCHAR(30),)BEGIN--检查学生是否存在IFEXISTS(SELECT1FROMstudentWHEREstudentNo=_studentNo)THEN--检查课程是否存在IFEXISTS(SELECT1FROMcourseWHEREcourseNo=_courseNo)THEN--检查是否已经存在该学生和课程的成绩记录IFNOTEXISTS(SELECT1FROMscoreWHEREstudentNo=_studentNoANDcourseNo=_courseNo)THEN--插入成绩记录INSERTINTOscore(studentNo,courseNo,score,term)VALUES(_studentNo,_courseNo,_score,_term);ELSE--如