CREATE DATABASE stu_course;
CREATE TABLE studentinfo
(
sno CHAR(8) PRIMARY KEY NOT NULL,
sname VARCHAR(10) NOT NULL,
sgender CHAR(1) CHECK(sgender='男'or sgender='女'),
sbirth DATETIME,
sclass VARCHAR(20)
);
CREATE TABLE teacher
(
tno CHAR(4) PRIMARY KEY NOT NULL,
tname VARCHAR(10) NOT NULL,
tgender CHAR(1) CHECK(tgender='男'or tgender='女'),
tedu VARCHAR(10),
tpro VARCHAR(8)
);
CREATE TABLE course
(
cno CHAR(4) PRIMARY KEY NOT NULL,
cname VARCHAR(40) UNIQUE,
cperiod INT,
credit DECIMAL(3,1),
ctno CHAR(4),
CONSTRAINT fk_teacher_course PRIMARY KEY(ctno) REFERENCES teacher(tno)
);
CREATE TABLE elective
(
sno CHAR(8),
cno CHAR(4),
score INT CHECK(score>=0 and score<=100),
PRIMARY KEY(sno,cno),
CONSTRAINT fk_course_elective FOREIGN KEY (cno) REFERENCES course (cno),
CONSTRAINT fk_stu_elective FOREIGN KEY (sno) REFERENCES studentinfo (sno)
);