Database Lecture 6/3

前回の復習
• データベースの設計

• 選択、射影、結合

宿題
• 商品名、卸先名、卸値、仕入値、仕入先
• 学生名、学籍番号、科目、担当教官、担当教官番号、成績
• 学生名(ID)、所属学科名、学生室名、研究課題、先生名(ID)、先生室名
問題は、来週までに、 kono@ie.u-ryukyu.ac.jp まで、[database lecture 6/3] というsubjectを付けたメール で送ること。

関係代数と関係論理

RDBの表に対する演算(選択、射影、結合)は、関係代数と呼ばれる。

一方、データベースに対する質問は、言葉で表現されるのであり、 それは、一般的に論理式で表されると考えられる。しかし、どんな質問にも 答えられるというわけにはいかない(ex. 神は存在するか?)

RDBに対する操作=関係代数は、RDBの質問に答えられる能力を表しており、 その能力に対応する論理体系のサブセット(部分体系)が存在する。それは 関係論理と呼ばれている。逆に言えば、RDBは関係論理で表される質問には すべて答えることができる。

関係論理は、以下の要素からなる

• 定数 c,d,e

• あるリレーション(表)Rのタプルs,t,r ...

• あるリレーション(表)Rの属性A,B,C ...

• タプルの属性値 s[A]

• 定数やタプルの属性値間の比較演算式P,Q,R ...( <, = , > )

• タプルsがリレーションRに属することを表す式 R(s)

• 式の論理演算(AND, OR, NOT)

• 式に対する量化 (exists, for all)

質問は、関係論理式によって表現される。そして、質問の答は、 関係論理式によって表された集合によって表される。 例(1)
{t | 社員(t) AND t[所属]='経理' AND t[給与]> 50 }
ここでは、社員というリレーションが、所属と給与という属性を持っている としている。

例えば、データベースに時刻に関する情報を入れた時に、関係代数や関係論理 をどのように拡張すれば良いのか、その時の論理と代数の等価性は? などが一時期、活発に研究された。

データベースへの質問

データベースに対する質問は、関係論理によって表すのが自然である。現在は、 以下のような質問の方法がある。
• QBE (Query by Example) レコードを表示して穴埋め式に部分的に値を指定する
• Datalog 関係論理を直接に表すことのできる論理型言語Prolog言語に似た表現
• SQL (Structured Query Language) IBMが開発したCOBOLに似た言語
QBEはカード型データベースで良く使われる形式だがプログラムなどから 使うのには向かない。QBEを関係論理に直す作業は自明である。教科書に あるクエリー図は、QBEに近い表現である。

Datalog は理解しやすい構文を持っているが広く使われているとはいいがたい。

例(2)
{t | 社員(t) AND t[所属]='経理' AND t[給与]> 50 }
はDatalogでは、社員(所属、給与) という述語を仮定して、
?- 社員('経理',X), X> 50.
と表すことができる。

SQL は、時代遅れの構文、中途半端な意味を持っているが、IBMの 威光もあり、現在は標準的なデータベース言語になっている。デー タベースシステムによって独自な拡張がされているのが普通なので 気を付けよう。stored procedure などプログラミング言語として 強力な拡張がおこなわれている場合も多い。

• 参考文献 標準SQL[改訂第2版] トッパン

  例(3)
  {t | 社員(t) AND t[所属]='経理' AND t[給与]>50 }
  はSQLでは、
  select * from 社員
  where 所属='経理' and 給与> 50 と表すことができる。

SQL (Structured Query Language)

SQLの言語は以下のような部分に分けることができる。
• スキーマ定義言語(SDL) create, grant

• スキーマ操作言語(SML) drop,add,alter,revoke

• データ操作言語(DML) select, insert, fetch, update, delete, declare, open, close, commit, rollback

データベース設計者、メンテナンスをする人が最初の二つを使う。通常の ユーザはDMLを使うことになる。

基本的な流れ

• データベースを作る (create database)

• 表を作る (create table)

• データを入れる (insert table)

• 検索をおこなう (select * from)

ここではMini SQLというオーストラリアのBond Universityで、 David J. Hughes という人が作ったSQLのサブセットを使って見よう。 もともとはネットワーク管理のためのデータベースであったらしい。

Mini SQL

Mini SQL はclinet/server型のデータベースである。msqldというサーバ(daemon) を立ち上げ、それにmsqlというクライアントが接続する。サーバのホスト名は msql -h bw03 databse というように指定する。 プログラムは bw03:/usr/open/Minerva にあり、簡単なドキュメントがPostScript ファイルでbw03:/usr/open/Minerva/doc/mSQL-1.0.psにある。(もちろん英語 である) データベースへのアクセスは、 bw03:/usr/open/Minerva/msql.acl に記述されている。データベースの作成は最初に決めた人しかおこなう ことはできないが、その他の操作はクライアント側から可能である。 テーブルの作り方

create table test (
        name    char(10) primary key,
        age     int,
        phone   char(20)
)
\p\g

create table test2 (
        user    char(10) primary key,
        name    char(30)
) \p\g

\p が質問の表示、\gが質問の実行をおこなう。残念ながら漢字は 属性名やテーブル名には使えないようだ。

データの入力

insert into test ( name, age, phone)
        values ( 'Bambi', 26, '(075) 951450')
\p\g
insert into test ( name, age, phone)
        values ( 'paulp', 25, '(075) 951412')
\p\g
insert into test2 (user, name) values ('paulp','Paul Pyyvaara') \p\g
insert into test2 (user, name) values ('Bambi','David J. Hughes') \p\g

漢字もそのまま使える(EUCコードならね)。 簡単な検索

select phone from test where name = 'Bambi'
\p\g

select * from test \p\g

select * from test where phone = '(075) 951412'
\p\g

select * from test where name like '%am%'
\p\g

結合は二つの表からの検索をおこなえば良い。

select test2.name, test.age
        from test2,test
        where test2.user = test.name
\p\g

Mini SQLの制限
• 属性の型は、char(len), int, real しかない
• 演算は < , > ,= , =< , => , < > , like
• table名、属性名には漢字は使えない
• primary key はただ一つ
• not null をcreate table時に指定することができる
• open, rollback などはない
• selectのネストはできない
• AIX上ではorder by, updateがバグッているようです ...

問題

先週の問題
• 学生名、学籍番号、科目、担当教官、担当教官番号、成績

  で設計したデータベースを作成するSQL文を作れ。属性名、テーブル名は 英語に直すこと。

  そのデータベースから、田中先生の教えている 数学の科目の学生成績一覧を作るSQLを作成せよ。