| データベーススペシャリスト 平成８年度 午後�機〔簑� |
|
|
 |
データベースの基礎理論に関する次の記述を読んで, 設問1〜3に答えよ。
物流EDI(電子データ交換)が対象とする主な業務は，物流事業者が依頼主からの発送指示を受け，指定された荷送元から運送業者を介して，荷物を荷届先の荷受人に運送することである。次に示すのは，交換する電文（電子データ）を管理するための関係データベースの一部である。
電文 （電文番号，区分コード，作成日時）
依頼主（電文番号，運送依頼番号，法人コード，法人名，部門コード，部門名，住所，電話番号，担当者）
荷送先（電文番号，法人コード，法人名，部門コード，部門名，住所，電話番号，担当者，集荷日時，作業条件）
図1は，関係"依頼主"の関係従属性の構造を示したものである。属性の意味は，表1のとおりとする。関係"荷送主"のタプルには，次のような運用規則があるものとする。
※依頼主と荷送元が完全に一致し，集荷時間，作業時間の属性値が必要ない場合には，関係"荷送元"のタプルを省く。
┌─────────────┐
│ ┌───────┐ │
│ │運送依頼番号 │ │
│ └───────┘ │
│ │
│ ┌─────────┐ │
│ │┌───────┐│ │
┌────→│ ││┌─────┐││ │ ┌─────┐
│ │ │││法人コード├┼┼─┼→│ 法人名 │
┌──┴──┐ │ ││└─────┘││ │ └─────┘
│電文番号 │ │ ││┌─────┐││ │ ┌─────┐
└─────┘ │ │││部門コード│├┼─┼→│ 部門名 │
↑ │ ││└─────┘││ │ └─────┘
└─────┤ ││ ││ │ ┌─────┐
│ ││ ├┼─┼→│ 住 所 │
│ ││ ││ │ └─────┘
│ │└───────┘│ │
└─┼─────────┼─┘
│ │ ┌─────┐
│ ┌─────┐ ├──→│電話番号 │
│ │ 担当者 │ │ └─────┘
│ └─────┘ │
└─────────┘
┌──┬───────────┬───────────┬───────────┐
│ │┌────┐ │┌─────┐ │┌────┐ │
│ ││┌──┐│ ┌──┐││┌──┐ │┌──┐││┌──┐│ │
│ │││ Ａ ├┼→│ Ｃ ││││ Ａ │←┼┤ Ｃ ││││ Ａ ││ ┌──┐│
│ ││└──┘│ └──┘││└──┘ │└──┘││└──┘│←┤ Ｃ ││
│凡例││┌──┐│ ┌──┐││┌──┐ │ ││┌──┐│ └──┘│
│ │││ Ｂ │├→│ Ｄ ││││ Ｂ │ │ │││ Ｂ ││ │
│ ││└──┘│ └──┘││└──┘ │ ││└──┘│ │
│ │└────┘ │└─────┘ │└────┘ │
├----┼----------------------┼----------------------┼----------------------┤
│ │Ａ → Ｂ │ │ Ｃ→｛Ａ,Ｂ｝ │
│ │｛Ａ, Ｂ｝→Ｃ │ Ｃ → Ａ │ Ｃ→ Ａ │
│ │ │ │ Ｃ→ Ｂ │
└──┴───────────┴───────────┴───────────┘
図1 関数"依頼主"の関係従属性
表1 属性の説明
┌──────┬─────┬──────────────────────────────┐
│ 属性 │ 関 係 │ │
│ ├─┬─┬─┤ │
│ │電│依│荷│ │
│ │文│頼│送│ │
│ │ │主│元│ │
├──────┼─┼─┼─┼──────────────────────────────┤
│電文番号 │○│○│○│電文の受信者側でつける一意な管理番号 │
│区分コード │○│ │ │電文の種類を識別するコード │
│作成日時 │○│ │ │電文の送信者が電文を作成した日付け │
│伝送依頼番号│ │○│ │依頼主の部門の中で一意な管理番号 │
│法人コード │ │○│○│依頼主や荷送り主などの法人を一意に識別するコード │
│法人名 │ │○│○│依頼主や荷送り主などの法人名 │
│部門コード │ │○│○│法人の中の部門を一意に識別するコード(法人毎にコードをふる) │
│部門名 │ │○│○│法人の中の部門名 │
│住所 │ │○│○│法人の部門の住所 │
│電話番号 │ │○│○│法人の部門の担当の電話番号 │
│ │ │ │ │(１担当者に対して複数の電話番号は許されない) │
│担当者 │ │○│○│法人の部門の運送依頼番号ごとに一意な担当者の氏名 │
│集積日時 │ │ │○│運送事業者が荷物の集荷を行う日時 │
│作業条件 │ │ │○│荷物に対する取扱い上の作業区分 │
└──────┴─┴─┴─┴──────────────────────────────┘
設問1
関数従属性について次の問いに答えよ。
（１）関係"依頼主"の候補キーを挙げよ。
（２）関係"荷送元"の関数従属性の構造を示す図2を図1にならって完成せよ。
┌─────────┐
│┌───────┐│
┌────┐ ││┌─────┐││ ┌─────┐
│集荷日時│ │││法人コード│││ │ 法人名 │
└────┘ ││└─────┘││ └─────┘
┌────┐ ││┌─────┐││ ┌─────┐
│電文番号│ │││部門コード│││ │ 部門名 │
└────┘ ││└─────┘││ └─────┘
┌────┐ ││ ││ ┌─────┐
│作業条件│ │└───────┘│ │ 住 所 │
└────┘ │ │ └─────┘
│ │ ┌─────┐
│ ┌─────┐ │ │電話番号 │
│ │ 担当者 │ │ └─────┘
│ └─────┘ │
└─────────┘
図2 関数"荷送元"の関係従属性
設問2
正規形について，次の問いに答えよ。
（１）関係"依頼主"は，第1正規形であるが第2正規形ではない。
その理由を関係"依頼主"の属性を用いて具体的に50字以内で述べよ。
（２）関係"依頼主"は，第3正規形の関係に分解せよ。主キーを下線を引いて示せ。
設問3
関係演算について，次の問いに答えよ。
（１）表2は，関係"電文"のタプルの集合，表3は，関係"依頼主"に対する射影によって得られたタプルの集合である。
"─"の箇所は属性地の表記を省略してある。表2と表3の自然結合によって得られるタプルの集合と必要な属性を，答案用紙の表に記入せよ。
表2 関係"電文" 表3 関係"依頼主"の射影の結果
┌──┬───┬──┐ ┌──┬───┬───┬──┐
│電文│区 分│作成│ │電文│運送依│法 人│法人│
│番号│コード│日時│ │番号│頼番号│コード│名 │
├──┼───┼──┤ ├──┼───┼───┼──┤
│ 01 │ A │ ─ │ │ 01 │ 1001 │ 100 │Ｘ社│
│ 02 │ B │ ─ │ │ 02 │ 2001 │ 101 │Ｙ社│
│ 03 │ C │ ─ │ │ 03 │ 3001 │ 102 │Ｚ社│
└──┴───┴──┘ └──┴───┴───┴──┘
（２）関係"依頼主"と関係"荷送元"の電文番号を共通属性とする自然結合を行うと，データベースの検索業務上どのような不都合が生じるか。40字以内で述べよ。
（３）（２）の不都合を避けるためには，関係"依頼主"と関係"荷送元"にどのような関係演算を行えばよいか，10字以内で述べよ。
|
| 解答 |
|
|
|
|
SQL文に関する次の記述を読んで, 設問1〜3に答えよ。
大手機械メーカのＧ社では，基幹業務アプリケーションが，関係データベース管理システム(RDBMS)上で稼動している。ユーザ部門ではRDBMSが提供する対話形式の問合せ画面から，SQL言語を利用して必要なデータ照会･加工を行う。
情報システム部のＨ君は，ユーザに対するデータベース教育を担当している。具体的には，各業務データベース構造や内容，SQL言語などの使用方法などの教育，ユーザからの質問・要望への回答などを担当している。
ある日，Ｈ君は人事部給与課のユーザから，次のような質問を受けた。
〔人事部給与課ユーザからの質問〕
(1) SQL言語を用いた照会によって，社員の課別平均給与金額を計算して図1のような照会結果を得たい。
┌──────────────────┐
│ 課別平均給与一覧 │
│ │
│課コード 課名 平均給与金額│
│ 2001 総務部総務課 296,472 │
│ 1002 人事部給与課 334,587 │
│ 1003 人事部研修課 411,821 │
│ 1001 人事部人事課 353,123 │
│ 2002 総務部法務課 402,333 │
│ │
└──────────────────┘
図1 課別平均給与一覧のイメージ
(2) 同様に，各課における最高額の給与を得ている社員の一覧として，図2のような照会結果を得たい。
┌───────────────────────┐
│ 課別最高給与一覧表 │
│ │
│課コード 課名 社員名 最高給与金額│
│ 1001 人事部人事課 山田太郎 553,343 │
│ 1001 人事部人事課 鈴木花子 553,343 │
│ 2001 総務部総務課 大田実 601,296 │
│ 1003 人事部研修課 山本正 674,186 │
│ 1002 人事部給与課 田中茂 601,296 │
│ 2002 総務部法務課 木下淳 482,444 │
└───────────────────────┘
図2 課別最高給与一覧表のイメージ
(3) 部門内社員数が100人以上の部門を構成する課の名称と課内社員数の一覧を作成しようとして，図3のSQL文を実行したところ，エラーとなった。
┌──────────────────┐
│SELECT 課名，課内社員数 │
│ FROM 課テーブル │
│ WHERE 部門コード = │
│ (SELECT 部門コード │
│ FROM 部門テーブル │
│ WHERE 部門内社員数 >= 100)│
└──────────────────┘
図3 実行時にエラーを起こしたSQL文
〔給与システムのテーブル構造〕
ユーザから質問に関する給与システムのテーブル(表)の構造は，次のとおりである。
なお，下線のついた項目は主キーをあらわす。
社員テーブル
┌────┬────┬────┬────┬──────┐
│社員番号│社員氏名│社員住所│給与金額│所属課コード│
│￣￣￣￣│ │ │ │ │
└────┴────┴────┴────┴──────┘
所属課コードは，課テーブルに対する外部キーである。
部門テーブル
┌─────┬───┬──────┐
│部門コード│部門名│部門内社員数│
│￣￣￣￣￣│ │ │
└─────┴───┴──────┘
課テーブル
┌────┬──┬─────┬─────┐
│課コード│課名│課内社員数│部門コード│
│￣￣￣￣│ │ │ │
└────┴──┴─────┴─────┘
部門コードは，部門テーブルに対する外部キーである。
設問1
ユーザからの質問(1)の課別平均給与一覧表を作成するためのSQL文を記述せよ。図1のイメージ中の表題，項目の見出しは，考慮しなくてもよい。
設問2
ユーザからの質問(2)の課別最高給与一覧表を作成するためのSQL文を，副問合せを用いて記述せよ。図2のイメージ中の表題，項目の見出しは，考慮しなくてもよい。
設問3
ユーザからの質問(3)について，図3のSQL文が実行時にエラーを起こした理由と，訂正すべき点を80字以内で記述せよ。
|
| 解答 |
|
|
|