--- 1A Databases
--- Computer Lab 
--- University of Cambridge 
--- October 2018
--- by Timothy G. Griffin
--- tgg22@cam.ac.uk
---
--- DATA CUBE EXAMPLE FOR LECTURE 8
---
--- 
--- This "data cube" example based on our small movie database. 
--- I'll admit that this example is a bit contrived, 
--- but I don't want to switch to another data set. 
---
--- Our main table is the fact table that  has two dimensions and one fact: 
---
---   release_dates_facts = our fact table 
---   ------------------
---   release_date        = release date dimension, day : month : year 
---   country,            = country dimension, country : region : area 
---   total               = fact: number of movie releases for associated release_date, contry 
---
--  Our "Star Schema" is as follows (a * marks the key attributes) : 
--- 
---  date_dimension            release_dates_facts
---  --------------            -------------------     country_dimension       
---  *release_date <---------- *release_date           -----------------       area_contains_region   regions 
---  year                      *country -------------> *country                --------------------   -------
---  month                     total                   region_id --------------> *region_id --------> *region_id      
---  day                                                                         area_id              region 
----                                                                                |
----                                                                                |                 
---                                                                                 |                  area
---										    |		       -------
---										    -----------------> *area_id
---												       area 
---
---------------------------------------------

-- The date dimension. 
--
-- The release_dates table contains dates of the form YYYY-MM-DD. 
-- For example, the date '2016-02-17' goes to record  ('2016', '02', '17').
-- Yes, this is a bit contrived. 
--
-- Then need for (..) in the following table creation and the "with data" keywords
-- seem to be HyperSQL-isms that are not part of the SQL standard.
-- 

CREATE TABLE date_dimension (release_date, year, month, day) 
as 
(select distinct release_date, 
       substring(release_date, 1, 4) as year,
       substring(release_date, 6, 2) as month,
       substring(release_date, 9, 2) as day
from release_dates where release_date like '____-__-__')
with data; 

-----------------------------------------------

-- The country dimension
--
-- The UN has an official document describing global areas, containing regions, containing countries. 
-- For example, 
-- 
-- COUNTRY                         AREA                             REGION
-- ------------------------------  -------------------------------  ---------------------
-- Albania                         Europe                           Southern Europe
-- Algeria                         Africa                           Northern Africa
-- Andorra                         Europe                           Southern Europe
-- Argentina                       Latin America and the Caribbean  South America
-- Armenia                         Asia                             Western Asia
-- Aruba                           Latin America and the Caribbean  Caribbean
-- Australia                       Oceania                          Australia/New Zealand
-- ...                             ...                              ...
-- ...                             ...                              ... 
--
-- What follows is an implementation of an ER model of this information.
-- 

-- Area entity 
create table areas (
   area_id integer NOT NULL,
   area varchar(100) NOT NULL   
); 

insert into areas (area_id, area) values
(0, 'Africa'),
(1, 'Asia'),
(2, 'Europe'),
(3, 'Latin America and the Caribbean'),
(4, 'Oceania'),
(5, 'Northern America'); 


-- Region entity 
create table regions (
   region_id integer NOT NULL,
   region varchar(100) NOT NULL	
); 

insert into regions (region_id, region) values
-- Africa 
(0, 'Eastern Africa'),
(1, 'Middle Africa'),
(2, 'Northern Africa'),
(3, 'Southern Africa'),
(4, 'Western Africa'),
-- Asia
(5, 'Eastern Asia'),
(6, 'South-central Asia'),
(7, 'South-eastern Asia'),
(8, 'Western Asia'),
-- Europe
(9, 'Eastern Europe'),
(10, 'Northern Europe'),
(11, 'Southern Europe'),
(12, 'Western Europe'), 
-- Latin America
(13, 'Caribbean'),
(14, 'Central America'),
(15, 'South America'),
-- Oceana 
(16, 'Australia/New Zealand'),
(17, 'Melanesia'),
(18, 'Micronesia Polynesia'),
-- Northern America   
(19, 'Canada'),
(20, 'USA');



-- area_contains_region relationship 
create table area_contains_region (
    area_id    integer NOT NULL,
    region_id  integer NOT NULL
);

insert into area_contains_region (area_id, region_id) values
-- Africa 
(0, 0),
(0, 1),
(0, 2),
(0, 3),
(0, 4),
-- Asia
(1, 5),
(1, 6),
(1, 7),
(1, 8),
-- Europe
(2, 9),
(2, 10),
(2, 11),
(2, 12),
-- Latin America
(3, 13),
(3, 14),
(3, 15),
-- Oceana
(4, 16),
(4, 17),
(4, 18),
-- Northern America   
(5, 19),
(5, 20);



-- country, region relationship 
create table country_dimension (
    country varchar(100) NOT NULL,
    region_id  integer NOT NULL
);

