1. Entity relationship model
Welcome back to our journey in data modeling for data warehousing with Snowflake! We're diving into how to connect and retrieve data from various entities, starting with entity-relationship modeling.
2. Introduction to entity-relationship modeling
The entity-relationship model, known as the ER model, structures data with entities, attributes, and relationships, similar to what we've seen with normalized data.
We'll simulate a university database structure for our Entity-Relationship model, with students, classes, and schools, showing how keys link these entities.
3. Example of the entity-relationship model
The Entity-Relationship model is like a map for a business, illustrating connections between different parts like products and departments. It's beneficial for visualizing relationships in systems like our university example, where it displays interactions between 'students', 'courses', and 'schools'.
4. Why choose the ER model
The Entity-Relationship model's strengths lie in its clarity, organization, and flexibility.
It simplifies understanding business connections by breaking down data into related entities. For instance, it can show relationships between products and departments in a business, easing information management.
Its flexibility allows easy adaptation to changes, such as adding new courses or departments, without overhauling the entire system.
5. Building the ER model
Let's build our Entity-Relationship model for this university system. To focus on building relationships, let's assume that the entities 'students,' 'departments,' and 'classes' already exist and are defined by their attributes and primary keys. We will modify the structures and build our Entity-Relationship model from this point. To mirror the real-world university setup, we'll introduce an 'enrollments' entity, bridging students with classes and detailing the year and semester of enrollment.
6. Building the ER model
Our first step will be to update existing entities. In our case, we need to update 'classes' to link with 'schools'. We use the ALTER command to add a FOREIGN KEY, establishing a relationship between these entities.
7. Building the ER model
Next, we will create a new entity, 'enrollments', linking 'students' and 'classes'.
8. Building the ER model
Finally, we'll add the keys to reference the foreign keys of students and classes.
9. Building the ER model
Our Entity-Relationship model now closely represents the university's operational structure with the enrollments entity established.
Let's now see how to excel in data retrieval within this model.
10. Retrieving data from the ER Model
Retrieving data in an Entity-Relationship model is intuitive and powerful, especially when pulling connected information from multiple tables. Let's walk through an example: finding all students' names and classes signed during 2023 in the Science school.
11. Retrieving data from the ER Model
Let's start with students. We begin by selecting the student name attribute from the 'students' entity. This way, we will get the list of all the students.
12. Retrieving data from the ER Model
The next step involves linking 'students' to the 'enrollments' entity. We'll use the JOIN clause to establish the relationship ON the primary and foreign key, 'student_id'.
13. Retrieving data from the ER Model
Since we already have an 'enrollments' entity, we can set a filter using the WHERE clause to narrow down the year when they signed up to 2023.
14. Retrieving data from the ER Model
We extend our JOIN to include the 'classes' entity which links enrollments to classes, enabling us to fetch the class names.
15. Retrieving data from the ER Model
Finally, to focus on the Science school, we JOIN with the schools entity. This last JOIN allows us to add the filter to the Science school by using an AND clause in the WHERE statement.
16. Retrieving data from the ER Model
Using JOIN clauses, this step-by-step approach helps us navigate through the relationships in our Entity-Relationship model and retrieve the precise information we need, showcasing the model's effectiveness in handling complex data relationships.
17. Terminology and functions overview
Here's a quick reference to the terms and SQL clauses we've discussed.
18. Let's practice!
Time to put your knowledge into action! Let's build our Entity-Relationship model in Snowflake.