Data Modeling and SQL | Beginner's Guide

Introduction Data Modeling

Certainly the subject of Data Modeling and SQL is one of the most important topics when we want to develop robust systems or applications.

Structuring your application's SQL data modeling can make all the difference in the future, saving work and bringing a lot of performance and scalability.

This Data Modeling and SQL content will explain the most important database fundamentals and present a step-by-step methodology to structure your business data and turn it into an application.

Watch the full video content here:

What will you learn?

• Fundamentals of Data Modeling and SQL
• Methodology how to model your database
• Conceptual, Logical and Physical Model in practice
• How to create an application from your database model

Database Fundamentals

Before we get into the subject of Databases, you need to understand some important concepts:

• Die: A value in its individual form
• Database: Set of data organized and related to each other
• Information: Important insights from a database that helps strategic decisions
  • How many courses did you sell in the month? 
  • What are the best months of the year for sales?

In order to manage and manipulate data in a system, we generally use a Database Management System, the most famous of which are:

• mysql
• NoSQL
• MongoDB
• firebase

To understand the difference between them, you need to understand what Relational Databases are and what SQL is.

Relational Databases and SQL

Relational databases are databases that store and manipulate data related to each other.

Tables are usually connected via primary and foreign key. The Primary Key (PK) is the identifier of that record in the table, and must be unique.

The foreign key (Foreign Key – FK) is the primary key of another table, so we can connect the data.

The most famous databases on the market are PostgreSQL, MySQL and SQLite.

To manipulate the information in the database we use SQL, which is how we do queries in our database.

Non-Relational Databases and NoSQL

NoSQL stands for Not Only SQL, which shows that NoSQL databases can have relations or not.

There are several ways to structure our data:

• document and collection
• Graphs
• Key Value
• Columnar

Our focus will be document and collection.. where we store data in “folders” which are collections and “documents” which are records.

The main NoSQL databases on the market are MongoDB, Firebase and Cassandra.

The focus of this content will be on SQL, see our NoSQL data modeling content later.

Methodology for Data Modeling

To perform data modeling from scratch, you need to follow these steps:

1. Requirements gathering;
2. Conceptual Model;
3. Logical Model'
4. Physical Model.

In the requirements analysis stage, a survey is carried out of all the functionalities that the software will do, all of which must be aligned according to the end user's vision.

In this data modeling content, it will not be the focus to explain the requirements gathering part, but it could be a future class here.

Let's start?

Let's start with the conceptual model!

Step 1 Data Modeling – Conceptual Model

To carry out the database modeling, we will use the case of No-Code Start-Up as an example, here in our community we have our Training (courses) and students can purchase these courses.

Let's understand how to model?

The first stage is the Conceptual Model, where we understand the general concept of the business and what will be the main data involved. We will take the following actions:

1. Define Entities
2. Set Attributes
3. Define Relationships
4. Build Final Conceptual Model - Entity Relationship Diagram (ERD)

The entities are all the main tables involved, such as “Students”, “Courses” and “Sales”. The attributes are the fields that we will have in these tables, the design will look like this:

From there we can define how the entities are related, for that we have some options:

• 1 to 1 relationship
• 1 to Many Ratio
• Many to Many Relationship

See some examples

Thus, our final conceptual model will look like this:

Step 2 Data Modeling – Logical Model

In the Logical Model, we will further detail the entities and attributes. We will also design the schemas and their relationships.

Before proceeding, we need to ensure that we are in accordance with the 3 Normal Forms.

Normalization is the optimization we perform on tables to reduce data redundancy, duplication and inconsistency. 

This way we can have a more consistent, organized and better performing data model.

• NF1 – The table must have only unique attributes, there cannot be multivalued attributes.
• NF2 – Attributes (non-key) depend only on the primary key
• NF3 – Attributes (non-key) must be independent of each other

Watch the video on YouTube about data modeling to understand with real examples.

From data normalization, we can proceed to the design of schemas and their relationships. The final drawing will look like this:

Step 3 Data Modeling – Physical Model

Step 3 is where we bring our data modeling to life, we must choose one of the DBMS on the market to create our database. It consists of 3 actions:

1. Choice of DBMS Technology
2. Create database with SQL
3. Bank management and maintenance

We will create all tables from SQL.

Finalization

I hope you enjoyed this data modeling content, for more details see our full video where we create an app from our ready-made database. The created application is made in 100% without programming, with Bubble.

If you want to know more about the nocode world, understand how to create an application without code.

To the next!

Matheus Castelo