astah*によるER図の作成とクエリの出力

この文書では，astah*によりER図を描いた後，テーブルを作成するMySQLのクエリを出力する方法について説明しています．ER図によりテーブルの全体を見渡すことができるのみならずテーブルを出力するときに外部キーや制約もあわせて出力してくれるので大変便利です．是非この方法を使って楽してより良いデータベースを作成しましょう．

ER図の新規作成

この文書では，成績管理データベースを題材にします．まずはER図を追加しましょう．下図のようにastah*を立ち上げて下さい．立ち上がりましたら下図のように新規にER図をクリックします．

エンティティとフィールド追加

ここではエンティティを追加すると共に，そこへフィールドを追加します．下図のように画面上側にあるツールチップからエンティティを選び，空白の領域をクリックするとテーブルが追加されます．

追加したのち，テーブル名を入力しましょう．今回は「学生表」と記入してください．ここで記入した表名は論理名と呼ばれるものであり，後で出力されるクエリの表名は物理名と呼ばれるものとは別物です．物理名を入力するには入力したいエンティティを選択し，左側にあるプロパティの中にある物理名に記入します．今回は物理名を「STUDENT_T」とします．下図のように論理名には「学生表」，物理名には「STUDENT_T」が記入されていることを確認してください．

エンティティには上下に2つの領域に別れています．このうち上が主キーの入る領域，下がそれ以外のフィールドが入る領域です．次にフィールドを追加します．エンティティにマウスカーソルを近づけると下図のように赤とオレンジの丸が現れます．上側にある赤を押すと主キーを追加でき，下側にあるオレンジを押すと主キー以外のフィールドを追加できます．

ではフィールドを追加しましょう．主キーとして「学籍番号」，その他のフィールドとして「学年」，「氏名」を追加してください．追加できますと下のようになります．

さらに続けて物理名も記入します．論理名をクリックすると左側にプロパティが現れ，そのなかに物理名を記入できる箇所があります．今回は学籍番号には「STUDENT_ID」，氏名には「NAME」，学年には「GRADE」と入力してください．下の図は学年に対して物理名を入力した例です．

同様に下記のようにエンティティとフィールドを追加してください．大括弧[]で囲まれているものが物理名です．

• 学科表[DEPARTMENT_T]
  • 学科ID[DEPARTMENT_ID](主キー)
  • 学科名[NAME]
  • 学科創立日[EST_DATE]
• 科目表[SUBJECT_T]
  • 科目ID[SUBJECT_ID](主キー)
  • 科目名[NAME]
  • 担当教員[TEACHER_NAME]
• 成績表[SCORE_T]
  • 成績[SCORE]

すべての記入が終わりますと下図のようになります．

型の定義

astah*にはプリミティブな型が定義されていますが，新たに追加したり既存の型を修正したりできます．今回，INT型(整数型)について桁数を指定できないようになっていますので，これを変更します．下図のようにメニューの中から「ツール」→「ER図」→「ERデータ型の設定」を選択します．

開かれたダイアログの中にはINT型がありますのでこれを選択し，下図のように編集ボタンを押してください．

デフォルトではINTの長さが無しとなっておりますので，下図のように「任意」に変更してください．また，長さを省略したときに入れられるデフォルトの長さを「11」とします．なお，11という値はMySQLのデフォルト値にならったものです．このように，データ型を変更したり，新たなデータ型を作成したりすることができます．

フィールドへの型の設定とNOT NULL制約

次に型をフィールドへ設定しましょう．下表のように各フィールドの型を設定してください．加えて，主キーでないフィールドすべてをNOT NULL制約をつけてください．

下図のようにフィールドを選択し，左側にあるプロパティの中にあるデータ型と長さを設定します．

NOT NULL制約をつけるには下図のようにNOT NULLのチェックボックスにチェックを入れてください．同様にすべてのフィールドに対してもNOT NULLにしてください．

エンティティの接続

作成したエンティティを接続して外部キーを追加すると共に，個数を指定します．このような接続のことをリレーションシップといい，個数をカーディナリといいます．例えば部品テーブル（部品ID[主キー]，部品名）と部品所在テーブル（ケース番号[主キー]，部品ID）があったとします．この2つには部品IDでテーブルが接続されます．これがリレーションシップです．そして在庫テーブルでは部品IDが外部キーになります．また，リレーションシップとなるのは，テーブルの主キーのみであり，主キー以外のフィールドは外部キーとしてリレーションシップとなりません．もし接続をしたいのであれば，リレーションシップとしたいフィールドを主キーとする別の表に分ける必要があります．さて，このリレーションシップにはどのような個数が考えられるでしょうか．部品には1つ以上ケースあります(1つの部品ケースでは収まりきらず，複数のケースに同じ部品が入る場合を想定しています)ので「1以上」といえます．一方，ケースには1つの部品の種類しか入りませんので「1」といえます．このような個数をカーディナリといいます．そして，カーディナリには下に示す種類があります．

