CREATE TABLE COUNTRY_DOS(COUNTRYID NUMERIC(10) NOT NULL,
             COUNTRYNAME VARCHAR2(40) NOT NULL,
             REGION VARCHAR2(11) NOT NULL,
             CONSTRAINT COUNTRY_PK_DOS PRIMARY KEY(COUNTRYID));

CREATE TABLE CUSTOMER_DOS(MOBILEPHONENUMBER NUMERIC(10) NOT NULL,
                      FIRSTNAME VARCHAR2(40) NOT NULL,
                      LASTNAME VARCHAR2(40) NOT NULL,
              EMAILADDRESS VARCHAR(40) NOT NULL,
                      HOUSEADDRESS VARCHAR2(40) NOT NULL,
                      STREETADDRESS VARCHAR2(50) NOT NULL,
                      CITY VARCHAR2(30) NOT NULL,
                      COUNTY_STATE VARCHAR2(40) NOT NULL,
                      ZIPCODE VARCHAR2(20),
                      COUNTRYID NUMERIC(10) NOT NULL,
              PASSPORT VARCHAR2(10) NOT NULL,
              CONSTRAINT CUSTOMER_PK_DOS PRIMARY KEY(EMAILADDRESS),
              CONSTRAINT COUNTRY_FK1_DOS FOREIGN KEY(COUNTRYID)
                      REFERENCES COUNTRY_DOS(COUNTRYID));

CREATE TABLE PRODUCT_DOS(PRODUCTID NUMERIC(10) NOT NULL,
                     PRODUCTNAME VARCHAR2(255) NOT NULL,
                     PRODUCTUNITCOST DECIMAL(10,2) NOT NULL,
                     CONSTRAINT PRODUCT_PK_DOS PRIMARY KEY(PRODUCTID));

CREATE TABLE TIMEDIMENSION_DOS(TIMEID NUMERIC(10) NOT NULL,
                           TIMEDAY VARCHAR2(10) NOT NULL,
                           TIMEMONTH VARCHAR2(9) NOT NULL,
                           TIMEQUARTER NUMERIC(1) NOT NULL,
                           TIMEYEAR NUMERIC(4) NOT NULL,
                           TIMEDAYOFWEEK VARCHAR2(9) NOT NULL,
                           CONSTRAINT TIME_PK_DOS PRIMARY KEY(TIMEID));

CREATE TABLE SALES_FACT_DOS(SALESID NUMERIC(10) NOT NULL,
                        SALESDATE DATE NOT NULL,
                        SALESUNITS NUMERIC(10) NOT NULL,
                        EUROSALESAMOUNT DECIMAL(10,2) NOT NULL,
                        TIMEID NUMERIC(10) NOT NULL,
                        PRODUCTID NUMERIC(10) NOT NULL,
                        COUNTRYID NUMERIC(10) NOT NULL,
                        CUSTOMERID VARCHAR2(10) NOT NULL,
                        CONSTRAINT SALES_PK_DOS PRIMARY KEY(SALESID),
                        CONSTRAINT TIME_FK_DOS FOREIGN KEY(TIMEID)
                        REFERENCES TIMEDIMENSION_DOS(TIMEID),
                        CONSTRAINT PRODUCT_FK_DOS FOREIGN KEY(PRODUCTID)
                        REFERENCES PRODUCT_DOS(PRODUCTID),
            CONSTRAINT COUNTRY_FK2_DOS FOREIGN KEY(COUNTRYID)
                        REFERENCES COUNTRY_DOS(COUNTRYID),
                        CONSTRAINT CUSTOMER_FK_DOS FOREIGN KEY(CUSTOMERID)
                        REFERENCES CUSTOMER_DOS(EMAILADDRESS));

CREATE DIMENSION CUSTOMERDIMENSION_DOS
 LEVEL CUSTOMERID   IS CUSTOMER_DOS.MOBILEPHONENUMBER
 LEVEL HOUSEADDRESS IS CUSTOMER_DOS.HOUSEADDRESS
 LEVEL STREETADDRESS IS CUSTOMER_DOS.STREETADDRESS
 LEVEL CITY IS CUSTOMER_DOS.CITY
 LEVEL COUNTY_STATE IS CUSTOMER_DOS.COUNTY_STATE
 LEVEL COUNTRY IS COUNTRY_DOS.COUNTRYID
 LEVEL REGION IS COUNTRY_DOS.REGION
 HIERARCHY ADDRESSROLLUP (
   HOUSEADDRESS  CHILD OF
   STREETADDRESS CHILD OF
   CITY   CHILD OF
   COUNTY_STATE CHILD OF
   COUNTRY CHILD OF
   REGION
  JOIN KEY CUSTOMER_DOS.COUNTRYID REFERENCES COUNTRY)
  ATTRIBUTE CUSTOMERID DETERMINES
   (CUSTOMER_DOS.FIRSTNAME, CUSTOMER_DOS.LASTNAME)
 ATTRIBUTE COUNTRY DETERMINES (COUNTRY_DOS.COUNTRYNAME);


