Your Introduction About SQL/SQLite and Relational Databases

In a Pirate-Adventure Explanation Style

Illustration Made By Author

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.

Figure 1 Example is taken out of my SQL & SQLite Course

In figure 1 the table COMMITTED_CRIMES has a link to the table CRIME and even this table links back to PUNISHMENT.

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_ID, the Order Date, the Delivery, the CrewMember_ID, the exact Order, the Order QTY and even if it is already Delivered.

Example is taken out of my SQL & SQLite Course

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.

Example is taken out of my SQL & SQLite Course

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.

Example is taken out of my SQL & SQLite Course

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.

Example is taken out of my SQL & SQLite Course

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.

Picture from office-loesung.de

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: Travel_Destination.

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.

Logo by Activision Blizzard (All Rights Reserved)

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.

Takeaways

  • 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
Illustration Made By Author

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!

The SQL Pirate Adventure, where you set sails to SQisLand to meet some shady characters and have a pleasurable time together!

Already over 10000 Pirates have visited SQisLand with great reviews!

--

--

--

Are you someone who is curious about programming languages and who wants to start coding? Are you a beginner or intermediate coder? Well if all the answers are positive, you have come to the right place — Welcome to the Arnold Code Academy. Newsletter: https://bit.ly/3g9mHQ2

Recommended from Medium

Automated VM provisioning with Packer

Install SonaType Nexus 3 On Ubuntu 20.04 LTS

Web 1 Kaspersky Industrial CTF 2018

Control & Management of Microsoft Viva Topics

12 Designs From One!!!

Your Secret Interview Question is Bullshit

Certified Secure Software Lifecycle Professional (CSSLP)- Security Architecture

Validating SMILES with RDKit, PySMILES, MolVS, and PartialSMILES

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Arnold Abraham

Arnold Abraham

German software engineer who helps aspiring coders to learn software development enjoyably where fun & emotions come first to learn on the fly 😉 arnoldcode.com

More from Medium

Using In-Memory Databases in Windows — SQL Query Caching

Generic Type Inference through Function Arguments in TypeScript

Exploring Functional Programming

The Magnificent Five: List of Object-Oriented Programming Languages