経営情報システム　③応用編_データベースとSQL6問〜10問

問6：データベース設計における「サロゲートキー（代理キー）」と「ナチュラルキー（自然キー）」に関する記述として、最も適切なものはどれか。

A：サロゲートキーを設定したテーブルでは、データの一意性がシステム採番によってのみ保証されるため、元のビジネスデータ側の重複（同一顧客の二重登録など）を制約で防ぐことは不可能である。
B：ナチュラルキーは、システム開発時にエンジニアが考案したダミーの値であり、サロゲートキーは「顧客のマイナンバー」や「メールアドレス」など現実世界に存在する属性である。
C：ナチュラルキーを主キーに採用すると、サロゲートキーに比べてテーブル間の結合（JOIN）に必要なディスク容量やメモリ消費量が必ず増加する。
D：サロゲートキーは、業務的な意味を持たない連番（ID）などをシステム側で自動採番して主キーとしたものであり、業務要件の変更（コードの桁数変更など）による影響を受けにくい。
E：JIS Q 27001などのセキュリティ規格では、個人情報漏洩を防ぐため、すべてのテーブルの主キーをナチュラルキーに統一することが義務付けられている。

【第6問：正解と解説】

正解：D
【解説】
・A：不適切：サロゲートキーを主キーにしつつ、ビジネスデータ側の列（例：メールアドレスなど）に「UNIQUE（一意）制約」を別途設定することで、二重登録を防ぐことが可能です。
・B：不適切：説明が逆です。現実世界に実在する属性（社員番号、メールアドレス等）がナチュラルキーであり、システム用に作られた意味のない連番がサロゲートキーです。
・C：不適切：必ずしも増加するとは限りません。ナチュラルのデータ型（数バイトのコード等）によっては、サロゲートキーと変わらないか、むしろ結合効率が良い場合もあります。
・D：適切：サロゲートキー（代理キー）は、業務上の意味を持たない一意の識別子（自動連番など）です。製品コードの体系変更など、業務ルールが変更されても主キーを変更する必要がないため、保守性が高いというメリットがあります。対して、業務上の意味を持つコードなどをそのまま主キーにするのがナチュラルキー（自然キー）です。
・E：不適切：そのような義務や規定はISMS（JIS Q 27001）には存在しません。設計上の選択肢です。

問7：関係データベースの参照整合性制約において、親テーブル（参照される側）の行が削除されたとき、その行の主キーを参照していた子テーブル（参照する側）の対応する外部キー行も「連動して自動的に削除する」設定として、最も適切なものはどれか。

A：ON DELETE NO ACTION
B：ON DELETE SET NULL
C：ON DELETE CASCADE
D：ON DELETE RESTRICT
E：ON DELETE DEFAULT

【第7問：正解と解説】

正解：C
【解説】
・A：不適切：NO ACTIONは、子テーブルに参照されている行の削除要求があった場合、エラーにして削除を拒否（または標準の制約チェックを行う）設定です。
・B：不適切：SET NULLは、親テーブルの行が削除された際に、子テーブルの対応する外部キーの値を「NULL（空欄）」に自動更新する設定です。行を削除する動作とは異なります。
・C：適切：CASCADE（カスケード：連鎖）を指定すると、親テーブルのデータ削除に連動して、それを参照していた子テーブルの該当データも自動的に一括削除されます（例：顧客マスタから顧客を消すと、その顧客の過去の注文履歴も全削除される）。
・D：不適切：RESTRICTは、子テーブルに参照されている行の削除を拒否する設定です。NO ACTIONと似た効果ですが、制約チェックのタイミングが異なります。
・E：不適切：ON DELETE DEFAULTはSQL標準の一般的なオプションではなく、一部のRDBMSにのみ存在する非標準の設定です。親が削除された際に子の外部キーをデフォルト値に変更するという動作を指します。

A：INSERT INTO 社員 VALUES (‘S003’, ‘情報次郎’, ‘D03’);
B：DELETE FROM 部署 WHERE 部署コード = ‘D01’;
C：UPDATE 部署 SET 部署コード = ‘D99’ WHERE 部署コード = ‘D02’;
D：INSERT INTO 社員 VALUES (‘S003’, ‘情報次郎’, ‘D01’);
E：INSERT INTO 社員 VALUES (‘S001’, ‘複製三郎’, ‘D02’);

【第8問：正解と解説】

