CREATE TABLE Administrator(
    adId varchar(20) primary key,
    adName varchar(20),
    adPwd varchar(30)
);

CREATE TABLE student(
    sid varchar(20) primary key,
    sname varchar(20),
    spwd varchar(30)
);

CREATE TABLE teacher(
    tid varchar(20) primary key,
    tname varchar(20),
    tpwd varchar(30)
);

CREATE TABLE assistant(
    aid varchar(20) primary key,
    aname varchar(20),
    apwd varchar(30),
    tid varchar(20)，
    foreign key(tid) references teacher(tid)
);


CREATE TABLE course(
    cid varchar(20) primary key,
    cname varchar(20),
    ctime varchar(20),
    croom varchar(20),
    volume int,
    tid varchar(20),
    foreign key(tid) references teacher(tid)
);

CREATE TABLE selectCourse(
    sid varchar(20),
    cid varchar(20),
    foreign key(sid) references student(sid),
    foreign key(cid) references course(cid),
    primary key(sid,cid)
);

CREATE TABLE homework(
    hid integer primary key AUTO_INCREMENT,
    tid varchar(20),
    cid varchar(20),
    htime datetime,
    hcontent varchar(200),
    hlink varchar(100),
    studentRate float,
    teacherRate float,
    assistanceRate float,
    threshold double,
    title varchar(100),
    foreign key(tid) references teacher(tid),
    foreign key(cid) references course(cid),
);

CREATE TABLE submit(
    submitId integer primary key AUTO_INCREMENT,
    hid integer,
    sid varchar(20),
    submitTime datetime,
    submitContent varchar(200),
    submitLink varchar(100),
    similarity double,
    foreign key(hid) references homework(hid),
    foreign key(sid) references student(sid)
);

CREATE TABLE grade(
    submitId integer,
    grade double,
    gradeT double,
    gradeA double,
    gradeS1 double,
    gradeS2 double,
    gradeS3 double,
    isAppeal integer,
    primary key (submitId),
    foreign key (submitId) references submit(submitId)
);

CREATE TABLE assessment(
    submitId integer,
    primary key (submitId),
    sid1 varchar(20),
    s1comment varchar(200),
    sid2 varchar(20),
    s2comment varchar(200),
    sid3 varchar(20),
    s3comment varchar(200),
    tid varchar(20),
    tcomment varchar(200),
    aid varchar(20),
    acomment varchar(200),
    foreign key (submitId) references submit(submitId),
    foreign key(sid1) references student(sid),
    foreign key(sid2) references student(sid),
    foreign key(sid2) references student(sid),
    foreign key(tid) references teacher(tid),
    foreign key(aid) references assistant(aid)
);

CREATE TABLE statistics(
    hid integer primary key,
    average double,
    up90 integer,
    up80 integer,
    up70 integer,
    up60 integer,
    down60 integer,
    foreign key(hid) references homework(hid)
);

CREATE TABLE post(
    pid integer primary key AUTO_INCREMENT,
    pcontent varchar(300),
    ptime datetime DEFAULT CURRENT_TIMESTAMP,
    sid varchar(20),
    tid varchar(20),
    aid varchar(20),
    cid varchar(20),
    foreign key(sid) references student(sid),
    foreign key(tid) references teacher(tid),
    foreign key(aid) references assistant(aid),
    foreign key(cid) references course(cid)
);

CREATE TABLE comment(
    commentId integer primary key AUTO_INCREMENT,
    commentContent varchar(300),
    commentTime datetime DEFAULT CURRENT_TIMESTAMP,
    sid varchar(20),
    tid varchar(20),
    aid varchar(20),
    pid integer,
    foreign key(sid) references student(sid),
    foreign key(tid) references teacher(tid),
    foreign key(aid) references assistant(aid),
    foreign key(pid) references post(pid)
);

alter table assistant add constraint foreign key(tid) references teacher(tid);

alter table course add constraint foreign key(tid) references teacher(tid);

alter table selectCourse add constraint foreign key(sid) references student(sid);

alter table selectCourse add constraint foreign key(cid) references course(cid);

alter table homework add constraint foreign key(tid) references teacher(tid);

alter table homework add constraint foreign key(cid) references course(cid);

alter table submit add constraint foreign key(sid) references student(sid);

alter table submit add constraint foreign key(hid) references homework(hid);

alter table grade add constraint foreign key (submitId) references submit(submitId);

alter table assessment add constraint foreign key (submitId) references submit(submitId);

alter table assessment add constraint foreign key(sid1) references student(sid);

alter table assessment add constraint foreign key(sid2) references student(sid);

alter table assessment add constraint foreign key(sid3) references student(sid);

alter table assessment add constraint foreign key(tid) references teacher(tid);

alter table assessment add constraint foreign key(aid) references assistant(aid);

alter table post add constraint foreign key(sid) references student(sid);

alter table post add constraint foreign key(cid) references course(cid);

alter table post add constraint foreign key(tid) references teacher(tid);

alter table post add constraint foreign key(aid) references assistant(aid);

alter table comment add constraint foreign key(sid) references student(sid);

alter table comment add constraint foreign key(pid) references post(pid);

alter table comment add constraint foreign key(tid) references teacher(tid);

alter table comment add constraint foreign key(aid) references assistant(aid);