编辑代码

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)
);