正解：D
【解説】
・A：不適切：部署テーブルに存在しない部署コード’D03’を持つ社員を登録しようとしているため、外部キーの「参照整合性制約」に違反してエラーになります。
・B：不適切：部署コード”D01″（総務部）を参照している社員（診断太郎）が子テーブルに存在するため、参照整合性制約により親行の削除がブロックされてエラーになります。
・C：不適切：部署コード”D02″（営業部）を参照している社員（中小花子）が子テーブルに存在するため、主キーの値を変更する操作も参照整合性制約に違反してエラーになります。
・D：適切：部署コード’D01’は部署テーブルに既に存在している（総務部）ため、これを外部キーに持つ新しい社員（S003）を追加する処理は、参照整合性制約を満たしており正常に実行できます。
・E：不適切：社員番号”S001″（診断太郎）はすでに社員テーブルに存在しており、主キー重複として一意性制約（PRIMARY KEY制約）に違反するためエラーになります。

問9：2つのテーブル「A」と「B」を結合するSQLの操作のうち、「LEFT OUTER JOIN（左外部結合）」の挙動に関する記述として、最も適切なものはどれか。

A：テーブルAの行のうち、テーブルBの主キーと値が「一致しない」行だけを反転させて抽出する。
B：テーブルAとテーブルBの両方で結合条件が完全に一致する行のみを抽出し、どちらか一方にしか存在しない行はすべて検索結果から除外する。
C：テーブルBのすべての行を抽出し、テーブルAに対応する行がない場合は結合を破棄して、SQLの実行エラーを返す。
D：テーブルAとテーブルBの全レコードのすべての組み合わせ（直積）を生成し、結合条件による絞り込みを一切行わずに出力する。
E：テーブルAのすべての行を抽出し、テーブルBに結合条件を満たす行があればその値を結合し、対応する行がテーブルBにない場合はテーブルBの列をすべて「NULL」として出力する。

【第9問：正解と解説】

正解：E
【解説】
・A：不適切：これは「差集合（EXCEPT / MINUS）」や「アンチ結合（Anti Join）」に近い説明です。LEFT JOINとは異なります。
・B：不適切：これは「内部結合（INNER JOIN）」の説明です。内部結合はどちらか一方にしか存在しない行を結果から除外します。
・C：不適切：RIGHT OUTER JOIN（右外部結合）はテーブルBの全行を保持しますが、SQLエラーを返すという説明は誤りです。対応する行がない場合はNULLで補完します。
・D：不適切：これは「クロス結合（CROSS JOIN）」の説明です。すべての組み合わせを出力する直積であり、LEFT JOINとは全く異なります。
・E：適切：LEFT OUTER JOIN（左外部結合）の正確な挙動です。FROM句で左側に書かれたテーブルAの全行を維持し、右側のテーブルBから条件に合うデータを横に結合します。右側にデータがない場合は、右側由来の列の値はすべてNULL（空欄）として表現されます。

問10：次の2つのテーブルから、SQL文によって「内部結合（INNER JOIN）」を行った結果として得られる行数（レコード数）として、最も適切なものを一つ選べ。【店舗テーブル】店舗ID | 店舗名 1 | 東京店 2 | 大阪店 3 | 名古屋店【売上テーブル】売上ID | 店舗ID | 金額 101 | 1 | 5000 102 | 1 | 3000 103 | 2 | 8000 104 | 4 | 2000 【実行するSQL】 SELECT * FROM 店舗テーブル INNER JOIN 売上テーブル ON 店舗テーブル.店舗ID = 売上テーブル.店舗ID;

A：2行
B：3行
C：4行
D：5行
E：12行

【第10問：正解と解説】

正解：B
【解説】
・A：不適切：行数の計算が誤っています。
・B：適切：INNER JOINは両方のテーブルで「店舗ID」が一致する組み合わせのみを出力します。・売上ID 101 (店舗ID:1) -> 東京店とマッチ (1行目) ・売上ID 102 (店舗ID:1) -> 東京店とマッチ (2行目) ・売上ID 103 (店舗ID:2) -> 大阪店とマッチ (3行目) ・売上ID 104 (店舗ID:4) -> 店舗テーブルに「4」がないため除外。・店舗ID 3 (名古屋店) -> 売上テーブルに「3」がないため除外。よって、マッチした合計3行が出力されます。
・C：不適切：4行と答えた場合、売上テーブルの全4件をカウントしてしまっています。売上ID 104（店舗ID:4）は店舗テーブルに対応する行がないためINNER JOINでは除外されます。
・D：不適切：5行と答えた場合、店舗3件＋売上4件など誤った合計をしています。INNER JOINは交差する行のみを抽出します。
・E：不適切：12行は店舗3件×売上4件の直積（CROSS JOIN）の件数です。INNER JOINは条件が一致する組み合わせのみを返すため直積にはなりません。