Creating A Database

class: middle,

# .eight[CSET 155:]
## .eight[Database Design]

---
class: middle

# Creating A Database

---
class: middle

# Agenda

- [ ] [Review Terms](#terms)
- [ ] [Install PostgreSQL](#install)
- [ ] [Make a Database!](#sql)

---
name: terms

# Terms

1. Database
2. Database Management System
3. Data Type
4. CRUD Operations
5. Data Modeling

---

# Definitions

1. An organized collection of data.
2. Software that interacts with the database, applications, and users.
3. The data's role; how it is stored and how it can be processed.
4. Create, Read, Update, Delete
5. A formal process for finding required data and relationships.

---
class: middle, center

# .seven[Generic Category]
## .eight[Specific Example]
### .nine[Aspect of Example]

---
class: middle, center

# .seven[Entity Type]
## .eight[Entity]
### .nine[Attribute]

---
class: middle, center

# .seven[Sheet  ->  Table]
## .eight[Row  ->  Record]
### .nine[Column  ->  Field]

---
name: install

# Install PostgreSQL

```sh
$ brew update
$ brew install postgresql
$ brew services start postgresql
$ psql -V
psql (PostgreSQL) 12.1
```

---

# Install pgAdmin

```sh
$ brew cask install pgadmin4
```

pgAdmin is a web interface for our Postgres databases.

---

# Connect pgAdmin to Postgres

.eleven[You should only have to do this once.]

- Open pgAdmin using Spotlight
- Click "Add New Server"
- General > Name: type .fourteen[localhost]
- Connection > Hostname/addres: type .fourteen[localhost]
- Add your **computer** username and leave password blank
- Click "Save"

---

# Quick Overview of pgAdmin

- Server: a running instance of the DBMS, .eight[PostgreSQL], on our machine called .eight[localhost]
- Database: we have one, the default database called .eight[postgres]
- Schemas > public > Tables: we don't have any yet

.eleven[Don't worry about the other stuff yet.]

---

# Quick Overview of pgAdmin

- Postgres needs to be running for pgAdmin to work
- pgAdmin needs to be running for the web app to work
- Save a bookmark of the web app to open it again, if needed
- Or use the pgAdmin icon in the menu bar

---
name: sql

# Your First Database!

- Make sure the .eight[postgres] **database** is selected
- Click the .eight[Query Tool] icon at the top right of the sidebar
- This opens a new tab to execute queries.

```sql
CREATE DATABASE analysis;
```

- Hit the execute button, the play symbol, or F5

---

# Your First Query!

- .eight[Query]: A statement to extract data or perform an action.
- .eight[Keywords]: SQL syntax, should be uppercased
- .eight[Values]: data that you define, like names, strings, numbers, etc.
- .eight[Semicolon]: ends a SQL statement. Don't forget it!

---

# Did it work?

- Queries usually return .eight[Messages]:
```
Query returned successfully in 79 msec.
```
- In the sidebar, right click .eight[Databases]
- Click .eight[Refresh] to get the sidebar to update
- Ta-da!

---

# Connect To Your New Database

- Queries need to be executed on a database
- Certain exceptions...
- Hit the "X" icon to close the Query Tool tab
- Select your new .eight[analysis] database in the sidebar
- Reopen the Query Tool

---

# Creating a Table

Where data lives and relationships are defined

- Create the .eight[columns] for attributes, almost like variables
- Assign each column a .eight[data type]
  - For example, dates can be stored as YYYY-MM-DD

---

# Your First Table

```sql
CREATE TABLE users (
  id bigserial,
  name varchar(50),
  email varchar(50),
  registered date
);
```

---

# Your First Table

- Small example just to get us started
- 2 Keywords + Value: CREATE, TABLE, users
- Newlines and tabs are for readability
- Each column name represents one discrete data element defined by a data type
  - bigserial, varchar, date, etc.
  - We'll learn more later

---

# Inserting Data

```sql
INSERT INTO users (name, email, registered)
VALUES ('Amy', 'amy@example.com', '2017-10-30'),
  ('Bob', 'bob@hotmail.com', '2018-05-22'),
  ('Carmen', 'carmen@aol.com', '2015-08-01'),
  ('Dave', 'dave@fastmail.com', '2019-07-30');
```

---
class: middle, center

# Did it work?

```
INSERT 0 4

Query returned successfully in 106msec.
```

---
class: middle, center

# Why do the dates look funny?

# [Because](https://xkcd.com/1179/)

---
class: middle, center

# Why did we not add an ID?

# bigserial

_Don't worry, we'll learn what this is later_

---
class: middle

# .fourteen[Review Lab]

# Data Modeling in a Spreadsheet

- Get together in your groups
- Share your spreadsheet
- Explain it to your group
- Help each other improve your data models