DROP TABLE IF EXISTS `CONTAINS`;
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`)
);

INSERT INTO `CONTAINS` VALUES ('F1','G1',15,250);
INSERT INTO `CONTAINS` VALUES ('F1','G2',4300,3400);
INSERT INTO `CONTAINS` VALUES ('F2','G1',115,350);
INSERT INTO `CONTAINS` VALUES ('F2','G2',4400,3300);
INSERT INTO `CONTAINS` VALUES ('F3','G3',5,500);

DROP TABLE IF EXISTS `FRAG`;
CREATE TABLE `FRAG` (
  `FRAG` char(16),
  `NAME` varchar(20),
  `SEQUENCE` longtext,
  `CIRCULAR` boolean,
  PRIMARY KEY (`FRAG`)
);

INSERT INTO `FRAG` VALUES ('F1','r1.p1','ATCAAACCGGGAAAAATTTAGCT',1);
INSERT INTO `FRAG` VALUES ('F2','r1.p2','ATCAAACCGGGAAAAATTTAGCT',1);
INSERT INTO `FRAG` VALUES ('F3','r2.f1','ATCAAACCGGGAAAAATTTAGCT',0);

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

INSERT INTO `GENE` VALUES ('G1','AMP','MAKK...',-5);
INSERT INTO `GENE` VALUES ('G2','TET','MYAK...',-10);
INSERT INTO `GENE` VALUES ('G3','NGF','MYAK...',-1);

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

INSERT INTO `PRODUCES` VALUES ('R1','F1','1985-09-09');
INSERT INTO `PRODUCES` VALUES ('R1','F2','1985-09-09');
INSERT INTO `PRODUCES` VALUES ('R2','F3','1985-10-05');

DROP TABLE IF EXISTS `RECIPE`;
CREATE TABLE `RECIPE` (
  `RCP` char(16),
  `NAME` varchar(20),
  `FILE` varchar(20),
  `OWNER` varchar(8),
  PRIMARY KEY (`RCP`)
);

INSERT INTO `RECIPE` VALUES ('R1','r1','r1,cr','scooter');
INSERT INTO `RECIPE` VALUES ('R2','r2','r2.cr','ckw');
