郵便番号テーブル ken_all を用いた演習

テーブルの分解

データベースにおけるテーブルの分解について説明します. 例えば,テーブル R(A,B,C) が存在する場合,以下のような分解が可能です.

S と T を組み合わせることで,元のテーブル R システムの情報を完全に表現することができます. このような操作を,「S と T は R の分解である」と定義します. このような分解は SQL プログラムで容易に実装できます.

ただし,分解には適切な基準と手順が必要です. 上記の例において,S と T の結合 (join) 操作によって R が完全に復元できること(無損失分解)が重要な要件となります. 具体的には,属性 A がテーブル R のキーである場合,S と T の結合によって R が確実に復元できることが保証されます.

異なり数

異なり数は,以下の SQL 文を用いて計算します.

select count(属性名) FROM テーブル名;

という形での SQL で数えます.

SQL 演習

候補キーに関する分析

複数属性の関係の分析

属性間の関係を分析するために,以下のパターンの SQL クエリを頻繁に使用します.

select count(DISTINCT B), A, MAX(B), MIN(B)
FROM T
group by A
ORDER BY COUNT(DISTINCT B) DESC, A ASC;

このクエリは,属性 A でグループ化(group by)を行い,各グループにおける「COUNT(DISTINCT B), A, MAX(B), MIN(B)」を計算します. 具体的には,同一の A 値を持つレコードについて,B の異なり数,最大値,最小値を集計します. また,「ORDER BY COUNT(DISTINCT A) DESC」により,B の異なり数が最大のグループが最上位に表示されます.

  1. 「一つの郵便番号で二以上の町域を表す場合の表示」が false である行について,同一郵便番号に対する全国地方公共団体コードの出現数を確認

    「一つの郵便番号で二以上の町域を表す場合の表示」が false の場合,この問い合わせ結果は必ず「1」となるはずですが,データの整合性を確認するために実行します.

    #!/bin/bash
    
    cat >/tmp/a.$$.sql <<-SQL
      select count(DISTINCT jiscode), zipcode, MAX(jiscode), MIN(jiscode)
      FROM ken_all
      WHERE flag13 = 0
      group by zipcode
      ORDER BY COUNT(DISTINCT jiscode) ASC, zipcode ASC;
    SQL
    cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01
    
  2. 「一つの郵便番号で二以上の町域を表す場合の表示」が false である行について,同一の全国地方公共団体コードに対する異なる郵便番号の出現数を確認

    この場合,結果が「1」以上となることは想定内ですが,データの傾向を把握するために実行します.

    #!/bin/bash
    
    cat >/tmp/a.$$.sql <<-SQL
      select count(DISTINCT zipcode), jiscode, MAX(zipcode), MIN(zipcode)
      FROM ken_all
      WHERE flag13 = 0
      group by jiscode
      ORDER BY COUNT(DISTINCT zipcode) DESC, jiscode ASC;
    SQL
    cat /tmp/a.$$.sql | sqlite3 /tmp/mydb01