
CREATE TABLE `GENE`( `ID` char(16), `NAME` varchar(20), `PROTEIN` longtext, `START#` int, PRIMARY KEY (`ID`));

CREATE TABLE `PRODUCES` ( `RCP` char(16), `FRAG` char(16), `DATE` date, FOREIGN KEY (`RCP`) REFERENCES `RECIPE`(`RCP`), FOREIGN KEY (`FRAG`) REFERENCES `FRAG`(`ID`));

CREATE TABLE `CONTAINS` ( `FRAG` char(16), `GENE` char(16), `START` int, `END` int, FOREIGN KEY (`FRAG`) REFERENCES `FRAG` (`ID`), FOREIGN KEY (`GENE`) REFERENCES `GENE` (`ID`));

CREATE UNIQUE INDEX id_index on GENE (ID);


INSERT INTO `GENE` (`ID`, `NAME`, `PROTEIN`, `START#`) VALUES ('G1', 'AMP', 'MAKK...', -5);

CREATE TABLE TEMP1 AS SELECT CONTAINS.FRAG,CONTAINS.GENE,GENE.NAME FROM CONTAINS, GENE WHERE GENE.ID=CONTAINS.GENE AND GENE.NAME="AMP";

CREATE TABLE TEMP2 AS SELECT PRODUCES.RCP FROM TEMP1,PRODUCES WHERE TEMP1.FRAG=PRODUCES.FRAG;

SELECT DISTINCT RECIPE.RCP, RECIPE.NAME, RECIPE.FILE, RECIPE.OWNER FROM TEMP2, RECIPE WHERE TEMP2.RCP = RECIPE.RCP;

SELECT DISTINCT RECIPE.RCP, RECIPE.NAME, RECIPE.FILE, RECIPE.OWNER FROM GENE, CONTAINS, PRODUCES, RECIPE WHERE GENE.NAME = 'AMP' AND GENE.ID = CONTAINS.GENE AND CONTAINS.FRAG = PRODUCES.FRAG AND PRODUCES.RCP = RECIPE.RCP;



