drop table customer cascade constraints;
drop TABLE product CASCADE CONSTRAINTS;
drop TABLE time CASCADE CONSTRAINTS;
drop table geography cascade constraints;
drop TABLE todays_special_offers CASCADE CONSTRAINTS;
drop TABLE purchases CASCADE CONSTRAINTS;

---------------------------------------------
-- CUSTOMER Dimension
---------------------------------------------
CREATE TABLE customer
(customer_id            varchar2(10) CONSTRAINT pk_customer PRIMARY KEY,
 town                   varchar2(10),
 county                 varchar2(10),
 postal_code            varchar2(10),
 dob                    date,
 country                varchar2(20),
 occupation             varchar2(10));

---------------------------------------------
-- PRODUCT Dimension
---------------------------------------------
CREATE TABLE product
(product_id             varchar2(8) CONSTRAINT pk_product PRIMARY KEY,
 product_name           varchar2(30),
 category               varchar2(4),
 cost_price             number (6,2) constraint cost_price_not_null  NOT NULL,
 sell_price             number (6,2) constraint sell_price_not_null  NOT NULL,
 weight                 number (4,2),
 shipping_charge        number (5,2) constraint shipping_charge_not_null  NOT NULL,
 manufacturer           varchar2(20),
 supplier               varchar2(10));

---------------------------------------------
-- TIME Dimension
---------------------------------------------
CREATE TABLE time
(time_key               date CONSTRAINT pk_time PRIMARY KEY,
 day                    number (2,0),
 month                  number (2,0),
 quarter                number (2,0),
 year                   number (4,0),
 day_number             number (3,0),
 day_of_the_week        varchar2(10),
 week_number            number (2,0),
 public_holiday         varchar2(1)
   constraint public_holiday CHECK (public_holiday IN ('Y','N')) );

---------------------------------------------
-- GEOGRAPHY Dimension
---------------------------------------------
create table geography 
(state_id   varchar2(2) constraint state_id_pk primary key,
 state_name varchar2(20),
 region     varchar2(10),
 tax_rate   number);

---------------------------------------------
-- TODAYS_SPECIAL_OFFERS Dimension
---------------------------------------------
CREATE TABLE todays_special_offers
(product_id             varchar2(8),
 offer_date             date,
 special_price          number (6,2),
 offer_price            number (6,2),
 CONSTRAINT pk_specials PRIMARY KEY (offer_date,product_id ));

---------------------------------------------
-- Fact Table  PURCHASES
---------------------------------------------
CREATE TABLE purchases
(product_id             varchar2(8) constraint not_null_product_id NOT NULL
                                    constraint fk_product_id  REFERENCES  product(product_id),
 time_key               date constraint not_null_time   NOT NULL
                             constraint fk_time  REFERENCES  time(time_key),
 customer_id            varchar2(10) constraint not_null_customer_id   NOT NULL
                                     constraint fk_customer_id  REFERENCES  customer(customer_id),
 purchase_date          date,
 purchase_time          number(4,0),
 purchase_price         number(6,2),
 shipping_charge        number(5,2),
 state_id               varchar2(2),
 today_special_offer    varchar2(1) constraint special_offer
                                    CHECK (today_special_offer IN ('Y','N')));

alter session set nls_language='english';
alter session set nls_date_format='DD-MON-YYYY';