> 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 ![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2F6tTz30QltbIss9pTa6Zi%2Fimage.png?alt=media\&token=50ac6ce6-3bbd-459a-81f3-4176d54d66ff) {% 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 %} ![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2F85rXe7BEYx9aTL25TL9u%2Fimage.png?alt=media\&token=4490f226-1c96-424f-b2d5-a3733d9319f5) ## 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 ![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2F2UUZKeHClJ3XwQck6LBl%2Fimage.png?alt=media\&token=88048269-c197-4fd7-af90-3fb9cbe46eb3) * 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 ![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2F4h9OYruoosAD9emlAFwD%2Fimage.png?alt=media\&token=04d91c03-3e30-4099-a954-d7b77725060e) ### DataTypes * Databases have datatypes just like any other programming language * In our case Postgres: ![-](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FbRQ9kTy2maDMkSWekLsf%2Fimage.png?alt=media\&token=ddb638c9-45a4-4fff-bb7a-792b80a5979b) #### 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 ![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2F87SoDRG8rhdMRLdRAsws%2Fimage.png?alt=media\&token=16ea1abb-a65f-4e0f-a2c6-6ef6f545416f) * 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 ![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FT69FjKFOtEfWD6321Yfe%2Fimage.png?alt=media\&token=b6a74991-3028-4154-ae62-735991940c81) ### Unique Constraints * A UNIQUE constraint can be applied to any column to make sure every record has a unique value for that column ![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2Fxdly2Ho3ZfNkJqSTUopK%2Fimage.png?alt=media\&token=1ed9929d-589b-48c0-9ead-2d39683a46fd) 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 ![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FGMeDoctB9vSnAFNZUaU1%2Fimage.png?alt=media\&token=ffcdffbd-adf8-4afd-88d3-869fd3bf1ddc)