CREATE TABLE exam(
	academicyear	INTEGER NOT NULL,
	session	CHAR(8) NOT NULL,
	component	VARCHAR(10) NOT NULL,
	startdate	DATE NOT NULL,
	starttime	TIME NOT NULL,
	duration	TIME NOT NULL,
	endtime	TIME NOT NULL,
	mcode	CHAR(11) NOT NULL,

	-- Specify the PRIMARY KEY constraint for table "exam".
	-- This indicates which attribute(s) uniquely identify each row of data.
	CONSTRAINT	pk_exam PRIMARY KEY (academicyear,session,component,mcode)
) TYPE=INNODB;

CREATE TABLE module(
	mcode	CHAR(11) NOT NULL,
	title	VARCHAR(50) NOT NULL,

	-- Specify the PRIMARY KEY constraint for table "module".
	-- This indicates which attribute(s) uniquely identify each row of data.
	CONSTRAINT	pk_module PRIMARY KEY (mcode)
) TYPE=INNODB;

CREATE TABLE room(
	roomno	CHAR(8) NOT NULL,

	-- Specify the PRIMARY KEY constraint for table "room".
	-- This indicates which attribute(s) uniquely identify each row of data.
	CONSTRAINT	pk_room PRIMARY KEY (roomno)
) TYPE=INNODB;

CREATE TABLE exam_room(
	academicyear	INTEGER NOT NULL,
	session	CHAR(8) NOT NULL,
	component	VARCHAR(10) NOT NULL,
	mcode	CHAR(11) NOT NULL,
	roomno	CHAR(8) NOT NULL,
	PRIMARY KEY (academicyear,session,component,mcode,roomno),
	INDEX(academicyear,session,component,mcode),
	FOREIGN KEY(academicyear,session,component,mcode) REFERENCES exam(academicyear,session,component,mcode) ON DELETE RESTRICT ON UPDATE RESTRICT,
	INDEX(roomno),
	FOREIGN KEY(roomno) REFERENCES room(roomno) ON DELETE RESTRICT ON UPDATE RESTRICT
) TYPE=INNODB;

ALTER TABLE exam ADD INDEX (mcode), ADD CONSTRAINT fk1_exam_to_module FOREIGN KEY(mcode) REFERENCES module(mcode) ON DELETE RESTRICT ON UPDATE RESTRICT;