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

Introduction to Data Modeling in Snowflake

Example of the entity-relationship model

College theme entities interaction

Introduction to Data Modeling in Snowflake

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
Introduction to Data Modeling in Snowflake

Building the ER model

College theme model plus enrollments

Introduction to Data Modeling in Snowflake

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

Introduction to Data Modeling in Snowflake

Building the ER model

College theme model, new entity enrollments

Introduction to Data Modeling in Snowflake

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)
);
Introduction to Data Modeling in Snowflake

Building the ER model

Final college ER model

Introduction to Data Modeling in Snowflake

Retrieving data from the ER Model

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

Introduction to Data Modeling in Snowflake

Retrieving data from the ER Model

SELECT students.name  
FROM students;

Querying ER model for students names

Introduction to Data Modeling in Snowflake

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

Introduction to Data Modeling in Snowflake

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

Introduction to Data Modeling in Snowflake

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

Introduction to Data Modeling in Snowflake

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';
Introduction to Data Modeling in Snowflake

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

Introduction to Data Modeling in Snowflake

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;
Introduction to Data Modeling in Snowflake

Let's practice!

Introduction to Data Modeling in Snowflake

Preparing Video For Download...