教学管理系统数据库ER图及SQL语句

1、教学管理系统一、系统功能需求学校教务管理系统是针对学校的大量信息处理工作而开发的管理软件，完成的主要功能如下：(1) 学生基本信息管理：能够对学生基本信息进行输入、删除、修改。学生基本信息包括：学号、姓名、性别、出生日期、入学成绩、所在系号。(2) 系部基本信息管理：系部的基本信息输入、修改、删除。系部基本信息包括：系号、系名称、系的简介。(3) 课程信息管理：课程信息的输入、修改、删除。课程信息包括：课程号、课程名称、任课教师号、学时、学分、上课时间、上课地点、考试时间。(4) 教职工信息管理：教职工信息的输入、修改、删除。教职工信息包括：职工号、姓名、性别、出身年月、所在系号、职称、技术专

2、长。(5) 选课管理：学号、学生、课程号、课程名称、上课教师姓名、系号每学期所选课程的学分不能超过15分。学生可以同时选修一门或多门课程。可以同时为多个学生选修某一门或某几门课程。可以删除和修改选课信息。(6) 成绩管理可以按课程输入和修改成绩，也可以按学生输入和修改成绩。(7) 信息查询可以按学号、姓名、系号查询学生基本信息。可以按职工号、姓名、系号查询教职工基本信息。可以按系号、系名称查询系的基本信息。可以按课程号、课程名称、上课教师姓名查询课程基本信息。按学号、课程号、课程名称、上课教师姓名、系号查询学生成绩，内容包括课程基本情况。若查询涉及多门课程，则按课程分组。每门课程按总评成绩从高

3、分到低分给出选修该门课程的所有学生的成绩(平时成绩、考试成绩和总评成绩)。(8) 统计报表a、成绩报表：内容包括课程基本信息(课程号、课程名称、任课教师号、学时、学分)，选课学生名单(学号、姓名、性别)，每个学生的平时成绩、考试成绩和总评成绩。能按课程号、课程名称、教师姓名输出对应课程的成绩报表。b、能够根据课程、授课教师统计成绩=90分、=80分、=70分、=60分及不及格学生的人数及比例。二、任务描述1、根据需求描述，完成数据概念模型设计，画出 E-R图；2、优化E-R图，给出数据逻辑模型；3、将逻辑模型转换成物理模型并创建数据库和数据表。要求数据表能尽量实现数 据完整性要求。4、根据需要

4、，创建适当的索引。5、为了实现系统功能，使用 SQL语句向数据表添加相关数据。6、写出实现系统管理功能用到的删除、修改操作的SQL语句。7、写出实现系统需要用到的查询语句。8、用视图实现成绩报表的生成。9、为了更好的实现系统，创建合适的存储过程和函数。（至少各一个）。10、撰写数据库系统设计报告及项目总结。11、完成答辩。期末实训评分标准序号评分内容评分标准分值1E-R图和逻辑设计创建适当的E-R图和逻辑设计关系102创建数据库和数据表创建数据库2创建合适的数据表15具有适当的主键5具有适当的外键和其他约束53创建索引创建适当的索引34添加数据根据系统需求，添加适当的数据55修改、删除数据正确

