D2 データベースの設計と処理方式本指導書は本実験を行うにあたり有用な情報をまとめたものである実験の方法および実験課題は実験当日に配布する資料を参照のこと 1. 実験目的与えられた情報を持つデータベースを関係モデルにおける正規化手法に基づき冗長性を取り除くよう設計しこれを具体的な対象とし

(1)

1

D2 データベースの設計と処理方式

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

(2)

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

(3)

3

(SNAME)、供給業者状態コード (STATUS)、供給業者所在地 (CITY)、部品 (PARTS) の 5 つである。従って、この関係は5 つ組の集合になっている。定義域自身も集合であり、この例では S#は、{S1, S2, S3, S4}という集合である。さて、これまでは n 個組の中の要素を指定するために、何番目の要素という言い方をしてきた。これからは、関 係の中での定義域の呼び方を属性名と呼ぶことにする。関係R における n 個の定義域参照を R の属性と呼ぶ。 そこで、n 個組中の j 番目の要素は、関係 R の属性 Ajの値といえばよい。関係を表形式で表した場合は、各列に対して属性名を与える。属性名は、その列に現れる値の定義域とその意味とがわかりやすいように決めるのがよい。 2.5. 射影演算 関係を正規化するには、射影と呼ばれる論理演算を活用する。関係を一つの表形式で考えることにしよう。射影は、その関係をいくつかの属性を指定し、それに対して（またはその上へ）射影する、という言い方をする。すなわち、指定された属性の集合をA とするとき、A に対する射影とは、もとの関係（表）のうち A に含まれる属性の列だけからなる 表を作り、その中で重複する行を消去したものである。この射影は以下のように、数学的に定義を与えることができる。【射影演算】

関係R の全属性集合を A={A1, A2, …, An}とし、A の部分集合を B={Ai1, Ai2, …, Aim}とする。ここに 1 ≤ i1 ≤ i2 ≤ … ≤ im ≤ n、すなわち、B⊆A である。このとき、関係 R の属性集合 B 上への射影 R[B]は次のように定義される。

R[B] = R[Ai1, Ai2, …, Aim]

={(b1, b2, …, bm)|∃(a1, a2, …, am)∈R∧b1=ai1∧b2=ai2∧…∧bm=am}

