Database introduction

What is a database?

• Is a collection of organized data that can be easily accessed and managed

• When it comes to databases we don't really interact with the database directly

• Instead we have a Database management system (DBMS)

• After which the DBMS will send the data over to the Database and return the results

• So... we always have a piece of software that interacts with the database

Types of Databases

Relational Databases

• MySQL

• PostgreSQL

• Oracle

• SQL Server

Note: Fundamentally all the Relational databases are the same at the core, however there are small differences on how SQL is implemented in each of the above examples.

NoSQL

• MongoDB

• DynamoDB

• Oracle

• SQL Server

Relational Databases & SQL

• Structured Query Language (SQL) is the language used to communicate with the DBMS

Each instance of any sort of Relational Database can be carved into multiple separate databases

• These databases are completely isolated from each other

• So we can have multiple apps with their own databases

Database Schema and Tables

Tables

• A table represents a subject or event in an application

What does this mean?

Let's say we are building a E-Commerce application

We are going to have a table representing each part of the application

• Users - Table for all users that have registered

• Products - Tables for all the products that we sell

• Purchases - Table for all the purchases made

• All these tables will form a Relationship (Hence Relational Database)

If you think about it, a user will purchase a product and they will all be linked

• Every purchase order has to be associated with a user account

• A purchase order also has a list of products that the user wants to buy

It is very important that you think about these relationships before hand so you can design an efficient database

Columns vs Rows

• A table is made up of columns and rows

• Each Column represents a different attribute

• Each Row represents a different entry in the table

DataTypes

• Databases have datatypes just like any other programming language

• In our case Postgres:

-

Why is this important?

• When you create a Column within a table, you need to specify what kind of DataType you want to use

Primary Key

• Is a column or group of columns that uniquely identifies each row in a table

• Table can have one and only one primary key

• The Primary key does not have to be the ID column always. It's up to you to decide which column uniquely defines each record

• In the below example, since an email can only be registered once, the email column can also be used as the primary key

Unique Constraints

• A UNIQUE constraint can be applied to any column to make sure every record has a unique value for that column

Primary Key has to be UNIQUE, but what happens when we have another row that needs to be unique?

• That's where the Unique Constraints come in

• In the above example we don't want duplicate names

• Once we apply the Unique Constraint, SQL will check to make sure that the new name we are trying to enter doesn't already exist in the database

NULL Constraint

• By default when adding a new entry to a database, any column can be left blank

• When a column is left blank, it has a null value

• If you need column to be properly filled in to create a new record, a NOT NULL constraint can be added to the column to ensure that the column is never left blank