Learn SQL #2 : How is data stored in a database?

table_icon

Now that we know how the database system works, and how data is retrieved from the database, it would be time to understand how is data stored in a database.

Inside a database, data is stored into …

Where is data stored in a database?

Inside a database, data is stored into tables.

As we mentioned in the previous post, the S in SQL stands for structured.

This means that all the data has to be stored in a standardized manner. This is why tables have been created.

Tables are the simplest objects (structures) for data storage that exist in a database.

tableee

For example, the picture above is a screenshot of a table that has stored general information about some cars.

What is a field?

A field is actually the attribute name (column name) and in the case of our above table the fields are:

  • brand
  • horsepower
  • color

These fields are used only for easier identification of what values are stored in that column.

What is a record or row?

A row also called a record, is each individual entry that exists in a table. Each column represents an attribute of the car, and each row stores all of the attributes of that specific car.

For instance, row number one store’s information for the first car, about which we can see that it is a blue BMW with 185 horsepower, the second row shows us that the car is black, made by Mercedes and has 155 horsepower. (all of this data is fictive and used for example purposes only)

You can create any table you want with SQL, to keep multiple types of data (text, numbers, dates, etc.).

As I have said, data is stored in a database in tables, but tables are not the database.

There might be a small confusion and tables would be mistaken for databases or a database for a table. 

The correct way to see a database is like a folder. And in that folder, you have text files with information. In the case of a database, these files with information are actually the tables that contain data. So, the table is just like a file with text inside a folder.

Just like going to a library and you look for a book, you go to the section where your book is placed, by genre, and then look for its name to find and read it. The same logic you apply when looking for specific information inside a database (library). You go to a specific section (table) and retrieve the book you want (the row or rows with information).

Now that we know that data is stored inside tables, you must also know that the fact that there is no data in a column is still saved inside a database. This is why NULL exists.

What is NULL?

It is very important to understand what NULL is and what it means and why it exists.

NULL values are different than zeros or fields that have white spaces. A field that contains a NULL value actually means that when the row was created, there was no data provided for that field.

null

For example, if a user has to insert information about the last car, let’s say a Jaguar, but forgets to input a value for horsepower, then the value for horsepower will be stored as NULL in the table.

It would be incorrect to automatically put 0 in that column because then you could retrieve data and say that the car makes 0 horsepower.

This is also different than what can be seen below.

all_cars

If the user that inserted the information for the Bugatti did not know the color of the car, it would be best to insert NULL rather than do as what happened for the Volvo car, whose color is whitespace.

Just to reiterate and refresh our memory, a NULL value means that the field for that row has been created with no value, which is different than a 0 (zero) or a white space.

10 thoughts on “Learn SQL #2 : How is data stored in a database?”

  1. When you refer to a white space, is that the same as an empty string (”), or is that separate? What differentiates whitespace from null?

    Reply

Leave a Comment