トップページ -> データの扱い -> 種々のオープンデータ -> 郵便番号データテーブルを3つのテーブルに分解
[サイトマップへ], [サイト内検索へ]

郵便番号データテーブル zipall を 3つテーブル zips, kens, shichosons に分解

この Web ページでは,日本郵政公社「ゆうびんホームページ」で公開されている 2つの郵便番号データのCSV(カンマ区切り値)形式ファイル

  1. 住所の郵便番号(CSV形式)(ken_all.csv)
  2. 事業所の個別郵便番号(CSV形式)(JIGYOSYO.CSV)

には,

隠された冗長性があります(つまり「第三正規形」でないため,更新不整合の問題があります). そこで,次の手順で,郵便番号データを3つのテーブルに分解し,冗長性を取り除くことにします.

作成するSQLite3 データベース: zipdb


前準備

使用するソフトウエア

あらかじめ決めておく事項

この Web ページでは,データベースの作成を行うので, 作成するデータベースのデータベース論理名を決めておくこと. この Web ページでは,次のように書きます.

テーブルの準備

「郵便番号データをリレーショナルデータベース管理システム SQLite3 の1つのテーブルに格納」の Web ページの記述に従って,郵便番号データテーブル zipall の作成が済んでいること.


郵便番号データテーブル zipall を 3 つのテーブル zips, kens, shichosons に分解

SQL のプログラムを書いて, 郵便番号データテーブル zipall3つのテーブルに分解します

  1. 郵便番号 ・・・ zips テーブル
  2.  ・・・ kens テーブル
  3. 市町村 ・・・ shichosons テーブル

郵便番号辞書では、「町域」というものが登場するのですが、 「町域」に対応するテーブルは作りません.これは、 「町域」のテーブルを作ったとして、「キー」が無い(正確には,全属性を集めないとキーにならない)という理由です. 「町域」には、同じ漢字でよみかたの違うものがあります(例えば、「上川」で「かみがわ」、「かみかわ」のように)ので、「キー」がありません。

zips, kens, shichosons のテーブル定義

bash プログラム

#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL

CREATE TABLE kens (
    id        INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    ken_kanji TEXT UNIQUE NOT NULL,
    ken_kana  TEXT UNIQUE NOT NULL );

CREATE TABLE shichosons (
    jiscode         INTEGER PRIMARY KEY NOT NULL CHECK (jiscode >= 1000 AND jiscode <= 50000),
    ken_kanji       TEXT NOT NULL,
    shichoson_kanji TEXT NOT NULL,
    shichoson_kana  TEXT );

CREATE TABLE zips (
    id           INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    zipcode      INTEGER NOT NULL,
    zip_old      INTEGER NOT NULL,
    jiscode      INTEGER NOT NULL REFERENCES shichosons(jiscode),
    choiki_kanji TEXTL,
    choiki_kana  TEXT,
    flag10       TEXT    NOT NULL,
    flag11       INTEGER NOT NULL CHECK ( flag11 >= 0 AND flag11 <= 1 ),
    flag12       INTEGER NOT NULL CHECK ( flag12 >= 0 AND flag12 <= 3 ),
    flag13       INTEGER NOT NULL CHECK ( flag13 >= 0 AND flag13 <= 1 ),
    info14       INTEGER, 
    info15       INTEGER
); 
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/zipdb

[image]

zips, kens, shichosons テーブルの作成 (populate)

bash プログラム

#!/bin/bash

# dump /tmp/zipall.sql from mydb01
cat >/tmp/a.$$.sql <<-SQL
.output /tmp/zipall.sql
.dump zipall
.exit
SQL
#
cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01

# load the dump result into zipdb
cat >/tmp/a.$$.sql <<-SQL
.read /tmp/zipall.sql
.exit
SQL
#
cat /tmp/a.$$.sql | sqlite3 /tmp/zipdb

#
cat >/tmp/a.$$.sql <<-SQL

INSERT INTO kens(ken_kanji, ken_kana)
    select distinct ken_kanji, ken_kana
    from zipall;

INSERT INTO shichosons (jiscode, ken_kanji, shichoson_kanji, shichoson_kana)
    select distinct jiscode, ken_kanji, shichoson_kanji, shichoson_kana
    from zipall;

INSERT INTO zips(zipcode, zip_old,
           jiscode, choiki_kanji, choiki_kana,
          flag10, flag11, flag12, flag13, info14, info15)
    select zipcode, zip_old,
           jiscode, choiki_kanji, choiki_kana,
          flag10, flag11, flag12, flag13, info14, info15
    from zipall;

vacuum; 
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/zipdb

[image]

【各テーブルの中身の先頭部分】

#!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
select * from kens limit 3; 
select * from shichosons limit 3; 
select * from zips limit 3; 
SQL

cat /tmp/a.$$.sql | sqlite3 /tmp/zipdb

[image]
  • (オプション) 再構成

    bash プログラム

    #!/bin/bash
    
    cat >/tmp/a.$$.sql <<-SQL
    .output /tmp/kens.sql
    .dump kens
    .exit
    SQL
    #
    cat /tmp/a.$$.sql | sqlite3 /tmp/zipdb
    
    cat >/tmp/a.$$.sql <<-SQL
    .output /tmp/zips.sql
    .dump zips
    .exit
    SQL
    #
    cat /tmp/a.$$.sql | sqlite3 /tmp/zipdb
    
    cat >/tmp/a.$$.sql <<-SQL
    .output /tmp/shichosons.sql
    .dump shichosons
    .exit
    SQL
    #
    cat /tmp/a.$$.sql | sqlite3 /tmp/zipdb
    
    #
    rm -f /tmp/zipdb 
    
    # load the dump result into zipdb
    cat >/tmp/a.$$.sql <<-SQL
    .read /tmp/kens.sql
    .read /tmp/zips.sql
    .read /tmp/shichosons.sql
    vacuum; 
    .exit
    SQL
    #
    cat /tmp/a.$$.sql | sqlite3 /tmp/zipdb
    

    本サイトは金子邦彦研究室のWebページです.サイトマップは,サイトマップのページをご覧下さい. 本サイト内の検索は,サイト内検索のページをご利用下さい.

    問い合わせ先: 金子邦彦(かねこ くにひこ) [image]