| データベーススペシャリスト 平成７年度 午後�機〔簑� |
|
|
 |
エンドユーザコンピュ−ティングのためのデータベース設計に関する次の記述を読んで,
設問1〜3に答えよ。
Ｘ社の人事部の資格管理システムでは、社員の資格保有状況などを管理し、多くのレポートを出力してきた。利用者である教育担当者は資格の組合わせによる調査や資格を持っていない人の調査という業務を行っているが、バッチシステムでの定例出力では実際の業務の要求に応じられなくなってきた。
今回、新たに人事及び資格に関するデータベースを利用者に開放し、利用者が自由にオンラインで検索できる汎用検索を提供することとなった。
新システムでは、あらかじめよく使用されるテーブル(表)の結合をビューとして提供している、利用者はビューも単独のテーブルと区別できない仕組みになっている。このシステムでは、利用者自身も簡単にテーブルやビューの結合を行うことができ、さらに、日付計算などの関数を利用したデータの加工を容易にできる仕組みも提供している。
教育担当者が主に管理している資格は5種類であり、この5種類の資格管理を中心に、資格のみ保有者の検索や各種統計の作成を行っている。業務の拡大によって資格の種類も合計で50種類にまで増加している。新システムでは、主要な5種類の資格の検索に便利な社員主要資格テーブルを提供しているが、いずれは5種類の主要資格の見直しも必要になると考えられる。
このデータベースのテーブル構成及びその特性は、図1と表1のとおりである。
社員基本テーブル
┌────┬──┬─────┬───────┬────┬──┐
│社員番号│氏名│入社年月日│所属部署コード│生年月日│職級│
│────│ │ │ │ │ │
└────┴──┴─────┴───────┴────┴──┘
社員資格テーブル
┌────┬─────┬───────┐
│社員番号│資格コード│資格取得年月日│
│────│─────│ │
└────┴─────┴───────┘
社員主要資格テーブル
┌────┬───┬───┬───┬───┬───┐
│社員番号│資格Ａ│資格Ｂ│資格Ｃ│資格Ｄ│資格Ｅ│
│────│ │ │ │ │ │
└────┴───┴───┴───┴───┴───┘
部署テーブル
┌─────┬────┐
│部署コード│直間区分│
│─────│ │
└─────┴────┘
資格テーブル
┌─────┬────┐
│資格コード│資格名称│
│─────│ │
└─────┴────┘
※注図中の下線部分は、各テーブルの主キーを表す。
図1 テーブル構成
表1 各テーブルの特性
┌──────────┬───┬───────────────────────────┐
│ テーブル名 │件数 │ 特 性 │
├──────────┼───┼───────────────────────────┤
│社員基本テーブル │ 5,000│資格管理対象の社員は、5,000人。
│
├──────────┼───┼───────────────────────────┤
│社員資格テーブル │20,000│資格を持っていない社員のデータは、このテーブルには登録│
│ │ │されない。一人平均4件の資格を持っている
│
├──────────┼───┼───────────────────────────┤
│社員主要資格テーブル│ 5,000│主要資格の保有の有無を0又は1で表現する。 │
├──────────┼───┼───────────────────────────┤
│部署テーブル │ 100│課が間接部門か直接部門化を識別 │
├──────────┼───┼───────────────────────────┤
│資格テーブル │ 50│資格コードの名称を管理。 │
└──────────┴───┴───────────────────────────┘
このシステムで事前に設定しているビューの構成及びその特性は、図2と表2のとおりである。テーブルを結ぶ線は、テーブル間の結合を示す。
Ｖ１：
┌────┬──┬─────┬───────┬────┬──┐
│社員番号│氏名│入社年月日│所属部署コード│生年月日│職級│
│────│ │ │ │ │ │
└─┬──┴──┴─────┴───────┴────┴──┘
│
┌─┴──┬───┬───┬───┬───┬───┐
│社員番号│資格Ａ│資格Ｂ│資格Ｃ│資格Ｄ│資格Ｅ│
│────│ │ │ │ │ │
└────┴───┴───┴───┴───┴───┘
Ｖ２：
┌────┬──┬─────┬───────┬────┬──┐
│社員番号│氏名│入社年月日│所属部署コード│生年月日│職級│
│────│ │ │ │ │ │
└─┬──┴──┴─────┴───────┴────┴──┘
│
┌─┴──┬─────┬───────┐
│社員番号│資格コード│資格取得年月日│
│────│─────│ │
└────┴─────┴───────┘
Ｖ３：
┌────┬──┬─────┬───────┬────┬──┐
│社員番号│氏名│入社年月日│所属部署コード│生年月日│職級│
│────│ │ │───────│ │ │
└─┬──┴──┴─────┴────┬──┴────┴──┘
│ └───┐
┌─┴──┬─────┬───────┐ ┌─┴───┬────┐
│社員番号│資格コード│資格取得年月日│ │部署コード│直間区分│
│────│─────│ │ │─────│ │
└────┴──┬──┴───────┘ └─────┴────┘
┌──┴──┬────┐
│資格コード│資格名称│
│─────│ │
└─────┴────┘
図2 ビューの構成
表2 各ビューの特性
| ビュー名 |
|
特性 |
|
|
|
| Ｖ１ |
|
多くの資格のうち、通常検索の対象となるものは主要資格5種類であることから、このビュ
ーを提供している。
|
| Ｖ２ |
|
主要視覚以外の資格を検索するときもあるので、このビューを提供している。
|
| Ｖ３ |
|
主要資格以外は、資格コードだけでは内容がわかりにくいので、資格名称テーブルを結合し
たものを提供している。
その部課が直接部課か間接部課かを判定するためのテーブルも結合している。年齢も計算して付加している。
このビューは、すべての情報が結合されており、便利だが、応答時間が遅くなることを利用者に説明してある。 |
設問1
Ｖ２のビューで特定の部課を指定して所属社員の一覧と資格コードを表示する場合に、表示
されない社員が発生するケースがある。この現象を40字以内で述べよ。
設問2
社員主要資格テーブルは，種類の主要資格の有無を社員の一覧形式で出力することが一度の
検索で可能であり、便利である。しかし、社員主要テーブルを設計したことによる不利な点もある。それを２つあげ、それぞれ40字
以内で述べよ。
設問3
全社員の中から直接部門だけを対象に年齢別の主要資格の保有一覧を出力するために、Ｖ１とＶ３を結合した検索を行ったが応答が非常に遅いとの苦情が発生した。各テーブルのは適切なイン
デックスが定義されており、検索時にもこれらが適切に使用さている場合、この現象が起きる原因を
60字以内で述べよ。
|
| 解答 |
|
|
|
|
受注管理システムに関する次の記述を読んで, 設問1〜3に答えよ。
Ｇ社では，関東地方を中心とした大手食品卸業者である。Ｇ社では，ホストコンピュータ上で関係
データベース管理システムを利用した収集方式の受注管理システムを稼動させている。
このたび，Ｇ社の情報システム部の SE であるＨ君は，ユーザ部門からの依頼に基づいて，"受注
集計表出力のアプリケーション開発"，及び商品値上げに伴う"商品テーブルの更新"を実施すること
になった。
受注管理システムのデータベース構造，及びＨ君の実施する作業の内容は次のとおりである。
〔受注管理システムのデータベース構造〕
受注管理システムのデータベースは次の四つのテーブル(表)から構成されている。下線のついた項
目の組は主キーを表す。
(1)受注テーブル
┌────┬─────┬───────┬───────┐
│受注番号│受注年月日│受注支社コード│受注顧客コード│
│￣￣￣￣│ │ │ │
└────┴─────┴───────┴───────┘
"受注支社コード"は，支社テーブルに対する外部キーである。
(2)受注明細テーブル
┌────┬──────┬───────┬────┐
│受注番号│受注明細番号│受注商品コード│受注数量│
│￣￣￣￣│￣￣￣￣￣￣│ │ │
└────┴──────┴───────┴────┘
"受注番号は，受注テーブルに対する外部キーである。
"受注商品コード"は，商品テーブルに対する外部キーである。
(3)商品テーブル
┌─────┬────┬────┬────┐
│商品コード│商品名称│商品単価│商品区分│
│￣￣￣￣￣│ │ │ │
└─────┴────┴────┴────┘
(4)支社テーブル
┌─────┬────┐
│支社コード│支社名称│
│￣￣￣￣￣│ │
└─────┴────┘
〔受注集計表のアプリケーション開発〕
受注管理システムの四つのテーブルを参照して，"支社別商品別日別の受注集計表"を出力するためのアプリケーションを開発する。帳票はバッチ処理での出力とし，COBOL言語によるSQL文を組み込んでテーブルにアクセスする。
"支社別商品別日別の受注集計表"の外部スキーマは，次のとおりである。
┌─────┬─────┬────┬────┬────┬────┬────┐
│支社コード│商品コード│受注数量│受注金額│受注件数│支社名称│商品名称│
└─────┴─────┴────┴────┴────┴────┴────┘
受注金額は，受注数量に商品テーブルから参照した商品単価を掛けることによって算出する。受注件数は，支社・商品・日別の受注件数である。行の並び順は，受注年月日，支社，商品の順である。
〔商品テーブルの更新〕
卸売り手数料の料率の更新によって，一部商品の単価がいっせいに2％値上げされることになった。値上げの対象となる商品は，商品テーブルの項目，"商品区分"
の値が "1" の商品である。その数は多く，マスタメンテナンス画面から1商品ずつ単価を変更するのは作業が膨大となるため，DBMSが提供する対話形式の問合せ画面から，SQLのUPDATE文を用いて一括更新を行うこととした。
設問1
SQL文中の(1)〜(5)の空欄を埋めて，"支社別商品別日別の受注集計表"の外部スキーマのカーソルである。"支社別商品別日別受注カーソル"の定義を完成させよ。
【 (1) 】
FOR
SELECT
A.受注支社コード，
【 (2) 】
FORM
受注 A
受注明細 B
支社 C
商品 D
WHERE
【 (3) 】
GROUP BY
A.受注支社コード，
【 (4) 】
ORDER BY
【 (5) 】
設問2
"支社別商品別日別受注カーソル"の検索条件に，"受注金額"の値が10,000以上という条件を付加する。条件を付加すべきSQL文の位置を(ア)〜(オ)の中から選べ。さらに，付加すべきSQL文を記述せよ。
…
SELECT ...
(ア)
FROM ....
(イ)
WHERE ...
(ウ)
GROUP BY ...
(エ)
ORDER BY ...
(オ)
設問3
値上げ対象商品の単価を一括して2％値上げするための商品テーブルに対するSQL文を記述せよ。なお，"商品区分"のデータ型は，文字列である。
|
| 解答 |
|
|
|