図1 の関係 R を射影した例を図 2 に示す。関係 S は R の４つの属性 {S#, SNAME, STATUS, CITY} の上に射影したものである。この列において R の関係表から、PARTS の列を消去した後、重複行を消去した結果、関係表は4 つの属性と 4 つのデータ行になっている。

図 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

S S# SNAME STATUS CITY

S1 Smith 20 London S2 Jones 10 Paris S3 Blaks 30 Paris S4 Clark 20 London

射影

R[S#, SNAME, STATUS, CITY]

重複行を取り除く

(4)

4 3. 関係データベースの正規化 一般に関係の属性に対応する定義域が、集合の集合であることがわかる。このような属性を含む関係を非正規関係と呼ぶ。関係を要素とする定義域を非単純定義域と呼ぶ。ここでは非単純定義域は、その要素である関係がすべて同一形式のもの、すなわち属性の組合せが同じものだけを考えることにする。換言すれば、非単純定義域 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 = {A1, A2, …, An}のとき、A の１つの値 a は、 a = {a1, a2, …, an}という n 個組になる。ここで、aj は属性 Ajの値であり、Ajに対応する定義域Djの要素 (aj∈Dj) である。R によって定義される A と B の間の写像がどんな性質のものかは、R[A, B]なる射影を行って調べればわかる。

R S# SNAME STATUS CITY PARTS

PT1 P# PNAME COLOR WEIGHT QTY CLASS

P1 Nut Red 12 3 1 P2 Bolt Green 17 2 2 P3 Screw Blue 17 4 2 P4 Screw Red 14 2 1 P5 Cam Blue 12 1 1 P6 Cog Red 19 1 2

P1 Nut Red 12 3 1 P2 Bolt Green 17 4 2

P3 Screw Blue 17 4 2 P5 Cam Blue 12 2 1

P2 Bolt Green 17 2 2 P4 Screw Red 14 2 1 P5 Cam Blue 12 1 1

P5 Cam Blue 12 5 1 非正規関係R, PT

（非単純定義域(PARTS) が存在）

(5)

5 こうして得られる写像の性質は、次の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 のただ１つの値に対応し、逆の関係も成立している。このよう な写像を特に索引関数と呼ぶ。

関係R の 2 つの属性集合 D，E を考えたとき、E が D に関数従属であったとしよう (D→E)。このとき D のあらゆる真部分集合を考えて、E がそのいずれにも関数従属でないならば、E は D に全関数従属であるという。D⇒E で表すことにする。

R S# SNAME STATUS CITY PARTS

P1 Nut Red 12 3 1 P2 Bolt Green 17 2 2 P3 Screw Blue 17 4 2 P4 Screw Red 14 2 1 P5 Cam Blue 12 1 1 P6 Cog Red 19 1 2

P1 Nut Red 12 3 1 P2 Bolt Green 17 4 2

P3 Screw Blue 17 4 2 P5 Cam Blue 12 2 1

P2 Bolt Green 17 2 2 P4 Screw Red 14 2 1 P5 Cam Blue 12 1 1

P5 Cam Blue 12 5 1

関係R, PT の第一正規形 S, SPT

S S# SNAME STATUS CITY

S1 Smith 20 London S2 Jones 10 Paris S3 Blaks 30 Paris S4 Clark 20 London

SPT S# P# PNAME COLOR WEIGHT QTY CLASS

S1 P1 Nut Red 12 3 1 S1 P2 Bolt Green 17 2 2 S1 P3 Screw Blue 17 4 2 S1 P4 Screw Red 14 2 1 S1 P5 Cam Blue 12 1 1 S1 P6 Cog Red 19 1 2 S2 P1 Nut Red 12 3 1 S2 P2 Bolt Green 17 4 2 S3 P3 Screw Blue 17 4 2 S3 P5 Cam Blue 12 2 1 S4 P2 Bolt Green 17 2 2 S4 P4 Screw Red 14 3 1 S4 P5 Cam Blue 12 4 1 S5 P5 Cam Blue 12 5 1 射影展開＆射影図 4：非正規形から第一正規形への変換

(6)

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 のどの属性 BjもA に関数従属であり（一意性）、A から属性を 1 つ取り除くと、もはや それには関数従属にならない属性Bjが存在する（非冗長性）ような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 つの新しい関係に分解（射影による）すればよい。

(7)

7 R1 = R (A, C) R2 = R (A, B, D) 第二正規形でない形には 3 つの問題点が指摘されている。供給業者と部品の関係の例でその問題点は以下のようになる。 (1) P#、PNAME、COLOR、WEIGHT といった部品の明細情報は、業者がその部品を実際に供給するまでデータベースに格納できない。部品情報だけを事前に格納するには架空の業者番号を作って登録しなければならない。 (2) ある部品を供給しているすべての業者が、一時的にその部品の供給を中止した場合、この部品の番号 1NF:

P#→(PNAME, COLOR, WEIGHT, CLASS) (S#, P#)→QTY

SPT S# P# PNAME COLOR WEIGHT QTY CLASS

S1 P1 Nut Red 12 3 1 S1 P2 Bolt Green 17 2 2 S1 P3 Screw Blue 17 4 2 S1 P4 Screw Red 14 2 1 S1 P5 Cam Blue 12 1 1 S1 P6 Cog Red 19 1 2 S2 P1 Nut Red 12 3 1 S2 P2 Bolt Green 17 4 2 S3 P3 Screw Blue 17 4 2 S3 P5 Cam Blue 12 2 1 S4 P2 Bolt Green 17 2 2 S4 P4 Screw Red 14 3 1 S4 P5 Cam Blue 12 4 1 S5 P5 Cam Blue 12 5 1 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

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 射影： SPT[S#, P#, QTY] 射影：

SPT[P#, PNAME, COLOR, WEIGHT, CLASS]

(8)

8 を持つデータがデータベースから消去され、その結果、この部品に関する固有の明細情報もすべて失われる。 (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 が第三正規形であるとは、第一正規形であり主キー以外のいずれの属性もすべて候補キーに全関数従属であり、しかも推移従属性が存在しないものをいう。

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]

2NF: 推移従属が存在

P#→PNAME, COLOR, WEIGHT WEIGHT→CLASS

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 C WEIGHT CLASS 12 1 14 1 17 2 19 2 射影： C[WEIGHT, CLASS] 図 6：供給業者と部品の関係の第三正規形

(9)

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 つの属性 AiとAjがθ 比較可 能というのは、AiとAjが同じ定義域の上に定義されていて、かつR の任意のタップルを t とするとき t[Ai ] θ t[Aj] という命題が真か偽かのいずれかに決めうる（すなわち、判定不能にならない）ことを意味する。個々で t[Ai]は関係R のタップル t の第 i 属性の値である。 同様に、2 つの関係 R(A) と S(B)の属性集合 A’, B’ A’=A1, A2, …, Ak B’=B1, B2, …, Bk の間にθ 比較可能というのはそれぞれの Aj, Bj (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(A1, A2, …, An)と S(B1, B2, …, Bm)の共通属性（すなわち、名前と定義域が等しい属性）を C1, C2, …, Ckとする（つまり、(A1, A2, …, An)∩(B1, B2, …, Bm) = C1, C2, …, Ck）。このときR と S の自然結合（これを R*S と書く）は次のように定 義される。 R*S=R([C1 =C1, C2 =C2, …, Ck =Ck]S)[ A1, A2, …, An, D1, D2, …, Dm-k] ここで、(B1, B2, …, Bm)－C1, C2, …, Ck = A1, A2, …, An, D1, D2, …, Dm-kとする。自然結合は、次のように操作的に説明することもできる。R の n 個組と S の n 個組とで、自然結合すべき属性に対応する値が等しいものを一組ずつ（r と s）抽出し、2 つを続けて並べた長い n 個組の関係を作る。一致するものすべてに対してこれを行い、長いn 個組の関係を作る。この関係から重複した属性の列の一方を消去する。こうしてできた関 係が、R と S の自然結合である。関係の正規化によって、関係従属性に基づいて分割された 2 つの関係は、自然結合によってもとの1 つの関係に復元できる。例えば図 5 の関係 SPT は、関係 P と関係 SP とを、属性 P#に関して自然結

(10)

10 合すれば復元できる。これまで述べてきた射影と結合は、関係表の列に関して分割あるいは併合するものであった。もう一つの関係演算である選択は、関係の行をある条件を満足するものだけに選択するものである。例えば図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.377-387 (1970).

SPT S# P# PNAME COLOR WEIGHT QTY CLASS S1 P1 Nut Red 12 3 1 S2 P1 Nut Red 12 3 1 図 1：供給業者と部品の関係を選択した結果の例

(11)

1

D2 データベースの設計と処理方式

担当：情報システム工学クラス天方大地

実験の目的

与えられた情報を持つデータベースを、関係モデルにおける正規化手法に基づき冗長性を取り除くよう設 計し、これを具体的な対象として、各種の問い合わせを行うための基本処理方式について関係演算を用 いて実現し、関係データベースに関する理解を深めることを主たる目的とする。

実験の準備

1. ワークステーション（SunRay クライアント）にログインする。 2. 端末エミュレータを起動する。 3. データベースを構築し、内容を確認する。 - データベースの操作は、ソースファイルを作成し、それを読み込ませることで行う。ソースファイルの編集は好きなテキストエディタを用いて良い。 - 以下のコマンドでサンプルプログラム “test.sql”をコピーし、内容を確認する。 cp ~amagata/expD2/test.sql . （終端のピリオドを忘れないこと） test.sqlの中身は、下図の通り．次に端末エミュレータ上で以下のコマンドを入力し、実行する。 $ psql –h exptrma1 –f test.sql

(12)

2 オプションの説明: -h データベースのあるサーバ -f 実行するファイル名 SQLの実行に成功すると以下のように表示される。 ※注意点（実行に失敗する場合）文字コード、スペース（半角かどうか）、セミコロンの有無、シングルクオーテーションにしているかどうかを確認すること。次に、test.sql を下図のように修正し、psql コマンドで再度実行する。

(13)

3

実験課題

【1 週目】 課題1. 正規化 以下のデータベーススキーマを用いて、学生が受講した科目とその成績を管理しているものとする。このスキーマを正規化（第一正規形→第二正規形→第三正規形）せよ。学生＝{学籍番号、氏名、年齢} 個人成績＝{学籍番号、科目コード、科目名、単位数、点数、成績} ※ 属性「単位数」は、科目の単位数（例えば、この専門実験の単位数は 2 単位）。 ※ 属性「成績」は、点数によって決まるランク（例えば「秀, 優, 良, …」「S, A, B, …」など）。 ※ 第二正規形、第三正規形に正規化する手順と、それぞれの理由（主キーや属性の従属関係を用いて）を明記すること。 課題2. データベース構築 課題 1.で設計したスキーマ（第三正規形）をもとに、関係表を定義（CREATE TABLE …）せよ。また、課題 3.の問合せ結果が正しいことを証明できるような、適当なデータを格納（INSERT INTO …）せよ。

※ CREATE TABLE の際、主キー(PRIMARY KEY)は必ず指定すること。

※ 学生 10 人程度、5 科目程度は登録し、各学生は最低 2 科目以上受講しており、各科目に最低 2 名以上の受講生がいる状態とすること。 ※ 科目の内容など、具体的なデータの中身については、特に制限を設けない。課題 3.の結果が正しいか判定できる範囲で任意のものを登録可とする。 【2 週目】 課題3. 問合せ 以下の問合せを SQL で記述し、結果を確認せよ。 (1) 科目別平均点のリスト〔科目名、平均点〕を求めよ。 (2) 個人別平均点のリスト〔氏名、平均点〕を求めよ。 (3) 単位取得者の個人別取得単位数のリスト〔氏名、取得単位数〕を求めよ。 (4) 科目別合格者のリスト〔科目名、氏名〕を求めよ。 (5) 科目別最高点〔科目名、氏名、最高点〕を求めよ。（やや難） ← ヒント：「サブクエリ」

※ SQL による問合せ文作成の際、テーブルを結合する SQL 構文、NATURAL [INNER] JOIN は 利用しないこと。

考察課題

1. 関係データベース管理システム (RDBMS: Relational Database Management System) を、今回の実験で用いたものを含めて三つ挙げ、それぞれの特徴を述べよ。

(14)

4 2. 以下の用語のうち二つを選択し、今回用いた関係データベース管理システムと比較しながら、それらの特徴を説明せよ。 (ア) オブジェクト指向データベース (イ) XML データベース (ウ) Key-Value Store 3. 非常に重要なデータ（成績や銀行口座のデータなど）が格納されたデータベースでは、機密保護や障害対策が非常に重要となる。そのためにデータベース管理しシステムがどのような機能を提供しているかを調べよ。（アクセス権管理、バックアップ管理、二重化、複製管理など）

4. 現在広く利用されている World Wide Web (WWW) とデータベースの関係について、あなたが思うことを述べよ。

レポートの作成・提出

1. 2 週で 1 つのレポートとする。作成方法（Word 等）については自由とする。

2. レポートの形式は以下のようにすること。

(1) 実験日時

(2) 実験目的

(3) 実験の理論的背景

(4) 実験課題（SQL や出力結果は付録として記載すること）

(5) 考察課題

(6) 得られた知見（800 字程度）

(7) 実験の感想

(8) 参考文献（ホームページなどを含む）

(9) 付録（課題で用いたテーブル、および出力結果）

※ソースファイルを個別ファイルとして送らないこと

レポートの提出期限は、2 週目の翌週の木曜日の 13:00 とする。提出は電子メールにより行い、レポートは PDF ファイルにまとめて添付すること。宛先: amagata.daichi@ist.osaka-u.ac.jp。件名: データベースレポート本文: 氏名、学籍番号（全桁）実験の内容についての質問は、天方（情報系総合研究棟 2F A202 原研究室 : amagata.daichi@ist.osaka-u.ac.jp）まで電子メール等にて受け付ける。端末の操作などの質問は、竹本先生（E6 棟 2F 情報処理演習室: takemoto@ist.osaka-u.ac.jp）まで。

(15)

専門実験データベースの設計と処理方式補足資料

関係表の定義

と書くと、関係表が出来上がる。 ※（）内はオプション “属性の型”としては、以下の様なものがある。・ INT: 整数値・ CHAR(m): m 文字の固定長文字列 “列制約”としては、以下の様なものがある。・ NOT NULL: NULL を許さない・ PRIMARY KEY: 主キー

“テーブル制約”としては、以下の様なものがある。

・ PRIMARY KEY (属性名 1, 属性名 2): 属性 1 と属性 2 のセットを主キーとする

・ FOREIGN KEY (属性名 2) REFFERENCES 関係表名: 属性 2 を、関係表名の外部キーとする。 create table テーブル名 ( 属性名 1 属性 1 の型（属性 1 に対する列制約）属性名 2 属性 2 の型（属性 2 に対する列制約） … 属性名 n 属性 n の型（属性 n に対する列制約） (テーブル制約) );

(16)

関係表の削除

と書くと、「テーブル名」として定義している関係表が削除される。

データの削除

と書くと、「テーブル名」として定義している関係表が削除される。と書くと、「テーブル名」の関係表に、（属性値 1, …, 属性値 n）のレコードが追加される。

データの検索

と書くと、「テーブル名」として定義している関係表が削除される。と書くと、「テーブル名」の関係表から、「条件」を満たすレコードの「属性名 1, 属性名 2, …」を照会することができる。全属性を照会する場合は、”*”とすれば良い。また、where 句は省略可能。複数のテーブルを紹介する場合は、以下の例のように記述する。その他、select 句や where 句で指定できる例を挙げる。

・ select 句、where 句では、avg(平均値)や max (最大値)、sum (合計値)が利用できる。

drop table テーブル名;

insert into テーブル名 values ( 属性値 1, … 属性値 n ); select 属性名 1, 属性名 2, … from テーブル名 where 条件 select テーブル名 1.属性名 1, テーブル名 2.属性名 2, … from テーブル名 1, テーブル名 2 where 条件

(17)

・ where 句では、”group by 属性名”で、属性名でグループ化した結果を出力・ where 句では、”order by 属性名 (desc)”で、属性名でソートした結果を出力・ where 句では、”and” や “or” を用いて条件を複数指定可能

以下は、上で紹介したものの利用例これは、「テーブル名」中で、「条件 1」 or 「条件 2」を満たすレコードの内、「属性名 2」ごとの「属性名 1」の平均値が降順で出力される。また、と書くと、と同じ意味になる。これは、以下のような”サブクエリ”を用いた応用例がある。 select avg (属性名 1) from テーブル名 where 条件 1 or 条件 2 group by 属性名 2

order by avg (属性名 1) desc;

where 属性名 1 in (属性名 1, 属性名 2, …, 属性名 n) where 属性名 1 = 属性値 1 or 属性名 1 = 属性値 2 … or 属性名 1 = 属性値 n where 属性名 1 in ( select 属性名 2 from テーブル名 2 where 条件 )