In this first topic of our first lesson, we will start by going over some basic database concepts and database types.
I’m sure you’ve all worked with databases in some capacity and are ready to dig into SQL, but let’s take a brief step back and start by taking a broader perspective. We’ll look at the types of databases and where SQL lies in the world of databases and what characteristics it has.
A database could be thought of as any software implementation of organizing information that offers access, management and a method to update that information.
There are multiple types of databases out there, but by far, SQL is the most broadly used, so it’s critical that you have a good working knowledge of it.
We are going to learn that SQL is a relational database and then learn more on what that means.
But before we get there, if SQL is a relational database, what other types of databases are there?
Let’s take a quick peek at a couple different types of database structures.
One type of database is a hierarchical database, where the information is organized in a tree-like structure.
An easy example for data structured in this manner could be XML, or “Extensible Markup Language”, which is commonly used for structured data to be transferred through flat files.
Another type of database that has recently been getting attention are graph databases.
Graph databases are databases that use a graph structure to organize information in a semantic nature using its nodes and edges. Between graph databases, Neo4j has been popular, so you may have heard of it.
Now that we’ve taken a look at other types of databases, let’s now get to the most dominant type of database, the relational database!
Relational databases are databases that structure information in tables. Tables with rows and columns are a very intuitive and a common way to organize data.
For instance, when we have information about people, we could easily think of rows of people with columns being the name, email, age, phone number and so on. We could think of a school having a database of their teachers, organized in a table.
How relational databases could organize information and the relationship between the individual data points is quite intuitive. You could also notice that it’s looking quite similar to what you see in spreadsheets, such as Microsoft Excel or Google Sheets.
Let’s take a quick look at NoSQL and then come back to relational databases to dig in a bit deeper.
NoSQL stands for “Not Only SQL.” It is a more flexible approach to a database that could house not only SQL style relational database structures in tabular form, but also incorporate a variety of non-relational database structures such as graph formats, document based data as well. So it could be thought to be more flexible and scalable. MongoDB is a well known NoSQL database.
Now that we’ve gone through some different types of databases, let’s dig back into relational databases, which is what SQL is, and some characteristics of it.
Going back to our school example, let’s say that we have a table of teachers. And we also have a table of students. Now we want to express the relationship between the teachers and the students. A very straightforward method to do this is to assign IDs, or a “key” to the teachers and also the students. Now the databases could house the relationship between the two tables.
Relational databases provide the basis of how information is stored and managed in a highly secure and consistent way with a robust foundation to implement different rules that we will go through as we progress in this course.
An acronym that is used often to describe four important characteristics of relational databases is ACID.
It stands for Atomicity, Consistency, Isolation, and Durability.
Atomicity means that changes to data are executed in a grouped manner as if they are a single operation if there is more than one change that needs to be made. So all the changes are performed, or none of them are. Any separate threads accessing the data will see all the grouped changes already made, or none at all.
Consistency means that the database as a whole could always be upheld to a certain set of rules.
Isolation keeps the effect of an execution unnoticeable to others until it is committed. This avoids confusion.
Durability makes sure that data changes become permanent once the transaction is committed.
Now that we’ve had a quick review of the database landscape by reviewing different database types and learned the characteristics of relational databases and why they are so popular, we’re ready to embark on our SQL journey!
You can watch a video of a brief walk through of this topic here.