CTF問題とセキュリティ(4年実験)
この実験では、セキュリティコンテストのCTF問題(Capture The Flag競技)について、インターネットの仕組みを理解し、その問題の解き方を考え、新しく自分自身でCTF問題を作ってもらいます。
日程
実験は、4週にわたり、以下の日程で行います。
週 | 内容 |
1 | (前半)暗号・ファイル・Web |
2 | |
3 | (後半) プログラム作成・インターネット・OS |
4 |
前半・後半でそれぞれ、問題例(もしくは自分で見つけたCTF問題)の1つの説明と、自作の問題を新しく作り説明をしてください。
提出物
- 実験の目的
- 問題例(or 自分で見つけたCTF問題)を1つ選び
- 前半・後半それぞれについて
- その問題が情報セキュリティにどう関係しているのか
- 問題の解き方のしくみと解説
- 自作問題について
- 前半・後半それぞれ
- その問題が情報セキュリティにどう関係しているのか
- 問題の解き方、問題の作り方
- しくみと解説
- 提出先はこちらのTeams共有フォルダに。
- 自作問題の例
- サブリミナル効果で隠されたGIF
- 電子透かし
- 学校からアクセスする時だけ、答えが見える。(REMOTE_HOST)
- スマホでアクセスした時だけ、答えが見える。(HTTP_USER_AGENT)
- Google 検索した後だけ、答えが見える。(HTTP_REFERER)
専攻科実験・コンパイラと関数電卓プログラム作成
- コンパイラの技術と関数電卓プログラム(1)
- 再帰下降パーサによる構文解析(LL法)による電卓プログラム作成
- 補助資料:コンパイラの技術と関数電卓プログラム(1-2)
- 課題
- 複数桁の数字が使えること。
- 式中に空白が使えること。
- 何らかの演算子を追加すること。
- (例) %,単項演算子のマイナスなど
- 演算子が左結合か右結合か確認すること。
- オプション課題
- 変数が使えること。
(変数名は1文字のA-Zといったもので良い)
- 変数が使えること。
- レポート内容
- コンパイラ技術の概要、課題(1)の説明・最終的なBNF記法・ソース・動作検証、考察
- コンパイラの技術と関数電卓プログラム(2)
- コンパイラツールを使ったLR構文解析による電卓プログラムの作成
- 補助資料:専攻科実験: unix系 開発環境のインストール
- 課題
- 基本的に、lex+yaccで(1)と同様の課題で参考資料を元に改良を行う。
- レポート内容
- lex,yaccの概要、課題(2)の説明・ソース・動作検証、考察
AVL木と2分ヒープ
2分探索木へのデータ追加と不均一な木の成長
先週の講義で説明していた、entry() では、データを追加すべき末端を探し、追加する処理であった。
しかし、前回のプログラムで、以下のような順序でデータを与えたら、どのような木が出来上がるであろうか?
- 86, 53, 11 – 降順のデータ
- 12, 24, 42 – 昇順のデータ
この順序でデータが与えられると、以下のような木が出来上がってしまう。このような木では、データを探しても1回の比較でもデータ件数が1つ減るだけで、O(N)となってしまう。通常のデタラメな順序でデータが与えられれば、木はほぼ左右均等に成長するはずである。
AVL木
このような、不均一な木が出来上がっても、ポインタの繋ぎ変えで検索回数を改善できる。例えば、以下のような木では、赤の左側に偏っている。
このような場合でも、最初、青の状態であっても、不均一な部分で赤のようなポインタの繋ぎ変えを行えば、木の段数を均一に近づけることができる。この例では、11,65,92の木が、右回転して 11 の木の位置が上がっている。(右回転)
この様に、左右の枝の大きさが不均一な場所を見つけ、右回転や左回転を行う処理を繰り返すことで、段数が均一な2分探索木に修正ができる。この様な処理でバランスの良い木に修正された木は、AVL木と呼ばれる。
理解確認
- 木の根からの段数を求める関数 tree_depth() を作成せよ。
例えば、上のAVL木の説明の図であれば、4段なので4を返すこと。
// 木の段数を数える関数 _____ tree_depth( _______________ p ) { if ( p == NULL ) { return _____ ; } else { int d_L = ______________ ; int d_R = ______________ ; if ( d_L > d_R ) return _____ ; else return _____ : } } // pをつなぎ替え上部を返り値で返す。 struct Tree*rot_right( struct Tree* p ) { struct Tree* pl = p->left ; struct Tree* pr = pl->right ; pl->right = p ; p->left = = pr ; return pl ; } int main() { printf( "%d¥n" , tree_depth( top ) ) ; top = rot_right( top ) ; return 0 ; }
2分ヒープ(binary heap)
2分探索木では、1つのノードにつき2つのポインタを持ち、データ1件あたりのメモリの使用量が多い。通常の「配列の先頭から昇順にデータを並べる2分探索法」では、途中にデータを挿入する場合、データを後ろにずらす必要があるため、O(N)の処理時間を要する。
これらの問題の解決法の1つとして、2分ヒープがある。左右に均一に成長している2分探索木で、上から番号を以下の様に振ると、i番目のデータの左の枝は 2×i+1 番目、右の枝は 2×i+2 番目であることが判る。
このような順序で配列にデータを保存する方法が2分ヒープである。この方式ならアルゴリズムの説明は省略するが、O(log(N))で挿入が可能となる。
int a[ 7 ] = { 53 , 11 , 86 , 10 , 22 , 65 , 92 } ; // 2分ヒープを表示 void print_heap( int array[] , int idx , int size ) { if ( idx < size ) { // 左の枝を表示 print_heap( array , 2*idx + 1 , size ) ; // 真ん中の枝を表示 printf( "%d " , array[ idx ] ) ; // 右の枝を表示 print_heap( array , 2*idx + 2 , size ) ; } } // 2分ヒープから key を検索 int find_heap( int array[] , int idx , int size , int key ) { while( idx < size ) { if ( array[ idx ] == key ) return idx ; // 見つかったら配列の番号を返す else if ( array[ idx ] _____ key ) // 何が入るか考えよう idx = ________________ ; else idx = ________________ ; } return -1 ; // 見つからなかったら、-1 を返す } int main() { print_heap( a , 0 , 7 ) ; if ( find_heap( a , 0 , 7 , 65 ) >= 0 ) printf( "Find!!¥n" ) ; return 0 ; }
レポート課題
以下のようなデータを扱う2分探索木のプログラムを作成せよ。以下の箇条書き番号の中から、(出席番号 % 3+1)のデータについてプログラムを作ること。
- 名前(name)と電話番号(phone)
- 名前(name)と誕生日(year,mon,day)
- 名前(name)とメールアドレス(mail)
プログラムは以下の機能を持つこと。
- 1行1件でデータを入力し、2分木に追加できること。
- 全データを昇順(or降順)で表示できること。
- 検索条件を入力し、目的のデータを探せること。
レポートでは、(a)プログラムリスト,(b)その説明,(c)動作検証結果,(d)考察 を記載すること。
集約関数と副問い合わせ
特殊な条件演算子
WHERE 節の中で使える特殊な条件演算子を紹介する。
... AND ... WHERE S.業者番号 <= 100 AND S.業者番号 >= 200 ; ... OR ... WHERE S.業者番号 >= 100 OR S.業者番号 <= 200 ; NOT ... WHERE NOT S.業者番号 >= 100 ; ... IN ... WHERE S.業者番号 IN ( 'S1' , 'S4' ) ; ... BETWEEN A AND B WHERE S.優良度 BETWEEN 50 AND 100 ; ... LIKE ... WHERE S.業者名 LIKE 'A_C社' ; _ は任意の1文字 ABC社 ADC社 WHERE S.業者名 LIKE 'A%社' ; % は任意の0~N文字 A社, AA社 ABC社 ... IS NULL WHERE S.業者名 IS NULL WHERE S.業者名 IS NOT NULL
集約関数
集約関数は、SQL の SELECT の射影部分で使える関数で、出力対象となった項目に対して、COUNT(),SUM(),AVG()といった計算を行うもの。
COUNT() - 項目の数 SUM() - 項目の合計 AVG() - 項目の平均 MAX() - 項目の最大値 MIN() - 項目の最低値 SELECT COUNT(S.業者番号) FROM S WHERE S.優良度 > 20 ;
集合計算
複数の SQL の結果に対し、集合和, 集合積, 集合差などの処理を行う。
... UNION ... 集合和 ... EXPECT ... 集合差 ... INTERSECT ... 集合積 SELECT S.業者名 FROM S WHERE S.所在 = '福井' UNION SELECT S.業者名 FROM S WHERE S.所在 = '東京'
SQLの副問い合せ
前節の結合処理は時として効率が悪い。このような場合は、副問い合わせを用いる場合も多い。
SELECT S.業者名, S.所在 FROM S WHERE S.業者番号 IN ( SELECT SG.業者番号 FROM SG WHERE SG.商品番号 = 'G2' AND SG.在庫量 >= 200 ) ;
まず、『◯ IN { … }』 の比較演算子は、◯が{…}の中に含まれていれば真となる。また、SQLの中の (…) の中が副問い合わせである。
この SQL では、副問い合わせの内部には、テーブル S に関係する要素が含まれない。この場合、副問い合わせ(商品番号がG2で在庫量が200以上)は先に実行される。
{ (S1,G2,200), (S2,G2,400), (S3,G2,200), (S4,G2,200) }が該当し、その業者番号の{ S1, S2, S3, S4 }が副問い合わせの結果となる。最終的に SELECT … FROM S WHERE S.業者番号 IN { ‘S1’, ‘S2’, ‘S3’, ‘S4’ } を実行する。
相関副問い合わせ
SELECT G.商品名, G.色, G.価格 FROM G WHERE 'S4' IN ( SELECT SG.業者番号 FROM SG WHERE SG.商品番号 = G.商品番号 ) ;
この副問い合わせでは、内部に G.商品番号 が含まれており、単純に()内を先に実行することはできない。こういった副問い合わせは、相関副問い合わせと呼ばれる。
処理は、Gのそれぞれの要素毎に、副問い合わせを実行し、その結果を使って WHERE節の判定を行う。WHERE節の選択で残った結果について、射影で商品名,色,価格が抽出される。
// 概念の説明用に、C言語風とSQL風を混在して記載する for( int i = 0 ; i < sizeofarray( G ) ; i++ ) { SELECT SG.業者番号 FROM SG WHERE SG.商品番号 = G[i].商品番号 を実行 if ( WHERE 'S4' IN 副query の結果が真なら ) { printf( ... ) ; } } // 全てのG 副queryの結果 WHERE 射影 // G1 -> {S1,S2} // G2 -> {S1,S2,S3,S4} -> ◯ -> (ノート,青,170) // G3 -> {S1} // G4 -> {S1,S4} -> ◯ -> (消しゴム,白,50) // G5 -> {S1,S4} -> ◯ -> (筆箱,青,300) // G6 -> {S1}
演習課題
Paiza.io などを使って、自分で考えたSQLの命令を2つ実行すること。実行した命令とその意味を説明し、出力された結果と一致することを確認すること。
さらにこの実行と同じ結果が出力される様なC言語のプログラムを作成し、おなじく結果を確認すること。
考察として、SQLで書いたプログラムとCで書いたプログラムの違いや便利な点や、Cでのプログラムの速度を早めるにはどう書くと良いかを比較検討すること。
ネットワーク層とIPアドレス
前回の授業でL2スイッチのVLAN機能や、WiFi の話が不十分だったので、前回資料にて補足説明を行う。
説明したMACアドレスによるデータリンク層では、1つのサブネットの中で指定した相手にデータを送ることはできる。しかし、データリンク層だけでは、他のサブネットにいる相手にデータを送ることができない。(相手の名前を知っていても、住所を知らなければ郵便は送れない。)
ネットワーク層とIPアドレス(IPv4)
サブネットに分割し、隣接するサブネット、さらには上流のインターネットと通信をするためには、IPアドレスを用いた通信が行われる。
ネットワークに接続する機器には、それぞれユニークな32bitの番号(IPv4アドレス)を割り振る。
コンピュータへのIPアドレスの設定には、(a)IPアドレス,(b)サブネットマスク,(c)ゲートウェイの情報が必要となる。
- IPアドレス: 192.156.145.100 といった、0~255の8bitの値をピリオド区切りで4つ並べて表記するのが一般的。
- サブネットマスク: 255.255.255.0 といった値で、IPアドレスを2進数で書き並べた32bitと、サブネットマスクの32bitで、2進数の論理積をとった値は、ネットワーク番号と呼ばれ、機器が存在する場所を表す。
また、IPアドレスとサブネットマスクの否定と論理積をとった値は、ホスト番号と呼ばれる。
サブネットマスクは、先行する1のbit数で書き表すことも多い。255.255.255.0は、”/24″のように書く。 - ゲートウェイ: 自分自身のネットワーク番号と通信相手のネットワーク番号が異なる場合は、異なるサブネットにいるので、パケットを中継してもらう機器(ルータ,ゲートウェイ)にパケットを送る。
- IPアドレスとクラス: IPアドレスは、先頭8bit をネットワーク番号とするクラスA,16bitのクラスB,24bitのクラスCに分類されている。以前は、IPアドレスを割り当てる企業規模に応じて、大規模な大学だからクラスA、中規模ならクラスB(福井大学は133.7.0.0/16 ←このような書き方はCIDR記法という)、小規模ならクラスCを割り当てていた。(福井高専はCクラスを5本192.156.145~149.0/24 : 福井高専のIPアドレスでは3つのCIDR記法で表現できる。 192.156.145.0/24, 192.156.146.0/23, 192.156.148.0/23)
しかし、最近では IPv4 アドレスの不足から、大きな組織に割り振られた クラスA を再分配している。
ARP(IPアドレスとMACアドレスの橋渡し)
同じサブネットの中では、データリンク層でMACアドレスを用いて通信相手を指定するが、ネットワーク層ではIPアドレスを用いて通信相手を指定する。この違いを埋めるためのプロトコルがARPである。
サブネット内に相手先IPアドレスの指定されたパケット(10.10.22.102)が届くと、通信機器はサブネット内の全ての機器相手に ARPリクエストを送信する。(10.10.22.102はいますか?)
この時、10.10.22.102 のコンピュータは、自分宛てのパケットがあることを知るので、送信元のコンピュータに、自分のMACアドレスを付けたARPリプライを送り返す。(10.10.22.102は、私 “FE:DC:BA:98:76:54” です!)。送信元は、ARP通信をへらすために、その情報を記憶して、2度目以降は覚えたMACアドレスですぐに通信を始める。
ルータとRIP
ルータは、隣接するサブネットの間に入る機器で、各サブネットにゲートウェイとなるインタフェースを持つ。ルータでは受け取ったパケットをどこに流すか…という経路情報が重要となる。
- 静的ルーティング – 末端のルータの設定で、管理者が経路を設定
- 動的ルーティング – 上流ルータでRIPにより設定
経路設定には2種類あり、(a)末端のルータではこのネットワーク番号はどのルータに送るという情報をルータに直接設定する静的ルーティングがとられる。(b)上流のルータでは、末端のルータの設定が変更されることがあるので、ルータ同士がルーティング情報を送りあう動的ルーティングがとられる。動的ルーティングでは、RIPというプロトコルにより、各サブネットのつながっている経路情報が送られてくる。この経路情報を見て、パケットのIPアドレスを見て、パケットの送り先を判断する。
((Windows の場合))
C:> ipconfig /all インタフェース名: IPv4アドレス............192.168.xx.xx サブネットマスク.........255.255.255.0 デフォルトゲートウェイ....192.168.xx.1 C:> arp -a インタフェース: 192.168.xx.xx 74-03-xx-xx-xx-xx 動的 192.168.xx.yy B0-05-xx-xx-xx-xx 動的 C:> netstat -r ネットワーク宛先 ネットマスク ゲートウェイ インタフェース メトリック 0.0.0.0 0.0.0.0 192.168.xx.1 192.168.xx.xx 45 192.168.xx.0 255.255.255.0 ....
((Unix の場合))
$ ifconfig -a en1: .... inet 192.168.xx.xx netmask 0xffffff00 ... $ arp -an .... (192.168.xx.xx) at 74:03:xx:xx:xx:xx ... .... (192.168.xx.yy) at b0:05:xx:xx:xx:xx ... $ netstat -rn Destination Gateway ... default 192.168.xx.1 ... 192.168.xx ...
プライベートアドレス
IPv4 では、32bit でコンピュータを識別することから、最大でも 232台≒40億台しか識別できない。実際、IPアドレスの管理団体では、2017年度には IPv4 アドレスは使い切った状態となっている。この対応として、その組織やその家庭内だけで使われる IPアドレス である、プライベートアドレスが用いられる。
- 10.0.0.0~10.255.255.255 / 8 – 大きな機関向け
- 172.16.0.0~172.31.255.255 / 12
- 192.168.0.0~192.168.255.255 /16 – 個人向け
プライベートアドレスを利用する組織では、インターネットに接続するルータでは NAT(もしくはNAPT) という機能を内蔵し、プライベートアドレスとグローバルアドレスの変換を行う。
IPv6アドレス
IPv4の32bit の IP アドレスでは、40億台のコンピュータを区別することしかできない。そこで、最近では 128bit の IPv6 アドレスが用いられる。IPv6 では、2004:6800:4004:0826:0000:0000:0000:0000:2003 (www.google.co.jp) といった16進数4桁(16bit)を8個を”:”区切りで書き連ねる書き方をする。ただし16進4桁の先行する0や、全部”0000″ は省略して、”2404:6800:4004:826::2003″ と書く。IPv6 は最近では普及がかなり進んでいるが、途中のルータなどがすべて IPv6 に対応する必要があり IPv4 しか使えない組織も多い。
DNS と nslookup
IPアドレスみたいな数字の羅列は覚えることが難しい。このためコンピュータの名前からIPアドレスを調べるサービスがあり、Domain Name Service(DNS) と呼ばれる。詳しい仕組みは ドメイン名の説明の際に行うが、IP アドレスの調べ方を説明する。
ドメイン名から、IP アドレスを調べるには、nslookup (もしくは dig) を使用する。
$ nslookup www.fukui-nct.ac.jp : Non-authoritative answer: Name: www.fukui-nct.ac.jp Address: 104.215.53.205 $ nslookup -query=A www.fukui-nct.ac.jp # IPv4 アドレスの取得 (同上) $ nslookup -query=AAAA www.google.co.jp # IPv6 アドレスの取得 : Non-authoritative answer: Name: www.google.co.jp Address: 2404:6800:4004:826::2003
理解確認
- Cクラスのサブネットに設置できるコンピュータの台数は何台?
- “172.”で始まるプライベートアドレスでは最大何台?
- 192.168.11.2/24 のコンピュータから、192.168.1.50にデータを送る場合、どのような処理が行われるか、IPアドレス、サブネットマスク、ゲートウェイ、ネットワーク番号を使って説明せよ。
- 同じサブネット内で相手のIPアドレスが与えられた時、どのようにパケットが送られるか、MACアドレスとARPを交えて説明せよ。
SQLの基本
先週の、関係データベースの導入説明を終えて、実際のSQLの説明。
SQLの命令
SQL で使われる命令は、以下のものに分類される。((参考資料))
- データ定義言語 – CREATE, DROP, ALTER 等
- データ操作言語 – INSERT, UPDATE, DELETE, SELECT 等
- データ制御言語 – GRANT, REVOKE 等 (その他トランザクション制御命令など)
データベースは元々商用データの処理に使われることが多かったため、商用計算向けプログラム言語 COBOL と似ている点が多い。COBOL では、計算命令を 英語の文章の様に記述するのが特徴。
一般的な言語 // COBOL A_100 // A-100 変数名にハイフン(マイナス記号)が使える。Aから100を引くという意味ではない。 A = 100 ; // MOVE 100 TO A . A = A + B ; // ADD A B TO A .
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 ユーザ名 オブジェクトへの権限を剥奪する。
ただし、後に示す実験環境では、データベースのシステムにSQLiteを用いている。SQLite はネットワーク対応型のデータベースではないため、データベースをアクセスするユーザや権限の概念が存在せず、これらの命令は実行できても無視される。
create table
実際にテーブルを宣言する命令。構造体の宣言みたいなものと捉えると分かりやすい。
CREATE TABLE テーブル名 ( 要素名1 型 , 要素名2 型 ... ) ; PRIMARY KEY 制約 1つの属性でのキーの場合、型の後ろに"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 ;
理解確認
- キー・プライマリキー・外部キーについて説明せよ。
- 上記説明中の、科目テーブルにふさわしい create table 文を示せ。
- select文における、射影,結合,選択について説明せよ。
SQLの演習環境の使い方
SQL の演習は、Paiza.IO で動作確認をしてください。
SQLの基礎/select文と射影・結合・選択
ここまで述べたようにデータベースでは記録されているデータの読み書きは、SQL で行われ、射影・結合・選択を表す処理で構成されることを示した。SQL の機能を理解するために、同じ処理を C 言語で書いたらどうなるのかを示す。
((( 元のSQL ))) SELECT S.業者番号 -- 必要とされるデータを抽出する射影 -- FROM S -- 複数のテーブルを組合せる結合 -- WHERE S.優良度 >= 20 ; -- 対象となるデータを選び出す選択 -- ((( SQLをC言語で書いたら ))) // 配列の個数を求める #define 文 #define sizeofarray(ARY) (sizeof(ARY) / sizeof(ARY[0])) // C言語なら... S のデータを構造体宣言で書いてみる。 struct Table_S { char 業者番号[ 6 ] ; // 当然、C言語では要素名を char 業者名[ 22 ] ; // 漢字で宣言はできない。 int 優良度 ; char 所在[ 16 ] ; } S[] = { { "S1" , "ABC社" , 20 , "福井" } , : } ; // SELECT...をC言語で書いた場合の命令のイメージ // 結合 for( int i = 0 ; i < sizeofarray( S ) ; i++ ) { // 選択 if ( S[i].優良度 >= 20 ) // 射影 printf( "%d¥n" , S[i].業者番号 ) ; }
Sは、テーブル名であり、文脈上対象テーブルが明らかな場合、フィールド名の前の テーブルは省略可能である。
SELECT 業者番号 FROM S WHERE 優良度 >= 20 ;
WHERE 節で記述できる条件式では、= , <>(not equal) , < , > , <= , >= の比較演算子が使える。
# これ以外の演算機能は、次週にて紹介予定。
直積と結合処理
ここで、SQLの最も便利な機能は、直積による結合処理。複数の表を組み合わせる処理。単純な表形式の関係データベースで、複雑なデータを表現できる基本機能となっている。
SELECT SG.商品番号 , S.所在 FROM S , SG WHERE SG.業者番号 = S.業者番号
上記の様に FROM 節に複数のテーブルを書くと、それぞれのテーブルの直積(要素の全ての組み合わせ)を生成する処理が行われる。この機能が結合となる。しかし、これだけでは意味がないので、通常は外部キーが一致するレコードでのみ処理を行うように、WHERE SG.業者番号 = S.業者番号 のような選択を記載する。最後に、結果として欲しいデータを抽出する射影を記載する。
SELECTの結合処理と処理内容
selectでの選択,結合,射影の処理(select 射影 from 結合 where 選択)を理解するために、同じ処理をC言語で書いたらどうなるかを示す。
// C言語なら struct Table_S { char 業者番号[ 6 ] ; char 業者名[ 22 ] ; int 優良度 ; char 所在[ 16 ] ; } S[] = { { "S1" , "ABC社" , 20 , "福井" } , : } ; struct Table_SG { char 業者番号[ 6 ] ; char 商品番号[ 6 ] ; int 在庫量 ; } = SG[] { { "S1" , "G1" , 300 } , : } ; // FROM S for( int i = 0 ; i < sizeofarray( S ) ; i++ ) { // FROM SG for( int j = 0 ; j < sizeofarray( SG ) ; j++ ) { // WHERE S.業者番号 = SG.業者番号 if ( strcmp( S[i].業者番号 , SG[j].業者番号 ) == 0 ) { // SELECT SG.商品番号 , S.所在 printf( "%s %s¥n" , SG[j].商品番号 , S[i].所在 ) ; } } }
(1) i,jの2重forループが、FROM節の結合に相当し、(2) ループ内のif文がWHERE節の選択に相当し、(3) printfの表示内容が射影に相当している。
射影の処理では、データの一部分を抽出することから、1件の抽出レコードが同じになることもある。この際の重複したデータを1つにまとめる場合には、DISTINCT を指定する。
SELECT DISTINCT SG.商品番号, S.所在 FROM S, SG WHERE SG.業者番号 = S.業者番号 ;
上記のプログラムでは、データの検索は単純 for ループで記載しているが、内部で HASH などが使われていると、昇順に処理が行われない場合も多い。出力されるデータの順序を指定したい場合には、ORDER BY … ASC (or DESC) を用いる
SELECT SG.商品番号, S.所在 FROM S, SG WHERE SG.業者番号 = S.業者番号 ORDER BY S.所在 ASC ; -- ASC:昇順 , DESC:降順 --
表型のデータと串刺し
FROM に記載する直積のための結合では、2つ以上のテーブルを指定しても良い。
SELECT S.業者名, G.商品名, SG.在庫量 FROM S, G, SG WHERE S.業者番号 = SG.業者番号 -- 外部キー業者番号の対応付け -- AND SG.商品番号 = G.商品番号 -- 外部キー商品番号の対応付け -- // 上記の処理をC言語で書いたら struct Table_G { char 商品番号[ 6 ] ; char 商品名[ 22 ] ; char 色[ 4 ] ; int 価格 ; char 所在[ 12 ] ; } = G[] = { { "G1" , "赤鉛筆" , "青" , 120 , "福井" } , : } ; // [結合] S,G,SGのすべての組み合わせ // FROM S -- 結合 for( int i = 0 ; i < sizeofarray( S ) ; i++ ) { // FROM G -- 結合 for( int j = 0 ; j < sizeofarray( G ) ; j++ ) { // FROM SG -- 結合 for( int k = 0 ; k < sizeofarray( SG ) ; k++ ) { // [選択] 条件でレコードを選び出す // WHERE S.業者番号 = SG.業者番号 // AND SG.商品番号 = G.商品番号 if ( strcmp( S[i].業者番号 , SG[k].業者番号 ) == 0 && strcmp( SG[k].商品番号 , G[j].商品番号 ) == 0 ) { // [射影] 使用するフィールドを出力 printf( "%s %s %d\n" , S[i].業者名 , G[j].商品名 , SG[k].在庫量 ) ; } } } }
ここで結合と選択で実行している内容は、外部キーである業者番号を S から探す、商品番号を G から探している。この、外部キー対応しているものを探すという視点で、上記 C 言語のプログラムを書き換えると、以下のように表せる。
// FROM SG for( int k = 0 ; k < sizeofarray( SG ) ; k++ ) { // 外部キー SG.業者番号に対応するものを S から探す for( int i = 0 ; i < sizeofarray( S ) ; i++ ) { if ( strcmp( S[i].業者番号 , SG[k].業者番号 ) == 0 ) { // 外部キー SG.商品番号に対応するものを G から探す for( int j = 0 ; j < sizeofarray( G ) ; j++ ) { if ( strcmp(SG[k].商品番号,G[j].商品番号) == 0 ) { printf( "%s %s %d\n" , S[i].業者名,G[j].商品名,SG[k].在庫量 ) ; } } } } }
このような、複数の表の実体と関係を対応付けた検索を、データベースの専門の人は「データを串刺しにする」という言い方をすることも多い。
また、SQL では、このようなイメージの繰り返し処理を、数行で分かりやすく記述できている。このプログラム例では、キーに対応するものを単純 for ループで説明しているが、SQL ではプライマリキーなら、B木やハッシュなどを用いた効率の良い検索が自動的に行われる。このため、SQLを扱う応用プログラマは、SQLの結合の処理方法は通常はあまり考えなくて良い。
Microsoft AutoUpdate Trouble
MacOS で、Office 系を立ち上げると、以下の警告ポップアップがしつこく表示されるようになった。ウィンドウ閉じても数秒後に沸いて出てくるし、OK 押しても同じ。
表示されたメッセージでググると、こちらのページが見つかる。/Library/Application Support/Microsoft の中を探して Microsoft AutoUpdate のフォルダを消せと書いてある。はぁ…
$ cd /Library/Application\ Support/Microsoft/ $ find -name "*AutoUpdate*" -print $ cd MAU2.0 $ sudo rm -rf Microsoft\ AutoUpdate.app
んで、うざい画面は出なくなったけど、当然ながら AutoUpdate が動かなくなったので、Office 365 の再インストール。Office系はすでにインストールをしてあるし、新たに AutoUpdate で問題があったので AutoUpdate をインストールするよ…と表示されて、無事復旧。
事の始めは、Microsoft Teams Classic を使ってたら、”新しい Teams” という表示があってインストールしたのが原因かも。ということで、新しい Teams は入れないのが正解かな。
2分探索木
配列やリスト構造のデータの中から、目的となるデータを探す場合、配列であれば2分探索法が用いられる。これにより、配列の中からデータを探す処理は、O(log N)となる。(ただし事前にデータが昇順に並んでいる必要あり)
// 2分探索法 int array[ 8 ] = { 11, 13 , 27, 38, 42, 64, 72 , 81 } ; int bin_search( int a[] , int key , int L , int R ) { // Lは、範囲の左端 // Rは、範囲の右端+1 (注意!!) while( R > L ) { int m = (L + R) / 2 ; if ( a[m] == key ) return key ; else if ( a[m] > key ) R = m ; else L = m + 1 ; } return -1 ; // 見つからなかった } void main() { printf( "%d¥n" , bin_search( array , 0 , 8 ) ) ; }
一方、リスト構造ではデータ列の真ん中のデータを取り出すには、先頭からアクセスするしかないのでO(N)の処理時間がかかり、極めて効率が悪い。リスト構造のようにデータの追加が簡単な特徴をもったまま、もっとデータを高速に探すことはできないものか?
2分探索木
ここで、データを探すための効率の良い方法として、2分探索木(2分木)がある。以下の木のデータでは、分離する部分に1つのデータと、左の枝(下図赤)と右の枝(下図青)がある。
この枝の特徴は何だろうか?この枝では、中央のデータ例えば42の左の枝には、42未満の数字の枝葉が繋がっている。同じように、右の枝には、42より大きな数字の枝葉が繋がっている。この構造であれば、64を探したいなら、42より大きい→右の枝、72より小さい→左の枝、64が見つかった…と、いう風にデータを探すことができる。
特徴としては、1回の比較毎にデータ件数は、(N-1)/2件に減っていく。よって、この方法であれば、O(log N)での検索が可能となる。これを2分探索木とよぶ。
このデータ構造をプログラムで書いてみよう。
struct Tree { struct Tree* left ; int data ; struct Tree* right ; } ; // 2分木を作る補助関数 struct Tree* tcons( struct Tree* L , int d , struct Tree* R ) { struct Tree* n = (struct Tree*)malloc( sizeof( struct Tree ) ) ; if ( n != NULL ) { /* (A) */ n->left = L ; n->data = d ; n->right = R ; } return n ; } // 2分探索木よりデータを探す int tree_search( struct List* p , int key ) { while( p != NULL ) { if ( p->data == key ) return key ; else if ( p->data > key ) p = p->left ; else p = p->right ; } return -1 ; // 見つからなかった } struct Tree* top = NULL ; void main() { // 木構造をtcons()を使って直接生成 (B) top = tcons( tcons( tcons( NULL , 13 , NULL ) , 27 , tcons( NULL , 38 , NULL ) ) , 42 , tcons( tcons( NULL , 64 , NULL ) , 72 , tcons( NULL , 81 , NULL ) ) ) ; printf( "%d¥n" , tree_search( top , 64 ) ) ; }
この方式の注目すべき点は、struct Tree {…} で宣言しているデータ構造は、2つのポインタと1つのデータを持つという点では、双方向リストとまるっきり同じである。データ構造の特徴の使い方が違うだけである。
理解度確認
- 上記プログラム中の、補助関数tcons() の(A)の部分 “if ( n != NULL )…” の判定が必要な理由を答えよ。
- 同じくmain() の (B) の部分 “top = tcons(…)” において、末端部に NULL を入れる理由を答えよ。
2分木に対する処理
2分探索木に対する簡単な処理を記述してみよう。
// データを探す int search( struct Tree* p , int key ) { // 見つかったらその値、見つからないと-1 while( p != NULL ) { if ( p->data == key ) return key ; else if ( p->data > key ) p = p->left ; else p = p->right ; } return -1 ; } // データを全表示 void print( struct Tree* p ) { if ( p != NULL ) { print( p->left ) ; printf( "%d¥n" , p->data ) ; print( p->right ) ; } } // データ件数を求める int count( struct Tree* p ) { if ( p == NULL ) return 0 ; else return 1 + count( p->left ) + count( p->right ) ; } // データの合計を求める int sum( struct Tree* p ) { if ( p == NULL ) return 0 ; else return p->data + count( p->left ) + count( p->right ) ; } // データの最大値 int max( struct Tree* p ) { while( p->right != NULL ) p = p->right ; return p->data ; }
これらの関数では、木構造の全てに対する処理を実行する場合には、再帰呼び出しが必要となる。
2分探索木にデータを追加
前回の授業では、データの木構造は、補助関数 tcons() により直接記述していた。実際のプログラムであれば、データに応じて1件づつ木に追加するプログラムが必要となる。この処理は以下のようになるだろう。
struct Tree* top = NULL ; // 2分探索木にデータを追加する処理 void entry( int d ) { struct Tree** tail = &top ; while( *tail != NULL ) { if ( (*tail)->data == d ) // 同じデータが見つかった break ; else if ( (*tail)->data > d ) tail = &( (*tail)->left ) ; // 左の枝に進む else tail = &( (*tail)->right ) ; // 右の枝に進む } if ( (*tail) == NULL ) *tail = tcons( d , NULL , NULL ) ; } int main() { char buff[ 100 ] ; int x ; while( fgets( buff , sizeof( buff ) , stdin ) != NULL ) if ( sscanf( buff , "%d" , &x ) != 1 ) break ; entry( x ) ; return 0 ; }
このプログラムでは、struct Tree** tail というポインタへのポインタ型を用いている。tail が指し示す部分をイメージするための図を以下に示す。
理解確認
- 関数entry() の14行目の if 判定を行う理由を説明せよ。
- 同じく、8行目の tail = &( (*tail)->left ) の式の各部分の型について説明せよ。
- sscanf() の返り値を 1 と比較している理由を説明せよ。
- entry() でデータを格納する処理時間のオーダを説明せよ。
ポインタのポインタを使わない挿入
struct Tree* insert( struct Tree* p , int x ) { if ( p == NULL ) p = tcons( NULL , x , NULL ) ; else if ( p->data == x ) ; else if ( p->data > x ) p->left = insert( p->left , x ) ; else p->right = insert( p->right , x ) ; return p ; } int main() { struct Tree* top = NULL ; int x ; while( scanf( "%d" , &x ) == 1 ) { top = insert( top , x ) ; } print( top ) ; return 0 ; }