📄 教务系统数据库设计.sql
字号:
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '01','2007', '0103', 3, '1','2007','2008', '1')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '02','2006', '0201', 3, '1','2006','2007', '1')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '02','2006', '0202', 3, '1','2006','2007', '1')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '02','2006', '0201', 3, '2','2006','2007', '2')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '02','2006', '0202', 3, '2','2006','2007', '2')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '02','2006', '0201', 3, '3','2007','2008', '1')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '02','2006', '0202', 3, '3','2007','2008', '1')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '02','2006', '0201', 3, '4','2007','2008', '2')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '02','2006', '0202', 3, '4','2007','2008', '2')
CREATE TABLE Class( /* 10.班级 */
deptId VARCHAR(2) NOT NULL, /* 单位代码 */
grade VARCHAR(4) NOT NULL, /* 年级 */
majorId VARCHAR(4) NOT NULL, /* 专业代码 */
lengthOfStudy NUMERIC DEFAULT 3 /* 学制 */
CHECK(lengthOfStudy>0),
classId VARCHAR(10) NOT NULL, /* 班级代码 */
className VARCHAR(50) NOT NULL, /* 班级名称 */
classSimpleName VARCHAR(8) NOT NULL, /* 班级简称 */
teacherIdOfAssistant VARCHAR(5), /* 辅导员教师代码 */
teacherIdOfTutor VARCHAR(5), /* 专业导师教师代码 */
UNIQUE(classId),
UNIQUE(className),
UNIQUE(classSimpleName),
FOREIGN KEY(grade,majorId,lengthOfStudy) REFERENCES
Eurolment(grade,majorId,lengthOfStudy)
)
INSERT INTO Class(deptId, grade,majorId, classId, className,classSimpleName)
VALUES( '01','2006', '0101','2006010101','计算机及应用2006-1班', '计用0601')
INSERT INTO Class(deptId, grade,majorId, classId, className,classSimpleName)
VALUES( '01','2006', '0101','2006010102','计算机及应用2006-2班', '计用0602')
INSERT INTO Class(deptId, grade,majorId, classId, className,classSimpleName)
VALUES( '01','2006', '0102','2006010201','软件技术2006-1班', '软件0601')
INSERT INTO Class(deptId, grade,majorId, classId, className,classSimpleName)
VALUES( '01','2006', '0102','2006010202','软件技术2006-2班', '软件0602')
INSERT INTO Class(deptId, grade,majorId, classId, className,classSimpleName)
VALUES( '01','2006', '0102','2006010203','软件技术2006-3班', '软件0603')
INSERT INTO Class(deptId, grade,majorId, classId, className,classSimpleName)
VALUES( '01','2006', '0103','2006010301','计算机网络2006-1班', '网络0601')
INSERT INTO Class(deptId, grade,majorId, classId, className,classSimpleName)
VALUES( '01','2006', '0103','2006010302','计算机网络2006-2班', '网络0602')
INSERT INTO Class(deptId, grade,majorId, classId, className,classSimpleName)
VALUES( '01','2007', '0102','2007010201','软件技术2007-1班', '软件0701')
INSERT INTO Class(deptId, grade,majorId, classId, className,classSimpleName)
VALUES( '01','2007', '0102','2007010202','软件技术2007-2班', '软件0702')
INSERT INTO Class(deptId, grade,majorId, classId, className,classSimpleName)
VALUES( '01','2007', '0102','2007010203','软件技术2007-3班', '软件0703')
INSERT INTO Class(deptId, grade,majorId, classId, className,classSimpleName)
VALUES( '02','2006', '0201','2006020101','电子工程2006-1班', '电子0601')
INSERT INTO Class(deptId, grade,majorId, classId, className,classSimpleName)
VALUES( '02','2006', '0201','2006020102','电子工程2006-2班', '电子0602')
INSERT INTO Class(deptId, grade,majorId, classId, className,classSimpleName)
VALUES( '02','2006', '0202','2006020201','通信2006-1班', '通信0601')
INSERT INTO Class(deptId, grade,majorId, classId, className,classSimpleName)
VALUES( '02','2006', '0202','2006020202','通信2006-2班', '通信0602')
CREATE TABLE Mission( /* 11.课堂 */
year1 VARCHAR(4) NOT NULL, /* 学年1 */
year2 VARCHAR(4) NOT NULL, /* 学年2 */
term VARCHAR(1) NOT NULL, /* 学期 */
deptIdOfOfferCourse VARCHAR(2) NOT NULL, /* 开课单位代码 */
courseId VARCHAR(5) NOT NULL, /* 课程代码 */
missionNo VARCHAR(4) NOT NULL, /* 课堂编号 */
teacherId VARCHAR(5), /* 任课教师代码 */
classHour NUMERIC NOT NULL /* 学时 */
CHECK(classHour>0),
numberOfStudy NUMERIC, /* 修读人数 */
UNIQUE(year1,year2,term,courseId,missionNo)
)
CREATE TABLE LessonsProgram( /* 12.授课计划 */
deptId VARCHAR(2) NOT NULL, /* 单位代码 */
grade VARCHAR(4) NOT NULL, /* 年级 */
majorId VARCHAR(4) NOT NULL, /* 专业代码 */
lengthOfStudy NUMERIC DEFAULT 3 /* 学制 */
CHECK(lengthOfStudy>0),
termNo VARCHAR(2) NOT NULL, /* 学期序号 */
year1 VARCHAR(4) NOT NULL, /* 学年1 */
year2 VARCHAR(4) NOT NULL, /* 学年2 */
term VARCHAR(1) NOT NULL, /* 学期 */
courseId VARCHAR(5) NOT NULL, /* 课程代码 */
courseType VARCHAR(1) /* 课程性质代码: */
CHECK(courseType='1' OR courseType='2'), /* '1'--'必修'
'2'--'选修' */
credit NUMERIC(4,1) NOT NULL /* 学分 */
CHECK(credit>0),
classHour NUMERIC(5,0) NOT NULL /* 学时 */
CHECK(classHour>0),
UNIQUE(grade,majorId,lengthOfStudy,courseId),
FOREIGN KEY(grade,majorId,lengthOfStudy,termNo) REFERENCES
TermCreditPlan(grade,majorId,lengthOfStudy,termNo),
FOREIGN KEY(grade,majorId,lengthOfStudy,year1,year2,term) REFERENCES
TermCreditPlan(grade,majorId,lengthOfStudy,year1,year2,term)
)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2006','0102', '1','2006','2007', '1', '01001', '1', 4, 64)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2006','0102', '1','2006','2007', '1', '01002', '1', 6, 96)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2006','0102', '2','2006','2007', '2', '01003', '1', 4, 64)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2006','0102', '2','2006','2007', '2', '01004', '1', 4, 64)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2006','0102', '3','2007','2008', '1', '01005', '1', 4.5, 72)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2006','0102', '3','2007','2008', '1', '01006', '1', 4.5, 72)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2006','0102', '4','2007','2008', '2', '01007', '1', 6, 96)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2006','0102', '4','2007','2008', '2', '01008', '1', 4.5, 72)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2007','0102', '1','2007','2008', '1', '01001', '1', 4, 64)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2007','0102', '1','2007','2008', '1', '01002', '1', 6, 96)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2007','0102', '2','2007','2008', '2', '01003', '1', 4, 64)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2007','0102', '2','2007','2008', '2', '01004', '1', 4, 64)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2007','0102', '3','2008','2009', '1', '01005', '1', 4.5, 72)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2007','0102', '3','2008','2009', '1', '01006', '1', 4.5, 72)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2007','0102', '4','2008','2009', '2', '01007', '1', 6, 96)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2007','0102', '4','2008','2009', '2', '01008', '1', 4.5, 72)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2006','0201', '1','2006','2007', '1', '01001', '1', 4, 64)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2006','0201', '1','2006','2007', '1', '01002', '1', 6, 96)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2006','0201', '2','2006','2007', '2', '02001', '1', 4, 64)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2006','0201', '2','2006','2007', '2', '02002', '1', 6, 96)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2006','0202', '3','2007','2008', '1', '01001', '1', 4, 64)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2006','0202', '3','2007','2008', '1', '01002', '1', 6, 96)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2006','0202', '4','2007','2008', '2', '02001', '1', 4, 64)
INSERT INTO LessonsProgram(
deptId,grade,majorId,termNo, year1, year2,term,courseId,courseType,credit,classHour)
VALUES( '01','2006','0202', '4','2007','2008', '2', '02002', '1', 6, 96)
CREATE TABLE Student( /* 13.学生 */
classId VARCHAR(10) NOT NULL, /* 班级代码 */
studentId VARCHAR(12) NOT NULL, /* 学号 */
studentName VARCHAR(30) NOT NULL, /* 姓名 */
sex VARCHAR(1), /* 性别代码:
'1'--'男',
'2'--'女' */
personalID VARCHAR(18), /* 身份证号码 */
birthDate DATETIME, /* 出生日期 */
enteranceDate DATETIME, /* 入学日期 */
CET3 NUMERIC /* 外语3级成绩 */
CHECK(CET3>=0 AND CET3<=100),
CET4 NUMERIC /* 外语4级成绩 */
CHECK(CET4>=0 AND CET4<=700),
CET6 NUMERIC /* 外语6级成绩 */
CHECK(CET6>=0 AND CET6<=700),
creditOfRequiredCourse NUMERIC DEFAULT 0 /* 必修课完成学分 */
CHECK(creditOfRequiredCourse>=0),
creditOfElectiveCourse NUMERIC DEFAULT 0 /* 选修课完成学分 */
CHECK(creditOfElectiveCourse>=0),
credit NUMERIC DEFAULT 0 /* 完成学分 */
CHECK(credit>=0),
graduationStatus VARCHAR(1), /* 毕业结论:
'1'--'毕业',
'2'--'结业' */
graduationDate DATETIME, /* 毕(结)业日期 */
elecRegistId VARCHAR(18), /* 电子注册号 */
certificateId VARCHAR(20), /* 毕业证书编号 */
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -