Data Entity Relationships (ERD) Fundamentals: A Non-Chef Learns the Importance of Data Design

The Queenstown QRC data analysis course in New Zealand is coming to a close. Here's how I learned to establish data entity relationships in a low-cost school for non-professional chefs. Learn how to structure complex data with ERD fundamentals, the blueprint for data analysis. I'll also unpack my misconceptions.

The importance of understanding data entity relationships (ERD)

ERD fundamentals with hand-drawn data entity relationships

Looks complicated, right? The data entity relationship (ERD) above is a Queenstown, New Zealand QRC assignment. I wouldn't recognize it if I tried.

An entity relationship diagram (ERD) is a diagram that shows the relationships between data entities.Before you can analyze your data, you need to understand the relationships between the entities in your data so that you can properly analyze them and reach conclusions.

In other words, if the design is wrong, the results are not the right results. Just like when building a house, you need to make sure the plans are straight to build a strong building, the same is true for data analysis.

Entities, the heroes of the data world

Throughout the above, we keep referring to entities. Entity is a vessel that holds datafor example.

I didn't understand it even after hearing it a hundred times, but my tutor threw me an excel zipped file and asked me to solve it. It was only after I solved it that I understood the concept of Entity.

Multiple Excel files to understand data entity relationships

This was a data analysis assignment for a bike shop. There are nine files. The sharp-eyed among you may remember how many entities (squares) there are in the ERD photo above: nine.

Brands is one of those entities. Categories is also an entity, so that's what the vessel that holds the data is for. Let's open the Brands file.

Entity & Artribute

When we open the Brands file, we see two columns, Brand_id and Brand_name. These are called Artributes. They represent the granularity of the data contained in the Brand Entity.

In my data entity relationship, ERD, I expressed it like this

brands and his atritibute in a hand-drawn data entity relationship

You're probably wondering: how do I draw this? The ERD picture above was drawn by opening each of the nine files one by one. I know you're thinking, "Why do I have to draw this manually when I have AI?" That's what I thought at first.

However, I think it's a much better way to understand data entity relationships than to have an AI do it for you, so I've found that drawing them one by one is a much better way to understand them. Initially, I used the

If you look at the picture of Brands above, you'll notice that I've written PK (Primary Key) next to Brand_id, which is a numbered key that makes each piece of Brand data unique.

It's like everyone in the country has a different social security number, and that number is unique because it's different.

PK, FK And Bridge

Data entity relationship bridge table (bridge)

This time, in the Products entity picture, do you see the Product_id (PK), Brand_id (FK), and Category (FK)? You recognize the PK, but you also see the FK (foreign key), which in layman's terms means that it has the PK of another entity.

The Brand_ID is PK in the Brand entity, but when you import it into the Products entity, it says FK.

If you want to know the relationship between two data entities and they don't have any data in common, but you really need to know the relationship between them, you can create a Bridge entity.

Create a data entity relationship bridge table

The picture above is from another data analysis. For example, you want to know the relationship between Transaction and Product, but there is no common data between them.

In this case, create a new bridge entity called Transaction Details. To create it, get the respective PKs of the two data entities.

Separation	Meaning
Primary Key (PK)	The key that makes the entity unique
Foreign key (FK)	Keys that enable associations with other entities
Bridge Table	Entities that connect two entities

Types of Data Entity Relationships

Data entity relationships as seen in Power Bi

Now that you understand the data entities, let's talk about the relationships between them. The picture above shows the 9 files I received in the Power BIand you're looking at that relationship as an EDR.

If you look closely, you'll see a 1, a *, and an arrow between the entities.

Separation	1	*	Arrow
Meaning	one	many	Orientation

For example, let's look at the relationship between Brands and Products: Brands is labeled with a 1, Products is labeled with a *, and the direction is Brands -> Product. Let me make this into a sentence.

- A brand can have multiple product models.

The Apple brand can have multiple product models, such as iPads, iPhones, MacBooks, and so on, so there is a relationship of 1 : many.

Putting data entity relationships into words

A category can have multiple products
A single customer can have multiple order statuses
One order status can have multiple order products (order A can contain bikes 1, 2, and 3)
A single staff member can handle the status of multiple order items.
A product model can be sold multiple times
A product model can have multiple inventory records across multiple stores (even multiple specific inventory records, but only for one product model).
A store can have multiple staff members
A store can own multiple inventory records, but a specific inventory record belongs to only one store.

I drew my data entity relationships on paper like this and expressed them in sentences like this, and then compared them to the entity relationships in Power BI.

The tutor advised us not to blindly trust the AI. He said that most of the time it's right, but that we should still check it out.

If you're interested in learning more about the QRC Data Analytics course in Queenstown, New Zealand, we recommend reading the posts in the order below. If you're interested in data analytics, or want to become an analyst, this will be helpful.

ERD lab logs for non-certified chefs

The biggest thing I took away from the ERD challenge was understanding the nature of the data. I realized this when I looked at the Order items and Orders data, and I thought, "What's the difference between the two?.

It occurred to me that if I didn't know what these two pieces of data were talking about, I couldn't see the relationship. Order Items refers to the items that a customer has ordered from one of the many product models. Orders, on the other hand, is about that one instance where a customer placed an order for a product.

For example, let's say a customer named A has an order with order id 1, and inside it are several order items: bike model A, bike model B, bike model C, etc.

But if I don't know what they mean, I can't even begin to establish a relationship, right?

Finalize

Today we've been talking about data entity relationships, or ERD fundamentals, and that's if we only had files as our data source.

But the data we need at any given time isn't always in front of us - we may need it from multiple organizations, one by one.

When that happens, Tutor says you can utilize the API. We'll talk about APIs in the next post.

Data Entity Relationships (ERD) Fundamentals: A Non-Chef's Lesson in the Importance of Data Design