Your Introduction About SQL/SQLite and Relational Databases
In a Pirate-Adventure Explanation Style
Anything that collects and organizes data is, as per definition, a database. A good example is a simple data spreadsheet or a plain text file. Also, an XML- or CSV-File is covered by this definition.
But you aren’t here for any wishy-washy definitions.
You want the narrowed-down definition in a professional context. To be precise, when you think about databases, especially the one called SQL, you are talking about a relational database management system (RDBMS).
Making a quick conversive link to non-relational databases. In terms of SQL, an RDBMS is simply a database that holds one or more tables
having relations to each other. Those hold each entry as their own document.
The Reason behind RDBMS
You don’t only get tables with records inside. You will also get a whole language or command set to query for the data.
Also, if you use full SQL (MySQL, MS SQL Server, Maria DB, etc.), you get a server that handles asynchronous and multitasking events.
SQLite is not capable of this management, but it is far simpler to set up. Also, the way SQLite saves the entire database is kept simple. It is just a file with the *.db extension.
You can easily share and collaborate.
Components of a Relational Database
A relational database consists of two components: Basic Components and Metadata.
The basic components are tables and records. In contrast, records are the entries of a table and are divided by the table columns. In contrast, metadata consists of conditions or keys and indexes.
Keys are linkers between tables, and they specify a column of each table to be linked together. These allow the tables to link certain records together. With these links, you can find specific related data of table A in table B.
In figure 1 the table
COMMITTED_CRIMES has a link to the table
CRIME and even this table links back to
Why should we want to link data from two tables, if we could then have just one table? — This is a good question, matey!
The Separation of Concerns
Take a look at the following example table out of my SQisLand — Pirate Adventure Course.
It has 7 columns and defines a bar on a sailing ship. Where every order has its’ own entry.
We can see the
Order Date, the
CrewMember_ID, the exact
Order QTY and even if it is already
This lets you check detailed information about the orders made during the heavy drinking evening on the sailing ship.
And this is the point. Only the orders!
Do you see the numbers below the column header CrewMember_ID? These are the links to another table because we don’t want to mix up the orders with unnecessary information about who ordered the next round of rum, for example. But you should still be able to comprehend if we need to know who it was.
Some of them might be troublemakers and should not be served anymore. Not to mention having a riot on the ship.
For detailed information about the ones who ordered, we got another table, the Crew Member Archives. The link is made between the
CrewMember_ID of both tables.
Your Motivation Behind
It is obviously better to separate different types of information. Therefore they belong in different tables.
This is called separation of concerns.
I will show you now what would happen if you have it all in just one table.
This table has more columns than before. To be precise, the number of columns is not the problem.
As you can see, everybody has made just one order so far.
Imagine now the crew has a good evening and wants to celebrate. The number of orders would rise drastically! Adding up more orders leads to redundant entries with marginal changes in specific columns.
The result is a bloated table and filled with redundant entries that are difficult to maintain.
And that is not everything. It also takes unnecessary storage space.
And what do you do if some of the crew members changed their job after the party or fell into the water being drunk? Then someone has to update all rows. I would go off my duty if this happens!
Using relational tables then means change one record of the Crew Members Archives table.
This is the true advantage of SQL and relational databases.
You can then explore both tables again with the true defining command of SQL- the JOIN command.
Typical Examples of Database Usages
Don’t you go away from this article without knowing some examples where databases are heavily used!
Example 1: A Travel Agency offers flights and hotels, and users want to receive only offers for the famous German Island Mallorca. This Agency then queries a database table for the record of the column:
Example 2: A student directory has the task of sending out reminder emails to the pity students who haven’t paid their semester fees yet. Normally, a clerk must scan the existing excel file and write a letter to all students. Having a program connected to a database could automatically send out reminder emails or even print out the letters if you do it the postal way.
Example 3: My favorite MMORPG World of Warcraft (of the past) or any MMORPG makes use of databases. Imagine a player kills an enemy, and this player will receive a reward (loot). Loot varies depending on the enemy type (loot table), and to get the loot, the client sends out a request to the server, which is responding with an answer from the queried loot table.
- Relational Databases consists of one or many tables
- Tables can be linked together, mostly better should be linked
- Tables have columns and rows to represent data sets
- There is a difference between SQL and SQLite (setup, asynchrony, multitasking, server, and non-server based)
- Relational databases are straightforward to understand
If you enjoyed the examples inside this article, a treasure chest full of them is awaiting you if you subscribe to my newsletter to get an hour-long taste of freedom!
Already over 10000 Pirates have visited SQisLand with great reviews!