5、完成数据的修改、删除56查询写出系统要求的查询语句157创建视图正确创建视图48存储过程和函数正确创建存储过程3正确创建函数39设计报告设计报告内容合理5包含个人心得体会510答辩根据要求完成答辩15总计100分USE marketGOCREATE TABLE学生基本信息(学号 INT PRIMARY KEY,姓名 VARCHAR(10) NOT NULL,性另IVARCHAR(50),出生日期INT ,入学成绩INT,所在系号INT,)GOUSE marketGOCREATE TABLE系部基本信息(系号 INT PRIMARY KEY,系名称 VARCHAR(50) NOT NULL, 系

6、的简介VARCHAR(50)GOUSE marketGOCREATE TABLE课程信息(课程号 INT PRIMARY KEY,课程名称 VARCHAR(50) NOT NULL,任课教师号INT,学时INT ,学分INT,上课时间 VARCHAR(50),上课地点 VARCHAR(50),考试时间 VARCHAR(50)GOUSE marketGOCREATE TABLE教职工信息(职工号 INT PRIMARY KEY,教 姓名 VARCHAR(10) NOT NULL,性别 VARCHAR(50),出身年月INT ,所在系号INT,职称 VARCHAR(50),技术专长 VARCHAR

7、（50）GOUSE marketGOINSERT学生基本信息（学号,姓名，性别，出生日期,入学成绩,所在系号）VALUES （'',' 王晓明,男',",'278','4'）INSERT学生基本信息VALUES （'','吴小丽,女',",'276','4'）INSERT学生基本信息VALUES （'',' 章明敏,女',",'281','4'）INSERT学生基本信息VAL

8、UES （'',' 王孝亚','男',",'273','4'）INSERT学生基本信息VALUES （'','李明月',女',",'263','4'）INSERT学生基本信息VALUES （'',' 任心； 女',",'267','4'）INSERT学生基本信息VALUES （'','吴佳丽,女',",'

9、;294','4'）INSERT学生基本信息VALUES （'',' 王丽','女',",'287','4'）INSERT学生基本信息VALUES （'',朱明,男',",'278','4'）INSERT学生基本信息GOUSE marketGOINSERT系部基本信息（系号,系名称,系的简介）VALUES （'1','经济贸易学院,'经济贸易,）INSERT系部基本信息VALUES （

10、'2','会计经融学院','会计经融）INSERT系部基本信息VALUES （'3','旅游管理学院','旅游管理'）INSERT系部基本信息VALUES （'4','信息工程学院','信息工程）INSERT系部基本信息VALUES （'5','商务外语学院','商务外语'）INSERT系部基本信息VALUES （'6','电子工程学院','电子工程）INSERT系部基本信息VAL

11、UES （'7',机电工程学院',机电工程）INSERT系部基本信息VALUES （'8','艺术设计学院','艺术设计）GOUSE marketGOINSERT课程信息（课程号，课程名称，任课教师号，学时，学分，上课时间，上课地点，考试时间）VALUES （'1',' 数学','1','40','2',' 周一、四,教 3-A209','06-29'）INSERT课程信息VALUES （'2','

12、;体育','2','40','2',' 周一','体育馆','06-24,）INSERT课程信息VALUES （'3',' 商业应用文写作','3','40','2','INSERT课程信息VALUES （'4','实用英语','4','40','2','INSERT课程信息VALUES （'5','影

13、视鉴赏','5','16','2','INSERT课程信息VALUES （'6','音乐鉴赏','6','16','2','GO周一','教 3-A408','06-24,）周二、五','教 3-C304','06-30,）周一','教 3-A408','06-02,）周五',教 3-C304','06-06,）USE mar

14、ketGOINSERT教职工信息（职工号，教师姓名，性别，出身年月，所在系号，职称，技术专长）VALUES （'1',' 王明','女',",'4',' 教师',数学'）INSERT教职工信息VALUES （'2',' 李丽','女',",'4',' 教师',体育'）INSERT教职工信息VALUES （'3',' 徐新','女',",'

15、;2',' 教师','语文'）INSERT教职工信息教师',英语'）VALUES （'4',' 王曦',女',",'4','INSERT教职工信息VALUES （'5','王翔','男',",'2',' 教师',影视'）INSERT教职工信息VALUES （'6',' 李雨','男',",'2',

16、' 教师',音乐'）GO建立外键Alter table课程信息addconstraint Fk_ 课程信息_教职工信息foreign key （任课教师号）REFERENCE教职工信息（职工号）goUSE marketGOCREATE TABLE选课信息(学号 INT PRIMARY KEY,教 姓名 VARCHAR(10) NOT NULL,课程号INT,课程名称VARCHAR(10),平时成绩INT,考试成绩INT,总评成绩INT)GOUSE marketGOINSERT选课信息（学号，姓名，课程号，课程名称，平时成绩，考试成绩，总评成绩）VALUES ('

17、','王晓明','6','INSERT选课信息VALUES ('','吴小丽','5','INSERT选课信息VALUES ('','章明敏','6','INSERT选课信息VALUES ('',' 王孝亚','5','INSERT选课信息VALUES ('','李明月','5','INSERT选课信息VALUES ('

18、;',' 任心','6','INSERT选课信息VALUES ('',' 吴佳丽','5','INSERT选课信息VALUES ('',' 王丽','6','INSERT选课信息VALUES ('','朱明','6','INSERT选课信息VALUES ('',' 章自强','5','GO音乐鉴赏','70&#

19、39;,'80','75')影视鉴赏','50','60','55')','55','65','60')影视鉴赏','80','80','80')影视鉴赏','90','90','90')音乐鉴赏','90','80','85')影视鉴赏','65','6

20、5','65')音乐鉴赏','55','65','60')音乐鉴赏','95','95','95')影视鉴赏','70','80','75')USE marketGOCREATE TABLE数学成绩信息(课程号INT,学号INT, 姓名 VARCHAR（10） NOT NULL,平时成绩INT,考试成绩INT,总评成绩INT）GO数学成绩信息USE marketGOINSERT数学成绩信息（课程号，学号，

21、姓名，平时成绩，考试成绩，总评成绩）VALUES （'1','','王晓明,70','80',75'）INSERT数学成绩信息VALUES （'2','','吴小丽,50','60','55'）INSERT数学成绩信息VALUES （'3','','章明敏','55','65','60'）INSERT数学成绩信息VALUES （'4'

22、,'','王孝亚,'80','80','80'）INSERT数学成绩信息VALUES （'5','','李明月,'90','90','90'）INSERT数学成绩信息VALUES （'6','','任心','90','80','85'）INSERT数学成绩信息VALUES （'7','','吴佳丽

23、9;,'65','65','65'）INSERT数学成绩信息VALUES （'8','','王丽','55','65','60'）INSERT数学成绩信息VALUES ('9','',' 朱明','95','95','95')INSERT数学成绩信息VALUES ('10','',' 章自强','70&

24、#39;,'80','75')GOUSE marketGOCREATE TABLE体育成绩信息(课程号INT,学号INT ,姓名 VARCHAR(10) NOT NULL,平时成绩INT,考试成绩INT,总评成绩INT)GO体育成绩信息USE marketGOINSERT体育成绩信息(课程号，学号，姓名，平时成绩，考试成绩，总评成绩)VALUES ('1','',' 王晓明','70','80','75')INSERT体育成绩信息VALUES ('2'

25、,'','吴小丽,'50','60','55')INSERT体育成绩信息INSERT体育成绩信息VALUES ('4','','王孝亚','80','80','80')INSERT体育成绩信息VALUES ('5','','李明月',90','90','90')INSERT体育成绩信息VALUES ('6',''

26、;,'任心','90','80','85')INSERT体育成绩信息VALUES ('7','','吴佳丽','65','65','65')INSERT体育成绩信息VALUES ('8','','王丽','55','65','60')INSERT体育成绩信息VALUES ('9','','朱明'

27、,'95','95','95')INSERT体育成绩信息VALUES ('10','','章自强','70','80','75')GOUSE marketGOCREATE TABLE商业应用文写作成绩信息(课程号INT,学号INT ,姓名 VARCHAR(10) NOT NULL,平时成绩INT,考试成绩INT,总评成绩INT）GO商业应用文写作成绩信息USE marketGOINSERT商业应用文写作成绩信息（课程号,学号,姓名,平时成绩,考试成绩，

28、总评成 绩）VALUES （'1','',' 王晓明','70','80','75'）INSERT商业应用文写作成绩信息VALUES （'2','','吴小丽,'50','60','55'）INSERT商业应用文写作成绩信息VALUES （'3','','章明敏','55','65','60'）INSERT商业应用文写

29、作成绩信息VALUES （'4','','王孝亚','80','80','80'）INSERT商业应用文写作成绩信息VALUES （'5','','李明月,'90','90','90'）INSERT商业应用文写作成绩信息VALUES （'6','','任心','90','80','85'）INSERT商业应用文写作成绩

30、信息VALUES （'7','','吴佳丽','65','65','65'）INSERT商业应用文写作成绩信息VALUES （'8','','王丽','55','65','60'）INSERT商业应用文写作成绩信息VALUES （'9','','朱明','95','95','95'）INSERT商业应用文写作成

31、绩信息VALUES （'10','',' 章自强','70','80','75'）GOUSE marketGOCREATE TABLE实用英语信息（课程号INT,学号INT ,姓名 VARCHAR（10） NOT NULL,平时成绩INT,考试成绩INT,总评成绩INT）GO实用英语信息USE marketGOINSERT实用英语信息（课程号，学号，姓名，平时成绩，考试成绩，总评成绩）VALUES （'1','','王晓明,70','80

32、9;,75'）INSERT实用英语信息VALUES （'2','','吴小丽,50','60','55'）INSERT实用英语信息VALUES （'3','','章明敏','55','65','60'）INSERT实用英语信息INSERT实用英语信息VALUES ('5','',' 李明月','90','90','90'

33、;)INSERT实用英语信息VALUES ('6','','任心','90','80','85')INSERT实用英语信息VALUES ('7','','吴佳丽','65','65','65')INSERT实用英语信息VALUES ('8','','王丽','55','65','60')INSERT实用英语信息

34、VALUES ('9','','朱明','95','95','95')INSERT实用英语信息VALUES ('10','',' 章自强','70','80','75')GO修改语句USE marketGOUPDATE学生基本信息SET姓名='王晓明',出生日期=''WHERE 号=GO删除语句USE marketGODELETE学生基本信息WHERE 号=GO查询可以按学

35、号、姓名、系号查询学生基本信息。USE marketGOSELECT学号，姓名，所在系号FROM学生基本信息可以按职工号、姓名、系号查询教职工基本信息。USE marketGOSELECT职工号，教师姓名，所在系号FROM教职工信息可以按系号、系名称查询系的基本信息。USE marketGOSELECT系号,系的简介FROM系部基本信息可以按课程号、课程名称、上课教师姓名查询课程基本信息。SELECT a.课程号，a.课程名称,b.教师姓名FROM课程信息 AS a INNER JOIN 教职工信息 AS bON a.课程号=b.职工号按学号、课程号、课程名称、上课教师姓名、系号查询学生成绩，

36、内容包括课程基本情况。若查询涉及多门课程，则按课程分组。每门课程按总评成绩从高分到低分给出选修该门课程的所有学生的成绩（平时成绩、考试成绩和总评成绩）。体育USE marketGOSELECT课程信息.课程号，课程信息.课程名称，教职工信息.姓名，教职工信息.所在系号，体育 成绩信息.学号，体育成名言息.平时成绩，体育成名言息.考试成绩，体育成绩信息.总评成绩FROM课程信息INNER JOIN教职工信息ON课程信息.课程号=教职工信息.职工号INNER JOIN体育成绩信息on体育成信息.课程号=课程信息.课程号group by 课程信息.课程号，课程信息.课程名称，教职工信息.姓名，教职工

37、信息.所在系号， 体育成绩信息.学号，体育成名言息.平时成绩，体育成名数言息.考试成绩，体育成名言息.总评 成绩order by 体育成绩信息.总评成绩desc数学USE marketGOSELECT课程信息.课程号，课程信息.课程名称，教职工信息.姓名，教职工信息.所在系号，数学成绩信息.学号，数学成绩信息.平时成绩，数学成绩信息.考试成绩，数学成绩信息.总评成绩 FROM课程信息INNER JOIN教职工信息ON课程信息.课程号=教职工信息.职工号INNER JOIN数学成绩信息on数学成绩信息.课程号=课程信息.课程号group by 课程信息.课程号，课程信息.课程名称，教职工信息.姓

38、名，教职工信息.所在系号， 数学成绩信息.学号，数学成绩信息.平时成绩，数学成绩信息.考试成绩，数学成绩信息.总评成绩 order by 数学成绩信息.总评成绩desc商业应用文写作USE marketGOSELECT课程信息.课程号，课程信息.课程名称，教职工信息.姓名，教职工信息.所在系号，商业 应用文写作成绩信息.学号，商业应用文写作成绩信息.平时成绩，商业应用文写作成绩信息. 考试成绩，商业应用文写作成绩信息.总评成绩FROM课程信息INNER JOIN教职工信息 ON课程信息.课程号=教职工信息.职工号INNER JOIN商业应用文写作成绩信息on商业应用文写作成绩信息.课程号=课程信息.课程号group by课程信息.课程号，课程信息.课程名称，