1vRkqC27

1DROP TABLE IF EXISTS TEE;
2DROP TABLE IF EXISTS TEE_SET;
3DROP TABLE IF EXISTS HOLE;
4DROP TABLE IF EXISTS COURSE;
5DROP TABLE IF EXISTS FACILITY;
6
7DROP SEQUENCE IF EXISTS SYSTEM_SEQUENCE_FACILITY;
8DROP SEQUENCE IF EXISTS SYSTEM_SEQUENCE_COURSE;
9DROP SEQUENCE IF EXISTS SYSTEM_SEQUENCE_HOLE;
10DROP SEQUENCE IF EXISTS SYSTEM_SEQUENCE_TEE_SET;
11
12
13CREATE SEQUENCE SYSTEM_SEQUENCE_FACILITY START WITH 1;
14CREATE SEQUENCE SYSTEM_SEQUENCE_COURSE START WITH 1 ;
15CREATE SEQUENCE SYSTEM_SEQUENCE_HOLE START WITH 1 ;
16CREATE SEQUENCE SYSTEM_SEQUENCE_TEE_SET START WITH 1 ;
17
18CREATE  TABLE FACILITY(
19    ID BIGINT  DEFAULT nextval('SYSTEM_SEQUENCE_FACILITY') NOT NULL primary key,
20--    VERSION INTEGER DEFAULT 0 NOT NULL,
21    NAME VARCHAR(50) NOT NULL,
22    DESCRIPTION varchar(500) DEFAULT '',
23    TYPE VARCHAR(15) NOT NULL CHECK (TYPE IN ('PUBLIC', 'PRIVATE', 'SEMI_PRIVATE', 'RESORT', 'MILITARY')),
24    ADDRESS VARCHAR(50),
25    CITY VARCHAR(30),
26    STATE VARCHAR(2),
27    ZIP VARCHAR(5),
28    COUNTY VARCHAR(30),
29    COUNTRY VARCHAR(30),
30    PHONE VARCHAR(10),
31    URI VARCHAR(255),
32    PRICE_RANGE INTEGER CHECK ((PRICE_RANGE >= 1) AND (PRICE_RANGE <= 5))
33--    ,WEEKDAY_RATE INTEGER,
34--    ,WEEKEND_RATE INTEGER
35--    ,GOLF_PRO VARCHAR(50),
36--    ,DRIVING_RANGE BIT DEFAULT FALSE NOT NULL
37--    ,PRO_SHOP BIT DEFAULT FALSE NOT NULL,
38--    ,RENTS_CLUBS BIT DEFAULT FALSE NOT NULL,
39--    ,SNACK_BAR BIT DEFAULT FALSE NOT NULL,
40--    ,RESTAURANT BIT DEFAULT FALSE NOT NULL
41);
42
43CREATE  TABLE COURSE(
44    ID BIGINT DEFAULT nextval('SYSTEM_SEQUENCE_COURSE') NOT NULL primary key,
45--    VERSION INTEGER DEFAULT 0 NOT NULL,
46    NAME VARCHAR(50),
47    DESCRIPTION varchar(500) DEFAULT '',
48--    STYLE VARCHAR(15),
49    DESIGNER VARCHAR(50),
50    FAIRWAYS VARCHAR(15) NOT NULL CHECK (FAIRWAYS IN ('UNKNOWN', 'BENT', 'BERMUDA', 'ZOYSIA', 'BLUEGRASS', 'RYE', 'FESCUE')),
51    GREENS VARCHAR(15) NOT NULL CHECK (GREENS IN ('UNKNOWN', 'BENT', 'BERMUDA')),
52    YEAR_BUILT INTEGER CHECK ((YEAR_BUILT >= 1000) AND (YEAR_BUILT <= 9999)),
53    NUM_HOLES INTEGER DEFAULT 18 NOT NULL CHECK ((NUM_HOLES = 9) OR (NUM_HOLES = 18)),
54    SIGNATURE_HOLE BIGINT, -- QUESTION: should this be a foreign key?
55    FACILITY_ID BIGINT NOT NULL
56);
57
58CREATE  TABLE HOLE(
59    ID BIGINT DEFAULT nextval('SYSTEM_SEQUENCE_HOLE') NOT NULL primary key,
60--    VERSION INTEGER DEFAULT 0 NOT NULL,
61    NAME VARCHAR(25),
62    NUMBER INTEGER NOT NULL CHECK ((NUMBER >= 1) AND (NUMBER <= 18)),
63    M_PAR INTEGER NOT NULL CHECK ((M_PAR >= 2) AND (M_PAR <= 6)),
64    M_HANDICAP INTEGER CHECK ((M_HANDICAP >= 1) AND (M_HANDICAP <= 18)),
65    L_PAR INTEGER NOT NULL CHECK ((L_PAR >= 2) AND (L_PAR <= 6)),
66    L_HANDICAP INTEGER CHECK ((L_HANDICAP >= 1) AND (L_HANDICAP <= 18)),
67    COURSE_ID BIGINT NOT NULL
68);
69
70CREATE  TABLE TEE_SET(
71    ID BIGINT DEFAULT nextval('SYSTEM_SEQUENCE_TEE_SET') NOT NULL primary key,
72--    VERSION INTEGER DEFAULT 0 NOT NULL,
73    NAME VARCHAR(25),
74    COLOR VARCHAR(10) NOT NULL,
75    L_COURSE_RATING FLOAT CHECK (L_COURSE_RATING > 0),
76    L_SLOPE_RATING FLOAT CHECK ((L_SLOPE_RATING >= 55) AND (L_SLOPE_RATING <= 155)),
77    M_COURSE_RATING FLOAT CHECK (M_COURSE_RATING > 0),
78    M_SLOPE_RATING FLOAT CHECK ((M_SLOPE_RATING >= 55) AND (M_SLOPE_RATING <= 155)),
79    POS INTEGER CHECK (POS >= 1),
80    COURSE_ID BIGINT NOT NULL
81);
82
83CREATE  TABLE TEE(
84    TEE_SET_ID BIGINT NOT NULL,
85    HOLE_ID BIGINT NOT NULL,
86--    VERSION INTEGER DEFAULT 0 NOT NULL,
87    DISTANCE INTEGER NOT NULL CHECK ((DISTANCE >= 1) AND (DISTANCE <= 999)),
88    PRIMARY KEY ( TEE_SET_ID ,HOLE_ID )
89);
90
91ALTER TABLE COURSE ADD CONSTRAINT FK_COURSE_REF_FACILITY FOREIGN KEY(FACILITY_ID) REFERENCES FACILITY(ID);
92ALTER TABLE HOLE ADD CONSTRAINT FK_HOLE_REF_COURSE FOREIGN KEY(COURSE_ID) REFERENCES COURSE(ID);
93ALTER TABLE HOLE ADD CONSTRAINT UNIQ_HOLE_NUMBER UNIQUE(NUMBER, COURSE_ID);
94ALTER TABLE TEE_SET ADD CONSTRAINT UNIQ_TEE_SET_COLOR UNIQUE(COLOR, COURSE_ID);
95ALTER TABLE TEE_SET ADD CONSTRAINT FK_TEE_SET_REF_COURSE FOREIGN KEY(COURSE_ID) REFERENCES COURSE(ID);
96ALTER TABLE TEE_SET ADD CONSTRAINT UNIQ_TEE_SET_POS UNIQUE(POS, COURSE_ID);
97ALTER TABLE TEE ADD CONSTRAINT FK_TEE_REF_TEE_SET FOREIGN KEY(TEE_SET_ID) REFERENCES TEE_SET(ID);
98ALTER TABLE TEE ADD CONSTRAINT FK_TEE_REF_HOLE FOREIGN KEY(HOLE_ID) REFERENCES HOLE(ID);