-- I have not included all countries in the UN standard, only those that
-- are in our release_dates table.
--
insert into country_dimension (country, region_id) values
-- Africa, Eastern Africa
('Ethiopia', 0),
('Kenya', 0),
('Zimbabwe', 0),
-- Africa, Middle Africa
-- Africa, Northern Africa 
('Algeria', 2),
('Egypt', 2),
('Morocco', 2),
('Tunisia', 2),
-- Africa, Southern Africa 
('South Africa', 3),
-- Africa, Western Africa 
('Ghana', 4), 
('Nigeria', 4),
-- Asia, Eastern Asia
('China', 5),
('Hong Kong', 5),  -- is 'China, Hong Kong SAR' in UN standard
('Japan', 5),
('South Korea', 5), -- 'Republic of Korea' in UN standard 
('Mongolia', 5),
('Taiwan', 5), -- not in UN standard 
-- Asia, South-central Asia
('Bangladesh', 6),
('India', 6),
('Iran', 6), -- is 'Iran (Islamic Republic of)' in UN standard
('Kazakhstan', 6),
('Kyrgyzstan', 6),
('Nepal', 6),
('Pakistan', 6),
('Sri Lanka', 6),
('Tajikistan', 6),
-- Asia, South-eastern Asia
('Cambodia', 7),
('Indonesia', 7),
('Malaysia', 7),
('Philippines', 7),
('Singapore', 7),
('Thailand', 7),
('Vietnam', 7), -- is 'Viet Nam' in UN standard
-- Asia, Western Asia
('Armenia', 8),
('Azerbaijan', 8),
('Bahrain', 8),
('Cyprus', 8),
('Georgia', 8),
('Iraq', 8),
('Israel', 8),
('Jordan', 8),
('Kuwait', 8),
('Lebanon', 8),
('Occupied Palestinian Territory', 8),
('Oman', 8),
('Qatar', 8),
('Saudi Arabia', 8),
('Syria', 8), -- is 'Syrian Arab Republic' in UN standard
('Turkey', 8),
('United Arab Emirates', 8),
-- Europe, Eastern Europe
('Belarus', 9), 
('Bulgaria', 9), 
('Czech Republic', 9), 
('Hungary', 9), 
('Kosovo', 9), -- not in UN standard 
('Moldova', 9),  -- is 'Republic of Moldova' in UN standard
('Poland', 9),
('Romania', 9), 
('Russia', 9),  -- 'Russian Federation' in UN standard
('Slovakia', 9),
('Ukraine', 9),
-- Europe, Northern Europe
('Denmark', 10),
('Estonia', 10),
('Finland', 10),
('Iceland', 10),
('Ireland', 10),
('Latvia', 10),
('Lithuania', 10),
('Norway', 10),
('Sweden', 10),
('UK', 10),-- is 'United Kingdom of Great Britain and Northern Ireland' in UN standard
-- Europe, Southern Europe
('Albania', 11),
('Andorra', 11),
('Bosnia and Herzegovina', 11),
('Croatia', 11),
('Greece', 11),
('Italy', 11),
('Malta', 11),
('Montenegro', 11), -- not in UN document
('Portugal', 11),
('Serbia', 11), -- not in UN document
('Serbia and Montenegro', 11), -- not in UN document
('Slovenia', 11),
('Spain', 11),
('Federal Republic of Yugoslavia', 11), -- 'Yugoslavia' in UN standard 
('Republic of Macedonia', 11), -- 'The former Yugoslav Republic of Macedonia' in UN standard
-- Europe, Western Europe
('Austria', 12), 
('Belgium', 12), 
('France', 12), 
('Germany', 12), 
('Luxembourg', 12), 
('Netherlands', 12), 
('Switzerland', 12),
-- Latin America and the Caribbean, Caribbean,
('Aruba', 13), 
('Bahamas', 13), 
('Dominican Republic', 13),
('Jamaica', 13),
('Puerto Rico', 13), 
('Trinidad and Tobago', 13), 
-- Latin America and the Caribbean, Central America
('Costa Rica', 14),
('El Salvador', 14),
('Guatemala', 14),
('Honduras', 14),
('Mexico', 14),
('Nicaragua', 14),
('Panama', 14),
-- Latin America and the Caribbean, South America
('Argentina', 15), 
('Bolivia', 15), 
('Brazil', 15), 
('Chile', 15), 
('Colombia', 15), 
('Ecuador', 15), 
('Guyana', 15), 
('Paraguay', 15), 
('Peru', 15), 
('Suriname', 15), 
('Uruguay', 15), 
('Venezuela', 15),
-- Oceana, Australia/New Zealand
('Australia', 16), 
('New Zealand', 16), 
-- Oceana, Melanesia
('Fiji', 17), 
-- Oceana, Micronesia Polynesia
-- Northern America 
('Canada', 19),
('USA', 20);


---------------------------------------------
--- Of the 7128 release dates in our small relational database
--- there are only 54 that do not fit the YYYY-MM-DD pattern. (Such as
--- 2007, 2017-09, and 2).  We will ignore these. 
---------------------------------------------

CREATE TABLE release_dates_facts (release_date, country, total)
as 
(select release_date, country, count(*) as total 
from release_dates
where release_date like '____-__-__' 
group by release_date, country)
with data; 

commit; 



