Microsoft Word - D2指導書.docx

Transcription

1 D2 データベースの設計と処理方式 本指導書は 本実験を行うにあたり有用な情報をまとめたものである 中盤に SQL 構文についての補助資料 を また後半に実験の方法および実験課題を添付しているのでそれぞれ参照のこと 1. 実験目的与えられた情報を持つデータベースを 関係モデルにおける正規化手法に基づき冗長性を取り除くよう設計し これを具体的な対象として 各種の問い合わせを行うための基本処理方式について関係演算を用いて実現し データベース [1] に関する理解を深めることを主たる目的とする 2. 関係データベースの基礎本セクションでは 本実験を進めるに際して理解しておく必要があると思われる諸概念を簡単に紹介する まず データベース構築の意義について簡単に触れた後 本実験に取り上げている関係モデルとこれに基づくデータベース処理方式の位置づけをする 2.1. データとはデータとは 辞書的定義によると データとは 物事の推論の基礎となる事実 また 参考となる資料 情報 [2] と定義されている データは実世界を認識 ( 観察 観測 調査 検査など ) することによって収集される また一般的に収集されたデータは 続いて編集 加工される ここで 編集 加工とは 蓄積や利用の形態にふさわしいようにデータの形態を変えることである さらに 編集 加工されたデータは続けて蓄積され 利用の段階において 必要に応じてユーザ向けの表現に変換され さまざまな計算や推論に用いられる 2.2. データベースとはデータベースとは 先に述べたデータを蓄積することによって コンピュータ内に構築された実世界のモデルと捉えることができる ( 厳密には データベースは実世界記述するためのデータモデルを基礎として 次にデータベーススキーマを規定し そのインスタンスとして形式的に定義されるべきものであるが この段階では 先に述べたような直感的な理解を前提に議論を進める ) データベースの要件としては 以下のものが挙げられる (1) データベース中のデータは統合されていること (2) データベース中は組織体の共有資源となっていることここで統合という概念は データベース中の異なったデータでも それらが実世界の同一の事物に関わるデータであるなら相互に関連づけられていて 本来有縁なデータ同士が無縁であるかのようには組織化されていないことを意味する また共有資源とは データベースがある特定の応用のためだけに構築されたものではなく 何人ものユーザ 何本もの応用プログラムから共通にアクセスされうる組織体の唯一無二の資源であることを意味する 従って 応用プログラムごとに構築される個別のデータ群であるファイルとは異なり データベースは応用プログラムの実行や終了とは無関係に存在する 即ち データはデータベース管理システム (Database Management System: DBMS) によって一元的に管理され 応用プログラムはデータベース管理システムの仲介でデータにアクセスすることになる このデータベース管理システムは ユーザとデータベースの橋渡しをするシステム であり 一般にはオペレーティングシステム (Operating System: OS) の上に構築される巨大なソフトウェアとして実現されている 1

2 関係名 属性名 R S# SNAME STATUS CITY PARTS S1 Smith 20 London PT1 S2 Jones 10 Paris PT2 S3 Blaks 30 Paris PT3 S4 Clark 20 London PT4 S1 Smith 20 London PT5 タップル 図 1: 関係表の例 2.3. データベース構築の意義データベース構築の意義 [3] は 組織体の統合 共有環境としての実世界の有限モデルをコンピュータ内に構築し さまざまな質問や応用プログラムの実行に役立てようとするものであり 以下のような効用があるとされている (1) 応用プログラムのデータ独立性の達成物理的データ独立性と論理的データ独立性 (2) データの一貫性の保証 - 参照一貫性 - ドメイン一貫性 - データの無矛盾性 (3) 重複データの発生の抑制特に 関係データベースでは後述するように関係の正規化によって理論的にもデータの重複が抑えられる (4) データの標準化の達成 (5) データの機密保護の達成 2.4. 関係モデル関係モデルが基礎とする関係は 数学的には以下のように定義される 関係 n 個の集合 S 1, S 2,, S n を考える これらの集合には互いに共通要素があってもよい 各 S j から要素を一つずつ取ってきて並べた n 個組 (s 1, s 2,, s n ) を要素とする集合 R を これら n 個の集合の上の関係と呼ぶ また S j を関係 R の第 j 定義域 n 個組 (s 1, s 2,, s n ) を関係 R のタップルと呼ぶ 一つの関係は 一つの表 ( 行列 ) で表すことができ この表を関係表と呼び この関係表は次の性質を持つものとする 関係表の性質 (1) 表の一つの行が関係 R の一つの n 個組を表す (2) 行同士の前後関係は意味を持たない (3) 全く同一の行を含まない (4) 表の一つの列 ( 例えば 第 j 列 ) は関係 R の定義域 S j に対応する (5) 各列には 関係 R の中で一意となる名前を付ける ここで 一般的には関係の定義域はすべて異なった集合である必要はない 図 1 には 部品の供給に関する関係を示した この関係の定義域は 供給業者番号 (S#) 供給業者名 2

