- Spring Cleaning (Installing PostgreSQL)
- Database Concepts
- Entity Relationship Diagram
- Intro to SQL
For Mac:
Install Postgres with Homebrew:
brew update
brew install postgres
Check version
$ postgres --version
> postgres (PostgreSQL) 11.2
For WSL/Ubuntu
Sourced from Here:
Install PostgreSQL using WSL
Installation
sudo apt update
sudo apt install postgresql postgresql-contrib
Check Version
psql --version
What Is a Database?
Some common definitions:
A structured set of data held in a computer, especially one that is accessible
Put another way:
It’s a structured system to put your data in that imposes rules upon that data
a structured set of data held in a computer, especially one that is accessible
Why Do We Need a Database
We need an organized way to store data permanently, for it to be persistent
Common Misconceptions
We often think of words like Oracle, SQL Server, MongoDB as databases. But, they are actually a way of managing different databases. Specifically, they are known as a DBMS or a Database Management System
Visually:
We can think of a DBMS such as MySQL or PostgreSQL as:
Going further, we can express the flow of data with:
Why might a DBMS be important?
Fundamentally, DBMS's are advantageous for:
- Reducing Data Redundancy
- Data Sharing
- Data Integrity
- Data Security
- Backup and Recovery
- Privacy/Authorization
- Backup and Recovery
- Data Consistency
Let's explain a couple of these topics above that are critical to databases:
Another way of ensuring that the data is accurate and consistent.
Any change or database transaction must change the affected data only in allowed ways.
- Relational
- Hierarchial
- Network
- Object Oriented
- NoSQL
We are only going to focus on relational database management systems (RDBMS)
- Most commonly used
- Understand this, and the other types are easier to learn
We are most likely going to use PostgreSQL.
Source: Lingo
Query: An information request from a database
Transaction: A sequence of queries that perform the desired task
Schema: The structure of a database. A logical blueprint of how the database is not only constructed, but how things are related to each other.
Distributed vs Centralized: A centralized DB only has one database file, kept at a single location. A distributed one is built of multiple database files stored in multiple locations
Scalability: The ability for a database to handle a growing amount of data