kaggle_erd_staging_queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Entity Relationship Diagram (ERD) Staging Queries**\n",
    "The following queries were used to create an ERD of the Global Surface Temperature Data across the five tables. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Primary Key Queries**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Showing dt attribute is a primary key for Global_Temperatures**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Record_Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3192</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Record_Count\n",
       "0          3192"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT COUNT(*) as Record_Count\n",
    "FROM kaggle_staging.Global_Temperatures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dt_Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3192</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   dt_Count\n",
       "0      3192"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery \n",
    "SELECT COUNT(DISTINCT dt) as dt_Count\n",
    "FROM kaggle_staging.Global_Temperatures"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Showing no obvious primary key for Global_Land_Temperatures_by_State**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Record_Count</th>\n",
       "      <th>dt_Count</th>\n",
       "      <th>AvgTemp_Count</th>\n",
       "      <th>AvgTempUncertainty_Count</th>\n",
       "      <th>State_Count</th>\n",
       "      <th>Country_Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>645675</td>\n",
       "      <td>3239</td>\n",
       "      <td>100924</td>\n",
       "      <td>8040</td>\n",
       "      <td>241</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Record_Count  dt_Count  AvgTemp_Count  AvgTempUncertainty_Count  \\\n",
       "0        645675      3239         100924                      8040   \n",
       "\n",
       "   State_Count  Country_Count  \n",
       "0          241              7  "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT  COUNT(*) as Record_Count,\n",
    "COUNT(DISTINCT dt) as dt_Count,\n",
    "COUNT(DISTINCT AverageTemperature) as AvgTemp_Count,\n",
    "COUNT(DISTINCT AverageTemperatureUncertainty) as AvgTempUncertainty_Count,\n",
    "COUNT(DISTINCT State) as State_Count,\n",
    "COUNT(DISTINCT Country) as Country_Count\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_State"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Showing no obvious primary key for Global_Land_Temperatures_by_City**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Record_Count</th>\n",
       "      <th>dt_Count</th>\n",
       "      <th>AvgTemp_Count</th>\n",
       "      <th>AvgTempUncertainty_Count</th>\n",
       "      <th>City_Count</th>\n",
       "      <th>Country_Count</th>\n",
       "      <th>Latitude_Count</th>\n",
       "      <th>Longitude_Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8599212</td>\n",
       "      <td>3239</td>\n",
       "      <td>113163</td>\n",
       "      <td>10902</td>\n",
       "      <td>3448</td>\n",
       "      <td>159</td>\n",
       "      <td>73</td>\n",
       "      <td>1227</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Record_Count  dt_Count  AvgTemp_Count  AvgTempUncertainty_Count  \\\n",
       "0       8599212      3239         113163                     10902   \n",
       "\n",
       "   City_Count  Country_Count  Latitude_Count  Longitude_Count  \n",
       "0        3448            159              73             1227  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT  COUNT(*) as Record_Count,\n",
    "COUNT(DISTINCT dt) as dt_Count,\n",
    "COUNT(DISTINCT AverageTemperature) as AvgTemp_Count,\n",
    "COUNT(DISTINCT AverageTemperatureUncertainty) as AvgTempUncertainty_Count,\n",
    "COUNT(DISTINCT City) as City_Count,\n",
    "COUNT(DISTINCT Country) as Country_Count,\n",
    "COUNT(DISTINCT Latitude) as Latitude_Count,\n",
    "COUNT(DISTINCT Longitude) as Longitude_Count\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_City"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Showing no obvious primary key for Global_Land_Temperatures_by_Country**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Record_Count</th>\n",
       "      <th>dt_Count</th>\n",
       "      <th>AvgTemp_Count</th>\n",
       "      <th>AvgTempUncertainty_Count</th>\n",
       "      <th>Country_Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>577462</td>\n",
       "      <td>3239</td>\n",
       "      <td>77219</td>\n",
       "      <td>8979</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Record_Count  dt_Count  AvgTemp_Count  AvgTempUncertainty_Count  \\\n",
       "0        577462      3239          77219                      8979   \n",
       "\n",
       "   Country_Count  \n",
       "0            243  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT COUNT(*) as Record_Count,\n",
    "COUNT(DISTINCT dt) as dt_Count,\n",
    "COUNT(DISTINCT AverageTemperature) as AvgTemp_Count,\n",
    "COUNT(DISTINCT AverageTemperatureUncertainty) as AvgTempUncertainty_Count,\n",
    "COUNT(DISTINCT Country) as Country_Count\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_Country"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Showing no obvious primary key for Global_Land_Temperatures_by_Major_City**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Record_Count</th>\n",
       "      <th>dt_Count</th>\n",
       "      <th>AvgTemp_Count</th>\n",
       "      <th>AvgTempUncertainty_Count</th>\n",
       "      <th>City_Count</th>\n",
       "      <th>Country_Count</th>\n",
       "      <th>Latitude_Count</th>\n",
       "      <th>Longitude_Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>239177</td>\n",
       "      <td>3239</td>\n",
       "      <td>56694</td>\n",
       "      <td>6269</td>\n",
       "      <td>100</td>\n",
       "      <td>49</td>\n",
       "      <td>49</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Record_Count  dt_Count  AvgTemp_Count  AvgTempUncertainty_Count  \\\n",
       "0        239177      3239          56694                      6269   \n",
       "\n",
       "   City_Count  Country_Count  Latitude_Count  Longitude_Count  \n",
       "0         100             49              49               93  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery \n",
    "SELECT  COUNT(*) as Record_Count,\n",
    "COUNT(DISTINCT dt) as dt_Count,\n",
    "COUNT(DISTINCT AverageTemperature) as AvgTemp_Count,\n",
    "COUNT(DISTINCT AverageTemperatureUncertainty) as AvgTempUncertainty_Count,\n",
    "COUNT(DISTINCT City) as City_Count,\n",
    "COUNT(DISTINCT Country) as Country_Count,\n",
    "COUNT(DISTINCT Latitude) as Latitude_Count,\n",
    "COUNT(DISTINCT Longitude) as Longitude_Count\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_Major_City"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Foreign Key Queries**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Showing dt attribute is not foreign key for Global_Land_Temperatures_by_State to dt of Global_Temperatures**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unmatched_Elements</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5920</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unmatched_Elements\n",
       "0                5920"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery \n",
    "SELECT COUNT(*) as Unmatched_Elements\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_State\n",
    "LEFT JOIN kaggle_staging.Global_Temperatures\n",
    "ON Global_Land_Temperatures_by_State.dt = Global_Temperatures.dt\n",
    "WHERE Global_Temperatures.dt IS NULL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Showing dt attribute is not foreign key for Global_Land_Temperatures_by_City to dt of Global_Temperatures**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unmatched_Elements</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>52244</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unmatched_Elements\n",
       "0               52244"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery \n",
    "SELECT COUNT(*) as Unmatched_Elements\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_City\n",
    "LEFT JOIN kaggle_staging.Global_Temperatures\n",
    "ON Global_Land_Temperatures_by_City.dt = Global_Temperatures.dt\n",
    "WHERE Global_Temperatures.dt IS NULL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Showing dt attribute is not foreign key for Global_Land_Temperatures_by_Country to dt of Global_Temperatures**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unmatched_Elements</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3700</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unmatched_Elements\n",
       "0                3700"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery \n",
    "SELECT COUNT(*) as Unmatched_Elements\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_Country\n",
    "LEFT JOIN kaggle_staging.Global_Temperatures\n",
    "ON Global_Land_Temperatures_by_Country.dt = Global_Temperatures.dt\n",
    "WHERE Global_Temperatures.dt IS NULL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Showing dt attribute is not foreign key for Global_Land_Temperatures_by_Major_City to dt of Global_Temperatures**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unmatched_Elements</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>962</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unmatched_Elements\n",
       "0                 962"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery \n",
    "SELECT COUNT(*) as Unmatched_Elements\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_Major_City\n",
    "LEFT JOIN kaggle_staging.Global_Temperatures\n",
    "ON Global_Land_Temperatures_by_Major_City.dt = Global_Temperatures.dt\n",
    "WHERE Global_Temperatures.dt IS NULL"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}