3 重複行を取り除く R S# SNAME STATUS CITY PARTS S1 Smith 20 London PT1 S2 Jones 10 Paris PT2 S3 Blaks 30 Paris PT3 S4 Clark 20 London PT4 S1 Smith 20 London PT5 S S# SNAME STATUS CITY S1 Smith 20 London S2 Jones 10 Paris S3 Blaks 30 Paris S4 Clark 20 London 図 2: 射影演算の例 射影 R[S#, SNAME, STATUS, CITY] (SNAME) 供給業者状態コード (STATUS) 供給業者所在地 (CITY) 部品 (PARTS) の 5 つである 従って この関係は 5 つ組の集合になっている 定義域自身も集合であり この例では S# は {S1, S2, S3, S4} という集合である さて これまでは n 個組の中の要素を指定するために 何番目の要素という言い方をしてきた これからは 関係の中での定義域の呼び方を属性名と呼ぶことにする 関係 R における n 個の定義域参照を R の属性と呼ぶ そこで n 個組中の j 番目の要素は 関係 R の属性 A j の値といえばよい 関係を表形式で表した場合は 各列に対して属性名を与える 属性名は その列に現れる値の定義域とその意味とがわかりやすいように決めるのがよい 2.5. 射影演算関係を正規化するには 射影と呼ばれる論理演算を活用する 関係を一つの表形式で考えることにしよう 射影は その関係をいくつかの属性を指定し それに対して ( またはその上へ ) 射影する という言い方をする すなわち 指定された属性の集合を A とするとき A に対する射影とは もとの関係 ( 表 ) のうち A に含まれる属性の列だけからなる表を作り その中で重複する行を消去したものである この射影は以下のように 数学的に定義を与えることができる 射影演算 関係 R の全属性集合を A={A 1, A 2,, A n } とし A の部分集合を B={A i1, A i2,, A im } とする ここに 1 i1 i2 im n すなわち B A である このとき 関係 R の属性集合 B 上への射影 R[B] は次のように定義される R[B] = R[A i1, A i2,, A im ] ={(b 1, b 2,, b m ) (a 1, a 2,, a m ) R b 1 =a i1 b 2 =a i2 b m =a m } 図 1 の関係 R を射影した例を図 2 に示す 関係 S は R の4つの属性 {S#, SNAME, STATUS, CITY} の上に射影したものである この列において R の関係表から PARTS の列を消去した後 重複行を消去した結果 関係表は 4 つの属性と 4 つのデータ行になっている 3

4 R S# SNAME STATUS CITY PARTS S1 Smith 20 London PT1 S2 Jones 10 Paris PT2 S3 Blaks 30 Paris PT3 S4 Clark 20 London PT4 S5 Smith 20 London PT5 非正規関係 R, PT ( 非単純定義域 (PARTS) が存在 ) PT1 P# PNAME COLOR WEIGHT QTY CLASS P1 Nut Red P2 Bolt Green P3 Screw Blue P4 Screw Red P5 Cam Blue P6 Cog Red PT2 P# PNAME COLOR WEIGHT QTY CLASS P1 Nut Red P2 Bolt Green PT3 P# PNAME COLOR WEIGHT QTY CLASS P3 Screw Blue P5 Cam Blue PT4 P# PNAME COLOR WEIGHT QTY CLASS P2 Bolt Green P4 Screw Red P5 Cam Blue 図 3: 非正規関係の例 PT5 P# PNAME COLOR WEIGHT QTY CLASS P5 Cam Blue 関係データベースの正規化一般に関係の属性に対応する定義域が 集合の集合であることがわかる このような属性を含む関係を非正規関係と呼ぶ 関係を要素とする定義域を非単純定義域と呼ぶ ここでは非単純定義域は その要素である関係がすべて同一形式のもの すなわち属性の組合せが同じものだけを考えることにする 換言すれば 非単純定義域 D は D のすべての要素 ( 関係 ) が現れるある関係のべき集合 ( 集合のすべての部分集合を要素とする集合 ) の一つの部分集合になっている 図 3 は非正規関係の例である この関係 R の属性 PARTS の値が関係 PT になっている この供給業者と部品のデータモデルは 関係モデルの重要な性質が端的に現れるのでよく引用される ここに示した例の関係 R の属性が既に説明したが 残りの関係 PT1~PT5 の属性は 部品の色 (COLOR) 部品の重量 (WEIGHT) 部品数量 (QTY) 配送種別 (CLASS) である 3.1. 関係の正規化図 3 を見てもわかるように 非正規関係は整然としていないし 多くの冗長性を持っている このような非正規関係に対して 単純定義域だけを属性とすれば 取り扱いが容易になると同時に それを操作するための論理的な演算が定義できる 非正規関係を この望ましい性質の関係に変換することを 正規化と呼ぶ 3.2. 関数従属関係 R は一般に その属性の間の写像を定義していると捉えることができる 関係 R の属性の部分集合を 2 つ考え それぞれを A B とする (A B には共通要素があってもよい ) A = {A 1, A 2,, A n } のとき A の1つの値 a は a = {a 1, a 2,, a n } という n 個組になる ここで a j は属性 A j の値であり A j に対応する定義域 D j の要素 (a j D j ) である R によって定義される A と B の間の写像がどんな性質のものかは R[A, B] なる射影を行って調べればわかる 4

