データベース工学の最近のブログ記事

データベース(SQLの演習)

SQLの説明も大体終わったので、演習を行う。

SQLの演習

学内のアカウントでログインすれば、SQLite3 を使った簡単な演習ができる環境を 準備しているので、それを使って演習。 教科書で示されるデータなども、簡単に読み込めるようにしてある。

演習内容は、SQLでデータを検索する処理を2つ考え動かしてみる。 最低でも、2つのテーブルにまたがった処理であること。 できれば、副問い合わせなどを含んだ処理などにチャレンジすること。 また、そのSQL命令をC言語で記述したものを作り、 直積処理などの意味を考える。

提出は、SQL命令、C言語、説明、動作確認、考察が記載されていること。

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 による直積では、異なる表のすべての組合せを簡単に実現できるが、 1つの表の組合せはどうであろうか?このための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 ;

ビューテーブル

先週が演習課題で、未完成な人も多いので、前半講義、後半演習とする。

ビューテーブル

データベースでプログラムを作る場合、3層スキーマ構成で考えることが多い。 概念スキーマ、内部スキーマ、外部スキーマ。 ビューテーブルは、外部スキーマに相当する。

使い方としては、元々複数のテーブルで扱われているデータを、 あたかも合成したテーブルがあるかのように扱えるもの。

((成績データベースを例に...))
[成2] こういうシンプルな表だと分かりやすい
名前 |科目|担当|点数
Aさん|DB|斉藤| 65
Bさん|電磁|高久| 73
Cさん|DB|斉藤| 45
 
   でも修正不整合などを防ぐには...
[成]
名前 |科ID|点数
Aさん|1000| 65
Bさん|1020| 73
Cさん|1000| 45
 
[担]
科ID|科目|担当
1000|DB|斉藤
1020|電磁|高久

create view 成2 (名前,科目,担当,点数)
    as select 成.名前, 担.科目, 担.担当, 成.点数
         from 成, 担
         where 成.科ID = 担.科ID ;

データベース組込み言語

データベースの処理のプログラムを実際に記述する場合、C言語などで扱う場合は、 特殊なSQL埋め込み用の機能を用いて記述する場合が多い。 select文などを埋め込むと、各要素を処理するためのループ命令が作られ、 カーソルという概念で1件づつの処理を繰り返してくれる。

SQLの埋め込みなどの機能でプログラム言語と整合性の高いものは、 COBOLが一番有名。しかし、COBOL自体は歴史の長い言語であり、 最近の金融系では、COBOLプログラマが定年でプログラムを改良できる技術者不足などが原因の トラブルが多いことも雑談として説明しておく。

授業アンケート結果

恒例の授業アンケートの、プログラミング応用とデータベースが送られてきた。

データベース

例年であれば、80ポイント前後のところが85ポイントとなっており、 他の科目などにも比べ高い評価となった。

興味関心については、90ポイントを越え、学生の興味のある領域を 話せたのかと安堵感。

プログラミング応用

例年であれば、3年は辛めの評価が多いのであるが、その中で約82ポイントは 良い法だと思われる。シラバスへのポイントが低めであるが、 予定通り進んでいることなどをもう少し PR しながら進める必要が あるかもしれない。

インターネットの情報量

最初に、インターネットで扱われる情報が増加しているという点で、 世界中の情報量を説明。Googleが推測するインターネットの情報量は、 2010年度では281EB(エクサバイト10^18,kMGTP*E*ZY)で、この時点で 55倍/6年だったらしい。 約2倍/年とすれば、2013年は、2ZB(ゼタバイト)程かな。

ちなみに人間の脳は、大脳皮質だけで16TB、脳全体と遺伝情報も含めると230TB程らしい。

Webシステムとデータベース

データベースが情報共有のために重要な技術であり、 Webシステムの中での使われ方 ということで、 サーチエンジンの話(設立当初のYahooのユーザ登録型)や、 Google に代表されるクローラ・ロボットによるサーチエンジンの説明を行う。 これに伴い、データは巨大化し、大量のユーザを抱える現在、 データ検索を極めて短い時間で返答することの難しさを説明する。

このため、一般的なWebシステムでは、Webサーバを負荷分散目的で大量に配置し、 その後段にスレーブデータベースが待機し、 その後段にさらにマスターデータベースが 並ぶという3段スキーマ構成の説明などを行う。 また、最近のIT産業では、システム構築からサービス開始までを短期間に行うために、 LAMP (Linux+Apache+MySQL+PHP) といった構成が多いことなども紹介。

さらに、普及しているリレーショナルデータベースシステムの名称として、 Oracle, MySQL, PostgreSQL, SQLite, BerkleyDB などを紹介し、 ネットワーク型、ファイル型 の違いやSQLを使うもの使わない物などがあることも紹介。 最近では、巨大なデータベースを分散システム上に作る必要から、NoSQLなどと呼ばれる 手法も使われている。

