DROP TABLE Schedule;
DROP TABLE Activity;
DROP TABLE Room;

CREATE TABLE Room (
       roomName VARCHAR(20) NOT NULL,
       roomLocation VARCHAR(20),
       CONSTRAINT pk_room PRIMARY KEY (roomName) 
);

CREATE TABLE Activity (
       actModCode CHAR(6) NOT NULL,
       actDescription VARCHAR(10) NOT NULL,
       CONSTRAINT pk_activity PRIMARY KEY (actModCode, actDescription)
);

	
CREATE TABLE Schedule(
       schSlot VARCHAR(10) NOT NULL,
       roomName VARCHAR(20),
       actModCode CHAR(6),
       actDescription VARCHAR(10),
       CONSTRAINT pk_sch PRIMARY KEY (schSlot),
       CONSTRAINT fk_schRoom FOREIGN KEY (roomName) REFERENCES Room (roomName),
       CONSTRAINT fk_schAct FOREIGN KEY (actModCode, actDescription) REFERENCES Activity
);

-- Inserting tuples

-- into Room:

INSERT INTO Room VALUES ('JC-EXCHGE-C.LT2', 'Exchange');
INSERT INTO Room VALUES ('JC-EXCHGE-D.LT3', 'Exchange');
INSERT INTO Room VALUES ('JC-EXCHGE-COMP-A32', 'Computer Science');

-- into Activity:

INSERT INTO Activity VALUES ('G51DBS', 'Lecture1');
INSERT INTO Activity VALUES ('G51DBS', 'Lecture2');
INSERT INTO Activity VALUES ('G51PRG', 'Lecture1');
INSERT INTO Activity VALUES ('G51PRG', 'Lecture2');
INSERT INTO Activity VALUES ('G51DBS', 'Lab');
INSERT INTO Activity VALUES ('G51PRG', 'Lab');

-- into Schedule:

INSERT INTO Schedule VALUES ('Mon 9-10', 'JC-EXCHGE-C.LT2', 'G51DBS', 'Lecture1');
INSERT INTO Schedule VALUES ('Mon 10-11', 'JC-EXCHGE-C.LT2', 'G51PRG', 'Lecture1');
INSERT INTO Schedule VALUES ('Tue 13-14', 'JC-EXCHGE-C.LT2', 'G51PRG', 'Lecture2');
INSERT INTO Schedule VALUES ('Tue 14-16', 'JC-EXCHGE-COMP-A32', 'G51PRG', 'Lab');
INSERT INTO Schedule VALUES ('Wed 9-11', 'JC-EXCHGE-COMP-A32', 'G51DBS', 'Lab');
INSERT INTO Schedule VALUES ('Wed 12-13', 'JC-EXCHGE-D.LT3', 'G51DBS', 'Lecture2');

COLUMN actModCode FORMAT A10;
COLUMN actDescription FORMAT A14;
