Intro to Databases

Learning Goals

• Describe a database and its uses in various applications
• Define relational data
• Create a Database with Postgresql and PgAdmin

Warmup

Let’s think about some differences between our Message Logger application, and an application like Instagram. Instagram allows users to log in on multiple devices, see other user’s posts, and track how many likes a post receives.

With a partner, brainstorm how instagram is able to do this - how does instagram ‘remember’ users and their posts? how does instagram keep track of who has liked a post?

Data, Information, and Databases

Nearly all applications require some data and information to be useful to its users, and many applications need to store data for future use.

Data refers to a collection of facts: "Kat", "Davis", 22, "Denver, CO". Data on its own is not super useful to consumers - there is no context for a piece of data.

When we give context to data, we create information: firstName: "Kat", lastName: "Davis", age: 22, birthCity: "Denver, CO".

As developers, what we focus on with data and information is how to store information in a Database so that it can be used later. Databases allow us to Create, Read (review), Update and Destroy (delete) records.

There are two main types of databases - table (SQL), and non-table (noSQL). At turing, we are going to focus mainly on SQL databases - our data will be organized into tables, and will be easy to scale.

Data Tables and Relationships

As noted before, we have all of the data that we need to fulfill orders for this shop, but we have a lot of extra or duplicated information. This makes it difficult to answer questions like:

• How many customers do we have?
• What is the total for each order?

A better way to organize this data would be to break it into separate tables. Each table should contain data related to a single idea. When we have split the tables, we can then add a relationship between the two tables.

First, let’s split the data into two tables: Customers and Orders

Since we have split the data into two tables, we need a way to relate each table. Specifically, we need a way to know which customer an order belongs to. We will create this relationship by using primary keys and foreign keys:

To create these relationships, we need every table to have a primary key. A primary key is typically an Id for a specific record, and it will always be unique - no two records will ever share the same primary key.

We link the two tables by using a foreign key on one or more tables. In this case, we use Customer Id as a foreign key on the Orders, that refers back to the primary keys from Customers.

Create Database

So far, we have been working with representations of what data might look like inside of a database. We have seen data organized into tables, using primary and foreign keys to relate to records in other tables. Now, we are going to create a database. This database will live on your computer (it will be accessible only by you, or someone with your laptop).

Download PostgreSQL

There are many different databases that are available. During your time at Turing, we will primarily work with PostgreSQL.

1. Download the Windows x86-64 PostgreSQL Installer
   • Choose the installer for the most current version (the highest number).
   • It will take a few minutes for the installer to download.
2. Open the installer file to start the setup Wizard.
   
3. Click ‘Next’ through the wizard, keeping all default settings. You will need to enter and confirm a password on the 4th or 5th step - this is the only customization you will do. Please use password123 as your password because we will be working in groups so other people will see your password.
4. On the last step, before you click ‘Finish’, uncheck the box so that Stack Builder is not launched upon completion.
5. Open PgAdmin in preparation for the next section

If you have any issues during this installation, reach out to an instructor!

Create a Database with PgAdmin

Once in PgAdmin, we are going to create a database for our customer and order information.

1. Right Click on Databases and Create a Database
2. Give your database a name and Save
3. If successful, your pgAdmin should look like:

Now that we have a database created, we need to add two tables to our database for customers and orders

Create a table for Customers and Orders

Continue working individually, using your small group for support!

1. Click on your Database to highligh it, then click the icon for Query Tool

The Query Tool is where we will interact with our database. Today, you are going to be copying the commands that we give you; we are going to dig deeper into SQL commands in later lessons!

1. In the query window, recreate the command below - be very careful of spelling and semicolons! When you have created the query, click the ‘play’ icon to run the query.
   
2. Again, In the query window, recreate the command below - be very careful of spelling and semicolons! When you have created the query, click the ‘play’ icon to run the query.
   
3. To verify that you have created your tables appropriately, run this query: SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;. Your output should look like this:
   

If you run into any error messages or issues - reach out to an instructor!