--用户登陆表
CREATE TABLE UserLogin(
userID BIGINT,
userPass VARCHAR(20) NOT NULL,
permission INT NOT NULL,--权限
CONSTRAINT PK_UserLoginID PRIMARY KEY(userID),--主键
)
--教师信息表
CREATE TABLE TeaInfo(
teaID BIGINT,
userName VARCHAR(20) NOT NULL,
sex CHAR(2) NOT NULL,
age INT NOT NULL,
phoneNum CHAR(11) NOT NULL,
CONSTRAINT PK_TeaID PRIMARY KEY (teaID),--主键
--教师ID依赖于登陆ID
CONSTRAINT FK_TeaLoginID FOREIGN KEY(teaID) REFERENCES UserLogin(userID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
--教师年龄约束
CONSTRAINT CN_UserAge CHECK(age<150 and age>0),
--电话唯一
CONSTRAINT AK_TeaphoneNum UNIQUE (phoneNum),
)
--班级信息表
CREATE TABLE ClassInfo(
classID BIGINT,
className VARCHAR(30),
deptID BIGINT,--系别
CONSTRAINT PK_ClassInfoID PRIMARY KEY(classID),--主键
)
--学生信息表
CREATE TABLE StuInfo(
stuID BIGINT,
classID BIGINT NOT NULL,
userName VARCHAR(20) NOT NULL,
sex CHAR(2) NOT NULL,
age INT NOT NULL,
phoneNum CHAR(11) NOT NULL,
startTime VARCHAR(10) NOT NULL,--入学时间
CONSTRAINT PK_StuInfoID PRIMARY KEY(stuID),--主键
--学生ID依赖于登陆ID
CONSTRAINT FK_stuLoginID FOREIGN KEY(stuID) REFERENCES UserLogin(userID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
--学生classID依赖于班级信息表的classID
CONSTRAINT FK_stuInClassID FOREIGN KEY(classID) REFERENCES ClassInfo(classID),
--学生年龄约束
CONSTRAINT CN_StuAge CHECK(age<150 and age>0),
--电话唯一
CONSTRAINT AK_StuphoneNum UNIQUE (phoneNum),
)
--课程详细信息表
CREATE TABLE CourseInfo(
courseID BIGINT,
courseName VARCHAR(30),
credits INT,
period INT,--学时
CONSTRAINT PK_CourseInfoID PRIMARY KEY(courseID),--主键
)
--课表
CREATE TABLE Syllabus(
classID BIGINT,
courseID BIGINT,
teacherID BIGINT,
classTime VARCHAR(30),--上课时间
venue VARCHAR(50),--上课地点
theYear INT,--学年
term INT,--学期
CONSTRAINT PK_Syllabus PRIMARY KEY(classID,courseID,classTime),--主键
--课程ID依赖于课程详细信息表的ID，并随着课程详细信息表删除而删除
CONSTRAINT FK_CourseIDSyllabus FOREIGN KEY(courseID) REFERENCES CourseInfo(courseID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
--教师ID依赖于教师信息表的ID
CONSTRAINT FK_TeaInfoIDSyllabus FOREIGN KEY(teacherID) REFERENCES TeaInfo(teaID),
--班级ID依赖于班级信息表，并随着班级信息表删除而删除
CONSTRAINT FK_ClassInfoIDSyllabus FOREIGN KEY(classID) REFERENCES ClassInfo(classID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
--学期小于
CONSTRAINT CN_ReportTerm CHECK(term<4),
--学年不能大于当前的年份
CONSTRAINT CN_ReportTheYear CHECK(theYear<=YEAR(GETDATE())),
)
--成绩报表
CREATE TABLE Report(
userID BIGINT,
courseID BIGINT,
grade FLOAT,
CONSTRAINT PK_ReportID PRIMARY KEY(userID,courseID),--主键
--成绩报表随着用户的删除而删除
CONSTRAINT FK_TeaInfoReportID FOREIGN KEY(userID) REFERENCES TeaInfo(teaID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
--课程依赖于课程信息表
CONSTRAINT FK_CourseReportID FOREIGN KEY(courseID) REFERENCES CourseInfo(courseID),
--成绩约束
CONSTRAINT CN_ReportGrade CHECK(grade>=0 and grade<=100),
)
--教师个人课表
create view TeacherSyllabus
as
select TeaInfo.teaID,TeaInfo.userName,CourseInfo.courseID,CourseInfo.courseName,Syllabus.classTime,Syllabus.Venue,Syllabus.classID,Syllabus.theYear,Syllabus.term
from Syllabus
left join CourseInfo on
Syllabus.courseID=CourseInfo.courseID
left join TeaInfo on
Syllabus.teacherID=TeaInfo.teaID
--学生个人课表
create procedure StudentSyllabus
@stuID bigint
as
select StuInfo.stuID,StuInfo.userName,CourseInfo.courseID,CourseInfo.courseName,Syllabus.classTime,Syllabus.venue,Syllabus.teacherID,Syllabus.classID,Syllabus.theYear,Syllabus.term
from Syllabus
left join CourseInfo on
Syllabus.courseID=CourseInfo.courseID
left join StuInfo on
Syllabus.classID=StuInfo.classID
where StuInfo.stuID=@stuID
--学生个人成绩单
create view StuReport
as
select StuInfo.stuID,StuInfo.userName,StuInfo.classID,CourseInfo.courseID,CourseInfo.courseName,CourseInfo.credits,Report.grade,Syllabus.theYear,Syllabus.term
from Report
left join StuInfo on
Report.userID=StuInfo.stuID
left join CourseInfo on
Report.courseID=CourseInfo.courseID
left join Syllabus on
Syllabus.courseID=Report.courseID
--单科成绩排名
create proc ReportByOrder
@courseID bigint,
@classID bigint
as
select StuInfo.stuID,StuInfo.userName,CourseInfo.courseID,CourseInfo.courseName,CourseInfo.credits,Report.grade
from Report
left join StuInfo on
StuInfo.stuID=Report.userID
left join CourseInfo on
CourseInfo.courseID=Report.courseID
where CourseInfo.courseID=@courseID and StuInfo.classID=@classID
order by grade desc
--班级排名
create proc ClassReportOrder
@classID bigint
as
select StuInfo.stuID,StuInfo.userName,SUM(grade) as 总分
from StuInfo
left join Report on
StuInfo.stuID=Report.userID
where StuInfo.classID=@classID
group by StuInfo.stuID,StuInfo.userName
order by sum(grade) desc,StuInfo.stuID
本文来源：https://www.2haoxitong.net/k/doc/7aadd6341ed9ad51f01df2eb.html
文档为doc格式