正規化データベースと非正規化データベース

データベース設計

Lis Sulmont

Curriculum Manager

書店の例

非正規化: スター スキーマ

$$

正規化: スノーフレークスキーマ

$$

データベース設計

非正規化クエリ

目標: 2018年第4四半期にバンクーバーで販売されたオクタヴィア・E・バトラーの全書籍の数量を取得する 

  SELECT SUM(quantity) FROM fact_booksales
    -- Join to get city
    INNER JOIN dim_store_star on fact_booksales.store_id = dim_store_star.store_id
    -- Join to get author
    INNER JOIN dim_book_star on fact_booksales.book_id = dim_book_star.book_id
    -- Join to get year and quarter
    INNER JOIN dim_time_star on fact_booksales.time_id = dim_time_star.time_id
  WHERE 
    dim_store_star.city = 'Vancouver' AND dim_book_star.author = 'Octavia E. Butler' AND
    dim_time_star.year = 2018 AND dim_time_star.quarter = 4;

7600

合計3件の結合

データベース設計

正規化クエリ

SELECT
  SUM(fact_booksales.quantity)
FROM
  fact_booksales
  -- Join to get city
  INNER JOIN dim_store_sf ON fact_booksales.store_id = dim_store_sf.store_id
  INNER JOIN dim_city_sf ON dim_store_sf.city_id = dim_city_sf.city_id
  -- Join to get author
  INNER JOIN dim_book_sf ON fact_booksales.book_id = dim_book_sf.book_id
  INNER JOIN dim_author_sf ON dim_book_sf.author_id = dim_author_sf.author_id
  -- Join to get year and quarter
  INNER JOIN dim_time_sf ON fact_booksales.time_id = dim_time_sf.time_id
  INNER JOIN dim_month_sf ON dim_time_sf.month_id = dim_month_sf.month_id
  INNER JOIN dim_quarter_sf ON dim_month_sf.quarter_id =  dim_quarter_sf.quarter_id
  INNER JOIN dim_year_sf ON dim_quarter_sf.year_id = dim_year_sf.year_id
データベース設計

正規化クエリ（続き）

WHERE
  dim_city_sf.city = `Vancouver`
  AND 
  dim_author_sf.author = `Octavia E. Butler`
  AND
  dim_year_sf.year = 2018 AND dim_quarter_sf.quarter = 4; 

sum
7600

合計8件の結合

データベースを正規化する理由は？

データベース設計

正規化でスペースを節約できる

非正規化データベースはデータの冗長性を増やす

データベース設計

正規化でスペースを節約できる

正規化によりデータの冗長性が排除される

データベース設計

正規化によりデータの整合性が向上する

$$

1. データの一貫性を保つ

参照整合性のため、命名規則を尊重する。例：「CA」「california」ではなく「California」

2. 安全な更新、削除、挿入

データの冗長性が少ない = 変更するレコードが少ない

3. 拡張により再設計しやすくなる

小さいテーブルは大きいテーブルより拡張しやすい

データベース設計

データベース正規化

メリット

  • 正規化によりデータの冗長性を排除：ストレージの節約

  • より高いデータ整合性: 正確で一貫したデータ

デメリット

  • 複雑なクエリにはより多くのCPUが必要
データベース設計

OLTPとOLAP

OLTP

例: 運用データベース

通常は高度に正規化されている

  • 書き込み中心
  • データ 挿入の迅速さ・安全さ優先

OLAP

例：データウェアハウス

通常は正規化されない

  • 読み取り中心
  • 分析用クエリの高速性優先
データベース設計

練習しましょう！

データベース設計

Preparing Video For Download...