Data Model

ERD chart

• Raw tables: Tables used to extract raw data. Names include suffix _raw.

• Dimentional tables: Tables built from transforming raw tables. Names include suffix _dim.

• Fact tables: Tables build from transforming raw tables. Names inculde suffix _fact.

• How to get metadata of tables:

MySQL keep metadata in information_schema.TABLES and update that table during the working time of DBMS.

SELECT table_name AS 'Table', table_rows, avg_row_length, round(((data_length + index_length) / 1024 / 1024), 2) 'Size (MB)'  FROM information_schema.TABLES WHERE table_schema = 'covid19cor';

Table	Rows	Columns	AVG row length	Table size	Period	Description
covid19_us_raw	1,280,029	15	144	176.7 MiB	2020.01.22 - 2021.02.25	Raw table for covid19 in the US
covid19_us_dim	3,221	12	152	0.47 MiB	2020.01.22 - 2021.02.25	Dim table for covid19 in the US
covid19_us_fact	22,222	5	165	3.52 MiB	2020.01.22 - 2021.02.25	Fact table for covid19 in the US
covid19_us_monthly_fact	812	8	141	0.11 MiB	2020.01.22 - 2021.02.25	Fact table for covid19 in the US (aggregate monthly)
covid19_global_raw	73,101	7	79	5.52 MiB	2020.01.22 - 2021.02.25	Raw table for covid19 in the global
covid19_global_fact	70,562	5	141	13.03 MiB	N/A	Fact table for global
covid19_global_monthly_fact	2646	8	111	48 KiB	N/A	Fact table for global (aggregate monthly)
covid19_sum_fact	390	?	210	0.08 MiB	2020.01.22 - 2021.02.25	Sum fact tables from covid19_us_fact and covid19_global_fact so that each row has sum total cases/deaths in the US and sum total cases/deaths in global.
`covid19_sum_monthly_fact	14	?	1170	0.02 MiB	aggregated from table covid19_sum_fact
stock_ticker_raw	505	8	194	112 KiB	N/A	Raw table for stock ticker. Acquired by fetching wiki page of S&P 500.
stock_price_raw	97,416	8	91	8.5 MiB	2020.01.02 - 2020.11.01	Raw table for stock price extract from Yahoo finance.
stock_price_fact	104,695	9	145	18.1 MiB	2020.01.02 - 2020.11.01	Fact table for stock price transfomred from raw table.
stock_price_monthly_fact	5,011	12	317	1.7 MiB	2020.01.02 - 2020.11.01	Monthly fact table aggregated from daily fact table.
stock_index_fact	294	?	167	0.05 MiB	??? - 2021.02.25	Fact table of major stock indexes such as nasdaq100, dowjones, s&p500
stock_index_monthly_fact	14	?	1170	0.02 MiB	Aggregated from stock_index_fact
bol_raw	37,236	5	70	2.52 MiB	2020.01.01 - 2021.02.25	Raw table for unemployment rate data extract from U.S Bureau of Labor
bol_series_dim	23	4	712	0.03 MiB	N/A	Dimensional table contains desired features (e.g., unemployment rate Employment / Unemployment rate
bol_series_fact	5,819	5	61	0.58 MiB	N/A	Fact table transform from bol_series_raw table
covid_stock_fact	390	?	210	0.08 MiB	N/A	Represents correslation between covid19 and stock market. This table is consolidated from covid19_sum_fact and stock_index_fact.
covid_stock_monthly_fact	14	?	1170	0.02 MiB	N/A	Aggregated from covid19_stock_fact for monthly data.One row for one month.
`covid_stock_bol_monthly_fact	5704	?	278	1.52 MiB	N/A	Consolidated from covid19_sum_fact, stock_index_fact and bol_series_fact. Represents correlations between Covid19, stock martket and job market.