use GEOCACHING;
drop table LOGENTRY;
drop table CCREATE;
drop table SEEK;
drop table DEVICE;
drop table GEOCACHER;
drop table GEOLOG;
drop table GEOCACHE;
drop table LOCATION;

CREATE TABLE LOCATION (
    x_coordinate      FLOAT(7)  NOT NULL,
    CONSTRAINT x_coordinate_CK CHECK  (x_coordinate >= -100.00 AND x_coordinate <= 100.00),
    y_coordinate      FLOAT(7)  NOT NULL,
    CONSTRAINT y_coordinate_CK CHECK  (y_coordinate >= -100.00 AND y_coordinate <= 100.00),
    location_name   VARCHAR(15) NOT NULL,
    terrain         VARCHAR(10),
    difficulty_level VARCHAR(8),
CONSTRAINT LOCATION_PK PRIMARY KEY(x_coordinate, y_coordinate)
 );

CREATE TABLE GEOCACHE (
    cache#          VARCHAR(6) NOT NULL,
    cache_type      VARCHAR(15)NOT NULL,
    cache_size      VARCHAR(8) NOT NULL,
    clue            VARCHAR(50),
    x_coordinate      FLOAT(7)  NOT NULL,
    y_coordinate      FLOAT(7)  NOT NULL,
CONSTRAINT GEOCACHE_PK PRIMARY KEY(cache#),
CONSTRAINT GEOCACHE_FK FOREIGN KEY(x_coordinate, y_coordinate)REFERENCES LOCATION(x_coordinate, y_coordinate),
 );

CREATE TABLE GEOLOG (
    geolog_id       VARCHAR(6) NOT NULL,
    cache#          VARCHAR(6) NOT NULL,
    geolog_type     VARCHAR(20) NOT NULL,
    geolog_date     DATETIME NOT NULL,
CONSTRAINT GEOLOG_PK PRIMARY KEY(geolog_id),
CONSTRAINT GEOLOG_FK FOREIGN KEY(cache#)REFERENCES GEOCACHE(cache#),
 );

CREATE TABLE GEOCACHER (
    cacher_id       VARCHAR(8) NOT NULL,
    first_name      VARCHAR(15) NOT NULL,
    last_name       VARCHAR(15) NOT NULL,
    email           VARCHAR(30) NOT NULL UNIQUE,
    telephone       NUMERIC(15),
    membership_type VARCHAR(8) NOT NULL,
CONSTRAINT GEOCACHER_PK PRIMARY KEY(cacher_id),
);

CREATE TABLE DEVICE (
    serial#     VARCHAR(10) NOT NULL,
    cacher_id   VARCHAR(8) NOT NULL,
    brand       VARCHAR(8) NOT NULL,
    drange      VARCHAR(8),
    price       NUMERIC(5),
CONSTRAINT DEVICE_PK PRIMARY KEY(serial#),
CONSTRAINT DEVICE_FK FOREIGN KEY(cacher_id)REFERENCES GEOCACHER(cacher_id),
 );

CREATE TABLE SEEK (
    cacher_id   VARCHAR(8) NOT NULL,
    cache#      VARCHAR(6) NOT NULL,
CONSTRAINT SEEK_PK PRIMARY KEY(cacher_id, cache#),
CONSTRAINT SEEK_FK FOREIGN KEY(cacher_id)REFERENCES GEOCACHER(cacher_id),
CONSTRAINT SEEK_FK2 FOREIGN KEY(cache#)REFERENCES GEOCACHE(cache#),
 );

CREATE TABLE CCREATE (
    cacher_id   VARCHAR(8) NOT NULL,
    cache#      VARCHAR(6) NOT NULL,
CONSTRAINT CCREATE_PK PRIMARY KEY(cacher_id, cache#),
CONSTRAINT CCREATE_FK FOREIGN KEY(cacher_id)REFERENCES GEOCACHER(cacher_id),
CONSTRAINT CCREATE_FK2 FOREIGN KEY(cache#)REFERENCES GEOCACHE(cache#),
 );

CREATE TABLE LOGENTRY (
    cacher_id   VARCHAR(8) NOT NULL,
    geolog_id       VARCHAR(6) NOT NULL,
CONSTRAINT LOGENTRY_PK PRIMARY KEY(cacher_id, geolog_id),
CONSTRAINT LOGENTRY_FK FOREIGN KEY(cacher_id)REFERENCES GEOCACHER(cacher_id),
CONSTRAINT LOGENTRY_FK2 FOREIGN KEY(geolog_id)REFERENCES GEOLOG(geolog_id),
 );