-- Generated by Oracle SQL Developer Data Modeler 4.1.5.907
--   at:        2017-03-17 13:55:25 EDT
--   site:      Oracle Database 12c
--   type:      Oracle Database 12c




CREATE TABLE DIM_PERSON
  (
    PERSON_ID  NUMBER NOT NULL ,
    FULL_NAME  VARCHAR2 (60 BYTE) NOT NULL ,
    LAST_NAME  VARCHAR2 (30 BYTE) NOT NULL ,
    FIRST_NAME VARCHAR2 (30 BYTE) NOT NULL
  ) ;
CREATE UNIQUE INDEX DIM_PERSON__IDX ON DIM_PERSON
  (
    PERSON_ID ASC
  )
  ;
ALTER TABLE DIM_PERSON ADD CONSTRAINT DIM_PERSON_PK PRIMARY KEY ( PERSON_ID ) ;


CREATE TABLE DIM_PLAYER
  (
    PLAYER_ID         NUMBER NOT NULL ,
    SPORT_TEAM_ID     NUMBER NOT NULL ,
    PLAYER_LAST_NAME  VARCHAR2 (30 BYTE) ,
    PLAYER_FIRST_NAME VARCHAR2 (30 BYTE) ,
    PLAYER_FULL_NAME  VARCHAR2 (30 BYTE)
  ) ;
ALTER TABLE DIM_PLAYER ADD CONSTRAINT DIM_PLAYER_PK PRIMARY KEY ( PLAYER_ID ) ;


CREATE TABLE DIM_SPORTING_EVENT
  (
    SPORTING_EVENT_ID             NUMBER NOT NULL ,
    SPORT_LOCATION_ID             NUMBER NOT NULL ,
    SPORT_TYPE_NAME               VARCHAR2 (15 BYTE) NOT NULL ,
    HOME_TEAM_ID                  NUMBER NOT NULL ,
    AWAY_TEAM_ID                  NUMBER NOT NULL ,
    START_DATE_TIME               DATE NOT NULL ,
    SOLD_OUT                      NUMBER (1) NOT NULL ,
    DIM_SPORT_TEAM_SPORT_TEAM_ID  NUMBER NOT NULL ,
    DIM_SPORT_TEAM_SPORT_TEAM_ID1 NUMBER NOT NULL
  ) ;
ALTER TABLE DIM_SPORTING_EVENT ADD CONSTRAINT DIM_SPORTING_EVENT_PK PRIMARY KEY ( SPORTING_EVENT_ID ) ;


CREATE TABLE DIM_SPORT_LOCATION_SEATS
  (
    SPORT_LOCATION_SEAT_ID    NUMBER NOT NULL ,
    SPORT_LOCATION_ID         NUMBER (3) NOT NULL ,
    LOCATION_NAME             VARCHAR2 (60 BYTE) NOT NULL ,
    LOCATION_CITY             VARCHAR2 (60 BYTE) NOT NULL ,
    LOCATION_SEATING_CAPACITY NUMBER (7) ,
    LOCATION_LEVELS           NUMBER (1) ,
    LOCATION_SECTIONS         NUMBER (4) ,
    SEAT_LEVEL                NUMBER (1) NOT NULL ,
    SEAT_SECTION              VARCHAR2 (15) NOT NULL ,
    SEAT_ROW                  VARCHAR2 (10 BYTE) NOT NULL ,
    SEAT_NO                   VARCHAR2 (10 BYTE) NOT NULL ,
    SEAT_TYPE                 VARCHAR2 (15 BYTE) ,
    SEAT_TYPE_DESCRIPTION     VARCHAR2 (120 BYTE) ,
    RELATIVE_QUANTITY         NUMBER (2)
  ) ;
ALTER TABLE DIM_SPORT_LOCATION_SEATS ADD CONSTRAINT DIM_SPORT_LOCATION_SEATS_PK PRIMARY KEY ( SPORT_LOCATION_SEAT_ID ) ;


