Database for newbies: Theorethical Approach

Either if you have just started learning programming or if you already have some experience with it, I'm pretty sure you already asked yourself (or others) what a database really is, how you can deal with it, what is the difference between relational and non relational databases and what they are used for.

When it comes to programming, feels like our doubts are endless and sometimes we can feel lost in this sea of information. I will talk about some concepts that every beginner should know to create its first database. I hope to teach you at least something about database, one of the most important parts of the softwares.

Table of contents

• What is database?
• Types of database
  • Relational
  • Non-Relational
• What is SQL?

What is database?

Before everything, it's important to understand what is data. Well, it is a value, like 21. But this data won't have a meaning unless we organize it, creating an information. For example, this number, all alone, could be an age, could be the total people that logged on your website or even your state number. Now if I say to you that we have the data: age = 21 , we can pretty much understand that the value 21 is someone's age. That's what we call information.

Knowing that, I can tell you that database is a collection of organized information. I like to imagine that databases are similar the process of moving to a new home. For example, when we are moving, it is normal to pack our things inside boxes. We would have a box for books, one for clothes and another for electronics. And in the end, we put everything inside a truck and take it to our new home.

In this example, our database would be the truck, because we have many boxes of specific things - a collection of organized information. This information is structured and normally stored in a computer system. (We use databases to store data, access and maintain it. )

To control a database, we are going to use what we call as Database Management System (DBMS), softwares used to visualize the database's tables, its information, and where we can also run queries (commands to do actions on the database) and many other things. It's basically a interface between the user - the person using the database - and the database itself.

We can say that every time we create, delete, update and read - and so much more - we will deal with a DBMS.

Examples of Database Management System:

• MySQL
• SQL Server
• Oracle Database

There are so many options and we should choose the one that fit us and the project better.

Types of database

The most important types of database, the ones every beginner should know are:

• Relational Database
• Non-relational Database

Relational

This type of database is structured using tables with columns and rows, like the image below, and we use SQL to deal with this structure.
In a table, we will have:

• Columns, which defines what every piece of data is (ID, Name, Age...) and its type;
• Row, which holds the data given.

Every row in the table is a record with an exclusive ID, which we call as a key. In this table, for example, the key would be the numbers 1, 2, 3... We can see that every record has its own key, used to identify itself.

We have at least three types of keys that every beginner should acknowledge:

• Key : it is basically one or more columns in a table that will be responsible for identifying in an exclusive way the rows.

• Primary Key : this type will ensure uniqueness to the register, will refer the register on a relationship between tables and it is indexed, which increases the efficiency of our DBMS.

• Foreign Key : it is a column that will kind of connect the tables using the primary key of each of them. We use this type of key to create relationships between tables. It is important to understand that the table that has the foreign key is called reference table and the one that is being referred is called referenced table or father table.

Non relational

Non relational database, also called as NoSQL (Not Only SQL, nowadays), are a type of database that don't use the table, and its columns and rows, as an organization scheme. It offers more flexibility because you won't have to worry about creating a table as we have to do on relational databases.

The storage on NoSQL is optimized and it adapts itself to fit every need. For example, it is possible to storage data as key/value, as JSON documents or as graphics.

NoSQL databases were created to solve the problem people were suffering until the late 90's, which was the difficulty to design databases that could cope with the scale and agility needed using the relational database, that wasn't able to do it at that time.

One of the most important features of NoSQL is:

• Higher scalability
• No complex relationship
• Reduced costs

Non relational databases also have types, and two of them are:

• Key Value: the most common one. Every item on the database will be stored as an attribute name (or 'key') with its value. For example: {"name": "Maria"}. The key is "name" and the value is "Maria". The most used databases in these cases are Redis, DynamoDB and Berkeley DB.

• Document: stores each key with its document, and its principal feature is having all the information inside one document. This document normally is a JSON but it can also be a XML, for example. The most famous document database is MongoDB

There are a lot more information about this type of databases but this is the basics you have to know to start! Besides that, don't let this beginning stop you from digging deeper to learn more.

What is SQL?

SQL stands for Structured Query Language, a programming language used to work with databases created on the labs of IBM around the 70's. After a while, it became a pattern when it comes to data management.

We use this programming language to run queries on the database and we can retrieve, create or delete information from the database using the most various parameters. We just have to follow the syntax, just like any other programming language.

SQL has many commands but the most important ones are:

• SELECT: search rows on the database table.
• INSERT: insert new rows on the table.
• UPDATE: used to update the data that already exists on the table.
• DELETE: delete a register.

It is important to say that we use this commands with other parameters so we can make the queries very specific. And it is a convention to use SQL on Capslock.

SQL also has some conditions that we can add on our query in order to modify the registers that will be returned, like:

• FROM: used to say what table will be consulted.
• ORDER BY: used to organize the data returned in a specific order.
• WHERE: used to specify the conditions of the data to be return. For example, if we want to see the users we have on our table that are older than 18 years old, we would use the 'where'.

There are many other commands in SQL but this is the very basic we need to know to create our first table and learn the commands to create our first CRUD, which stands for Create, Read, Update and Delete.

And that's it!

Through this article we learned the basics of database, like what it is, it's types and what is SQL. Now, we must learn how to create one, topic covered on the next article of this series.

This content was created based on my notes during the 100 days of code, in which I learned about database, PHP and Laravel, so I am really proud of it, since I knew almost nothing about it before starting the challenge. Also, feel free to ask me questions if needed!

And thanks to the people that reviewed my article and helped me to get here:

• @danielhe4rt
• @cherryramatis
• @reenatoteixeira