DROP TABLE VIS_MAPPING_DATA;
DROP TABLE VIS_POPULATION;
DROP TABLE VIS_CULTIVAR;


CREATE TABLE VIS_CULTIVAR
(
  VIS_CULTIVAR_ID NUMBER (38,0) NOT NULL,
  NAME VARCHAR2 (40) NOT NULL,
  NOTE VARCHAR2 (4000)
)
;

ALTER TABLE VIS_CULTIVAR ADD
(
  PRIMARY KEY
  (
    VIS_CULTIVAR_ID
  )
)
;

CREATE TABLE VIS_POPULATION
(
  VIS_POPULATION_ID NUMBER (38,0) NOT NULL,
  VIS_CULTIVAR_ID_PAR1 NUMBER (38,0) NOT NULL,
  VIS_CULTIVAR_ID_PAR2 NUMBER (38,0) NOT NULL,
  VIS_CULTIVAR_ID_OFFSPRING NUMBER (38,0) NOT NULL
)
;

ALTER TABLE VIS_POPULATION ADD
(
  PRIMARY KEY
  (
    VIS_POPULATION_ID
  )
)
;

ALTER TABLE VIS_POPULATION ADD
(
  FOREIGN KEY
  (
    VIS_CULTIVAR_ID_PAR1
  )
  REFERENCES VIS_CULTIVAR
  ( 
    VIS_CULTIVAR_ID
  )
)
;

ALTER TABLE VIS_POPULATION ADD
(
  FOREIGN KEY
  (
    VIS_CULTIVAR_ID_OFFSPRING
  )
  REFERENCES VIS_CULTIVAR
  ( 
    VIS_CULTIVAR_ID
  )
)
;

ALTER TABLE VIS_POPULATION ADD
(
  FOREIGN KEY
  (
    VIS_CULTIVAR_ID_PAR2
  )
  REFERENCES VIS_CULTIVAR
  ( 
    VIS_CULTIVAR_ID
  )
)
;

CREATE TABLE VIS_MAPPING_DATA
(
  VIS_MAPPING_DATA_ID NUMBER (38,0) NOT NULL,
  POPULATION_ID NUMBER (38,0) NOT NULL,
  MARKER_ID NUMBER,
  MARKER_TYPE NUMBER (2,0),
  MARKER_CODE VARCHAR2 (30),
  MAP_POSITION NUMBER,
  NOTE VARCHAR2 (4000),
  EST_ID VARCHAR2 (30),
  CHROMOSOMEN_LOCATION VARCHAR2 (5),
  MAPPING_CODE NUMBER 
)
;

ALTER TABLE VIS_MAPPING_DATA ADD
(
  PRIMARY KEY
  (
    VIS_MAPPING_DATA_ID
  )
)
;

ALTER TABLE VIS_MAPPING_DATA ADD
(
  CONSTRAINT VIS_MAPPING_DATA_VIS_MAPPIN_FK
  FOREIGN KEY
  (
    POPULATION_ID
  )
  REFERENCES VIS_POPULATION
  ( 
    VIS_POPULATION_ID
  )
)
;

