郵便番号テーブル ken_all を 3つテーブル zips, kens, shichosons に分解（Ubuntu 上）

このページでは，日本郵政「ゆうびんホームページ」で公開されている 2つの郵便番号データのＣＳＶ（カンマ区切り値）形式ファイル

住所の郵便番号（ＣＳＶ形式）(ken_all.csv)
事業所の個別郵便番号（ＣＳＶ形式）(JIGYOSYO.CSV)

には，

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

前準備

使用するソフトウェア

SQLite 3 コマンドラインシェルのインストールが済んでいること．

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

このページでは，SQLite 3 データベースの生成を行う．生成するSQLite 3 データベースのデータベース名を決めておくこと．このページでは，次のように書く．

データベース名: zipdb
データベース名は，自由に決めてよいが，半角文字（つまり英字と英記号）を使い，スペースを含まないこと，

テーブルの準備

「郵便番号 CSV データを SQLite 3 にインポート（SQLite 3 を使用）」の Web ページの記述に従って，郵便番号テーブル ken_all の作成が済んでいること．

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

SQL のプログラムを書いて，郵便番号テーブル ken_allを3つのテーブルに分解します

郵便番号　・・・ zips テーブル
県　・・・ kens テーブル
市町村　・・・ 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

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

◆ bash プログラム

#!/bin/bash

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

# load the dump result into zipdb
cat >/tmp/a.$$.sql <<-SQL
.read /tmp/ken_all.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 ken_all;

insert into shichosons (jiscode, ken_kanji, shichoson_kanji, shichoson_kana)
    select distinct jiscode, ken_kanji, shichoson_kanji, shichoson_kana
    from ken_all;

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

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

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

#!/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

(オプション) 再構成

◆ 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