CREATE TABLE sm(
	sm_id    NUMBER(3) PRIMARY KEY
);

CREATE TABLE sm_loc(
	sm_id	NUMBER(3),
	sm_slot  NUMBER,
	sm_loctime TIMESTAMP,
	FOREIGN KEY (sm_id) REFERENCES sm
);

CREATE TABLE roc(
	roc_type    CHAR(4),
	roc_serial  NUMBER,
	PRIMARY KEY(roc_type,roc_serial)
	--CONSTRAINT roc_pos_unique UNIQUE(sm_id,sm_stack,sm_layer),
);

CREATE TABLE roc_loc(
	roc_type    CHAR(4),
	roc_serial  NUMBER,
	sm_id       NUMBER,
	sm_stack    NUMBER,
	sm_layer    NUMBER,
	dcs_id      NUMBER,
	roc_loctime TIMESTAMP,
	FOREIGN KEY (sm_id) REFERENCES sm,
	FOREIGN KEY(roc_type,roc_serial) REFERENCES roc,
	--CONSTRAINT roc_dcs_unique UNIQUE(dcs_id)
);



CREATE TABLE rob(
	rob_id      NUMBER,
	type        CHAR(2),
	PRIMARY KEY (rob_id)
);

CREATE TABLE rob_loc(
	rob_id      NUMBER,
	roc_type    CHAR(4),
	roc_serial  NUMBER,
	roc_pos     NUMBER,
	rob_loctime TIMESTAMP,
	FOREIGN KEY (rob_id) REFERENCES rob,
	FOREIGN KEY (roc_type,roc_serial) REFERENCES roc
	--CONSTRAINT rob_pos_unique UNIQUE (roc_type,roc_serial,roc_pos)
);


CREATE TABLE mcm (
	mcm_id		NUMBER,
	laserid		NUMBER(6), --IS LASERNUM
	temp_offset	NUMBER,
	temp_slope	NUMBER,
	PRIMARY KEY(mcm_id)
);

CREATE TABLE mcm_loc (
	mcm_id		NUMBER REFERENCES mcm,
	rob_id		NUMBER REFERENCES rob,
	rob_pos		NUMBER(2),
	mcm_loctime TIMESTAMP,
	FOREIGN KEY (mcm_id) REFERENCES mcm,
	FOREIGN KEY (rob_id) REFERENCES rob
);
