DROP TABLE affy_annotation;
DROP TABLE gcrma_expression;
DROP TABLE affy_probe;
DROP TABLE gcrma_samples;

CREATE TABLE gcrma_samples (
       id SERIAL NOT NULL, -- PRIMARY KEY,
       tissue TEXT NOT NULL
);

CREATE TABLE affy_probe (
      id SERIAL NOT NULL, -- PRIMARY KEY,
      probe TEXT NOT NULL
);

CREATE TABLE affy_annotation (
       id SERIAL NOT NULL, -- PRIMARY KEY,
       probe INT NOT NULL,-- REFERENCES affy_probe,
       gene_symbol TEXT,
       gene_name TEXT,
       species TEXT,
       array_name TEXT,
       entrez_id TEXT,
       refseq_prot TEXT,
       refseq_transcript TEXT
);

CREATE TABLE gcrma_expression (
       id SERIAL NOT NULL, -- PRIMARY KEY,
       probe INT NOT NULL,-- REFERENCES affy_probe, 
       sample INT NOT NULL,-- REFERENCES gcrma_samples,
       expression FLOAT NOT NULL
);

CREATE VIEW gcrma AS 
       SELECT ge.id AS id,
       gs.tissue AS tissue,
       ap.probe AS probe,
       ge.expression AS expression,
       aa.gene_symbol AS symbol
       FROM gcrma_expression ge 
       JOIN affy_probe ap ON ge.probe=ap.id
       JOIN gcrma_samples gs ON ge.sample=gs.id
       LEFT OUTER JOIN affy_annotation aa ON ap.id=aa.probe;
       
       
