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

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

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

には，

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

前準備

使用するソフトウエア

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

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

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

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

テーブルの準備

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

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

SQL のプログラムを書いて，郵便番号データテーブル zipallを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/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










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





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












本サイトは金子邦彦研究室のWebページである．

資料等の公開では，原則，「クリエイティブコモンズ BY NC SA」として公開するようにしている．
PDFファイル，パワーポイントファイルなどには，
「クリエイティブコモンズ BY NC SA」を明記するとともに，ロゴを記載するようにしている（作業が間に合っていない分もあるのでご容赦ください）．

公開している資料をご利用になる場合の，再配布の条件，剽窃の防止などについて，別ページで説明している． 再配布や資料改変の際には，そのページをご確認ください．

サイトマップは，サイトマップのページをご覧下さい． 本サイト内の検索は，サイト内検索のページをご利用下さい．

問い合わせ先： 金子邦彦（かねこ くにひこ）