5 R S# SNAME STATUS CITY PARTS PT1 P# PNAME COLOR WEIGHT QTY CLASS S1 Smith 20 London PT1 PT2 P# P1 PNAME Nut COLOR Red WEIGHT12 QTY3 CLASS1 S2 Jones 10 Paris PT2 PT3 P2 P# P1 Bolt PNAME Nut Green COLOR Red WEIGHT12 QTY3 CLASS1 S3 Blaks 30 Paris PT3 P2 P3 Bolt Screw Green Blue PT4 P# P3 PNAME Screw COLOR Blue WEIGHT17 QTY4 CLASS2 S4 Clark 20 London PT4 P4 Screw Red P5 Cam Blue PT5 P# P2 S5 Smith 20 London PT5 P5 PNAME Bolt Cam COLOR Green Blue WEIGHT17 12 QTY2 CLASS2 1 1 P5 P4 P6 Cam Screw Cog Blue Red Red P5 Cam Blue 射影 展開 & 射影 S S# SNAME STATUS CITY SPT S# P# PNAME COLOR WEIGHT QTY CLASS S1 Smith 20 London S1 P1 Nut Red S2 Jones 10 Paris S1 P2 Bolt Green S3 Blaks 30 Paris S1 P3 Screw Blue S4 Clark 20 London S1 P4 Screw Red S1 P5 Cam Blue S1 P6 Cog Red S2 P1 Nut Red 関係 R, PT の第一正規形 S, SPT S2 P2 Bolt Green S3 P3 Screw Blue S3 P5 Cam Blue S4 P2 Bolt Green S4 P4 Screw Red S4 P5 Cam Blue S5 P5 Cam Blue 図 4: 非正規形から第一正規形への変換 こうして得られる写像の性質は 次の 4 分類のいずれか あるいはそのいくつかを満たすものに整理できる (1) 限定しない写像 :A のどの属性も B のすべての属性に対応しその逆もいえる (2) 多対多の写像 :A の複数個の値が B の複数個の値に対応し B のある複数個の値も A の複数個の値に 対応している (3) 多対一の写像 :A のどの値もそれぞれ B のただ 1 つの値に対応しているが いくつかの A の値に対応す る B の値が同一でもあってもよい このような写像を特に関数関係と呼び B は A に関数従属であるとい う 関数従属であることを A B と書くことにする また 関数従属がないことを で表すことにする 例えば 図 3 の PT1 の関係において f:p# {PNAME} という関数関係があるため P# PNAME となる なぜならば 部品番号 (P# の値 ) はどれもただ 1 つの部品名に対応しているが 1 つの部品名が 2 つの異 なる部品番号に対応しているからである (4) 一対一の写像 : A のどの値もそれぞれ B のただ1つの値に対応し 逆の関係も成立している このよう な写像を特に索引関数と呼ぶ 関係 R の 2 つの属性集合 D,E を考えたとき E が D に関数従属であったとしよう (D E) このとき D のあらゆる 真部分集合を考えて E がそのいずれにも関数従属でないならば E は D に全関数従属であるという D E で表すこ とにする 5

