【応用情報】午後『データベース』のリレーションを制する!第5回『連関エンティティ』編
はじめに
今回は、応用情報技術者試験の午後『問6:データベース』 に出題されることが多い(ほぼ毎回)リレーションを回答する際のポイントについて書きます。
この記事は続編(5回目) となっております。
まずは前回までの記事を読んで頂くことで、何をやろうとしているのかが分かると思います。
- 【応用情報】午後『データベース』のリレーションを制する!第1回『必ず1対多』編
- 【応用情報】午後『データベース』のリレーションを制する!第2回『必ず1対1』編
- 【応用情報】午後『データベース』のリレーションを制する!第3回『本文を読んで判断』編
- 【応用情報】午後『データベース』のリレーションを制する!第4回『自己結合』編
今回のサンプル
今回はこちらの過去問を使い、解説をします。
※出典:令和5年度 秋期 応用情報技術者試験 午後 問題冊子
少し加工して使います
タイトルにある連関エンティティを説明するために、図中の赤枠テーブルを使います。
ただし、分かり易くするために一部の項目を削ります。
仕様を読み解く
では、赤枠テーブルの関係は何を表現しているでしょうか?
- 倉庫テーブル:倉庫コードで一意
- 商品テーブル:商品コードで一意
- 在庫テーブル:倉庫コードと商品コードの組合せで一意
つまり
どの倉庫に、どの商品が置いてあるのか?という情報を、在庫として記録しています。
連関エンティティの役割
しかし、このように思った方もおられるのではないでしょうか?
「え?倉庫テーブルと商品テーブルだけで良いのでは?」
果たして、そうでしょうか?
『多対多』になる
では、倉庫テーブルと商品テーブルだけで「どの倉庫に、どの商品が?」を表現します。
ER図では、このようになります。
では、実装できない理由をデータで説明します。
サンプルデータ
次のように、倉庫が3カ所と商品が3種類というシンプルなものとします。
テーブル『倉庫』
倉庫コード(主キー) | 倉庫名 |
---|---|
1 | 木更津 |
2 | 勝浦 |
3 | 銚子 |
テーブル『商品』
商品コード(主キー) | 商品名 |
---|---|
A | お徳用チョコ |
B | 高級なポテチ |
C | 世界一まずいグミ |
どうですか?
これらのテーブルだけで「どの倉庫に、どの商品が?」は、表現できません。
理由は、倉庫も商品も一意なので1倉庫につき1商品しか持てないからです。
※例)『木更津』は『高級なポテチ』だけしか持てない
ただ、業務要件次第では、本当に1倉庫に1商品しか持たないこともあり得ます。
(倉庫内いっぱいにポテチだけしか置かないとかw)
しかし、その場合はそもそも『多対多』ではありませんよね?
連関エンティティの出番
では、次は同じサンプルデータを使って連関エンティティ『在庫』を挟む場合です。
在庫テーブルを真ん中に挟み、矢印の先端(『多』の方)を全て在庫テーブルに向けます。
在庫テーブル
全ての倉庫に全ての商品がある想定で、在庫データを作ります。
テーブル『在庫』
倉庫コード(主キー) | 商品コード(主キー) |
---|---|
1 | A |
1 | B |
1 | C |
2 | A |
2 | B |
2 | C |
3 | A |
3 | B |
3 | C |
在庫データが作成できたことで、理論上の『多対多』 が実現できました!
ここまでご紹介した全てのテーブルを結合すれば、倉庫名と商品名の組合せも表現できます。
倉庫コード(主キー) | 倉庫名 | 商品コード(主キー) | 商品名 |
---|---|---|---|
1 | 木更津 | A | お徳用チョコ |
1 | 木更津 | B | 高級なポテチ |
1 | 木更津 | C | 世界一まずいグミ |
2 | 勝浦 | A | お徳用チョコ |
2 | 勝浦 | B | 高級なポテチ |
2 | 勝浦 | C | 世界一まずいグミ |
3 | 銚子 | A | お徳用チョコ |
3 | 銚子 | B | 高級なポテチ |
3 | 銚子 | C | 世界一まずいグミ |
結論
『多対多』を実現するには、連関エンティティを利用し 『1対多』と『多対1』を組合せる。
試験で見かけたときは
もし、1つのテーブルから複数本のリレーションが張られている箇所の回答をする場合、今回のサンプルを思い出していただくと、連関エンティティかどうか判断しやすいと思います。
補足(UMLの場合)
また、UMTP(UMLモデリング検定)を勉強すると、このような話が出てきます。
『もの』と『もの』の間に『こと』がある『もの・こと・もの』の関係
それが連関エンティティの考え方です。
倉庫(もの)・在庫(「在庫する」こと)・商品(もの)という関係
おまけ
では、毎回恒例ですが実際にSQLを動かします。
ご興味があれば、ご自身でも試してみてください。
SQLを書いて動かしてみよう
PostgreSQLで書きますので、よろしければ試してみてください。
※簡単に試す方法はこちらの記事を参考にして下さい。
まず、先程のデータを作ります。
-- 倉庫テーブルを作る
CREATE TABLE 倉庫 (
倉庫コード INTEGER PRIMARY KEY -- 主キー
, 倉庫名 VARCHAR
);
-- 倉庫テーブルにデータを登録
INSERT INTO 倉庫 VALUES
(1,'木更津')
,(2,'勝浦')
,(3,'銚子');
-- 商品テーブルを作る
CREATE TABLE 商品 (
商品コード CHAR(1) PRIMARY KEY -- 主キー
, 商品名 VARCHAR
);
-- 商品テーブルにデータを登録
INSERT INTO 商品 VALUES
('A','お徳用チョコ')
,('B','高級なポテチ')
,('C','世界一まずいグミ');
-- 在庫テーブルを作る
CREATE TABLE 在庫 (
倉庫コード INTEGER
, 商品コード CHAR(1)
, PRIMARY KEY(倉庫コード ,商品コード) -- 主キー
);
-- 在庫テーブルにデータを登録
INSERT INTO 在庫 VALUES
(1,'A')
,(1,'B')
,(1,'C')
,(2,'A')
,(2,'B')
,(2,'C')
,(3,'A')
,(3,'B')
,(3,'C');
では、リレーションを張って各テーブルからデータを取得するSELECT文を発行します。
先程の最後のサンプル、つまり『在庫情報を名称で取得する』とします。
-- 在庫情報を名称で取得
SELECT
st.倉庫コード
, wa.倉庫名
, st.商品コード
, pr.商品名
FROM 在庫 st
INNER JOIN 倉庫 wa USING(倉庫コード)
INNER JOIN 商品 pr USING(商品コード)
ORDER BY st.倉庫コード ,st.商品コード
;
実行すると、このような結果になります。
サンプルと同じになっています。
さいごに
いかがでしたか?
今回で、このシリーズは終わりです。
リレーションだけで5回も連載しましたが、もしここまで全部読んでSQLまで試すとなると、かなり理解が深まると思います。
応用情報技術者試験の対策として書いてみましたが、実務でも役に立つ内容ですので、開発現場の上流工程に携わる(または目指している)方々は、ぜひモデリングやSQLで実践してみて下さい。
では、今回は以上です。
ここまでお付き合い頂き、ありがとうございましたm(_ _)m
Discussion