DVIOUT-08

Transcription

1 データベースと情報検索 2010 Part データベースシステム 1.1. データベース管理システム (DBMS) 1.2. Web 上ではバックエンドに 2. データモデルと関係データベース 2.1. RDB と関係代数 2.2. 実体関係モデル (ER: entity-relationship) 2.3. 関係データベース : RDB: 複数の表の集まり 2.4. 用語の整理 1 3. データベース操作 4. DB 定義 操作言語 4.1. キー 4.2. データ入力 更新 削除 4.3. 集合演算と問合わせ 5. SELECT 文による問合わせ : 検索し一覧を作る 6. 関係モデルと論理モデル ( 検索と証明 ) 6.1. 推論例 6.2. 命題論理 6.3. 命題論理の導出原理 6.4. 一階論理と導出原理 6.5. 変数, 定数に関する単一化 ( マッチング ) 6.6. DDB( 演繹データベース ) の導入的まとめ ) 7. 関係代数と SQL 7.1. Cartesian product, theta-join 7.2. 自然結合 7.3. タプル参照 7.4. subquery ( 副問合せ ) 7.5. ビュー表 (viewed table) 7.6. 構文と例 7.7. 商演算 $ R/S $ 7.8. 否定演算と集合差 \label{negation 7.9. GROUP BY と集計 SQL コマンド列の例 8. 予備 8.1. 集計 8.2. GROUP BY 8.3. ビュー表 (viewed table) 1 データベースシステム データベースシステムの考え方を概説し Part1 ( 関係代数 ) と Part2( データベース操作言語 論理モデル ) への橋渡しを行う ファイルシステムからデータベースへ : データを集めたとする 1. 単なるデータのコレクション? 2. 効率的なデータ管理を誰がするのか? 3. 他のサーバとの接続 ( 通常 Web サーバのバックエンド ) 4. 安全性への配慮は誰が行うのか? データベース管理システム (DBMS) 1.2 Web 上ではバックエンドに データベース : 複数の応用目的でその共有を意図して組織的 永続的に格納されたデータ群 DBMS: データベースを管理するソフト群データの物理的な表現や配置 アクセスは一手に引き受ける DBMSの基本機能データベース定義データベースアクセス ( 質問処理 ) ハッシュ表 Bツリー等によるインデッキシングレコードの所在情報 : ディスク上のどこにあるか大規模になるとメモリに全部は置けない 3 4

2 2 データモデルと関係データベース 蓄積すべきデータに対する枠組み 捉え方 概念設計 (e.g. ERモデル RDB) ( 概念 ) スキーマ : データモデルに従うデータの形式的側面型にはめることで不具合 不整合性を抑制 RDBの場合は関係スキーマと従属性など. R(A 1,,A n ) with A 2,A 3 A 1 関係名 ( 属性名のリスト ) 整合性条件 ( 属性間の制約 ) 各属性の定義域 ( 属性がとりえる値の集合 ) は指定データ型 データベースインスタンス : スキーマに基づいて格納されたデータ群 1 つのスキーマに対し,1 つの表 RDBでは 一般に複数の表から構成される 2.1 RDBと関係代数 DBに蓄積すべき対象を全て表 ( 関係 ) で表現表はタプル ( 行 ) の集まり データ操作は集合演算表で表せるデータは多く 分かりやすさからこれまで普及 冗長性を排除するために 複数の表でデータを管理 依存性に基づく表の分解データ操作言語 (SQL)+アプリ言語分解したものを統合 (join) し 必要な情報をゲットし操作 処理 Triangle( TID color pattern) t1 赤 メッシュ t2 青 塗り潰し Point( PID X Y Type) p p p p Triangle-Point( TID PID ) t1 p11 t1 p12 t1 p13 t2 p21 データベース言語データ定義言語 (DDL) データ操作言語(DML) RDBのSQLは 両者の機能を持つ本講義では MySQL ( を参照マニュアル : Developer Zone - Documentation 実体関係モデル (ER: entity-relationship) スキーマ設計の際に用いられるダイアグラム表現実体 (entity): で表示され 固体 (object) を表す属性 : 楕円 で表示され 固体が持つ属性をあらわす Key( 主キー ): 属性の中で 固体を識別するキー属性 ( 複数属性 OK) ( 同じクラスの ) 異なる実体が同一の主キーを持ってはいけない実体と実体の関係 : 菱形 で表示され 概念スキーマでは 実体のキーを属性としてもつ ( 新たな集合としての ) 関係に変換される 例のように 独自の属性を持つこともできる 2.3 関係データベース : RDB: 複数の表の集まり 実体 : 主キーを持つタプルで表現 実体間関係 : 実体の主キーの組をキーとするタプルで表現実体の主キーは この文脈において 外部キー (*) 職員 { 職員番号 氏名 住所 月給 E00101 太郎 札幌市西区八軒 300K E00102 一郎 札幌市西区琴似 480K E00103 ニ郎 札幌市中央区丸山 600K 所属 { 職員番号 部局名 開始年 部局 { 部局名 予算額 E00101 工学部 1989 工学部 E00102 工学部 1989 医学部 E00103 医学部 1897 外部キー : 実体を指す主キーで, 他の表におけるキーとして使用されるもの 7 8

3 2.4 用語の整理 1 関係スキーマ : R(A 1,,A n ) with A 2,A 3 A 1 関係名 ( 属性名のリスト ) 整合性条件 ( 属性間の制約 ) ただし ある集合 dom(a j ) を仮定関係 : 関係スキーマ R(A 1,, A n ) のインスタンス集合論の意味での関係 R dom(a 1 ) dom(a n ) データベースインスタンス : 複数の関係スキーマとそのインスタンスから構成される Remark: 関係スキーマのインスタンス 即ち関係は集合だが 実際のDB では 同一のレコードが重複する場合もある ( 多重集合 ) 3 データベース操作 4 DB 定義 操作言語 関係代数, 関係論理 : RDB に対してどのような関係 ( 集合 ) 操作を規定 実際には : 1. スキーマの定義 2. 検索 問い合わせ 3. データの更新, 4. アクセス制御 DB 定義 操作言語としての SQL (Structured Query Language) 関係代数演算 ( 集合演算 ) に加えて 各種の集計操作が可能 キー 1. 超キー (superkey): 属性の集まりで, タプルを同定できるもの 2. ( 候補 ) キー (candidate key, または単に key): 超キーのうち, 極小 ( どれかを除去すると超キーでない ) なもの 3. 主キー (primary key): 候補キーのうち, 適宜選択したもの 主キー制約 ( どれを主キーにするか宣言 ) 4. 外部キー (foreign key):null 値でない限り,( 他の ) 表の主キーの値とし て必ず出現するもの non-null 制約 : null ( 何も指していない状態 ) を許さない 他クラス ( 関係 ) オブジェクト ( タプル ) へのポインタと理解 参照整合性条件 (referential integrity constraint) どの表のタプルなら指して ( 参照して ) よいかを指定する制約 科目 { ( 主キー ) 単位 ( 外部キー ) 科目名 履修 { 科目番号数科目番号 学籍番号 成績 101 データベース 工学 知識ベース工 2 学 プログラミン グ言語 111 卒業研究 表定義と各種の制約 各種のドメイン制約 : 属性毎のデータ型を定めるプログラミングにおける変数の型宣言と同じ入力時の型チェック等につかえる実際はSQLの方言にもよる 使うときにマニュアルを参照 CREATE TABLE 履修 ( 科目番号 CHAR(3) NOT NULL, // 属性のデータ型, ドメイン制約学籍番号 CHAR(5) NOT NULL, 成績 INTEGER, PRIMARY KEY ( 科目番号, 学籍番号 ), // 主キーの指定 FOREIGN KEY ( 科目番号 ) // 参照整合性条件 REFERENCE 科目 ( 科目番号 ), // どの表のどの属性かを指定 FOREGIN KEY ( 学籍番号 ) REFERENCE 学生 ( 学籍番号 ), // 参照整合性条件 CHECK ( 成績 BETWEEN 0 AND 100) // ドメイン制約 ) 11 12

4 4.2 データ入力 更新 削除実表 ( ディスク上で実際にレコードが形成 ) が対象 ビュー表 ( 実表から論理的に派生的に定義される ) に対しては不可 実表が更新されると ビュー表の定義に従って ビュー表も更新されると見なせる INSERT INTO 実表名 VALUES( 属性値 1, 属性値 2,) // タプルの登録 DELETE FROM 実表名 WHERE 条件 // 条件を満たすタプルを削除 4.3 集合演算と問合わせ UNION, MINUS( 集合差 ), INTERSECT 問合せ文 1 UNION 問合せ文 2 問合わせ (SELECT 文 ): SELECT 属性リスト FROM 表リスト WHERE 条件指定 集合演算と問合わせにおける表演算 ( 射影 選択 結合 ) は言語的には区別されていることに注意 (*) ( SELECT name FROM MovieStar ) UNION ( SELECT name FROM President ) このほか ファイルから一括入力を行う LOAD 表の削除を行う DROP などのコマンド群あり (*) このように SQLは関係代数の代数演算体系に直接対応しているわけではない. 共通集合や差集合演算は subquery ( 後述 ) 等を用いて実現できる (*1) 操作言語によっても多少, 扱いが異なるので, 実際に使うときに確認すること 大規模な表な同士の例えば差集合操作など, レコードの比較操作を行うさいに, インデッキシングがなされていることは大前提である R(A1,,Ak) と S(B1,,Bk) で, 主キーがそれぞれ A1, B1 で同じデータ型を持つとする. 主キーはインデッキシングされており, よって,R のインスタンスにおける特定のキーが Sのインスタンスに出現するかは, 後述の subquery で判定でき, 質問評価が失敗するもの ( 失敗による否定 ) を選択するすることにより, 差集合は実現できる.( 節 7.9 を参照 ) SELECT 文による問合わせ : 検索し一覧を作る SELECT T i1.c 1,, T im.c m FROM T 1,, T n WHERE ϕ 表 T 1,, T n のタプルで, 条件 ϕ を満たすもののなかから T i1.c 1,, T im.c m を抽出せよ query 実行 一覧出力出力表 { 学籍番号成績 SELECT 学籍番号, 成績 FROM 履修 WHERE 科目番号 = 005 は引用府履修 { 科目番号 学籍番号 成績 結果は一覧 ( 表 ) 名前をつける場合はビュー表にする ( 後述 ) 関係代数式 π 学籍番号, 成績 (σ 科目番号 = ( 履修 )) 6 関係モデルと論理モデル ( 検索と証明 ) DBタプル 論理式 ( 事実 ), 質問 データアクセス 論理的推論履修 (004,203456,40). 履修 (005,203521,100). 履修 { 科目番号学籍番号成績 学生番号が の学生の 科目番号 005 の科目の成績 :- 履修 (005,203521,X 成績 ) 科目 005 の < 学生, 成績 > の一覧を 求める場合 SELECT 成績 FROM 履修 query(x 学籍番号,X 成績 ) WHERE 科目番号 = 005 :- 履修 (005,X 学籍番号,X 成績 ). AND 学籍番号 = :- query(x 学籍番号,X 成績 ). 記法 : 英大文字で始まる文字列で変数を, それ以外で定数を表す 123 のような, 数値型のデータは, 構文解析の時点で自動で数値として解釈されるとする 15 16

5 6.1 推論例?-query(203521, X 成績 ). 学籍番号が の学生の成績 X 成績を求めよ query(x 学籍番号,X 成績 ): 履修 (005, X 学籍番号,X 成績 ). query(203521, X 成績 ): 履修 (005,203521, X 成績 ). with X 学籍番号 = ?- 履修 (005, , X 成績 ). 履修 (005,203521, X 成績 ). with X 成績 =100 X 成績. query(203521,x 成績 ) が証明され 同時に X 成績 = 100 だと分かった 計算 検索 = 存在証明 6.2 命題論理 Socrates is a man. if Socrates is a man then Snocrates is mortal. Hence, Socrates is mortal. Socrates is a man : 命題 ( 真偽のみが興味の対象 ) 上記の内容は 前提が真であれば どのような命題 A Bに対しても成立する A B : A A : B (BならばA) の方向に前提から結論を導く 導出原理 : 逆に ある命題が真かをテストする方法論 : B 命題論理の導出原理 A B : A B?-B (B であることを確かめなさい ) B : A (BであるためにはAであれば十分)?-A (Aであることを確かめなさい ) A (A は前提として正しいことが既知 ) ( 確かめるべきことがなくなった! 証明終 ) 命題が変数をもつ場合も同様 6.4 一階論理と導出原理具体化と3 段論法 mortal(x) : man(x) man(soc) mortal(soc) : man(soc) mortal(soc) 命題論理と全く同様に,mortal(soc) を確かめるための導出原理 :?-mortal(soc). mortal(x) : man(x) mortal(soc) : man(soc). with X=soc 単一化 ( ルールを特化する )?-man(soc). man(soc). ( 確かめるべきことがなくなった! 証明終 ) 19 20

6 6.5 変数, 定数に関する単一化 ( マッチング ) r(t 1,t 2,, t n ): s 1 (t 11,, t 1n1 ),, s m (t m1,, t mnm ). m 0 (m =0で事実,m >0 でルール ( 質問 ビュー ) 定義 ) t i,t jnk は定数 or 変数 ( ビュー表については後述 ) 等号 =: 変数 X= 変数 Y 一方を他方に置き換えてよい変数 X= 定数 a 変数を定数に置き換えてよい定数 a= 定数 b a と b が同じ定数のときのみ成功.?- query(203521, X 成績 ). query(x 学籍番号,X 成績 ): 履修 (005, X 学籍番号,X 成績 ). 単一化 : = X 学籍番号結果 : 単に, X 学籍番号を に置換し, 本体に置き換える 6.6 DDB( 演繹データベース ) の導入的まとめ ) 複雑な関係操作と質問処理を データ式に対する導出原理と単一化という統一的な計算原理で扱える 実表 ( ディスク装置上の存在するデータ ) とビュー表 ( 論理的に定義された表で, 実表に依存. ディスク上に実体はないという意味でバーチャルな表 ) と実表の論理的依存関係を, ルール ( 定義式 ) で単純に表記できる関係スキーマで要求された属性のデータ型 : ( 順序 ) ソート論理等に拡張して扱える. 本講義では省略以下,SQLによる質問処理に対応させる形で,DDBによる質問処理も講述する?- 履修 (005, , X 成績 ). データ ( 事実 ) 履修 (004, , 40) とはマッチしない. データ履修 (005, , 100) と with X 成績 =100 でマッチ (thus, 一般には, マッチングに関する試行錯誤が繰り返される ) 関係代数と SQL 7.1 Cartesian product, theta-join π 科目. 科目番号, 成績 (σ 科目. 科目番号 = 履修. 科目番号 学籍番号 = ( 科目 履修 )) π 科目. 科目番号, 成績 ( 科目 履修 ) SELECT 科目. 科目番号, 成績 履修 { 学籍番号科目番号成績 FROM 科目, 履修 WHERE 科目. 科目番号 = 履修. 科目番号 AND 学籍番号 = 科目 { 科目番号科目名 複数表にまたがる操作で, 表の 直積 もし 10 DB くは theta-join R ϕ S = σ ϕ (R S) 科目. 科目番号 などは 重複した属性名を 14 DB 識別するため その必要がない場合は 単 15 KB に 属性名をかく query(y 科目番号, X 成績 ) :- query 実行 Result { 科目番号 成績 履修 (X 学籍番号, X 科目番号, X 成績 ), 科目 (Y 科目番号, Y 科目名,), X 科目番号 =Y 科目番号, X 学籍番号 = 004. X 科目番号 = Y 科目番号で最初から等化すると 右記の query natural join に相当 query2(c 科目番号, X 成績 ) :- 履修 (X 学籍番号, C 科目番号, X 成績 ), 科目 (C 科目番号, Y 科目名,), X 学籍番号 = 自然結合 直積 (product) 選択 および射影 R S = π X Y σ F (R S) スキーマ R(X), S(Y ), F = ^ A X Y R.A = S.A 結果 R S { A B C D a b c f c d e c SQL 文 SELECT * FROM R NATURAL JOIN S 論理的には : query(a,b,c,d) :- r(a,b,c), s(b,c,d). 属性は引数に対応 R { A B C a b c b c d c d e d e f R S S { B C D b c f d e a d e c R S { A R.B R.C S.B S.C D a b c b c f a b c d e f a b c d e c Z = σ R.B=S.B R.C=S.C (R S) Z { A R.B R.C S.B S.C D a b c b c f c d e d e c π A,R.B,R.C,D (Z) R.B, R.C をもとの B,C に名前替 23 24

7 7.3 タプル参照 SELECT y. 学籍番号 FROM 履修 AS x, 履修 AS y WHERE x. 科目番号 = 15 AND y. 科目番号 = 15 AND x. 学籍番号 = 001 AND y. 成績 > x. 成績 履修 { 学籍番号科目番号成績 条件を満足するタプル x, y の組 ( 一般には n 組 ) を集め,SELECT ドメイン論理 ( タプル レコードを変数で参照する論理 ) オブジェクト指向における オブジェクト参照変数と同じ働き {yid xscore yscore [ 履修 (001,15,xscore) 履修 (yid,15,yscore) yscore > xscore ] 演繹 DB では レコードは 述語の引数として表現 query(yid) :- 履修 (001,15,xscore), 履修 (yid,15,yscore), yscore > xscore. :- の右側 (body) のみで出現する変数は 存在限定 7.4 subquery ( 副問合せ ) SQLによる問合せ結果の要素である という条件の書き方を許すもの SELECT 科目名 FROM 科目 WHERE 科目番号 IN (// 科目のレコード r に対する条件 SELECT 履修. 科目番号 FROM 履修, 学生 WHERE AND 履修. 学籍番号 = 学生. 学籍番号 AND 学科 = 電子工学 ) r. 科目番号が副問合せの結果レコード群の一要素であれば真 query(x 科目名 ) :- 科目 (X 科目番号, X 科目名 ), subquery(x 科目番号 ). subquery(y 科目番号 ) :- 履修 (Y 科目番号, Y 学籍番号, ), 学生 (Y 学籍番号,, 電子工学 ). 又は subquery(y 科目番号 ) :- 履修 (Y 科目番号, Y 学籍番号, ), 学生 (Z 学籍番号,, 電子工学 ), Y 学籍番号 = Z 学籍番号 ビュー表 (viewed table) 副問合せ : 実表から派生的に定義可能な関係を定める 新たな属性は導入できない ビュー表 : 副問合せの表を仮想的な表として定義 FROM 行であたかも実表のごとく参照可 定義上の依存関係は木構造 7.6 構文と例 科目 ( 科目名, 科目 ID, 単位数 ) 実験課題 ( 課題 ID 題目 ) 科目実験関係 ( 科目 ID 課題 ID) // 多対多とする CREATE VIEW // 実験課題を持つ科目実験科目 ( 科目名, 科目 ID, 単位数 ) AS SELECT 科目 * // * は "any" FROM 科目 NATURAL JOIN 科目実験関係 // 科目と科目実験関係の自然結合から // 科目の属性を全て抜き出し それで // 実験科目 を定義 SELECT 科目名 FROM 実験科目 WHERE 単位数 = 2 実験課題を持つ単位数 2の科目の科目名の一覧 科目 { 科目名 科目 ID 単位数 DB L101 2 KBS L102 2 KBE E201 1 科目実験関係 { 科目 ID 課題 ID L101 Y101 L101 X201 実験課題 { 課題 ID 課題 X201 なんとかなんとか Y101 に関する演習 実験課題を少なくとも一つ ( 複数可 ) 持 つ科目のみが 実験科目 に残る 実験科目 { 科目名 科目 ID 単位数 DB L101 2 この場合 ビュー表のタプル数が絞られてい るので join の負荷が減る ( 効率を考え ビュー表を定義することもある ) ビュー表は subquery を作るのと論理的には同じ実験科目 (X 科目名,X 科目 ID,X 単位数 ) :- 科目 (X 科目名,X 科目 ID,X 単位数 ), 科目実験関係 (X 科目 ID, _). query(x 科目名 ) :- 実験科目 (X 科目名, _, 2). _ は無名変数 : 何でも良い 27 28

8 7.7 商演算 R/S 適用条件 :S の属性 atr(s) は R の属性 atr(r) の部分集合 R/S の属性 : atr(r/s) =atr(r) - atr(s) π atr(r/s) (R) のタプルで全てのSのタプルとの結合がRに属するようなものだけを集める hence, R/S S R R/S は derived operation. (π name (R) S) R もともと R になかったタプルが残る R/S = π name (R) π name (π name (R) S R) 論理的には : name(r) :- r(r,s). ドメイン述語 reject(r):- 補助述語 name(r), dept(s), not r(r,s). query(r):-name(r), not reject(r). R { name dept t1 u1 t1 u2 t1 u3 t2 u1 t2 u3 t3 u2 R/S { name t1 πz(r) S { name dept t1 u1 t1 u2 t1 u3 t2 u1 t2 u2 t2 u3 t3 u1 t3 u2 t3 u3 S { dept u1 u2 u3 (πz(r) S-R)=W { name dept t2 u2 t3 u1 t3 u3 MINUS, EXCEPT がサポートされていれば 関係代数式の部分に対応する view 表 もしくは SELECT 文をもとめ それに対する MINUS で表をappend する 次ページに NOT EXISTS, および GROUP BY を用いたものを示しておく 7.8 否定と集合差 (DDB 版 ) 正しいことの証明 : 証明を作ってみせれば良い証明できないことを示すためにはどうすればよいか 失敗による否定 : 全ての可能な証明パスを試みて ことごとくを導出できないことを実際に確かめる?- 親 ( フネ, フネ ).?- 父 ( フネ, フネ ).?- 親 ( フネ, フネ ).?- 母 ( フネ, フネ ). トップゴールが同一な 2 つの反駁を木の 2 つのパスで図示 成功する場合 : どこかで を葉に持つパスがある 証明木が有限でかつ 葉が全て失敗 ( これ以上展開できない ) なとき not が成功 有限分枝ゆえに, 有限でない 無限長のパス 再帰を許した DDB では 無限パスが存在すれば not も答えが返らない 通常のRDBでは, 階層的なビュー定義しか許さないので, その心配はないが, 否定は全部のデータを なめる ことになるので, インデキシングをしないと一般に重たい 集合差 (DDBにおける定義) 差集合 : r3(x,y) :- r1(x,y), not r2(x,y). 質問 : :-r3(a,b). (a,b) は関係 r3 のタプルか? 導出過程 : :- r3(a,b). :- r1(a,b), not r2(a,b). (a,b) が関係 r1 のタプルとする :- not r2(a,b) 否定処理のため, :- r2(a,b) の証明を試みる :- r2(a,b) (a,b) が r2 のタプルでなければ, 証明できない :- r2(a,b) は失敗し, よって, :- not は r2(a,b) は成功 (a,b) は差集合 r3 のタプル RDB の NOT EXIST クエリーと論理的には同じ操作. RDB では indexing やハッシュにより高速化 総合例題 受講 (st101, lec202, 100). 受講 (st102, lec201, 50). 受講 (st101, lec201, 70). 受講 (st101, lec203, 80). 受講 (st101, lec204, 30). 受講 (st103, lec201, 60). 受講 (st103, lec203, 90). 講義 (lec202, db, 講義 ). 講義 (lec203, kbs, 講義 ). 講義 (lec204, 応数, 演習 ). 講義 (lec201, pl, 講義 ). 学生 (st101, 白鳥, ee_dept). 学生 (st102, 山田, cs_dept). 学生 (st103, 原田, ee_dept). 学生 (st104, 山西, ee_dept). 受講 _name(x 講義名, X 学生氏名 ) :- 講義 (X 講義, X 講義名, _), 学生 (X 学生,X 学生氏名,_), 受講 (X 学生, X 講義, _). 全科目受講 (X 学生氏名 ) :- 学生 (X 学生,X 学生氏名,_), \+ 非受講科目有 (X 学生 ). 非受講科目有 (X 学生 ) :- 講義 (X 講義, _,_), \+ 受講 (X 学生, X 講義,_). \+ : 失敗による否定 ( 証明できなかったとき成功 ) 31 32

9 7.9 否定演算と集合差 (SQL 版 ) CREATE TABLE r (name char(8), dept char(8)); INSERT INTO r VALUES( mh, dept1 ); INSERT INTO r VALUES( jiro, dept1 ); INSERT INTO r VALUES( jiro, dept2 ); create table neg (name char(8)); INSERT INTO neg VALUES( jiro ); SELECT * FROM r WHERE NOT EXISTS // r のレコードに対する条件 ( SELECT * FROM neg WHERE r.name = neg.name ); // 関係名の有効範囲に注意 NOT EXISTS (subquery Q) Qが非空なレコード集合を返す false と評価 otherwise true と評価左記のDDB 版 : r(jiro,dept1). r(mh,dept1). r(jiro,dept1). r(jiro,dept2). neg(jiro). query(name,dept) :- r(name,dept), not neg(name) GROUP BY と集計 CREATE VIEW deptcount(name,count) AS SELECT name,count(*) FROM R GROUP BY name; GROUP BY name は,name ごとに部分表をつくり,count, avg 等の集計操作が適用可能 CREATE view scount(count) AS SELECT count(*) FROM s; SELECT name FROM deptcount, scount WHERE deptcount.count = scount.count; 結局, 全ての dept と R の関係にある name の一覧 R { name mh ichi mh mh dept S { dept cs ss mn mn deptcount { name scount { count 3 mh 3 ichi 1 cs ss mn count レポート課題商演算を実現する SQL 文を NOT EXISTS を用いて与えよ ビュー表を定義しても良い SQL コマンド列の例 CREATE TABLE r (name char(8), dept char(8) ); INSERT INTO r VALUES ( makoto, dept1 ); INSERT INTO r VALUES ( jiro, dept1 ); INSERT INTO r VALUES ( jiro, dept2 ); CREATE VIEW rdom(name) AS SELECT DISTINCT name FROM r ; // 関係 r の第 1 属性への射影 // 重複レコードを除去するため DISTINCT オプション CREATE TABLE s (dept char(8)); INSERT INTO s VALUES ( dept1 ); INSERT INTO s VALUES ( dept2 ); // または,CREATE VIEW s(dept) AS SELECT DISTINCT dept FROM r; CREATE VIEW scount(count) AS SELECT count(*) FROM s; CREATE VIEW deptcount(name,deptcount) AS SELECT name, count(*) FROM r GROUP BY name; CREATE VIEW result(name) AS SELECT name FROM deptcount, scount WHERE deptcount.deptcount = scount.count; SELECT * FROM result; INSERT INTO r VALUES ( makoto, dept2 ); SELECT * FROM result; 35