データベースの構築・Raspberry Pi

　シンプルなデータ・モデルを検討して、すでにインストール済みのMySQLを使ってデータベースを構築します。SQL文でデータベースとテーブルを作成した後、CSV形式のデータを取り込んでデータを格納します。出来上がったデータベースは、以降の章のデータベース保守やWebアプリケーション開発で使用します。
　それでは、RDB（Relational Database）の設計～開発～データ実装の一連の作業をやってみることにしましょう。

１．データ・モデルの設計

　簡単でなじみ深いテーマを対象にと考え、ここでは「日本の世界遺産」をデータ・モデルとして取り上げることにします。2016年7月末に、わが国では16の文化遺産と4つの自然遺産、合わせて20がユネスコの世界遺産に登録されています。
　内容を見ると、ある都道府県の一つの資産が登録されているだけでなく、複数の都道府県にまたがる複数の資産が一括して登録されたものもあります。またそれぞれの遺産は、多くの場合複数の構成資産が指定されています。例えば「古都京都の文化財」では、17の寺社や城などの文化財が指定されています。
　これらの構成資産まで調べ上げてデータベース化することにします。ただし、構成資産は個々の遺産についてWeb情報を検索した結果に基づいており、取り上げ方や内容に正確さを欠くものもあることをご承知ください。
　世界遺産データモデルは、次のように4つのテーブルで構成します。
　テーブルは表の形をしていて行と列から成り立っていますが、以下の説明では、行はレコード、列はフィールドと表しています。テーブル上のレコードを識別するために、内容が重複しないフィールドが存在しますが、これをプライマリキーと呼びます。

　続いて各テーブルの様式と格納データ例を掲げますが、テーブル名のカッコ内とフィールド名の下段のアルファベットは、MySQLでテーブルやフィールドを定義するときに使用する名称です。
　まずはそれぞれのテーブルに記録する内容を眺めて、「日本の世界遺産」というデータ・モデルを表現するために、どのようにデータ同士を関連付けようとしているかを感覚的に把握してください。

①世界遺産テーブル

　登録No.は登録の順番号です。続いてそれぞれの世界遺産名と遺産種別、登録日を記録します。遺産種別の内容から種別テーブルを参照することで、「文化遺産」か「自然遺産」かが明示されます。なお、登録日データの下位2桁はすべて「01」になっていますが、特に意味はありません。

②種別テーブル

　上記の世界遺産テーブルの種別と関係づけられるテーブルです。
種別の内容によって種別名が対応付けされます。

③構成資産テーブル

　遺産テーブルの遺産No.に対応した番号に続いて、それを構成する構成資産No.を記載します。構成資産がひとつだけの場合は1を、複数資産あるいは一資産でも複数の都道府県にまたがる場合は、遺産No.内で1から始まる順番号を記入します。都道府県コードは、都道府県テーブルに記録された都道府県名と対応するコードを記入します。構成資産名は日本語名とふりがなを、備考には別称や通称などを記入しています。

④都道府県テーブル

　都道府県名の一覧を記録します。構成遺産テーブルから都道府県コードで参照されて、都道府県名が対応付けされます。

　〔注意：英文字の名前について〕

　MySQLはデータベース名とテーブル名の大文字・小文字を区別しますが、一部のOSでは区別されません。Windowsがそのひとつで、デフォールトの状態では区別されず、これらは小文字に変換されます。一方Linuxでは区別されることから、大文字と小文字を混在すると互換性の問題が発生します。このため、ここではデータベース名とテーブル名は小文字で統一しています。また、フィールド名は大文字と小文字を使用していますが、MySQLはこれを区別しません。例えば、HeritageNameとheritagenameは同一の名前と解釈されます。なお、日本語による命名は、異なるOSとのポータビリティの問題や、保守環境との不整合を回避するために使用していません。

２．データベースとテーブルの作成

　Windows PCでPoderosaを立ち上げて、次のようにMySQLを起動します。起動時にパスワードを求められるので入力して下さい。

mysql -u root -p

　これからSQL文を使ってデータベースとテーブルを作成します。

①データベースの作成

　CREATE DATABASE文でデータベースworld_heritageを作成します。続いて配下にテーブルを作成するために、use文で使用データベースをworld_heritageに切り替えます。以下、SQLのキーワードは大文字にしていますが、入力は小文字でもかまいません。

CREATE DATABASE world_heritage;

USE world_heritage;

②テーブルの作成

　以下のようにCREATE TABLE文を入力して4つのテーブルを作成します。
　各フィールドの定義はフィールド名とデータ型から成り、データの未入力を許さない項目にはNOT NULL句を書き添え、項目定義間はカンマで区切ります。データ型のINT, SMALLINTは整数型、CHAR, VARCHARは文字列で括弧内に文字数を指定します。項目定義の最後に記述されたPRIMARY KEY(項目名)は、その項目名をプライマリキーにすることを宣言しています。

・世界遺産テーブル

CREATE TABLE heritage_table ( RegisterNo INT NOT NULL, HeritageName VARCHAR(100) NOT NULL, Kind SMALLINT NOT NULL, RegisterDate CHAR(10) NOT NULL, PRIMARY KEY (RegisterNo) );

・種別テーブル

CREATE TABLE kind_table ( Kind SMALLINT NOT NULL, KindName CHAR(20) NOT NULL, PRIMARY KEY (Kind) );