CREATE TABLE DIM_SPORT_TEAM
  (
    SPORT_TEAM_ID               NUMBER NOT NULL ,
    SPORT_TEAM_NAME             VARCHAR2 (30 BYTE) NOT NULL ,
    SPORT_TEAM_ABBREVIATED_NAME VARCHAR2 (10 BYTE) ,
    HOME_FIELD_ID               NUMBER (3) ,
    SPORT_TYPE_NAME             VARCHAR2 (15 BYTE) NOT NULL ,
    SPORT_TYPE_DESCRIPTION      VARCHAR2 (120 BYTE) ,
    SPORT_LEAGUE_SHORT_NAME     VARCHAR2 (10 BYTE) NOT NULL ,
    SPORT_LEAGUE_LONG_NAME      VARCHAR2 (60 BYTE) NOT NULL ,
    SPORT_LEAGUE_DESCRIPTION    VARCHAR2 (120 BYTE) ,
    SPORT_DIVISION_SHORT_NAME   VARCHAR2 (10 BYTE) NOT NULL ,
    SPORT_DIVISION_LONG_NAME    VARCHAR2 (60 BYTE) ,
    SPORT_DIVISION_DESCRIPTION  VARCHAR2 (120 BYTE)
  ) ;
ALTER TABLE DIM_SPORT_TEAM ADD CONSTRAINT DIM_SPORT_TEAM_PK PRIMARY KEY ( SPORT_TEAM_ID ) ;


CREATE TABLE FACT_SPORT_EVENT_TICKET_INFO
  (
    SPORTING_EVENT_TICKET_ID NUMBER NOT NULL ,
    SPORTING_EVENT_ID        NUMBER NOT NULL ,
    SPORT_LOCATION_SEAT_ID   NUMBER NOT NULL ,
    TICKETHOLDER_ID          NUMBER ,
    TICKET_PRICE             NUMBER (8,2) NOT NULL ,
    DIM_PERSON_ID            NUMBER NOT NULL ,
    EVENT_SPORTING_EVENT_ID  NUMBER NOT NULL ,
    SPORT_LOCATION_SEAT_ID_1 NUMBER NOT NULL ,
    TRANSFERRED_FROM         NUMBER ,
    PURCHASE_PRICE           NUMBER (8,2) ,
    TRANSFER_DATE            DATE
  ) ;
ALTER TABLE FACT_SPORT_EVENT_TICKET_INFO ADD CONSTRAINT FACT_SPORTEVENT_TICKET_INFO_PK PRIMARY KEY ( SPORTING_EVENT_TICKET_ID ) ;


ALTER TABLE DIM_SPORTING_EVENT ADD CONSTRAINT DIM_SPORT_EVENT_AWAY_TEAM_FK FOREIGN KEY ( DIM_SPORT_TEAM_SPORT_TEAM_ID1 ) REFERENCES DIM_SPORT_TEAM ( SPORT_TEAM_ID ) ;

ALTER TABLE DIM_SPORTING_EVENT ADD CONSTRAINT DIM_SPORT_EVENT_HOME_TEAM_FK FOREIGN KEY ( DIM_SPORT_TEAM_SPORT_TEAM_ID ) REFERENCES DIM_SPORT_TEAM ( SPORT_TEAM_ID ) ;

ALTER TABLE FACT_SPORT_EVENT_TICKET_INFO ADD CONSTRAINT FACT_SPRT_EVNT_TIKET_EVNT_FK FOREIGN KEY ( EVENT_SPORTING_EVENT_ID ) REFERENCES DIM_SPORTING_EVENT ( SPORTING_EVENT_ID ) ;

ALTER TABLE FACT_SPORT_EVENT_TICKET_INFO ADD CONSTRAINT FACT_SPRT_EVNT_TIKET_PERSON_FK FOREIGN KEY ( DIM_PERSON_ID ) REFERENCES DIM_PERSON ( PERSON_ID ) ;

ALTER TABLE FACT_SPORT_EVENT_TICKET_INFO ADD CONSTRAINT FACT_SPRT_EVNT_TIKET_SEATS_FK FOREIGN KEY ( SPORT_LOCATION_SEAT_ID_1 ) REFERENCES DIM_SPORT_LOCATION_SEATS ( SPORT_LOCATION_SEAT_ID ) ;

ALTER TABLE DIM_PLAYER ADD CONSTRAINT SPORT_TEAM_FK FOREIGN KEY ( SPORT_TEAM_ID ) REFERENCES DIM_SPORT_TEAM ( SPORT_TEAM_ID ) ;


-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                             6
-- CREATE INDEX                             1
-- ALTER TABLE                             12
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           0
-- ALTER TRIGGER                            0
-- CREATE COLLECTION TYPE                   0
-- CREATE STRUCTURED TYPE                   0
-- CREATE STRUCTURED TYPE BODY              0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          0
-- CREATE MATERIALIZED VIEW                 0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
-- 
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
-- 
-- REDACTION POLICY                         0
-- TSDP POLICY                              0
-- 
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
-- 
-- ERRORS                                   0
-- WARNINGS                                 0