Introduction to Databases and an Example of a Data Table

SQL Tutorials 7 min read
data table

Introduction to Databases and an Example of a Data Table

7 min read
Blog / SQL Tutorials / Introduction to Databases and an Example of a Data Table

When you become a successful SQL programmer, working with multiple data tables will be a walk in the park. One thing you’ll notice when studying programming languages is that the concepts you encounter are interrelated. This means by focusing on a single topic we will never get the full story. In this regard, SQL makes no exception.

An Example of a Data Table

The table you see in the picture below contains the customer sales data of a furniture store.

Sales data of a furniture store table

And, this is how we should read this information:  Purchase number 1 was registered on the 3rd of September 2016, and it refers to Customer number 1 buying item A_1.

Data Values

Each of the four elements has a specific meaning. We call each one a data value.

Data values

Records

All four data values make up one record. It corresponds to a row of the table. Therefore, the data values on the first-row form one record, while the selected data values regarding purchase number 2, form another record. The terms row and record can be used interchangeably.

Data Values make up a record or row

data-science-training

Fields

Besides the rows, you can see the data is separated into four columns, or fields. A field is a column in our example of a data table containing specific information about every record in the table. It doesn’t matter if they are 10, 10 thousand, or 10 million or if they have a certain purchase number, date of purchase, customer ID, and item information.

A column in a table is a field

Stored Tabular Data

When the data you have is organized into rows and columns, this means you are dealing with stored tabular data. This is important to mention because you will often see in the literature that database management relates to data stored in tabular form.

Reference to Other Tables

Here comes the more interesting part. In this table, we know nothing about a customer besides their ID. The information about customers is stored in another example of a data table, called “Customers”. There are several fields, such as first and last names, gender, e-mail addresses, and the number of times customers have filed a complaint.

A data table with a customers details

Accessing Data About the Customers

So, what would the logic of that structure be? Every time we have a customer with an ID number 1 in the “Sales” table, we can refer to the customer with ID number 1 in the “Customers” table and see his name, email, and the number of complaints filed.

Connecting tables through customer ID

Getting Data About the Items

The same goes for the “Items” table. It contains the item code, product description, its unit price, ID, the name of the company that has delivered it, and the company headquarters’ phone number. Here, the connection between the “Sales” table and the “Items” table is not the customer ID, but the item code.

Connecting tables through the item code.

Why You Should Avoid Using Only One Table

Well, we could stuff this information into one table. Let’s take a look at the picture below.

A data table that contains too many fields

It is huge! Imagine what the table would look like if we had registered over 10 rows! My point is – there are too many columns, and it is hard to understand what type of information is contained in the larger table.

What is a Relational Database

Relational algebra allows us to use mathematical logic and create a relation between a few tables in a way that allows us to retrieve data efficiently.

Namely, these three tables – “Sales”, “Customers”, and “Items”– relate to the customer ID or the item code columns and form a relational database.

How three separate data tables connect with different information

And, importantly, each one bears a specific meaning and contains data characterizing it. One of the tables contains information about sales, the other, about customers, and the third about the items.

What are Entities 

For those of you who are interested in slightly more technical definitions, remember the smallest unit that can contain a meaningful set of data is called an entity. Therefore, the rows represent the horizontal in the table, the columns – its vertical entity. The table is a bigger data entity on its own. We can also refer to it as a database object. A single row of a table, being a single occurrence of that entity, we can also call it an entity instance.

Entities are the smallest unit that contain meaningful data

What Tables Consist of

To recap, remember that the data values in a row form a record in a data table. Each column represents a field that carries specific information about every record. A few related tables form a relational database.

You just gained the knowledge of working with databases you need before you begin coding in SQL. So, if you want to continue your theoretical preparation, feel free to jump into the next tutorial introducing you to the idea of declarative languages.

***

Eager to hone your SQL skills? Learn how to put theory into practice with our hands-on tutorials!

Next Tutorial: SQL as a Declarative Language

 

Leave a Reply

Your email address will not be published.

A Free Data Science Career Guide?

Check your email shortly!