INSERT INTO COUNTRY_DOS VALUES(1,'Ireland','EU');
INSERT INTO COUNTRY_DOS VALUES(2,'England','EU');
INSERT INTO COUNTRY_DOS VALUES(3,'France','EU');
INSERT INTO COUNTRY_DOS VALUES(4,'Spain','EU');
INSERT INTO COUNTRY_DOS VALUES(5,'Germany','EU');
INSERT INTO COUNTRY_DOS VALUES(6,'UK','EU');


INSERT INTO PRODUCT_DOS VALUES(1,'Flights within a country', 25.00);
INSERT INTO PRODUCT_DOS VALUES(2,'Flights Throughout EU' ,70.00);
INSERT INTO PRODUCT_DOS VALUES(3,'Late Booking EU' ,50.00);
INSERT INTO PRODUCT_DOS VALUES(4,'Late Booking INT' ,150.00);
INSERT INTO PRODUCT_DOS VALUES(5,'Budjet Hotel' ,45.00);
INSERT INTO PRODUCT_DOS VALUES(6,'Mid Range Hotel', 60.00);
INSERT INTO PRODUCT_DOS VALUES(7,'Premium Hotel' ,100.00);
INSERT INTO PRODUCT_DOS VALUES(8,'Overweight KG' ,15.00);
INSERT INTO PRODUCT_DOS VALUES(9,'Boarding Card Pen', 40.00);


--CUSTOMER PURCHASING A DOMESTIC FLIGHT FROM IRELAND in 2016

INSERT INTO CUSTOMER_DOS VALUES('0877785411', 'Diarmaid', 'O Sullivan', 'diarmaidosullivan@mycit.ie','Ambleside', 'SCR', 'Limerick', 'Limerick', '12554', '1', 'P225654');
--Add the two lines below to each customer, ensuring that they satisfy the other queries
INSERT INTO CUSTOMERS_LRT VALUES('Liam','Timmins','Fastnet','Castlerock','Carrigaline','Cork','CK10CK','123456789','1234567899','liamtim@gmail.com',1);
--Insert Time DIMENSION - decrypt this f rom the TO_DATE entered in the sales fact table
INSERT INTO TIMEDIMENSION_LRT VALUES(1,03,05,2,2016,'Saturday');
-- Record international flight / any other products purchased
INSERT INTO SALES_FACT_LRT VALUES(1,1,TO_DATE('2016/05/03', 'yyyy/mm/dd'),1,75.00,2,1,'liamtim@gmail.com');



--Then do a customer that suits next query here
INSERT INTO CUSTOMER_DOS VALUES('0877556474', 'Laura', 'Butler', 'laurabutler@gmail.com','Downside', 'Ralla Road', 'Paris', 'Paris', '12555', '3', 'P256487');
INSERT INTO CUSTOMER_DOS VALUES('0851256698', 'James', 'Green', 'jamesgreen@mycit.ie','Rockville', 'Chico Street', 'Valencia', 'Valencia', '34554', '004', 'P226474');
INSERT INTO CUSTOMER_DOS VALUES('0836654214', 'Marcus', 'Finucan', 'marucsfinucan@mycit.ie','25', 'Amber Road', 'Berlin', 'Berlin', '67554', '5', 'P233644');
INSERT INTO CUSTOMER_DOS VALUES('0836652312', 'John', 'O Shea', 'johnoshea@mycit.ie','Nashville Terrace', 'NCR', 'Limerick', 'Limerick', '99554', '1', 'P015654');
INSERT INTO CUSTOMER_DOS VALUES('0862584213', 'David', 'O Leary', 'davidoleary@gmail.com','Broke Yard', 'N50', 'Barcelona', 'Barcelona', '13364', '4', 'P225111');
INSERT INTO CUSTOMER_DOS VALUES('0869089523', 'JJ', 'O Neil', 'jjoneil@mycit.ie','44', 'Mount Terrace', 'Dortmund', 'Dortmund', '12666', '5', 'P227774');
INSERT INTO CUSTOMER_DOS VALUES('0877744569', 'Roisin', 'Murphy', 'roisinmurphy@gmail.com','122', 'Raheen', 'Dublin', 'Dublin', '33554', '1', 'P885654');