データベースが無かったら...

C言語レベルの簡単な演習でデータベースっぽいことをする時は、 fscanf+fprintfだろうけど、大量データを永続的に扱いたいのであれば、 全データ読み込み&全データ出力のプログラムを書くのが簡単。 でも、データ量が増えれば、修正・追加のあった部分だけ書きこむ必要が出てくる。 しかしながら、1行1件のデータであれば1行の長さが変化するとダメ。 こういう場合には、1件のデータ長を固定として、lseek+fwrite+freadを使って ランダムアクセスのプログラムを書くことになる。

しかし、こういうプログラムは1件のデータ長が変化すれば、 プログラムの修正も大変。 さらに、複数の並行処理で書き換えを行えるのであれば、 flockなどを使ったプログラムが 必要となる。

データベース設計の演習

今日は、テストを挟んで説明を行なっていた、データベースの設計の 演習時間とした。レポートで記載すべきテーマは、以下のとおり。

データベース設計演習

卒業研究やそれに伴う実験などのデータを、データベースで取り扱う場合の データベースの設計を行う。トップダウン・ボトムアップの2つの方法で行い、 そのデータベースが正規形を満たしていることが分かるようなER図を記載し 考察を行う。

  • トップダウン設計を行う場合の、実体・属性・関連の抽出が分かるように、 対象となる卒研や実験データの説明を記載し、実体・属性・関連を示す。 抽出の経緯などが分かるような説明を伴うこと。
  • ボトムアップ設計を行う場合の、既存の帳票からの設計過程が分かるように、 表を正規形を満たすようにグループ化などを行った経過。
  • その結果を、第2正規形・第3正規形を満たすことを、確認するような、 ER図。ボトムアップ設計とトップダウン設計での違いなどがあれば、 それをまとめた経緯などの説明を含むこと。
  • 最終的な考察として、出来上がったデータベースの評価として、 正規形などの確認を含め説明を行うこと。 例えば、自分の卒研のプログラムでの設計と違いが発生したら、どういう理由であったとか、当初イメージしていたデータベースとの違いとか、実際にプログラムで動かす場合は、効率などを考えると、こう修正したほうが良い....とか...

レポート提出期限は、期末試験前までとする。 ただし、直前は卒研発表などで、忙しいことが予想されるので、 本日の演習時間および冬休み期間中に取り組むこと。

データベース授業は演習

本校OBの北野くんが採用などの話にて来校してくれたため、急遽OB講座をお願いした。 データベースの授業は早めに進んでいる一方、レポート課題提出も少ないため 演習の時間とした。

SQLの命令の説明 part2 と、残り時間は課題のための演習時間とする。

SQL説明2

SQLの説明の残りということで、WHERE節でかける特殊な条件や、 副問い合わせなどについて説明する。

値 IN 集合
 例:SELECT S.業者名, S.所在 FROM S
    WHERE S.業者番号 IN
     (SELECT SG.業者番号
      FROM SG
      WHERE SG.商品番号 = 'G2' AND SG.在庫量 >= 200) ;

文字列 LIKE パターン
 例: SELECT S.業者名 FROM S WHERE S.業者名 LIKE 'AB%' ;
    ワイルドカード文字: % 任意の文字 , _ 1文字

値 BETWEEN 値 AND 値
 例: SELECT G.価格 FROM G G.価格 BETWEEN 100 AND 200 ;

上記の IN の例に示すように、SQLの中の() に、別のSQL命令を記述できる。 この場合は、()の中のSQLを予め実行し、その結果の中に S.業者番号 が含まれれば、 WHERE節が成立する。このような処理が副問い合わせである。

実際には、もう少し複雑な"相関副問い合わせ"というものもあり、

 SELECT G.商品名, G.色, G.価格 FROM G
  WHERE 'S4' IN
   (SELECT SG.業者名 FROM SG
    WHERE SG.商品番号 = G.商品番号 ) ;

上記の副問い合わせの中には、全体の問い合わせのテーブルGが含まれており、 このような副問い合わせでは、()の中を先に実行しておくことはできない。 このため、Gのテーブルについて1件づつG.商品番号を代入して()の中を実行し、 その結果で WHERE 'S4' IN (...) が成立するかチェックされる。 このような問い合わせは、相関副問い合わせと呼ばれる。

注意:この授業で使う実験環境は、SQLiteであり、相関副問い合わせは使えない。

課題用資料

2015年12月

    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31    

アーカイブ

Google

このアーカイブについて

このページには、過去に書かれたブログ記事のうちデータベース工学カテゴリに属しているものが含まれています。

前のカテゴリはシステム設計です。

次のカテゴリはプログラム応用です。

最近のコンテンツはインデックスページで見られます。過去に書かれたものはアーカイブのページで見られます。