応用情報技術者過去問題 平成31年春期 午後問6
⇄問題文と設問を画面2分割で開く⇱問題PDF問6 データベース
薬剤管理システムの再構築に関する次の記述を読んで，設問1～4に答えよ。
W病院は，複数の外来診療科をもっており，症状や病状に応じて処方箋を発行している。W病院には院内薬局があり，受診者の多くは院内薬局で薬剤の処方を受ける。
処方箋には期限があり，発行年月日から有効年月日までを，薬剤の処方を受けることのできる期間としている。
W病院では，受診者への医療サービス向上を目的に，薬剤管理システムの再構築を行うことになった。再構築するシステムには，医師の処方箋作成を支援する次のチェック機能を実装する。
開発に当たり設計した，薬剤管理システムのデータベースのE-R図を図1に示す。
なお，"在庫量_大人1日"，"処方量_大人1日"とは，"大人1日の分量"を単位とした在庫量，処方量を示す。
このデータベースでは，E-R図のエンティティ名をテーブル名に，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。
業務処理と操作対象のテーブルを表1に示す。また，テーブルの列に対する処理内容を表2に示す。ここで，表2中の括弧付き数字は，表1中の業務処理の括弧付き数字に対応している。
〔薬剤併用チェック処理〕
薬剤管理システムでは，薬剤併用情報テーブルに"併用禁忌"と"併用注意"となる薬剤の組合せを保持しており，この情報を使い，薬剤併用チェックを行う。
"併用禁忌"と"併用注意"に該当する薬剤の組合せ一覧を出力するSQLを図2に示す。ここで，":受診者ID"，":半年前年月日"，":処方箋ID"は，該当の値を格納する埋込み変数である。また，TO_DATE関数は，指定された文字型の年月日をDATE型に変換するユーザ定義関数である。
薬剤の組合せ一覧には，今回の外来受診では処方しない薬剤や院内薬局で処方を受けなかった薬剤の組合せも含まれており，出力内容を医師が確認し，必要に応じて処方する薬剤を見直す。見直しの結果，処方箋明細が0件になることもあるが，このような場合には，処方箋の発行は行わない運用とし，処方箋レコードは削除しない。
〔在庫チェック処理〕
在庫チェックでは，"発行した処方箋に記載の薬剤が，院内薬局で有効年月日まで確保されるよう，在庫の保証を行う。"という要件の判定を簡素化するために，処方箋を発行し，有効年月日までに院内薬局で処方する可能性のある薬剤の処方量の合計を，確保量として管理するためのビューを作成することにした。
ビューを作成するSQLを図3に示す。ここで，CURRENT_DATE関数は，参照時の日付をDATE型で返す日時値関数である。
在庫チェックで在庫不足が判明した際は，医師は処方する薬剤の見直しや，長期処方を希望する受診者との処方量の調整を行う。
W病院は，複数の外来診療科をもっており，症状や病状に応じて処方箋を発行している。W病院には院内薬局があり，受診者の多くは院内薬局で薬剤の処方を受ける。
処方箋には期限があり，発行年月日から有効年月日までを，薬剤の処方を受けることのできる期間としている。
W病院では，受診者への医療サービス向上を目的に，薬剤管理システムの再構築を行うことになった。再構築するシステムには，医師の処方箋作成を支援する次のチェック機能を実装する。
- 処方箋発行の際，処方しようとしている薬剤と過去6か月以内にW病院で発行した処方箋に記載の薬剤との組合せに対し，薬剤併用チェックを行う。薬剤併用チェックでは，併用を禁止する"併用禁忌"となる薬剤の組合せ，及び併用に注意を要する"併用注意"となる薬剤の組合せに該当しないことを確認する。
- 院内薬局で処方することを前提に，処方箋発行時に院内薬局の薬剤に対する在庫チェックを行う。在庫チェックでは，発行した処方箋に記載の薬剤が，院内薬局で有効年月日まで確保されるよう，在庫の保証を行う。
開発に当たり設計した，薬剤管理システムのデータベースのE-R図を図1に示す。
なお，"在庫量_大人1日"，"処方量_大人1日"とは，"大人1日の分量"を単位とした在庫量，処方量を示す。
業務処理と操作対象のテーブルを表1に示す。また，テーブルの列に対する処理内容を表2に示す。ここで，表2中の括弧付き数字は，表1中の業務処理の括弧付き数字に対応している。
薬剤管理システムでは，薬剤併用情報テーブルに"併用禁忌"と"併用注意"となる薬剤の組合せを保持しており，この情報を使い，薬剤併用チェックを行う。
"併用禁忌"と"併用注意"に該当する薬剤の組合せ一覧を出力するSQLを図2に示す。ここで，":受診者ID"，":半年前年月日"，":処方箋ID"は，該当の値を格納する埋込み変数である。また，TO_DATE関数は，指定された文字型の年月日をDATE型に変換するユーザ定義関数である。
薬剤の組合せ一覧には，今回の外来受診では処方しない薬剤や院内薬局で処方を受けなかった薬剤の組合せも含まれており，出力内容を医師が確認し，必要に応じて処方する薬剤を見直す。見直しの結果，処方箋明細が0件になることもあるが，このような場合には，処方箋の発行は行わない運用とし，処方箋レコードは削除しない。
在庫チェックでは，"発行した処方箋に記載の薬剤が，院内薬局で有効年月日まで確保されるよう，在庫の保証を行う。"という要件の判定を簡素化するために，処方箋を発行し，有効年月日までに院内薬局で処方する可能性のある薬剤の処方量の合計を，確保量として管理するためのビューを作成することにした。
ビューを作成するSQLを図3に示す。ここで，CURRENT_DATE関数は，参照時の日付をDATE型で返す日時値関数である。
在庫チェックで在庫不足が判明した際は，医師は処方する薬剤の見直しや，長期処方を希望する受診者との処方量の調整を行う。
設問1
図1中のa，bに入れる適切なエンティティ間の関連を解答群の中から選び，記号で答えよ。
a，b に関する解答群
解答入力欄
- a：
- b：
解答例・解答の要点
- a：カ
- b：ウ
解説
〔aについて〕
受診者エンティティと外来受診エンティティには共通の属性"受診者ID"があります。この属性"受診者ID"は、受診者エンティティ側では主キーであり、受診者を一意に特定します。一方、外来受診エンティティ側では属性"受診予定日時"との組み合わせで複合主キーを構成していて、1人の受診者が異なる日時に受診することが考慮されています（つまり、外来受診エンティティには同一の受診者IDの値を持つ複数のレコードが存在し得るということです）。このとき、外来受診エンティティの"受診者ID"は、受診者エンティティの"受診者ID"を参照するので、主キーであると同時に外部キーでもあります。
原則として、ある主キーをもつエンティティと、その主キーを外部キーに持つエンティティのカーディナリティ（多重度）は、主キー側を"1"、外部キー側を"多"とした「1対多」になります。
さらに、表1「業務処理と操作対象のテーブル」の"受付"の行を見ると、初回の外来受診の際に受診者テーブルと外来受診テーブルの両方についてレコード作成をすることがわかります。よって、受診者エンティティに登録されているにもかかわらず、外来受診エンティティに当該受診者IDをもつレコードが存在しないということはあり得ません。言い換えれば、受診者エンティティに存在する受診者IDをもつ外来受診エンティティのレコードが少なくとも1つ以上は存在するということになります。
したがって、受診者エンティティと外来受診エンティティのカーディナリティは1対多（1以上）であり、aには「
」が当てはまります。
∴a＝カ：
〔bについて〕
薬剤在庫エンティティも薬剤エンティティも主キーとして共通の属性"薬剤コード"があります。どちらのエンティティでも薬剤コードでレコードを一意に特定できるので、1対1に対応させることができます。また、表1の"院内薬局"の行を見ると、薬剤の初回発注の際に薬剤テーブルと薬剤在庫テーブルの両方についてレコードを作成することがわかります。よって、薬剤エンティティに登録された薬剤コードが薬剤在庫エンティティに存在しないということはあり得ません。言い換えると、薬剤エンティティに登録された薬剤コードは、薬剤在庫エンティティにも必ず存在するということです。
したがって、薬剤在庫エンティティと薬剤エンティティのカーディナリティは1対1（どちら側も0を含まない）であり、bには「
」が当てはまります。
∴b＝ウ：
受診者エンティティと外来受診エンティティには共通の属性"受診者ID"があります。この属性"受診者ID"は、受診者エンティティ側では主キーであり、受診者を一意に特定します。一方、外来受診エンティティ側では属性"受診予定日時"との組み合わせで複合主キーを構成していて、1人の受診者が異なる日時に受診することが考慮されています（つまり、外来受診エンティティには同一の受診者IDの値を持つ複数のレコードが存在し得るということです）。このとき、外来受診エンティティの"受診者ID"は、受診者エンティティの"受診者ID"を参照するので、主キーであると同時に外部キーでもあります。
原則として、ある主キーをもつエンティティと、その主キーを外部キーに持つエンティティのカーディナリティ（多重度）は、主キー側を"1"、外部キー側を"多"とした「1対多」になります。
さらに、表1「業務処理と操作対象のテーブル」の"受付"の行を見ると、初回の外来受診の際に受診者テーブルと外来受診テーブルの両方についてレコード作成をすることがわかります。よって、受診者エンティティに登録されているにもかかわらず、外来受診エンティティに当該受診者IDをもつレコードが存在しないということはあり得ません。言い換えれば、受診者エンティティに存在する受診者IDをもつ外来受診エンティティのレコードが少なくとも1つ以上は存在するということになります。
したがって、受診者エンティティと外来受診エンティティのカーディナリティは1対多（1以上）であり、aには「
」が当てはまります。∴a＝カ：
〔bについて〕
薬剤在庫エンティティも薬剤エンティティも主キーとして共通の属性"薬剤コード"があります。どちらのエンティティでも薬剤コードでレコードを一意に特定できるので、1対1に対応させることができます。また、表1の"院内薬局"の行を見ると、薬剤の初回発注の際に薬剤テーブルと薬剤在庫テーブルの両方についてレコードを作成することがわかります。よって、薬剤エンティティに登録された薬剤コードが薬剤在庫エンティティに存在しないということはあり得ません。言い換えると、薬剤エンティティに登録された薬剤コードは、薬剤在庫エンティティにも必ず存在するということです。
したがって、薬剤在庫エンティティと薬剤エンティティのカーディナリティは1対1（どちら側も0を含まない）であり、bには「
」が当てはまります。∴b＝ウ：
設問2
表1と表2について，(1)～(3)に答えよ。
- 表1中のc，dに入れる適切なテーブル名を答えよ。
- 表2中のeに入れる適切な処理内容を，対応する表1中の括弧付き数字を含めて答えよ。
- 表2中のfに入れる適切な列名を答えよ。
解答入力欄
- c：
- d：
- e：
- f：
解答例・解答の要点
- c：処方箋明細
- d：薬剤在庫
- e：値を更新する。(3)
- f：処方箋ID
解説
- 〔cについて〕
医師が、(4)処方箋の登録（発行前）を行うと、処方箋テーブルと処方箋明細テーブルに処方しようとしている薬剤が登録されます。その後〔薬剤併用チェック処理〕により、"併用禁忌"または"併用注意"となる組合せが出力され、医師はその情報を参考にして処方する薬剤を見直します。見直しでは、処方する薬剤の種類を変えたり、追加したり、取り止めたりといったことが行われ、その際に処方箋明細テーブルから登録済の薬剤を削除したり、新たに薬剤を追加したりといった操作を行うことになります。
つまり、(5)薬剤チェックの段階では、処方箋明細テーブルに対してレコードの削除や作成が行われると考えられます。したがって、cには「処方箋明細」が当てはまります。
なお、削除の結果、処方箋明細が0件になる場合がありますが、その場合でも「処方箋レコードは削除しない」としているので、処方箋テーブルは操作対象外となります。
∴c＝処方箋明細
〔dについて〕
(11)薬剤の入庫の段階で処理が行われるテーブル・列を表2から探すと、薬剤在庫テーブルの"在庫量_大人1日"列に更新が行われることがわかります。常識的に考えて、薬剤が入庫したときには当該薬剤の在庫数量を増やさなければなりませんから、薬剤の入庫時に操作するテーブルは「薬剤在庫」が適切です。
∴d＝薬剤在庫 - 〔eについて〕
表1から受診者テーブルの列が更新される業務処理を探すと、(3)受診者情報変更時の修正が該当することがわかります。受診者の住所が変更になった際は、受診者テーブルの"住所"列の値を新たな住所で更新することになります。他の更新時処理の記述に倣えば、eには「値を更新する。(3)」が入ると判断できます。
∴e＝値を更新する。(3) - 〔fについて〕
業務処理の対象となっている外来受診テーブルの列を選択します。作成時に「NULLを設定する」という説明から、あらかじめ主キーである"受診者ID"列と"受診予定日時"列は除外できます（主キー属性にはNOT NULL制約が課されるため）。
表1から外来受診テーブルの列が作成・更新される業務処理を探すと、作成については(1)初回の外来受診、(2)2回目以降の外来受診が、更新については(4)処方箋の登録（発行前）が該当することがわかります。(1)(2)の外来受診を受け付けた段階ではNULLを設定する、そして受診後の(4)処方箋の登録（発効前）の段階で値を更新するという情報をもとに考えると、fは診察後の処方箋登録時に初めて必要となる「処方箋ID」列であると判断できます。外来受診の受付時には当然ながら処方箋が交付されていないため、"処方箋ID"列の値を暫定的にNULLにしておく必要があるからです。
∴f＝処方箋ID
設問3
図2中のg，hに入れる適切な字句を答えよ。
解答入力欄
- g：
- h：
解答例・解答の要点
- g：UNION 又は UNION ALL
- h：EXISTS
解説
まず、解説のためにSQLの各部分に番号を付けておきます。
〔gについて〕
図2のSQLは2つのSQL文で構成されており、WITH句により副問合せを定義する部分、WITH句で定義された副問合せを使用して実際に結果表を導出する部分に分かれています。
※WITH句は、これは副問合せに名前を付け、それを他のSQL文内で使用できるようにするものです。「WITH 副問合せ名 AS (クエリ文)：副問合せ名」を記述することにより、ASの直後のクエリ文を何度も記述することなく使い回すことができます。
〔薬剤併用チェック処理〕では、②で得られた薬剤の一覧と、③で得られた薬剤コードの一覧に含まれるすべての薬剤の組合せについて〔薬剤チェック処理〕を行う必要があります。gには、2つの結果セットを一つにまとめる（②の一覧に③の一覧を加える）演算が入るため、関係同士の和演算を求める「UNION」が適切です。
※①のSELECT文にはDISTINCTが記されておらず、薬剤コードの重複は特に考慮していないと考えられるため、「UNION」ではなく「UNION ALL（要素の重複を許す和演算）」でも正解です。
∴g＝UNION
〔hについて〕
T2を定義する副問合せの部分では、2つの"チェック対象薬剤"同士を直積結合し、1つめの薬剤コード（薬剤コード1）と2つ目の薬剤コード（薬剤コード2）を抜き出します。これにより過去6カ月以内に発行した薬剤と今回処方しようとする薬剤のすべての組合せが網羅されます。
"併用禁忌"と"併用注意"に該当する薬剤の組み合わせは、T2のレコード（薬剤コード1と薬剤コード2の組み合わせ）に、薬剤併用情報テーブル（T1）の各レコード（薬剤コード1と薬剤コード2の組み合わせ）が存在するかどうかを判定することにより得ることができます。そのためには、EXISTS句による相関副問合せを用い、T2のレコードの中に、薬剤併用情報テーブルのレコードと一致するものがあるかを1行ずつ確認していきます（④の部分）。「T1.薬剤コード1 = T2.薬剤コード1」かつ「T1.薬剤コード2 = T2.薬剤コード2」となるレコードが存在すれば、WHERE句は真を返し、薬剤併用情報テーブルの現在行が出力されることとなります。したがって、hには「EXISTS」が当てはまります。
∴h＝EXISTS
図2のSQLは2つのSQL文で構成されており、WITH句により副問合せを定義する部分、WITH句で定義された副問合せを使用して実際に結果表を導出する部分に分かれています。
※WITH句は、これは副問合せに名前を付け、それを他のSQL文内で使用できるようにするものです。「WITH 副問合せ名 AS (クエリ文)：副問合せ名」を記述することにより、ASの直後のクエリ文を何度も記述することなく使い回すことができます。
- ①"B2"を定義する副問合せの部分
- 外来受診テーブルと処方箋テーブルを処方箋IDで結合し、外来受診テーブルの受診者IDが対象受診者のもので、処方箋テーブルの発行年月日が半年前の年月日以降であるレコードの処方箋IDを抜き出します。
- ②の部分
- 処方箋明細テーブルとB2を処方箋IDで結合し、処方箋明細テーブルの薬剤コードを抜き出します。
- ③の部分
- 処方箋明細テーブルの処方箋IDが該当のものであるレコードの薬剤コードを抜き出します。
〔薬剤併用チェック処理〕では、②で得られた薬剤の一覧と、③で得られた薬剤コードの一覧に含まれるすべての薬剤の組合せについて〔薬剤チェック処理〕を行う必要があります。gには、2つの結果セットを一つにまとめる（②の一覧に③の一覧を加える）演算が入るため、関係同士の和演算を求める「UNION」が適切です。
※①のSELECT文にはDISTINCTが記されておらず、薬剤コードの重複は特に考慮していないと考えられるため、「UNION」ではなく「UNION ALL（要素の重複を許す和演算）」でも正解です。
∴g＝UNION
〔hについて〕
T2を定義する副問合せの部分では、2つの"チェック対象薬剤"同士を直積結合し、1つめの薬剤コード（薬剤コード1）と2つ目の薬剤コード（薬剤コード2）を抜き出します。これにより過去6カ月以内に発行した薬剤と今回処方しようとする薬剤のすべての組合せが網羅されます。
"併用禁忌"と"併用注意"に該当する薬剤の組み合わせは、T2のレコード（薬剤コード1と薬剤コード2の組み合わせ）に、薬剤併用情報テーブル（T1）の各レコード（薬剤コード1と薬剤コード2の組み合わせ）が存在するかどうかを判定することにより得ることができます。そのためには、EXISTS句による相関副問合せを用い、T2のレコードの中に、薬剤併用情報テーブルのレコードと一致するものがあるかを1行ずつ確認していきます（④の部分）。「T1.薬剤コード1 = T2.薬剤コード1」かつ「T1.薬剤コード2 = T2.薬剤コード2」となるレコードが存在すれば、WHERE句は真を返し、薬剤併用情報テーブルの現在行が出力されることとなります。したがって、hには「EXISTS」が当てはまります。
∴h＝EXISTS
設問4
図3中のi～kに入れる適切な字句を答えよ。
解答入力欄
- i：
- j：
- k：
解答例・解答の要点
- i：SUM(T3.処方量_大人1日)
- j：>= CURRENT_DATE
- k：IS NULL
解説
〔iについて〕
iは、作成されるビューの2列目"確保量_大人1日"列となります。
GROUP BY句によりレコードのグループ化が行われているので、SELECT文にはGROUP BY句で指定した列名と集約関数以外は記述できません。SELECT文の対象となっているT3には"薬剤コード"列と"処方量_大人1日"列しか存在しないので、これだけでも答えを絞り込むことができるはずです。
"確保量_大人1日"は、有効年月日までに院内薬局で処方する可能性のある薬剤の処方量の合計ですから、T3の"処方量_大人1日"を薬剤コードごとに合計すれば、"確保量_大人1日"を得られます。したがって、iには「SUM(T3.処方量_大人1日)」が当てはまります。
※本設問にはテーブル名を付けてという指定がないのでT3.は省略可能です。
∴i＝SUM(T3.処方量_大人1日)
〔j、kについて〕
T3を定義する副問合せの部分は、処方箋テーブルと処方箋明細テーブルを処方箋IDで結合し、処方箋テーブルの発行年月日が参照時の日付以前で、有効年月日がj、薬剤処方年月日がkであるレコードの"薬剤コード"と"処方量_大人1日"を抜き出します。
〔在庫チェック処理〕には「在庫チェックでは，"発行した処方箋に記載の薬剤が，院内薬局で有効年月日まで確保されるよう，在庫の保証を行う。"」とあります。在庫確保の対象となるのは、処方箋が発行されているにもかかわらず、まだ薬剤の処方（受渡し）を受けていないレコードとなります。つまり、次の3つの条件を満たすレコードです。
kには上記の3つ目の条件で集計対象を絞るための条件式が入ります。表1・2を見ると、薬剤が処方されたときは、処方箋テーブルの"薬剤処方年月日"列をNULLから（非NULLの日付に）更新することがわかります。つまり、処方箋テーブルの"薬剤処方年月日"列の値がNULLのレコードは、まだ薬剤が処方されていない状態ということです。
在庫保証の対象となるのは「まだ薬剤が処方されていない」レコードですから、"薬剤処方年月日"列の値がNULLのレコードを選択すれば、該当するレコードを絞り込めます。よって、条件式には「薬剤処方年月日 IS NULL」を指定するのが適切です。
∴j＝ >= CURRENT_DATE
k＝IS NULL
iは、作成されるビューの2列目"確保量_大人1日"列となります。
GROUP BY句によりレコードのグループ化が行われているので、SELECT文にはGROUP BY句で指定した列名と集約関数以外は記述できません。SELECT文の対象となっているT3には"薬剤コード"列と"処方量_大人1日"列しか存在しないので、これだけでも答えを絞り込むことができるはずです。
"確保量_大人1日"は、有効年月日までに院内薬局で処方する可能性のある薬剤の処方量の合計ですから、T3の"処方量_大人1日"を薬剤コードごとに合計すれば、"確保量_大人1日"を得られます。したがって、iには「SUM(T3.処方量_大人1日)」が当てはまります。
※本設問にはテーブル名を付けてという指定がないのでT3.は省略可能です。
∴i＝SUM(T3.処方量_大人1日)
〔j、kについて〕
T3を定義する副問合せの部分は、処方箋テーブルと処方箋明細テーブルを処方箋IDで結合し、処方箋テーブルの発行年月日が参照時の日付以前で、有効年月日がj、薬剤処方年月日がkであるレコードの"薬剤コード"と"処方量_大人1日"を抜き出します。
〔在庫チェック処理〕には「在庫チェックでは，"発行した処方箋に記載の薬剤が，院内薬局で有効年月日まで確保されるよう，在庫の保証を行う。"」とあります。在庫確保の対象となるのは、処方箋が発行されているにもかかわらず、まだ薬剤の処方（受渡し）を受けていないレコードとなります。つまり、次の3つの条件を満たすレコードです。
- 参照日に発行済の処方箋であること（登録だけされているものを除く）
- 参照日に有効年月日を過ぎていないこと
- まだ薬剤が処方されていないこと
kには上記の3つ目の条件で集計対象を絞るための条件式が入ります。表1・2を見ると、薬剤が処方されたときは、処方箋テーブルの"薬剤処方年月日"列をNULLから（非NULLの日付に）更新することがわかります。つまり、処方箋テーブルの"薬剤処方年月日"列の値がNULLのレコードは、まだ薬剤が処方されていない状態ということです。
在庫保証の対象となるのは「まだ薬剤が処方されていない」レコードですから、"薬剤処方年月日"列の値がNULLのレコードを選択すれば、該当するレコードを絞り込めます。よって、条件式には「薬剤処方年月日 IS NULL」を指定するのが適切です。
∴j＝ >= CURRENT_DATE
k＝IS NULL
