-- SQL script for generation of coreCERA
--     here: generation of general lists of NEW values (LOVs)
--           to be inserted into the standard LOV after a check
--           Only LOVs for CERA Core

create table NEW_ACCESS_CONSTRAINT (
       access_constraint_id    number(15)    PRIMARY KEY,
       constraint_descr        varchar(2000) NOT NULL
       );

create table NEW_ACCESS_TYPE   (
       access_type_id          number(15)    PRIMARY KEY,
       access_acronym          varchar(31)   UNIQUE NOT NULL,
       access_descr            varchar(2000)        NOT NULL
       );

create table NEW_AGGREGATION   (
       aggregation_id          number(15)    PRIMARY KEY,
       aggregation_descr       varchar(80)   UNIQUE NOT NULL
       );

create table NEW_CITATION_TYPE (
       citation_type_id        number(15)    PRIMARY KEY,
       citation_type           varchar(80)   UNIQUE NOT NULL,
       citation_type_descr     varchar(2000) NOT NULL
       );

create table NEW_CONNECT_TYPE  (
       connect_type_id         number(15)    PRIMARY KEY,
       connect_type            varchar(31)   UNIQUE NOT NULL,
--     connect_descr           varchar(2000) NOT NULL  korrig., ft, 30.1.01
       connect_type_descr      varchar(2000) NOT NULL
       );

create table NEW_CONTACT_TYPE  (
       contact_type_id         number(15)    PRIMARY KEY,
       contact_type            varchar(31)   UNIQUE NOT NULL
       );

create table NEW_CURRENTNESS_REF (
       currentness_ref_id      number(15)    PRIMARY KEY,
       currentness_ref_descr   varchar(2000)        NOT NULL
       );

create table NEW_ENTRY_TYPE        (
       entry_type_id           number(15)    PRIMARY KEY,
       entry_type              varchar(31)   UNIQUE NOT NULL,
       entry_type_descr        varchar(2000)        NOT NULL
       );

create table NEW_FEES          (
       fees_id                 number(15)    PRIMARY KEY,
       fees_acronym            varchar(31)   UNIQUE NOT NULL,
       fees_descr              varchar(2000)
       );

create table NEW_FORMAT        ( 
       format_id               number(15)    PRIMARY KEY,
       format_acronym          varchar(31)   UNIQUE NOT NULL,
       format_descr            varchar(2000)
       );

create table NEW_GENERAL_KEY   (
       general_key_id          number(15)    PRIMARY KEY,
       general_key             varchar(250)  UNIQUE NOT NULL
       );

create table NEW_HOR_COORD_SYS (
       hor_coord_sys_id        number(15)    PRIMARY KEY,
       sys_descr               varchar(2000)        NOT NULL
       );

create table NEW_LOCATION (
       location_id             number(15)    PRIMARY KEY,
       location_name           varchar(80)   UNIQUE NOT NULL,
       location_descr          varchar(2000)                ,
       location_pointer        number(15)           NOT NULL,
       location_level          number(2)            NOT NULL,
       ref_date                varchar(15)          NOT NULL,
       min_lat                 number(15,6)         NOT NULL,
       max_lat                 number(15,6)         NOT NULL,
       min_lon                 number(15,6)         NOT NULL,
       max_lon                 number(15,6)         NOT NULL
       );

create table NEW_PRESENTATION  (
       presentation_id         number(15)    PRIMARY KEY,
       presentation_descr      varchar(2000)        NOT NULL
       );

create table NEW_PROGRESS      (
       progress_id             number(15)    PRIMARY KEY,
       progress_descr          varchar(31)   UNIQUE NOT NULL
       );

create table NEW_PROJECT       (
       project_id              number(15)    PRIMARY KEY,
       project_acronym         varchar(31)   UNIQUE NOT NULL,
       project_name            varchar(80)   UNIQUE NOT NULL,
       project_descr           varchar(2000)
       );

create table NEW_REF_TYPE      (
       ref_type_id             number(15)    PRIMARY KEY,
       ref_type_descr          varchar(2000)        NOT NULL
       );

create table NEW_SPATIAL_DATA_ORG (
       spatial_data_org_id     number(15)    PRIMARY KEY,
       reference_method        varchar(80)   UNIQUE NOT NULL
       );

create table NEW_TOPIC         (
       topic_id                number(15)    PRIMARY KEY,
       topic_name              varchar(80)   UNIQUE NOT NULL,
       topic_acronym           varchar(31)   UNIQUE,
       topic_descr             varchar(2000) ,
       topic_pointer           number(15)    ,
       topic_level             number(2)            NOT NULL
       );

create table NEW_UNIT          (
       unit_id                 number(15)    PRIMARY KEY,
       unit_name               varchar(80)   UNIQUE NOT NULL,
       unit_acronym            varchar(31)   UNIQUE NOT NULL,
       unit_descr              varchar(2000)
       );

create table NEW_USE_CONSTRAINT (
       use_constraint_id       number(15)    PRIMARY KEY,
       constraint_descr        varchar(2000)        NOT NULL
       );

create table NEW_VER_COORD_SYS (
       ver_coord_sys_id        number(15)    PRIMARY KEY,
       sys_descr               varchar(2000)        NOT NULL
       );

--     now:  Entities for CERA Core, used like LOVs
create table NEW_CITATION      (
       citation_id             number(15)    PRIMARY KEY,
       title                   varchar(2000) NOT NULL,
       authors                 varchar(2000) ,
       publication             varchar(80)   ,
       publisher               varchar(80)   ,
       editor                  varchar(80)   ,
       publication_date        date          ,
       country                 varchar(80)   ,
       state                   varchar(80)   ,
       place                   varchar(80)   ,
       edition                 varchar(80)   ,
       access_spec             varchar(160)  ,
       additional_info         varchar(250)  ,
       presentation_id         number(15)    ,
       citation_type_id        number(15)
-- following 2 lines changed, when NEW_CITATION  was set identical
--     to  CITATION.          ft, 2000-07-05
--     presentation_descr      varchar(2000) ,
--     citation_type           varchar(80)   NOT NULL
       );

create table NEW_INSTITUTE     (
       INSTITUTE_ID            number(15)    PRIMARY KEY,
       INSTITUTE_NAME          varchar(80)   NOT NULL,
       INSTITUTE_ACRONYM       varchar(31)   ,
       DEPARTMENT_NAME         varchar(160)  ,
       DEPARTMENT_ACRONYM      varchar(31)   ,
       COUNTRY                 varchar(80)   ,
       STATE_OR_PROVINCE       varchar(80)   ,
       PLACE                   varchar(80)   ,
       STREET                  varchar(80)   ,
       STREET_POSTAL_CODE      varchar(10)   ,
       POBOX                   varchar(80)   ,
       POBOX_POSTAL_CODE       varchar(10)   ,
       URL                     varchar(80)   ,
       ADDITIONAL_INFO         varchar(250)
       );

create table NEW_PERSON        (
       person_id               number(15)    PRIMARY KEY,
       first_name              varchar(80)   ,
       second_name             varchar(80)   ,
       last_name               varchar(80)   NOT NULL,
       title                   varchar(31)   ,
       institute_id            number(15)    ,
       telephone               varchar(80)   ,
       fax                     varchar(80)   ,
       url                     varchar(80)   ,
       email                   varchar(80)
       );

