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

数学管理系统一、系统功能需求学校教务管理系统是针对学校的大量信息处理工作而开发的管理软件，完成的主要功能如下：(1)学生根本信息管理：能够对学生根本信息进行输入、删除、修改。学生根本信息包括：学号、姓名、性别、出生日期、入学成绩、所在系号。(2)系部根本信息管理：系部的根本信息输入、修改、删除。系部根本信息包括：系号、系名称、系的简介。(3)课程信息管理：课程信息的输入、修改、删除。课程信息包括：课程号、课程名称、任课教师号、学时、学分、上课时间、上课地点、考试时间。(4)教职工信息管理：教职工信息的输入、修改、删除。教职工信息包括：职工号、姓名、性别、出身年月、所在系号、职称、技术专长。(5)选课管理：学号、学生、课程号、课程名称、上课教师姓名、系号每学期所选课程的学分不能超过15分。学生可以同时选修一门或多门课程。可以同时为多个学生选修某一门或某几门课程。可以删除和修改选课信息。(6)成绩管理可以按课程输入和修改成绩，也可以按学生输入和修改成绩。(7)信息查询可以按学号、姓名、系号查询学生根本信息。可以按职工号、姓名、系号查询教职工根本信息。可以按系号、系名称查询系的根本信息。可以按课程号、课程名称、上课教师姓名查询课程根本信息。按学号、课程号、课程名称、上课教师姓名、系号查询学生成绩，内容包括课程根本情况。假设查询涉及多门课程，那么按课程分组。每门课程按总评成绩从高分到低分给出选修该门课程的所有学生的成绩(平时成绩、考试成绩和总评成绩)。(8)统计报表a、成绩报表：内容包括课程根本信息(课程号、课程名称、任课教师号、学时、学分)，选课学生名单(学号、姓名、性别)，每个学生的平时成绩、考试成绩和总评成绩。能按课程号、课程名称、教师姓名输出对应课程的成绩报表。b、能够根据课程、授课教师统计成绩”90分、=80分、=70分、=60分及不及格学生的人数及比例二、任务描述1、根据需求描述，完成数据概念模型设计，画出E-R图；2、优化E-R图，给出数据逻辑模型；3、将逻辑模型转换成物理模型并创立数据库和数据表。要求数据表能尽量实现数据完整性要求。4、根据需要，创立适当的索引。5、为了实现系统功能，使用SQL语句向数据表添加相关数据。6、写出实现系统管理功能用到的删除、修改操作的SQL语句。7、写出实现系统需要用到的查询语句。8、用视图实现成绩报表的生成。9、为了更好的实现系统，创立适宜的存储过程和函数。（至少各一个）。10、撰写数据库系统设计报告及工程总结。11、完成辩论。期末实训评分标准序号评分内容评分标准分值1E-R图和逻辑设计创立适当的E-R图和逻辑设计关系102创立数据库和数据表创立数据库2创立适宜的数据表15具有适当的主键5具有适当的外键和其他约束53创立索引创立适当的索引34添加数据根据系统需求，添加适当的数据55修改、删除数据正确完成数据的修改、删除56查询写出系统要求的查询语句157创立视图正确创立视图48存储过程和函数正确创立存储过程3正确创立函数39设计报告设计报告内容合理5包含个人心得体会510辩论根据要求完成辩论15总计100分入学系号出生日期学号USEmarketGOCREATETABLE学生根本信息(学号INTPRIMARYKEY,姓名VARCHAR(10)NOTNULL,性别VARCHAR(50),出生日期INT,入学成绩INT,所在系号INT,)GOUSEmarketGOCREATETABLE系部根本信息(系号INTPRIMARYKEY,系名称VARCHAR(50)NOTNULL,系的简介VARCHAR(50)GOUSEmarketGOCREATETABLE课程信息(课程号INTPRIMARYKEY,课程名称VARCHAR(50)NOTNULL,任课教师号INT,学时INT,学分INT,上课时间VARCHAR(50),上课地点VARCHAR(50),考试时间VARCHAR(50)GOUSEmarketGOCreatetable教职工信息(职工号INTPRIMARYKEY,教师姓名VARCHAR(IO)NOTNULL,性别VARCHAR(50),出身年月INT,所在系号INT,职称VARCHAR(50),技术专长VARCHAR(50)GOUSEmarketGOINSERT学生根本信息（学号,姓名,性别,出生日期,入学成绩,所在系号）VALUES（,12432201王晓明？男'；1994072,'278','4'）INSERT学生根本信息VALUES（'12432202;吴小丽女19930916,276','4'）INSERT学生根本信息VALUES（,12432203','章明敏,'女19940812','28,'4'）INSERT学生根本信息VALUES（'12432204','王孝亚男；1994072,273','4'）INSERT学生根本信息VALUES（,12432205','李明月女19930916','263','4'）INSERT学生根本信息VALUES（'12432206;任心？女19940812','267','4'）INSERT学生根本信息VALUES（,12432207;吴佳丽女19930916,'294','4'）INSERT学生根本信息VALUES（'12432208','王丽？女9940812','287','4'）INSERT学生根本信息VALUES（,12432209','朱明','男19940721','278','4'）INSERT学生根本信息VALUES（'12432210;章自强?男'；19930916,290','4'）GOUSEmarketGOINSERT系部根本信息（系号,系名称,系的简介）VALUES（Tr经济贸易学院：经济贸易）INSERT系部根本信息VALUES（'2:会计经融学院？会计经融）INSERT系部根本信息VALUES（3：旅游管理学院？旅游管理）INSERT系部根本信息VALUES（4;信息工程学院？信息工程INSERT系部根本信息VALUES（'5:商务外语学院:商务外语）INSERT系部根本信息VALUES（'6:电子工程学院？电子工程）INSERT系部根本信息VALUES（7,机电工程学院？机电工程）INSERT系部根本信息VALUES（'8:艺术设计学院？艺术设计）GOUSEmarketGOINSERT课程信息（课程号,课程名称,任课教师号,学时,学分,上课时间,上课地点,考试时间）VALUES（T,'数学',T,'40',2,'周一、四','教3-A209,106-29,）INSERT课程信息VALUES（2,'体育',2,'40',2,'周一','体育馆','0624'）INSERT课程信息VALUES（3；商业应用文写作,3,4012,周一。教3-A408,06-24,）INSERT课程信息VALUES（'4','实用英语？4','40',2,'周二、五','教3-C304',06-30,）INSERT课程信息VALUES（'5?影视鉴赏？5','16,'2',周一','教3-A408',06-02,）INSERT课程信息VALUES（'6?音乐鉴赏？6'J6,2,调五？教3-C304',06-06,）GOUSEmarketGOINSERT教职工信息（职工号,教师姓名,性别,出身年月,所在系号,职称,技术专长）VALUES（'王明？女？19650913:4:教师？数学）INSERT教职工信息VALUES（'2?李丽？女？19830313','4:教师？体育）INSERT教职工信息VALUES（3,'徐新？女？19750913',2；教师？语文'）INSERT教职工信息VALUES（'4；王曦？女？19840313','4:教师？英语）INSERT教职工信息VALUES（5；王翔；男；19650913,Z'教师影视'）INSERT教职工信息VALUES（'6?李雨？男？19650913'21教师？音乐）GO建立外键Altertable课程信息addconstraintFk_课程信息.教职工信息foreignkey（任课教师号）REFERENCES教职工信息（职工号）goUSEmarketGOCreatetable选课信息（学号INTPRIMARYKEY,教师姓名VARCHAR（Io）NOTNULL,课程号INT,课程名称Varchar（IO）5平时成绩INT,考试成绩INT,总评成绩INT）GOUSEmarketGOINSERT选课信息（学号,姓名,课程号,课程名称,平时成绩,考试成绩,总评成绩）VALUES（'12432201'王晓明','6?音乐鉴赏,70'80'75'）INSERT选课信息VALUES（'12432202;吴小丽影视鉴赏','50','60','55'）INSERT选课信息VALUES（'12432203','章明敏',6,'音乐鉴赏','55','65','60'）INSERT选课信息VALUES（'12432204','王孝亚'5?影视鉴赏','80','80','80'）INSERT选课信息VALUES（'12432205','李明月','5?影视鉴赏','90','90','90'）INSERT选课信息VALUES（'12432206;任心、','6:音乐鉴赏','90','80','85'）INSERT选课信息VALUES（'12432207;吴佳丽','5?影视鉴赏','65','65','65'）INSERT选课信息VALUES（'12432208','王丽',6,'音乐鉴赏','55','65','60'）INSERT选课信息VALUES（'12432209','朱明','6:音乐鉴赏','95','95','95'）INSERT选课信息VALUES（'124322107$自强,5,影视鉴赏,7018（T,75,）GOUSEmarketGOCREATETABLE数学成绩信息（课程号INT,学号INT,姓名VARCHAR（10）NOTNULL,平时成绩INT,考试成绩INT,总评成绩INT)GO数学成绩信息USEmarketGOINSERT数学成绩信息(课程号,学号,姓名,平时成绩,考试成绩,总评成绩)VALUES(',l243220,'三晓明',70','80','75')INSERT数学成绩信息VALUES(2J2432202;吴小丽？50?60:55)INSERT数学成绩信息VALUES(,3,12432203;章明敏','55','65','60)INSERT数学成绩信息VALUES(4J24322041王孝亚；80:80:80)INSERT数学成绩信息VALUES(52432205;李明月？90:90:9(T)INSERT数学成绩信息VALUES(6124322067任心?9(,8(T,85)INSERT数学成绩信息VALUES(,7,12432207;吴佳丽？65?65:65)INSERT数学成绩信息VALUES(18,;12432208;王丽？55；65160，)INSERT数学成绩信息VALUES('9V12432209?朱明','95','95','95')INSERT数学成绩信息VALUES('10','124322107章自强；70:80；75')GOUSEmarketGOCREATETABLE体育成绩信息(课程号INT,学号INT,姓名VARCHAR(IO)NOTNULL,平时成绩INT,考试成绩INT,总评成绩INT)GO体育成绩信息USEmarketGOINSERT体育成绩信息(课程号,学号,姓名,平时成绩,考试成绩,总评成绩)VALUES(','1243220,'三晓明',70','80','75')INSERT体育成绩信息VALUES(2J2432202;吴小丽？50?60:55)INSERT体育成绩信息VALUES(,3V12432203;章明敏','55','65','60)INSERT体育成绩信息VALUES(4,J24322041王孝亚；80:80:80)INSERT体育成绩信息VALUES(52432205;李明月？90:90:9(T)INSERT体育成绩信息VALUES(6124322067任心?9(,8(T,85)INSERT体育成绩信息VALUES(,7,12432207;吴佳丽？65?65:65)INSERT体育成绩信息VALUES(18,;12432208;王丽？55；65160，)INSERT体育成绩信息VALUES('9V12432209?朱明','95','95','95')INSERT体育成绩信息VALUES('10','124322107章自强；70:80；75')GOUSEmarketGOCreatetable商业应用文写作成绩信息(课程号INT,学号INT,姓名VARCHAR(Io)NOTNULL,平时成绩INT,考试成绩INT,总评成绩INT)GO商业应用文写作成绩信息USEmarketGOINSERT商业应用文写作成绩信息(课程号,学号,姓名,平时成绩,考试成绩,总评成绩)VALUES(Tj2432201王晓明','70','80','75')INSERT商业应用文写作成绩信息VALUES(2J2432202;吴小丽？50:60:55)INSERT商业应用文写作成绩信息VALUES('3,12432203','章明敏？55','65','6O)INSERT商业应用文写作成绩信息VALUES（4,'12432204；王孝亚？80:80180,）INSERT商业应用文写作成绩信息VALUES（,5',124322051李明月,90,90,（90,）INSERT商业应用文写作成绩信息VALUES（6124322061任心？90:80:,85，）INSERT商业应用文写作成绩信息VALUES（,7','12432207;吴佳丽？65:65:65）INSERT商业应用文写作成绩信息VALUES（,8,12432208;王丽？55；65160，）INSERT商业应用文写作成绩信息VALUES（19'12432209;朱明？95','95','95'）INSERT商业应用文写作成绩信息VALUES（,10'124322107自强','70?80775'）GOUSEmarketGOCREATETABLE实用英语信息(课程号INT,学号INT,姓名VARCHAR(IO)NOTNULL,平时成绩INT,考试成绩INT,总评成绩INT)GO实用英语信息USEmarketGOINSERT实用英语信息(课程号,学号,姓名,平时成绩,考试成绩,总评成绩)VALUES(Tj243220,'E晓明','70','80','75')INSERT实用英语信息VALUES(2J2432202;吴小丽？50:60:55)INSERT实用英语信息VALUES(,3'12432203;章明敏','55','65','60)INSERT实用英语信息VALUES(,4'12432204；王孝亚；80:80:80)INSERT实用英语信息VALUES(512432205;李明月？90?90:90)INSERT实用英语信息VALUES(,6',12432206;任心？90Y8(T,85)INSERT实用英语信息VALUES(,7'12432207;吴佳丽','65','65','65')INSERT实用英语信息VALUES（18,;12432208;王丽？55；65160，）INSERT实用英语信息VALUES（,9',12432209?朱明','95','95','95'）INSERT实用英语信息VALUES（'10','124322107章自强；70:80；75'）GO修改语句USEmarketGOUPDATE学生根本信息SET姓名=，王晓明;出生日期+19930721'WHERE学号=12432201GO删除语句USEmarketGODELETE学生根本信息WHERE学号=12432210GO查询可以按学号、姓名、系号查询学生根本信息。USEmarketGOSELECT学号，姓名，所在系号FROM学生根本信息可以按职工号、姓名、系号查询教职工根本信息。USEmarketGOSELECT职工号,教师姓名,所在系号FROM教职工信息可以按系号、系名称查询系的根本信息。USEmarketGOSELECT系号,系的简介FROM系部根本信息可以按课程号、课程名称、上课教师姓名查询课程根本信息。SELECTa.课程号，a.课程名称，b.教师姓名FROM课程信息ASaINNERJOIN教职工信息ASbONa.课程号=b.职工号按学号、课程号、课程名称、上课教师姓名、系号查询学生成绩，内容包括课程根本情况。假设查询涉及多门课程，那么按课程分组。每门课程按总评成绩从高分到低分给出选修该门课程的所有学生的成绩（平时成绩、考试成绩和总评成绩）。体育USEmarketGOSELECT课程信息.课程号，课程信息、.课程名称，教职工信息.姓名，教职工信息.所在系号，体育成绩信息.学号，体育成绩信息.平时成绩，体育成绩信息.考试成绩，体育成绩信息.总评成绩FROM课程信息工NNERJOIN教职工信息ON课程信息.课程号=教职工信息.职工号INNERJOIN体育成绩信息on体育成绩信息.课程号=课程信息.课程号groupby课程信息.课程号，课程信息.课程名称，教职工信息.姓名，教职工信息.所在系号，体育成绩信息.学号，体育成绩信息.平时成绩，体育成绩信息.考试成绩，体育成绩信息.总评成绩orderby体育成绩信息.总评成绩desc数学USEmarketGOSELECT课程信息.课程号，课程信息.课程名称，教职工信息.姓名，教职工信息.所在系号，数学成绩信息.学号，数学成绩信息.平时成绩，数学成绩信息.考试成绩，数学成绩信息.总评成绩FROM课程信息工NNERJOIN教职工信息ON课程信息.课程号=教职工信息.职工号INNERJOIN数学成绩信息on数学成绩信息.课程号=课程信息.课程号groupby课程信息.课程号，课程信息.课程名称，教职工信息.姓名，教职工信息.所在系号，数学成绩信息.学号，数学成绩信息.平时成绩，数学成绩信息.考试成绩，数学成绩信息.总评成绩orderby数学成绩信息.总评成绩desc商业应用文写作USEmarketGOSELECT课程信息.课程号，课程信息.课程名称，教职工信息.姓名，教职工信息.所在系号，商业应用文写作成绩信息.学号，商业应用文写作成绩信息.平时成绩，商业应用文写作成绩信息.考试成绩，商业应用文写作成绩信息.总评成绩FROM课程信息INNERJOIN教职工信息ON课程信息.课程号=教职工信息.职工号INNERJOIN商业应用文写作成绩信息On商业应用文写作成绩信息.课程号=课程信息.课程号groupby课程信息.课程号，课程信息.课程名称，教职工信息.姓名，教职工信息.所在系号，商业应用文写作成绩信息.学号，商业应用文写作成绩信息.平时成绩，商业应用文写作成绩信息.考试成绩，商业应用文写作成绩信息.总评成绩orderby商业应用文写作成绩信息.总评成绩desc实用英语USEmarketGOSELECT课程信息.课程号，课程信息.课程名称，教职工信息.姓名,教职工信息.所在系号，实用英语信息.学号，实用英语信息.平时成绩，实用英语信息.考试成绩，实用英语信息.总评成绩FROM课程信息INNERJOIN教职工信息ON课程信息.课程号=教职工信息.职工号INNERJOIN实用英语信息On实用英语信息.课程号=课程信息.课程号groupby课程信息.课程号，课程信息.课程名称，教职工信息.姓名，教职工信息.所在系号，实用英语信息.学号，实用英语信息.平时成绩，实用英语信息.考试成绩，实用英语信息.总评成绩orderby实用英语信息.总评成绩desca、成绩报表：内容包括课程根本信息（课程号、课程名称、任课教师号、学时、学分），选课学生名单（学号、姓名、性别），每个学生的平时成绩、考试成绩和总评成绩USEmarketGOCREATEVIEW成绩报表ASSELECT课程信息.课程号，课程信息.课程名称，课程信息.任课教师号，课程信息.学时，课程信息.学分,教职工信息,.教师姓名，选课信息.学号，选课信息.平时成绩，选课信息.考试成绩，选课信息.总评成绩，学生根本信息.性别FROM课程信息工NNERJOIN教职工信息ON课程信息.课程号=教职工信息.职工号INNERJOIN选课信息On选课信息.课程号=课程信息.课程号INNERJOIN学生根本信息On学生根本信息.学号=选课信息.学号groupby课程信息、.课程号，课程信息.课程名称，课程信息.任课教师号，课程信息.学时，课程信息.学分，教职工信息.教师姓名，选课信息.学号，选课信息.平时成绩，选课信息.考试成绩，选课信息.总评成绩，学生根本信息.性别g。USEmarketGO-建立信息的存储过程CREATEPROCEDUREorderlSET(三)SelectKey='课程号，ASSELECT*FROM数学成绩信息工NNERJOIN选课信息工NNERJOIN体育成绩信息工NNERJOIN商业应用文写作成绩信息INNERJOIN实用英语信息WHERE总评成绩=90-比拟运算符- 调用存储过程EXECUTEorderlUSEmarketGO- 建立信息的存储过程CREATEPROCEDUREorderlSELECT>=90人数=COUNT(ALL>=90)FROMorderlrenshuUSEmarketGO- 建立信息的存储过程CREATEPROCEDUREorderlbiliASSELECT>=90分所在比例=AVG(distinct10)FROMorderlrenshu