CREATE TABLE contributor
(
        contributor_id                  number(11) not null     CONSTRAINT pk_contributor_id PRIMARY KEY,
        contributor_name                varchar2(128)   not null,
        contributor_email               varchar2(128)   not null,
        contributor_organization        varchar(255) not null,
        contributor_address             varchar2(255),
        contributor_phone               number,
        contributor_fax                 number  
);

CREATE TABLE source (
    source_id                      NUMBER                     NOT NULL,
    source_name                    VARCHAR2(1000)             NOT NULL,
    ISSN                           VARCHAR2(40),
    PRIMARY KEY ( source_id )
);


CREATE TABLE old_source (
    source_id                      NUMBER                     NOT NULL,
    old_name                       VARCHAR2(1000)             NOT NULL,
    PRIMARY KEY ( source_id ),
    FOREIGN KEY ( source_id ) REFERENCES source
);



CREATE TABLE source_synonym (
    source_id                       NUMBER                   NOT NULL,
    source_synonym                  VARCHAR2(500)            NOT NULL,
    UNIQUE ( source_id, source_synonym ),
    FOREIGN KEY ( source_id ) REFERENCES source
);


CREATE TABLE source_url (
    source_id                       NUMBER                      NOT NULL,
    source_url                      VARCHAR2(500)               NOT NULL,
    url_label                       VARCHAR2(40),
    UNIQUE ( source_id, source_url ),
    FOREIGN KEY ( source_id ) REFERENCES source
);                     



CREATE TABLE reference (
    reference_id                   NUMBER                    NOT NULL,
    source_id                      NUMBER,
    title                          VARCHAR2(1000)            NOT NULL,
    volume                         VARCHAR2(100),
    year                           NUMBER,
    start_page                     VARCHAR2(20),
    end_page                       VARCHAR2(20),
    language                       VARCHAR2(50),
    corresponding_author           NUMBER,
    PRIMARY KEY ( reference_id ),
    FOREIGN KEY ( source_id ) REFERENCES source,
    FOREIGN KEY ( corresponding_author ) REFERENCES contributor
);



CREATE TABLE reference_abstract (
    reference_id                   NUMBER                    NOT NULL,
    abstract_part_a                VARCHAR2(4000),
    abstract_part_b                VARCHAR2(4000),
    PRIMARY KEY ( reference_id ),
    FOREIGN KEY ( reference_id ) REFERENCES reference
);



CREATE TABLE keyword (
    keyword_id                    NUMBER                    NOT NULL,
    keyword                       VARCHAR2(100)             NOT NULL,
    PRIMARY KEY ( keyword_id )
);



CREATE TABLE reference_to_keyword (
    reference_id                   NUMBER                    NOT NULL,
    keyword_id                     NUMBER                    NOT NULL,
    PRIMARY KEY ( reference_id ),
    FOREIGN KEY ( reference_id ) REFERENCES reference,
    FOREIGN KEY ( keyword_id ) REFERENCES keyword
);



CREATE TABLE author (
    contributor_id                 NUMBER                    NOT NULL,
    reference_id                   NUMBER                    NOT NULL,
    authorship_position            NUMBER,
    PRIMARY KEY ( contributor_id, reference_id ),
    FOREIGN KEY ( contributor_id ) REFERENCES contributor,
    FOREIGN KEY ( reference_id ) REFERENCES reference
);




CREATE TABLE reference_url (
    reference_id                  NUMBER                      NOT NULL,
    reference_url                 VARCHAR2(500)               NOT NULL,
    url_label                     VARCHAR2(40),
    PRIMARY KEY ( reference_id, reference_url ),
    FOREIGN KEY ( reference_id ) REFERENCES reference
);



CREATE TABLE reference_comment (
    reference_id                  NUMBER                      NOT NULL,
    reference_comment             VARCHAR2(4000)              NOT NULL,
    contributor_id                NUMBER,
    PRIMARY KEY ( reference_id ),
    FOREIGN KEY ( reference_id ) REFERENCES reference,
    FOREIGN KEY ( contributor_id ) REFERENCES contributor
);


CREATE TABLE reference_to_object (
    reference_to_object_id           NUMBER                  NOT NULL,
    reference_id                     NUMBER                  NOT NULL,
    object_table                     VARCHAR2(120)           NOT NULL,
    object_id                        NUMBER                  NOT NULL,
    PRIMARY KEY ( reference_to_object_id ),
    FOREIGN KEY ( reference_id ) REFERENCES reference
); 


CREATE TABLE reference_extra (
    reference_id                   NUMBER                    NOT NULL,
    title                          VARCHAR2(1000)            NOT NULL,
    source                         VARCHAR2(1000),
    author 			   VARCHAR2(1000),
    year                           NUMBER,
    abstract_part_a                VARCHAR2(4000),
    abstract_part_b                VARCHAR2(4000),
    PRIMARY KEY ( reference_id ),
    FOREIGN KEY ( reference_id ) REFERENCES reference
);
---For reference search only. This is actually a redundant table, but it improves the performance. 


CREATE TABLE reference_load_help (
    reference_id                   NUMBER                    NOT NULL,
    original_id                    VARCHAR2(100)             NOT NULL,
    reference_source               VARCHAR2(100)             NOT NULL,
    PRIMARY KEY ( reference_id ),
    FOREIGN KEY ( reference_id ) REFERENCES reference
);

