![[kaneko lab.]](https://www.kkaneko.jp/info/logo_png.png){kind=logo}
データベースの物理構造 (Physical Structure of Relational Database)
大学で使用した自作の資料等を，手直しの上公開している． クリエイティブ・コモンズ BY NC SA．
リレーショナルデータベースの基礎であるテーブル定義，一貫性制約，SQL，結合と分解，トランザクション，埋め込みSQL，実行計画，二次索引を学ぶ．SQLite 3 を用いて，SQL についての演習も行う．
【サイト内の関連ページ】
演習で行うこと
- (Windows の場合) バイナリエディタのインストール
- SQLite 3 のデータベースファイルの物理構造 (Physical Structure of SQLite Database File)
Windows では，バイナリエディタのインストール
( 用のバイナリエディタとしては、
Stirling, BZ, HxD などが有名です．
すでに作成済みのデータベースを，下記の手順で開くことができる．
以下の手順で，既存のデータベースファイルを開く．
(Open an existing database file)
* Ubuntu での実行例（「mydb」を開く場合）
データベースファイル /home/ubuntuuser/mydb を選び，
「開く」をクリック (Click '開く' after choosing the database file "/home/ubuntuuser/mydb")
* Windows での実行例（「C:\SQLite\mydb」を開く場合）
データベースファイル C:\SQLite\mydb を選び，
「開く」をクリック (Click '開く' after choosing the database file "C:\SQLite\mydb")
要するに，/home/＜ユーザ名＞/SQLite 3の下の mydb を選ぶ．
SQL を用いて，R テーブルを定義し，一貫性制約を記述する．
(Define 'R' table and specify integrity constrants of the table using SQL)
次の SQL を入力し，「Run SQL」のアイコンをクリック
(Write the following SQL, and click "Run SQL" icon).
* 「SQL Editor」のウインドウには，SQL プログラムを書くことができる．
In the 'SQL string' window, you can write down SQL program(s).
エラーメッセージが出ていないことを確認
次のような path テーブルを作る．
(Construct table 'R')
以下の手順で，SQL を用いて path テーブルへの行の挿入を行う (Insert rows into table 'path' using SQL)
複数の SQL 文を一括実行したいので，カーソルを先頭行に移動した後に，「Run multiple SQL statements ...」のボタンをクリックする．
「Move the cursor to the top statement. Click "Run multiple SQL statements
from current cursor position in one batch" icon)
エラーメッセージが出ていないことを確認
まず，オブジェクト・ブラウザ (Object Browser) の中の「Tables」を展開 (Click 'Tables')
次に，テーブル pathを選ぶ (Select table 'path')
テーブル pathが表示される (table 'path' appears)
* もし，データに間違いがあれば，このウインドウで修正できる
(If you find any mistakes, you can modify the data using this window).
path テーブルのルート・ページ番号が分かる．この場合はたまたま「9」になっているが，「9」と違う値になっていても問題はない． (The root page number of table 'path'. In this figure, the number is '9').
ルート・ページ番号は，データベース管理システムが自動的に決める番号である．
このときデータベースが保存される (Database is saved automatically)
SQLite 3 のデータベースファイルは，レコードを単位とした物理構造になっている．
This figure is from the 'SQLite Database File Format' Web page. https://www.sqlite.org/fileformat.html
以下の手順で、データベースファイル mydb をバイナリエディタで開き， データベースファイルが，レコードを単位とした物理構造になっていることを確認しておく． (The physical structure of relational database is based on the record).
*
Ubuntu の場合 (GHex を起動する)
「プログラミング」→
「Hex エディタ」のように操作する
* 端末を開いて，
「ghex」 のように実行しても良い．
*
ghex2 が無いときは, 「sudo apt -y install ghex2」を実行して、ghex2 をインストールする。あるいは、類似の同機能のソフトウェア (okteta など) を試す。
* Windows の場合 (Bz を開く)
*
Ubuntu の場合 (GHex を使う場合)
「ファイル」→「開く」
ディレクトリを選ぶ．
ここでは，データベースファイル mydb2 を置いているディレクトリである「/home/ubuntuuser」を選んでいる
ファイルを選ぶ．
ここでは，「mydb」を選んでいる
（「mydb」をダブルクリック）．
* Windows の場合 (Bz)
*
Ubuntu の場合 (GHex を使う場合)
「編集」→
「検索」
検索文字列「root/」を指定して、「検索」をクリック
末尾に「.」が自動で入る。これは気にしないこと。
バイナリエディタ GHex では、
で表示され、ファイルの中身を簡単に確認できる。
データベース・ファイルのデータページの中には，レコードが並んでいることが確認できる．データページの中には未使用部分がある．
There a sequence of records in data pages in database file.
* データベースの構造
created_at には，now を使って現在時刻を入れたので，値が違っているでしょう．
この演習では docid フィールドの値の確認は行わないする．docid は整数データである。整数データはコード化されている．数値データのコード化体系はデータベース管理システムの種類によって違う．
In this exercise, ignore the 'docid' field. The integer value is encoded.
レコードヘッダには，レコードの長さとレコードのキー (key) が格納されている (Record header includes record length and key value).
今回は
「id integer primary key autoincrement NULL,」と定義したので、idがキーである。
Sqliteman で既存のデータベースを開く (Open an existing database using Sqliteman)
SQL を用いたテーブル定義と一貫性制約の記述 (Table definition and integrity constraint specification using SQL)
リレーショナル・スキーマ (relational schema): path(id, docid, path, val, created_at)
create table path (
id integer primary key autoincrement NULL,
docid integer not null,
path text not null,
val text not null,
created_at DATETIME not null );
SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)
begin transaction;
insert into path values( 1, 1, '/root/title', 'report A', datetime('now', 'localtime') );
insert into path values( 2, 1, '/root/author', 'kaneko', datetime('now', 'localtime') );
insert into path values( 3, 1, '/root/date', '2012/11/21', datetime('now', 'localtime') );
commit;
Sqliteman を用いたデータのブラウズ (Browse Data using Sqliteman)
SQLite 3 のデータベースファイルの物理構造 (Physical Structure of SQLite Database File)
