高校教务管理数据库.docx
《高校教务管理数据库.docx》由会员分享，可在线阅读，更多相关《高校教务管理数据库.docx（21页珍藏版）》请在冰豆网上搜索。
高校教务管理数据库
E-R模型设计
学生实体E-R图
专任教师实体E-R图
选课表实体E-R图
3.创建高校教务管理数据库
CREATEDATABASEJWGL
DEFAULTCHARACTERSETgb2312COLLATEgb2312_chinese_ci;
创建学生表
USEJWGL;
CREATETABLEstudent
（
学号CHAR（8）NOTNULL,
姓名VARCHAR（10）NOTNULL,
性别CHAR
（2）NOTNULL,
出生日期DATENOTNULL,
入学成绩INTNOTNULL,
系号INT
（2）NOTNULL,
籍贯VARCHAR（20）NOTNULL,
所在专业VARCHAR（20）NOTNULL,
PRIMARYKEY（学号）
）;
创建系部基本信息表
CREATETABLEdepartment（
系号INT
（2）NOTNULL,
系名称VARCHAR（50）NOTNULL,
系的简介VARCHAR（50）NOTNULL,
系主任CHAR（10）NOTNULL,
PRIMARYKEY（系号）
）;
创建课程信息表
CREATETABLEcourse
（
课程号INT（10）NOTNULL,
课程名称VARCHAR（20）NOTNULL,
专任教师号INT（10）NOTNULL,
学时INT（5）NOTNULL,
学分INT（5）NOTNULL,
上课时间DATENOTNULL,
上课地点VARCHAR（50）NOTNULL,
考试时间DATENOTNULL，
PRIMARYKEY（课程号）
）;
创建专任教师表
CREATETABLEteacher
（
职工号INT
（2）NOTNULL,
教师姓名VARCHAR（10）NOTNULL,
性别CHAR
（2）NOTNULL,
出身年月DATENOTNULL,
系号INT
（2）NOTNULL,
职称VARCHAR（50）NOTNULL,
技术专长VARCHAR（50）NOTNULL,
PRIMARYKEY（职工号）
）;
创建选修表
CREATETABLEelective（
学号CHAR（8）NOTNULL,
学生姓名VARCHAR（10）NOTNULL,
课程号INT（10）NOTNULL,
课程名称VARCHAR（20）NOTNULL,
专任教师号INT（10）NOTNULL,
学时INT（5）NOTNULL,
学分INT（5）NOTNULL,
上课时间DATENOTNULL,
上课地点VARCHAR（50）NOTNULL,
PRIMARYKEY（学号）
）;
创建成绩表
CREATETABLEgrade（
学号CHAR（8）NOTNULL,
学生姓名VARCHAR（10）NOTNULL,
课程号INT（10）NOTNULL,
课程名称VARCHAR（20）NOTNULL,
平时成绩INT（5）NOTNULL,
考试成绩INT（5）NOTNULL,
PRIMARYKEY（学号）
）;
完整性约束要求
在学生表中对学生“性别”列添加CHECK约束，保证性别只有‘男’和‘女’两个选项
ALTERTABLEstudent
ADDCHECK（性别IN（‘男’,’女’））;
在学生表中对学生“所在系号”列创建外键
ALTERTABLEstudent
ADDFOREIGNKEY（系号）
REFERENCESdepartment（系号）
ONDELETERESTRICT
ONUPDATERESTRICT;
4.插入学生基本信息
INSERTINTOstudentVALUES
（'12432201','小明','男','19940721','478','1','广东','软件'）;
INSERTINTOstudentVALUES
（'12432202','小丽','女','19930916','476','3','广西','英语'）;
INSERTINTOstudentVALUES
（'12432203','小敏','女','19940812','481','2','湖北','电信'）;
INSERTINTOstudentVALUES
（'12432204','小月','女','19930916','463','3','广东','德语'）;
INSERTINTOstudentVALUES
（'12432205','小朱','男','19940721','478','6','湖南','舞蹈'）;
INSERTINTOstudentVALUES
（'12432206','小强','男','19930916','490','1','广东','软件'）;
修改语句
UPDATEstudent
SET姓名='小白',出生日期='19930715'
WHERE学号='12432201';
删除语句
DELETEfromstudent
WHERE学号='12432206';
运行截图如下：
5.插入系部基本信息
INSERTINTOdepartmentVALUES
（'1','人工智能学院','人工智能','阿刚'）;
INSERTINTOdepartmentVALUES
（'2','信息工程学院','信息工程','阿强'）;
INSERTINTOdepartmentVALUES
（'3','商务外语学院','商务外语','阿勇'）;
INSERTINTOdepartmentVALUES
（'4','电子工程学院','电子工程','阿华'）;
INSERTINTOdepartmentVALUES
（'5','机电工程学院','机电工程','阿民'）;
INSERTINTOdepartmentVALUES
（'6','艺术设计学院','艺术设计','阿陈'）;
修改语句
UPDATEdepartment
SET系名称='医护学院',系主任='阿珍'
WHERE系号='2';
运行截图如下：
删除语句
DELETEfromdepartment
WHERE系号='1';
6.插入课程信息
INSERTINTOcourseVALUES
（'1','物理','1','20','2','周一','格物园','期末'）;
INSERTINTOcourseVALUES
（'2','体育','2','20','3','周二','操场','期末'）;
INSERTINTOcourseVALUES
（'3','文学','3','20','3','周三','日新楼','期末'）;
INSERTINTOcourseVALUES
（'4','英语','4','20','3','周五','日新楼','期末'）;
INSERTINTOcourseVALUES
（'5','电影','5','20','2','周三','日新楼','期末'）;
INSERTINTOcourseVALUES
（'6','音乐','6','20','2','周四','日新楼','期末'）;
修改语句
UPDATEcourse
SET课程名称='美术',学分='2'
WHERE课程号='1';
删除语句
DELETEfromcourse
WHERE课程号='1';
运行截图如下：
7.插入专任教师信息
INSERTINTOteacherVALUES
（'1','牛顿','女','19650913','4','教授','数学'）;
INSERTINTOteacherVALUES
（'2','刘翔','男','19830313','4','教授','体育'）;
INSERTINTOteacherVALUES
（'3','鲁迅','男','19750913','2','教授','语文'）;
INSERTINTOteacherVALUES
（'4','杨仁','男','19840313','4','教授','英语'）;
INSERTINTOteacherVALUES
（'5','王家卫','男','19650913','2','教授','影视'）;
INSERTINTOteacherVALUES
（'6','聂耳','女','19650913','2','教授','音乐'）;
修改语句
UPDATEteacher
SET教师姓名='姚明',出生年月='19930721'
WHERE职工号='2';
删除语句
DELETEFROMteacher
WHERE职工号='1';
运行截图如下：
8.可以按学号、姓名、系号查询学生基本信息,根据学号从小到大排序
SELECT学号,姓名,系号
FROMstudentORDERBY学号ASC;
运行截图如下：
9.可以按职工号、姓名、系号查询教职工基本信息
SELECT职工号,教师姓名,系号
FROMteacher;
运行截图如下：
10.可以按系号、系名称查询系的基本信息，按系号从小到大排列
SELECT系号,系名称
FROMdepartmentORDERBY系号ASC;
运行截图如下：
11.可以按课程号、课程名称、上课教师姓名查询课程基本信息，按课程号从小到大排序
SELECT课程号,课程名称,教师姓名
FROMcourseJOINteacher
ON专任教师号=职工号ORDERBY课程号ASC;
运行截图如下：
12.统计
a.成绩统计：
内容包括课程基本信息（课程号、课程名称、任课教师号、学时、学分），选课学生名单（学号、姓名、性别），每个学生的平时成绩、考试成绩和总评成绩。
能按课程号、课程名称、教师姓名输出对应课程的成绩
SELECTcourse.课程号,elective.专任教师号,elective.学时,elective.学分,elective.学生姓名,平时成绩,考试成绩,（平时成绩*0.6+考试成绩*0.4）as总评成绩
FROMcourseJOINelective
ONcourse.课程号=elective.课程号
JOINgrade
ongrade.学号=elective.学号
GROUPBYcourse.课程号
orderby总评成绩desc;
运行截图如下：
b.能够根据课程、授课教师统计成绩优、良、中、差、不及格学生的人数及比例
14.创建适当的视图
学生成绩视图：
CREATEVIEWstudent_grade
AS
（SELECTcourse.课程号,elective.专任教师号,elective.学时,elective.学分,elective.学生姓名,平时成绩,考试成绩,（平时成绩*0.6+考试成绩*0.4）as总评成绩
FROMcourseJOINelective
ON（course.课程号=elective.课程号）
JOINgrade
on（grade.学号=elective.学号））;
选修信息视图：
CREATEVIEWelective_information
AS
（SELECTcourse.课程名称,教师姓名,elective.上课时间,elective.上课地点,elective.学时,elective.学分
FROMcourseJOINelective
ON（course.课程号=elective.课程号）
JOINteacher
ON（teacher.职工号=elective.专任教师号））;
学生信息视图：
CREATEVIEWstudent_information
AS
（SELECT学号,姓名,性别,系名称,所在专业
FROMstudentJOINdepartment
ON（student.系号=department.系号））;
17.如何保证数据库安全：
a、对用户安全管理
首先要保证的就是数据库用户的权限的安全性，用户在访问数据库时，必须经过身份认证，对非超管用户，必须设定有限的权限和专用的密码。
如访问数据库要输入相应的密码，学生访问时权限小于老师和辅导员。
b、定义视图
为不同的用户定义不同的视图，可以限制用户的访问范围。
通过视图机制把需要保密的数据对无权存取这些数据的用户隐藏起来，可以对数据库提供一定程度的安全保护。
c、数据加密
数据安全隐患无处不在，因此对数据的加密，是保护数据库安全的有效措施。
数据加密是应用最广、成本最低廉而相对最可靠的方法。
数据加密是保护数据在存储和传递过程中不被窃取或修改的有效手段。
d、启动事务管理和故障恢复
事务管理和故障恢复主要是对付系统内发生的自然因素故障，保证数据和事务的一致性和完整性。
故障恢复的主要措施是进行日志记录和数据复制；事务运行的每一步结果都记录在系统日志文件中，并且对重要数据进行复制，发生故障时根据日志文件利用数据副本准确地完成事务的恢复。
e、数据库备份与恢复
数据库系统必须采取必要的措施，以保证发生故障时，可以恢复数据库。
数据备份需要仔细计划，制定出一个策略测试后再去实施，备份计划也需要不断地调整。
建立信息的存储过程
CREATEPROCEDUREorder1
SET@SelectKey='课程号'
AS
SELECT*FROM数学成绩信息INNERJOIN选课信息INNERJOIN体育成绩信息INNERJOIN商业应用文写作成绩信息INNERJOIN实用英语信息
WHERE总评成绩>=90
--比较运算符
--调用存储过程
EXECUTEorder1
建立信息的存储过程
CREATEPROCEDUREorder1
SELECT>=90人数=COUNT（ALL>=90）
FROMorder1renshu
建立信息的存储过程
CREATEPROCEDUREorder1bili
AS
SELECT>=90分所在比例=AVG（distinct10）FROMorder1renshu
升级会员 