6 図 4 の SPT を具体例に考えてみよう D = {S#, P#}, E = {QTY} からなる属性集合を考える D のあらゆる部分集合について調べてみればわかるように S#= QTY, P#= QTY であり しかも {S#, P#} QTY であるから E は D に全関数従属である すなわち {S#, P#} {QTY} である ここで我々が扱っているデータは時間の推移に従って変化していくものである そこで ある関係において属性間に関数関係は索引関数があるというのは 現在および将来のどの時点をとってもデータ間にそのような写像があると考える場合を指すのである 換言すれば 関数従属性とは モデル化される対象の実世界における構造や データに対して与えられた構造の定義によって決まるデータの本質的な構造を表すものと考えるべきである それは ある時点でのデータにたまたま見いだされるといった偶発的なものではないのである 関係の候補キーとは その 1 つの属性または属性の集まり A であり R の一意の識別子であって非冗長な識別子なるものをいう 換言すれば R のどの属性 B j も A に関数従属であり ( 一意性 ) A から属性を 1 つ取り除くと もはやそれには関数従属にならない属性 B j が存在する ( 非冗長性 ) ような A のことである これからわかるように A が 1 つの属性であり かつ R の一意な識別子であれば それは明らかに候補キーである 例えば 関係 PT1 において P は 1 つの候補キーである 1 つの関係に対して 候補キーが 2 つ以上存在することがある この場合 そのどれか 1 つを選択してその関係の主キーとする 図 3 では R の主キーとして S を考えることができる 以降主キーには下線をつけて示すことにする 3.3. 正規形 第一正規形 関係 R がその属性として非単純定義域を 1 つも含まないとき R は第一正規形 (1NF) であるという R(A, B, C) を非正規関係とし 属性 C が非単純定義域を参照し A B は単純定義域に対する属性であるとする C が参照する非単純定義域は C(X, Y) という関係で作られているとしよう ( 非単純定義域とは 既に述べたように同一形式の関係を要素とする集合である ) 従って 関係 R は R(A, B, C (X, Y)) と書くことができる R の主キーを A C の主キーを X とすれば R を次の 2 つの関係に分解することができ これによって情報が失われることはない R = R (A, B) C = C (A, X, Y) 図 3 に示した供給業者と部品の関係モデルを第一正規形にすれば 図 4 のようになる 第二正規形 第一正規形の関係が さらに次の条件を満たすとき その関係は第二正規形 (2NF) であるという 第二正規形では 主キー以外の属性はいずれもいかなる候補キーにも全関数従属である 図 4 の SPT では PNAME COLOR WEIGHT CLASS はいずれも P# だけに関数従属である 一方 QTY は S# と P# の組に対して関数従属である ここで SPT の主キーは S# と P# の組であるから 主キー ( これも候補キーの 1 つである ) に全関数従属でない属性 (PNAME COLOR WEIGHT CLASS) が存在することになり SPT は第二正規形ではないことがわかる 第二正規形への正規化は 第一正規形に含まれるある種の冗長性と不統一性を除くことを意味している 供給業者と部品の第二正規形表現を図 5 に示す 第二正規形を作るには 第一正規形の関係に対して射影演算を行う 例えば 第一正規形の関係 R(A, B, C, D) を考え 次の従属関係があるとする A C (A, B) D この場合 R を次の 2 つの新しい関係に分解 ( 射影による ) すればよい 6

7 SPT S# P# PNAME COLOR WEIGHT QTY CLASS SP S# P# QTY S1 P1 3 S1 P2 2 S1 P3 4 S1 P4 2 S1 P5 1 S1 P6 1 S2 P1 3 S2 P2 4 S3 P3 4 S3 P5 2 S4 P2 2 S4 P4 3 S4 P5 4 S5 P5 5 S1 P1 Nut Red S1 P2 Bolt Green S1 P3 Screw Blue S1 P4 Screw Red S1 P5 Cam Blue S1 P6 Cog Red S2 P1 Nut Red S2 P2 Bolt Green S3 P3 Screw Blue S3 P5 Cam Blue S4 P2 Bolt Green S4 P4 Screw Red S4 P5 Cam Blue S5 P5 Cam Blue 射影 : SPT[S#, P#, QTY] 1NF: P# (PNAME, COLOR, WEIGHT, CLASS) (S#, P#) QTY P P# PNAME COLOR WEIGHT CLASS P1 Nut Red 12 1 P2 Bolt Green 17 2 P3 Screw Blue 17 2 P4 Screw Red 14 1 P5 Cam Blue 12 1 P6 Cog Red 19 2 図 5: 供給業者と部品の関係の第二正規形 射影 : SPT[P#, PNAME, COLOR, WEIGHT, CLASS] R1 = R (A, C) R2 = R (A, B, D) 第二正規形でない形には 3 つの問題点が指摘されている 供給業者と部品の関係の例でその問題点は以下のようになる (1) P# PNAME COLOR WEIGHT といった部品の明細情報は 業者がその部品を実際に供給するまでデータベースに格納できない 部品情報だけを事前に格納するには架空の業者番号を作って登録しなければならない (2) ある部品を供給しているすべての業者が 一時的にその部品の供給を中止した場合 この部品の番号 7

8 P P# PNAME COLOR WEIGHT CLASS P1 Nut Red 12 1 P2 Bolt Green 17 2 P3 Screw Blue 17 2 P4 Screw Red 14 1 P5 Cam Blue 12 1 P6 Cog Red 19 2 射影 : P[P#, PNAME, COLOR, WEIGHT] P P# PNAME COLOR WEIGHT P1 Nut Red 12 P2 Bolt Green 17 P3 Screw Blue 17 P4 Screw Red 14 P5 Cam Blue 12 P6 Cog Red 19 2NF: 推移従属が存在 P# PNAME, COLOR, WEIGHT WEIGHT CLASS 射影 : C[WEIGHT, CLASS] C WEIGHT CLASS 図 6: 供給業者と部品の関係の第三正規形を持つデータがデータベースから消去され その結果 この部品に関する固有の明細情報もすべて失われる (3) ある部品の明細に変更があった場合 多数の n 個組を変更しなければならなくなる いくつの n 個組を変更しなければならないかは そのときどきで異なってくる 第三正規形 関係 R において 互いに重複しない 3 つの属性集合 A B C を考えよう いま B は A に関数従属であり C は B に関数従属であるが A は B に関数従属でないとしよう このとき C は A に推移従属であるという これを以下のように書くことにする A B B C 第二正規形の関係に推移従属性があるとき それがなくなるように分解した関係を第三正規形と呼ぶ 上記の関係 R は次のように分解すればよい R1 = R (A, B) R2 = R (B, C) 関係 R において属性集合 A B C の間に次のような関数従属性があるとき C は A に完全推移従属であるという A B B C B A C B 関係 R が第三正規形であるとは 第一正規形であり主キー以外のいずれの属性もすべて候補キーに全関数従属であり しかも推移従属性が存在しないものをいう 8

9 図 5 の例について考えてみよう この関係 P には推移従属性がある この列の配送種別は WEIGHT<15 なら CLASS = 1 WEIGHT 15 なら CLASS = 2 という約束で決められている 従って CLASS は WEIGHT に関数従属 (WEIGHT CLASS) であるが その逆は成り立たない (CLASS WEIGHT) 同様に CLASS と WEIGHT とは ともにそれぞれ P# に関数従属である (P# CLASS, P# WEIGHT) そこで P の関係から CLASS を除くように分解すれば第三正規形を作ることができる 図 6 はこうして得られた結果である 第二正規形を第三正規形にしなければならない理由は データベースを後日更新する場合に 第二正規形のままでは困った問題が起こる可能性があるためである 例えば 前の例で部品 P4 の重量を 14 から 17 に変更する必要が起こったとしよう 第二正規形でこの更新を行うとデータベースに矛盾が生じる可能性がある すなわち この重量の変更に伴って 配送種別も 1 から 2 に変更しておかなければならないからである データベースが第三正規形であれば 部品の重量を変更するだけでよく 関係 C を参照することによって 正しい配送種別が自然に選ばれることになる もちろんこの場合 新しい重量値 17 が関係 C に登録されていることが必要である 3.4. 関係演算重要な関係演算は既に述べた射影に加えて 結合 選択の 3 種類である まず結合演算と選択演算を定義するために θ 比較演算を説明する ここに θ は =,, <,, >, のいずれかの関係演算子を表すものとする 関係 R の 2 つの属性 A i と A j が θ 比較可能というのは A i と A j が同じ定義域の上に定義されていて かつ R の任意のタップルを t とするとき t[a i ] θ t[a j ] という命題が真か偽かのいずれかに決めうる ( すなわち 判定不能にならない ) ことを意味する 個々で t[a i ] は関係 R のタップル t の第 i 属性の値である 同様に 2 つの関係 R(A) と S(B) の属性集合 A, B A =A 1, A 2,, A k B =B 1, B 2,, B k の間に θ 比較可能というのはそれぞれの A j, B j (j=1, 2,, k) の間に θ 比較可能であることをいう さて R(A) と S(B) を 2 つの関係とする A, B はそれぞれ A, B の部分集合とする さらに A, B を θ 比較可能な属性集合とするとき R と S の A と B 上の θ 結合とは次のように定義される関係である R[A θ B ]S = (a,b) a R b S (a[a ] θ b[b ]) なお θ 結合演算は後で述べる直積と θ 選択演算を使って次のように定義することができる R[A θ B ]S = (R S) [R.A θ S.B ] θ が等号 (=) のとき 等結合と呼ぶ さらに自然結合を次のように定義する R(A 1, A 2,, A n ) と S(B 1, B 2,, B m ) の共通属性 ( すなわち 名前と定義域が等しい属性 ) を C 1, C 2,, C k とする ( つまり (A 1, A 2,, A n ) (B 1, B 2,, B m ) = C 1, C 2,, C k ) このとき R と S の自然結合 ( これを R*S と書く ) は次のように定義される R*S=R([C 1 =C 1, C 2 =C 2,, C k =C k ]S)[ A 1, A 2,, A n, D 1, D 2,, D m-k ] ここで (B 1, B 2,, B m )-C 1, C 2,, C k = A 1, A 2,, A n, D 1, D 2,, D m-k とする 自然結合は 次のように操作的に説明することもできる R の n 個組と S の n 個組とで 自然結合すべき属性に対応する値が等しいものを一組ずつ (r と s) 抽出し 2 つを続けて並べた長い n 個組の関係を作る 一致するものすべてに対してこれを行い 長い n 個組の関係を作る この関係から重複した属性の列の一方を消去する こうしてできた関係が R と S の自然結合である 関係の正規化によって 関係従属性に基づいて分割された 2 つの関係は 自然結合によってもとの 1 つの関係に復元できる 例えば図 5 の関係 SPT は 関係 P と関係 SP とを 属性 P# に関して自然結 9

10 SPT S# P# PNAME COLOR WEIGHT QTY CLASS S1 P1 Nut Red S2 P1 Nut Red 図 1: 供給業者と部品の関係を選択した結果の例 合すれば復元できる これまで述べてきた射影と結合は 関係表の列に関して分割あるいは併合するものであった もう一つの関係演 算である選択は 関係の行をある条件を満足するものだけに選択するものである 例えば図 7 の関係は 関係 SPT を P# = p1 で選択した結果である このように選択は 指定した条件を満たさない行を関係からすべて消去するという働 きをする R[A θ B]= r : r R (r[a] θ r[b]) これまで説明してきた 3 つの関係演算以外にも 次の演算も使われる (1) デカルト積 ( 外積 ) (2) 和 (3) 共通集合 (4) 差 - (5) 商 デカルト積 ( 外積 )( ) は関係代数で用いられ m+n 個組の拡大関係を作り出し次のように定義される R S = (r, s): r R s S 和 ( ) 共通集合( ) 差(-) は集合演算のそれと同じである ただし 演算対象となる関係は 同じ属性からな る関係どうしでなければならない 商 ( ) は 次式を満足するものとして定義する (R S ) S = R 他の方法で商を定義することもできるが ここではデカルト積の逆演算として定義しておく 以上の演算を組み合 わせれば 関係データベースのいずれの情報も完全に取り出すことが可能となることが知られている [4] 参考文献 [1] Olle, T. W.: The Codasyl Approach to Data Base Management, John Wiely & Sons, New York (1978). ( 邦訳 : 西村, 植村 ( 監訳 ), CODASYL のデータベース, 共立出版 (1979)). [2] 松村明監修他 : 大辞泉, 小学館 (1998). [3] 増永良文 : リレーショナルデータベースの基礎, オーム社 (1990). [4] Codd, E. F.: A Relational Model of Data for Large Shared-Data Banks, Communication ACM, Vol.13, no.6, pp (1970). 10

11 SQL 構文についての補助資料 ( 次頁に続く )

12 電子情報工学専門実験データベースの設計と処理方式補足資料 関係表の定義 CREATE TABLE テーブル名 ( 属性名 1 属性 1 の型 ( 属性 1 に対する列制約 ), 属性名 2 属性 2 の型 ( 属性 2 に対する列制約 ),..., 属性名 n 属性 n の型 ( 属性 n に対する列制約 ), ( テーブル制約 ) ); カッコ内はオプションと書くと 下の関係表が出来上がります テーブル名 属性名 1 属性名 2 属性名 n ( 中身は空 ) 属性の型 としては 例えばこんなものがあります INT: 整数値 CHAR(m): m 文字の固定長文字列 VARCHAR(m): 可変長文字列 ( 最大 m 文字 ) また 列制約 については NOT NULL: NULL( 空白 ) を許可しない PRIMARY KEY: 主キー などがあります テーブル制約 では PRIMARY KEY ( 属性名 1, 属性名 2): 属性名 1 と属性名 2 のセットを主キーとする FOREIGN KEY ( 属性名 2) REFERENCES 関係表名 : 属性名 2 を 関係表名の外部キーとする などの指定ができます

13 電子情報工学専門実験データベースの設計と処理方式補足資料 関係表の削除 DROP TABLE テーブル名 ; と書くと テーブル名 として定義している関係表を削除します データの追加 関係表の定義 で定義した関係表に対し INSERT INTO テーブル名 VALUES ( 属性値 1, 属性値 2,..., 属性値 n ); と書くと 以下のようにデータが追加されます テーブル名 属性名 1 属性名 2 属性名 n 属性値 1 属性値 2 属性値 n データの検索 ( 基本 ) SELECT 属性名 1, 属性名 2 FROM テーブル名 WHERE 条件 ; と書くと テーブル名 の関係表から 条件 を満たすタップルの 属性名 1 と 属性名 2 を照会することができます 全属性を照会したい場合は 属性名のところを * すれば OK です また 条件を指定しない場合 WHERE の行は省略できます 例えば テーブル名 の関係表に入っている全てのデータを照会したい場合は SELECT * FROM テーブル名 ; とすれば良いわけです

14 電子情報工学専門実験データベースの設計と処理方式補足資料 データの検索 ( 複数テーブルの照会 ) SELECT テーブル名 1. 属性名 1, テーブル名 2. 属性名 2 FROM テーブル名 1, テーブル名 2 WHERE 条件 ; と書くと 複数のテーブルに対する照会が行えます 上記の例だと 条件 を満たすタップルのうち テーブル名 1 の 属性名 1 と テーブル名 2 の 属性名 2 を照会することになります また 条件の中で指定する属性は SELECT 句の中身と同様 テーブル名. 属性名 とピリオドで区切る形で表現します 例えば 関係表 A の属性 a と 関係表 B の属性 b の値が等しい という条件は WHERE A.a = B.b と表現します データの検索 ( その他もろもろ ) SELECT で指定する属性名には 平均値 (avg) や最大値 (max) 合計 (sum) などを指定できます 例えば GROUP BY 属性名という記述を追加すると その属性名でグループ化した結果を出力します ORDER BY 属性名という記述を追加すると 出力結果がその属性名について昇順に並べ替えられます 降順にしたい場合は さらに後ろに DESC と記述すれば OK です SELECT で指定する属性名の後ろに as 表示属性名と書くと 出力の際に表示される属性名を指定できます WHERE の 条件 は AND や OR を使って複数指定できます SELECT avg( 属性名 1) as 平均値 FROM テーブル名 WHERE 条件 1 OR 条件 2 GROUP BY 属性名 2 ORDER BY avg( 属性名 1) DESC;

15 電子情報工学専門実験データベースの設計と処理方式補足資料 と書くと テーブル名 中で 条件 1 または 条件 2 を満たすタップルのうち 属性名 2 ごとの 属性名 1 の平均値が 平均値 という属性として 属性名 1 の平均に対して降順に出力されます WHERE で指定する 条件 には =, <, >,!= などが使えます また WHERE 属性名 1 IN ( 属性値 1, 属性値 2,..., 属性値 n) と書くと WHERE 属性名 1 = 属性値 1 OR 属性名 1 = 属性値 2... OR 属性名 1 = 属性値 n と同じ意味になります これを応用すると WHERE 属性名 1 IN ( ) SELECT 属性名 2 FROM テーブル名 2 WHERE 条件 のように SELECT 文の結果出力されたものを条件として利用できるようになります 上記の例は テーブル名 2 の中で 条件 を満たすタップルの 属性名 2 のいずれかと 属性名 1 が等しい という意味になります 複数の属性の組み合わせで IN を使うこともでき WHERE ( 属性名 1-1, 属性名 1-2) IN ( ) SELECT 属性名 2-1, 属性名 と書けば IN 内の SELECT 文で抽出される 属性名 2-1 と 属性名 2-2 の組み合わせのいずれかと 属性名 1-1 と 属性名 1-2 の組み合わせが等しい という意味になります

16 実験の方法および実験課題 ( 次頁に続く )

17 実験の方法および実験課題 電子情報工学専門実験第 1 部 データベースの設計と処理理 方式 担当 : 情報システム工学クラス古川正紘 実験の 目的与えられた情報を持つデータベースを 関係モデルにおける正規化手法に基づき冗長性を取り除くよう設計し これを具体的な対象として 各種の問い合わせを行うための基本処理方式について関係演算を用いて実現し 関係データベースに関する理解を深めることを主たる目的とする 実験の準備 1. ワークステーション (SunRay クライアント ) にログインする 初期パスワードは アカウント名! に設定されている 適宜変更すること 2. 端末エミュレータを起動する デスクトップ上の右クリックメニューから 端末エミュレータを開く を選択する 以降 実験はこの端末エミュレータ上で行う 3. データベースを構築し 内容を確認する データベースの操作は ソースファイルを作成し それを読み込ませることで行う ソースファイルの編集にはテキストエディタ ( 画面左下の 起動 アプリケーション アクセサリ テキストエディタ ) を用いる vi など 他のエディタを使っても良い 以下のコマンドで サンプルプログラム test.sql をコピーし その内容を確認する cp ~furu/expd2/test.sql. ( 終端の ピリオド を忘れないこと sql は SQL の小文字 ) ( 次頁に続く ) 1

18 実験の方法および実験課題 test.sql の内容は以下の通り の後はコメント -- 関係表 テスト の構築 CREATE TABLE テスト ( 氏名 CHAR(16), 年齢 INT ); -- テスト にデータを追加 INSERT INTO テスト VALUES ( 佐藤太郎, 20); -- テスト の内容を確認 SELECT * FROM テスト ; 次に端末エミュレータ上で以下のコマンドを入力し SQL を実行する psql は PSQL の小文字 $ psql h exptrma1 f test.sql オプションの説明 : -h データベースのあるサーバ -f 実行するファイル名 SQL の実行に成功すると以下のように表示される CREATE TABLE INSERT xxxxxx 1 (xxxxxx は適当な数値 ) 氏名 年齢 佐藤太郎 20 (1 row) 注意! エラーが出る場合 以下の原因が考えられる ü 日本語を使用するときは文字コードを UTF にする ü スペース 括弧 コンマ セミコロン スペースなど 日本語文字以外で全角になっている 文字がある すべて半角にすること 全角 / 半角は [ 半角 / 全角 ] キーで切り替える ü 文の末尾にセミコロンがない Ctrl-S で全角スペースを検索すること ü 文字列をシングルクオーテーション ( ) で囲んでいない ü 必要なところにスペースが入っていない 2

19 実験の方法および実験課題 最後に test.sql を以下のように修正し psql コマンドで再実行する の後はコメント -- 関係表 テスト の削除 DROP TABLE テスト ; -- 関係表 テスト の再構築 CREATE TABLE テスト ( 学籍番号 CHAR(16) NOT NULL, -- 空白を許可しない 氏名 CHAR(16), 年齢 INT, 性別 CHAR(4), PRIMARY KEY ( 学籍番号 ) -- 主キー ); -- テスト にデータを追加 INSERT INTO テスト VALUES ( 822xxx01, 佐藤太郎, 20, 男 ); INSERT INTO テスト VALUES ( 822xxx02, 鈴木花子, 21, 女 ); -- テスト の内容を確認 SELECT * FROM テスト ; 実行に成功すると以下のように表示される DROP TABLE NOTICE: CREATE TABLE / PRIMARY KEY will create ( 以下略 ) CREATE TABLE INSERT xxxxxx 1 (xxxxxx は適当な数値 ) INSERT xxxxxx 1 (xxxxxx は適当な数値 ) 学籍番号 氏名 年齢 性別 xxx01 佐藤太郎 20 男 822xxx02 鈴木花子 21 女 (2 rows) メモ 実験を進めていくと 問い合わせ結果を表示させた直後から操作ができなくなることがある こ の場合はアルファベットの q キーを押して 画面表示状態から抜け出る (quit) 3

20 実験の方法および実験課題 実験課題 1 週 目 1. 正規化以下のデータベーススキーマを用いて 学生が受講した科目とその成績を管理しているものとする このスキーマを正規化 ( 第 1 正規形 第 2 正規形 第 3 正規形 ) せよ グループ={ 学籍番号 氏名 年令 } 個人成績 ={ 学籍番号 科目コード 科目名 単位数 点数 成績 } 属性 単位数 は 科目の単位数 ( 例えば この専門実験の単位数は 2 単位 ) 属性 成績 は 点数によって決まるランク ( 例えば 優 良 可 不可 ) 第 2 正規形 第 3 正規形に正規化する手順と それぞれの理由 ( 主キーや属性の従属関係を用いて ) を明記すること 2. データベース構築課題 1. で設計したスキーマ ( 第 3 正規形 ) をもとに 関係表を定義 (CREATE TABLE ) せよ また 課題 3. の問合せ結果が正しいことを証明できるような 適当なデータを格納 (INSERT INTO ) せよ CREATE TABLE の際 主キー (PRIMARY KEY) は必ず指定すること 学生 10 人程度 5 科目程度は登録すること 各学生には 少なくとも 2 科目は受講させること 科目の内容など 具体的なデータの中身については 特に制限を設けない 課題 3. の結果が正しいか判定できる範囲で任意のものを登録可とする 2 週 目 3. 問合せ以下の問合せを SQL で記述し 結果を確認せよ (1) 科目別平均点のリスト 科目名 平均点 を求めよ (2) 個人別平均点のリスト 氏名 平均点 を求めよ (3) 個人別取得単位数のリスト 氏名 取得単位数 を求めよ (4) 科目別合格者のリスト 科目名 氏名 を求めよ (5) 科目別最高点 科目名 氏名 最高点 を求めよ ( やや難 ) SQL による問合せ文作成の際 テーブルを結合する SQL 構文 JOIN は利用しないこと 考察課題 1. データベース管理システムとしては 今回用いた関係データベース管理システム (RDBMS: Relational Database Management System) のほかに オブジェクト指向データベース管理システム (OODBMS: Object-Oriented Database Management System) がある OODBMS の特徴を RDBMS と比較しながら説明せよ また RDBMS と OODBMS がそれぞれどのようなアプリケーションに向いているかを述べよ 4

21 実験の方法および実験課題 2. 非常に重要なデータ ( 成績や銀行口座のデータなど ) が格納されたデータベースでは 機密保護や障害対策が非常に重要となる そのためにデータベース管理しシステムがどのような機能を提供しているかを調べよ ( アクセス権管理 バックアップ管理 二重化 複製管理など ) 3. 現在広く利用されている World Wide Web (WWW) とデータベースの関係について あなたが思うことを述べよ レポートの作成 提出 1. 2 週分で 1 つのレポートを作成すること Word OpenOffice などのワープロソフト LaTeX など 作成方法は問わない 2. レポート形式は以下の構成とすること (1) 実験日時 (2) 実験目的 (3) 実験の理論的背景 (4) 実験課題 : 課題 2, 3 の SQL や出力結果については (9) 付録に記載してもよい (5) 考察課題 (6) 得られた知見 (800 字程度に要約する ) (7) 実験の感想 (8) 謝辞 参考文献 (9) 付録 ( タイトルを付記したプログラム およびデータリスト ) 付録のデータリストについては 作成したテーブルの属性名が分かるように表形式で出力すること psql コマンドを実行した結果出力された形式で記載することが望ましい レポート本文の付録として記載のこと ( 出力結果を個別ファイルで送らないこと ) レポートの提出期限は 2 週目の翌週の木曜日 13:00 とする 提出は電子メールにより行うこと 宛先 : m.furukawa@ist.osaka-u.ac.jp 件名 : データベースレポート 本文 : 氏名, 学籍番号 ( 全桁 ) を含めること 参考 HP 等 PostgreSQL 文書 ( Ⅱ SQL 言語 および Ⅵ リファレンス 等 ) データの正規化 (Web 教材木暮仁氏作 ) 実験の内容についての質問は 古川 ( 脳情報通信融合研究センター (CiNet) 3A1 前田研究室 : m.furukawa@ist.osaka-u.ac.jp ) まで電子メール等にて受け付ける 端末の操作などの質問は 大倉先 生 (E6 棟 2F 情報処理演習室 : ookura@ist.osaka-u.ac.jp ) まで 5