#3 Relational Data & Models
SQL Crash Course #3
All the course material is stored in the SQL Crash Course repository.
Hi everyone! Josep and Cornellius Yudha Wijaya from Non-Brand Data here 👋🏻
As promised, today we are publishing the next two issues of our SQL Crash Course – From Zero to Hero! 🚀
I am sure you are here to continue our SQL Crash Course Journey!📚
If this is your first time or you’ve forgotten what we’ll cover, we will examine seven key SQL topics, each divided into multiple posts across Non-Brand Data and DataBites.
Remember last Thursday we already saw
📌 #1 What is SQL?
📌 #2 Why learn SQL?
Today’s turn is for:
📌 #3 Relational Data & Models by me in DataBites
📌 #4 Basic Commands by Cornellius Yudha Wijaya in Non-Brand Data
So, let’s dive in!
SQL & NoSQL Databases
Databases are the backbone of data science and application development, determining how data is stored, retrieved, and processed.
There are two main types of databases:
SQL (Structured Query Language) → Relational
NoSQL (Not Only SQL) → Non-Relational
They both serve different purposes depending on the nature of the data and the application requirements. This lesson delves into SQL databases, their characteristics, advantages, and real-world applications.
What is Relational Model?
Now you might be wondering... what is really a Relational Model?
So let's try to understand this concept easily.
The relational model organizes data into tables where each record is stored as a row and attributes are represented as columns.
Relationships between tables are established using keys, such as:
Primary keys (unique identifiers for each record)
Foreign keys (linking records across tables).
This structure ensures data consistency and allows for complex queries by connecting related information efficiently.
So let's go step by step...
Imagine we run a couple of Newsletters (*wink wink*). We will have a Newsletter table that will register all our newsletters and then a second Posts table that will collect all published posts.
So now we have two main tables: NEWSLETTERS and POSTS.
In this case the records of both tables are easily relatable using:
The id column in the NEWSLETTERS table, which uniquely identifies each newsletter. This serves as the Primary Key.
The newsletter_id column in the POSTS table, which associates each post with its corresponding newsletter. This acts as the Foreign Key.
1.1 Characteristics of SQL Databases
In SQL databases, the relational model serves as the conceptual foundation, organizing data into structured and interconnected tables. SQL databases are made up of schemas and tables:
Schema: defines the structure of the database, including tables, their fields, data types, available values, and relationships.
Tables: The foundational units of a database, representing entities with rows containing individual records for each entity and columns storing specific attributes about the entity.
Keys and Relationships
Primary Key: A unique identifier for each record in a table (
idin the Product Table).Foreign Key: A column that establishes a relationship between two tables ( product
_idin a Sales table referencing Products).Supports one-to-many and many-to-many relationships between tables.
Normalization
The process of organizing data to reduce redundancy and improve consistency.
SQL, or Structured Query Language, serves as the interface for managing these databases, enabling the creation, retrieval, updating, and deletion of data through standardized commands. A list of these commands and SQL’s capabilities can be seen in my SQL Basics Cheat Sheet.
1.2 Advantages of SQL
SQL provides a standard set of commands for defining, querying, updating, and administering data in a relational database management system (RDBMS). Key SQL operations include:
SELECT (querying data)
INSERT (adding new records)
UPDATE (modifying existing records)
DELETE (removing records)
When using these commands, SQL ensures that ACID (Atomicity, Consistency, Isolation, and Durability) properties are maintained.
This ensures the reliability and consistency of database changes.
Atomicity: Ensures that each transaction is treated as a single, indivisible unit of work. If any part of the transaction fails, none of its changes are applied to the database. Either the entire transaction is committed, or none of it takes effect.
Consistency: Guarantees that a transaction transitions the database from one valid state to another, preserving predefined integrity constraints. The database remains consistent both before and after the transaction.
Isolation: Ensures that concurrent transactions do not interfere with each other, preventing inconsistencies. Each transaction executes as if it were the only one running, maintaining data integrity even in a multi-user environment.
Durability: Ensures that once a transaction is committed, its changes are permanently recorded in the database. The effects persist even in the event of system failures, such as crashes or power outages.
These fundamental properties of SQL databases ensure that database systems are reliable and consistent, even in the case of unexpected events or system failures.
1.3 Common SQL Databases
There are three commonly used RDBMS:
MySQL: An open-source RDBMS, now maintained by Oracle, known for its speed, reliability, and ease of use. It is widely used in LAMP stacks (Linux, Apache, MySQL, PHP/Python/Perl) and is a popular choice for small to medium-sized web applications.
PostgreSQL: A powerful open-source object-relational database system with advanced capabilities, including support for custom functions, complex queries, indexing, and ACID-compliant transactions. It is well-suited for large-scale applications, data warehousing, and geospatial data processing.
Microsoft SQL Server: A proprietary RDBMS developed by Microsoft, available in multiple editions (Express, Standard, Enterprise) to cater to different use cases. It seamlessly integrates with the Microsoft ecosystem, making it a strong choice for businesses of all sizes, from small enterprises to large corporations.
How to Get Started 🚀
Over the coming weeks, we’ll guide you through:
✅ SQL Fundamentals
✅ Intermediate SQL
✅ Advanced SQL
✅ Database Operations
✅ Writing Efficient Queries
Once you grasp the basics, practice is key! Start working on real-world projects and solving hands-on data problems.
What’s Next? ➡️
This is the first of many posts about the upcoming SQL Courses. It will only explain what SQL is in its crude form.
To get the full experience and fully immersed in the learning:
👉 Subscribe to Databites.tech (By Josep)
👉 Subscribe to Non-Brand Data (By Cornellius)
👉 Check out the SQL Crash Course GitHub repo
👉 Share with your friend and whoever needs it!
🗓️ Every Thursday, you will have two new issues in your inbox!
Let’s dive in and make SQL less scary, more fun, and way more useful! 🚀
Josep & Cornellius