📄 教务系统数据库设计.sql
字号:
CREATE TABLE Term( /* 1.学期 */
year1 VARCHAR(4) NOT NULL, /* 学年1 */
year2 VARCHAR(4) NOT NULL, /* 学年2 */
term VARCHAR(1) NOT NULL, /* 学期 */
UNIQUE(year1,year2,term)
)
INSERT INTO Term VALUES('2005','2006','1')
INSERT INTO Term VALUES('2005','2006','2')
INSERT INTO Term VALUES('2006','2007','1')
INSERT INTO Term VALUES('2006','2007','2')
INSERT INTO Term VALUES('2007','2008','1')
INSERT INTO Term VALUES('2007','2008','2')
INSERT INTO Term VALUES('2008','2009','1')
INSERT INTO Term VALUES('2008','2009','2')
CREATE TABLE Department( /* 2.单位 */
deptId VARCHAR(2) NOT NULL, /* 单位代码 */
deptName VARCHAR(30) NOT NULL, /* 单位名称 */
isOfferCourse VARCHAR(1) DEFAULT '0', /* 开课标记:
'1'--'是',
'0'--'否' */
isOfferMajor VARCHAR(1) DEFAULT '0', /* 办学标记:
'1'--'是',
'0'--'否' */
UNIQUE(deptId),
UNIQUE(deptName)
)
INSERT INTO Department VALUES('01','计算机系','1','1')
INSERT INTO Department VALUES('02','电子系', '1','1')
INSERT INTO Department VALUES('03','经济系', '1','1')
INSERT INTO Department VALUES('04','管理系', '1','1')
INSERT INTO Department VALUES('05','人文系', '1','1')
INSERT INTO Department VALUES('06','艺术系', '1','1')
INSERT INTO Department VALUES('30','宣传部', '0','0')
INSERT INTO Department VALUES('11','基础课部','1','0')
INSERT INTO Department VALUES('15','教务处', '0','0')
INSERT INTO Department VALUES('20','培训中心','0','1')
INSERT INTO Department VALUES('35','人事处', '0','0')
CREATE TABLE Grade( /* 3.年级 */
grade VARCHAR(4) NOT NULL, /* 年级 */
UNIQUE(grade)
)
INSERT INTO Grade VALUES('2005')
INSERT INTO Grade VALUES('2006')
INSERT INTO Grade VALUES('2007')
CREATE TABLE Teacher( /* 4.教师 */
deptId VARCHAR(2) NOT NULL, /* 单位代码 */
teacherId VARCHAR(5) NOT NULL, /* 教师代码 */
teacherName VARCHAR(30) NOT NULL, /* 教师名称 */
sex VARCHAR(1), /* 性别代码:
'1'--'男',
'2'--'女' */
isFullTime VARCHAR(1), /* 全职标记:
'1'--'是',
'0'--'否' */
UNIQUE(teacherId),
FOREIGN KEY(deptId) REFERENCES Department(deptId)
)
INSERT INTO Teacher VALUES('01','01001','张三' ,'1','1')
INSERT INTO Teacher VALUES('01','01002','张小三','1','0')
INSERT INTO Teacher VALUES('01','01003','张四', '1','1')
INSERT INTO Teacher VALUES('01','01004','张小四','2','0')
INSERT INTO Teacher VALUES('02','02001','王五' ,'1','1')
INSERT INTO Teacher VALUES('02','02002','王小五','1','0')
INSERT INTO Teacher VALUES('02','02003','王铁', '1','1')
INSERT INTO Teacher VALUES('02','02004','王坤', '2','0')
INSERT INTO Teacher VALUES('03','03001','刘五' ,'1','1')
INSERT INTO Teacher VALUES('03','03002','刘小五','1','0')
INSERT INTO Teacher VALUES('03','03003','刘铁', '1','1')
INSERT INTO Teacher VALUES('03','03004','刘坤', '2','0')
CREATE TABLE Course( /* 5.课程 */
deptId VARCHAR(2) NOT NULL, /* 开课单位代码 */
courseId VARCHAR(5) NOT NULL, /* 课程代码 */
courseName VARCHAR(50) NOT NULL, /* 课程名称 */
UNIQUE(courseId),
UNIQUE(courseName),
FOREIGN KEY(deptId) REFERENCES Department(deptId)
)
INSERT INTO Course VALUES('01','01001','计算机应用基础')
INSERT INTO Course VALUES('01','01002','VB.NET程序设计')
INSERT INTO Course VALUES('01','01003','计算机组成原理')
INSERT INTO Course VALUES('01','01004','C语言程序设计')
INSERT INTO Course VALUES('01','01005','数据结构')
INSERT INTO Course VALUES('01','01006','数据库原理与应用')
INSERT INTO Course VALUES('01','01007','ASP.NET程序设计')
INSERT INTO Course VALUES('01','01008','软件工程')
INSERT INTO Course VALUES('02','02001','模拟电路')
INSERT INTO Course VALUES('02','02002','数字电路')
INSERT INTO Course VALUES('05','05001','英语1')
INSERT INTO Course VALUES('05','05002','英语2')
INSERT INTO Course VALUES('05','05003','英语3')
INSERT INTO Course VALUES('05','05004','英语4')
CREATE TABLE Major( /* 6.专业 */
deptId VARCHAR(2) NOT NULL, /* 单位代码 */
majorId VARCHAR(4) NOT NULL, /* 专业代码 */
majorName VARCHAR(50) NOT NULL, /* 专业名称 */
UNIQUE(majorId),
UNIQUE(majorName),
FOREIGN KEY(deptId) REFERENCES Department(deptId)
)
INSERT INTO Major VALUES('01','0101','计算机及应用')
INSERT INTO Major VALUES('01','0102','软件技术')
INSERT INTO Major VALUES('01','0103','计算机网络工程')
INSERT INTO Major VALUES('01','0104','动漫')
INSERT INTO Major VALUES('02','0201','电子工程')
INSERT INTO Major VALUES('02','0202','通信')
CREATE TABLE CourseQueryCode( /* 7.课程查询码 */
courseId VARCHAR(5) NOT NULL, /* 课程代码 */
queryCode VARCHAR(30) NOT NULL, /* 课程查询码 */
UNIQUE(courseId,queryCode),
FOREIGN KEY(courseId) REFERENCES Course(courseId)
)
INSERT INTO CourseQueryCode VALUES('01001','jsjyyjc')
INSERT INTO CourseQueryCode VALUES('01001','jsjjc')
INSERT INTO CourseQueryCode VALUES('01001','jsj')
INSERT INTO CourseQueryCode VALUES('01001','jsjyy')
INSERT INTO CourseQueryCode VALUES('01002','vb.net')
INSERT INTO CourseQueryCode VALUES('01002','vb')
INSERT INTO CourseQueryCode VALUES('01002','vbcxsj')
INSERT INTO CourseQueryCode VALUES('01002','vb.netcxsj')
CREATE TABLE Eurolment( /* 8.招生(培养计划) */
deptId VARCHAR(2) NOT NULL, /* 单位代码 */
grade VARCHAR(4) NOT NULL, /* 年级 */
majorId VARCHAR(4) NOT NULL, /* 专业代码 */
lengthOfStudy NUMERIC DEFAULT 3 /* 学制 */
CHECK(lengthOfStudy>0 AND lengthOfStudy<=6),
creditOfRequiredCourse NUMERIC DEFAULT 0 /* 必修课学分 */
CHECK(creditOfRequiredCourse>=0),
creditOfElectiveCourse NUMERIC DEFAULT 0 /* 选修课学分 */
CHECK(creditOfElectiveCourse>=0),
isSeparated VARCHAR(1) DEFAULT '0', /* 分流标记:
'1'--'是',
'0'--'否' */
UNIQUE(grade,majorId,lengthOfStudy),
FOREIGN KEY(majorId) REFERENCES Major(majorId),
FOREIGN KEY(deptId) REFERENCES Department(deptId),
FOREIGN KEY(grade) REFERENCES Grade(grade)
)
INSERT INTO Eurolment(deptId,grade,majorId) VALUES('01','2005','0101')
INSERT INTO Eurolment(deptId,grade,majorId) VALUES('01','2005','0102')
INSERT INTO Eurolment(deptId,grade,majorId) VALUES('01','2005','0103')
INSERT INTO Eurolment(deptId,grade,majorId) VALUES('01','2006','0101')
INSERT INTO Eurolment(deptId,grade,majorId) VALUES('01','2006','0102')
INSERT INTO Eurolment(deptId,grade,majorId) VALUES('01','2006','0103')
INSERT INTO Eurolment(deptId,grade,majorId) VALUES('01','2006','0104')
INSERT INTO Eurolment(deptId,grade,majorId) VALUES('01','2007','0101')
INSERT INTO Eurolment(deptId,grade,majorId) VALUES('01','2007','0102')
INSERT INTO Eurolment(deptId,grade,majorId) VALUES('01','2007','0103')
INSERT INTO Eurolment(deptId,grade,majorId) VALUES('02','2005','0201')
INSERT INTO Eurolment(deptId,grade,majorId) VALUES('02','2005','0202')
INSERT INTO Eurolment(deptId,grade,majorId) VALUES('02','2006','0201')
INSERT INTO Eurolment(deptId,grade,majorId) VALUES('02','2006','0202')
INSERT INTO Eurolment(deptId,grade,majorId) VALUES('02','2007','0201')
INSERT INTO Eurolment(deptId,grade,majorId) VALUES('02','2007','0202')
CREATE TABLE TermCreditPlan( /* 9.学期计划 */
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, /* 学期 */
creditOfRequiredCourse NUMERIC DEFAULT 0 /* 必修课学分 */
CHECK(creditOfRequiredCourse>=0),
UNIQUE(grade,majorId,lengthOfStudy,termNo),
UNIQUE(grade,majorId,lengthOfStudy,year1,year2,term),
FOREIGN KEY(grade,majorId,lengthOfStudy) REFERENCES
Eurolment(grade,majorId,lengthOfStudy)
)
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '01','2006', '0101', 3, '1','2006','2007', '1')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '01','2006', '0102', 3, '1','2006','2007', '1')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '01','2006', '0103', 3, '1','2006','2007', '1')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '01','2006', '0101', 3, '2','2006','2007', '2')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '01','2006', '0102', 3, '2','2006','2007', '2')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '01','2006', '0103', 3, '2','2006','2007', '2')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '01','2006', '0101', 3, '3','2007','2008', '1')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '01','2006', '0102', 3, '3','2007','2008', '1')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '01','2006', '0103', 3, '3','2007','2008', '1')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '01','2006', '0101', 3, '4','2007','2008', '2')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '01','2006', '0102', 3, '4','2007','2008', '2')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '01','2006', '0103', 3, '4','2007','2008', '2')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '01','2007', '0101', 3, '1','2007','2008', '1')
INSERT INTO TermCreditPlan(deptId, grade,majorId,lengthOfStudy,termNo,year1, year2,term)
VALUES( '01','2007', '0102', 3, '1','2007','2008', '1')
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -