Notes

create database ICT_5E17;
use ict_5e17;
create table student
(sid numeric(5) primary key, ename varchar(40) not null,
sex char(1) not null, dob date not null,hkid char(8) not null,
school_year char(1), school_name varchar(70), tel_home char(8),
tel_mobile char(8),address varchar(70), nick varchar(15));
create table room
(room_id numeric(2) primary key, max_cap numeric(2) not null);
create table online_stu
(sid numeric(5) primary key, passphrase varchar(20) not null,
email varchar(30), praise numeric(4) default 0,
foreign key(sid) references student(sid));
create table teacher
(tid numeric(3) primary key, hkid char(8) not null,
ename varchar(40) not null, sex char(1) not null,
dob date not null, tel_home char(8),tel_mobile char(8),
address varchar(70));
create table t_subject
(tid numeric(3), subjects varchar(25),
primary key(tid,subjects),
foreign key(tid) references teacher(tid));
create table class
(cid numeric(3) primary key, class_name varchar(30) not null,
length numeric(3) not null);
create table stu_cls
(sid numeric(5),cid numeric(3),
foreign key(sid) references student(sid),
foreign key(cid) references class(cid) on delete cascade);
create table lesson
(lesson_id numeric(3) primary key, cid numeric(3),
day char(3) not null, start_time time not null,
room_id numeric(1) not null,
foreign key(cid) references class(cid)
on delete cascade,
foreign key(room_id) references room(room_id)
on update cascade);
create table t_class
(t_class numeric(4) primary key,
tid numeric(3),lesson_id numeric(3),
foreign key(tid) references teacher(tid)
on delete set null,
foreign key(lesson_id) references lesson(lesson_id)
on delete cascade
);
create table message
(mid numeric(7) primary key, sender numeric(5) not null,
recipient numeric(5) not null, date_time datetime not null,
content text not null,
foreign key(sender) references student(sid)
on delete cascade,
foreign key(recipient) references student(sid)
on delete cascade);
create table thread
(thread_id numeric(2) primary key, name varchar(20));
create table post
(pid numeric(5) primary key, parent_id numeric(5) not null,
user_id numeric(5) not null, thread_id numeric(2) not null,
date_time datetime not null, content text not null,
foreign key(pid) references post(pid)
on delete cascade,
foreign key(user_id) references student(sid)
on delete cascade,
foreign key(thread_id) references thread(thread_id)
on delete cascade);