1. 0または1以上
2. 1以上
3. 0または1

それぞれ記号でER図では描けますが，実は「IDEF1X」と「IE」の2種類の表記法があります．下表はそれぞれの表記法で示してあります．IEでは，縦の棒が「1」を表し，丸が「0」を表し，矢じりのような3本の先で構成された図形が「以上」を表します．それらを組み合わせることでカーディナリを表しています．

	IDEF1X	IE
0または1以上
1以上
0または1

このようにリレーションシップを結ぶとき，カーディナリが1対多の場合と多対多の場合があります．先ほどの部品とケースの例では，部品テーブルと部品所在テーブルのカーディナリは1対多(1以上)といえます．

さて，成績管理データベースではどのようなリレーションシップ及びカーディナリになるでしょうか．学生表と学科表の関係について考えてみます．学科には1名以上の学生が所属しますよね?一方，学生は1個の学科に所属します．従って，学科表と学生表には，「学科ID」で1対1以上のリレーションシップがあります．これをastah*では下の図のように描きます．まず，上側にあるツールチップから「依存型リレーションシップ」を選択します．これが1対多を表すときのリレーションシップであり，多対多を表すときには2つ右にある「多対多型リレーションシップ」を選択します．次に，学科表と学生表の間に線を引きます．学科表から学生表に向かって引きます．間違わないようにしましょう．最後にプロパティのカーディナリを「1以上」とします．

これでリレーションシップとカーディナリの設定は終了ですが，学生表を見ると主キーとして「学科ID(FK)」が追加されてしまっていますね．学生表において学科IDは主キーではありませんので学科IDを移動します．下の図のように学科IDをクリックしたのち，ドラッグアンドドロップしてください．

外部キーを主キーから外したとき，2つの変化が起こったことに気が付きましたか?1つは学科表と学生表にリレーションシップを結んだ時には学生表のエンティティの角が丸くなっていましたが，外部キーを主キーから移動すると角が元のとおり角になったはずです．このように外部キーがそのテーブルの主キーの一部になっているとエンティティの角を丸く表記することになっています．先ほどまでは学科IDが外部キーに入ってしまっていたために角が丸くなっていました．もう1つはエンティティの線です．外部キーが主キーになっていた時には実線でしたが主キーから外すと破線になりましたね?このように破線のリレーションシップを「非依存型リレーションシップ」といいます．反対に外部キーが主キーになっている実線を「依存型リレーションシップ」といいます．

リレーションシップについてはもう一つ属性があります．それは親が必須かということです．親とは1対多の関係において1の方ですので今回は学科表が親となります．この親が必須か，それともなくてもよいかを区別します．言い換えれば必須とは「1」であり，必須ではないとは「0もしくは1」となります．この違いでリレーションシップの端が異なります．下表はそれをまとめたものです．IEの場合では丸が0，立の棒が1を表していますので前に述べたカーディナリと同じ書き方になります．IDEF1Xではひし形で必須でないことを表現しています．親を必須かそうでないかをastah*ではエンティティを選択したときのプロパティにチェックボックスがありますので，そこで操作してください．

	IDEF1X	IE
必須(1)
必須でない(0もしくは1)

次に成績表についてもリレーションシップとカーディナリを考えてみましょう．成績は科目と学生の両方がないと決定しませんよね?したがって科目表の科目IDが親のリレーションシップと学生表の学籍番号が親のリレーションシップが2つあることになります．さらに科目IDと学籍番号は成績表においてともに主キーとすべきですので依存型リレーションシップといえます．カーディナリはすべて1以上と考えられますので，まとめますと下図のような関係が出来上がります．

クエリを含むスクリプトの出力

クエリを出力するには下図のようにメニューの中から「ツール」→「ER図」→「SQLエクスポート」を選択します．

クエリを含むスクリプトを出力するには，何をどこまで出力するのか指定する必要があります．そのために下図のように「オプション」を選択してください．

スクリプトを出力するとき下図のようなオプションがあります．ここでは，「CREATE TABLE文のみを使用する」にチェックを入れるとともに，一意インデックスを「出力しない」にしてください．

