高校成绩管理系统数据库设计与实现（含源码和报告）

/**************************建表开始**************************/ /*创建班级表*/ CREATE TABLE nirszhangy_Classes ( nrszy_ClassNo CHAR(12) PRIMARY KEY,/*班级编号*/ nrszy_Deptname CHAR(20),/*专业名称*/ ) /*创建学生表*/ CREATE TABLE nirszhangy_Students ( nrszy_Sno CHAR(12) PRIMARY KEY ,/*学号*/ nrszy_Spassword CHAR(15) DEFAULT 123456,/*登录密码（默认123456）*/ nrszy_ClassNo CHAR(12) NOT NULL,/*班级编号*/ nrszy_Sname CHAR(20),/*姓名*/ nrszy_Ssex CHAR(2),/*性别*/ nrszy_Sage INT CHECK(nrszy_Sage>0),/*年龄*/ nrszy_Splace CHAR(10),/*生源所在地*/ nrszy_Scredit char(8),/*已修学分总数*/ nrszy_Sarea CHAR(20),/*地区信息*/ CONSTRAINT ClassNo_Students FOREIGN KEY (nrszy_ClassNo) REFERENCES nirszhangy_Classes, /*nrszy_ClassNo是nirszhangy_Classes的外键*/ ) SELECT * FROM nirszhangy_Reports drop table nirszhangy_Reports drop table nirszhangy_Students alter table nirszhangy_Students alter column nrszy_Scredit char(8) /*创建教师表*/ CREATE TABLE nirszhangy_Teachers ( nrszy_Tno CHAR(12) PRIMARY KEY,/*教师编号*/ nrszy_Tname CHAR(20),/*姓名*/ nrszy_Tsex CHAR(2),/*性别*/ nrszy_Tage INT CHECK(nrszy_Tage>0),/*年龄*/ nrszy_Ttitle CHAR(12),/*职称*/ nrszy_Tphone CHAR(15),/*联系电话*/ nrszy_Tpassword CHAR(15) DEFAULT 123456,/*登录密码（默认123456）*/ ) /*创建课程表*/ CREATE TABLE nirszhangy_Courses ( nrszy_Cno CHAR(12) PRIMARY KEY,/*课程编号*/ nrszy_Cname CHAR(20) NOT NULL,/*课程名称*/ nrszy_Cterm CHAR(12),/*开课学期*/ nrszy_Ctime INT CHECK(nrszy_Ctime>0),/*学时*/ nrszy_Ctestway CHAR(5),/*考试或考查*/ nrszy_Ccredit INT CHECK(nrszy_Ccredit>0),/*学分*/ nrszy_ClassNo CHAR(12),/*班级编号*/ nrszy_Tno CHAR(12),/*任课教师*/ CONSTRAINT ClassNo_Courses FOREIGN KEY (nrszy_ClassNo) REFERENCES nirszhangy_Classes, /*nrszy_ClassNo是nirszhangy_Classes的外键*/ ) /*创建教师授课表*/ CREATE TABLE nirszhangy_Teach ( nrszy_Tno CHAR(12),/*教师编号*/ nrszy_Cno CHAR(12),/*课程编号*/ PRIMARY KEY(nrszy_Tno,nrszy_Cno), CONSTRAINT Tno_Teach FOREIGN KEY (nrszy_Tno) REFERENCES nirszhangy_Teachers, /*nrszy_Tno是nirszhangy_Teachers的外键*/ CONSTRAINT Cno_Teach FOREIGN KEY (nrszy_Cno) REFERENCES nirszhangy_Courses, /*nrszy_Cno是nirszhangy_Courses的外键*/ ) /*创建成绩表*/ CREATE TABLE nirszhangy_Reports ( nrszy_Sno CHAR(12),/*学号*/ nrszy_Cno CHAR(12),/*课程编号*/ nrszy_Gterm CHAR(12),/*学期*/ nrszy_Grade INT CHECK(nrszy_Grade>=0 AND nrszy_Grade<=100),/*成绩*/ PRIMARY KEY(nrszy_Sno,nrszy_Cno), CONSTRAINT Sno_Reports FOREIGN KEY (nrszy_Sno) REFERENCES nirszhangy_Students, /*nrszy_Sno是nirszhangy_Students的外键*/ CONSTRAINT Cno_Reports FOREIGN KEY (nrszy_Cno) REFERENCES nirszhangy_Courses, /*nrszy_Cno是nirszhangy_Courses的外键*/ ) /**************************建表结束**************************/ /**************************创建视图开始**************************/ /*创建视图nirszhangy_stu_grade_view*/ CREATE VIEW nirszhangy_stu_grade_view AS SELECT nirszhangy_Students.nrszy_Sno,nirszhangy_Students.nrszy_Sname, nirszhangy_Courses.nrszy_Cno,nirszhangy_Courses.nrszy_Cname, nirszhangy_Reports.nrszy_Gterm,nirszhangy_Reports.nrszy_Grade FROM nirszhangy_Students,nirszhangy_Courses,nirszhangy_Reports WHERE nirszhangy_Students.nrszy_Sno = nirszhangy_Reports.nrszy_Sno AND nirszhangy_Courses.nrszy_Cno = nirszhangy_Reports.nrszy_Cno GO /*创建视图nirszhangy_courses_avg_grade*/ CREATE VIEW nirszhangy_courses_avg_grade AS SELECT nirszhangy_Reports.nrszy_Cno,nirszhangy_Courses.nrszy_Cname, AVG(nirszhangy_Reports.nrszy_Grade) avg_grade FROM nirszhangy_Reports,nirszhangy_Courses WHERE nirszhangy_Reports.nrszy_Cno = nirszhangy_Courses.nrszy_Cno GROUP BY nirszhangy_Reports.nrszy_Cno,nirszhangy_Courses.nrszy_Cname GO /*创建视图nirszhangy_stu_course_view*/ CREATE VIEW nirszhangy_stu_course_view AS SELECT nirszhangy_Reports.nrszy_Sno, nirszhangy_Reports.nrszy_Cno, nirszhangy_Courses.nrszy_Ccredit, nirszhangy_Courses.nrszy_Cname, nirszhangy_Students.nrszy_Sname FROM nirszhangy_Reports INNER JOIN nirszhangy_Courses ON nirszhangy_Reports.nrszy_Cno = nirszhangy_Courses.nrszy_Cno INNER JOIN nirszhangy_Students ON nirszhangy_Reports.nrszy_Sno = nirszhangy_Students.nrszy_Sno GO /*创建视图nirszhangy_Teaer_course_view*/ CREATE VIEW nirszhangy_Teaer_course_view AS SELECT nrszy_Tname,nrszy_Cname FROM nirszhangy_Teachers,nirszhangy_Courses WHERE nirszhangy_Teachers.nrszy_Tno = nirszhangy_Courses.nrszy_Tno GO /**************************创建视图结束**************************/ /**************************建立索引开始**************************/ /*建立对班级号的索引*/ CREATE UNIQUE INDEX classes_ClassNo ON nirszhangy_Classes(nrszy_ClassNo) /*建立对课程号的索引*/ CREATE UNIQUE INDEX courses_Cno ON nirszhangy_Courses(nrszy_Cno) /*建立对学号的索引*/ CREATE UNIQUE INDEX students_Sno ON nirszhangy_Students(nrszy_Sno) /*建立对教师工号的索引*/ CREATE UNIQUE INDEX teachers_Tno ON nirszhangy_Teachers(nrszy_Tno) /**************************建立索引结束**************************/ /**************************建立触发器开始**************************/ /*建立级联删除触发器UPD_delete*/ CREATE TRIGGER UPD_delete ON nirszhangy_Students FOR delete AS delete from nirszhangy_Reports where nrszy_Sno =(select nrszy_Sno from deleted) /***********************************/ /*建立触发器UPD_grade*/ CREATE TRIGGER UPD_grade ON nirszhangy_Reports FOR INSERT,UPDATE AS IF(SELECT nrszy_Grade FROM INSERTED) < 0 OR (SELECT nrszy_Grade FROM INSERTED) > 100 BEGIN PRINT '学生成绩必须是1~100之间的数！操作失败' ROLLBACK TRANSACTION END /*建立触发器UPD_credit*/ CREATE TRIGGER UPD_credit ON nirszhangy_Courses FOR INSERT,UPDATE AS IF(SELECT nrszy_Ccredit FROM INSERTED) < 0 BEGIN PRINT '学分必须是大于0的数！操作失败' ROLLBACK TRANSACTION END /*建立触发器UPD_grade2*/ /*用于统计学分*/ CREATE TRIGGER UPD_grade2 ON nirszhangy_Reports FOR INSERT,UPDATE AS UPDATE nirszhangy_Students SET nrszy_Scredit = (SELECT SUM(nrszy_Ccredit) FROM nirszhangy_Courses WHERE nrszy_Cno IN (SELECT nrszy_Cno FROM nirszhangy_Reports WHERE nirszhangy_Reports.nrszy_Grade >= 60 AND nirszhangy_Reports.nrszy_Sno IN (SELECT nrszy_Sno FROM INSERTED))) WHERE nirszhangy_Students.nrszy_Sno IN (SELECT nrszy_Sno FROM INSERTED) /*建立触发器BUD_Spassword*/ /*设置学生的默认登陆密码123456*/ CREATE TRIGGER BUD_Spassword ON nirszhangy_Students FOR INSERT AS UPDATE nirszhangy_Students SET nrszy_Spassword = '123456' WHERE nrszy_Sno IN(SELECT nrszy_Sno FROM INSERTED) /*建立触发器BUD_Tpassword*/ /*设置教师的默认登陆密码123456*/ CREATE TRIGGER BUD_Tpassword ON nirszhangy_Teachers FOR INSERT AS UPDATE nirszhangy_Teachers SET nrszy_Tpassword = '123456' WHERE nrszy_Tno IN(SELECT nrszy_Tno FROM INSERTED) /*建立触发器SET_Scredit*/ /*设置学分从0开始计*/ CREATE TRIGGER SET_Scredit2 ON nirszhangy_Students FOR INSERT AS IF(SELECT nrszy_Scredit FROM INSERTED) < 0 BEGIN PRINT '学分不能小于0的！操作失败' ROLLBACK TRANSACTION END /**************************建立触发器结束**************************/ /**************************建立存储过程开始**************************/ /*如果输入的学号不存在，则提示“学生基本信息表中不存在此学号，请检查后重新输入”*/ CREATE PROCEDURE InsertResult (@nrszy_Sno char(12), @nrszy_Cno char(12), @nrszy_Grade float ) AS SET NOCOUNT ON DECLARE @nCount int SET @nCount=0 DECLARE @nCourse int SET @nCourse=0 DECLARE @nExistAchievement int SET @nExistAchievement=0 SELECT @nCount=COUNT(nrszy_Sno) FROM nirszhangy_Students WHERE nirszhangy_Students.nrszy_Sno=@nrszy_Sno IF @nCount<1 BEGIN PRINT'学生基本信息表中不存在此学号，请检查后重新输入' RETURN END /*如果输入的课程不存在，则提示“还没有这门课程，请检查后重新输入”*/ SELECT @nCourse=COUNT(nrszy_Cno) FROM nirszhangy_Courses WHERE nirszhangy_Courses.nrszy_Cno=@nrszy_Cno IF @nCou