實體關係模型

Snowflake 資料建模入門

Nuno Rocha

Director of Engineering

實體關係建模導論

實體關係（ER）模型：以實體、屬性與關係來結構化正規化資料

以大學主題的實體進行建模

實體關係模型範例

大學主題的實體互動

為何選用 ER 模型

清晰
- 以貼近現實的互動，簡化商業關聯
組織性
- 將資料拆成關聯實體，便於管理
彈性
- 可隨時間擴充與調整

建立 ER 模型

大學主題模型加上選課

建立 ER 模型

在既有實體間新增關係：

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

大學主題模型，連結 classes 與 schools 實體

建立 ER 模型

大學主題模型，新實體 enrollments

建立 ER 模型

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)

);

建立 ER 模型

大學 ER 模型最終版

從 ER 模型擷取資料

查詢 ER 模型：科學學院 2023 年的學生與課程

從 ER 模型擷取資料

SELECT students.name  
FROM students;

查詢 ER 模型：學生姓名

從 ER 模型擷取資料

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

查詢 ER 模型：學生與選課

從 ER 模型擷取資料

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

查詢 ER 模型：2023 年選課

從 ER 模型擷取資料

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';

查詢 ER 模型：課程名稱

從 ER 模型擷取資料

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';

從 ER 模型擷取資料

JOIN ON：SQL 子句，用於依關聯欄位將多個資料表的列合併。

查詢 ER 模型：科學學院

術語與函式總覽

實體關係（ER）模型：以實體與關係結構化正規化資料。
SELECT FROM：SQL 指令，從資料表擷取欄位。
JOIN ON：SQL 子句，依關聯欄位合併多表資料列。
WHERE：SQL 子句，用於依條件篩選紀錄。
AND：邏輯運算子，與 WHERE 合用以組合多個條件。

-- 從合併後的實體查詢資料，並以特定條件篩選
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;

一起來練習吧！

Snowflake 資料建模入門