Lab 10 - Create and manage a digital bookstore collection.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Week 10 - Create and manage a digital bookstore collection\n",
    "*© 2021 Colin Conrad*\n",
    "\n",
    "Welcome to Week 10 of INFO 6270! Last week marked an important milestone, in the sense that you completed the second course unit on core data science skills. Starting this week, we will have three labs on \"other skills\" that are valuable to data scientists but do not constitute the core practices. This week we will change gears into a topic that is important to most data-related careers: SQL. Many former students have stressed the importance of having basic knowledge of this topic in the workforce.\n",
    "\n",
    "Many of you are likely to have covered SQL in other courses and this lab assumes that you have seen it before. If you have not explored SQL before, I strongly recommend that you spend a few hours working your way through this week's reading. The Khan Academy [Intro to SQL: Querying and managing data](https://www.khanacademy.org/computing/computer-programming/sql) is an excellent course and may be interesting to you even if you have covered it before. It is important to qualify that they use MySQL, which is ever-so slightly different from the tool that we will use this week: SQLite. The principles are the same regardless.\n",
    "\n",
    "**This week, we will achieve the following objectives:**\n",
    "- Create a SQL table with Python\n",
    "- Conduct simple SQL queries\n",
    "- Create relations between tables\n",
    "- Conduct a more complex SQL query\n",
    "- Create and query a relational table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Case: Kobo Inc.\n",
    "[Rakuten Kobo Inc.]( https://www.kobo.com/) is a Canadian company which sells e-books, audiobooks and e-readers and is a competitor to Amazon's bookselling business. Founded as a subsidiary of the Indigo Books and Music Inc., in 2010 Kobo was spun off as an independent company in order to form partnerships with other book retailors which whished to compete with Amazon. The company was later acquired by Japanese e-commerce giant Rakuten and today competes with Amazon for e-book dominance in Canada and Japan. \n",
    "\n",
    "Like many e-commerce companies, Rakuten Kobo keeps a database of products for download. Though it is unclear whether they use an SQL database specifically, it is likely that the company stores its inventories using a relational database framework. As an e-commerce company, they maintain an inventory of thousands of items, many of which are related to transactions which happen every day. In this lab we will explore hypothetical data structures which may be similar to the technology that Rakuten Kobo uses on the backend. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective 1: Create an SQL table with Python\n",
    "If you have [kept up with this week's readings](https://www.khanacademy.org/computing/computer-programming/sql), you likely already know that SQL stands for _Structured Query Language_ and is the standard method for communicating with a relational database. Databases are the backbone of virtually every information system used in businesses and organizations. Relational databases are the most common variety of database; it is not difficult to see why using and managing SQL is important. In fact, _**MI, MDI and MBA students who work for companies or governments consistently express to me that this skill is among the most important skills for securing gainful employment*_. If you have not done a module on SQL, I strongly recommend that you at least look through the Khan Academy materials!\n",
    "\n",
    "It will probably be no surprise to you by now that you can also connect to an SQL database using Python. In this lab we use a simple SQL database called [SQLite](https://www.sqlite.org/index.html). Without going into the details of SQLite, just know that this is a fully functional SQL database that is optimized for small datasets. We will use Python' `sqlite3` library to create, query and retrieve data from an SQLite database. You can read the [documentation for this library here](https://docs.python.org/2/library/sqlite3.html).\n",
    "\n",
    "As usual, we will start by importing the `sqlite3` library. This library will allow us to connect to an SQLite database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3 # import SQLite"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is important to remember that `sqlite3` is not the database itself but the means to connect to the database. To make `sqlite3` work we must first create a *connection* to a database. Though it does not yet exist, we will connect to `kobo.db` in your data folder. When `sqlite3` connects to a database which does not yet exist, it will automatically create a new database for you.\n",
    "\n",
    "If you are interested, you can open the `/data` folder and see the database that was just created!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('data/kobo.db') # this is your database connection. We will create a database in the data folder"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Normally we would connect to an SQL database using a command line, which would allow us to execute SQL commands, similarly to the Khan Academy example. Similarly, SQLite must connect to the database and it does so using a series of `cursor()` methods. We can create a cursor object by declaring it as below. This will allow us to use python to execute SQL queries of our database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c = conn.cursor() # this is the tool for interfacing between SQLite and Python "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Good work! We are now connected to a database. Let's talk about that a bit before proceeding.\n",
    "\n",
    "### A bit more about relational databases\n",
    "Relational databases have been around a long time (in computer years) and were originally proposed by E. F. Codd (1970). What makes relational databases different from data tables (which we have explored so far) is that they actually consist of many tables which *relate* to one another in a variety of ways. For example: \n",
    "- The Government of Canada may maintain a table of citizens and a table of passports. Each passport belongs to exactly one citizen and each citizen may have a passport. This is an example of a one-to-one relationship.\n",
    "- A library database could have table of patrons and a table of books. This is a one-to-many relationship. Each patron could borrow many books, though each book only lent to one patron at a given time.\n",
    "- A university database may have table consisting of courses and a table consisting of students. This is a many-to-many relationship. Each course contains many students and each student takes many courses.\n",
    "\n",
    "Each entity in a relational database is typically represented by a key. For now, we will only focus on the `primary key`, a unique indicator of each entry in a table. Primary keys are the way that you can navigate relationships between tables.\n",
    "\n",
    "Let's start by creating a table. In SQL you can create a table using the CREATE TABLE command. We will create a table for e-readers called *readers* which will contain the following information:\n",
    "- id (integer, primary key)\n",
    "- date_added\n",
    "- model (unique)\n",
    "- description\n",
    "- quantity\n",
    "- price\n",
    "\n",
    "### SQLite data types\n",
    "The following command executes the order to create this table. Note that the command to create table column must also specify the data type. The following are some SQL data types for your reference with their python equivalent in brackets:\n",
    "- integer (integer ... aka complete numbers)\n",
    "- real (float ... aka decimal value)\n",
    "- text (string)\n",
    "- blob (binary number)\n",
    "- null (an empty value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this is the SQL query to create the readers table\n",
    "c.execute('''CREATE TABLE readers\n",
    "             (id integer primary key, date_added text, model text unique, description text, quantity integer, price real)''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, after executing the table we must *commit* the change to the database and *close* our connection. The following code accomplishes this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit() # commits the query to the database\n",
    "conn.close # closes the connection to the database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data will be saved for subsequent sessions. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## *Challenge Question 1 (2 points)*\n",
    "Modify the code below to create a table for *books* which contains the information below. We will return to this later.\n",
    "- id (integer, primary key)\n",
    "- date_added (text)\n",
    "- author (text)\n",
    "- title (text)\n",
    "- publisher (text)\n",
    "- ISBN (text, unique)\n",
    "- price (real)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective 2: Conduct simple SQL queries\n",
    "Once we have some tables up and running we can start to add values to the tables. Similarly to other SQL databases, we do this by using the `INSERT INTO` command. Adding values to an SQLite database is a matter of using this command as well as the corresponding values.\n",
    "\n",
    "The following command will `INSERT INTO` the database information about the [Kobo Forma](https://ca.kobobooks.com/products/kobo-forma?store=ca-en&utm_source=Kobo&utm_medium=TopNav&utm_campaign=Forma). Execute the cell below to add the command to the cursor. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('data/kobo.db') # connect to the DB\n",
    "c = conn.cursor() # create the cursor\n",
    "\n",
    "c.execute(\"INSERT INTO readers VALUES (1, '2020-02-22', 'Forma', 'To make the reading experience better for ravenous booklovers who read for hours on end, and want a lightweight, portable alternative to heavy print books, we’ve delivered our most comfortable eReader yet with waterproof reliability, the choice of landscape or portrait mode, and the expanded access of book borrowing. Storage size available in 8GB and 32GB.',10000, 299.99)\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `INSERT INTO` command will add an entry to the table as long as the entry exactly matches the requirements of the table columns. However, the way that we inserted the value is not ideal. Typically it is not advisable to specify the primary key value for an entry. Primary keys are designed to auto increment.\n",
    "\n",
    "Let's delete the value that we just created by using the `DELETE` command. This command will remove a value from a table depending on whether that value meets the specified condition. The simplest way to delete the value is to tell SQL to delete all values from readers where id = 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute('''DELETE FROM readers WHERE id=1''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To properly add a value to the readers table we would typically specify the values that we wish to add using the INSERT INTO command. By doing this there is less room for error."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute('''INSERT INTO readers(date_added, model, description, quantity, price) \n",
    "          VALUES ('2020-02-22', 'Forma', 'To make the reading experience better for ravenous booklovers who read for hours on end, and want a lightweight, portable alternative to heavy print books, we’ve delivered our most comfortable eReader yet with waterproof reliability, the choice of landscape or portrait mode, and the expanded access of book borrowing. Storage size available in 8GB and 32GB.',10000, 299.99)''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So far so good, however manually specifying an INSERT INTO command can become burdensome. In SQL it is often important to enter many entries into the database. In SQLite and Python it is a matter of creating a list of queries. For example, we could add information about Kobo's other reading products by creating one such list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "readers_to_add = [\n",
    "    ('2020-02-22', 'Libra H20', 'The perfect balance between innovative digital technology, a comfortable reading experience, and modern design is here. Offered in black or white, and with four colourful accessories to pair with, Kobo Libra H2O lets you choose the model that best suits you. Read longer than ever before with its comfortable ergonomic design, the option for landscape orientation, and easy-to-use page-turn buttons. With ComfortLight PRO and full waterproofing, Kobo Libra H2O lets you enjoy your books day or night, in any environment. Kobo Libra H2O is designed for a better reading life.',10000, 199.99),\n",
    "    ('2020-02-22', 'Clara HD', 'Kobo Clara HD is the perfect reading companion for any booklover. It always provides the best light to read by with ComfortLight PRO, and a natural, print-like reading experience on its superior 6” HD screen. Easily customizable features help customers new to eReading to read the way they prefer. With 8 GB of on-board memory and the ability to carry up to 6,000 eBooks, Kobo Clara HD always has room for your next escape',10000, 139.99)\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We then want to use the sqlite3 cursor's `executemany` method to execute multiple queries. The following code achieves this. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.executemany('INSERT INTO readers(date_added, model, description, quantity, price) VALUES (?,?,?,?,?)', readers_to_add)\n",
    "conn.commit() # commits the query to the database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selecting data\n",
    "When working as a data scientist or analyst, perhaps the most important SQL skill to have is to query the database. In SQL, queries are typically executed using the `SELECT` command. The command `SELECT * FROM readers`, for instance will retrieve all of the entries from the `readers` table. Try executing the cell below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute(\"SELECT * FROM readers\")\n",
    "print(c.fetchone())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Gotchya!** This code indeed retrieves all of the entries, but only prints one of them. This is because I used the `fetchone()` method to print only a single entry from the database. This is a surprisingly helpful function in most circumstances because you will not want to print all of the contents of a hundred-thousand entry database!\n",
    "\n",
    "When the cursor retrieves query entries it saves them in a list behind the scene, similarly to the `csv` library previously explored. If we wanted to print multiple entries we would loop through them using a `for` loop, just like in previous weeks. Try executing the cell below to retrieve all of the readers entered so far."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in c.execute(\"SELECT * FROM readers\"):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In addition to executing entire entries, SQL can be used to select only specific columns. To do this, you would replace the `*` with the fields that you desire to retrieve. The following code retrieves the `id` and `model` from the `readers` table. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in c.execute(\"SELECT id, model FROM readers\"):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQL does not order itself the same way as a CSV spreadsheet, so often you need to specify the order that you desire to retrieve the information in. You can use the ORDER BY command to achieve this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in c.execute(\"SELECT id, model FROM readers ORDER BY id\"):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## *Challenge Question 2 (1 point)*\n",
    "Write a script that [inserts information about this book](https://www.kobo.com/ca/en/ebook/pride-and-prejudice-32) into your database. Consider today to be the `date_added`; you can retrieve the rest of the necessary data from the web page using the link provided."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## *Challenge Question 3 (1 point)*\n",
    "Print a line that retrieves the `author` and `title` of the book that you just entered. Refer back to the examples for more information on how to do this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## *Challenge Question 4 (1 point)*\n",
    "Using a list, add two more books to add to this table. They can be any books from the Kobo website."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.executemany('INSERT INTO books VALUES (?,?,?,?,?,?,?)', books_to_add)\n",
    "conn.commit() # commits the query to the database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective 3: Create relations between tables\n",
    "As mentioned earlier, perhaps the most powerful feature of relational databases are the relationships that tables have to one another. So far, we have not specified relations between the `readers` and `books` tables. We do not need to because these two entities do not interact in meaningful way.\n",
    "\n",
    "Users with Kobo accounts will contain information about the e-readers that they own. If we wanted to create a table for users, we would probably create a table that looks something like the following."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute('''CREATE TABLE users(id integer primary key, date_joined text, email text)''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, this does not contain any information about other tables! What we need to relate to other tables is a `FOREIGN KEY`, a value from another table. Foreign keys are usually primary keys from another table that can be used to link two tables together. They need to be specified when creating a table that relates to another.\n",
    "\n",
    "Let's drop that bad table before proceeding."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute('''DROP TABLE users''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To specify a foreign key in SQLite, you must first specify the value in your table and then declare it as a foreign key. The code below creates an integer called userreader, and then declares it to be a foreign key and `REFERENCES` it to the id column in the `readers` table. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute('''CREATE TABLE users(\n",
    "id integer primary key, \n",
    "date_joined text, \n",
    "email text, \n",
    "userreader integer, \n",
    "FOREIGN KEY(userreader) REFERENCES readers(id))''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now enter an entry into the users table which identifies the users' primary e-reader. The following line creates an a user entry and connects that user to the e-reader with the ID 2 (aka Libra H20). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute('''INSERT INTO users VALUES (1, '2020-02-22','colin.conrad@dal.ca', 2)''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also query the user table to check our sanity. Let's do that before proceeding."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute('''SELECT * FROM users''')\n",
    "for row in c:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## *Challenge Question 5 (1 point)*:\n",
    "Take a momemnt to generate some more data. Create three more users and add them to the database. At least one of the users should have the Clara HD reader assigned to them. We will return to this later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective 4: Conduct a complex SQL query\n",
    "Great! It's now time to move on to something slightly more complex. Let's start by adding a few more users. I am sure that these email addresses may be familiar to some of you!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_to_add = [\n",
    "    ('2010-01-26','harry@hogwarts.co.uk', 1),\n",
    "    ('2010-01-26','hermione@hogwarts.co.uk', 3),\n",
    "    ('2010-01-26','ron@hogwarts.co.uk', 2),\n",
    "    ('2010-01-26','ginny@hogwarts.co.uk', 2),\n",
    "    ('2010-01-26','severus@hogwarts.co.uk', 2),\n",
    "    ('2010-01-26','dumbledore@hogwarts.co.uk', 2),\n",
    "    ('2010-01-26','luna@hogwarts.co.uk', 3)\n",
    "]\n",
    "\n",
    "c.executemany('INSERT INTO users(date_joined, email, userreader ) VALUES (?,?,?)', users_to_add)\n",
    "\n",
    "c.execute('''select * from users''')\n",
    "for r in c:\n",
    "    print(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are now ready to see relational tables in action. So far we have only executed queries from single table. Now that we have a table with a foreign key we can create a JOIN query. These types of queries draw data from multiple tables.\n",
    "\n",
    "Let's create a query that shows us the `date_joined`, `email` and the name of the `model` that they own. The query below achieves this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute('''SELECT users.date_joined, users.email, readers.model\n",
    "FROM users \n",
    "JOIN readers\n",
    "ON users.userreader = readers.id''')\n",
    "\n",
    "for r in c: # print the query results\n",
    "    print(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_Magic right?_ The beauty of relational databases is that you do not have to duplicate data because you are can retrieve the necessary data from other tables. This is extremely helpful when managing larger databases.\n",
    "\n",
    "Let's unpack this query a bit. The query contained:\n",
    "- `SELECT` which specified the data that you wished to retrieve and the table the data belongs to\n",
    "- `FROM` specifies the main table (we could have chosen the `readers` table here as well in this case)\n",
    "- `JOIN` specifies the table that you wish to match\n",
    "- `ON` specifies the relation between the two tables, in this case they are linked by the `userreader` foreign key\n",
    "\n",
    "Take a while to study this before proceeding. Consider trying different `JOIN` queries."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Constraining your query with `WHERE`\n",
    "There is one more thing that we should discuss before wrapping up this objective. Nearly every `SELECT` query can be limited by specifying a `WHERE` condition, which helps you limit the amount of data retrieved. For instance, we might wish to query only those users who own the Clara HD model of reader. To do this we would change the query to something like the following: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute('''SELECT users.email, readers.model\n",
    "FROM users \n",
    "JOIN readers\n",
    "ON users.userreader = readers.id\n",
    "WHERE readers.id=3''') # you could also specify readers.model=\"Clara HD\" in this instance\n",
    "\n",
    "for r in c:\n",
    "    print(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`SELECT` queries constrained by `JOIN` and `WHERE` are among the most common type of queries used by business analysts in industry. Using these queries, you can retrieve desired data and generate reports for analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## *Challenge Question 6 (2 points)*:\n",
    "Create a query which retrieves the following data:\n",
    "- The users' email address\n",
    "- The model that they purchased\n",
    "- The price of the model that they purchased\n",
    "- Only select users who have purchased the Libra H20 model\n",
    "\n",
    "Be sure to print your results for easy grading!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Objective 5: Create and query a relational table\n",
    "The final thing worth mentioning is that SQL databases do not always manage so-called \"one-to-many\" relationships, such as the relationship between users and e-readers. These types of relationships can be managed with a foreign key and they are much simpler.\n",
    "\n",
    "Often however, you will be faced with a \"many-to-many\" relationship. In our Kobo example, we could envision a scenario where each owns many books, but each book is owned by many people. A foreign key alone will not help us here. To illustrate this issue, let's start by adding some more books. Execute the cell below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "books_to_add = [\n",
    "    ('2020-02-22','J. K. Rowling','Harry Potter and the Philosophers Stone', 'Pottermore Publishing','9781781100219', 10.99),\n",
    "    ('2020-02-22','J. K. Rowling','Harry Potter and the Chamber of Secrets', 'Pottermore Publishing','9781781100226', 10.99),\n",
    "    ('2020-02-22','J. K. Rowling','Harry Potter and the Prisoner of Azkaban', 'Pottermore Publishing','9781781100233', 10.99),\n",
    "    ('2020-02-22','J. K. Rowling','Harry Potter and the Goblet of Fire', 'Pottermore Publishing','9781781105672', 10.99),\n",
    "    ('2020-02-22','J. K. Rowling','Harry Potter and the Order of the Phoenix', 'Pottermore Publishing','9781781100240', 10.99),\n",
    "    ('2020-02-22','J. K. Rowling','Harry Potter and the Half-Blood Prince', 'Pottermore Publishing','9781781100257', 10.99),\n",
    "    ('2020-02-22','J. K. Rowling','Harry Potter and the Deathly Hallows', 'Pottermore Publishing','9781781100264', 10.99)\n",
    "]\n",
    "\n",
    "c.executemany('INSERT INTO books(date_added, author, title, publisher, isbn, price ) VALUES (?,?,?,?,?,?)', books_to_add)\n",
    "\n",
    "c.execute('''select * from books''')\n",
    "for r in c:\n",
    "    print(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Many-to-many relationships cannot be expressed with two tables. When faced with these sort of situations, you must create an intemediary table that contains records of the relationships between the entities. Let's create a new table called `userbooks`. This table can consist of a series of `user.id` and `book.id` pairings held as foreign keys. We can keep this one simple because its entire purpose is to hold those relationships."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# creates the intermediary table\n",
    "\n",
    "c.execute('''CREATE TABLE userbooks(\n",
    "userid integer,\n",
    "bookid integer,\n",
    "FOREIGN KEY(userid) REFERENCES users(id),\n",
    "FOREIGN KEY(bookid) REFERENCES books(id))''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Just like any other table, we can `INSERT INTO` the userbooks table values which correspond to the users' books. The following code should assign a relationship between user 1 (likely `colin.conrad@dal.ca`) and book 4, likely to be Harry Potter, if implemented correctly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute('''INSERT INTO userbooks VALUES (1, 4)''') # Colin owns Harry Potter and the Philosophers Stone"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similarly you can express the books which you would like to add using a list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "userbooks_to_add = [\n",
    "    ('1', '5'), # Colin owns Harry Potter and the Chamber of Secrets\n",
    "    ('2', '4'), # Justin Trudeau owns Harry Potter and the Philosophers Stone\n",
    "    ('2', '5'), # ... etc\n",
    "    ('2', '6'),\n",
    "    ('2', '7'),\n",
    "    ('2', '8'),\n",
    "    ('3', '4'),\n",
    "    ('3', '5')\n",
    "]\n",
    "\n",
    "c.executemany('INSERT INTO userbooks(userid, bookid ) VALUES (?,?)', userbooks_to_add)\n",
    "\n",
    "c.execute('''select * from userbooks''')\n",
    "for r in c:\n",
    "    print(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we can retrieve records from a many-to-many relationship by `SELECTING` from the intermediary table and `JOIN`ing on the other two tables. The code below should retrieve records for `colin.conrad@dal.ca`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.execute('''SELECT users.email, books.title\n",
    "FROM userbooks \n",
    "JOIN users\n",
    "ON users.id = userbooks.userid\n",
    "JOIN books\n",
    "ON books.id = userbooks.bookid\n",
    "WHERE users.id=1''') # you could also specify readers.model=\"Clara HD\" in this instance\n",
    "\n",
    "for r in c:\n",
    "    print(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## *Challenge Question 7 (2 points)*\n",
    "Let's try another complex query. Select the `user.email` and `books.title` for all users who own the Forma reader. Execute the cell below before conducting this query. **Hint:** the Forma reader's ID is 1."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Execute this cell"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "userbooks_to_add = [\n",
    "    ('4', '1'),\n",
    "    ('4', '2'),\n",
    "    ('4', '3')\n",
    "]\n",
    "\n",
    "c.executemany('INSERT INTO userbooks(userid, bookid ) VALUES (?,?)', userbooks_to_add)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Enter your code here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Other Stuff - Connecting to MySQL (not just SQLite)\n",
    "Finally, a final note on connecting to MySQL databases or other related tools. You can use Python to connect to MySQL environments easily using similar skills to what you have explored here. Python provides a [MySQL connector](https://www.w3schools.com/python/python_mysql_getstarted.asp) to establish a connection to a remote MySQL server. For example, the call below could be used to connect to establish a connection to a remove SQL server. \n",
    "\n",
    "I will provide a video demonstration of a connection to one of our research servers. You could similarly use this code to connect to your own MySQL server if you have one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# connect to a MySQL db by entering a host name. Be sure to install the MySQL connector before attempting it\n",
    "import mysql.connector\n",
    "\n",
    "# establish the connection\n",
    "mydb = mysql.connector.connect(\n",
    "    host=\"qsslab.mysql.database.azure.com\",\n",
    "    user=\"conradc@qsslab.mysql.database.azure.com\",\n",
    "    password=\"Zt9!L#lG2fsM\",\n",
    "    database=\"userdb_conradc\"\n",
    ")\n",
    "\n",
    "print(mydb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the remote connection established, you could then execute commands as you normally would. The code below was used to select all of the `tweets` from Colin's `tweets` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor = mydb.cursor()\n",
    "\n",
    "mycursor.execute(\"SELECT * FROM tweets\")\n",
    "\n",
    "myresult = mycursor.fetchall()\n",
    "\n",
    "for x in myresult:\n",
    "  print(x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## References\n",
    "\n",
    "Codd, E. F. (1970). A Relational Model of Data for Large Shared Data Banks. *Communications of the ACM. 13*(6), 377–387. doi:10.1145/362384.362685.\n",
    "\n",
    "W3SChools (2021). Python MySQL. Retrieved from: https://www.w3schools.com/python/python_mysql_getstarted.asp\n",
    "\n",
    "Khan Academy (2019). Intro to SQL: Querying and managing data. Retrieved from: https://www.khanacademy.org/computing/computer-programming/sql"
   ]
  }
 ],
 "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.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}