データベースの物理構造 (Physical Structure of Relational Database)

大学で使用した自作の資料等を,手直しの上公開している. クリエイティブ・コモンズ BY NC SA.

リレーショナルデータベースの基礎であるテーブル定義,一貫性制約,SQL,結合と分解,トランザクション,埋め込みSQL,実行計画,二次索引を学ぶ.SQLite 3 を用いて,SQL についての演習も行う.

【サイト内の関連ページ】

  • リレーショナルデータベースの基本まとめ:別ページ »で説明
  • SQL 入門演習(SQLite 3 を使用)(全3回):別ページ »にまとめ

    入門者向け.リレーショナルデータベースを知る.

  • リレーショナルデータベースシステム(スライド資料と SQL 演習)(全15回):別ページ »にまとめ

    入門者向け.設計と利用と運用.MySQL, Access を利用.基本を学ぶ.演習付き.

  • リレーショナルデータベース演習(全15回):別ページ »にまとめ

    入門者向け.設計と利用に関する演習.

  • リレーショナルデータベースの基礎(まとめ) [PDF], [ワードファイル]

    基礎を1つにまとめた資料.

演習で行うこと

Windows では,バイナリエディタのインストール

( 用のバイナリエディタとしては、 Stirling, BZ, HxD などが有名です.

  1. バイナリエディタ BZ のファイルの入手
  2. ファイルの確認
  3. ファイルの解凍 (Extract the file)

Sqliteman で既存のデータベースを開く (Open an existing database using Sqliteman)

すでに作成済みのデータベースを,下記の手順で開くことができる.

以下の手順で,既存のデータベースファイルを開く. (Open an existing database file)

  1. File」→ 「Open
  2. データベースファイルを開く (Open 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 を選ぶ. 

  • 「Tables」を展開すると,テーブルの一覧 (List of Tables) が表示されるので確認する (List of tables appears by clicking 'Tables')

    SQL を用いたテーブル定義と一貫性制約の記述 (Table definition and integrity constraint specification using SQL)

    SQL を用いて,R テーブルを定義し,一貫性制約を記述する. (Define 'R' table and specify integrity constrants of the table using SQL)

    リレーショナル・スキーマ (relational schema): path(id, docid, path, val, created_at)
    
    1. path テーブルの定義 (Define a table)

      次の SQL を入力し,「Run SQL」のアイコンをクリック (Write the following SQL, and click "Run SQL" icon).

      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 Editor」のウインドウには,SQL プログラムを書くことができる. In the 'SQL string' window, you can write down SQL program(s).

    2. コンソールの確認 (Inspect console)

      エラーメッセージが出ていないことを確認

    SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

    次のような path テーブルを作る. (Construct table 'R')

    以下の手順で,SQL を用いて path テーブルへの行の挿入を行う (Insert rows into table 'path' using SQL)

    1. 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;
      
    2. 複数の SQL 文の一括実行 (Run multiple SQL statements)

      複数の 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)

    3. 「Script Output」ウインドウの確認 (Inspect "Script Output" window)

      エラーメッセージが出ていないことを確認

    Sqliteman を用いたデータのブラウズ (Browse Data using Sqliteman)

    SQLite 3 のデータベースファイルの物理構造 (Physical Structure of SQLite Database File)

    SQLite 3 のデータベースファイルは,レコードを単位とした物理構造になっている

    This figure is from the 'SQLite Database File Format' Web page. https://www.sqlite.org/fileformat.html

    1. Sqliteman を終了しておくこと (Exit the Sqliteman)
    2. バイナリエディタの起動 (Execute the binary editor)

      以下の手順で、データベースファイル 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 を開く)

    3. データベースファイルを開く (Open the database file)

      * Ubuntu の場合 (GHex を使う場合)

      「ファイル」→「開く」

      ディレクトリを選ぶ. ここでは,データベースファイル mydb2 を置いているディレクトリである「/home/ubuntuuser」を選んでいる

      ファイルを選ぶ. ここでは,「mydb」を選んでいる (「mydb」をダブルクリック).

      * Windows の場合 (Bz)

    4. データベース・ファイルの中から R テーブルのレコードを探す (Find the table 'records' in a database file manually)

      * Ubuntu の場合 (GHex を使う場合)

      「編集」→ 「検索」

      検索文字列「root/」を指定して、「検索」をクリック

      末尾に「.」が自動で入る。これは気にしないこと。

    5. データベースの中身の確認

      バイナリエディタ GHex では、

      • 左側: ファイルの中身が16進数で
      • 右側: ファイルの中身がアルファベット、数字、英記号で

      で表示され、ファイルの中身を簡単に確認できる。

      データベース・ファイルのデータページの中には,レコードが並んでいることが確認できる.データページの中には未使用部分がある.

      There a sequence of records in data pages in database file.

      * データベースの構造

      • SQLite 3 では,データベースヘッダがある
      • SQLite 3 では,データページのサイズは 1024バイト (16進数で 400)
    6. path フィールドの値の確認 ('path' field)
    7. val フィールドの値の確認 ('val' field)
    8. created_at フィールドの値の確認 ('created_at' field)

      created_at には,now を使って現在時刻を入れたので,値が違っているでしょう.

      この演習では docid フィールドの値の確認は行わないする.docid は整数データである。整数データはコード化されている.数値データのコード化体系はデータベース管理システムの種類によって違う. In this exercise, ignore the 'docid' field. The integer value is encoded.

    9. レコードの長さキー (Key and Record Lenght)

      レコードヘッダには,レコードの長さとレコードのキー (key) が格納されている (Record header includes record length and key value).

      今回は

      • レコードの長さ: 2E, キーの値: 03
      • レコードの長さ: 2C, キーの値: 02
      • レコードの長さ: 2D, キーの値: 01

      「id integer primary key autoincrement NULL,」と定義したので、idがキーである。