最後に出力先を指定して「作成」ボタンを押してください．

スクリプトの修正

出力されたスクリプトにより外部キーが設定されますが，レコードの削除と変更を行えません．そこで削除と変更を行えるようにスクリプトを修正します．下に示すように「ON DELETE CASCADE ON UPDATE CASCADE」を外部キーを設定している記述の近くに挿入してください．なお，33行目の末尾もともとあったカンマが34行目の末尾に移動していることに注意してください．

CREATE TABLE DEPARTMENT_T (
 DEPARTMENT_ID CHAR(1) NOT NULL PRIMARY KEY,
 NAME CHAR(20) NOT NULL,
 EST_DATE DATE NOT NULL
);


CREATE TABLE SUBJECT_T (
 SUBJECT_ID INT(8) NOT NULL PRIMARY KEY,
 NAME VARCHAR(30) NOT NULL,
 TEACHER_NAME VARCHAR(30) NOT NULL
);


CREATE TABLE STUDENT_T (
 STUDENT_ID INT(5) NOT NULL PRIMARY KEY,
 NAME VARCHAR(30) NOT NULL,
 GRADE INT(1) NOT NULL,
 DEPARTMENT_ID CHAR(1) NOT NULL,

 FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT_T (DEPARTMENT_ID)
 ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE SCORE_T (
 SUBJECT_ID INT(8) NOT NULL,
 STUDENT_ID INT(5) NOT NULL,
 SCORE INT(3) NOT NULL,

 PRIMARY KEY (SUBJECT_ID,STUDENT_ID),

 FOREIGN KEY (SUBJECT_ID) REFERENCES SUBJECT_T (SUBJECT_ID)
 ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT_T (STUDENT_ID)
 ON DELETE CASCADE ON UPDATE CASCADE
);

スクリプトの実行

最後にスクリプトを実行し，いくつかの動作確認をします．MySQLのクライアントを立ち上げ，新たなデータベース「PerformanceDB」を作成しましょう．

create database PerformanceDB;

 次にPerformanceDBに接続します．

connect PerformanceDB;

 先ほど生成したスクリプトを実行します．生成したスクリプトを絶対パスで指定します．このときデリミタ\は特殊文字なので2個書くようにして下さい．また，他の命令と異なり末尾にセミコロンを付けないようにしてください．例えばCドライブの直下にスクリプト「ER.sql」があったとした場合，下のように書きます．

source C:\\er.sql

 うまくいくと下のようになります．

ではレコードを挿入してみましょう．下のようにinsert命令を使って下さい．ただし，外部キーが付いているテーブルへの挿入はあらかじめ親のテーブルにデータが挿入されてないとなりません．したがいまして，科目表，学科表，学生表，成績表の順番で挿入してみましょう．

insert into SUBJECT_T values(1, "論理回路", "押田");
insert into SUBJECT_T values (2, "ディジタル電子回路", "芦田");
insert into SUBJECT_T values(3, "情報数理", "秋山");
insert into SUBJECT_T values(4, "システム工学", "藤田");
insert into DEPARTMENT_T values(1, "機械工学科", "1963/6/1");
insert into DEPARTMENT_T values(4, "電子情報工学科", "1989/4/1");
insert into STUDENT_T values(99101, "高専太郎", 5, 1);
insert into STUDENT_T values(99401, "高専花子", 5, 4);
insert into SCORE_T values(1, 99101, 80);
insert into SCORE_T values(2, 99401, 75);
insert into SCORE_T values(3, 99401, 85);
insert into SCORE_T values(4, 99401, 90);

 すべてを実行したのちにselect命令でレコードを表示すると下のようになります．

 では，外部キーとなっている値を親のテーブルで変更したとき，自動的に子のテーブルにも反映されるか確認してみましょう．例えばディジタル電子回路のSUBJECT_IDを2から20に変更します．それには下のようなupdate命令を書きます．

update SUBJECT_T set SUBJECT_ID=20 where NAME="ディジタル電子回路";

 この結果SUBJECT_Tの変化のみならず，SCORE_Tも下図のように変化しています．

このように外部キーに設定されていた値を自動的に変更してくれるので，テーブル間の不整合を防いでくれます．

以上でastah*を使ったER図の表記方法と，生成されるスクリプトによるテーブルの実装の説明を終わります．繰り返しになりますが，ER図でテーブルをしっかりと設計してから実装したほうが正確な実装が行えるはずですので，バグを直す無駄な労力を防ぎ，結果として時間が短縮できるかと思います．是非とも今回のようにER図を使って設計をしましょう．