Entity relationship model

Introduction to Data Modeling in Snowflake

Nuno Rocha

Director of Engineering

Introduction to entity-relationship modeling

Entity-relationship (ER) model: Structures normalized data using entities, attributes, and relationships

College theme entities to model

Example of the entity-relationship model

College theme entities interaction

Why choose the ER model

Clarity
- Simplifies business connections mirroring real-life interactions
Organization
- Breaks down data into related entities, easing information management
Flexibility
- Adaptability to grow and change over time

Building the ER model

College theme model plus enrollments

Building the ER model

Add relationship between existing entities:

ALTER TABLE classes
ADD FOREIGN KEY (school_id) REFERENCES schools(school_id);

College theme model, linking classes and schools entities

Building the ER model

College theme model, new entity enrollments

Building the ER model

CREATE OR REPLACE TABLE enrollments (
      -- Create a new entity with a unique identifier
      enrollment_id NUMBER(10,0) PRIMARY KEY

      -- Add the entity's attributes
      year NUMBER(4,0),
      semester VARCHAR(255)

      -- Add relationships to other entities
      FOREIGN KEY (student_id) REFERENCES students(student_id),
      FOREIGN KEY (class_id) REFERENCES classes(class_id)

);

Building the ER model

Final college ER model

Retrieving data from the ER Model

Querying ER model for students names and classes signed during 2023 in the Science school

Retrieving data from the ER Model

SELECT students.name  
FROM students;

Querying ER model for students names

Retrieving data from the ER Model

SELECT students.name
FROM students
    JOIN enrollments 
    ON students.student_id = enrollments.student_id;

Querying ER model for students and enrollments

Retrieving data from the ER Model

SELECT students.name
FROM students
    JOIN enrollments 
    ON students.student_id = enrollments.student_id
WHERE enrollments.year = '2023';

Querying ER model for 2023 enrollments

Retrieving data from the ER Model

SELECT students.name, 
    classes.class_name
FROM students
    JOIN enrollments 
    ON students.student_id = enrollments.student_id
    JOIN classes 
    ON enrollments.class_id = classes.class_id
WHERE enrollments.year = '2023';

Querying ER model for classes names

Retrieving data from the ER Model

SELECT students.name, 
    classes.class_name
FROM students
    JOIN enrollments 
    ON students.student_id = enrollments.student_id
    JOIN classes 
    ON enrollments.class_id = classes.class_id
    JOIN schools 
    ON classes.department_id = schools.school_id
WHERE enrollments.year = '2023'
    AND schools.school_name = 'Science';

Retrieving data from the ER Model

JOIN ON: SQL clause to combine rows from tables, based ON a related column.

Querying ER model for Science school

Terminology and functions overview

Entity-relationship (ER) model: Structures normalized data using entities and relationships.
SELECT FROM: SQL command to fetch columns from a table.
JOIN ON: SQL clause to combine rows from tables, based ON a related column.
WHERE: SQL clause to filter records based on a set condition.
AND: Logical operator used with WHERE clause to combine multiple conditions.

-- Querying data from merged entities filtered by specific conditions
SELECT column_name
FROM table_name
    JOIN other_table ON table_name.FK = other_table.PK
WHERE column_name  condition  value
    AND column_name  condition  value;

Let's practice!

Introduction to Data Modeling in Snowflake