データベーススペシャリスト 2024年 秋期 午後I 問題

共通の文章

オンライン学習プラットフォームの概念データモデリングに関する次の記述を読 んで,設問に答えよ。 教育事業者のA tld,オンライン学習プラットフォーム(UF,PFという)を運 営している。このたび,新規要件の追加に伴い,データベースを再設計した。 [現行業務 1.アカウント 1(1)PFを利用する個人をアカウントと呼び、アカウント番号(以下,番号を#で 示す)で識別する。 (2)PF上で学習教材を受講するアカウントを受講生と呼ぶ。受講生としてアカウ ントを登録する際,学習教材を購入するための支払情報を登録する。後述する 講師としてのアカウントを既に保持している場合,再度アカウントを登録する 必要はなく,支払情報を追加で登録する。 (3)PF上で受講生に提供する学習教材を制作するアカウントを講師と呼ぶ。講師 としてアカウントを登録する際,自身のプロフィールを登録する。受講生とし てのアカウントを既に保持している場合,再度アカウントを登録する必要はな く,自身のプロフィールを追加で登録する。 2.コース (1)コースは,講師がPF上で受講生に提供する一連の学習教材を指し,コース# で識別する。各コースの制作は1名の講師が行い,制作した講師が分かるよう になっている。全て有料であり,標準価格を設定している。 (2)コースは,複数のセクションから成る。セクションは,コースごとにセクシ ョン#で識別する。 (3)セクションは,一つ又は複数の講義動画と,講義動画を視聴した後の理解度 を確認する高々ーつのテストから成る。講義動画とテストとを併せてコンテン YEU,セクションごとにコンテンツ#で識別する。 ・テストは,複数の設問から成る。設問は,テストごとに設問#で識別する。 ・設問は,全て単一選択式であり,設問ごとに設問文と配点を設定する。 ・設問の各選択肢は,設問ごとに選択肢#で識別し,選択文と正解フラグを設 定する。 3.コース購入 (1)受講生は,コースを検索して,受講したいコースを購入する。コースの購入 は,コース購入#で識別する。購入情報(購入年月日,購入価格及び受講生ア カウント#)は,PF上に記録される。 (2)受講生は,PF上で利用できるクーポンを使用すると,標準価格より安くコー スを購入できる。クーポンを使用する際は,クーポン#を指定して,購入の手 続を行う。クーポンの併用はできない。 ・クーポンは,講師が自身のコースの販売促進を目的に発行している。 ・クーポンは,クーポン#で識別し,割引率や値引額,クーポンの適用期間及 び同一クーポンの使用可能人数をもつ。使用可能人数は最大1,000名であり, クーポン使用者数が使用可能人数の上限に達すると,適用期間にかかわらず, クーポンの使用は不可となる。 (3)受講生は,お薦めしたいコースがある場合,アカウントをもっている相手に コースをプレゼントすることができる。受講生がコースをプレゼントするには, 相手のアカウントと対象のコースをそれぞれ一つ指定して購入の手続を行う。 4.コース受講 (1)受講生は,購入したコース及びプレゼントされたコースを,いつでも受講す ることができる。 ①コンテンツの受講の完了は,講義動画については視聴を終えた時点,テス トについては全設問に解答した時点である。セクション内の全コンテンツの 受講が完了するとセクションの受講が完了となり,全セクションの受講が完 了するとコースの受講が完了となる。 ②自己研鐘が目的なので。どのコンテンツから受講してもよい。テストに合 格基準点はなく,どのコンテンツも繰り返し受講することができる。 ③コースの受講中又は完了後,5段階評価と感想を登録することができる。 ④受講生は,自分が最後に実施したテストで選択した各設問の選択肢と解答 日時を確認することができる。また,自分が過去に実施したテストについて は,最高獲得点数と,全設問の合計点数の履歴を確認できる。 (2)受講生は,コンテンツの内容に疑問があれば,PF上で講師に質問することが ー8一 できる。各質問に対する講師からのコメントは1回だけできる。 5.購入実績及び受講の確認 講師はPF上で,自分が制作したコースを購入した受講生の購入情報,クーポン を使用した場合のクーポン#受講生による5段階評価及び感想を確認することが できる。 [企業向けサービスの追加〕 PFに,次のような企業向けのサービスを追加することにした。 1.受講生の登録- 企業は,研修対象の従業員を受講生としてアカウントを登録する。これを法人 受講生と呼び,現行業務の受講生とは区別する。既に個人でアカウントを保持し ていても,法人用のアカウント#を新たに付与する。 2.研修カリキュラムの登録 (1)企業は,受講させる一つ以上のコースをまとめた研修カリキュラムを作成す る。研修カリキュラムは,企業ごとに研修カリキュラム#で識別し,研修カリ キュラム名をもつ。研修カリキュラムに含まれるコースには,受講順を設定す S, (2)企業は,法人受講生に対して一つ以上の研修カリキュラムを受講させる。 (3)研修カリキュラムへの法人受講生の割当てでは,あらかじめ複数の法人受講 生を束ねたグループを作成し,グループ単位で割当てを行う。グループは,企 業ごとにグループ#で識別し,グループ名をもつ。また,同じ研修カリキュラ ACH,割り当てられるグループによって,受講開始可能年月日及び受講完了 期限年月日が異なる場合がある。 (4)テストを含むコースについては,研修カリキュラムごとに各テストの合格基 準点を設定できる。 3.法人受講生によるコース受講 (1)法人受講生は,指定された研修カリキュラムを期限までに受講する。受講を 開始すると,研修カリキュラム受講開始日時が記録される。全てのコースの受 講が完了すると,研修カリキュラムが完了となり,研修カリキュラム受講完了 日時が記録される。 (2)現行業務のコース受講と同じサービスを提供するが,企業が合格基準点を定 =H ws めているテストについては,合格基準点に達しないとテストの受講は完了とな らない。 [受講生からの要望〕 受講生から,学習に対する理解を深めるために,次の要望があった。 ・コースを提供する講師だけではなく,他の講師及び受講生も質問に対して回数に 制限なくコメントできるようにしてほしい。 ・その際,誰がコメントしたかを特定できるようにしてほしい。 ・質問だけではなく,既出のコメントを指定してコメントできるようにしてほしい。 [概念データモデルと関係スキーマの設計]〕 1.[現行業務〕に基づく設計 現行業務の概念データモデルを図1に,関係スキーマを図2に示す。 ーー デコpa|es :旧記 ハハ 選択肢詳細履歴 図1現行業務の概念データモデル(未完成) アカウント(アカウント#アカウント名,||) 講師(アカウント#,講師プロフィール) 受講生(アカウント#支払情報) コース(コース#』アカウント#コース名,標準価格) セクション(コース#セクション#4セクション名) コンテンツ(コース#4セクションれコンテンツ#れコンテンツ名,コンテンツ区分) 講義動画(コース#4セクション#コンテンツ#講義時間) FARK(コース#セクション#コンテンツ#解答制限時間) テスト設問(コース#』セクション#コンテンツ#設問4設問文,配点) テスト設問選択肢(コース#』セクション#4コンテンツ#設問選択肢4選択文,正解フラグ) クーポン(クーポン#コース#,割引率,値引額,適用開始年月日,適用終了年月日,使用可能人数, クーポン発行年月日)- コース購入(コース購入』コース#購入年月日購入価格,|a|) コース受講(アカウント#コース#コース受講開始日時,コース受講完了日時,5段階評価,感想) セクション受講(アカウント#れコース4セクション#4セクション受講開始日時, セクション受講完了日時) コンテンツ受講(アカウント#れ4コースセクション#コンテンツ#』コンテンツ受講開始日時, コンテンツ受講完了日時) テスト結果(アカウント#』コースルセクション#コンテンツ#最高獲得点数) テスト結果詳細(アカウント#』コース#』セクション#』コンテンツ#』|b|,SA) テスト結果履歴(アカウント#』コース#』セクション#コンテンツ#』テスト実施日時,合計点数) 質問(質問』コース#』セクション#コンテンツ#』アカウント#質問タイトル,質問文,質問日時) コメント(質問コメント文,コメント日時) 注記設問の都合上,網掛け部分は表示していない。 図2現行業務の関係スキーマ(未完成 2.[企業向けサービスの追加]」に基づく設計 企業向けサービスの追加部分の概念データモデルを図3に,関係スキーマを図4 に示す。 | 注記1コースとテストのエンティティタイプは,BI中のものと同じである。 注記2設問の都合上,網掛け部分は表示していない。 図3企業向けサービスの追加部分の概念データモデル(未完成) ュー(a> 企業(企業4企業名) テスト(a)) 法人受講生(アカウント#企業) グループ(企業れグループ#』グループ名) 研修カリキュラム(企業#研修カリキュラム#研修カリキュラム名) (企業#コース# (eset,アカウント!%[dg|) (eset,グループ%|s|) (eset,|f ],研修カリキュラム受講開始日時,研修カリキュラム受講完了日時) (企業#コース#』セクション#』コンテンツれ|g]) 注記1コースとテストの網掛け部分は,図2と同じ属性である。 注記2図中の[あ|~|お|には,図3の[|あ|]~[|お|と同じ字句が入る。 図4企業向けサービスの追加部分の関係スキーマ(未完成) 解答に当たっては,巻頭の表記ルールに従うこと。ただし,エンティティタイ プ間の対応関係にゼロを含むか否かの表記は必要ない。エンティティタイプ間の リレーションシップとして“多対多′”のリレーションシップを用いないこと。サ ブタイプが存在する場合,他のエンティティタイプとのリレーションシップは, スーパータイプ又はいずれかのサブタイプの適切な方との間に設定すること。同 ーのエンティティタイプ間に異なる役割をもつ複数のリレーションシップが存在 する場合,役割の数だけリレーションシップを表す線を記述すること。属性名は, 意味を識別できる適切な名称とすること。関係スキーマに入れる属性を答える場 合,主キーを表す下線,外部キーを表す破線の下線についても答えること。

共通の文章

情報システム会社のプロジェクト稼働管理システムのデータベース物理設計・SQL 設計・性能に関する次の記述を読んで,設問に答えよ。 情報システム会社のE社は,自社のプロジェクト乏働管理システム(以下,PJシ ステムという)を,RDBMSを用いて更改することになり,Fさんが実装を任された。 (RDBMSの主な仕様] 1.DMLのアクセス経路は,RDBMSによって索引探索又は表探索が選択される。 2.索引は,クラスタ性という性質によって,高クラスタな索引と低クラスタな索 引に分けられる。 ・高クラスタな索引は,キー値の順番と,キーが指す行の物理的な並び順が一致 しているか,完全に一致していなくても,隣接するキーが指す行が同じページ に格納されている割合が高い。 ・低クラスタな索引は,キー値の順番と,キーが指す行の物理的な並び順が一致 している割合が低く,行へのアクセスがランダムになる。 (BORE) 1.組織,従業員,役職,ランク,時間単価 (1)E社には,複数の組織がある。組織は階層構造であり,最上位の組織以外は いずれかーつの上位組織に属する。 (2)従業員は,従業員コードで識別し,いずれかーつの組織に属する。 (3)役職には,SE,シニアSE,マネージャなどがある。役職は役職コードで識別 する。従業員はいずれかーつの役職をもつ。 (4)ランクは,労務費の時間単価を区別するもので,ランクコードで識別する。 役職はいずれかーつのランクに対応する。 (5)時間単価は,ランク別組織別年月日別に決めている。組織の変更,従業員の 異動などによって,月初に時間単価を見直すことがある。| 2.プロジェクト,稼働計画,稼働実績 (1)プロジェクトWE,Psという)は,従業員の稼働状況を管理する単位であ ー20一 る。従業員は複数のPJに参加することがあり,PJに参加していない従業員も BU De (2)PJに必要な人員を要員という。 (3)PJ開始前に稼働計画を立案するとき,要員ごと参加年月ごとに計画時間を見 積もる。参加する従業員が確定したとき,稼働計画の要員に対して従業員を割 り当てる。PJ開始後,必要に応じて計画を修正する。 (4)PJに参加している各従業員は,稼働実績として月内の日別PJ別の稼働時間 を入力する。従業員は同じ日に複数PJの稼働時間を入力できる。 [PJシステムのテーブル〕 1.テーブル構造,列の意味・制約,統計情報・索引定義 主なテーブルのテーブル構造を図1に,主な列の意味・制約を表1に示す。また, “従業員”"テーブルの主な統計情報・索引定義を表2に,“稼働実績"テーブルの 主な統計情報・索引定義を表3に示す。 組織(組織コード,組織名,発足年月日,廃止年月日,組織長従業員コード,上位組織コード) 従業員(従業員コード,CHARS,組織コード,役職コード,退職年月日,更新年月日) 従業員履歴(従業員コード,更新年月日,従業員氏名,組織コード,役職コード) 役職(役職コード,役職名,ランクコード) 時間単価(ランクコード,組織コード,適用開始年月日,時間単価) ランク(ランクコード,ランク名) PJ(PJコード,PJ名,開始年月日,終了年月日,PM従業員コード) 稼働計画(PJコード,要員番号,計画年,計画月,ランクコード,従業員コード,計画時間) 稼働実績(PJコード,稼働年月日,従業員コード,稼働時間) M1主なテーブルのテーブル構造 表1主な列の意味・制約 意味制約 上位組織コード|組織が属する上位組織の組織コード。最上位組織の上位組織コードにはNULL を設定する。 更新年月日“Get 8”テーブルの列値を更新した日付 PJを管理する従業員の従業員コード 要員番号稼働計画においてPJ内で要員を一意に識別する通し番号。要員に従業員が割 り当てられていない場合,当該要員の従業員コードにはNULLを設定する。 計画時間PJごと要員ごと計画年ごと計画月ごとに見積もった作業計画。単位は時間。 稼働時間PJごと稼働年月日ごと従業員ごとの作業実績。単位は時間。 =21にニニ 表2“HERA”テーブルの主な統計情報・索引定義(一部省略) 上段:行数,下段:ペRB副次索引1 fama[eel 1!| ao[mene。|879|| 20行ノページ|組織コード。|490||1i| 表3“稼働実績"テーブルの主な統計情報・索引定義 +R:行数,下段:ペ主索引副次索引1|副次索引2 fae|swl ji|||| 100行ノページ[amma[eof 3|1!|| 稼働時間ee i|| | 注記網掛け部分は表示していない。 2.“KER”テーブルの行更新における更新履歴処理 “HERA”テーブルの組織コード,役職コードを更新するとき,当該従業員の 更新前の行を更新の履歴として"従業員履歴"テーブルに挿入する。 3.組織”テーブルの行削除処理 E社では,組織の改廃がある。PJ管理に不要になった組織コードを削除する場合 次のような手順で行う。 ①廃止済みの組織であり,DD,PJが終了済みなどPJ管理に不要と判断できる 組織コードを,SELECT文を用いて調べる。 ②①で調べた組織コードの行を,“組織”テーブルからDELETE文を用いて削除す る。 [テーブルの定義と実装〕 1.テーブルの定義 (1)Fさんは,図1中の各テーブルを定義するCREATE TABLE文を設計した。ここ で,各CREATE TABLE文には外部キー制約を実装することとした。そのうち, “組織”テーブルを定義するCREATE TABLE文を,図2に示す。 22— CREATE TABLE組織( 組織コードCHAR(8)NOT NULL PRIMARY KEY, 組織名VARCHAR(50)NOT NULL, 発足年月日DATE NOT NULL, 廃止年月日DATE, 組織長従業員コードCHAR(8), 上位組織コードCHAR(8), FOREIGN KEY(組織長従業員コード)REFERENCES従業員(従業員コード)ON DELETE RESTRICT, FOREIGN KEY(上位組織コード)REFERENCES組織(組織コード)ON DELETE RESTRICT) 2“組織”テーブルを定義するCREATE TABLE文 (2)Fさんは,他の表が未定義の状態で“組織"テーブルを定義する図2の CREATE TABLE文を実行したところ失敗した。そこで,Fさんは,図2のCREATE TABLE文を見直し,次の①ー③の順番で定義を実行したところ,全ての実行が 成功した。 ①“組織”テーブルを定義する図2中から,| a|を外部キーとする指 定を削除したCREATE TABLE文を実行する。 ②“従業員”,"役職",“時間単価”,“ランク”の各テーブルを定義する の順番で実行する。 ③“Meテーブルに対する| e|文を用いて,|a。|を外部キ ーとする指定の定義を追加する。 2.テーブルへの行登録 次に,Fさんは,“組織”テーブルにINSERT文を用いて行を挿入した。次いで “従業員”,役職”,“時間単価",“SUD”の各テーブルに対してもINSERT文を 用いて行を挿入した。その後,UPDATE文で適宜列値を更新した。 [稼働計画の立案・稼働実績の確認〕 Fさんは,稼働計画の立案及び稼働実績の確認を支援するためのSOL文を設計した。 設計したSQL文の例を,表4に示す。 —23 ais 表4稼働計画の立案及び稼働実績の確認を支援するSQL文の例 SOL文の構文(上段:目的,下段:構文) 要員が埋まらないPJがあるので,2024年11月の稼働計画において役職コードがSEの従業員を対象 に,稼働計画に登録されていない従業員も含めて要員を探す。従業員に対して,組織ごとに計画時 間の少ない順に順序付けし,順序に沿って従業員数が均等となるように1~3の番号を付与した"時 RAR”,及び従業員全体で計画時間の少ない順に順位付けした“時間ランク"を算出する。結果行 は組織コード順,時間ランクが低い順,従業員コード順に並べる。ここで,稼働計画にない従業員 の計画時間はゼロで表示する。 SQL1|SELECT組織コード,A.従業員コード,COALESCE(SUM(計画時間),9)AS計画時間合計, NTILE(3)OVER(PARTITION BY ORDER BY)AS時間階級, RANK()OVER(ORDER BY)AS時間ランク FROM A LEFT OUTER JOIN B ON A.従業員コード=B.従業員コードAND計画年=2024 AND HA=711’ WHERE役職コード=’SE’ GROUP BY組織コード,A.従業員コードORDER BY組織コード,時間ランク,A.従業員コード 指定した組織に所属する従業員の指定した月の1か月分の稼働時間の合計を調べる。 SELECT SUM(稼働時間)AS稼働時間合計 SQL2|FROM乏働実績K JOIN従業員S ON K.従業員コード=S.従業員コード AND組織コード=:hv1 AND稼働年月日BETWEEN:hv2 AND:hv3 注記ホスト変数のhv1には組織コードを,hv2には開始年月日(例:2024-06-01)を,hv3には終了年月日 (fill:2024-06-30)を設定する。 [問合せの性能改善] Fさんは,“稼働実績'"テーブルへの問合せに利用される表4中のSQL2について, 性能の改善を依頼された。Fさんが調べたところ,稼働実績を一括入力する従業員が 多く,1か月単位で見たとき,行の登録順が従業員,稼働年月日,PJ』コード順であり, 従業員当たり1か月分の行が高々2ページに格納されることが分かった。そこで,R 引のクラスタ性と次の三つの前提を踏まえて,(1)~(5)の手順で性能改善を試みた。 ・それぞれの列値は均等に分布していると仮定する。 ・PJに参加しない従業員だけで構成される組織はないと仮定する。 ・全従業員が同じ曜日で働いていると仮定し,1か月は20日として計算する。 (1)SQL2のアクセス経路として,“HEHE”テーブルを外表,“稼働実績"テーブル を内表とする入れ子ループ結合を想定する。 (2)このアクセス経路では,まず外表から指定した組織コードに対して,外表の副 次索引1を用いて平均22行を読み込む。外表の副次索引1は低クラスタな索引な ので,最大で|+|]ページを読み込む。 (3)外表から読み込んだ従業員コード1件ごとに,内表の副次索引1を用いて①④従 業員1人当たりの稼働実績である1,200行を読み込み,行データの稼働年月日に 対してBETWEEN述語を評価する。表3の乏働年月日列の列値個数は1,000(50か 月分)なので,内表の集計対象の行は。1人当たり|9|行である。稼働実 績を計上している従業員は組織当たり|"h|人なので,集計対象の行は組 織当たり| i|行となる。内表の副次索引1は高クラスタなので,読込みペ ージ数は組織当たり最大|」|ページである。 (4)次に,読込みページ数を削減するために,{従業員コード,稼働年月日}をキー とする副次索引3を追加した場合の性能を検討した。この索引を使用した場合, 副次索引1と比較すると,1か月分を索引で絞り込めるので,表からの読込み行 数及び読込みページ数は|k|分の1に削減される。 (5)副次索引3の利用によって,表からの読込み行数及び読込みページ数を削減で きるので,副次索引3を実装することにした。