Relational Databases

What are they?

What makes them "relational"?

Relational Databases

Tables

Relational Databases

Relationships

Structured Query Language

Structured Query Language

Structured Query Language

SELECT id, name, email, phone FROM users;

SELECT [ a list of column names] FROM [a table name]

Getting Data Out

Structured Query Language

SELECT id, name, email, phone FROM users
WHERE id = 1;

SELECT [ a list of column names] FROM [a table name]

WHERE [some condition is true]

Specific Data Out

Structured Query Language

INSERT INTO users (id, name, email, phone)
VALUES (5, 'Aaron', 'aa@ron.com', '555-2156');

INSERT INTO [ a table name ] ([a list of column names])

VALUES ([comma-separated expressions in the same order as the column names])

Putting Data In

Objectives

1. Describe what a Relational Database is
2. Describe what SQL is used for
3. CRUD with SQL
4. Define a Schema with SQL

Reflect

What does CRUD stand for?

How do we filter queries?

How do we get data into the database?

Data Modeling

CREATE TABLE pokemon (
    id SERIAL PRIMARY KEY,
    name TEXT,
    pokedex_number VARCHAR(10),
    type1 TEXT,
    type2 TEXT
);

Defining A Table

Data Modeling

Data Types

• Serial
• Integer
• Numeric
• Text, Varchar
• JSON
• BLOB
• Timestamp
• Boolean (True or False)

Setup A Schema

CREATE DATABASE pokemon;

\c pokemon;

CREATE TABLE pokemon (
    id SERIAL PRIMARY KEY,
    name TEXT,
    pokedex_number NUMERIC,
    type1 TEXT,
    type2 TEXT
);

CREATE TABLE moves (
    id SERIAL PRIMARY KEY,
    name TEXT,
    type TEXT
);

$ psql -f schema.sql

$ psql

> \c pokemon

> \dt

> SELECT * FROM pokemon;

Delete A Schema

DROP TABLE pokemon;

DROP TABLE moves;

DROP DATABASE pokemon;

Objectives

1. Describe what a Relational Database is
2. Describe what SQL is used for
3. CRUD with SQL
4. Define a Schema with SQL

Pair:

20 Minuites

1. Create a schema.sql file
2. Create a database in your schema.sql file
3. From your book attributes, create a table and use the book's attributes as columns
4. Set the data types for your columns
5. psql -f schema.sql
6. Check to see that the tables were created in your database