Database

CREATE TABLE IF NOT EXISTS FARM
(
   FARM_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
   FARM_NAME VARCHAR(45) NOT NULL,
   ADDRESS VARCHAR(45) NOT NULL,
   OWNER VARCHAR(45) NOT NULL
)ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS PADDOCK
(
   PADDOCK_NUM INT NOT NULL,
   FARM_FARM_ID INT NOT NULL,
   PRIMARY KEY(FARM_FARM_ID, PADDOCK_NUM),   
   AREA VARCHAR(45) NOT NULL,
   CONSTRAINT FARM_FARM_ID_CON FOREIGN KEY(FARM_FARM_ID)
   REFERENCES FARM(FARM_ID)
)ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS VISIT
(
  VISIT_ID INT NOT NULL PRIMARY KEY, 
  GATHERING DATE,
  PADDOCK_PADDOCK_NUM INT NOT NULL, 
  PADDOCK_FARM_ID INT NOT NULL, 
  CONSTRAINT PADDOCK_PADDOCK_NUM_CON FOREIGN KEY(PADDOCK_PADDOCK_NUM)
  REFERENCES PADDOCK(PADDOCK_NUM),
  CONSTRAINT PADDOCK_PADDOCK_FARM_ID_CON FOREIGN KEY(PADDOCK_FARM_ID)
  REFERENCES PADDOCK(FARM_FARM_ID)
)ENGINE = INNODB;  

CREATE TABLE IF NOT EXISTS VISIT
(
  VISIT_ID INT NOT NULL PRIMARY KEY, 
  GATHERING DATE,
  PADDOCK_PADDOCK_NUM INT NOT NULL, 
  PADDOCK_FARM_ID INT NOT NULL, 
  CONSTRAINT PADDOCK_PADDOCK_NUM_CON FOREIGN KEY(PADDOCK_PADDOCK_NUM)
  REFERENCES PADDOCK(PADDOCK_NUM),
  CONSTRAINT PADDOCK_PADDOCK_FARM_ID_CON FOREIGN KEY(PADDOCK_FARM_ID)
  REFERENCES PADDOCK(FARM_FARM_ID),
  UNIQUE KEY(PADDOCK_PADDOCK_NUM, PADDOCK_FARM_ID, GATHERING)
)ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS SAMPLE
(
   SAMPLE_NUM INT UNSIGNED NOT NULL AUTO_INCREMENT,
   VISIT_VISIT_ID INT NOT NULL,
   PRIMARY KEY(SAMPLE_NUM, VISIT_VISIT_ID),
   COUNT INT,
   CONSTRAINT VISIT_VISIT_ID_CON FOREIGN KEY(VISIT_VISIT_ID)
   REFERENCES VISIT(VISIT_ID)
)ENGINE = INNODB;