viewテーブルとデータベース設計
実テーブルとビューテーブル
データベースでの表の構造は、テーブルの集合で表され、これは概念スキーマに相当する。
しかし、プログラマーの視点では、複数の表から構成されているといった所を気にせずプログラムを作れると便利。こういう使い勝手のよいテーブルは、外部スキーマに相当する。この外部スキーマを定義するものが、create view 命令である。
-- 優良業者しか見えないテーブル -- create view 優良業者 ( 業者番号, 優良度, 所在 ) as select S.業者番号, S.優良度, S.所在 from S where S.優良度 > 15 ; -- 串刺し結果で参照できるテーブル -- create view 業者在庫 ( 業者名, 在庫量 ) as select S.業者名, SG.在庫量 from S, SG where S.業者番号 = SG.業者番号 ; -- 外部スキーマで参照 -- select 業者在庫.業者名, 業者在庫.在庫量 from 業者在庫 ;
このように外部スキーマを設けることで、SQLをシンプルに記述できたり、外部プログラマが余計な内容を見れないようにしたりすることができる。ただし、データベースシステムによっては読み出しだけだったり、ビューテーブルを更新する時に更新異常が発生する場合がある。 (さらに…)
SQLの基礎とデータ定義文
前回のデータベースの説明で、最も中心的な機能のSQLについて説明を行ったが、その続き。
SQLの基礎
データベースでは、記録されているデータの読み書きは、SQLという言語を用いる。 SQLでは、射影・結合・選択を表す処理で構成される。
SELECT S.業者番号 -- 必要とされるデータを抽出する射影 --
FROM S -- 複数のテーブルを組合せる結合 --
WHERE S.優良度 >= 20 ; -- 対象となるデータを選び出す選択 --
// C言語なら
for( int i = 0 ; i < sizeofarray( S ) ; i++ ) {
if ( S[i].優良度 >= 20 )
printf( "%d¥n" , S[i].業者番号 ) ;
}
Sは、テーブル名であり、文脈上対象テーブルが明らかな場合、フィールド名の前の テーブルは省略可能である。
SELECT 業者番号 FROM S WHERE 優良度 >= 20 ;
ここで、SQLの最も便利な機能は、直積による結合処理。2つの表を組み合わせる処理。
PERSON 個人の情報 ((C言語の構造体をイメージ))
id | name | age | work | struct PERSON {
----+---------+-----+--------- | int id ;
101 | tsaitoh | 52 | teacher | char name[10] ;
102 | tomoko | 41 | worker | int age ;
203 | mitsuki | 18 | student | char work[10] ;
| } table_person[3] ;
GRAD 卒業した学校
id | graduate # idは外部キー
----+----------- | struct GRAD {
203 | manyo-jh | int id ;
102 | fukui-nct | char graduate[10] ;
101 | univ-fukui | } table_grad[3] ;
※ ER図的に言うと、実体(PERSON),関係(GRAD) の他に実体(SCHOOL)があるべき
データベースとガイダンス
今日が後期の選択科目「データベース」の第一回目ということで、シラバス説明＆ガイダンスをしてからぁ〜のぉ〜、概要説明。
インターネットの情報量
インターネット上の情報量の話として、2010年度に281EB(エクサバイト)参考:kMGTPEZYの情報があるらしい。また 2013年度で、1.2 ZB(ゼタバイト)という情報があった。これらをムーアの法則(2年で2倍)の概算に照らし合わせても、それなりに近い。2017年であれば、約4年で、5 ZBにはなっているかな。
そして、これらの情報をGoogleなどで探す場合、すぐにそれなりに情報をみつけてくれる。これらは、どの様に実装されているのか？
Webシステムとデータベース
まず、指定したキーワードの情報を見つけてくれるものとして、検索システムがあるが、このデータベースはどのようにできているのか？
Web創成期の頃であれば、Yahooがディレクトリ型の検索システムを構築した。(ページ作者がキーワードとURLを登録する方式) しかし、ディレクトリ型では、自分が考えたキーワードではページが見つからないことが多い。
そこで、GoogleはWebロボット(クローラー)による検索システムを構築した。Webロボットは、定期的に登録されているURLをアクセスし、そのページ内の単語を分割しURLと共にデータベースに追加する。さらに、ページ内にURLが含まれていると、そのURLの先で、同様の処理を再帰的に繰り返す。 (さらに…)
データベースの課題ER図の作成
課題
「卒業研究で扱うデータをER図で表現する」 実験系のテーマの人は、実験結果をデータベースに保存することを 想定してER図を作成すること。 どうしても、ER図で表現しづらいテーマの人は、身の回りのデータ をデータベースで扱うことをテーマにしてもいい。
内容
データベースを、トップダウンで設計した場合のER図、 ボトムアップで設計した場合のER図を作成すること。
提出物に記載すべき内容
- トップダウンで設計する場合の、要求仕様に相当するもの
- それから抽出した実体・関連・属性などの説明
- そこから作成したER図
- 正規形の条件を満たすために書き換えたER図の説明、および結果の図が正規形
の条件を満たしていることの確認
- ボトムアップ設計する場合の、もともとのデータ(帳票)に相当するもの
- その説明
- レベル分けやグループ分けをして作成したER図
- 正規形の条件を満たすために書き換えたER図の説明、および結果の図が正規形
の条件を満たしていることの確認
- 2つのER図を見比べた場合の考察
SQLと串刺し処理と副問い合わせ
SQLの基本命令selectの使い方を説明したけど、 複数の表を組合せた処理の記述の説明が不十分だったので、 串刺しに相当する処理を説明
SQLの直積と串刺し処理
データベースの処理の例として、学生と科目の成績表を使って説明。
まずは、以下の様な表データがあったとする。
User(学生のテーブル) uid | name | ... -----+------+------ 1000 | 斉藤 | ... 1001 | 青山 | ... 1002 | 坂本 | ... Subject(科目名のテーブル) sid | name | 単位... -----+------+------ 2000 | データベース ... 2001 | 数学 ... 2002 | 情報構造論 Result(成績表) uid | sid | point -----+------+----- 1000 | 2000 | 75 1001 | 2000 | 80 1002 | 2001 | 90
このデータの中から、80点以上の情報が欲しければ、SQL や C言語で書くなら、 以下のようになるであろう。
(( SQL )) select Result.point from Result where Result.point >= 80 ; (( C言語 )) for( i = 0 ; < Result.length ; i++ ) if ( Result[ i ].point >= 80 ) printf( "%d¥n" , Result[ i ].point ) ;
しかし、点数ではなく、氏名や科目名が欲しいのなら、C言語なら以下の様な処理を記述することになる。
// C言語で複数の表を串刺し
for( i = 0 ; i < Result.length ; i++ )
if ( Result[ i ].point >= 80 ) {
// 対応する名前を探す
for( j = 0 ; j < User.length ; j++ )
if ( User[j].uid == Result[i].uid )
break ;
// 対応する科目名を探す
for( k = 0 ; k < Subject.length ; k++ )
if ( Subject[k].sid == Result[i].sid )
break ;
printf( "%s %s¥n" , User[j].name , Subject[k].name ) ;
}
この場合、if文の処理回数は、Result.length回 + (1/2)*User.length回 + (1/2)*Subject.length回となるであろう。 (1/2)は、単純検索の平均回数の意味。
この処理を SQL で行う場合は、以下のようになるであろう。
(( SQLでの串刺し ))
select User.name , Subject.name
from User , Subject , Result
where User.uid = Result.uid
and Subject.sid = Result.sid
and Result.point >= 80 ;
-- 2つのand節が重要 --
この問い合わせ文では、"from User,Subject,Result"が直積で、すべての組合せを行うという イメージで捉えると、以下の様なC言語のイメージを持つであろう。
for( i = 0 ; i < Result.length ; i++ )
for( j = 0 ; j < User.length ; j++ )
for( k = 0 ; k < Subject.length ; k++ )
if ( User[j].uid == Result[i].uid
&& Subject[k].sid == Result[i].sid
&& Result[i].point >= 80 ) {
printf( "%s %s¥n",User[j].name,Subject[k].name ) ;
}
このプログラムでは、ループ回数は、Result.length * User.length * Subject.length であり、 これだけをみると、処理効率が悪いように見える。 しかし、データベースシステムは、UserやSubjectの列の検索で、uid,sidの値から、 ハッシュ法やB木を使って探すかもしれない。 そうすれば、串刺し検索の処理も必ずしも効率が悪いとは言えないし、 そういった部分はデータベースシステムに任せ、その他のプログラムを効率よく書けば生産性が高いはず。
where節で使える特殊な比較命令
where節で使える比較命令には、大小比較やand,or 以外にも、以下の様な条件も記述できる。
((集合計算)) where 式 in ( 値1,値2, ... ) ; ((範囲計算)) where 式 between 値1 and 値2 ; ((空判定)) where 式 is null ; ((文字列パターンマッチ)) where 文字列 like 'c%t' ; _ 1文字の任意文字 c_t = cat ◯ , chart × % 0文字以上の任意文字 c%t = cat ◯ , chart ◯
自己結合
from による直積では、異なる表のすべての組合せを簡単に実現できるが、 １つの表の組合せはどうであろうか？このためのfrom節の書き方に自己結合がある。
例えば、前例のUserの中で、同姓同名の人がいるかどうかを探したい場合は、自己結合で以下のように記述する。
select U1.name
from U1 User , U2 User
where U1.name = U2.name
and U1.uid <> U2.uid ;
副問い合わせ命令
SQLの問い合わせで便利なのが副問い合わせ命令であろう。 特殊な相関副問い合わせを考えないのであれば、 () の中に記述された select 文を先に実行すると考えればいいであろう。
(( 斉藤の80点以上を出力 ))
select User.name
from User
where User.uid in ( select Result.uid
from Result
where Result.point >= 80 )
この副問い合わせでは、() の中の問い合わせを先に実行すると、1001,1002 が求まり、 where User.uid in ( 1001 , 1002 ) という条件となり、最終的にその名前が求まる。
SQLの最初
関係データベースの導入説明が終わったので、実際のSQLの説明。
create user
データベースを扱う際の create user 文は、DDL(Data Definition Language)で行う。
CREATE USER ユーザ名 IDENTIFIED BY "パスワード"
grant
テーブルに対する権限を与える命令。
GRANT システム権限 TO ユーザ名 データベースシステム全体に関わる権限をユーザに与える。 (例) GRANT execute ON admin.my_package TO saitoh GRANT オブジェクト権限 ON オブジェクト名 TO ユーザ名 作られたテーブルなどのオブジェクトに関する権限を与える。 (例) GRANT select,update,delete,insert ON admin.my_table TO saitoh REVOKE オブジェクト権限 ON オブジェクト名 TO ユーザ名 オブジェクトへの権限を剥奪する。
create table
実際にテーブルを宣言する命令。構造体の宣言みたいなものと捉えると分かりやすい。
CREATE TABLE テーブル名 ( 要素名1 型 , 要素名2 型 ... ) ; PRIMARY KEY 制約 型の後ろに"PRIMARY KEY"をつける、 もしくは、要素列の最後に、PRIMARY KEY(要素名,...)をつける。 これによりKEYに指定した物は、重複した値を格納できない。 型には、以下の様なものがある。(Oracle) CHAR( size) : 固定長文字列 / NCHAR国際文字 VARCHAR2( size ) : 可変長文字列 / NVARCHAR2... NUMBER(桁) :指定 桁数を扱える数 BINARY_FLOAT / BINARY_DOUBLE : 浮動小数点(float / double) DATE : 日付(年月日時分秒) SQLiteでの型 INTEGER : int型 REAL : float/double型 TEXT : 可変長文字列型 BLOB : 大きいバイナリデータ DROP TABLE テーブル名 テーブルを削除する命令
insert,update,delete
指定したテーブルに新しいデータを登録,更新,削除する命令
INSERT INTO テーブル名 ( 要素名,... ) VALUES ( 値,... ) ; 要素に対応する値をそれぞれ代入する。 UPDATE テーブル名 SET 要素名=値 WHERE 条件 指定した条件の列の値を更新する。 DELETE FROM テーブル名 WHERE 条件 指定した条件の列を削除する。
select
データ問い合わせは、select文を用いる、 select文は、(1)必要なカラムを指定する射影、(2)指定条件にあうレコードを指定する選択、 (3)複数のテーブルの直積を処理する結合から構成される。
SELECT 射影 FROM 結合 WHERE 選択 (例) SELECT S.業者番号 FROM S WHERE S.優良度 > 30 ;
データベース・ガイダンス
今日が後期の選択科目「データベース」の第一回目ということで、 シラバス配布＆ガイダンスをしてからぁ〜のぉ〜、概要説明。
インターネットの情報量
インターネット上の情報量の話として、2010年度に281EB(エクサバイト) 参考:kMGTPEZYで、今日改めて探したら、2013年度で、1.2 ZB(ゼタバイト) という情報があった。ムーアの法則2年で2倍の概算にも、それなりに近い。 今年2015年であれば、約2年で、2 ZBにはなっているかな。
そして、これらの情報をGoogleなどで探す場合、すぐにそれなりに情報を みつけてくれる。これらは、どの様に実装されているのか？
Webシステムとデータベース
まず、指定したキーワードの情報を見つけてくれるものとして、 検索システムがあるが、このデータベースはどのようにできているのか？
Web創成期の頃であれば、Yahooがディレクトリ型の検索システムを構築 してくれている。(ページ作者がキーワードとURLを登録する方式) しかし、ディレクトリ型では、自分が考えたキーワードではページが 見つからないことが多い。
そこで、GoogleはWebロボット(クローラー)による検索システムを構築した。 Webロボットは、定期的に登録されているURLをアクセスし、 そのページ内の単語を分割しURLと共にデータベースに追加する。 さらに、ページ内にURLが含まれていると、そのURLの先で、 同様の処理を再帰的に繰り返す。
これにより、巨大なデータベースが構築されているが、これを少ない コンピュータで実現すると、処理速度が足りず、3秒ルール/5秒ルール (Web利用者は次のページ表示が3秒を越えると、次に閲覧してくれない) これを処理するには負荷分散が重要となる。
一般的に、Webシステムを構築する場合には、 1段:Webサーバ、2段:動的ページ言語、3段:データベースとなる場合も 多い。この場合、OS=Linux,Web=Apache,DB=MySQL,言語=PHPの組合せで、 LAMP構成とする場合も多い。
一方で、大量のデータを処理するDBでは、 フロントエンドDB,スレーブDB,マスタDBの3段スキーマ構成となることも多い。
データベースシステム
データベースには、ファイル内のデータを扱うためのライブラリの、 BerkleyDBといった場合もあるが、複雑なデータの問い合わせを実現する 場合には、リレーショナル・データベース(RDB)を用いる。 RDBでは、データをすべて表形式であらわし、SQLというデータベース 問い合わせ言語でデータを扱う。 また、問い合わせは、ネットワーク越しに実現可能であり、こういった RDBで有名なものとして、Oracle , MySQL , PostgreSQL などがある。 単一コンピュータ内でのデータベースには、SQLite などがある。
しかし、RDBでは複雑なデータの問い合わせはできるが、 大量のデータ処理のシステムでは、フロントエンドDB,スレーブDB,マスタDB の同期が問題となる。この複雑さへの対応として、最近は NoSQL が 注目されている。
データベースが無かったら
これらのデータベースが無かったら、どのようなプログラムを作る 必要があるのか？
情報構造論ではC言語でデータベースっぽいことをしていたが、 大量のデータを永続的に扱うのであれば、ファイルへのデータの読み書き 修正ができるプログラムが必要となる。
こういったデータをファイルで扱う場合には、1件のデータ長が途中で 変化すると、N番目のデータは何処？といった現象が発生する。 このため、簡単なデータベースを自力で書くには、1件あたりのデータ量を 固定し、lseek() , fwrite() , fread() などの 関数でランダムアクセスのプログラムを書く必要がある。
また、データの読み書きが複数同時発生する場合には、排他処理も 重要となる。例えば、銀行での預け金10万の時、3万入金と、2万引落としが 同時に発生したらどうなるか？ 最悪なケースでは、 (1)入金処理で、残金10万を読み出し、 (2)引落し処理で、残金10万を読み出し、 (3)入金処理で10万に+3万で、13万円を書き込み、 (4)引落し処理で、残金10万-2万で、8万円を書き込み。 で、本来なら11万になるべき結果が、8万になるかもしれない。
さらに、コンピュータといってもハードディスクの故障などは発生する。 障害が発生してもデータの一貫性を保つためには、バックアップや 障害対応が重要となる。
SQLite3でデータベース
卒研でデータベースを使いたい人もいるようだけど、 MySQLとかまで完璧なのが必要なければ、SQLite を使ったほうが楽。 ただし、データ型は実質すべてtext型になるけど、簡単なアプリベースなら 支障はないはず。
<?php
// データ保存用の sqlite-data はあらかじめ作っておく。
// sqlite-data の書き込み許可
// (手抜) $ chmod 777 sqlite-data
// (厳密) # chgrp sqlite-data
// # chmod 774 sqlite-data
// sqlite-data/.htaccess には"Require all denied"を書いて
// ディレクトリ内をWeb的にアクセス禁止にする。
// サーバのPHPを使うと、エラーが見つからず苦労するかも
// その時は、.htaccess ファイルに、以下の設定を記載しておく
// "php_flag display_errors On"
// 説明しやすいように実行だけ関数をつくる
function exec_command( $db , $cmd ) {
if ( ($db->exec( $cmd )) === FALSE ) {
print $db->lastErrorMsg() ;
}
}
// データベースを作って初期データを登録
function table_initialize( $db ) {
exec_command( $db ,
"create table Person(name text,phone text) ;" ) ;
exec_command( $db ,
"insert into Person (name,phone) values('t-saitoh','272925');" ) ;
exec_command( $db ,
"insert into Person (name,phone) values('tomoko' ,'123456');" ) ;
exec_command( $db ,
"insert into Person (name,phone) values('mitsuki' ,'234567');" ) ;
}
// データベースを作る
if ( !file_exists( "./sqlite-data/sample.db" ) ) {
// なにも無い状態
$db = new SQLite3( "./sqlite-data/sample.db" ) ;
table_initialize( $db ) ;
} else {
// すでに作られている場合
$db = new SQLite3( "./sqlite-data/sample.db" ) ;
}
?>
<html>
<head>
</head>
<body>
<pre>
<?php
// 登録されているデータを全部表示
if ( ($query = $db->query( "select * from Person" )) !== FALSE ) {
while( $res = $query->fetchArray( SQLITE3_NUM ) ) {
printf( "| %-10s | %-10s |\n" , $res[0] , $res[1] ) ;
}
}
?>
</pre>
</body>
</html>
データベースの用語など
データベースの２回目ということで、データベースの形式などの説明のあと、 データベースの数学的な表記方法などを示す。
データベースの基本
データベースを利用する際、プログラマにしてみれば、SQLというデータ問い合わせ言語を 用いれば、簡単にデータを扱える。 これにより、応用プログラマにしてみれば、データをプログラムから分離ができ、 実際の内部の構造を知ることなく独立性が得られる。 また、データの一貫性を保つことは、データベースを利用しないと大変であるが、 データベースでは(a)正当性確認,(b)同時実行制御,(c)障害回復などを行ってくれるので、 一貫性のあるデータ管理が容易となる。 一般的には、データベースのACID特性として(a)atomicity原子性,(c)consistency一貫性,(i)isolation独立性,(d)dulability耐久性といった特性が求められる。
データベースに対する視点として、エンドユーザからの要求を応用プログラマが処理する場合、 データベースの一部分だけであったり、複数のデータベースを大きな単独の表で扱えると 便利であったりする。こういう応用プログラマの視点を外部スキーマと呼ぶ。 しかし実際には、複数の表からなるデータベースでは更新なども容易な(正規化された) 表で あるほうが良い。このような視点は、概念スキーマと呼ばれる。 さらにデータベースの内部では、このデータを高速に処理するための インデックスが付加されたり、実際のファイル上に記録されるデータ形式がとられる。 これらは内部スキーマと呼ばれる。 この利用者の視点に応じたスキーマ構成を、３層スキーマ・アーキテクチャと呼ぶ。
これらのデータを扱う際、木構造で表現できるようなものや、さらにそれらが複雑に からみあったネットワーク構造などは、自由にデータを表現できる一方、それらの データを扱うシステムは複雑化してしまう。 そこで、IBMのコッド博士の提唱した、データを簡単な表構造の 組み合わせで表す関係データベースは、処理の簡潔さもあって広く普及している。
データベースの表現
具体的なデータを組み合わせたデータベースは数学的に以下のように表現する。 データの集合A={s,t,u},B={p,q}とした場合、 直積A×Bとは、A×B={(x,y)|x∈A,y∈B}で表される。 例をあげると、AとBの全ての組み合わせであり、 A×B={(s,q),(t,q),(u,q),(s,q),(t,q),(u,q)} となる。 関係とは、直積の部分集合 R(A,B)⊂A×B であり、すべての組み合わせのうち、 実際に存在するものといえる。