・構成資産テーブル

CREATE TABLE config_table ( RegisterNo INT NOT NULL, ConfigNo INT NOT NULL, PrefCode INT NOT NULL, Configure VARCHAR(100), Note VARCHAR(100), PRIMARY KEY (RegisterNo,ConfigNo) );

・都道府県テーブル

CREATE TABLE pref_table ( PrefCode INT NOT NULL, PrefName CHAR(10) NOT NULL, PRIMARY KEY (PrefCode) );

３．データの準備とテーブルへのインポート

　ここで使用するCSVファイルはダウンロードできます。　==>「このページから」

①CSVデータファイルの作成

　Windows PCで、それぞれのテーブルに格納するデータをCSV形式で準備します。エディターなどで直接CSVファイルを作成してもかまいませんが、複雑なものは表計算ソフトなどを利用して整理し、一括してCSVファイルに変換した方がいいかも知れません。
　いずれの場合も、文字セットはUnicode(UTF-8)を使用するよう注意してください。以下に各テーブル用のCSV形式ファイルの内容を例示します。
・世界遺産CSVデータ

・種別CSVデータ

・構成資産CSVデータ

・都道府県CSVデータ

②データの転送処理

　CSVデータファイルをRaspberry Piのtmpディレクトリーに転送します。
　WinSPCを立ち上げてRaspberry Piに接続し、左画面をWindows PC側のCSVデータが収納されたディレクトリーに移動してください。続いて右画面をRaspberry Pi側のtmpディレクトリーに移動します。左画面のCSVファイルを右にドラッグ&ドロップしてファイルを転送します。

③データのインポート処理

　Windows PC上のMySQL画面で、次のSQL文を入力してCSVデータをテーブルにインポート（移入または転記）します。

LOAD DATA INFILE "/tmp/heritage.csv" INTO TABLE world_heritage.heritage_table FIELDS TERMINATED BY "," ; LOAD DATA INFILE "/tmp/kind.csv" INTO TABLE world_heritage.kind_table FIELDS TERMINATED BY "," ; LOAD DATA INFILE "/tmp/config.csv" INTO TABLE world_heritage.config_table FIELDS TERMINATED BY "," ; LOAD DATA INFILE "/tmp/pref.csv" INTO TABLE world_heritage.pref_table FIELDS TERMINATED BY "," ;

　;に続く［Enter］キーを押すたびにインポートされてデータ件数が表示されます。

　これでデータベースが出来上がりました。テーブルに記録された内容の表示や編集は次章で扱いますが、SQL画面で次のようなSQL文を入力して記録状態を確認することができます。

１．データ・モデルの設計

上記の世界遺産テーブルの種別と関係づけられるテーブルです。種別の内容によって種別名が対応付けされます。

都道府県名の一覧を記録します。構成遺産テーブルから都道府県コードで参照されて、都道府県名が対応付けされます。

〔注意：英文字の名前について〕

２．データベースとテーブルの作成

mysql -u root -p これからSQL文を使ってデータベースとテーブルを作成します。

・世界遺産テーブル CREATE TABLE heritage_table ( RegisterNo INT NOT NULL, HeritageName VARCHAR(100) NOT NULL, Kind SMALLINT NOT NULL, RegisterDate CHAR(10) NOT NULL, PRIMARY KEY (RegisterNo) );

・種別テーブル CREATE TABLE kind_table ( Kind SMALLINT NOT NULL, KindName CHAR(20) NOT NULL, PRIMARY KEY (Kind) );

・構成資産テーブル CREATE TABLE config_table ( RegisterNo INT NOT NULL, ConfigNo INT NOT NULL, PrefCode INT NOT NULL, Configure VARCHAR(100), Note VARCHAR(100), PRIMARY KEY (RegisterNo,ConfigNo) );

・都道府県テーブル CREATE TABLE pref_table ( PrefCode INT NOT NULL, PrefName CHAR(10) NOT NULL, PRIMARY KEY (PrefCode) );

３．データの準備とテーブルへのインポート

　上記の世界遺産テーブルの種別と関係づけられるテーブルです。
種別の内容によって種別名が対応付けされます。

　都道府県名の一覧を記録します。構成遺産テーブルから都道府県コードで参照されて、都道府県名が対応付けされます。

　〔注意：英文字の名前について〕

mysql -u root -p

　これからSQL文を使ってデータベースとテーブルを作成します。

・世界遺産テーブル

CREATE TABLE heritage_table ( RegisterNo INT NOT NULL, HeritageName VARCHAR(100) NOT NULL, Kind SMALLINT NOT NULL, RegisterDate CHAR(10) NOT NULL, PRIMARY KEY (RegisterNo) );

・種別テーブル

CREATE TABLE kind_table ( Kind SMALLINT NOT NULL, KindName CHAR(20) NOT NULL, PRIMARY KEY (Kind) );

・構成資産テーブル

CREATE TABLE config_table ( RegisterNo INT NOT NULL, ConfigNo INT NOT NULL, PrefCode INT NOT NULL, Configure VARCHAR(100), Note VARCHAR(100), PRIMARY KEY (RegisterNo,ConfigNo) );

・都道府県テーブル

CREATE TABLE pref_table ( PrefCode INT NOT NULL, PrefName CHAR(10) NOT NULL, PRIMARY KEY (PrefCode) );