2019-12-13_intro_to_relational_databases.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Course: **[DataCamp: Introduction to Relational Databases in SQL][2]**\n",
    "- Notebook Author: [Trenton McKinney][1]\n",
    " - This notebook was created as a reproducible reference.\n",
    " - The material is from the course\n",
    " - I completed the exercises\n",
    " - I installed and configured a PostgreSQL database to run all the queries locally.\n",
    " - If you find the content beneficial, consider a [DataCamp Subscription][4].\n",
    "\n",
    "  [1]: https://trenton3983.github.io/\n",
    "  [2]: https://www.datacamp.com/courses/introduction-to-relational-databases-in-sql\n",
    "  [4]: https://www.datacamp.com/join-me/MjQwNzMyMg=="
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Course Description**\n",
    "\n",
    "You’ve already used SQL to query data from databases. But did you know that there's a lot more you can do with databases? You can model different phenomena in your data, as well as the relationships between them. This gives your data structure and consistency, which results in better data quality. In this course, you'll experience this firsthand by working with a real-life dataset that was used to investigate questionable university affiliations. Column by column, table by table, you'll get to unlock and admire the full potential of databases. You'll learn how to create tables and specify their relationships, as well as how to enforce data integrity. You'll also discover other unique features of database systems, such as constraints."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Datasets**\n",
    "\n",
    "* [University Professors][1]\n",
    "\n",
    "\n",
    "  [1]: https://github.com/trenton3983/DataCamp/tree/master/data/2019-12-13_intro_to_relational_databases"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Imports**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy import MetaData\n",
    "from sqlalchemy import Table\n",
    "from sqlalchemy import Column\n",
    "from sqlalchemy import Integer, String\n",
    "from sqlalchemy import inspect\n",
    "from sqlalchemy.types import Text\n",
    "import pandas as pd\n",
    "from pprint import pprint as pp\n",
    "from itertools import combinations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas Configuration Options**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('max_columns', 200)\n",
    "pd.set_option('max_rows', 300)\n",
    "pd.set_option('display.expand_frame_repr', True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PostgreSQL Connection**\n",
    "\n",
    "- In order to run this Notebook, [install][1], setup and configure a PostgreSQL database with the previously mentioned datasets.\n",
    "- Edit `engine` to use your database username and password.\n",
    "\n",
    "  [1]: https://www.postgresql.org/download/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine('postgresql://postgres:postgres@localhost/postgres')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = engine.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create the `university_professors` table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Create an empty table with `conn`**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.execute('CREATE SCHEMA uni;')\n",
    "conn.execute(' \\\n",
    "CREATE TABLE uni.university_professors ( \\\n",
    "  \"firstname\" text, \\\n",
    "  \"lastname\" text, \\\n",
    "  \"university\" text, \\\n",
    "  \"university_shortname\" text, \\\n",
    "  \"university_city\" text, \\\n",
    "  \"function\" text, \\\n",
    "  \"organization\" text, \\\n",
    "  \"organization_sector\" text \\\n",
    ");')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Create the filled table from pandas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uni_prof = pd.read_excel('data/2019-12-13_intro_to_relational_databases/university_professors.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uni_prof.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "_dtypes = {'firstname': Text(), 'lastname': Text(), 'university': Text(), 'university_shortname': Text(),\n",
    "           'university_city': Text(), 'function': Text(), 'organization': Text(), 'organization_sector': Text()}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uni_prof.to_sql('university_professors', con=engine, schema='uni', index=False, if_exists='replace', dtype=_dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": true,
    "toc-nb-collapsed": true
   },
   "source": [
    "# Your first database\n",
    "\n",
    "In this chapter, you'll create your very first database with a set of simple SQL commands. Next, you'll migrate data from existing flat tables into that database. You'll also learn how meta-information about a database can be queried."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction to relational databases"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Attributes of relational databases\n",
    "\n",
    "In the video, we talked about some basic facts about relational databases. Which of the following statements does not hold true for databases? Relational databases ...\n",
    "\n",
    "**Possible Answers**\n",
    "\n",
    "* __**... store different real-world entities in different tables.**__\n",
    "* ~~... allow to establish relationships between entities.~~\n",
    "* ~~... are called \"relational\" because they store data only about people.~~\n",
    "* ~~... use constraints, keys and referential integrity in order to assure data quality.~~"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query information_schema with `SELECT`\n",
    "\n",
    "`information_schema` is a meta-database that holds information about your current database. `information_schema` has multiple tables you can query with the known `SELECT * FROM` syntax:\n",
    "\n",
    "* `tables`: information about all tables in your current database\n",
    "* `columns`: information about all columns in all of the tables in your current database\n",
    "* ...\n",
    "\n",
    "In this exercise, you'll only need information from the `'public'` schema, which is specified as the column `table_schema` of the `tables` and `columns` tables. The `'public'` schema holds information about user-defined tables and databases. The other types of `table_schema` hold system information - for this course, you're only interested in user-defined stuff.\n",
    "\n",
    "**Instructions 1/4**\n",
    "\n",
    "* Get information on all table names in the current database, while limiting your query to the `'public'` `table_schema`.\n",
    "\n",
    "```sql\n",
    "-- Query the right table in information_schema\n",
    "SELECT table_name \n",
    "FROM information_schema.tables\n",
    "-- Specify the correct table_schema value\n",
    "WHERE table_schema = 'public';\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "SELECT table_name \\\n",
    "FROM information_schema.tables \\\n",
    "WHERE table_schema = 'uni'; \\\n",
    "\"\n",
    "res = conn.execute(stmt)\n",
    "res.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 2/4**\n",
    "\n",
    "* Now have a look at the columns in `university_professors` by selecting all entries in `information_schema.columns` that correspond to that table.\n",
    "\n",
    "```sql\n",
    "-- Query the right table in information_schema to get columns\n",
    "SELECT column_name, data_type \n",
    "FROM information_schema.columns \n",
    "WHERE table_name = 'university_professors' AND table_schema = 'public';\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "SELECT column_name, data_type \\\n",
    "FROM information_schema.columns \\\n",
    "WHERE table_name = 'university_professors' AND table_schema = 'uni'; \\\n",
    "\"\n",
    "res = conn.execute(stmt)\n",
    "res.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 3/4**\n",
    "\n",
    "How many columns does the table university_professors have?\n",
    "\n",
    "**Possible Answers**\n",
    "\n",
    "* ~~12~~\n",
    "* ~~9~~\n",
    "* __**8**__\n",
    "* ~~5~~"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 4/4**\n",
    "\n",
    "* Finally, print the first five rows of the `university_professors` table.\n",
    "\n",
    "```sql\n",
    "-- Query the first five rows of our table\n",
    "SELECT * \n",
    "FROM university_professors \n",
    "LIMIT 5;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "SELECT * \\\n",
    "FROM uni.university_professors \\\n",
    "LIMIT 5; \\\n",
    "\"\n",
    "\n",
    "pd.read_sql(stmt, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": false
   },
   "source": [
    "## Tables: At the core of every database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `CREATE` your first few `TABLE`s\n",
    "\n",
    "You'll now start implementing a better database model. For this, you'll create tables for the `professors` and `universities` entity types. The other tables will be created for you.\n",
    "\n",
    "The syntax for creating simple tables is as follows:\n",
    "\n",
    "```sql\n",
    "CREATE TABLE table_name (\n",
    " column_a data_type,\n",
    " column_b data_type,\n",
    " column_c data_type\n",
    ");\n",
    "```\n",
    "\n",
    "*Attention*: Table and columns names, as well as data types, don't need to be surrounded by quotation marks.\n",
    "\n",
    "**Instructions 1/2**\n",
    "\n",
    "* Create a table `professors` with two `text` columns: `firstname` and `lastname`.\n",
    "\n",
    "```sql\n",
    "-- Create a table for the professors entity type\n",
    "CREATE TABLE professors (\n",
    " firstname text,\n",
    " lastname text\n",
    ");\n",
    "\n",
    "-- Print the contents of this table\n",
    "SELECT * \n",
    "FROM professors\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "CREATE TABLE uni.professors ( \\\n",
    " firstname text, \\\n",
    " lastname text \\\n",
    "); \\\n",
    "\"\n",
    "conn.execute(stmt)\n",
    "\n",
    "stmt = \"\\\n",
    "SELECT *  \\\n",
    "FROM uni.professors \\\n",
    "\"\n",
    "\n",
    "pd.read_sql(stmt, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 2/2**\n",
    "\n",
    "* Create a table `universities` with three text columns: `university_shortname`, `university`, and `university_city`.\n",
    "\n",
    "```sql\n",
    "-- Create a table for the universities entity type\n",
    "CREATE TABLE universities (\n",
    "university_shortname text,\n",
    "university text,\n",
    "university_city text);\n",
    "\n",
    "-- Print the contents of this table\n",
    "SELECT * \n",
    "FROM universities\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "CREATE TABLE uni.universities ( \\\n",
    "university_shortname text, \\\n",
    "university text, \\\n",
    "university_city text); \\\n",
    "\"\n",
    "conn.execute(stmt)\n",
    "\n",
    "stmt = \"\\\n",
    "SELECT *  \\\n",
    "FROM uni.universities \\\n",
    "\"\n",
    "\n",
    "pd.read_sql(stmt, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `ADD` a `COLUMN` with `ALTER TABLE`\n",
    "\n",
    "We forgot to add the `university_shortname` column to the `professors` table. You've probably already noticed:\n",
    "\n",
    "In chapter 4 of this course, you'll need this column for connecting the `professors` table with the `universities` table.\n",
    "\n",
    "However, adding columns to existing tables is easy, especially if they're still empty.\n",
    "\n",
    "To add columns you can use the following SQL query:\n",
    "\n",
    "```sql\n",
    "ALTER TABLE table_name\n",
    "ADD COLUMN column_name data_type;\n",
    "```\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Alter professors to add the text column university_shortname.\n",
    "\n",
    "```sql\n",
    "-- Add the university_shortname column\n",
    "ALTER TABLE professors\n",
    "ADD COLUMN university_shortname text;\n",
    "\n",
    "-- Print the contents of this table\n",
    "SELECT * \n",
    "FROM professors\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.professors \\\n",
    "ADD COLUMN university_shortname text; \\\n",
    "\"\n",
    "conn.execute(stmt)\n",
    "\n",
    "stmt = \"\\\n",
    "SELECT *  \\\n",
    "FROM uni.professors \\\n",
    "\"\n",
    "\n",
    "pd.read_sql(stmt, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Update your database as the structure changes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Create the `affiliations` table**\n",
    "\n",
    "```sql\n",
    "CREATE TABLE affiliations (\n",
    " firstname text,\n",
    " lastname text,\n",
    " university_shortname text,\n",
    " function text,\n",
    " organisation text\n",
    ");\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "CREATE TABLE uni.affiliations ( \\\n",
    " firstname text, \\\n",
    " lastname text, \\\n",
    " university_shortname text, \\\n",
    " function text, \\\n",
    " organisation text \\\n",
    "); \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Create the `organizations` table**\n",
    "\n",
    "```sql\n",
    "CREATE TABLE organizations (\n",
    " organization text,\n",
    " organization_sector text\n",
    ");\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "CREATE TABLE uni.organizations ( \\\n",
    " organization text, \\\n",
    " organization_sector text \\\n",
    "); \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `RENAME` and `DROP COLUMN`s in affiliations\n",
    "\n",
    "As mentioned in the video, the still empty affiliations table has some flaws. In this exercise, you'll correct them as outlined in the video.\n",
    "\n",
    "You'll use the following queries:\n",
    "\n",
    "To rename columns:\n",
    "```sql\n",
    "ALTER TABLE table_name\n",
    "RENAME COLUMN old_name TO new_name;\n",
    "```\n",
    "To delete columns:\n",
    "```sql\n",
    "ALTER TABLE table_name\n",
    "DROP COLUMN column_name;\n",
    "```\n",
    "\n",
    "**Instructions 1/2**\n",
    "\n",
    "* Rename the `organisation` column to `organization` in `affiliations`.\n",
    "\n",
    "```sql\n",
    "-- Rename the organisation column\n",
    "ALTER TABLE affiliations\n",
    "RENAME COLUMN organisation TO organization;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.affiliations \\\n",
    "RENAME COLUMN organisation TO organization; \\\n",
    "\"\n",
    "conn.execute(stmt)\n",
    "\n",
    "stmt = \"\\\n",
    "SELECT *  \\\n",
    "FROM uni.affiliations \\\n",
    "\"\n",
    "\n",
    "pd.read_sql(stmt, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 2/2**\n",
    "\n",
    "* Delete the `university_shortname` column in `affiliations`.\n",
    "\n",
    "```sql\n",
    "-- Delete the university_shortname column\n",
    "ALTER TABLE affiliations\n",
    "DROP COLUMN university_shortname;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.affiliations \\\n",
    "DROP COLUMN university_shortname; \\\n",
    "\"\n",
    "conn.execute(stmt)\n",
    "\n",
    "stmt = \"\\\n",
    "SELECT *  \\\n",
    "FROM uni.affiliations \\\n",
    "\"\n",
    "\n",
    "pd.read_sql(stmt, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Migrate date with `INSERT INTO SELECT DISTINCT`\n",
    "\n",
    "Now it's finally time to migrate the data into the new tables. You'll use the following pattern:\n",
    "```sql\n",
    "INSERT INTO ... \n",
    "SELECT DISTINCT ... \n",
    "FROM ...;\n",
    "```\n",
    "It can be broken up into two parts:\n",
    "\n",
    "*First* part:\n",
    "```sql\n",
    "SELECT DISTINCT column_name1, column_name2, ... \n",
    "FROM table_a;\n",
    "```\n",
    "This selects all distinct values in table `table_a` - nothing new for you.\n",
    "\n",
    "*Second* part:\n",
    "```sql\n",
    "INSERT INTO table_b ...;\n",
    "```\n",
    "Take this part and append it to the first, so it inserts all distinct rows from `table_a` into `table_b`.\n",
    "\n",
    "**One last thing**: It is important that you run all of the code at the same time once you have filled out the blanks.\n",
    "\n",
    "**Instructions 1/2**\n",
    "\n",
    "* Insert all `DISTINCT` professors from `university_professors` into `professors`.\n",
    "* Print all the rows in `professors`.\n",
    "\n",
    "```sql\n",
    "-- Insert unique professors into the new table\n",
    "INSERT INTO professors \n",
    "SELECT DISTINCT firstname, lastname, university_shortname \n",
    "FROM university_professors;\n",
    "\n",
    "-- Doublecheck the contents of professors\n",
    "SELECT * \n",
    "FROM professors;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "INSERT INTO uni.professors \\\n",
    "SELECT DISTINCT firstname, lastname, university_shortname \\\n",
    "FROM uni.university_professors; \\\n",
    "\"\n",
    "conn.execute(stmt)\n",
    "\n",
    "stmt = \"\\\n",
    "SELECT *  \\\n",
    "FROM uni.professors \\\n",
    "\"\n",
    "\n",
    "pd.read_sql(stmt, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 2/2**\n",
    "\n",
    "* Insert all `DISTINCT` affiliations into `affiliations` from `university_professors`.\n",
    "\n",
    "```sql\n",
    "-- Insert unique affiliations into the new table\n",
    "INSERT INTO affiliations \n",
    "SELECT DISTINCT firstname, lastname, function, organization \n",
    "FROM university_professors;\n",
    "\n",
    "-- Doublecheck the contents of affiliations\n",
    "SELECT * \n",
    "FROM affiliations;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "INSERT INTO uni.affiliations \\\n",
    "SELECT DISTINCT firstname, lastname, function, organization \\\n",
    "FROM uni.university_professors; \\\n",
    "\"\n",
    "conn.execute(stmt)\n",
    "\n",
    "stmt = \"\\\n",
    "SELECT *  \\\n",
    "FROM uni.affiliations \\\n",
    "\"\n",
    "\n",
    "pd.read_sql(stmt, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Migrate data to the `universities` and `organizations` tables***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "INSERT INTO uni.universities \\\n",
    "SELECT DISTINCT university_shortname, university, university_city \\\n",
    "FROM uni.university_professors; \\\n",
    "\"\n",
    "conn.execute(stmt)\n",
    "\n",
    "stmt = \"\\\n",
    "SELECT *  \\\n",
    "FROM uni.universities \\\n",
    "\"\n",
    "\n",
    "pd.read_sql(stmt, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "INSERT INTO uni.organizations \\\n",
    "SELECT DISTINCT organization, organization_sector \\\n",
    "FROM uni.university_professors; \\\n",
    "\"\n",
    "conn.execute(stmt)\n",
    "\n",
    "stmt = \"\\\n",
    "SELECT *  \\\n",
    "FROM uni.organizations \\\n",
    "\"\n",
    "\n",
    "pd.read_sql(stmt, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Delete tables with `DROP TABLE`\n",
    "\n",
    "The `university_professors` table is now no longer needed and can safely be deleted.\n",
    "\n",
    "For table deletion, you can use the simple command:\n",
    "```sql\n",
    "DROP TABLE table_name;\n",
    "```\n",
    "**Instructions**\n",
    "\n",
    "* Delete the university_professors table.\n",
    "\n",
    "```sql\n",
    "-- Delete the university_professors table\n",
    "DROP TABLE university_professors;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": true,
    "toc-nb-collapsed": true
   },
   "source": [
    "# Enforce data consistency with attribute constraints\n",
    "\n",
    "After building a simple database, it's now time to make use of the features. You'll specify data types in columns, enforce column uniqueness, and disallow NULL values in this chapter."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": true
   },
   "source": [
    "## Better data quality with constraints\n",
    "\n",
    "- The idea of a database is to push data into a certain structure - a predefined model, where you enforce data types, relationships and other rules.\n",
    " - These rules are called integrity constraints, although different names exist.\n",
    "\n",
    "**Integrity Constrains**\n",
    "\n",
    "1. **Attribute constraints**: data types on columns (Ch 2)\n",
    " * A certain attribute, represented through a database column, could have the integer data type, allowing only integers to be stored in the column.\n",
    "1. **Key constraints**: primary keys (Ch 3)\n",
    " * Primary keys, for example, uniquely identify each record, or row, of a database table.\n",
    "1. **Referential integrity constraints**: enforced through foreign keys (Ch 4)\n",
    " * They glue different database tables together.\n",
    "\n",
    "**Why should you know about constraints?**\n",
    "\n",
    "* Constraints give the data structure\n",
    " * With good constraints in place, people who type in birthdates, for example, have to always enter them in the same form.\n",
    " * Data entered by humans is often very tedious to pre-process.\n",
    "* Constraints help with consistency, and thus improve data quality.\n",
    "* Data quality is a business advantage / data science prerequisite.\n",
    "* Enforcing constraints on human-entered data is difficult and tedious, but PostgreSQL helps.\n",
    "\n",
    "**Data types as attribute constaints**\n",
    "\n",
    "| Name                                    | Aliases            | Description                                        |\n",
    "|-----------------------------------------|--------------------|----------------------------------------------------|\n",
    "| bigint                                  | int8               | signed eight-byte integer                          |\n",
    "| bigserial                               | serial8            | autoincrementing eight-byte integer                |\n",
    "| bit [ (n) ]                             |                    | fixed-length bit string                            |\n",
    "| bit varying [ (n) ]                     | varbit [ (n) ]     | variable-length bit string                         |\n",
    "| boolean                                 | bool               | logical Boolean (true/false)                       |\n",
    "| box                                     |                    | rectangular box on a plane                         |\n",
    "| bytea                                   |                    | binary data (“byte array”)                         |\n",
    "| character [ (n) ]                       | char [ (n) ]       | fixed-length character string                      |\n",
    "| character varying [ (n) ]               | varchar [ (n) ]    | variable-length character string                   |\n",
    "| cidr                                    |                    | IPv4 or IPv6 network address                       |\n",
    "| circle                                  |                    | circle on a plane                                  |\n",
    "| date                                    |                    | calendar date (year, month, day)                   |\n",
    "| double precision                        | float8             | double precision floating-point number (8 bytes)   |\n",
    "| inet                                    |                    | IPv4 or IPv6 host address                          |\n",
    "| integer                                 | int, int4          | signed four-byte integer                           |\n",
    "| interval [ fields ] [ (p) ]             |                    | time span                                          |\n",
    "| json                                    |                    | textual JSON data                                  |\n",
    "| jsonb                                   |                    | binary JSON data, decomposed                       |\n",
    "| line                                    |                    | infinite line on a plane                           |\n",
    "| lseg                                    |                    | line segment on a plane                            |\n",
    "| macaddr                                 |                    | MAC (Media Access Control) address                 |\n",
    "| macaddr8                                |                    | MAC (Media Access Control) address (EUI-64 format) |\n",
    "| money                                   |                    | currency amount                                    |\n",
    "| numeric [ (p, s) ]                      | decimal [ (p, s) ] | exact numeric of selectable precision              |\n",
    "| path                                    |                    | geometric path on a plane                          |\n",
    "| pg_lsn                                  |                    | PostgreSQL Log Sequence Number                     |\n",
    "| point                                   |                    | geometric point on a plane                         |\n",
    "| polygon                                 |                    | closed geometric path on a plane                   |\n",
    "| real                                    | float4             | single precision floating-point number (4 bytes)   |\n",
    "| smallint                                | int2               | signed two-byte integer                            |\n",
    "| smallserial                             | serial2            | autoincrementing two-byte integer                  |\n",
    "| serial                                  | serial4            | autoincrementing four-byte integer                 |\n",
    "| text                                    |                    | variable-length character string                   |\n",
    "| time [ (p) ] [ without time zone ]      |                    | time of day (no time zone)                         |\n",
    "| time [ (p) ] with time zone             | timetz             | time of day, including time zone                   |\n",
    "| timestamp [ (p) ] [ without time zone ] |                    | date and time (no time zone)                       |\n",
    "| timestamp [ (p) ] with time zone        | timestamptz        | date and time, including time zone                 |\n",
    "| tsquery                                 |                    | text search query                                  |\n",
    "| tsvector                                |                    | text search document                               |\n",
    "| txid_snapshot                           |                    | user-level transaction ID snapshot                 |\n",
    "| uuid                                    |                    | universally unique identifier                      |\n",
    "| xml                                     |                    | XML data                                           |\n",
    "\n",
    "* From [PostgreSQL documentation][1]\n",
    "* In its simplest form, attribute constraints are data types that can be specified for each column of a table.\n",
    "\n",
    "**Dealing with data types (casting)\n",
    "\n",
    "* Data types also restrict possible SQL operations on the stored data.\n",
    "```sql\n",
    "CREATE TABLE weather (\n",
    " temperature integer,\n",
    " wind_speed text);\n",
    " \n",
    "SELECT temperature * wind_speed AS wind_chill\n",
    "FROM weather;\n",
    "\n",
    "operator does not exist: integer * text\n",
    "HINT: No operator matches the given name and argument type(s). \n",
    "You might need to add explicit type casts.\n",
    "\n",
    "SELECT temperature * CAST(wind_speed AS integer) AS wind_chill\n",
    "FROM weather;\n",
    "```\n",
    "* For example, it's impossible to calculate a product from an integer **and** a text column, as shown here in the example.\n",
    " * The text column `wind_speed` may store numbers, but PostgreSQL doesn't know how  to use text in a calculation.\n",
    " * The solution for this is type casts, that is, on-the-fly type conversions.\n",
    " * In this case, you can use the `CAST` function, followed by the column name, the `AS` keyword, and the desired data type.  PostgreSQL will run `wind_speed` into an integer before the calculation.\n",
    "\n",
    "  [1]: https://www.postgresql.org/docs/10/datatype.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Types of database constraints\n",
    "\n",
    "Which of the following **is not used** to enforce a database constraint?\n",
    "\n",
    "**Possible Answers**\n",
    "\n",
    "* ~~Foreign keys~~\n",
    "* __**SQL aggregate functions**__\n",
    "* ~~The BIGINT data type~~\n",
    "* ~~Primary keys~~"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Conforming with data types\n",
    "\n",
    "For demonstration purposes, I created a fictional database table that only holds three records. The columns have the data types `date`, `integer`, and `text`, respectively.\n",
    "```sql\n",
    "CREATE TABLE transactions (\n",
    " transaction_date date, \n",
    " amount integer,\n",
    " fee text\n",
    ");\n",
    "```\n",
    "\n",
    "| transaction_date | amount | fee |\n",
    "|------------------|--------|-----|\n",
    "| 1999-01-08       | 500    | 20  |\n",
    "| 2001-02-20       | 403    | 15  |\n",
    "| 2001-03-20       | 3430   | 35  |\n",
    "| 2018-09-24       | 5454   | 30  |\n",
    "| 1999-01-08       | 500    | 20  |\n",
    "| 2001-02-20       | 403    | 15  |\n",
    "| 2001-03-20       | 3430   | 35  |\n",
    "| 2018-09-24       | 5454   | 30  |\n",
    "| 1999-01-08       | 500    | 20  |\n",
    "| 2001-02-20       | 403    | 15  |\n",
    "| 2001-03-20       | 3430   | 35  |\n",
    "| 1999-01-08       | 500    | 20  |\n",
    "| 2001-02-20       | 403    | 15  |\n",
    "| 2001-03-20       | 3430   | 35  |\n",
    "\n",
    "Have a look at the contents of the `transactions` table.\n",
    "\n",
    "The `transaction_date` accepts `date` values. According to [the PostgreSQL documentation][1], it accepts values in the form of `YYYY-MM-DD`, `DD/MM/YY`, and so forth.\n",
    "\n",
    "Both columns `amount` and `fee` appear to be numeric, however, the latter is modeled as `text` - which you will account for in the next exercise.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Execute the given sample code.\n",
    "* As it doesn't work, have a look at the error message and correct the statement accordingly - then execute it again.\n",
    "\n",
    "```sql\n",
    "-- Let's add a record to the table\n",
    "INSERT INTO transactions (transaction_date, amount, fee) \n",
    "VALUES ('2018-24-09', 5454, '30');\n",
    "\n",
    "-- Doublecheck the contents\n",
    "SELECT *\n",
    "FROM transactions;\n",
    "\n",
    "date/time field value out of range: \"2018-24-09\"\n",
    "LINE 3: VALUES ('2018-24-09', 5454, '30');\n",
    "                ^\n",
    "HINT:  Perhaps you need a different \"datestyle\" setting.\n",
    "```\n",
    "\n",
    "\n",
    "  [1]: https://www.postgresql.org/docs/10/datatype-datetime.html#DATATYPE-DATETIME-INPUT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```sql\n",
    "-- Let's add a record to the table\n",
    "INSERT INTO transactions (transaction_date, amount, fee) \n",
    "VALUES ('2018-09-24', 5454, '30');\n",
    "\n",
    "-- Doublecheck the contents\n",
    "SELECT *\n",
    "FROM transactions;\n",
    "```\n",
    "\n",
    "| transaction_date | amount | fee |\n",
    "|------------------|--------|-----|\n",
    "| 1999-01-08       | 500    | 20  |\n",
    "| 2001-02-20       | 403    | 15  |\n",
    "| 2001-03-20       | 3430   | 35  |\n",
    "| 2018-09-24       | 5454   | 30  |\n",
    "| 1999-01-08       | 500    | 20  |\n",
    "| 2001-02-20       | 403    | 15  |\n",
    "| 2001-03-20       | 3430   | 35  |\n",
    "| 2018-09-24       | 5454   | 30  |\n",
    "| 1999-01-08       | 500    | 20  |\n",
    "| 2001-02-20       | 403    | 15  |\n",
    "| 2001-03-20       | 3430   | 35  |\n",
    "| 2018-09-24       | 5454   | 30  |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Type `CAST`s\n",
    "\n",
    "In the video, you saw that type casts are a possible solution for data type issues. If you know that a certain column stores numbers as `text`, you can cast the column to a numeric form, i.e. to `integer`.\n",
    "\n",
    "```sql\n",
    "SELECT CAST(some_column AS integer)\n",
    "FROM table;\n",
    "```\n",
    "\n",
    "Now, the `some_column` column is temporarily represented as `integer` instead of `text`, meaning that you can perform numeric calculations on the column.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Execute the given sample code.\n",
    "* As it doesn't work, add an `integer` type cast at the right place and execute it again.\n",
    "\n",
    "```sql\n",
    "-- Calculate the net amount as amount + fee\n",
    "SELECT transaction_date, amount + fee AS net_amount \n",
    "FROM transactions;\n",
    "\n",
    "operator does not exist: integer + text\n",
    "LINE 2: SELECT transaction_date, amount + fee AS net_amount \n",
    "                                        ^\n",
    "HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```sql\n",
    "-- Calculate the net amount as amount + fee\n",
    "SELECT transaction_date, amount + CAST(fee AS integer) AS net_amount \n",
    "FROM transactions;\n",
    "```\n",
    "\n",
    "| transaction_date | net_amount |\n",
    "|------------------|------------|\n",
    "| 1999-01-08       | 520        |\n",
    "| 2001-02-20       | 418        |\n",
    "| 2001-03-20       | 3465       |\n",
    "| 2018-09-24       | 5484       |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Working with data types\n",
    "\n",
    "* Enforced on columns (i.e. attributes)\n",
    "* Data types define the so-called **domain** of values in a column (what form the values can and cannot take).\n",
    "* Define what operations are possible.\n",
    "* Enforce consistent storage of values\n",
    "\n",
    "**THe most common data types in PostgreSQL**\n",
    "\n",
    "* text: character strings of any length\n",
    "* varchar \\[ (x) \\]: a maximum of n characters\n",
    "* char \\[ (x) \\]: a fixed-length string of n characters\n",
    "* boolean: can only take three states, e.g. TRUE, FALSE and NULL (unknown) or \"1\" or \"0\"\n",
    "* date, time and timestamp: various formats for date and time calculations\n",
    "* numeric: arbitrary precision numbers, e.g. 3.1457\n",
    "* integer: whole numbers in the range of -2147483648 and +2147483647\n",
    " * If this range isn't large enough, there is `bigint` for larger numbers\n",
    "\n",
    "**Specifying type upon table creation**\n",
    "\n",
    "```sql\n",
    "CREATE TABLE students (\n",
    " ssn integer, \n",
    " name varchar(64), \n",
    " dob date, \n",
    " average_grade numeric(3, 2), -- e.g. 5.54\n",
    " tuition_paid boolean \n",
    ");\n",
    "```\n",
    "\n",
    "**Alter types after table creation**\n",
    "\n",
    "```sql\n",
    "ALTER TABLE students\n",
    "ALTER COLUMN name \n",
    "TYPE varchar(128);\n",
    "```\n",
    "\n",
    "* Sometimes it may be necessary to truncate column values or transform them in any other way, so they fit the new data type.\n",
    "* Then the `USING` keyword can be used to specify a transformation that should happen before the type is altered.\n",
    "\n",
    "```sql\n",
    "ALTER TABLE students\n",
    "ALTER COLUMN average_grade\n",
    "TYPE integer\n",
    "-- Turns 5.54 into 6, not 5, before type conversion\n",
    "USING ROUND(average_grade);\n",
    "```\n",
    "* Normally, PostgreSQL would only keep the part of the number before the fractional point.  With `USING`, have the number rounded to the nearest integer, for example."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Change types with `ALTER COLUMN`\n",
    "\n",
    "The syntax for changing the data type of a column is straightforward. The following code changes the data type of the `column_name` column in `table_name` to `varchar(10)`:\n",
    "```sql\n",
    "ALTER TABLE table_name\n",
    "ALTER COLUMN column_name\n",
    "TYPE varchar(10)\n",
    "```\n",
    "Now it's time to start adding constraints to your database.\n",
    "\n",
    "**Instructions 1/3**\n",
    "\n",
    "* Have a look at the distinct `university_shortname` values in the `professors` table and take note of the length of the strings.\n",
    "\n",
    "```sql\n",
    "-- Select the university_shortname column\n",
    "SELECT distinct(university_shortname) \n",
    "FROM professors;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "SELECT distinct(university_shortname) \\\n",
    "FROM uni.professors; \\\n",
    "\"\n",
    "\n",
    "pd.read_sql(stmt, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instruction 2/3**\n",
    "\n",
    "* Now specify a fixed-length character type with the correct length for `university_shortname`.\n",
    "\n",
    "```sql\n",
    "-- Specify the correct fixed-length character type\n",
    "ALTER TABLE professors\n",
    "ALTER COLUMN university_shortname\n",
    "TYPE char(3);\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.professors \\\n",
    "ALTER COLUMN university_shortname \\\n",
    "TYPE varchar(3); \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 3/3**\n",
    "\n",
    "* Change the type of the `firstname` column to `varchar(64)`.\n",
    "\n",
    "```sql\n",
    "-- Change the type of firstname\n",
    "ALTER TABLE professors\n",
    "ALTER COLUMN firstname\n",
    "TYPE varchar(64);\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.professors \\\n",
    "ALTER COLUMN firstname \\\n",
    "TYPE varchar(64); \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert types USING a function\n",
    "\n",
    "If you don't want to reserve too much space for a certain `varchar` column, you can truncate the values before converting its type.\n",
    "\n",
    "For this, you can use the following syntax:\n",
    "```sql\n",
    "ALTER TABLE table_name\n",
    "ALTER COLUMN column_name\n",
    "TYPE varchar(x)\n",
    "USING SUBSTRING(column_name FROM 1 FOR x)\n",
    "```\n",
    "You should read it like this: Because you want to reserve only `x` characters for `column_name`, you have to retain a `SUBSTRING` of every value, i.e. the first `x` characters of it, and throw away the rest. This way, the values will fit the `varchar(x)` requirement.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Run the sample code as is and take note of the error.\n",
    "\n",
    "```sql\n",
    "-- Convert the values in firstname to a max. of 16 characters\n",
    "ALTER TABLE professors \n",
    "ALTER COLUMN firstname \n",
    "TYPE varchar(16)\n",
    "\n",
    "value too long for type character varying(16)\n",
    "```\n",
    "\n",
    "* Now use `SUBSTRING()` to reduce `firstname` to 16 characters so its type can be altered to `varchar(16)`.\n",
    "\n",
    "```sql\n",
    "-- Convert the values in firstname to a max. of 16 characters\n",
    "ALTER TABLE professors \n",
    "ALTER COLUMN firstname \n",
    "TYPE varchar(16)\n",
    "USING SUBSTRING(firstname FROM 1 FOR 16)\n",
    "```\n",
    "\n",
    "* Perfect! However, it's best not to truncate any values in your database, so we'll revert this column to `varchar(64)`. Now it's time to move on to the next set of attribute constraints!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The not-null and unique constraints\n",
    "\n",
    "### `not null` constraint\n",
    "\n",
    "* The not-null constraint disallows `NULL` values in a column.\n",
    " * Applies to the current and future state of the the database\n",
    "* not-null can only be specified on a column that doesn't hold any `NULL` values\n",
    "* It will not be possible to insert `NULL` values in the future\n",
    "* What does `NULL` mean\n",
    " * the value is unknown\n",
    " * the value doesn't exist\n",
    " * the value does not apply to the column\n",
    "\n",
    "```sql\n",
    "CREATE TABLE students (\n",
    " ssn integer not null,\n",
    " lastname varchar(64) not null,\n",
    " home_phone integer,\n",
    " office_phone integer\n",
    ");\n",
    "```\n",
    "\n",
    " * An important take away is that two `NULL` values must not have the same meaning\n",
    " * This also means that comparing `NULL` with `NULL` always results in a `FALSE` value\n",
    "* `not null` constraints can be added to or removed from existing tables\n",
    "\n",
    "**Add not null to an existing table**\n",
    "```sql\n",
    "ALTER TABLE students \n",
    "ALTER COLUMN home_phone \n",
    "SET NOT NULL;\n",
    "```\n",
    "\n",
    "**Drop not null from an existing table**\n",
    "```sql\n",
    "ALTER TABLE students \n",
    "ALTER COLUMN ssn \n",
    "DROP NOT NULL;\n",
    "```\n",
    "\n",
    "### `unique` constraint\n",
    "\n",
    "* Disallows duplicate values in a column\n",
    "* Must hold true for the current state\n",
    "* Must hold true for any future state\n",
    "\n",
    "```sql\n",
    "CREATE TABLE table_name (\n",
    " column_name UNIQUE\n",
    ");\n",
    "```\n",
    "\n",
    "**Alter an existing table**\n",
    "\n",
    "```sql\n",
    "ALTER TABLE table_name\n",
    "ADD CONSTRAINT some_name UNIQUE(column_name);\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Disallow `NULL` values with `SET NOT NULL`\n",
    "\n",
    "The `professors` table is almost ready now. However, it still allows for `NULL`s to be entered. Although some information might be missing about some professors, there's certainly columns that always need to be specified.\n",
    "\n",
    "**Instructions 1/2**\n",
    "\n",
    "* Add a not-null constraint for the `firstname` column.\n",
    "\n",
    "```sql\n",
    "-- Disallow NULL values in firstname\n",
    "ALTER TABLE professors \n",
    "ALTER COLUMN firstname SET NOT NULL;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.professors \\\n",
    "ALTER COLUMN firstname SET NOT NULL; \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 2/2**\n",
    "\n",
    "* Add a not-null constraint for the `lastname` column.\n",
    "\n",
    "```sql\n",
    "-- Disallow NULL values in lastname\n",
    "ALTER TABLE professors \n",
    "ALTER COLUMN lastname SET NOT NULL;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.professors \\\n",
    "ALTER COLUMN lastname SET NOT NULL; \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Good job - it is no longer possible to add professors which have either their first or last name set to `NULL`. Likewise, it is no longer possible to update an existing professor and setting their first or last name to `NULL`.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What happens if you try to enter NULLs?\n",
    "\n",
    "Execute the following statement:\n",
    "```sql\n",
    "INSERT INTO professors (firstname, lastname, university_shortname)\n",
    "VALUES (NULL, 'Miller', 'ETH');\n",
    "```\n",
    "Why does this throw an error?\n",
    "\n",
    "*** Possible Answers*** \n",
    "\n",
    "* ~~Professors without first names do not exist.~~\n",
    "* __**Because a database constraint is violated.**__\n",
    "* ~~Error? This works just fine.~~\n",
    "* ~~`NULL` is not put in quotes.~~"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Make your columns UNIQUE with ADD CONSTRAINT\n",
    "\n",
    "As seen in the video, you add the `UNIQUE` keyword after the `column_name` that should be unique. This, of course, only works for new tables:\n",
    "```sql\n",
    "CREATE TABLE table_name (\n",
    " column_name UNIQUE\n",
    ");\n",
    "```\n",
    "If you want to add a unique constraint to an existing table, you do it like that:\n",
    "```sql\n",
    "ALTER TABLE table_name\n",
    "ADD CONSTRAINT some_name UNIQUE(column_name);\n",
    "```\n",
    "Note that this is different from the `ALTER COLUMN` syntax for the not-null constraint. Also, you have to give the constraint a name `some_name`.\n",
    "\n",
    "**Instructions 1/2**\n",
    "\n",
    "* Add a unique constraint to the `university_shortname` column in `universities`. Give it the name `university_shortname_unq`.\n",
    "\n",
    "```sql\n",
    "-- Make universities.university_shortname unique\n",
    "ALTER TABLE universities\n",
    "ADD CONSTRAINT university_shortname_unq UNIQUE(university_shortname);\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.universities \\\n",
    "ADD CONSTRAINT university_shortname_unq UNIQUE(university_shortname); \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 2/2**\n",
    "\n",
    "* Add a unique constraint to the `organization` column in `organizations`. Give it the name `organization_unq`.\n",
    "\n",
    "```sql\n",
    "-- Make organizations.organization unique\n",
    "ALTER TABLE organizations\n",
    "ADD CONSTRAINT organization_unq UNIQUE(organization);\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.organizations \\\n",
    "ADD CONSTRAINT organization_unq UNIQUE(organization); \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Perfect. Making sure `universities.university_shortname` and `organizations.organization` only contain unique values is a prerequisite for turning them into so-called primary keys – the subject of the next chapter!**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": true,
    "toc-nb-collapsed": true
   },
   "source": [
    "# Uniquely identify records with key constraints\n",
    "\n",
    "Now let's get into the best practices of database engineering. It's time to add primary and foreign keys to the tables. These are two of the most important concepts in databases, and are the building blocks you'll use to establish relationships between tables."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Keys and superkeys\n",
    "\n",
    "- The previous constraint changes, didn't change the structure of the database model\n",
    "- The database will look slightly different after changing key constraints\n",
    "- Primary keys, `id`,  will be added to three different tables (`organizations`, `professors` and `universities`)\n",
    "- Keys are denoted by underlined attribute names\n",
    "\n",
    "### What is a key?\n",
    "\n",
    "- Attribute(s) that uniquely identify a record\n",
    "- Normally, a table, as a whole, only contains unique records, meaning the combination of all attributes is a key in itself.\n",
    "- As long as attributes from that combination can be removed and the attributes still uniquely identify records, it's called a **superkey**\n",
    "- If all possible attributes have been removed, but the records are still uniquely identifiable by the remaining attributes, we speak of a minimal superkey or **key**\n",
    " - A key is **always minimal**\n",
    " \n",
    "```\n",
    "| license_no         | serial_no | make       | model   | year |\n",
    "|--------------------|-----------|------------|---------|------|\n",
    "| Texas ABC-739      | A69352    | Ford       | Mustang | 2    |\n",
    "| Florida TVP-347    | B43696    | Oldsmobile | Cutlass | 5    |\n",
    "| New York MPO-22    | X83554    | Oldsmobile | Delta   | 1    |\n",
    "| California 432-TFY | C43742    | Mercedes   | 190-D   | 99   |\n",
    "| California RSK-629 | Y82935    | Toyota     | Camry   | 4    |\n",
    "| Texas RSK-629      | U028365   | Jaguar     | XJS     | 4    |\n",
    "```\n",
    "\n",
    "**Superkeys**\n",
    "\n",
    "- SK1 = {license_no, serial_no, make, model, year}\n",
    "- SK2 = {license_no, serial_no, make, model}\n",
    "- SK3 = {make, model, year}\n",
    "- SK4 = {license_no, serial_no}\n",
    "- SKi, ..., SKn\n",
    "- The table shows six different cars, so the combination of all attributes is a superkey\n",
    "- If we remove the `year` attribute from the superkey, the six records are still unique, so it's still a superkey\n",
    "- There are many possible superkeys in the example\n",
    "- There are only 4 minimal superkeys\n",
    "\n",
    "**Candidate keys**\n",
    "\n",
    "- K1 = {license_no}\n",
    "- K2 = {serial_no}\n",
    "- K3 = {model}\n",
    "- K4 = {make, year}\n",
    "- Remember, superkeys are minimal if no attributes can be removed without losing the uniqueness property.\n",
    " - The is trivial for K1 - K3, as they only consist of a single attribute.\n",
    " - If we remove `year` from K4, `make` would contain duplicates, and would, therefore, no longer be suited as a key\n",
    "- These four minimal superkeys are also called candidate keys\n",
    " - Only one candidate key can be the chosen key"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get to know `SELECT COUNT DISTINCT`\n",
    "\n",
    "Your database doesn't have any defined keys so far, and you don't know which columns or combinations of columns are suited as keys.\n",
    "\n",
    "There's a simple way of finding out whether a certain column (or a combination) contains only unique values - and thus identifies the records in the table.\n",
    "\n",
    "You already know the `SELECT DISTINCT` query from the first chapter. Now you just have to wrap everything within the `COUNT()` function and PostgreSQL will return the number of unique rows for the given columns:\n",
    "```sql\n",
    "SELECT COUNT(DISTINCT(column_a, column_b, ...))\n",
    "FROM table;\n",
    "```\n",
    "**Instructions 1/2**\n",
    "\n",
    "* First, find out the number of rows in `universities`.\n",
    "\n",
    "```sql\n",
    "-- Count the number of rows in universities\n",
    "SELECT count(*) \n",
    "FROM universities;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "SELECT count(*) \\\n",
    "FROM uni.universities; \\\n",
    "\"\n",
    "res = conn.execute(stmt)\n",
    "res.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 2/2**\n",
    "\n",
    "* Then, find out how many unique values there are in the `university_city` column.\n",
    "\n",
    "```sql\n",
    "-- Count the number of distinct values in the university_city column\n",
    "SELECT COUNT(DISTINCT(university_city)) \n",
    "FROM universities;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "SELECT COUNT(DISTINCT(university_city)) \\\n",
    "FROM uni.universities; \\\n",
    "\"\n",
    "res = conn.execute(stmt)\n",
    "res.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**The `university_city` column wouldn't lend itself as a key because there are only 9 distinct values, but the table has 11 rows.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Identify keys with `SELECT COUNT DISTINCT`\n",
    "\n",
    "There's a very basic way of finding out what qualifies for a key in an existing, populated table:\n",
    "\n",
    "1. Count the distinct records for all possible combinations of columns. If the resulting number `x` equals the number of all rows in the table for a combination, you have discovered a superkey.\n",
    "\n",
    "1. Then remove one column after another until you can no longer remove columns without seeing the number `x` decrease. If that is the case, you have discovered a (candidate) key.\n",
    "\n",
    "The table `professors` has 551 rows. It has only one possible candidate key, which is a combination of two attributes. You might want to try different combinations using the \"Run code\" button. Once you have found the solution, you can submit your answer.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Using the above steps, identify the *candidate key* by trying out different combination of columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "attribs = ['firstname', 'lastname', 'university_shortname']\n",
    "combos = sum([list(map(list, combinations(attribs, i))) for i in range(len(attribs) + 1)[1:]], [])\n",
    "for x in combos:\n",
    "    att_str = ', '.join(x)\n",
    "    stmt = f\"SELECT COUNT(DISTINCT({att_str})) FROM uni.professors;\"\n",
    "    res = conn.execute(stmt)\n",
    "    print(att_str, res.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**The only combination that uniquely identifies `professors` is `{firstname, lastname}`. `{firstname, lastname, university_shortname}` is a superkey, and all other combinations give duplicate values. Hopefully, the concept of superkeys and keys is now a bit more clear. Let's move on to primary keys!**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Primary keys\n",
    "\n",
    "- Primary keys are one of the most important concepts in database design.\n",
    "- Almost every database table whold ahve a primary key - chosen from the set of candidate keys\n",
    "- The main purpose is uniquely identifying records in a table\n",
    " - This makes it easier to reference these records from other tables.\n",
    "- Primary keys need to be defined on columns that don't accept duplicate or null values\n",
    "- Primary key constraints are time-invariant, meaning they must hold for the current data in the table and for any future data the might be added to the table.\n",
    "- **Choose columns where values will always be unique and not null.**\n",
    "\n",
    "### Specifying primary keys\n",
    "\n",
    "- The following two tables accept exactly the same data, however, the latter has an explicit primary key specified.\n",
    "\n",
    "```sql\n",
    "CREATE TABLE products (\n",
    "    product_no integer UNIQUE NOT NULL,\n",
    "    name text,\n",
    "    price numeric\n",
    ");\n",
    "\n",
    "CREATE TABLE products (\n",
    "    product_no integer PRIMARY KEY,\n",
    "    name text,\n",
    "    price numeric\n",
    ");\n",
    "```\n",
    "\n",
    "- Primary keys can also be specified as follows\n",
    " - This notation is necessary if you want to designate more than one column as the primary keys.\n",
    " - This is still only one primary key, it's just formed by the combination of two columns.\n",
    " - Ideally, primary keys consist of as few columns as possible.\n",
    "\n",
    "```sql\n",
    "CREATE TABLE example (\n",
    "    a integer,\n",
    "    b integer,\n",
    "    c integer,\n",
    "    PRIMARY KEY (a, c)\n",
    ");\n",
    "```\n",
    "\n",
    "- Adding primary key constraints to existing tables is the same procedure as adding unique constraints.\n",
    "- As with unique constraints, the constraint must be given a certain name.\n",
    "\n",
    "```sql\n",
    "ALTER TABLE table_name\n",
    "ADD CONSTRAINT some_name PRIMARY KEY (column_name)\n",
    "```\n",
    "\n",
    "- In the exercises that follow, add primary keys to the tables `universities` and `organizations`.\n",
    "- Add a special type of primary key, a surrogate key, to the table `professors` in the last part of this chapter.\n",
    "\n",
    "![database][1]\n",
    "\n",
    "  [1]: https://raw.githubusercontent.com/trenton3983/DataCamp/master/Images/into_to_relational_databases_in_sql/database_keys.JPG"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Identify the primary key\n",
    "\n",
    "Have a look at the example table from the previous video. As the database designer, you have to make a wise choice as to which column should be the primary key.\n",
    "\n",
    "```\n",
    "     license_no     | serial_no |    make    |  model  | year\n",
    "--------------------+-----------+------------+---------+------\n",
    " Texas ABC-739      | A69352    | Ford       | Mustang |    2\n",
    " Florida TVP-347    | B43696    | Oldsmobile | Cutlass |    5\n",
    " New York MPO-22    | X83554    | Oldsmobile | Delta   |    1\n",
    " California 432-TFY | C43742    | Mercedes   | 190-D   |   99\n",
    " California RSK-629 | Y82935    | Toyota     | Camry   |    4\n",
    " Texas RSK-629      | U028365   | Jaguar     | XJS     |    4\n",
    "```\n",
    "\n",
    "Which of the following column or column combinations could best serve as primary key?\n",
    "\n",
    "**Possible Answers**\n",
    "\n",
    "- ~~PK = {make}~~\n",
    "- ~~PK = {model, year}~~\n",
    "- __**PK = {license_no}**__\n",
    "- ~~PK = {year, make}~~\n",
    "- **A primary key consisting solely of `license_no` is probably the wisest choice, as license numbers are certainly unique across all registered cars in a country.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `ADD` key `CONSTRAINT`s to the tables\n",
    "\n",
    "Two of the tables in your database already have well-suited candidate keys consisting of one column each: `organizations` and `universities` with the `organization` and `university_shortname` columns, respectively.\n",
    "\n",
    "In this exercise, you'll rename these columns to `id` using the `RENAME COLUMN` command and then specify primary key constraints for them. This is as straightforward as adding unique constraints (see the last exercise of Chapter 2):\n",
    "```sql\n",
    "ALTER TABLE table_name\n",
    "ADD CONSTRAINT some_name PRIMARY KEY (column_name)\n",
    "```\n",
    "Note that you can also specify more than one column in the brackets.\n",
    "\n",
    "**Instructions 1/2**\n",
    "\n",
    "* Rename the `organization` column to `id` in `organizations`.\n",
    "* Make `id` a primary key and name it `organization_pk`.\n",
    "\n",
    "```sql\n",
    "-- Rename the organization column to id\n",
    "ALTER TABLE organizations\n",
    "RENAME COLUMN organization TO id;\n",
    "\n",
    "-- Make id a primary key\n",
    "ALTER TABLE organizations\n",
    "ADD CONSTRAINT organization_pk PRIMARY KEY (id);\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.organizations \\\n",
    "RENAME COLUMN organization TO id; \\\n",
    "\"\n",
    "conn.execute(stmt)\n",
    "\n",
    "stmt = \"\\\n",
    "ALTER TABLE uni.organizations \\\n",
    "ADD CONSTRAINT organization_pk PRIMARY KEY (id); \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 2/2**\n",
    "\n",
    "* Rename the `university_shortname` column to `id` in `universities`.\n",
    "* Make `id` a primary key and name it `university_pk`.\n",
    "\n",
    "```sql\n",
    "-- Rename the university_shortname column to id\n",
    "ALTER TABLE universities\n",
    "RENAME COLUMN university_shortname TO id;\n",
    "\n",
    "-- Make id a primary key\n",
    "ALTER TABLE universities\n",
    "ADD CONSTRAINT university_pk PRIMARY KEY (id);\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.universities \\\n",
    "RENAME COLUMN university_shortname TO id; \\\n",
    "\"\n",
    "conn.execute(stmt)\n",
    "\n",
    "stmt = \"\\\n",
    "ALTER TABLE uni.universities \\\n",
    "ADD CONSTRAINT university_pk PRIMARY KEY (id); \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Surrogate keys\n",
    "\n",
    "* Surrogate keys are sort of an artificial primary key.\n",
    " * They aren't based on a native column in the data, but on a column that just exist for the sake of having a primary key\n",
    "* There are several reason for creating an artificial surrogate key.\n",
    " * A primary key is ideally constructed from as few columns as possible.\n",
    " * The primary key of a record should never change over time.\n",
    "* If you define an artificial primary key, ideally consisting of a unique number or string, you can be sure that this number stays the same for each record.\n",
    "* Other attributes might change, but the primary key always has the same value for a given record.\n",
    "\n",
    "```\n",
    "| license_no         | serial_no | make       | model   | color     |\n",
    "|--------------------|-----------|------------|---------|-----------|\n",
    "| Texas ABC-739      | A69352    | Ford       | Mustang | blue      |\n",
    "| Florida TVP-347    | B43696    | Oldsmobile | Cutlass | black     |\n",
    "| New York MPO-22    | X83554    | Oldsmobile | Delta   | silver    |\n",
    "| California 432-TFY | C43742    | Mercedes   | 190-D   | champagne |\n",
    "| California RSK-629 | Y82935    | Toyota     | Camry   | red       |\n",
    "| Texas RSK-629      | U028365   | Jaguar     | XJS     | blue      |\n",
    "```\n",
    "\n",
    "* In this table, the **license_no** column would be suited as the primary key - the license number is unlikely to change over time, not like the color column, for example, which might change if the car is repainted.\n",
    "* There's no need for a surrogate key here.\n",
    "* However, lets say there were only the following three attributes in the table.\n",
    "\n",
    "```\n",
    "| make       | model   | color     |\n",
    "|------------|---------|-----------|\n",
    "| Ford       | Mustang | blue      |\n",
    "| Oldsmobile | Cutlass | black     |\n",
    "| Oldsmobile | Delta   | silver    |\n",
    "| Mercedes   | 190-D   | champagne |\n",
    "| Toyota     | Camry   | red       |\n",
    "| Jaguar     | XJS     | blue      |\n",
    "```\n",
    "\n",
    "* The only sensible primary key would be the combination of **make** and **model**, but that's two columns for the primary key.\n",
    "* You could add a new surrogate key column, called **id**, to solve this problem.\n",
    "* Actually there's a special data type in PostgreSQL that allows the addition of auto-incrementing numbers to an existing table: the **serial** type.\n",
    "* It's specified just like any other data type.\n",
    "* Once you add a column with the **serial** type, all the records in your table will be numbered.\n",
    "\n",
    "```sql\n",
    "ALTER TABLE cars\n",
    "ADD COLUMN id serial PRIMARY KEY;\n",
    "```\n",
    "\n",
    "```sql\n",
    "INSERT INTO cars\n",
    "VALUES ('Volkswagen', 'Blitz', 'black');\n",
    "```\n",
    "\n",
    "```\n",
    "| make       | model   | color     | id |\n",
    "|------------|---------|-----------|----|\n",
    "| Ford       | Mustang | blue      | 1  |\n",
    "| Oldsmobile | Cutlass | black     | 2  |\n",
    "| Oldsmobile | Delta   | silver    | 3  |\n",
    "| Mercedes   | 190-D   | champagne | 4  |\n",
    "| Toyota     | Camry   | red       | 5  |\n",
    "| Jaguar     | XJS     | blue      | 6  |\n",
    "| Volkswaven | Blitz   | black     | 7  |\n",
    "```\n",
    "\n",
    "* Whenever you add a new record to the table, it will automatically get a number that does not exist yet.\n",
    "\n",
    "```sql\n",
    "INSERT INTO cars\n",
    "VALUES ('Opel', 'Astra', 'green', 1);\n",
    "```\n",
    "\n",
    "_*duplicate key value violates unique constraint \"id_pkey\"*_\n",
    "_*DETAIL:  Key (id)=(1) already exists.*_\n",
    "\n",
    "* If you try to specify an ID that already exists, the primary key constraint will prevent you from doing so.\n",
    "* After all, the **id** column uniquely identifies each record in this table, which is very useful, for example, when you want to refer to these records from another table.\n",
    "\n",
    "**Another type of surrogate key**\n",
    "\n",
    "* Combine two existing columns into a new one\n",
    "\n",
    "```sql\n",
    "ALTER TABLE table_name\n",
    "ADD COLUMN column_c varchar(256);\n",
    "\n",
    "UPDATE table_name\n",
    "SET column_c = CONCAT(column_a, column_b);\n",
    "\n",
    "ALTER TABLE table_name\n",
    "ADD CONSTRAINT pk PRIMARY KEY (column_c);\n",
    "```\n",
    "\n",
    "1. Add a new column with the **varchar** data type\n",
    "1. **UPDATE** that column with the concatenation of two existing columns\n",
    " * The **CONCAT** function glues together the values of two or more existing columns.\n",
    "1. Turn the new column into a surrogate primary key.\n",
    "\n",
    "* In the exercises, add a surrogate key to the **professors** table, because the existing attributes are not really suited as primary keys.\n",
    "* There could be more than one professor with the same name working for one university, resulting in duplicates.\n",
    "* With an auto-incrementing **id** column as the primary key, you make sure that each professor can be uniquely referred to.\n",
    "* This was not necessary for organizations and universities, as their names can be assumed to be unique across these tables.\n",
    " * It is unlikely that two organizations with the same name exist, solely for trademark reasons; the same for universities.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add a `SERIAL` surrogate key\n",
    "\n",
    "Since there's no single column candidate key in `professors` (only a composite key candidate consisting of `firstname`, `lastname`), you'll add a new column `id` to that table.\n",
    "\n",
    "This column has a special data type `serial`, which turns the column into an auto-incrementing number. This means that, whenever you add a new professor to the table, it will automatically get an `id` that does not exist yet in the table: a perfect primary key!\n",
    "\n",
    "**Instructions 1/3**\n",
    "\n",
    "* Add a new column `id` with data type `serial` to the `professors` table.\n",
    "\n",
    "```sql\n",
    "-- Add the new column to the table\n",
    "ALTER TABLE professors \n",
    "ADD COLUMN id serial;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.professors \\\n",
    "ADD COLUMN id serial; \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 2/3**\n",
    "\n",
    "Make `id` a primary key and name it `professors_pkey`.\n",
    "\n",
    "```sql\n",
    "-- Make id a primary key\n",
    "ALTER TABLE professors\n",
    "ADD CONSTRAINT professors_pkey PRIMARY KEY (id);\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.professors \\\n",
    "ADD CONSTRAINT professors_pkey PRIMARY KEY (id); \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 3/3**\n",
    "\n",
    "Write a query that returns all the columns and 10 rows from `professors`.\n",
    "\n",
    "```sql\n",
    "SELECT * FROM professors\n",
    "LIMIT 10;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "SELECT * FROM uni.professors \\\n",
    "LIMIT 10; \\\n",
    "\"\n",
    "\n",
    "res = conn.execute(stmt)\n",
    "res.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**As you can see, PostgreSQL has automatically numbered the rows with the `id` column, which now functions as a (surrogate) primary key – it uniquely identifies professors.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `CONCAT`enate columns to a surrogate key\n",
    "\n",
    "Another strategy to add a surrogate key to an existing table is to concatenate existing columns with the `CONCAT()` function.\n",
    "\n",
    "Let's think of the following example table:\n",
    "```sql\n",
    "CREATE TABLE cars (\n",
    " make varchar(64) NOT NULL,\n",
    " model varchar(64) NOT NULL,\n",
    " mpg integer NOT NULL\n",
    ")\n",
    "```\n",
    "The table is populated with **10** rows of *completely fictional* data.\n",
    "\n",
    "Unfortunately, the table doesn't have a primary key yet. None of the columns consists of only unique values, so some columns can be combined to form a key.\n",
    "\n",
    "In the course of the following exercises, you will combine `make` and `model` into such a surrogate key.\n",
    "\n",
    "**Instructions 1/4**\n",
    "\n",
    "* Count the number of distinct rows with a combination of the `make` and `model` columns.\n",
    "\n",
    "```sql\n",
    "-- Count the number of distinct rows with columns make, model\n",
    "SELECT COUNT(DISTINCT(make, model))\n",
    "FROM cars;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new schema and table\n",
    "conn.execute('CREATE SCHEMA auto;')\n",
    "conn.execute(' \\\n",
    "CREATE TABLE auto.cars ( \\\n",
    " \"make\" varchar(64) NOT NULL, \\\n",
    " \"model\" varchar(64) NOT NULL, \\\n",
    " \"mpg\" integer NOT NULL \\\n",
    ");')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {'make': ['Subaru', 'Opel', 'Opel', 'Ford', 'Ford', 'Toyota', 'Toyota', 'Toyota', 'Mitsubishi', 'Mitsubishi'],\n",
    "        'model': ['Forester', 'Astra', 'Vectra', 'Avenger', 'Galaxy', 'Prius', 'Speedster', 'Galaxy', 'Forester', 'Galaxy'],\n",
    "        'mpg': [24, 45, 40, 30, 30, 50, 30, 20, 10, 30]}\n",
    "cars = pd.DataFrame(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cars.to_sql('cars', con=engine, schema='auto', index=False, if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "SELECT COUNT(DISTINCT(make, model)) \\\n",
    "FROM auto.cars; \\\n",
    "\"\n",
    "\n",
    "res = conn.execute(stmt)\n",
    "res.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 2/4**\n",
    "\n",
    "Add a new column `id` with the data type `varchar(128)`.\n",
    "\n",
    "```sql\n",
    "-- Add the id column\n",
    "ALTER TABLE cars\n",
    "ADD COLUMN id varchar(128);\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE auto.cars \\\n",
    "ADD COLUMN id varchar(128); \\\n",
    "\"\n",
    "\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 3/4**\n",
    "\n",
    "Concatenate `make` and `model` into `id` using an `UPDATE table_name SET column_name = ...` query and the `CONCAT()` function.\n",
    "\n",
    "```sql\n",
    "-- Update id with make + model\n",
    "UPDATE cars\n",
    "SET id = CONCAT(make, model);\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "UPDATE auto.cars \\\n",
    "SET id = CONCAT(make, model); \\\n",
    "\"\n",
    "\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 4/4**\n",
    "\n",
    "Make `id` a primary key and name it `id_pk`.\n",
    "\n",
    "```sql\n",
    "-- Make id a primary key\n",
    "ALTER TABLE cars\n",
    "ADD CONSTRAINT id_pk PRIMARY KEY(id);\n",
    "\n",
    "-- Have a look at the table\n",
    "SELECT * FROM cars;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE auto.cars \\\n",
    "ADD CONSTRAINT id_pk PRIMARY KEY(id); \\\n",
    "\"\n",
    "conn.execute(stmt)\n",
    "\n",
    "res = conn.execute(\"SELECT * FROM auto.cars\")\n",
    "res.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test your knowledge before advancing\n",
    "\n",
    "Before you move on to the next chapter, let's quickly review what you've learned so far about attributes and key constraints. If you're unsure about the answer, please quickly review chapters 2 and 3, respectively.\n",
    "\n",
    "Let's think of an entity type \"student\". A student has:\n",
    "\n",
    "* a *last name* consisting of up to 128 characters (this cannot contain a missing value),\n",
    "* a unique *social security number* of length 9, consisting only of integers,\n",
    "* a *phone number* of fixed length 12, consisting of numbers and characters (but some students don't have one).\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Given the above description of a student entity, create a table `students` with the correct column types.\n",
    "* Add a `PRIMARY KEY` for the social security number `ssn`.\n",
    "* Note that there is no formal length requirement for the integer column. The application would have to make sure it's a correct SSN!\n",
    "\n",
    "```sql\n",
    "-- Create the table\n",
    "CREATE TABLE students (\n",
    "  last_name char(128) NOT NULL,\n",
    "  ssn integer[9] UNIQUE,\n",
    "  phone_no varchar(12),\n",
    "  PRIMARY KEY (ssn)\n",
    ");\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.execute(' \\\n",
    "CREATE TABLE uni.students ( \\\n",
    " \"last_name\" char(128) NOT NULL, \\\n",
    " \"ssn\" integer[9] UNIQUE, \\\n",
    " \"phone_no\" varchar(12), \\\n",
    " PRIMARY KEY (ssn) \\\n",
    ");')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": false
   },
   "source": [
    "# Glue together tables with foreign keys\n",
    "\n",
    "In the final chapter, you'll leverage foreign keys to connect tables and establish relationships that will greatly benefit your data quality. And you'll run ad hoc analyses on your new database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": false
   },
   "source": [
    "## Model 1:N relationships with foreign keys\n",
    "\n",
    "**Current DB model**\n",
    "\n",
    "![db_model][1]\n",
    "\n",
    "* The three entity types **professors**, **organizations**, and **universities** all have primary keys, but **affiliations** doesn't, for a specific reason that will be revealed in this chapter.\n",
    "\n",
    "**Next DB model**\n",
    "\n",
    "![relationship][2]\n",
    "\n",
    "* You'll model a so-called relationship type between **professors** and **universities**.\n",
    "* In your database, each professor work for a university.\n",
    " * In the ER diagram, this is drawn with a rhombus.\n",
    " * The small numbers specify the cardinality of the relationship: a professor works for at most one university, while a university can have any number of professors working for it - even zero.\n",
    "* Such relationships are implemented with foreign keys.\n",
    " * Foreign keys are designated columns that point to a primary key of another table.  A FK points to the PK of another table.\n",
    " * There are some restrictions for foreign keys.\n",
    " 1. The domain and the data type must be the same as the one of the primary key.  Domain of FK and PK must be equal.\n",
    " 1. Only foreign key values are allowed that exist as values in the primary key of the referenced table.  Each value of FK must exist in PK of the other table (FK constraint or **referential integrity**).\n",
    " * This is the actual foreign key constraint, also called **referential integrity**.\n",
    "* FKs are not actual keys\n",
    " * A foreign key is not necessarily an actual key, because duplicates and  **NULL** values are allowed.\n",
    "\n",
    "```sql\n",
    "SELECT * FROM professors LIMIT 8;\n",
    "```\n",
    "\n",
    "```\n",
    "| id | firstname       | lastname    | university_shortname |\n",
    "|----|-----------------|-------------|----------------------|\n",
    "| 1  | Karl            | Aberer      | EPF                  |\n",
    "| 2  | Reza Shokrollah | Abhari      | ETH                  |\n",
    "| 3  | Georges         | Abou Jaoudé | EPF                  |\n",
    "| 4  | Hugues          | Abriel      | UBE                  |\n",
    "| 5  | Daniel          | Aebersold   | UBE                  |\n",
    "| 6  | Marcelo         | Aebi        | ULA                  |\n",
    "| 7  | Christoph       | Aebi        | UBE                  |\n",
    "| 8  | Patrick         | Aebischer   | EPF                  |\n",
    "```\n",
    "\n",
    "```sql\n",
    "SELECT * FROM universities;\n",
    "```\n",
    "\n",
    "```\n",
    "| id  | university     | university_city |\n",
    "|-----|----------------|-----------------|\n",
    "| EPF | ETH Lausanne   | Lausanne        |\n",
    "| ETH | ETH Zürich     | Zurich          |\n",
    "| UBA | Uni Basel      | Basel           |\n",
    "| UBE | Uni Bern       | Bern            |\n",
    "| UFR | Uni Freiburg   | Fribourg        |\n",
    "| UGE | Uni Genf       | Geneva          |\n",
    "| ULA | Uni Lausanne   | Lausanne        |\n",
    "| UNE | Uni Neuenburg  | Neuchâtel       |\n",
    "| USG | Uni St. Gallen | Saint Gallen    |\n",
    "| USI | USI Lugano     | Lugano          |\n",
    "| UZH | Uni Zürich     | Zurich          |\n",
    "```\n",
    "\n",
    "* The column **university_shortname** of **professors** has the same domain as the **id** column of the **universities** table\n",
    "* If you go through each record of **professors**, you can always find the respective **id** in the **universities** table.\n",
    "* So both criteria for a foreign key in the talbe **professor** referencing **universities** are fulfilled.\n",
    "* **university_shortname** is not really a key because there are duplicates.\n",
    " * **EPF** and **UBE** occur three times each.\n",
    "\n",
    "**Specifying foreign keys**\n",
    "\n",
    "```sql\n",
    "CREATE TABLE manufacturers (\n",
    " name varchar(255) PRIMARY KEY\n",
    ");\n",
    "\n",
    "INSERT INTO manufacturers \n",
    "VALUES ('Ford'), ('VW'), ('GM');\n",
    "\n",
    "CREATE TABLE cars (\n",
    " model varchar(255) PRIMARY KEY,\n",
    " manufacturer_name integer REFERENCES manufacturers (name)\n",
    ");\n",
    "\n",
    "INSERT INTO cars \n",
    "VALUES ('Ranger', 'Ford'), ('Beetle', 'VW');\n",
    "```\n",
    "\n",
    "* When you create a new table, you can specify a foreign key similarly to a primary key\n",
    "1. Create a **manufacturers** table with a primary key called **name**.\n",
    "1. Create a table **cars**, that also has a primary key, called **model**.\n",
    "* As each car is produced by a certain manufacturer, it makes sense to also add a foreign key to this table.\n",
    " * We do that by writing the **REFERENCES** keyword, followed by the referenced table and its primary key in brackets.\n",
    "* From now on, only cars with valid and existing manufacturers may be entered into that table.\n",
    "\n",
    "```sql\n",
    "-- Throws an error!\n",
    "INSERT INTO cars \n",
    "VALUES ('Tundra', 'Toyota');\n",
    "```\n",
    "\n",
    "* Trying to enter models with manufacturers that are not yet stored in the **manufacturers** table won't be possible, thanks to the foreign key constraint.\n",
    "\n",
    "\n",
    "**Specifying foreign keys to existing tables**\n",
    "\n",
    "```sql\n",
    "ALTER TABLE a \n",
    "ADD CONSTRAINT a_fkey FOREIGN KEY (b_id) REFERENCES b (id);\n",
    "```\n",
    "\n",
    "* The syntax for adding foreign keys to existing tables is the same as the one for adding primary keys and unique constraints.\n",
    "\n",
    "  [1]: https://raw.githubusercontent.com/trenton3983/DataCamp/master/Images/into_to_relational_databases_in_sql/df_model.JPG\n",
    "  [2]: https://raw.githubusercontent.com/trenton3983/DataCamp/master/Images/into_to_relational_databases_in_sql/relationship_model.JPG"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### REFERENCE a table with a FOREIGN KEY\n",
    "\n",
    "In your database, you want the `professors` table to reference the `universities` table. You can do that by specifying a column in `professors` table that references a column in the `universities` table.\n",
    "\n",
    "As just shown in the video, the syntax for that looks like this:\n",
    "\n",
    "```sql\n",
    "ALTER TABLE a \n",
    "ADD CONSTRAINT a_fkey FOREIGN KEY (b_id) REFERENCES b (id);\n",
    "```\n",
    "\n",
    "Table `a` should now refer to table `b`, via `b_id`, which points to `id`. `a_fkey` is, as usual, a constraint name you can choose on your own.\n",
    "\n",
    "Pay attention to the **naming convention** employed here: Usually, a foreign key referencing another primary key with name `id` is named `x_id`, where `x` is the name of the referencing table in the singular form.\n",
    "\n",
    "**Instructions 1/2**\n",
    "\n",
    "* Rename the `university_shortname` column to `university_id` in `professors`.\n",
    "\n",
    "```sql\n",
    "-- Rename the university_shortname column\n",
    "ALTER TABLE professors\n",
    "RENAME COLUMN university_shortname to university_id;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.professors \\\n",
    "RENAME COLUMN university_shortname to university_id; \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 2/2**\n",
    "\n",
    "* Add a foreign key on `university_id` column in `professors` that references the `id` column in `universities`.\n",
    "* Name this foreign key `professors_fkey`.\n",
    "\n",
    "```sql\n",
    "-- Add a foreign key on professors referencing universities\n",
    "ALTER TABLE professors \n",
    "ADD CONSTRAINT professors_fkey FOREIGN KEY (university_id) REFERENCES universities (id);\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.professors \\\n",
    "ADD CONSTRAINT professors_fkey FOREIGN KEY (university_id) REFERENCES uni.universities (id); \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Now, the `professors` table has a link to the `universities` table. Each professor belongs to exactly one university.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Explore foreign key constraints\n",
    "\n",
    "Foreign key constraints help you to keep order in your database mini-world. In your database, for instance, only professors belonging to Swiss universities should be allowed, as only Swiss universities are part of the `universities` table.\n",
    "\n",
    "The foreign key on `professors` referencing universities you just created thus makes sure that only existing `universities` can be specified when inserting new data. Let's test this!\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Run the sample code and have a look at the error message.\n",
    "\n",
    "```sql\n",
    "-- Try to insert a new professor\n",
    "INSERT INTO professors (firstname, lastname, university_id)\n",
    "VALUES ('Albert', 'Einstein', 'MIT');\n",
    "```\n",
    "\n",
    "> insert or update on table \"professors\" violates foreign key constraint \"professors_fkey\"\n",
    "\n",
    "> DETAIL:  Key (university_id)=(MIT) is not present in table \"universities\".\n",
    "\n",
    "* What's wrong? Correct the `university_id` so that it actually reflects where Albert Einstein wrote his dissertation and became a professor – at the University of Zurich (UZH)!\n",
    "\n",
    "```sql\n",
    "-- Try to insert a new professor\n",
    "INSERT INTO professors (firstname, lastname, university_id)\n",
    "VALUES ('Albert', 'Einstein', 'UZH');\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "INSERT INTO uni.professors (firstname, lastname, university_id) \\\n",
    "VALUES ('Albert', 'Einstein', 'MIT'); \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "INSERT INTO uni.professors (firstname, lastname, university_id) \\\n",
    "VALUES ('Albert', 'Einstein', 'UZH'); \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**As you can see, inserting a professor with non-existing university IDs violates the foreign key constraint you've just added. This also makes sure that all universities are spelled equally – adding to data consistency.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### JOIN tables linked by a foreign key\n",
    "\n",
    "Let's join these two tables to analyze the data further!\n",
    "\n",
    "You might already know how SQL joins work from the [Intro to SQL for Data Science][1] course (last exercise) or from [Joining Data in PostgreSQL][2].\n",
    "\n",
    "Here's a quick recap on how joins generally work:\n",
    "\n",
    "```sql\n",
    "SELECT ...\n",
    "FROM table_a\n",
    "JOIN table_b\n",
    "ON ...\n",
    "WHERE ...\n",
    "```\n",
    "\n",
    "While foreign keys and primary keys are not strictly necessary for join queries, they greatly help by telling you what to expect. For instance, you can be sure that records referenced from table A will always be present in table B – so a join from table A will always find something in table B. If not, the foreign key constraint would be violated.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* `JOIN` `professors` with `universities` on `professors.university_id = universities.id`, i.e., retain all records where the foreign key of `professor`s is equal to the primary key of `universities`.\n",
    "* Filter for `university_city = 'Zurich'`.\n",
    "\n",
    "```sql\n",
    "-- Select all professors working for universities in the city of Zurich\n",
    "SELECT professors.lastname, universities.id, universities.university_city\n",
    "FROM professors\n",
    "JOIN universities\n",
    "ON professors.university_id = universities.id\n",
    "WHERE universities.university_city = 'Zurich';\n",
    "```\n",
    "\n",
    "\n",
    "  [1]: https://www.datacamp.com/courses/intro-to-sql-for-data-science\n",
    "  [2]: https://www.datacamp.com/courses/joining-data-in-postgresql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "SELECT professors.lastname, universities.id, universities.university_city \\\n",
    "FROM uni.professors \\\n",
    "JOIN uni.universities \\\n",
    "ON professors.university_id = universities.id \\\n",
    "WHERE universities.university_city = 'Zurich'; \\\n",
    "\"\n",
    "\n",
    "res = conn.execute(stmt)\n",
    "res.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**First, the university belonging to each professor was attached with the `JOIN` operation. Then, only professors having \"Zurich\" as university city were retained with the `WHERE` clause.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Model more complex relationships\n",
    "\n",
    "* In the last few exercises, you made your first steps in modeling and implementing 1:N-relationships between **professors** and **universities**.\n",
    "* Lets look at more complex relationships.\n",
    "* 1:N-relationships are implemented with one foreign key in the table that has at most one foreign entity associated.\n",
    "* In this case, that's the **professors** table, as professors cannot have more than one university associated.\n",
    "* What about **affiliations**?\n",
    " * We know that a professor can have more than one affiliation with **organizations**, for instance, as a chairman of a bank and as a president of a golf club.\n",
    " * On the other hand, **organizations** can also have more than one professor connected with them\n",
    " * Let's look at the entity-relationship diagram that models this.\n",
    "\n",
    "![final_model][1]\n",
    "\n",
    "* There are a couple of things that are new.\n",
    "1. A new relationship between **organizations** and **professors** was added.\n",
    " * This is an N:M relationship\n",
    " * This depicts the fact that a **professor** can be affiliated with more than one **organization** and vice versa.\n",
    " * Also, it has an **own** attribute, the function.\n",
    " * Remember that each **affiliation** comes with a function, for instance, **chariman**.\n",
    "1. The **affiliations** entity type disappeared altogether (shadowed in the diagram).\n",
    " * You'll still have four tables: three for the entities **professors**, **universities**, and **organizations**, and one for the N:M relationship between **professors** and **organizations**.\n",
    "\n",
    "**How to implement N:M-relationships**\n",
    "\n",
    "* Create a table.  Such a relationship is implemented with an ordinary database table that contains two foreign keys that point to both connected entities.\n",
    "* Add foreign keys for every connected table.  In this case, that's a foreign key pointing to the **professors.id** column and one pointing to the **organizations.id** column.\n",
    "* Add additional attributes.  In this case, **function**.  \n",
    "\n",
    "```sql\n",
    "CREATE TABLE affiliations (\n",
    " professor_id integer REFERENCES professors (id),\n",
    " organization_id varchar(256) REFERENCES organization (id),\n",
    " function varchar(256)\n",
    ");\n",
    "```\n",
    "\n",
    "* If you were to create that relationship table from scratch, you would define it as shown.\n",
    "* The **professor_id** is stored as **integer**, as the primary key it refers to has the type **serial**, which is also an integer.\n",
    "* **organization_id** has **varchar(256)** as type, conforming to the primary key in the **organizations** table.\n",
    "* Notice that no primary key is define here because a professor can theoretically have multiple functions in one organization.\n",
    "* Possible PK = {professor_id, organization_id, function}\n",
    " * One could define the combination of all three attributes as the primary key in order to have some form of unique constraint in that table, but that would be a bit over the top.\n",
    "\n",
    "**Time to implement this**\n",
    "\n",
    "* Since you already have a pre-populated **affiliations** table, things are not going to be so straightforward.\n",
    "* You'll need to link and migrate the data to a new table to implement this relationship.\n",
    "\n",
    "\n",
    " [1]: https://raw.githubusercontent.com/trenton3983/DataCamp/master/Images/into_to_relational_databases_in_sql/final_model.JPG"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add foreign keys to the \"affiliations\" table\n",
    "\n",
    "At the moment, the affiliations table has the structure {`firstname`, `lastname`, `function`, `organization`}, as you can see in the preview at the bottom right. In the next three exercises, you're going to turn this table into the form {`professor_id`, `organization_id`, `function`}, with professor_id and organization_id being foreign keys that point to the respective tables.\n",
    "\n",
    "You're going to transform the affiliations table in-place, i.e., without creating a temporary table to cache your intermediate results.\n",
    "\n",
    "**Instructions 1/3**\n",
    "\n",
    "* Add a `professor_id` column with `integer` data type to `affiliations`, and declare it to be a foreign key that references the `id` column in `professors`.\n",
    "\n",
    "```sql\n",
    "-- Add a professor_id column\n",
    "ALTER TABLE affiliations\n",
    "ADD COLUMN professor_id integer REFERENCES professors (id);\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.affiliations \\\n",
    "ADD COLUMN professor_id integer REFERENCES uni.professors (id); \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 2/3**\n",
    "\n",
    "Rename the `organization` column in `affiliations` to `organization_id`.\n",
    "\n",
    "```sql\n",
    "-- Rename the organization column to organization_id\n",
    "ALTER TABLE affiliations\n",
    "RENAME COLUMN organization TO organization_id;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.affiliations \\\n",
    "RENAME COLUMN organization TO organization_id; \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 3/3**\n",
    "\n",
    "Add a foreign key constraint on `organization_id` so that it references the `id` column in `organizations`.\n",
    "\n",
    "```sql\n",
    "-- Add a foreign key on organization_id\n",
    "ALTER TABLE affiliations\n",
    "ADD CONSTRAINT affiliations_organization_fkey FOREIGN KEY (organization_id) REFERENCES organizations (id);\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.affiliations \\\n",
    "ADD CONSTRAINT affiliations_organization_fkey FOREIGN KEY (organization_id) REFERENCES uni.organizations (id); \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Making `organization_id` a foreign key worked flawlessly because these organizations actually exist in the `organizations table`. That was only the first part, though. Now it's time to update `professor_id` in `affiliations` – so that it correctly refers to the corresponding professors.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Populate the \"professor_id\" column\n",
    "\n",
    "Now it's time to also populate `professors_id`. You'll take the ID directly from `professors`.\n",
    "\n",
    "Here's a way to update columns of a table based on values in another table:\n",
    "\n",
    "```sql\n",
    "UPDATE table_a\n",
    "SET column_to_update = table_b.column_to_update_from\n",
    "FROM table_b\n",
    "WHERE condition1 AND condition2 AND ...;\n",
    "```\n",
    "\n",
    "This query does the following:\n",
    "\n",
    "1. For each row in `table_a`, find the corresponding row in `table_b` where `condition1`, `condition2`, etc., are met.\n",
    "2. Set the value of `column_to_update` to the value of `column_to_update_from` (from that corresponding row).\n",
    "\n",
    "The conditions usually compare other columns of both tables, e.g. `table_a.some_column = table_b.some_column`. Of course, this query only makes sense if there is only one matching row in `table_b`.\n",
    "\n",
    "**Instructions 1/3**\n",
    "\n",
    "* First, have a look at the current state of `affiliations` by fetching 10 rows and all columns.\n",
    "\n",
    "```sql\n",
    "-- Have a look at the 10 first rows of affiliations\n",
    "SELECT * FROM affiliations\n",
    "LIMIT 10;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "SELECT * FROM uni.affiliations \\\n",
    "LIMIT 10; \\\n",
    "\"\n",
    "res = conn.execute(stmt)\n",
    "res.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 2/3**\n",
    "\n",
    "* Update the `professor_id` column with the corresponding value of the `id` column in `professors`.\n",
    "* _\"Corresponding\" means rows in `professors` where the `firstname` and `lastname` are identical to the ones in `affiliations`._\n",
    "\n",
    "```sql\n",
    "-- Set professor_id to professors.id where firstname, lastname correspond to rows in professors\n",
    "UPDATE affiliations\n",
    "SET professor_id = professors.id\n",
    "FROM professors\n",
    "WHERE affiliations.firstname = professors.firstname AND affiliations.lastname = professors.lastname;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "UPDATE uni.affiliations \\\n",
    "SET professor_id = professors.id \\\n",
    "FROM uni.professors \\\n",
    "WHERE affiliations.firstname = professors.firstname AND affiliations.lastname = professors.lastname; \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 3/3**\n",
    "\n",
    "* Check out the first 10 rows and all columns of `affiliations` again. Have the `professor_ids` been correctly matched?\n",
    "\n",
    "```sql\n",
    "-- Have a look at the 10 first rows of affiliations again\n",
    "SELECT * FROM affiliations\n",
    "LIMIT 10;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "SELECT * FROM uni.affiliations \\\n",
    "LIMIT 10; \\\n",
    "\"\n",
    "res = conn.execute(stmt)\n",
    "res.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**As you can see, the correct `professors.id` has been inserted into `professor_id` for each record, thanks to the matching `firstname` and `lastname` in both tables.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop \"firstname\" and \"lastname\"\n",
    "\n",
    "The firstname and lastname columns of affiliations were used to establish a link to the professors table in the last exercise – so the appropriate professor IDs could be copied over. This only worked because there is exactly one corresponding professor for each row in affiliations. In other words: {firstname, lastname} is a candidate key of professors – a unique combination of columns.\n",
    "\n",
    "It isn't one in affiliations though, because, as said in the video, professors can have more than one affiliation.\n",
    "\n",
    "Because professors are referenced by professor_id now, the firstname and lastname columns are no longer needed, so it's time to drop them. After all, one of the goals of a database is to reduce redundancy where possible.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Drop the `firstname` and `lastname` columns from the `affiliations` table.\n",
    "\n",
    "```sql\n",
    "-- Drop the firstname & lastname column\n",
    "ALTER TABLE affiliations\n",
    "DROP COLUMN firstname;\n",
    "\n",
    "ALTER TABLE affiliations\n",
    "DROP COLUMN lastname;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.affiliations \\\n",
    "DROP COLUMN firstname, \\\n",
    "DROP COLUMN lastname; \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Now the `affiliations` table that models the N:M-relationship between `professors` and `organizations` is finally complete.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Referential integrity\n",
    "\n",
    "* A record referencing another table must refer to an existing record in that table\n",
    " * A record in table A can't point to a record in table B that doesn't exist.\n",
    "* Referential integrity is a constraint that always __concerns two tables__, and is enforced through foreign keys, as you've seen in the previous lessons of this chapter.\n",
    "* Enforced through foreign keys. If you define a foreign key in the table **professors** referencing the table **universities**, referential integrity is held from **professors** to **universities**.\n",
    "\n",
    "**referential integrity violations**\n",
    "\n",
    "* Referential integrity from table A to table B is violated...\n",
    " * If a record in table B that is already referenced from table A is deleted, you have a violation.\n",
    " * If you try to insert a record in table A that refers to something that does not exist in table B, you also violate the principle.\n",
    " * The main reason for Foreign keys is they prevent violations - they throw errors and stop you from accidentally doing these things.\n",
    "\n",
    "**Dealing with violations**\n",
    "\n",
    "```sql\n",
    "CREATE TABLE a (\n",
    " id integer PRIMARY KEY,\n",
    " column_a varchar(64), \n",
    " ...,\n",
    " b_id integer REFERENCES b (id) ON DELETE NO ACTION\n",
    ");\n",
    "```\n",
    "\n",
    "* Throwing errors is not the only option.\n",
    "* If you specify a foreign key on a column, you can tell the the database system what should happen if an entry in the referenced table is deleted.\n",
    "* By default, the **ON DELETE NO ACTION** keyword is automatically appended to a foreign key definition.\n",
    " * If you try to delete a record in table B, which is reference from table A, the system will throw an error.\n",
    "\n",
    "**Other options**\n",
    "\n",
    "```sql\n",
    "CREATE TABLE a (\n",
    " id integer PRIMARY KEY,\n",
    " column_a varchar(64), \n",
    " ...,\n",
    " b_id integer REFERENCES b (id) ON DELETE CASCADE\n",
    ");\n",
    "```\n",
    "\n",
    "1. There's the **CASCADE** option, which will first allow the deletion of the record in table B, and then will automatically delete all referencing records in table A.  That deletion is cascaded and will delete all referencing records.\n",
    "1. The **RESTRICT** option is almost identical to the **NO ACTION** option.  If will throw an error.\n",
    "1. The **SET NULL** option.  It will set the value of the foreign key for this record to **NULL**.\n",
    "1. The **SET DEFAULT** option sets the referencing column to its default value if the referenced record is deleted and only works if a default value for the column has been specified.\n",
    "* Let's practice by changing the referential integrity behavior of the database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Referential integrity violations\n",
    "\n",
    "Given the current state of your database, what happens if you execute the following SQL statement?\n",
    "\n",
    "```sql\n",
    "DELETE FROM universities WHERE id = 'EPF';\n",
    "```\n",
    "\n",
    "**Possible Answers**\n",
    "\n",
    "* ~~It throws an error because the university with ID \"EPF\" does not exist.~~\n",
    "* ~~The university with ID \"EPF\" is deleted.~~\n",
    "* ~~It fails because referential integrity from universities to professors is violated~~.\n",
    "* **It fails because referential integrity from professors to universities is violated.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**You defined a foreign key on `professors.university_id` that references `universities.id`, so referential integrity is said to hold from `professors` to `universities`.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Change the referential integrity behavior of a key\n",
    "\n",
    "So far, you implemented three foreign key constraints:\n",
    "\n",
    "1. `professors.university_id` to `universities.id`\n",
    "1. `affiliations.organization_id` to `organizations.id`\n",
    "1. `affiliations.professor_id` to `professors.id`\n",
    "\n",
    "These foreign keys currently have the behavior `ON DELETE NO ACTION`. Here, you're going to change that behavior for the column referencing `organizations` from `affiliations`. If an organization is deleted, all its affiliations (by any professor) should also be deleted.\n",
    "\n",
    "Altering a key constraint doesn't work with `ALTER COLUMN`. Instead, you have to **delete the key constraint** and then **add a new one** with a different `ON DELETE` behavior.\n",
    "\n",
    "For deleting constraints, though, you need to know their name. This information is also stored in `information_schema`.\n",
    "\n",
    "**Instructions 1/4**\n",
    "\n",
    "* Have a look at the existing foreign key constraints by querying `table_constraint`s in `information_schema`.\n",
    "\n",
    "```sql\n",
    "-- Identify the correct constraint name\n",
    "SELECT constraint_name, table_name, constraint_type\n",
    "FROM information_schema.table_constraints\n",
    "WHERE constraint_type = 'FOREIGN KEY';\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "SELECT constraint_name, table_name, constraint_type \\\n",
    "FROM information_schema.table_constraints \\\n",
    "WHERE constraint_type = 'FOREIGN KEY'; \\\n",
    "\"\n",
    "res = conn.execute(stmt)\n",
    "res.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 2/4**\n",
    "\n",
    "* Delete the `affiliations_organization_id_fkey` foreign key constraint in `affiliations`.\n",
    "\n",
    "```sql\n",
    "-- Drop the right foreign key constraint\n",
    "ALTER TABLE affiliations\n",
    "DROP CONSTRAINT affiliations_organization_id_fkey;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.affiliations \\\n",
    "DROP CONSTRAINT affiliations_organization_fkey; \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 3/4**\n",
    "\n",
    "* Add a new foreign key to `affiliations` that cascades deletion if a referenced record is deleted from `organizations`. Name it `affiliations_organization_id_fkey`.\n",
    "\n",
    "```sql\n",
    "-- Add a new foreign key constraint from affiliations to organizations which cascades deletion\n",
    "ALTER TABLE affiliations\n",
    "ADD CONSTRAINT affiliations_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "ALTER TABLE uni.affiliations \\\n",
    "ADD CONSTRAINT affiliations_organization_fkey FOREIGN KEY (organization_id) REFERENCES uni.organizations (id) ON DELETE CASCADE; \\\n",
    "\"\n",
    "conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 4/4**\n",
    "\n",
    "* Run the `DELETE` and `SELECT` queries to double check that the deletion cascade actually works.\n",
    "\n",
    "```sql\n",
    "-- Delete an organization \n",
    "DELETE FROM organizations \n",
    "WHERE id = 'CUREM';\n",
    "\n",
    "-- Check that no more affiliations with this organization exist\n",
    "SELECT * FROM affiliations\n",
    "WHERE organization_id = 'CUREM';\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "DELETE FROM uni.organizations \\\n",
    "WHERE id = 'CUREM'; \\\n",
    "\"\n",
    "conn.execute(stmt)\n",
    "\n",
    "stmt = \"\\\n",
    "SELECT * FROM uni.affiliations \\\n",
    "WHERE organization_id = 'CUREM'; \\\n",
    "\"\n",
    "res = conn.execute(stmt)\n",
    "res.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**As you can see, whenever an organization referenced by an affiliation is deleted, the affiliations also gets deleted. It is your job as database designer to judge whether this is a sensible configuration. Sometimes, setting values to `NULL` or to restrict deletion altogether might make more sense!**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Roundup\n",
    "\n",
    "**How the database has been transformed**\n",
    "\n",
    "![initial_db][1]\n",
    "\n",
    "* Started with a simple table with a lot of redundancy.\n",
    "* You might be used to such tables when working with flat files like CSVs or Excel files.\n",
    "* The table has undergone a step-by-step transformation into the following database schema - only by executing SQL queries.\n",
    "\n",
    "![final_model_2][2]\n",
    "\n",
    "* Column data types have been defined, primary and foreign keys added, and through that, specified relationships between tables.\n",
    "* All these measures will guarantee better data consistency and therefore quality.\n",
    "* This is especially helpful if you add new data to the database, but also makes analyzing the data easier.\n",
    "\n",
    "**The database ecosystem**\n",
    "\n",
    "![ecosystem][3]\n",
    "\n",
    "* To go further from here, it's useful to look at the bigger picture for a minute.\n",
    "* In this course, a database has been transformed.\n",
    " * That was done with PostgreSQL, which is also call a **Database Management System** or **DBMS**.\n",
    " * The **DBMS** and one or more databases together form the **Database System**.\n",
    " * The **DBMS** exposes a query interface where you can run ad-hoc analyses and queries with SQL.\n",
    " * You can also access this interface through other client applications.\n",
    " * You could, for example, program a Python script that connects to the database, loads data from it, and visualizes it.\n",
    "* In the remainder of this course, you'll no longer manipulate data in your database system, but employ some analysis queries on your database.\n",
    " * This will be a quick repetition of what you've learned in previous SQL courses such as **[Into to SQL for Data Science][4]**, but it will also demonstrate the advantages of having a database instead of a flat file in the first place.\n",
    "\n",
    "\n",
    "\n",
    " [1]: https://raw.githubusercontent.com/trenton3983/DataCamp/master/Images/into_to_relational_databases_in_sql/initial_db.JPG\n",
    " [2]: https://raw.githubusercontent.com/trenton3983/DataCamp/master/Images/into_to_relational_databases_in_sql/final_model_2.JPG\n",
    " [3]: https://raw.githubusercontent.com/trenton3983/DataCamp/master/Images/into_to_relational_databases_in_sql/ecosystem.JPG\n",
    " [4]: https://www.datacamp.com/courses/intro-to-sql-for-data-science"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Count affiliations per university\n",
    "\n",
    "Now that your data is ready for analysis, let's run some exemplary SQL queries on the database. You'll now use already known concepts such as grouping by columns and joining tables.\n",
    "\n",
    "In this exercise, you will find out which university has the most affiliations (through its professors). For that, you need both `affiliations` and `professors` tables, as the latter also holds the `university_id`.\n",
    "\n",
    "As a quick repetition, remember that joins have the following structure:\n",
    "\n",
    "```sql\n",
    "SELECT table_a.column1, table_a.column2, table_b.column1, ... \n",
    "FROM table_a\n",
    "JOIN table_b \n",
    "ON table_a.column = table_b.column\n",
    "```\n",
    "\n",
    "This results in a combination of `table_a` and `table_b`, but only with rows where `table_a.column` is equal to `table_b.column`.\n",
    "\n",
    "**Instructions**\n",
    "\n",
    "* Count the number of total affiliations by university.\n",
    "* Sort the result by that count, in descending order.\n",
    "\n",
    "\n",
    "```sql\n",
    "-- Count the total number of affiliations per university\n",
    "SELECT COUNT(*), professors.university_id \n",
    "FROM affiliations\n",
    "JOIN professors\n",
    "ON affiliations.professor_id = professors.id\n",
    "-- Group by the ids of professors\n",
    "GROUP BY professors.id \n",
    "ORDER BY count DESC;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "SELECT COUNT(*), professors.university_id \\\n",
    "FROM uni.affiliations \\\n",
    "JOIN uni.professors \\\n",
    "ON affiliations.professor_id = professors.id \\\n",
    "GROUP BY professors.university_id \\\n",
    "ORDER BY count DESC LIMIT 10; \\\n",
    "\"\n",
    "res = conn.execute(stmt)\n",
    "res.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**As you can see, the count of affiliations is completely different from university to university.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Join all the tables together\n",
    "\n",
    "In this last exercise, you will find the university city of the professor with the most affiliations in the sector \"Media & communication\".\n",
    "\n",
    "For this, you need to join all the tables, group by a column, and then use selection criteria to get only the rows in the correct sector.\n",
    "\n",
    "**Instructions 1/3**\n",
    "\n",
    "* Join all tables in the database (starting with `affiliations`, `professors`, `organizations`, and `universities`) and look at the result.\n",
    "\n",
    "```sql\n",
    "-- Join all tables\n",
    "SELECT *\n",
    "FROM affiliations\n",
    "JOIN professors\n",
    "ON affiliations.professor_id = professors.id\n",
    "JOIN organizations\n",
    "ON affiliations.organization_id = organizations.id\n",
    "JOIN universities\n",
    "ON professors.university_id = universities.id;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "SELECT * \\\n",
    "FROM uni.affiliations \\\n",
    "JOIN uni.professors \\\n",
    "ON affiliations.professor_id = professors.id \\\n",
    "JOIN uni.organizations \\\n",
    "ON affiliations.organization_id = organizations.id \\\n",
    "JOIN uni.universities \\\n",
    "ON professors.university_id = universities.id \\\n",
    "LIMIT 10; \\\n",
    "\"\n",
    "res = conn.execute(stmt)\n",
    "res.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "res = conn.execute(stmt).fetchall()\n",
    "# Create a DataFrame from the results: df\n",
    "df = pd.DataFrame(res)\n",
    "\n",
    "# Set column names\n",
    "df.columns = res[0].keys()\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 2/3**\n",
    "\n",
    "* Now group the result by organization sector, professor, and university city.\n",
    "* Count the resulting number of rows.\n",
    "\n",
    "```sql\n",
    "-- Group the table by organization sector, professor and university city\n",
    "SELECT COUNT(*), organizations.organization_sector, professors.id, universities.university_city\n",
    "FROM affiliations\n",
    "JOIN professors\n",
    "ON affiliations.professor_id = professors.id\n",
    "JOIN organizations\n",
    "ON affiliations.organization_id = organizations.id\n",
    "JOIN universities\n",
    "ON professors.university_id = universities.id\n",
    "GROUP BY organizations.organization_sector, professors.id, universities.university_city;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "SELECT COUNT(*), organizations.organization_sector, professors.id, universities.university_city \\\n",
    "FROM uni.affiliations \\\n",
    "JOIN uni.professors \\\n",
    "ON affiliations.professor_id = professors.id \\\n",
    "JOIN uni.organizations \\\n",
    "ON affiliations.organization_id = organizations.id \\\n",
    "JOIN uni.universities \\\n",
    "ON professors.university_id = universities.id \\\n",
    "GROUP BY organizations.organization_sector, professors.id, universities.university_city \\\n",
    "LIMIT 10; \\\n",
    "\"\n",
    "\n",
    "res = conn.execute(stmt).fetchall()\n",
    "df = pd.DataFrame(res)\n",
    "df.columns = res[0].keys()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Instructions 3/3**\n",
    "\n",
    "* Only retain rows with \"Media & communication\" as organization sector, and sort the table by count, in descending order.\n",
    "\n",
    "```sql\n",
    "-- Filter the table and sort it\n",
    "SELECT COUNT(*), organizations.organization_sector, \n",
    "professors.id, universities.university_city\n",
    "FROM affiliations\n",
    "JOIN professors\n",
    "ON affiliations.professor_id = professors.id\n",
    "JOIN organizations\n",
    "ON affiliations.organization_id = organizations.id\n",
    "JOIN universities\n",
    "ON professors.university_id = universities.id\n",
    "WHERE organizations.organization_sector = 'Media & communication'\n",
    "GROUP BY organizations.organization_sector, \n",
    "professors.id, universities.university_city\n",
    "ORDER BY count DESC;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = \"\\\n",
    "SELECT COUNT(*), organizations.organization_sector, professors.id, universities.university_city \\\n",
    "FROM uni.affiliations \\\n",
    "JOIN uni.professors \\\n",
    "ON affiliations.professor_id = professors.id \\\n",
    "JOIN uni.organizations \\\n",
    "ON affiliations.organization_id = organizations.id \\\n",
    "JOIN uni.universities \\\n",
    "ON professors.university_id = universities.id \\\n",
    "WHERE organizations.organization_sector = 'Media & communication' \\\n",
    "GROUP BY organizations.organization_sector, professors.id, universities.university_city \\\n",
    "ORDER BY count DESC LIMIT 10; \\\n",
    "\"\n",
    "\n",
    "res = conn.execute(stmt).fetchall()\n",
    "df = pd.DataFrame(res)\n",
    "df.columns = res[0].keys()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**The professor with `id` 538 (different for the downloaded dataset) has the most affiliations in the \"Media & communication\" sector, and he or she lives in the city of Lausanne. Thanks to your database design, you can be sure that the data you've just queried is consistent. Of course, you could also put `university_city` and `organization_sector` in their own tables, making the data model even more formal. However, in database design, you have to strike a balance between modeling overhead, desired data consistency, and usability for queries like the one you've just wrote. Congratulations, you made it to the end!**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Certificate\n",
    "\n",
    "![](https://raw.githubusercontent.com/trenton3983/DataCamp/master/Images/into_to_relational_databases_in_sql/2020-03-05_certificate.jpg)"
   ]
  }
 ],
 "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.8.5"
  },
  "toc-autonumbering": true
 },
 "nbformat": 4,
 "nbformat_minor": 4
}