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.
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.
Each of the four elements has a specific meaning. We call each one a data value.
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.
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.
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.
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.
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.
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.
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.
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.
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