はじめに
今回は、応用情報技術者試験の午後『問６：データベース』 に出題されることが多い（ほぼ毎回）リレーションを回答する際のポイントについて書きます。
この記事は続編（２回目） となっております。
まずは前回の記事を読んで頂くことで、何をやろうとしているのかが分かると思います。
今回のサンプル
前回と同じ過去問を使って解説をします。
※出典：令和6年度 春期 応用情報技術者試験 午後 問題冊子
簡単におさらい（１つ目：『必ず１対多』）
前回はリレーションが『必ず１対多』になるパターンを紹介しました。
テーブル結合時、結合するキー項目以外にも主キーを持つ側が『多(矢の先端)』になるのでしたね。
２つ目：必ず『１対１』になるパターン
ここではテーブル『従業員』と『退職』を見ましょう。
実線のアンダーラインが主キーです。
- テーブル『従業員』の主キーは『会社番号』と『従業員番号』
- テーブル『退職』の主キーは『会社番号』と『従業員番号』
はい、この時点で確定です。理由は
- テーブル『従業員』と『退職』の主キーの組合せが全く同じ項目である。
- １つのテーブル内に、同じ主キーの組合せを複数レコード持つことはできない。
- 結合する主キーの組合せが片方は１件で、もう片方が複数とはならない。
- つまり、『１件ー１件』だから『１対１』。
実際のデータをイメージしてみよう
上記の説明だけでは分かり辛いので、実際にテーブル定義を作ってみましょう。
まず、データはこのようなイメージです。
テーブル『従業員』
| 会社番号（主キー） | 従業員番号（主キー） | 従業員氏名 | 部署番号 | 入社年月日 | 退職年月日 |
|---|---|---|---|---|---|
| 1001 | 111111 | 幕張太郎 | 44 | 2001/4/1 | 2019/3/31 |
| 1001 | 222222 | 稲毛花子 | 44 | 2013/10/1 | NULL |
| 9009 | 111111 | チーバクン | 77 | 2022/4/1 | 2024/3/31 |
テーブル『退職』
| 会社番号（主キー） | 従業員番号（主キー） | 在籍期間 | 退職理由 |
|---|---|---|---|
| 1001 | 111111 | 216 | 家庭の事情 |
| 9009 | 111111 | 24 | ご当地キャラになるため |
まず、テーブル同士でリレーション（結合）できる項目は『会社番号』と『従業員番号』です。
どちらのテーブルも、その組合せは主キーなので重複していないですよね？
重複していない同士を結合するので『していない同士』、つまり『１対１』（くどいｗ）
ちなみに、テーブル『退職』の方は１レコード足りません。
これは稲毛花子さんが退職していないので、退職理由がないためです。
退職理由にNULLを設定し、レコード数を合わせる方法でも良いのですが、次の説明をしたいのであえてこのようにしました。
ゼロを許容するかしないか
今回題材にしている過去問のER図は 『１対１』や『１対多』は表現できています。
しかし『そもそもお互いのデータが必ず存在する必要はないのか？』、つまり『１対０』や『０対多』を許すかが表現できません。
その表現について、ここでは『ゼロ表記あり』といった書き方をしておきます。
応用情報技術者試験では滅多に出題されませんが、出題されたことはあります。
詳しくは、私の過去の記事や下記の過去問解説動画をご参照下さい。
脱線しかけていますが、結論としては『今回のサンプルはゼロが許容される』ことになります。
なので、上記のデータはそのままとします。
SQLを書いて動かしてみよう
前回に続き、今回も実際にSQLを書いて実際に結合する様子を見てみましょう。
PostgreSQLで書きますので、よろしければ試してみてください。
※簡単に試す方法はこちらの記事を参考にして下さい。
まず、先程のデータを作ります。
-- 従業員テーブルを作る
CREATE TABLE 従業員 (
会社番号 INTEGER
, 従業員番号 INTEGER
, 従業員氏名 VARCHAR
, 部署番号 INTEGER
, 入社年月日 DATE
, 退職年月日 DATE
, PRIMARY KEY(会社番号,従業員番号) -- 主キー
);
-- 従業員テーブルにデータを登録
INSERT INTO 従業員 VALUES
(1001,111111,'幕張太郎',44,'2001/4/1','2019/3/31')
,(1001,222222,'稲毛花子',44,'2013/10/1',NULL)
,(9009,111111,'チーバクン',77,'2022/4/1','2024/3/31');
-- 退職テーブルを作る
CREATE TABLE 退職 (
会社番号 INTEGER
, 従業員番号 INTEGER
, 在籍期間 INTEGER
, 退職理由 VARCHAR
, PRIMARY KEY(会社番号,従業員番号) -- 主キー
);
-- 退職テーブルにデータを登録
INSERT INTO 退職 VALUES
(1001,111111,216,'家庭の事情')
,(9009,111111,24,'ご当地キャラになるため');
では、リレーションを張って両方のテーブルからデータを取得するSELECT文を発行します。
例として『誰が、いつ、何で退職したの？』という要件に応えるとします。
-- 従業員の退職日と退職理由を取得
-- 退職していない従業員を含むバージョン
SELECT
ju.会社番号
,ju.従業員番号
,ju.従業員氏名
-- 退職年月日が無い場合はダミー日付
,COALESCE(ju.退職年月日,'2999/12/31') AS 退職年月日
-- 退職理由がNULLの場合は『※在籍中』
,COALESCE(ta.退職理由,'※在籍中') AS 退職理由
FROM 従業員 ju
-- 左外部結合：従業員テーブルに存在するレコードは紐づかなくても出力する
LEFT OUTER JOIN 退職 ta
ON ju.会社番号 = ta.会社番号
AND ju.従業員番号 = ta.従業員番号
ORDER BY ju.会社番号 ,ju.従業員番号;
実行すると、このような結果になります。
在籍有無に関わらず、全員を表示した中から退職情報を見たい場合です。
もう１つのパターンもやってみます。
こちらの方が、より現実的な要件かもしれないです。
-- 従業員の退職日と退職理由を取得
-- 退職した従業員のみ取得するバージョン
SELECT
ju.会社番号
,ju.従業員番号
,ju.従業員氏名
,ju.退職年月日
,ta.退職理由
FROM 従業員 ju
-- 内部結合：お互いに紐づくレコードのみ出力する
INNER JOIN 退職 ta
ON ju.会社番号 = ta.会社番号
AND ju.従業員番号 = ta.従業員番号
ORDER BY ju.会社番号 ,ju.従業員番号;
退職者だけになりました。
稲毛花子さんは在籍中なので、この結果には出力されません。
さいごに
いかがでしたか？
「１対１を説明するだけなのに、これは流石にやり過ぎでは？」
と思うかもしれません。
前回も同じことを書いてますが、実務で役立てるには本質を理解する必要があるので、このくらい面倒な方がちょうど良いのでは？と思います。
そして、まだ続きがあります（笑）
次回以降は ER図を見ただけでは判断ができない場合を解説する予定です。
また懲りずに付いてきていただけると嬉しいです^^;
では、今回は以上です。
ここまでお付き合い頂き、ありがとうございましたm(_ _)m
Discussion