令和7年秋期試験午後問題　問6

問6　データベース

⇱問題PDF
受講管理システムの改修に関する次の記述を読んで，設問に答えよ。
広告
　D社は，日本人を対象に英会話のオンライン教育サービスを提供する中堅企業である。従来は，ビジネス英会話の講座を提供していたが，それに加えて日常英会話の講座を追加することになった。
　従来のビジネス英会話講座と新規に追加する日常英会話講座との違いを表1に示す。
pm06_1.png
　日常英会話講座のジャンルは，サービスを開始してから1年程度は，申込み具合などを考慮して変更する可能性がある。
　講師は割り当てられた講座の開始時刻から終了時刻まで拘束されるが，それ以外の時間帯は自由である。現状は勤務日と講座担当の割当てだけで勤務を管理している。講座の受講管理には，受講管理システムを利用している。
　現状のビジネス英会話講座を管理する受講管理システムのE-R図を図1に示す。
pm06_2.png
　開催スケジュールについては，同じ講座が複数回開催されるので，開催番号で区別している。
　受講管理システムのデータベースでは，E-R図のエンティティ名を表名にし，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。

〔受講管理システムの関係スキーマの変更〕
　講座の種類には，表1に示す内容に沿った値以外のものを格納できないように定義域制約を適用する。
　日常英会話講座の追加に当たり，日常英会話講座のジャンルの変更の容易性と講師の勤務管理の厳密化を考慮して，受講管理システムの関係スキーマの変更内容を次のように検討した。
  • 講座エンティティをスーパータイプにして，ビジネス英会話講座エンティティ，日常英会話講座エンティティをサブタイプとして新規に追加する。講座エンティティにビジネス英会話講座か日常英会話講座かを区別する講座区分属性を追加して，講座エンティティからc，dの①二つの属性をサブタイプに移す。
  • 勤務日でも講師が講座を受けもつことができない時間帯は予約できないようにするため，勤務日の勤務可能時間帯を管理する勤務可能時間エンティティを追加する。
　追加及び変更するエンティティを図2に示す。
pm06_3.png
〔表の定義の追加，変更〕
　受講管理システムの関係スキーマの変更に伴い，新規表の定義，既存表の変更を行う。今回の変更では，スーパータイプ，サブタイプのエンティティごとに別の表にしている。講座表の変更に当たり，削除する列の情報は削除前に保存して，追加したサブタイプに反映する。
　講座表の変更を行うSQL文を図3に示す。
pm06_4.png
　勤務可能時間表を作成するSQL文を図4に示す。
　図4で，勤務日表の行が削除された場合は，参照元の勤務可能時間表の行も自動的に削除されるようにしている。
pm06_5.png
〔勤務日の削除〕
　勤務日が削除可能となるのは，指定した日の勤務年月日に開催する講座を講師が担当していない場合である。
　指定した日にある講師が担当している講座の講座コードの一覧を取得するSQL文を図5に示す。ここで，":講師コード"と":勤務年月日"は，それぞれ対象となる講師の講師コードと指定した日の年月日を格納する埋込み変数である。
　なお，CAST指定は，データ型を変換する際に使用される。
pm06_6.png
広告

設問1

図1中のa，bに入れる適切な属性名及びエンティティ間の関連を答え，E-R図を完成させよ。なお，属性名の表記及びエンティティ間の関連は図1の凡例に倣うこと。
(※正誤判定の都合上，主キー属性は{属性名}，外部キー属性は(属性名)と入力してください)

解答例・解答の要点

a：開催番号
b：←

解説

〔aについて〕
受講予約・結果エンティティは、受講者がどの開催回を予約し、実際に受講したか（結果）を記録するものです。本文には「開催スケジュールについては，同じ講座が複数回開催されるので，開催番号で区別している」とあり、ある開催回の識別には講座コードだけでなく、開催番号が必要なことがわかります。

