--
-- Created by SQL::Translator::Producer::Oracle
-- Created on Mon Dec  9 16:59:58 2002
--

-- We assume that default NLS_DATE_FORMAT has been changed
-- but we set it here anyway to be self-consistent.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

--
-- Table: marker_accession
--

DROP TABLE marker_accession;
CREATE TABLE marker_accession (
  accession_id number(11) DEFAULT '0' CONSTRAINT nn_accession_id01 NOT NULL,
  genbank_accession varchar2(20) DEFAULT '' CONSTRAINT nn_genbank_accession NOT NULL,
  CONSTRAINT pk_marker_accession PRIMARY KEY (accession_id)
);

--
-- Table: marker_accession_to_object
--

DROP TABLE marker_accession_to_object;
CREATE TABLE marker_accession_to_object (
  accession_to_object_id number(11) DEFAULT '0' CONSTRAINT nn_accession_to_object_id NOT NULL,
  accession_id number(11) DEFAULT '0' CONSTRAINT nn_accession_id02 NOT NULL,
  table_name varchar2(50) DEFAULT '' CONSTRAINT nn_table_name NOT NULL,
  marker_id number(11) DEFAULT '0' CONSTRAINT nn_marker_id NOT NULL,
  CONSTRAINT pk_marker_accession_to_object PRIMARY KEY (accession_to_object_id)
);

CREATE INDEX accession_id_marker_accession_ on marker_accession_to_object (accession_id);

--
-- Table: marker_aflp
--

DROP TABLE marker_aflp;
CREATE TABLE marker_aflp (
  marker_id number(11) DEFAULT '0' CONSTRAINT nn_marker_id02 NOT NULL,
  name varchar2(50) DEFAULT NULL,
  molecular_weight number(11) DEFAULT NULL,
  germplasm varchar2(50) DEFAULT NULL,
  adapter1_name varchar2(50) DEFAULT NULL,
  adapter1_restriction varchar2(50) DEFAULT NULL,
  adapter1_sequence varchar2(50) DEFAULT NULL,
  adapter1_complement varchar2(50) DEFAULT NULL,
  adapter2_name varchar2(50) DEFAULT NULL,
  adapter2_restriction varchar2(50) DEFAULT NULL,
  adapter2_sequence varchar2(50) DEFAULT NULL,
  adapter2_complement varchar2(50) DEFAULT NULL,
  primer1_name varchar2(50) DEFAULT NULL,
  primer1_common_seq varchar2(50) DEFAULT NULL,
  primer1_overhang varchar2(50) DEFAULT NULL,
  primer2_name varchar2(50) DEFAULT NULL,
  primer2_common_seq varchar2(50) DEFAULT NULL,
  primer2_overhang varchar2(50) DEFAULT NULL,
  species_id number(11) DEFAULT '0' CONSTRAINT nn_species_id01 NOT NULL,
  CONSTRAINT pk_marker_aflp PRIMARY KEY (marker_id),
  CONSTRAINT name_marker_aflp UNIQUE (name)
);

--
-- Table: marker_alias
--

DROP TABLE marker_alias;
CREATE TABLE marker_alias (
  alias_id number(11) DEFAULT '0' CONSTRAINT nn_alias_id NOT NULL,
  alias varchar2(50) DEFAULT NULL,
  CONSTRAINT pk_marker_alias PRIMARY KEY (alias_id),
  CONSTRAINT alias_marker_alias UNIQUE (alias)
);

--
-- Table: marker_alias_to_object
--

DROP TABLE marker_alias_to_object;
CREATE TABLE marker_alias_to_object (
  alias_to_object_id number(11) DEFAULT '0' CONSTRAINT nn_alias_to_object_id NOT NULL,
  alias_id number(11) DEFAULT '0' CONSTRAINT nn_alias_id02 NOT NULL,
  table_name varchar2(50) DEFAULT '' CONSTRAINT nn_table_name02 NOT NULL,
  marker_id number(11) DEFAULT '0' CONSTRAINT nn_marker_id03 NOT NULL,
  CONSTRAINT pk_marker_alias_to_object PRIMARY KEY (alias_to_object_id)
);

CREATE INDEX alias_id_marker_alias_to_objec on marker_alias_to_object (alias_id);

--
-- Table: marker_bac_accession
--

