Individual Project
Description
The dataset contains economic, education, and health data for over 200 countries from 1974 to 2024. The dataset is split into four tables that can work together in a MySQL database. The Country table has basic information about each country name, country code, what continent it's on, its region, development status, capital city, and population. Country varies by different continents and regions, and its identifier is the country name. The EconomicIndicator table contains yearly economic data for each country, including GDP in current US dollars, GDP per capita, GDP growth rate, consumer price inflation rate, and unemployment rate as a percentage of the total labor force. The EducationIndicator table has education related features by the year, including adult literacy rate for the population aged 15 and up, government expenditure on education as a percentage of GDP, gross primary school enrollment rate, primary education completion rate, and the number of primary school age children who are out of school. The HealthIndicator table has yearly health statistics including life expectancy at birth, measles immunization coverage for children aged 12 to 23 months, infant mortality rate per 1000 live births, current health expenditure as a percentage of GDP, and HIV prevalence in the population aged 15 to 49. All of the indicator related data was from the World Bank's World Development Indicators database. The database is structured in the Third Normal Form to reduce any redundancy and ensure data integrity. Each indicator table is linked to the Country table by a foreign key relationship, allowing for efficient queries.
Files
Steps to reproduce
Step 1: Download the indicator data The indicator related data were downloaded as CSV files from the World Bank DataBank (https://databank.worldbank.org/source/world-development-indicators). One CSV was downloaded for each of the economic, education, and health indicators. For each download, I selected all 266 countries and all available years. The economic indicators selected were GDP (current US$), GDP per capita (current US$), GDP growth (annual %), inflation consumer prices (annual %), and unemployment total (modeled ILO estimate). For education I used literacy rate adult total, government expenditure on education total (% of GDP), school enrollment primary (% gross), primary completion rate total, children out of school primary. The health indicators include life expectancy at birth total (years), immunization measles, mortality rate infant, current health expenditures (% of GDP), and prevalence of HIV total. Step 2 - Data Preprocessing The CSV files from the World Bank were in a wide format, with each year as a separate column. A Python script using pandas library was written to convert these files into the long format, so that each row represents one country for one year. The script used pandas melt function to unpivot year columns into rows, then pivot_table function is used to structure data so that each indicator became a column. Missing values that were input as ".." were converted to NaN. The country table data features such as continent, region, development status, and capital city were collected manually using sources such as UN Statistics Division (https://unstats.un.org/unsd/methodology/m49/) and the World Bank country pages (https://data.worldbank.org/country). Step 3 - Database Design (ER Diagram) An ER diagram was designed using MySQL with four entities including Country, EconomicIndicator, EducationIndicator, and HealthIndicator. Each indicator table has a one-to-many relationship with the Country table using a country_id foreign key. The schema satisfies 3NF, which ensures no partial or transitive dependencies. Step 4 - Database Implementation The database was implemented in MySQL Workbench. Tables were created for each entity using the SQL CREATE TABLE statements with appropriate primary keys, foreign keys, and data types. Data was loaded into each table using the Table Data Import Wizard, the imported files were the cleaned datasets from step 2. The tools used for this project include MySQL Workbench 8.0 CE, Python using pandas library, Microsoft Excel for data overview, and World Bank DataBank website for indicator data.