> For the complete documentation index, see [llms.txt](https://docs.arkannis.net/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.arkannis.net/database/general-sql/database-introduction.md).

# 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

{% hint style="info" %}
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.
{% endhint %}

#### NoSQL

* MongoDB
* DynamoDB
* Oracle
* SQL Server

### Relational Databases & SQL

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

![](/files/9nVfkFrCi7nui8b5q2u3)

{% hint style="warning" %}
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
  {% endhint %}

![](/files/4kVPW9cjyBZz4sqjnOYN)

## 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

![](/files/thy0s3hEbQpixMbe18r0)

* 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

{% hint style="warning" %}
It is very important that you think about these relationships before hand so you can design an efficient database
{% endhint %}

### 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

![](/files/G82ELwZx4T5rp0GgelLF)

### DataTypes

* Databases have datatypes just like any other programming language
* In our case Postgres:

![-](/files/QjXTbxQDPa7t1AIwVvBX)

#### 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

![](/files/alNhcTX7bimKdRLWPfia)

* 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 

![](/files/bzYuPeiqC6yUPxpReVWB)

### Unique Constraints

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

![](/files/yeVGSmi8Ad8zRHz6FZHy)

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 <mark style="color:orange;">`NOT NULL`</mark> constraint can be added to the column to ensure that the column is never left blank

![](/files/A8mOMraaOdj4VNT7ALZg)