set echo on

spool K:\lectures\lecture9\create.log

--drop tables

drop table students CASCADE CONSTRAINTS;
drop table teachers CASCADE CONSTRAINTS;
drop table courses  CASCADE CONSTRAINTS;
drop table stu_cou  CASCADE CONSTRAINTS;
drop table tea_cou  CASCADE CONSTRAINTS;

--create tables

create table students
(studentid number(9,0) CONSTRAINT pk_students PRIMARY KEY,
 fname varchar2(20), 
 lname varchar2(20),
 department varchar2(30),
 year number(1,0),
 email varchar2(30),
 phone varchar2(20),
 height number(3,0) 
);

create table teachers
(teacherid number(9,0) CONSTRAINT pk_teachers PRIMARY KEY,
 fname varchar2(20),
 lname varchar2(20),
 email varchar2(30),
 phone varchar2(20),
 office varchar2(20),
 hours varchar2(20)
);

create table courses
(courseid number(6,0) CONSTRAINT pk_courses PRIMARY KEY,
 coursenumber varchar2(20),
 name varchar2(60),
 description varchar2(200),
 www varchar2(30),
 courselevel varchar2(10),
 credit number(2,0)
);

create table tea_cou
(
 teacherid number(9,0),
 courseid number(6,0) 
);


create table stu_cou
(
 studentid number(9,0),
 courseid number(6,0) 
);


--specify primary key (multi-column as primary key)
ALTER TABLE stu_cou 
	ADD CONSTRAINT pk_stu_cou PRIMARY KEY (studentid,courseid);
ALTER TABLE tea_cou 
	ADD CONSTRAINT pk_tea_cou PRIMARY KEY (teacherid,courseid);

--specify foreign key
ALTER TABLE tea_cou
	ADD CONSTRAINT fk_teacherid
	FOREIGN KEY (teacherid)
	REFERENCES teachers (teacherid);

ALTER TABLE tea_cou
	ADD CONSTRAINT fk_tea_courseid
	FOREIGN KEY (courseid)
	REFERENCES courses (courseid);

ALTER TABLE stu_cou
	ADD CONSTRAINT fk_studentid
	FOREIGN KEY (studentid)
	REFERENCES students (studentid);

ALTER TABLE stu_cou
	ADD CONSTRAINT fk_stu_courseid
	FOREIGN KEY (courseid)
	REFERENCES courses (courseid);

commit;

spool off;