ところが、受講予約・結果エンティティでは、開催スケジュールエンティティを参照するためのキーとして、講座コードしか保持していません。このままでは、開催スケジュールの1つのレコードを特定することができないため、属性として「開催番号」の追加が必要と判断できます。受講予約・結果エンティティのレコードは、受講者コードと開催回（講座コード、開催番号）の組合せで一意になるため、開催番号は主キー（かつ外部キー）を構成します。したがって、空欄aには「開催番号」が当てはまります。

∴a＝開催番号

〔bについて〕
表1では、ビジネス英会話講座の詳細として「6人までのグループに対して1人の講師がつく受講形態」とあります。ある開催回には複数の受講者が参加することが想定されますから、受講予約・結果エンティティには同一の講座コード及び開催番号の組をもつ複数のレコードが存在しえます。したがって、受講予約・結果エンティティと開催スケジュールエンティティの多重度は多対1であり、空欄bには「←」が当てはまります。

原則として、ある主キーをもつエンティティと、その主キーを外部キーに持つエンティティのカーディナリティ（多重度）は「1対多」です。

∴b＝←
広告

設問2

〔受講管理システムの関係スキーマの変更〕について答えよ。
  • 本文，図2及び図3中のc，dに入れる適切な属性名を答えよ。
  • 本文中の下線①について，二つの属性をサブタイプに移した理由を20字以内で答えよ。
  • 図2中のeに入れる適切な属性名を答えよ。

解答例・解答の要点

  • c：レベル ※順不同
    d：ジャンル ※順不同
  • 定義域が講座区分によって異なるから (17文字)
  • e：年齢層

解説

  • 〔cdについて〕
    本文では「講座エンティティから…二つの属性をサブタイプに移す」としているため、空欄には講座エンティティにある属性のうち2つが入ることがわかります。

    本問のように、あるエンティティを、スーパータイプとサブタイプに分けて表現することを特化と言います。特化では、スーパータイプには全てのサブタイプに共通する属性を持たせます。一方、特定のサブタイプに固有の属性は、サブタイプ側に定義するのが基本となります。

    表1を見ると、ビジネス英会話講座と日常英会話講座では、レベルとジャンルに設定できる値が異なります。レベルとジャンルは一見すると共通属性ですが、〔受講管理システムの関係スキーマの変更〕では、「講座の種類には，表1に示す内容に沿った値以外のものを格納できないように定義域制約を適用する」とあります。属性名は同じでも定義域（取りうる値の範囲）が異なるため、この2つの属性はサブタイプ側に分けて制約を定義するのが適切です。

    ∴cd＝レベル、ジャンル

  • レベルとジャンルをサブタイプ側に移した理由は、前述のとおり、講座区分によって定義域が異なるためです。仮にレベルをスーパータイプ側で保持する場合、定義域制約は両講座をまとめたもの（初級／中級／上級／A1／A2／B1／B2／C1／C2）にせざるを得ません。しかし、このようにすると、ビジネス英会話講座に"A1"を登録できたり、日常英会話講座に"中級"を登録できたりしてしまい、本文中の要件を満たせません。したがって、サブタイプ側で保持する必要があります。

    ∴定義域が講座区分によって異なるから

  • 〔eについて〕
    図2の日常英会話講座には、空欄eとしてサブタイプ固有の属性が一つ追加されています。ビジネス英会話講座には存在しないことから、この属性は日常英会話講座に固有のものだと考えられます。表1を参照すると、日常英会話講座にのみ設定される種別として「年齢層」があります。したがって、空欄eは「年齢層」になります。

    ∴e＝年齢層
広告

設問3

図3中のfに入れる適切な字句を答えよ。

解答例・解答の要点

f：ALTER

解説

〔fについて〕
関係スキーマの変更に伴い、講座表には"講座区分"列を追加し、"レベル"と"ジャンル"列を削除する必要があります。図3はこの変更を行うためのSQL文です。

