lab3and4.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b81a2a64-4925-49af-8b6a-e16d22886d42",
   "metadata": {},
   "source": [
    "## Labs 3 and 4\n",
    "### Data Engineering 1\n",
    "\n",
    "60 points\n",
    "\n",
    "In this lab, we are going to build the Country Analysis Relational DataBase (which we will call the C.A.R.D.B. or the \"Cardi B\"):\n",
    "\n",
    "![CardbiB](https://media.giphy.com/media/3oEjI5ry4IwZ3RDw6k/giphy.gif \"cardib\")\n",
    "\n",
    "We will be collecting data from two sources. First, we will use open data from the World Bank's [Sovereign\n",
    "Environmental, Social, and Governance (ESG) Data](https://datatopics.worldbank.org/esg/) project. The ESG data reports data from every country in the world over the time frame from 1960-2021 on a wide variety of topics including education, health, and economic factors within the countries. Second, we will use data on the quality and democratic character of countries' governments as reported by the [Varieties of Democracy (V-Dem)](https://www.v-dem.net/data/the-v-dem-dataset/) project at the University of Notre Dame. By using both data sources, we can conduct analyses to see whether democratic openness leads to better societal outcomes for countries. We can also write SQL queries to capture a wide range of information on countries' political parties, tax systems, and banking industries, for example. Or as Cardi B would say, \"You in the club just to party, I'm there, I get paid a fee. I be in and out them banks so much, I know they're tired of me.\"\n",
    "\n",
    "Our goals are to download the data and load them into our Python environment, to use `pandas` to clean the data and merge corresponding datasets, to confirm that the tables comprise a 3rd normal form relational database, to load the dataframes as entities in a PostgreSQL database, to construct an ER diagram for the database, and to write several SQL queries for the database. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb452059-1a14-4ce1-ae68-717c4177a47c",
   "metadata": {},
   "source": [
    "(1) Initialize a new project for this lab by following these steps:\n",
    "\n",
    "a. Create a new GitHub repository named \"Lab3and4\". Choose the option to include a README.md file, select the MIT license and choose a Python .gitignore file. Once the repository is created, edit the .gitignore file on the GitHub website, and add the following lines to the file:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49b60b74-6657-42fa-bd01-a8b272e25579",
   "metadata": {},
   "source": [
    "```\n",
    "#Data\n",
    "*.csv  \n",
    "Country_Year_V-Dem_Core_CSV_v12/   \n",
    "__MACOSX/                            \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25f1191a-4ee3-4ba0-b568-5ab8067ca4a7",
   "metadata": {},
   "source": [
    "This ensures that none of the data files are uploaded to your public GitHub repo (although the data are open, they have more restrictive licenses, and the best approach is to share code but not the data). [1 point]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c4ff4d8-75b8-4ccb-893e-2d85a789e0b9",
   "metadata": {},
   "source": [
    "b. Use the `git clone` command to download the GitHub files into a folder on your computer. Then change the directory to your local Lab3and4 folder. Next create four new files:\n",
    "\n",
    "  * A requirements.txt file that loads jupyterlab==3.4.7, requests==2.28.1, pandas==1.5.1, numpy==1.23.4, psycopg2==2.9.5, and sqlalchemy==1.4.42 \n",
    "    \n",
    "  * A Dockerfile that loads a recent version of Python with the bulleye installation of Linux, copies the requirements.txt file into the image, runs commands to update pip and install the packages in requirements.txt, runs the commands to install nodejs and dbdocs, sets the working directory to /lab, exposes port 8888, and launches Jupyter lab. Use docker build to create an image from this Dockerfile.\n",
    "    \n",
    "  * A .env file that sets your POSTGRES_PASSWORD\n",
    "    \n",
    "  * A compose.yaml file that: \n",
    "  \n",
    "    * Runs the postgres:latest image from Docker hub and supplies the .env file and maps port 5432 to your local 5432 port. \n",
    "    \n",
    "    * Runs the image you just created, supplying your .env file, mapping the 8888 port to a port on your computer, and mapping the /lab directory to your local current working directory. \n",
    "    \n",
    "    * Defines a volume called 'postgresdata' and makes sure the postgres service maps the container's /var/lib/postgresql/data directory to this volume. \n",
    "    \n",
    "    * And defines a network named 'lab3network' and set each service to use this network.\n",
    "\n",
    "Once you've created these files and run the `docker build` command, type `docker compose up` to launch your project environment. [2 points]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7e355f6-e34e-4f1a-b508-2868b663684e",
   "metadata": {},
   "source": [
    "(2) The following problems are a walk-through of the various data wrangling steps we need to take with the data.\n",
    "\n",
    "Please refer to chapter 8 of \"[Surfing the Data Pipeline with Python](https://jkropko.github.io/surfing-the-data-pipeline/ch8.html)\" for guidance and examples on how to do these tasks.\n",
    "\n",
    "a. Import the `pandas`, `numpy`, `requests`, `os`, `psycopg2`, `zipfile`, and `io` packages. (`os`, `zipfile`, and `io` are included in base Python and do not need to be included in your requirements.txt file). Also load the `create_engine` function from `sqlalchemy`. Then load your POSTGRES_PASSWORD environmental variable into your Python kernel. [2 points]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "679d2a2a-e8c7-4037-8417-4ddd06d95c7f",
   "metadata": {},
   "source": [
    "b. Both the World Bank and V-Dem store their data in zipped directories containing CSV files. Download the World Bank data into your container's top-level directory by typing the following code:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7558a87-f243-46f5-a614-fc57bdae1dfe",
   "metadata": {},
   "source": [
    "```\n",
    "url = 'https://databank.worldbank.org/data/download/ESG_csv.zip'\n",
    "r = requests.get(url)\n",
    "z = zipfile.ZipFile(io.BytesIO(r.content))\n",
    "z.extractall()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "523cb7da-502d-4126-99fa-06ffecaaf329",
   "metadata": {},
   "source": [
    "And download the V-Dem data by typing:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aeb8d59f-9103-4a1c-bcff-585f898180c9",
   "metadata": {},
   "source": [
    "```\n",
    "url = 'https://v-dem.net/media/datasets/Country_Year_V-Dem_Core_CSV_v12.zip'\n",
    "r = requests.get(url)\n",
    "z = zipfile.ZipFile(io.BytesIO(r.content))\n",
    "z.extractall()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c0df91e-2af6-4173-bb3f-7f737abaac9d",
   "metadata": {},
   "source": [
    "There are multiple files downloaded, but only three we need for this project. Load the 'Country_Year_V-Dem_Core_CSV_v12/V-Dem-CY-Core-v12.csv', 'ESGCountry.csv', and 'ESGData.csv' files into your Python kernel. [2 points]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cbbcd0f-769f-4266-9873-b3e21168234f",
   "metadata": {},
   "source": [
    "c. First, let's focus on the V-Dem data ('Country_Year_V-Dem_Core_CSV_v12/V-Dem-CY-Core-v12.csv'). Use `pandas` methods to perform the following tasks:\n",
    "\n",
    "* Keep only the 'country_text_id', 'country_name','year', 'v2x_polyarchy' columns.\n",
    "\n",
    "* Use the `.query()` method to keep only the rows in which year is greater than or equal to 1960 and less than or equal to 2021.\n",
    "\n",
    "* Rename 'country_text_id' to 'country_code', 'country_name' to 'country_name_vdem', and 'v2x_polyarchy' to 'democracy'.\n",
    "\n",
    "* Sort the rows by 'country_code' and 'year' in ascending order. [2 points]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75103907-bec7-4ec7-b5b9-27f25b0a673a",
   "metadata": {},
   "source": [
    "d. Next let's focus on the country-level World Bank data ('ESGCountry.csv'). Use `pandas` methods to perform the following tasks:\n",
    "\n",
    "* Keep only the 'Country Code', 'Table Name', 'Long Name', 'Currency Unit', 'Region', and 'Income Group' columns.\n",
    "\n",
    "* Rename 'Country Code' to 'country_code', 'Table Name' to 'country_name_wb', 'Long Name' to 'country_longname', 'Currency Unit' to 'currency_unit', 'Region' to 'region', and 'Income Group' to 'income_group'.\n",
    "\n",
    "* The countries in this dataset include various grouping of countries such as \"Europe & Central Asia\". We want to remove these observations from the data. Use the .query() method to remove the rows in which 'country_name_wb' is equal to one of these non-countries. (Hint: within the .query() method, you can reference an external Python variable such as `noncountries` by placing an @ sign in front of the variable name.) Please use this list of non-countries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "5644672e-3233-476e-ab40-fb62ee0aa32e",
   "metadata": {},
   "outputs": [],
   "source": [
    "noncountries = [\"Arab World\", \"Central Europe and the Baltics\",\"Caribbean small states\",\n",
    "                \"East Asia & Pacific (excluding high income)\",\"Early-demographic dividend\",\"East Asia & Pacific\",\n",
    "                \"Europe & Central Asia (excluding high income)\",\"Europe & Central Asia\", \"Euro area\",\n",
    "                \"European Union\",\"Fragile and conflict affected situations\",\"High income\",\n",
    "                \"Heavily indebted poor countries (HIPC)\",\"IBRD only\",\"IDA & IBRD total\",\n",
    "                \"IDA total\",\"IDA blend\",\"IDA only\",\n",
    "                \"Latin America & Caribbean (excluding high income)\",\"Latin America & Caribbean\",\"Least developed countries: UN classification\",\n",
    "                \"Low income\",\"Lower middle income\",\"Low & middle income\",\n",
    "                \"Late-demographic dividend\",\"Middle East & North Africa\",\"Middle income\",\n",
    "                \"Middle East & North Africa (excluding high income)\",\"North America\",\"OECD members\",\n",
    "                \"Other small states\",\"Pre-demographic dividend\",\"Pacific island small states\",\n",
    "                \"Post-demographic dividend\",\"Sub-Saharan Africa (excluding high income)\",\"Sub-Saharan Africa\",\n",
    "                \"Small states\",\"East Asia & Pacific (IDA & IBRD)\",\"Europe & Central Asia (IDA & IBRD)\",\n",
    "                \"Latin America & Caribbean (IDA & IBRD)\",\"Middle East & North Africa (IDA & IBRD)\",\"South Asia\",\n",
    "                \"South Asia (IDA & IBRD)\",\"Sub-Saharan Africa (IDA & IBRD)\",\"Upper middle income\", \"World\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41c0f2f7-7242-48e0-86d2-ea3f3e32f99e",
   "metadata": {},
   "source": [
    "[2 points]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb34462a-ab2b-4757-ae45-9abb139b1cdc",
   "metadata": {},
   "source": [
    "e. Next focus on the World Bank country-time-series dataset 'ESGData.csv'. Use `pandas` methods to perform the following tasks:\n",
    "\n",
    "* Keep only the columns named 'Country Code', 'Country Name', and 'Indicator Code', or begin with '19' or '20'. (Don't type in all the years individually. Instead, use code that finds all columns that begin '19' or '20'.)\n",
    "\n",
    "* Then use the `.drop()` method to delete the column named '2050'.\n",
    "\n",
    "* Rename 'Country Code' to'country_code', 'Country Name' to 'country_name_wb', and 'Indicator Code' to 'feature'.\n",
    "\n",
    "* Type `noncountries.remove('World')` to remove 'World' from the `noncountries` list. (We want to keep the total world data for now). Then use the `.query()` method to remove the rows in which 'country_name_wb' is equal to one of the other entries in the `noncountries` list.\n",
    "\n",
    "* The features in this dataset are given strange and incomprehensible codes such as 'EG.CFT.ACCS.ZS'. Use the `replace_map` dictionary, defined below, to recode all of these values with more descriptive names for each feature."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fa936fc6-ba1b-496a-a48f-e9bc354b1a1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "replace_map = {'AG.LND.AGRI.ZS': 'agricultural_land',\n",
    " 'AG.LND.FRST.ZS': 'forest_area',\n",
    " 'AG.PRD.FOOD.XD': 'food_production_index',\n",
    " 'CC.EST': 'control_of_corruption',\n",
    " 'EG.CFT.ACCS.ZS': 'access_to_clean_fuels_and_technologies_for_cooking',\n",
    " 'EG.EGY.PRIM.PP.KD': 'energy_intensity_level_of_primary_energy',\n",
    " 'EG.ELC.ACCS.ZS': 'access_to_electricity',\n",
    " 'EG.ELC.COAL.ZS': 'electricity_production_from_coal_sources',\n",
    " 'EG.ELC.RNEW.ZS': 'renewable_electricity_output',\n",
    " 'EG.FEC.RNEW.ZS': 'renewable_energy_consumption',\n",
    " 'EG.IMP.CONS.ZS': 'energy_imports',\n",
    " 'EG.USE.COMM.FO.ZS': 'fossil_fuel_energy_consumption',\n",
    " 'EG.USE.PCAP.KG.OE': 'energy_use',\n",
    " 'EN.ATM.CO2E.PC': 'co2_emissions',\n",
    " 'EN.ATM.METH.PC': 'methane_emissions',\n",
    " 'EN.ATM.NOXE.PC': 'nitrous_oxide_emissions',\n",
    " 'EN.ATM.PM25.MC.M3': 'pm2_5_air_pollution',\n",
    " 'EN.CLC.CDDY.XD': 'cooling_degree_days',\n",
    " 'EN.CLC.GHGR.MT.CE': 'ghg_net_emissions',\n",
    " 'EN.CLC.HEAT.XD': 'heat_index_35',\n",
    " 'EN.CLC.MDAT.ZS': 'droughts',\n",
    " 'EN.CLC.PRCP.XD': 'maximum_5-day_rainfall',\n",
    " 'EN.CLC.SPEI.XD': 'mean_drought_index',\n",
    " 'EN.MAM.THRD.NO': 'mammal_species',\n",
    " 'EN.POP.DNST': 'population_density',\n",
    " 'ER.H2O.FWTL.ZS': 'annual_freshwater_withdrawals',\n",
    " 'ER.PTD.TOTL.ZS': 'terrestrial_and_marine_protected_areas',\n",
    " 'GB.XPD.RSDV.GD.ZS': 'research_and_development_expenditure',\n",
    " 'GE.EST': 'government_effectiveness',\n",
    " 'IC.BUS.EASE.XQ': 'ease_of_doing_business_rank',\n",
    " 'IC.LGL.CRED.XQ': 'strength_of_legal_rights_index',\n",
    " 'IP.JRN.ARTC.SC': 'scientific_and_technical_journal_articles',\n",
    " 'IP.PAT.RESD': 'patent_applications',\n",
    " 'IT.NET.USER.ZS': 'individuals_using_the_internet',\n",
    " 'NV.AGR.TOTL.ZS': 'agriculture',\n",
    " 'NY.ADJ.DFOR.GN.ZS': 'net_forest_depletion',\n",
    " 'NY.ADJ.DRES.GN.ZS': 'natural_resources_depletion',\n",
    " 'NY.GDP.MKTP.KD.ZG': 'gdp_growth',\n",
    " 'PV.EST': 'political_stability_and_absence_of_violence',\n",
    " 'RL.EST': 'rule_of_law',\n",
    " 'RQ.EST': 'regulatory_quality',\n",
    " 'SE.ADT.LITR.ZS': 'literacy_rate',\n",
    " 'SE.ENR.PRSC.FM.ZS': 'gross_school_enrollment',\n",
    " 'SE.PRM.ENRR': 'primary_school_enrollment',\n",
    " 'SE.XPD.TOTL.GB.ZS': 'government_expenditure_on_education',\n",
    " 'SG.GEN.PARL.ZS': 'proportion_of_seats_held_by_women_in_national_parliaments',\n",
    " 'SH.DTH.COMM.ZS': 'cause_of_death',\n",
    " 'SH.DYN.MORT': 'mortality_rate',\n",
    " 'SH.H2O.SMDW.ZS': 'people_using_safely_managed_drinking_water_services',\n",
    " 'SH.MED.BEDS.ZS': 'hospital_beds',\n",
    " 'SH.STA.OWAD.ZS': 'prevalence_of_overweight',\n",
    " 'SH.STA.SMSS.ZS': 'people_using_safely_managed_sanitation_services',\n",
    " 'SI.DST.FRST.20': 'income_share_held_by_lowest_20pct',\n",
    " 'SI.POV.GINI': 'gini_index',\n",
    " 'SI.POV.NAHC': 'poverty_headcount_ratio_at_national_poverty_lines',\n",
    " 'SI.SPR.PCAP.ZG': 'annualized_average_growth_rate_in_per_capita_real_survey_mean_consumption_or_income',\n",
    " 'SL.TLF.0714.ZS': 'children_in_employment',\n",
    " 'SL.TLF.ACTI.ZS': 'labor_force_participation_rate',\n",
    " 'SL.TLF.CACT.FM.ZS': 'ratio_of_female_to_male_labor_force_participation_rate',\n",
    " 'SL.UEM.TOTL.ZS': 'unemployment',\n",
    " 'SM.POP.NETM': 'net_migration',\n",
    " 'SN.ITK.DEFC.ZS': 'prevalence_of_undernourishment',\n",
    " 'SP.DYN.LE00.IN': 'life_expectancy_at_birth',\n",
    " 'SP.DYN.TFRT.IN': 'fertility_rate',\n",
    " 'SP.POP.65UP.TO.ZS': 'population_ages_65_and_above',\n",
    " 'SP.UWT.TFRT': 'unmet_need_for_contraception',\n",
    " 'VA.EST': 'voice_and_accountability'}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b06c31a-4d41-432b-99c1-5107673c06af",
   "metadata": {},
   "source": [
    "[2 points]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "681e7e03-5618-4745-af2c-4051ec59b114",
   "metadata": {},
   "source": [
    "f. The World Bank country-time-series dataset is strangely organized. The features are stored in the rows, when typically we would want these features to be columns. Also, years are stored in columns, when typically we would want years to be represented by different rows. We can repair this structure by reshaping the data. Following the discussion in chapter 9 of \"[Surfing the Data Pipeline with Python](https://jkropko.github.io/surfing-the-data-pipeline/ch9.html)\", perform the following reshapes:\n",
    "\n",
    "* First, turn the columns that refer to years into rows,\n",
    "\n",
    "* Then turn the rows that refer to features into columns. [3 points]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09c15064-3c93-42c7-9262-8a360662a117",
   "metadata": {},
   "source": [
    "g. After these reshapes, the year column in the World Bank country-time-series dataset is stored as a string. Convert this column to an integer data type. [1 point]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ddfff0a-a191-4c4b-bb36-d41e4ea40a65",
   "metadata": {},
   "source": [
    "h. Create a new dataframe containing just the rows from the World Bank country-time-series dataset that refer to the whole world. Then remove the \"World\" rows from the World Bank country-time-series dataset. Drop the 'country_code' and 'country_name_wb' from the world dataset. Finally, with the exception of the year column, rename all columns in the world dataset by adding \"world_\" in front of the column name. [2 points]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f081d0e1-26dd-4ad2-9905-2a9e6eac19fb",
   "metadata": {},
   "source": [
    "i. Next we will merge the World Bank country-time-series dataset with the V-Dem dataset, matching on the 'country_code' and 'year' columns. First, write a sentence stating whether you expect this merge to be one-to-one, many-to-one, one-to-many, or many-to-many, and describe your rationale. Second, merge the two datasets together in a way that checks whether your expectation is met, and also allows you to see the rows that failed to match. [3 points]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d0b9e18-e204-43b8-85e6-26dda2cdb67b",
   "metadata": {},
   "source": [
    "j. After this merge, use the `.value_counts()` method to see the total numnber of observations that were found in both datasets, the number found only in the left dataset, and the number found only in the right dataset. (If you entered the World Bank dataset into the merge function first, then \"left_only\" refers to the rows found in the World Bank but not V-Dem, and \"right_only refers to the rows found in V-Dem but not the World Bank.) There should be more than 9000 rows that matched, but more than 2000 that failed to match.\n",
    "\n",
    "Then conduct two data aggregations to help us investigate why these observations did not match:\n",
    "\n",
    "* First use `.query()` to keep only the observations that were present in the World Bank data but not V-Dem. (These are the 'left_only' observations if you typed the World Bank data into the merge function first.) Use `.groupby()` to aggregate the data by 'country_code' and 'country_name_wb'. Then save the minimum and maximum values of 'year' for each country.\n",
    "\n",
    "* Then use `.query()` to keep only the observations that were present in the V-Dem data but not the World Bank. Use `.groupby()` to aggregate the data by 'country_code' and 'country_name_vdem'. Then save the minimum and maximum values of 'year' for each country. [3 points]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2637675c-e4ac-4b0e-93cb-c411b8647ce1",
   "metadata": {},
   "source": [
    "k. Here's where a deep understanding of the data becomes very important. There are two reasons why an observation may fail to match in a merge. One reason is a difference in spelling. Suppose that South Korea (which is also known as the Republic of Korea) is coded as SKO in the World Bank data and ROK in V-Dem. In this case, we should recode one or the other of SKO and ROK so that they match, otherwise we will lose the data on South Korea. But the second reason why observations might fail to match is due to differences in coverage in the data collection strategy: it is possible that a country wasn't included in one data's coverage, or that certain years for that country were not included. For differences in coverage, there's no way to manipulate the data to match, so we are out of luck and we have to either delete these observations or proceed with missing data from one of the data sources.\n",
    "\n",
    "Take a close look at the two data aggregation tables you generated in part (j), and answer the following questions:\n",
    "\n",
    "* Do you see any countries that are present in both the unmatched World Bank rows and the unmatched V-Dem rows, but with different spellings?\n",
    "\n",
    "* Do some digging on Wikipedia and other sources on the Internet. What do you think is the primary reason why some countries are present in the V-Dem data but not the World Bank? (You don't need to describe the reasoning for every country. Just dig until you see a general pattern and describe it here.)\n",
    "\n",
    "* Do some more digging on Wikipedia and other sources on the Internet. What do you think is the primary reason why some countries are present in the World Bank data but not V-Dem? (You don't need to describe the reasoning for every country. Just dig until you see a general pattern and describe it here.) [3 points]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7fa6ff9d-13fe-4d2a-9975-de8a2f2176af",
   "metadata": {},
   "source": [
    "l. Once you are convinced that all of the unmatched observations are due to differences in the coverage of the data collection strategies of the World Bank and V-Dem, repeat the merge, this time dropping all unmatched observations. This time there is no need to validate the type of merge, and no need to define a variable to indicate matching. [1 point]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6202f169-439b-4193-870f-9d1c0bb492f1",
   "metadata": {},
   "source": [
    "(3) After working through all of the data wrangling steps in problem 2, you now have the following data frames:\n",
    "\n",
    "* The World Bank's country level indicators (we'll call this table \"country\")\n",
    "\n",
    "* The World Bank's indicators for the whole world through the 1960-2021 timeframe (we'll call this table \"world\")\n",
    "\n",
    "* The merged data frame containing both the World Bank's and V-Dem's country-time-series data (we'll call this table \"timeseries\")\n",
    "\n",
    "Do these three tables together comprise a third normal form database? Write a paragraph that describes your reasoning, and be clear about whether the criteria or 1st, 2nd, and 3rd normal forms have been achieved. If the data are not in 3rd normal form, take steps to put the data in 3rd normal form. (Hint: it's not important to keep the country names from both the World Bank and V-Dem. If one of these names is stored in one of the tables, that works.) [4 points]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4ced643-4f86-417a-9512-28c633c0774e",
   "metadata": {},
   "source": [
    "(4) Connect to the PostgreSQL server that is running on port 5432. Create a new database called 'cardib'. Then use `sqlalchemy` to create an engine that connects to this database, and upload the country, world, and timeseries tables to this database. (Hint: set 'user' in the `psycopg2.connect()` method to be 'postgres', and set 'host' to be whatever you named the PostgreSQL service in your compose.yaml file. I named this service 'postgres' in my file. In addition, change 'localhost' in the `create_engine()` function to the name of this service as well.) [4 points]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77570c36-786d-4990-a0de-a22132d256b7",
   "metadata": {},
   "source": [
    "(5) Use dbdocs to create an ER diagram for this database. Copy the text of your .dmbl file and paste it in a \"raw\" type cell below. Then copy the URL of the published ER diagram and paste it in a markdown cell.\n",
    "\n",
    "A few points to keep in mind:\n",
    "\n",
    "* In the timeseries table, country_code is varchar, year is int, and every feature is float.\n",
    "\n",
    "* Pay close attention to whether the tables have a one-to-one, many-to-one, or one-to-many relationship with each other, and make sure the ER diagram properly represents that relationship.\n",
    "\n",
    "* It would be good to write a sentence or two describing the sources of the data as a note for the database. [5 points]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2493ec68-26ed-4c32-9fe9-0d3c6c71753b",
   "metadata": {},
   "source": [
    "(6) Write SQL queries for the cardib database that perform the following tasks, and display the output: [3 points each]\n",
    "\n",
    "a. *What countries had the highest quality democracies in the year 2021?* Join the timeseries and country tables, select the country_name_wb and democracy attributes, rename county_name_wb to country, filter the rows to only those for the year 2021, and sort the rows in descending order by the value of democracy."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8247571d-0245-4ad1-aabf-1fb5b71a3d95",
   "metadata": {},
   "source": [
    "b. *How does the life expectancy at birth for Chile compare to the global average life expectancy at birth over the 1960-2021 time span?* Join the timeseries and world tables, select year from the timeseries table and the life expectancy attributes from both the timeseries and world tables, filter the rows to just those where the country code is CHL, and sort the rows by year."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3acfc692-d454-46c9-a050-b1e863bed44e",
   "metadata": {},
   "source": [
    "c. *What regions of the world generated the most carbon dioxide emissions in 2019?* Join the timeseries and country tables, filter the rows to only the ones that refer to 2019, and aggregate the data by region. Keep the region column from the country table and the sum of the co2_emissions attribute from the timeseries table. Rename the sum to co2_emissions. Then sort the rows in descending order by co2_emissions."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c102cc6-57bb-4bc9-944d-d9cce1c1b4b8",
   "metadata": {},
   "source": [
    "d. *What countries experienced the greatest increases in democratic quality over the 1960-2021 time span?* Use subqueries to create two new tables on the fly. One table contains country_code and democracy from the timeseries table in which only the rows from 1960 are present. The second table contains country_code and democracy from the timeseries table in which only the rows from 2021 are present. Join these two tables together, then join the result with the country table. Select the country name from county, democracy from the 1960 table, democracy from the 2021 table, and the difference between the two democracy scores. Give each column a more intuitive name. Then sort the rows in descending order by the democracy difference and display the first 10 rows."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f3c97af-bba9-4da4-a9ae-ae05a1cb5af9",
   "metadata": {},
   "source": [
    "For the next two questions, write a query that answers the given question without additional guidance.\n",
    "\n",
    "e. *By count of countries, what is the most commonly used currency in the world?*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a84b097-0eb7-431c-9c5e-97312606601b",
   "metadata": {},
   "source": [
    "f. *The GINI index measures the amount of economic inequality in a country. The higher the index, the greater the economic disparity between rich and poor. How does the average GINI index compare across income groups (see the country table) in 2019?* "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d463f101-e5a0-4ac2-82d5-8abb1ff2e266",
   "metadata": {},
   "source": [
    "Extra credit: *Some countries include the word 'Republic' or 'Democratic' in their official names. Other countries do not. Which of these two groups of countries had a higher average level of democratic quality in 2021?* (It's OK if you use two queries to provide this answer.) [5 bonus points]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9fe3902d-4586-401a-be58-fea50da68088",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}