データベースの正規化(エクセルでの処理)

 今回はデータベースの処理をエクセルで実施します。使用するデータはオープンデータの下記を使います。下記のデータをダウンロードして使用します。

浦安市災害協定締結先一覧（オープンデータ）https://www.city.urayasu.lg.jp/shisei/keikaku/1022110/1025892/1025894/1022122.html

(1) データの前処理

データの前処理を下記の項目で実施します。変更を反映できるようにエクセルファイルにしてから編集した方がいいでしょう。

・1行目を削除

・B列：区分に指定されている項目を入れる

・C列：日付を西暦に変更(色々な形式になっているので)、例：令和元年9月12日 と変換できない文字列として入れられたものは、手で変換する必要があります。日付の形式は「yyyy-mm-dd」とします。

・G列：締結に空白があるので「記載無し」としておく

ここまでのファイル：saigaikyoutei.teiketsusaki_20231026_v01_01.xlsx

(2) データの正規化

次にエクセルにシートを追加して、データの正規化をしてやっていきます。以下のシートを追加し、エクセルのunique関数を使って、重複の無い項目を抽出します。例として、classificationシートのA2に「=UNIQUE(saigaikyoutei.teiketsusaki_2023!B2:B130)」と入力。

・B列：区分 -> classificationシートを作成(name, classification_idの2列を作成)

・E列：締結の名称 -> agreementシートを作成(name, agreement_idの2列を作成)

・F列：協定等の内容 -> contentシートを作成(name, content_idの2列を作成)

・G列：締結の内容 -> conclusionシートを作成(name, conclusion_idの2列を作成)

ここまでのエクセルファイル：saigaikyoutei.teiketsusaki_20231026_v01_02.xlsx

(3) データの正規化：vlookupで外部キーを挿入

次に(2)で作成した表でvlookupを利用して、元データを外部キーで置き換えます。vlookupは検索したキーワードに対し、それに紐づいているID等を挿入するものです。

全体の表でB列「区分」の左に、classification_id列を追加して、vlookupで区分の値を検索し、classification_idを取得します。新たに追加したB列のB2には以下のように入力します。

=VLOOKUP(C2,cllasification!A$2:B$13,2,FALSE)

他の外部テーブルを作った列に対しても同様にやっていきます。classification,agreement,content,conclusionの4テーブルに対して同様にできたら、このシートを「値」でコピーして、新たなシート「corporation」に貼り付けます。idを挿入した区分、協定の名称、協定等の内容、締結の各列は削除します。

この「corpration」がメインテーブル、「classification」「agreement」「content」「conclusion」の４つが外部テーブルとなり、正規化が完了しました。

ここまでのファイル：saigaikyoutei.teiketsusaki_20231026_v01_03.xlsx

次はこの計5個のテーブルをデータベースに格納して、リレーションを張る作業になります。ここまでの作業を確認して、構造化されているデータがあったとき、どのように正規化してデータベースに格納するかを確認して下さい。