DROP TABLE IF EXISTS itu_courses;

CREATE TABLE itu_courses (course_id varchar(10) NOT NULL, course_name varchar(100) NOT NULL, INDEX(course_id)) TYPE=innoDB;

INSERT INTO itu_courses VALUES ('DMPI', 'Multimedia Production for the Internet');
INSERT INTO itu_courses VALUES ('DSDS', 'Introduktion til scripting, databaser og systemarkitektur');
INSERT INTO itu_courses VALUES ('KMP', 'Konceptudvikling af multimedieproduktioner');
INSERT INTO itu_courses VALUES ('U', 'Usability');
INSERT INTO itu_courses VALUES ('FRI', 'Mit yndlingsfag');





DROP TABLE IF EXISTS itu_persons;
CREATE TABLE itu_persons (person_id int NOT NULL, email varchar(100), name varchar(100) NOT NULL, kind varchar(20), INDEX(person_id) )TYPE=innoDB;

INSERT INTO itu_persons VALUES (101, 'hanne@itu.dk', 'Hanne', 'teacher');
INSERT INTO itu_persons VALUES (201, 'randi@itu.dk', 'Randi', 'teaching assistant');
INSERT INTO itu_persons VALUES (301, 'bent@itu.dk','Bent', 'teacher');





DROP TABLE IF EXISTS itu_courseresponsibility;
CREATE TABLE itu_courseresponsibility (
	course_id varchar(10) NOT NULL,
	person_id int NOT NULL,
	semester varchar(5) NOT NULL,
	INDEX(course_id, person_id),
	FOREIGN KEY (course_id) REFERENCES itu_courses (course_id),
	FOREIGN KEY (person_id) REFERENCES itu_persons (person_id)
)TYPE=innoDB;
	

INSERT INTO itu_courseresponsibility VALUES ('DMPI', 101, 'F2007');
INSERT INTO itu_courseresponsibility VALUES ('DSDS', 201, 'F2007');
INSERT INTO itu_courseresponsibility VALUES ('U', 301,'F2006');