# Data Modeling
---
## Relationships
- One to One
- One to Many
- Many to Many
---
## One to One
When a row in a table is related to only one other row in another to table.
ex: A counry can only have one president and president can only represent one country.
---
## One to Many
A row from one table can have multiple matching rows in another table.
ex: One project can have multiple engineers. Or One class room can have many students.
---
## Many to Many
A row from one table can have many matching rows in another table. Vice Versa
A pet can have many owners and a pet owner can have many pets
---
### Why do think we want to consider relationships?
### What are some relationship examples you can think of?
---
## Keys
```
Primary Key && Foreign Key
```
---
# Databases
---
## Definition
A digitally stored organized collection of information that represents data used to interact with an applicaition. Small databases are usually stroed in the file system while large database exost on servers (Clusters, Cloud Storage)
---
## Postgres and Beekeeper
- Install Postgres
- Install Beekeeper
---
## Create A Database
`CREATE DATABASE dbname` || `createdb dbname`
---
## Create a Table
```
create table fakeTable (
id serial primary key,
name varchar(50) not null,
email varchar(50) not null
);
```
---
## Insert Items Into a Database
```
insert into table_name (column1, column2)
values (value1, value2)
```
---
## Querying Database
```
SELECT * FROM fakeTable
SELECT count(*) FROM fakeTable
SELECT * FROM fakeusertable WHERE id=5
```
---
## Upate Items in Database
```
UPDATE table_name
SET column1 = value1
WHERE condition;
```
---
## Delete Items from Database
```
DELETE FROM table_name WHERE condition;
```
---
## JOINS
```
INNER JOIN
OUTTER JOIN
```
---
## INNER JOIN
Return all records from both tables having matching values.
```
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
```
---
## LEFT JOIN
Returns all records from the left table, and the matched records from the right table
```
SELECT column_name(s)
FROM left_table
LEFT JOIN right_table
ON table1.column_name = table2.column_name;
```
---
## RIGHT JOIN
Returns all records from the right table, and the matched records from the left table
```
SELECT column_name(s)
FROM left_table
LEFT JOIN right_table
ON table1.column_name = table2.column_name;
```
---
## Create create db using VS Code
---
# File Setup
```
schema.sql // will hold our schema
seed.sql // will insert our data
```
---
## NPM Scripts
```
"db:create": "createdb fakedb",
"db:destroy": "dropdb fakedb",
"db:init": "psql -f schema.sql fakedb",
"db:seed": "psql -f seed.sql fakedb",
"db:reset": "npm run db:destroy; npm run db:create && npm run db:init && npm run db:seed",
```
{"metaMigratedAt":"2023-06-17T02:48:21.569Z","metaMigratedFrom":"Content","title":"Data Modeling","breaks":true,"contributors":"[{\"id\":\"5e29e175-4809-4add-a41e-e8982dab52a9\",\"add\":3462,\"del\":610}]","description":"One to One"}