DROP TABLE marker_bac_accession;
CREATE TABLE marker_bac_accession (
  bac_accession_id number(11) DEFAULT '0' CONSTRAINT nn_bac_accession_id NOT NULL,
  bac_accession varchar2(20) DEFAULT '' CONSTRAINT nn_bac_accession NOT NULL,
  CONSTRAINT pk_marker_bac_accession PRIMARY KEY (bac_accession_id)
);

--
-- Table: marker_bac_accession_to_objec
--

DROP TABLE marker_bac_accession_to_object;
CREATE TABLE marker_bac_accession_to_object (
  bac_accession_to_object_id number(11) DEFAULT '0' CONSTRAINT nn_bac_accession_to_object_id NOT NULL,
  bac_accession_id number(11) DEFAULT '0' CONSTRAINT nn_bac_accession_id02 NOT NULL,
  table_name varchar2(50) DEFAULT '' CONSTRAINT nn_table_name03 NOT NULL,
  marker_id number(11) DEFAULT '0' CONSTRAINT nn_marker_id04 NOT NULL,
  CONSTRAINT pk_marker_bac_accession_to_obj PRIMARY KEY (bac_accession_to_object_id)
);

CREATE INDEX accession_id_marker_bac_access on marker_bac_accession_to_object (bac_accession_id);

--
-- Table: marker_image
--

DROP TABLE marker_image;
CREATE TABLE marker_image (
  image_id number(11) DEFAULT '0' CONSTRAINT nn_image_id NOT NULL,
  file_name varchar2(50) DEFAULT NULL,
  caption long,
  width number(11) DEFAULT NULL,
  height number(11) DEFAULT NULL,
  CONSTRAINT pk_marker_image PRIMARY KEY (image_id)
);

--
-- Table: marker_image_to_object
--

DROP TABLE marker_image_to_object;
CREATE TABLE marker_image_to_object (
  image_to_object_id number(11) DEFAULT '0' CONSTRAINT nn_image_to_object_id NOT NULL,
  image_id number(11) DEFAULT '0' CONSTRAINT nn_image_id02 NOT NULL,
  table_name varchar2(50) DEFAULT '' CONSTRAINT nn_table_name04 NOT NULL,
  marker_id number(11) DEFAULT '0' CONSTRAINT nn_marker_id05 NOT NULL,
  CONSTRAINT pk_marker_image_to_object PRIMARY KEY (image_to_object_id)
);

CREATE INDEX image_id_marker_image_to_objec on marker_image_to_object (image_id);

--
-- Table: marker_rapd
--

DROP TABLE marker_rapd;
CREATE TABLE marker_rapd (
  marker_id number(11) DEFAULT '0' CONSTRAINT nn_marker_id06 NOT NULL,
  name varchar2(50) DEFAULT NULL,
  primer1 varchar2(50) DEFAULT NULL,
  primer2 varchar2(50) DEFAULT NULL,
  band_size number(8,2) DEFAULT NULL,
  species_id number(11) DEFAULT '0' CONSTRAINT nn_species_id02 NOT NULL,
  CONSTRAINT pk_marker_rapd PRIMARY KEY (marker_id),
  CONSTRAINT name_marker_rapd UNIQUE (name)
);

--
-- Table: marker_reference
--

DROP TABLE marker_reference;
CREATE TABLE marker_reference (
  reference_id number(11) DEFAULT '0' CONSTRAINT nn_reference_id NOT NULL,
  reference varchar2(20) DEFAULT '' CONSTRAINT nn_reference NOT NULL,
  CONSTRAINT pk_marker_reference PRIMARY KEY (reference_id)
);

--
-- Table: marker_reference_to_object
--

DROP TABLE marker_reference_to_object;
CREATE TABLE marker_reference_to_object (
  reference_to_object_id number(11) DEFAULT '0' CONSTRAINT nn_reference_to_object_id NOT NULL,
  reference_id number(11) DEFAULT '0' CONSTRAINT nn_reference_id02 NOT NULL,
  table_name varchar2(50) DEFAULT '' CONSTRAINT nn_table_name05 NOT NULL,
  marker_id number(11) DEFAULT '0' CONSTRAINT nn_marker_id07 NOT NULL,
  CONSTRAINT pk_marker_reference_to_object PRIMARY KEY (reference_to_object_id)
);

