What is a database?

WHAT IS A DATABASE?

Nowadays we regularly interact with databases. For example when we register a new user to a website, we define a user name and a password among other values. Where do you think these data are stored? Correct! These data values are stored in a database. Moreover, every time you log into the website using your previously registered user, the website sends a query to the database to check if the user exists, and to verify if the password is correct. You probably saw that many webpages display your first name after you logged in. This is another example of an interaction between web application and database: to obtain the first name of a registered user, after the user login, the webpage asks the database for the first name of the registered user. The same idea applies to other data related to the user like birthday, to display a Congratulations message!

A database is a piece of software mainly designed to store and retrieve data. There are many different databases, like we have cars from different branches, we have different databases like: MySQL, Postgresql, Oracle, SQL Server among many others. Some of them are free like MySQL and Postgresql, others are not. At this point, it is important to clarify that the correct term to refer to these databases is “Database Management Systems”, or just “DBMS”. While all DBMS are different, they all have something in common: Almost all DBMS use SQL as a language for data retrieval and manipulation. SQL is a standard language used for data retrieval and also for data modification and deletion. As SQL is a language adopted by many DBMS, if you learn SQL, you can apply your knowledge to many different databases or DBMS. Any person can learn SQL, you don’t need to be a nerd or a rocket science expert to write a simple SQL query, and when you finish to read this article, you probably know a few things about SQL.

Another important point to understand is “What kind of data is stored“? Well, when we install a DBMS, the database is empty, there is no data stored in it. Of course we can store any kind of data: data about a customer, about products, sales or even more sophisticated data like spatial data or geographic data. However before storing any data we need to define a data model. A data model is like a description of what data we are going to store, it is like a map describing how we plan to organize the data inside the DBMS. For example if we have a website, and we like to store the registered user names with their passwords and date of registration we can define the following data model:

Table: USERS
Columns: username, password, register_date

The above data model is very simple, it only has one table. Usually, real life data models have hundreds of tables, even thousands. So, let’s expand our data model. Assuming we want to keep a register of every login intent to our website, we should add the next table:

Table: LOGIN_INTENTS
Columns: username, login_datetime, success

Great! We now have two tables in our data model. This reduced data model can give you an idea about how a data designer creates a data model. Data model design is a complex task, however in simple terms, one can say that goals need to be defined as data values first in the database before the designer creates then the corresponding tables to put these data in. All these tables are related to one another, allowing navigation between tables. In our example one can see that both tables USERS and LOGIN_INTENT are connected by the username column. We come back to this point in a future article.

Let’s check another important point of databases. A database is like a library with lots of books of many different areas, writers and epochs, all of them perfectly organized in different bookcases. However a library needs readers asking for books because without readers the library cannot work. Well, databases are similar, they need clients asking for data. Database clients are other pieces of software different than the DBMS, they can be web applications (that can be websites as well as mobile web application) written in php, java, ruby on rails, or any other language, or can be desktop applications written in any language. The client is also known as the “frontend” part and the DBMS is called the “backend” part.

We are getting to the most exciting part of databases. How do you think that clients communicate with DBMS? Yeah!!, SQL is the answer. All the traffic between the application and the database is SQL and data. The application sends SQL to the DBMS and the DBMS sends data as a result back to the application side. It is possible to create really complex SQL queries, many application developers love that, because they can save many code lines of complex data search and calculations on the application side by using a SQL query to ask the database to do the complex data search and calculations. Fortunately SQL has a smooth and easy learning curve, and you can do your first steps in SQL in minutes. Let’s see how we can manipulate data stored in our model:

Assuming this is our USERS table

USERNAME

PASSWORD

REGISTER_DATE

Jean1987@gmail.com

UGVV67!

Jan, 10 2016

Dominic_w@yahoo.com

HTGAF$00!!

Sep, 25 2016

thisisme@theworld.com

UHGFTR67!!

Jan, 06 2017

And we want to obtain the list of registered users, then this is the correct SQL:

SELECT username FROM users
Jean1987@gmail.com
Dominic_w@yahoo.com
thisisme@theworld.com

Assuming we want to obtain the list of users registered on ‘Sep, 25-2016’

SELECT username FROM users WHERE register_date = ‘25-09-2016’
Dominic_w@yahoo.com

Let’s do a little bit more complex query: Obtain the username and register_date of users registered during 2016.

SELECT username, register_date FROM users
WHERE register_date >= ‘01-01-2016’ AND register_date <= ‘31-12-2016’
Jean1987@gmail.com Jan, 10 2016
Dominic_w@yahoo.com Sep, 25 2016

If you understand the previous SQL you can translate this example to any other data model. Perhaps you have access to a database (at work, school or university), where you can create your own SQLs to search data. Don’t be afraid, write SQL commands beginning always with the word SELECT, and you won’t change any data. If readers show interest in a future article we will cover how to navigate between different tables. Please leave your comments. As a new challenge for our readers, let’s assume we want to verify if users registered in 2016 log into the application during 2017, or even more complex we want to get the list of users who, one year after registering, still log into the application. We can create SQLs to reply to these questions, however we need to create 2 tables: USERS and LOGIN_INTENTS. We will show you how to do this in a future article. If you can’t wait you can try to do it on your own!