create table if not exists Student
(StuID char(8),
StuName varchar(10),
StuSex char(2),
StuBirthday date,
Native varchar(20) comment ‘籍贯’,
National char(6) comment ‘民族’,
Telephone char(11),
ClassID char(10)
);

create table Course
(CourseID char(8),
CourseName varchar(25),
CourseNature char(10) comment ‘课程性质’,
Credit tinyint(1) comment ‘学分’,
CourseRemark text comment ‘备注’
)
engine=innodb
default character set utf8
default collate utf8_general_ci;

CREATE TABLE class(
ClassID char(10) NOT NULL,
ClassName varchar(20) DEFAULT NULL,
Num int(11) DEFAULT NULL,
TeacherID char(10) DEFAULT NULL,
MajorID char(8) DEFAULT NULL,
PRIMARY KEY (ClassID)
);

create table Teacher
(TeacherID char(10),
TeacherName varchar(8),
TeacherSex char(2) default ‘女’,
TeacherBirthday date,
Telephone char(11),
DepartmentID char(8)
);

create table Department
(DepartmentID char(8),
primary key(DepartmentID),
DepartmentName varchar(20)
);

create table Score
(StuID char(8),
CourseID char(8),
ExamScore int,
PartScore int,
primary key(StuID,CourseID)
);

create table Major
(MajorID char(8) primary key,
MajorName varchar(20) unique,
DepartmentID char(8)
);

create table Classroom
(ClassroomID varchar(10) primary key,
ClassroomType varchar(20) not null,
PersonNumber int,
Place varchar(20)
);

create table RollCall
(StuID char(8),
CourseID char(8),
AttendNumber int check(AttendNumber>=0),
AbsenceNumber int check(AbsenceNumber>=0),
LateNumber int check(LateNumber>=0),
primary key(StuID,CourseID)
);

alter table class add primary key(ClassID);
alter table course add primary key(CourseID);
alter table classroom add primary key(ClassroomID);

create table TeachingCourse
(ClassID char(10),
CourseID char(8),
ClassroomID varchar(10),
TeacherID char(10),
WeekDay char(3) not null,
Part tinyint not null,
foreign key(ClassID) references class(ClassID),
foreign key(CourseID) references course(CourseID),
foreign key(ClassroomID) references classroom(ClassroomID),
foreign key(TeacherID) references teacher(TeacherID)
);