CREATE INDEX reference_id_marker_reference_ on marker_reference_to_object (reference_id);

--
-- Table: marker_rflp
--

DROP TABLE marker_rflp;
CREATE TABLE marker_rflp (
  marker_id number(11) DEFAULT '0' CONSTRAINT nn_marker_id08 NOT NULL,
  name varchar2(50) DEFAULT NULL,
  species_id number(11) DEFAULT '0' CONSTRAINT nn_species_id03 NOT NULL,
  dna_treatment varchar2(50) DEFAULT NULL,
  source_type varchar2(50) DEFAULT NULL,
  source_germplasm varchar2(50) DEFAULT NULL,
  source_tissue varchar2(50) DEFAULT NULL,
  source_treatment varchar2(50) DEFAULT NULL,
  vector varchar2(50) DEFAULT NULL,
  vector_antibiotic_resistance varchar2(50) DEFAULT NULL,
  fragment_size number DEFAULT NULL,
  vector_excision_enzyme varchar2(50) DEFAULT NULL,
  vector_primers varchar2(50) DEFAULT NULL,
  CONSTRAINT pk_marker_rflp PRIMARY KEY (marker_id),
  CONSTRAINT name_marker_rflp UNIQUE (name)
);

--
-- Table: marker_ssr
--

DROP TABLE marker_ssr;
CREATE TABLE marker_ssr (
  marker_id number(11) DEFAULT '0' CONSTRAINT nn_marker_id09 NOT NULL,
  name varchar2(50) DEFAULT NULL,
  clone varchar2(50) DEFAULT NULL,
  repeat_motif varchar2(50) DEFAULT NULL,
  forward_primer varchar2(50) DEFAULT NULL,
  reverse_primer varchar2(50) DEFAULT NULL,
  anneal_temperature number(11) DEFAULT NULL,
  product_size number(11) DEFAULT NULL,
  source_germplasm varchar2(50) DEFAULT NULL,
  remarks long,
  species_id number(11) DEFAULT '0' CONSTRAINT nn_species_id04 NOT NULL,
  CONSTRAINT pk_marker_ssr PRIMARY KEY (marker_id),
  CONSTRAINT name_marker_ssr UNIQUE (name)
);

--
-- Table: marker_sts
--

DROP TABLE marker_sts;
CREATE TABLE marker_sts (
  marker_id number(11) DEFAULT '0' CONSTRAINT nn_marker_id10 NOT NULL,
  name varchar2(50) DEFAULT NULL,
  primer1 varchar2(50) DEFAULT NULL,
  primer2 varchar2(50) DEFAULT NULL,
  reference varchar2(50) DEFAULT NULL,
  species_id number(11) DEFAULT '0' CONSTRAINT nn_species_id05 NOT NULL,
  CONSTRAINT pk_marker_sts PRIMARY KEY (marker_id),
  CONSTRAINT name_marker_sts UNIQUE (name)
);

--
-- Table: marker_tos17
--

DROP TABLE marker_tos17;
CREATE TABLE marker_tos17 (
  marker_id number(11) DEFAULT '0' CONSTRAINT nn_marker_id11 NOT NULL,
  name varchar2(50) DEFAULT NULL,
  species_id number(11) DEFAULT '0' CONSTRAINT nn_species_id06 NOT NULL,
  dna_treatment varchar2(50) DEFAULT NULL,
  genome_position varchar2(50) DEFAULT NULL,
  nearest_upstream_gene varchar2(50) DEFAULT NULL,
  in_gene varchar2(50) DEFAULT NULL,
  nearest_downstream_gene varchar2(50) DEFAULT NULL,
  source_germplasm varchar2(200) DEFAULT NULL,
  CONSTRAINT pk_marker_sts01 PRIMARY KEY (marker_id),
  CONSTRAINT name_marker_sts01 UNIQUE (name)
);

create index marker_aflp_uc_name on marker_aflp (upper(name));

create index marker_rapd_uc_name on marker_rapd (upper(name));

create index marker_rflp_uc_name on marker_rflp (upper(name));

create index marker_ssr_uc_name on marker_ssr (upper(name));

create index marker_sts_uc_name on marker_sts (upper(name));

create index marker_tos17_uc_name on marker_tos17 (upper(name));
