| データベーススペシャリスト 平成９年度 午後�機〔簑� |
|
|
 |
SQLに関する次の記述を読んで, 設問1〜3に答えよ。
ソフトウェア会社のＳ社では，社員の勤務状況を手書き記入方式の勤務実績表(図1)を使って労務管理をしている。
業務作業の軽減のためと，将来，給与システム及び原価管理システムへの情報提供を可能にするために，勤務実績表をデータベース化して管理することになった。人事部のＫ部長は，データベースの設計に携わった経験のあるＦ君に対して，データベースの構築を命じた。
勤務実績表の表記ルールは，次のとおりである。
・起票は，各社員自らが行い，一週間単位で部門長へ提出する。
・作業時間は，30分単位で記入する。記入単位は，時間で行う。30分=0.5時間とする。
・基準内作業時間は1日8時間である。時間外作業が発生した場合は，時間外作業時間欄へ記入する。
・作業内容は作業コードを用いて記入し，併せて作業時間も記入する。
・作業コードには，休暇などの非就業を表すコードも含まれている。
非就業時間が発生したら，該当時間を基準内作業時間欄に記入する。
Ｆ君は，勤務実績表を分析して，図2のような正規化されたテーブルを設計し，Ｓ社が標準的に使用している関係データベース管理システム(RDBMS)に定義した。
┌────────────────────────────────┐
│ 勤務実績表 │
│ │
│部門コード [10] 部門名 [開発5部] 社員�� [300] 氏名 [鈴木一郎] │
│ │
│ 年月日 作業コード 基準内作業時間 時間外作業時間 │
│[97 04 01] [001] [5.0] [3.0] │
│[ ] [009] [3.0] [0.0] │
│[97 04 02] [001] [8.0] [1.0] │
│ : │
│ : │
└────────────────────────────────┘
図1 勤務実績表
┌─────────────────────────────────────┐
│部門 │
│┌─────┬────┐ │
││部門コード│部門名称│ │
││─────│ │ │
│┝━━━━━┿━━━━┥ │
││ 01 │経理部 │ │
│├─────┼────┤ │
││ 02 │人事部 │ │
│├─────┼────┤ │
││ ： │ ： │ │
│├─────┼────┤ ┌───┐ │
││ 10 │開発5課 │ │部 門│ │
│└─────┴────┘ └─┬─┘ │
│ ┌───────┘ │
│社員 ↓ │
│┌───┬────┬───────┐ │
││社員�皋◆〇疚勝；⊇蠡杏�門コード│ │
││───│ │…………………│ │
│┝━━━┿━━━━┿━━━━━━━┥ │
││ 001 │相川花子│ 01 │ │
│├───┼────┼───────┤ │
││ 002 │市川太郎│ 02 │ │
│├───┼────┼───────┤ │
││ ： │ ： │ ： │ │
│├───┼────┼───────┤ │
││ 300 │鈴木一郎│ 10 │ │
│└───┴────┴───────┘ │
│ │
│就業区分 │
│┌────┬──────┐ │
││就業区分│就業区分名称│ │
││────│ │ │
│┝━━━━┿━━━━━━┥ │
││ 0 │就業 │ │
│├────┼──────┤ ┌────┐ │
││ 1 │非就業 │ │就業区分│ │
│└────┴──────┘ └─┬──┘ │
│ ┌───────────┘ │
│部門 ↓ │
│┌─────┬────┬────┐ │
││作業コード│就業区分│作業名称│ │
││─────│…………│ │ │
│┝━━━━━┿━━━━┿━━━━┥ │
││ 001 │ 0 │担当業務│ │
│├─────┼────┼────┤ │
││ 002 │ 0 │研修 │ │
│├─────┼────┼────┤ │
││ ： │ ： │ ： │ │
│├─────┼────┼────┤ ┌───┐┌────┐ │
││ 009 │ 1 │休暇 │ │社 員││作業内容│ │
│└─────┴────┴────┘ └─┬─┘└─┬──┘ │
│ ┌────────────────────┘ │ │
│ │ ┌──────────────┘ │
│勤務実績 ↓ ↓ │
│┌─────┬─┬─┬─┬───────┬───────┬───────┐│
││勤務社員�皋�年│月│日│勤務作業コード│作業内勤務時間│時間外勤務時間││
││─────│─│─│─│───────│ │ ││
│┝━━━━━┿━┿━┿━┿━━━━━━━┿━━━━━━━┿━━━━━━━┥│
││ 001 │97│04│01│ 001 │ 0.5 │ 3.0 ││
│├─────┼─┼─┼─┼───────┼───────┼───────┤│
││ 001 │97│04│01│ 002 │ 3.0 │ 0.0 ││
│├─────┼─┼─┼─┼───────┼───────┼───────┤│
││ ： │ │ │ │ ： │ ： │ ： ││
│├─────┼─┼─┼─┼───────┼───────┼───────┤│
││ 300 │97│04│30│ 009 │ 0.0 │ 0.0 ││
│└─────┴─┴─┴─┴───────┴───────┴───────┘│
└─────────────────────────────────────┘
図2 Ｆ君が定義したテーブルの構造
次にＫ部長は，Ｆ君に対して，労務管理の基本となる月間労務時間を管理するためのユーザビューの作成と，各部門における時間外作業時間の状況を把握するための管理帳票の作成を命じた。
(1) ユーザビュー
月間，社員別に基準内作業時間及び時間外作業時間を合計する。ただし，非就業時間分については合計しない。
月別作業時間
┌─┬─┬───────┬─────┬───────┬───────┐
│年│月│所属部門コード│勤務社員�皋∈邏汎盒侈鎧�間│時間外勤務時間│
┝━┿━┿━━━━━━━┿━━━━━┿━━━━━━━┿━━━━━━━┥
│97│01│ 01 │ 001 │ 160.0 │ 100.0 │
├─┼─┼───────┼─────┼───────┼───────┤
│97│01│ 01 │ 001 │ 155.0 │ 70.5 │
├─┼─┼───────┼─────┼───────┼───────┤
│ │ │ ： │ ： │ ： │ ： │
├─┼─┼───────┼─────┼───────┼───────┤
│97│12│ 10 │ 300 │ 130.0 │ 0.0 │
└─┴─┴───────┴─────┴───────┴───────┘
図3 Ｆ君が定義した月別作業時間ビューの構造
(2) 管理帳票
毎月，一人当たりの全社平均時間外作業時間を超えている社員数と最高の時間外作業時間を，部門別に集計する。だたし，集計結果が5人以下の部門は表示されない。表示順は部門コード(昇順)である。
┌────────────────────────────────┐
│ 時間外作業状況管理表 │
│ │
│ 部門コード 部門名称 社員数 最高時間外作業時間 │
│ 02 人事部 10 90.0 │
│ 10 開発5課 6 100.0 │
│ ： │
└────────────────────────────────┘
図4 時間外作業状況管理表のイメージ
設問1
図3の月別作業時間ビューを定義するための，次のSQL文を完成させよ。
【 (a) 】 月別作業時間
（年，月，所属部門コード，勤務社員�癲ご霆狷盧邏隼�間計，時間外作業時間計）
【 (b) 】
年，月，所属部門コード，勤務社員�癲�SUM(基準内作業時間)，SUM(時間外作業時間)
FROM 【 (c) 】
WHERE 【 (d) 】
GRUPE BY 【 (e) 】
設問2
図4の時間外作業状況管理表（97年4月分）を作成するための，次のSQL文を完成させよ。
SELECT 所属部門コード，部門名称，【 (a) 】
FROM 月別作業時間，部門
WHERE 年 = '97' AND
月 = '04' AND
所属部門コード = 部門コード AND
時間外作業時間 > 【 (b) 】
GROUP BY 所属部門コード，部門名
【 (c) 】
ORDER BY 所属部門コード
設問3
テーブルの利用権限に関する次の問いに答えよ。
（１）図2の社員テーブルに対するすべての捜査権限を，人事部が使用している利用者名"USER人事"に与えるためのSQL文を記述せよ。
（２）（１）で作成したSQL文の社員テーブルを，月間作業時間ビューに置き換えて実行したところエラーになってしまった。エラーの原因となった月別作業時間ビューの構造上の特徴を二つ挙げ，それぞれ30字以内でのべよ。
|
| 解答 |
|
|
|
|
販売業務システムに関する次の記述を読んで, 設問1〜3に答えよ。
眼鏡専門店である、Ｑ商店は、フレームを独自生産している。同じフレームは、つくらない基本方針の下、オリジナルフレームを特徴として売上を伸ばしてきた。このため固定客が多く、またフレームの売上が業績に直接つながっている。売上の拡大を狙い顧客管理と販売管理の強化のため、販売業務システムを再構築することにした。
Ｑ商店のシステムの対象とする商品には、フレームとレンズがある。フレームは世代別、職業別など各層をターゲットとして形状、材質、色などを変えて商品化している。商品には、1本1本の製造番号が商品番号として付番される。レンズは、メーカに発注しており、Ｑ商店では、フレームにあわたカットだけを行っている。レンズには、材質、色などを区別するためにメーカが管理する型番がある。メーカへは、型番と度数及び乱視度を指定して発注する。Ｑ商店では、メーカの型番に対応した商品番号を付番する。フレームの商品番号とレンズの商品番号とは、けたが同じで、重複はない番号体系となっている。
フレームとレンズは、ほとんどが同時に販売されるが、破損や視力の変化などによって、フレームやレンズだけがそれぞれ単独に販売されることがある。ただし、他店のフレームにレンズだけを販売することはしていない。
顧客管理と販売管理の主な内容は、次のとおりである。
〔顧客管理〕
初めてめがねを購入する顧客に対しては、一意な顧客番号を割り当て、氏名や電話番号などの登録、視力や乱視度などの検査、カードの発行を行う。登録済みの顧客の来店時には、顧客番号などから顧客情報を照会し、前回の視力や購入履歴などを参照する。顧客情報は常に最新の者を管理する。
〔販売管理〕
登録済みの顧客であっても、眼鏡販売時は視力、乱視度を測定し、使用目的に合わせてレンズの度数などを決める。販売伝票は、図1に示すとおりであり、販売番号、販売日、顧客名、顧客番号、商品名、商品番号、金額が記入される。販売番号は、販売するフレームやレンズの個数によらず1回の販売のたびに一意に付番される。
┌──────────────────────────────┐
│販売番号：1234567 顧客名： 山本 和夫│
│販売日： 平成9年2月24日 顧客番号：912345678 │
│ │
│ ┌───────┬──────┬─────┐ │
│ │ 商品名 │ 商品番号 │ 金額 │ │
│ ├───────┼──────┼─────┤ │
│ │ＡＣＢフレーム│ ABC123456 │ 50,000 │ │
│ ├───────┼──────┼─────┤ │
│ │ＤＥＦレンズ │ DEF456789 │ 20,000 │ │
│ ├───────┼──────┼─────┤ │
│ │ＤＥＦレンズ │ DEF456789 │ 20,000 │ │
│ ├───────┴──────┼─────┤ │
│ │ 合 計 │ 90,000 │ │
│ └──────────────┴─────┘ │
└──────────────────────────────┘
注 販売番号は商品個数によらず一つ採られる
図1 販売伝票
現行のシステムでは、顧客、商品、販売の管理だけを実施しており、データベースの関係スキーマは図2のとおりである。現行のシステムでは、次のような問題点があり、顧客サービス向上のために新システムでの改造が必要であった。
【1】 購入した眼鏡のフレームとレンズの対応がとれなくなることがある。
【2】 眼鏡別の補修履歴がつかみにくい。
販売業務システム再構築にあたり、Ｑ商店では、眼鏡に対する補修履歴を、使用期間にわたって把握するためのライフサイクル管理が必要になると考え、コンサルタントのＲ氏にシステムの設計を依頼した。Ｒ氏は、Ｑ商店の依頼内容に基づいて次のようなデータベースの設計方針を立てた。
【1】 Ｑ商店は"フレーム"を中心とした経営をしており、"眼鏡"は、"フレーム"に"レンズ"を組み合わせたものとする。
【2】 "顧客"に販売した商品は、"眼鏡"の単位で管理する。
【3】 "販売"は、"眼鏡"に対するさまざまな補修の履歴と関連づけて管理する。
【4】 補修に関するシステム化の範囲としては、レンズの交換など売上が発生するものに限定する。
Ｒ氏の作成した関係スキーマを図3に示す。図3中の"眼鏡"の販売補修連番は、陳列時を初期値として販売や補修を行う度に1ずつ加算される連番である。販売補修区分は、販売や補修の内容を示す。例えば、01がフレームとレンズの販売、02がフレームだけの販売。03がレンズだけの販売などである。
┌────────────────────────────────────────────────┐
│ 顧客 （顧客番号，顧客登録日，生年月日，性別，検査日，右視力，右乱視度，左視力，左乱視度， │
│ ￣￣￣￣ │
│ 電話番号，顧客名，住所） │
│ │
│ フレーム（フレーム商品番号，商品名，形状，材質，色，陳列開始日，設定価格） │
│ ￣￣￣￣￣￣￣￣ │
│ レンズ （レンズ商品番号，型番，商品名，材質，単位重量，色，設定価格） │
│ ￣￣￣￣￣￣￣ │
│ 販売 （販売番号，販売日，販売金額，顧客番号） │
│ ￣￣￣￣ ‥‥‥ │
│ 販売明細（販売番号、行番号※(1)，商品番号※(2)，販売価格，左右区分，度数，乱視度※(3)） │
│ ￣￣￣￣￣￣￣￣ │
│ 注意(1) "販売明細"の行番号は販売伝票の行の番号 │
│ 注意(2) "販売明細"の商品番号はフレーム商品番号またはレンズ商品番号が入る。 │
│ 注意(3) "販売明細"の左右区分、度数、乱視度は、"レンズ"だけに適用される。 │
└────────────────────────────────────────────────┘
図2 現行の関係スキーマ
┌────────────────────────────────────────────────┐
│ 顧客 （顧客番号，顧客登録日，生年月日，性別，検査日，右視力，右乱視度，左視力，左乱視度， │
│ ￣￣￣￣ │
│ 電話番号，顧客名，住所） │
│ フレーム（フレーム商品番号，商品名，形状，材質，色，陳列開始日，設定価格，顧客番号） │
│ ￣￣￣￣￣￣￣￣ ‥‥‥ │
│ レンズ （レンズ商品番号，型番，商品名，材質，単位重量，色，設定価格） │
│ ￣￣￣￣￣￣￣ │
│ 眼鏡 （フレーム商品番号，*販売補修連番，販売補修区分，フレーム販売価格， │
│ ￣￣￣￣￣￣￣￣ │
│ 右レンズ商品番号※(1)，右レンズ度数，右レンズ乱視度，右レンズ販売価格， │
│ ‥‥‥‥‥‥‥‥ │
│ 左レンズ商品番号※(2)，左レンズ度数，左レンズ乱視度，左レンズ販売価格， │
│ ‥‥‥‥‥‥‥‥ │
│ 販売番号，販売日） │
│ ‥‥‥‥ │
│ 販売 （販売番号、販売日、販売金額） │
│ ￣￣￣￣ │
│ 注意(1) "眼鏡"の右レンズ商品番号、左レンズ商品番号は"レンズ"のレンズ商品番号と対応する。 │
└────────────────────────────────────────────────┘
図3 Ｒ氏の作成した関係スキーマ
設問1
販売された眼鏡の補修管理や現在の状態管理をする上で、図2の関係スキーマでは、顧客が購入した眼鏡の"フレーム"と"レンズ"の対応が取れなくなる場合がある。
（１）どのような場合が考えられるか。一つ挙げ、30字以内で述べよ。
（２）顧客が購入した眼鏡の"フレーム"と"レンズ"との対応がとれなくなるのは、関係スキーマのどのような原因によるものか、30字以内で述べよ。
設問2
図3の関係スキーマは、図2の関係スキーマに比べ、次の(1)、(2)に示す点が異なっている。図3のように変更したほうが適切である理由を、それぞれ40字以内で述べよ。
（１）図2中の"販売"の顧客番号を図3では、"フレーム"の属性としてもたせた点
（２）図2中の"販売"の販売日を図3では、"眼鏡"にも属性としてもたせた点
設問3
新システムの稼動後、Ｑ商店ではレンズをそのまま使用し、フレームだけ交換した顧客については、眼鏡の補修履歴が途切れてしまう事実が発見された。フレームに関しては、ライフサイクル管理できるが、レンズに関しては、ライフサイクル管理できない理由を30字以内で述べよ。
|
| 解答 |
|
|
|