/*

drop table prereq;

drop table time_slot;

drop table advisor;

drop table takes;

drop table student;

drop table teaches;

drop table section;

drop table instructor;

drop table course;

drop table department;

drop table classroom;

*/

create table classroom

(building varchar(15),

room_number varchar(7),

capacity numeric(4,0),

primary key (building, room_number)

);


create table department

(dept_name varchar(20), 

building varchar(15), 

budget         numeric(12,2) check (budget > 0),

primary key (dept_name)

);


create table course

(course_id varchar(8), 

title varchar(50), 

dept_name varchar(20),

credits numeric(2,0) check (credits > 0),

primary key (course_id),

foreign key (dept_name) references department

on delete set null

);


create table instructor

(ID varchar(5), 

name varchar(20) not null, 

dept_name varchar(20), 

salary numeric(8,2) check (salary > 29000),

primary key (ID),

foreign key (dept_name) references department

on delete set null

);


create table section

(course_id varchar(8), 

         sec_id varchar(8),

semester varchar(6)

check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 

year numeric(4,0) check (year > 1701 and year < 2100), 

building varchar(15),

room_number varchar(7),

time_slot_id varchar(4),

primary key (course_id, sec_id, semester, year),

foreign key (course_id) references course

on delete cascade,

foreign key (building, room_number) references classroom

on delete set null

);


create table teaches

(ID varchar(5), 

course_id varchar(8),

sec_id varchar(8), 

semester varchar(6),

year numeric(4,0),

primary key (ID, course_id, sec_id, semester, year),

foreign key (course_id,sec_id, semester, year) references section

on delete cascade,

foreign key (ID) references instructor

on delete cascade

);


create table student

(ID varchar(5), 

name varchar(20) not null, 

dept_name varchar(20), 

tot_cred numeric(3,0) check (tot_cred >= 0),

primary key (ID),

foreign key (dept_name) references department

on delete set null

);


create table takes

(ID varchar(5), 

course_id varchar(8),

sec_id varchar(8), 

semester varchar(6),

year numeric(4,0),

grade         varchar(2),

primary key (ID, course_id, sec_id, semester, year),

foreign key (course_id,sec_id, semester, year) references section

on delete cascade,

foreign key (ID) references student

on delete cascade

);


create table advisor

(s_ID varchar(5),

i_ID varchar(5),

primary key (s_ID),

foreign key (i_ID) references instructor (ID)

on delete set null,

foreign key (s_ID) references student (ID)

on delete cascade

);


create table time_slot

(time_slot_id varchar(4),

day varchar(1),

start_hr numeric(2) check (start_hr >= 0 and start_hr < 24),

start_min numeric(2) check (start_min >= 0 and start_min < 60),

end_hr numeric(2) check (end_hr >= 0 and end_hr < 24),

end_min numeric(2) check (end_min >= 0 and end_min < 60),

primary key (time_slot_id, day, start_hr, start_min)

);


create table prereq

(course_id varchar(8), 

prereq_id varchar(8),

primary key (course_id, prereq_id),

foreign key (course_id) references course

on delete cascade,

foreign key (prereq_id) references course

);


학교 데이터베이스 실습중인데

SSMS 사용법이나 이런 거 안알려줘서...

DB 는 실습까지 같이해야 하는데 교수가 이론만 가르침 ㅅㅂ