CREATE DATABASE EUDC;
use EUDC;
CREATE TABLE student (
sno char(8) NOT NULL primary key,
sname char(8) NOT NULL,
sex char(2),
native char(20),
birthday date,
dno char(6),
spno char(4),
classno char(4),
entime DATE
);
CREATE TABLE course (
cno char(6) NOT NULL primary key,
spon char(4) ,
cname char(20)NOT NULL,
ctno tinyint(1),
experiment tinyint(1),
lecture tinyint(1),
semester tinyint(1)
);
CREATE TABLE student_course (
sno char(8) NOT NULL,
tcid smallint(2) NOT NULL,
score tinyint(1),
PRIMARY KEY (sno, tcid)
);
CREATE TABLE teacher (
tno char(5) NOT NULL primary key,
tname char(8) NOT NULL,
sex char(2),
birthday DATE,
dno char(6),
pno tinyint(1),
home varchar(40),
zipcode char(6),
tel varchar(40),
email varchar(40)
);
CREATE TABLE teacher_course (
tcid smallint(2) NOT NULL primary key,
tno char(8),
spno char(4),
classno char(4),
cno char(6) NOT NULL,
semester char(6),
schoolyear char(10),
classtime varchar(40),
classroom varchar(40),
weektime tinyint(1),
FOREIGN KEY (tno) REFERENCES teacher(tno),
FOREIGN KEY (cno) REFERENCES course(cno)
);
SHOW TABLES;
DESCRIBE student;
ALTER TABLE student MODIFY COLUMN birthday DATE NOT NULL;
DROP TABLE teacher_course;
DROP TABLE teacher;
ALTER TABLE student DROP PRIMARY KEY;
ALTER TABLE student MODIFY COLUMN sno VARCHAR(20);
ALTER TABLE student ADD PRIMARY KEY (sno);
ALTER TABLE course ADD COLUMN year VARCHAR(4);
ALTER TABLE course ADD CONSTRAINT year CHECK (year IN ('第一', '第二', '第三', '第四'));
ALTER TABLE course DROP CHECK year;
ALTER TABLE course DROP COLUMN year;