open18-initial-schema.sql

DROP TABLE IF EXISTS TEE;
DROP TABLE IF EXISTS TEE_SET;
DROP TABLE IF EXISTS HOLE;
DROP TABLE IF EXISTS COURSE;
DROP TABLE IF EXISTS FACILITY;

DROP SEQUENCE IF EXISTS SYSTEM_SEQUENCE_FACILITY;
DROP SEQUENCE IF EXISTS SYSTEM_SEQUENCE_COURSE;
DROP SEQUENCE IF EXISTS SYSTEM_SEQUENCE_HOLE;
DROP SEQUENCE IF EXISTS SYSTEM_SEQUENCE_TEE_SET;


CREATE SEQUENCE SYSTEM_SEQUENCE_FACILITY START WITH 1;
CREATE SEQUENCE SYSTEM_SEQUENCE_COURSE START WITH 1 ;
CREATE SEQUENCE SYSTEM_SEQUENCE_HOLE START WITH 1 ;
CREATE SEQUENCE SYSTEM_SEQUENCE_TEE_SET START WITH 1 ;

CREATE  TABLE FACILITY(
    ID BIGINT  DEFAULT nextval('SYSTEM_SEQUENCE_FACILITY') NOT NULL primary key,
--    VERSION INTEGER DEFAULT 0 NOT NULL,
    NAME VARCHAR(50) NOT NULL,
    DESCRIPTION varchar(500) DEFAULT '',
    TYPE VARCHAR(15) NOT NULL CHECK (TYPE IN ('PUBLIC', 'PRIVATE', 'SEMI_PRIVATE', 'RESORT', 'MILITARY')),
    ADDRESS VARCHAR(50),
    CITY VARCHAR(30),
    STATE VARCHAR(2),
    ZIP VARCHAR(5),
    COUNTY VARCHAR(30),
    COUNTRY VARCHAR(30),
    PHONE VARCHAR(10),
    URI VARCHAR(255),
    PRICE_RANGE INTEGER CHECK ((PRICE_RANGE >= 1) AND (PRICE_RANGE <= 5))
--    ,WEEKDAY_RATE INTEGER,
--    ,WEEKEND_RATE INTEGER
--    ,GOLF_PRO VARCHAR(50),
--    ,DRIVING_RANGE BIT DEFAULT FALSE NOT NULL
--    ,PRO_SHOP BIT DEFAULT FALSE NOT NULL,
--    ,RENTS_CLUBS BIT DEFAULT FALSE NOT NULL,
--    ,SNACK_BAR BIT DEFAULT FALSE NOT NULL,
--    ,RESTAURANT BIT DEFAULT FALSE NOT NULL
);

CREATE  TABLE COURSE(
    ID BIGINT DEFAULT nextval('SYSTEM_SEQUENCE_COURSE') NOT NULL primary key,
--    VERSION INTEGER DEFAULT 0 NOT NULL,
    NAME VARCHAR(50),
    DESCRIPTION varchar(500) DEFAULT '',
--    STYLE VARCHAR(15),
    DESIGNER VARCHAR(50),
    FAIRWAYS VARCHAR(15) NOT NULL CHECK (FAIRWAYS IN ('UNKNOWN', 'BENT', 'BERMUDA', 'ZOYSIA', 'BLUEGRASS', 'RYE', 'FESCUE')),
    GREENS VARCHAR(15) NOT NULL CHECK (GREENS IN ('UNKNOWN', 'BENT', 'BERMUDA')),
    YEAR_BUILT INTEGER CHECK ((YEAR_BUILT >= 1000) AND (YEAR_BUILT <= 9999)),
    NUM_HOLES INTEGER DEFAULT 18 NOT NULL CHECK ((NUM_HOLES = 9) OR (NUM_HOLES = 18)),
    SIGNATURE_HOLE BIGINT, -- QUESTION: should this be a foreign key?
    FACILITY_ID BIGINT NOT NULL
);

CREATE  TABLE HOLE(
    ID BIGINT DEFAULT nextval('SYSTEM_SEQUENCE_HOLE') NOT NULL primary key,
--    VERSION INTEGER DEFAULT 0 NOT NULL,
    NAME VARCHAR(25),
    NUMBER INTEGER NOT NULL CHECK ((NUMBER >= 1) AND (NUMBER <= 18)),
    M_PAR INTEGER NOT NULL CHECK ((M_PAR >= 2) AND (M_PAR <= 6)),
    M_HANDICAP INTEGER CHECK ((M_HANDICAP >= 1) AND (M_HANDICAP <= 18)),
    L_PAR INTEGER NOT NULL CHECK ((L_PAR >= 2) AND (L_PAR <= 6)),
    L_HANDICAP INTEGER CHECK ((L_HANDICAP >= 1) AND (L_HANDICAP <= 18)),
    COURSE_ID BIGINT NOT NULL
);

CREATE  TABLE TEE_SET(
    ID BIGINT DEFAULT nextval('SYSTEM_SEQUENCE_TEE_SET') NOT NULL primary key,
--    VERSION INTEGER DEFAULT 0 NOT NULL,
    NAME VARCHAR(25),
    COLOR VARCHAR(10) NOT NULL,
    L_COURSE_RATING FLOAT CHECK (L_COURSE_RATING > 0),
    L_SLOPE_RATING FLOAT CHECK ((L_SLOPE_RATING >= 55) AND (L_SLOPE_RATING <= 155)),
    M_COURSE_RATING FLOAT CHECK (M_COURSE_RATING > 0),
    M_SLOPE_RATING FLOAT CHECK ((M_SLOPE_RATING >= 55) AND (M_SLOPE_RATING <= 155)),
    POS INTEGER CHECK (POS >= 1),
    COURSE_ID BIGINT NOT NULL
);

CREATE  TABLE TEE(
    TEE_SET_ID BIGINT NOT NULL,
    HOLE_ID BIGINT NOT NULL,
--    VERSION INTEGER DEFAULT 0 NOT NULL,
    DISTANCE INTEGER NOT NULL CHECK ((DISTANCE >= 1) AND (DISTANCE <= 999)),
    PRIMARY KEY ( TEE_SET_ID ,HOLE_ID )
);

ALTER TABLE COURSE ADD CONSTRAINT FK_COURSE_REF_FACILITY FOREIGN KEY(FACILITY_ID) REFERENCES FACILITY(ID);
ALTER TABLE HOLE ADD CONSTRAINT FK_HOLE_REF_COURSE FOREIGN KEY(COURSE_ID) REFERENCES COURSE(ID);
ALTER TABLE HOLE ADD CONSTRAINT UNIQ_HOLE_NUMBER UNIQUE(NUMBER, COURSE_ID);
ALTER TABLE TEE_SET ADD CONSTRAINT UNIQ_TEE_SET_COLOR UNIQUE(COLOR, COURSE_ID);
ALTER TABLE TEE_SET ADD CONSTRAINT FK_TEE_SET_REF_COURSE FOREIGN KEY(COURSE_ID) REFERENCES COURSE(ID);
ALTER TABLE TEE_SET ADD CONSTRAINT UNIQ_TEE_SET_POS UNIQUE(POS, COURSE_ID);
ALTER TABLE TEE ADD CONSTRAINT FK_TEE_REF_TEE_SET FOREIGN KEY(TEE_SET_ID) REFERENCES TEE_SET(ID);
ALTER TABLE TEE ADD CONSTRAINT FK_TEE_REF_HOLE FOREIGN KEY(HOLE_ID) REFERENCES HOLE(ID);