列の追加や削除は表定義を変更する操作であるため、ALTER TABLE文を使用します（alterは"変更する"という意味）。列の追加・削除の構文は次のとおりです。
（列追加）ALTER TABLE テーブル名 ADD COLUMN 列名 型
（列削除）ALTER TABLE テーブル名 DROP COLUMN 列名 [CASCADE]
※CASCADEを付けると、削除される列に関連するインデックスや制約も同時に削除される
ALTER TABLE文は列の追加・削除のほか、定義域の変更、制約の変更、デフォルト値の設定などにも利用されます。したがって、空欄fには「ALTER」が当てはまります。

∴f＝ALTER
広告

設問4

図4中のg，hに入れる適切な字句を答えよ。

解答例・解答の要点

g：勤務年月日，講師コード
h：CASCADE

解説

〔gについて〕
空欄2つはFOREIGN KEY制約、すなわち外部キー制約の構文の一部です。FOREIGN KEYの構文を確認します。
FOREIGN KEY(列名1, 列名2, ...) REFERENCES 参照先テーブル名(参照先の主キー1, 参照先の主キー2, ...)
空欄gは勤務日()の中にあるため、勤務日テーブルの列名が入ります。図2より、勤務可能時間テーブルの主キーは勤務年月日と講師コードであり、これは勤務日テーブルの主キー（勤務年月日、講師コード）と一致します。このため、この2つのキーが参照先になると判断できます。以上より、空欄gには「勤務年月日, 講師コード」が当てはまります。

∴g＝勤務年月日, 講師コード

〔hについて〕
ON DELETE句の後ろには、参照動作を指定します。参照動作とは、参照先の行が削除・変更されて参照整合性が崩れる場合に、参照元の外部キーをどのように扱うかを定めるものです。次の5つの指定方法があります。
CASCADE
参照先のデータが更新・削除されると、対応する外部キーの値も自動的に更新・削除される
RESTRICT
参照制約違反となるデータの更新・削除を拒否する
NO ACTION（デフォルトの動作）
処理の終了時に参照整合性を確認し、違反していれば更新・削除を拒否する
SET NULL
参照先のデータが更新・削除されると、対応する外部キーの値にNULLが設定される
SET DEFAULT
参照先のデータが更新・削除されると、対応する外部キーの値にデフォルト値が設定される
本文では「勤務日表の行が削除された場合は，参照元の勤務可能時間表の行も自動的に削除される」とあるため、該当する参照動作は「CASCADE」です。したがって、空欄hには「CASCADE」が当てはまります。

∴h＝CASCADE
広告

設問5

図5中のiに入れる適切な字句を答えよ。

解答例・解答の要点

i：ON t0.講座コード = t1.講座コード AND t0.開催番号 = t1.開催番号

解説

〔iについて〕
INNER JOIN句は、表同士を内部結合する際に使用する構文です。INNER JOIN句では両表の行を対応付ける条件をON句で指定しますが、図5のSQL文にはON句がありません。よって、空欄には、講座担当割当テーブルと開催スケジュールテーブルを結合するための条件式が入ります。

講座担当割当テーブルと開催スケジュールテーブルに共通する属性は講座コードと開催番号であり、この2つの属性で1つのレコードを特定しています。そのため、開催回と講師コードを適切に関連付けるためには、この2つの属性を結合キーとする必要があります※。これにより、開催スケジュールのレコードに講師コードが関連付けられます。この一覧から、講師コードと指定勤務年月日でレコードを絞れば、該当する講座コードの取得が可能です。

講座担当割当テーブルはt0、開催スケジュールテーブルはt1というエイリアス（別名）が指定されているため、結合条件式はこれを使用して「t0.講座コード = t1.講座コード AND t0.開催番号 = t1.開催番号」とするのが適切です。

∴i＝ON t0.講座コード = t1.講座コード AND t0.開催番号 = t1.開催番号

※講座コードだけで結合した場合、担当していない開催回にも講師コードが関連付けられてしまいます。また、開催番号だけで結合した場合、担当していない講座にも講師コードが関連付けられてしまいます。
広告
模範解答
広告

前の問題次の問題

▲
Pagetop