トップページ -> 研究道具箱と入門演習 -> データベース特論・支援 Web ページ -> リレーショナルデータベースのデータ構造と一貫性制約
[サイトマップへ]   [全文検索へ]   [統計情報へ]   [掲示板へ]  

リレーショナルデータベースのデータ構造と一貫性制約

URL: http://www.db.is.kyushu-u.ac.jp/rinkou/addb/2.html


演習で行うこと


演習を行うために必要になる機能や文法

SQL はリレーショナルデータベース言語の標準である. ここでは SQLite が持つ SQL の機能のうち今回の演習に関係する部分を紹介する。

SQLite の SQL の説明は http://www.hwaci.com/sw/sqlite/lang.html (English Web Page) にある.

  1. SQLite のデータ型

    データ型の種類は,データベース管理システムごとに違う. SQLite では,扱えるデータ型として次の 5 種類がある.詳しい説明は http://www.sqlite.org/datatype3.html にある.

    ※ SQlite のデータ型と,SQL の標準が定めるデータ型の定義は異なる.大胆にまとめると,SQlite のデータ型の方がより大きな範囲のデータを扱える.

  2. SQL の文字列定数

    SQL の規格では,文字列定数はシングルクオーテーションマーク「'」で囲むことになっている.

  3. SQL テーブル定義文 (create-table-statement) の例

    CREATE TABLE <table-name> (<column-name> <type-name> [<column constraint> ...], ...);

    ※ 「 [<column constraint> ...]」は省略可能であることに注意

  4. SQL 列制約 (column-constraint) の例

    create-table-statement の中に含める一貫性制約やデフォルト値の指定

  5. SQL テーブル制約 (table-constraint) の例

    create-table-statement の中に含める一貫性制約のうち複数の属性に関わるものは table-constraint の形で記述することになる.

  6. SQL 挿入文 (insert statement) の例

    テーブルへの行の挿入

  7. BEGIN TRANSACTION, COMMIT, ROLLBACK

    SQL 挿入文 (insert statement) などでのデータベース更新を行うときは,最初に「BEGIN TRANSACTION;」を実行する. データベース更新が終わったら「COMMIT;」または「ROLLBACK;」を実行する.

  8. SQL の SELECT, FROM, WHERE の例

    SQL での問い合わせ には SELECT, FROM, WHERE 句が多用される.


SQLite バージョン 3 のインストール (Install SQLite version 3)

  1. ディレクトリ C:\SQLite を作る

    ここに SQLite のプログラム(コマンドラインクライアント)とデータベースファイルを置くことにする.

  2. sqlite-3_6_21.zip の入手

    http://www.sqlite.org/

  3. sqlite-3_6_21.zip の解凍

    解凍すると sqlite3.exe ができる.

  4. sqlite3.exe を C:/SQLite にコピー

  5. TZ 環境変数を「JST」に設定


SQLite バージョン 3 の起動と終了 (Start and end SQLite version 3)

  1. Windows のコマンドプロンプトで,次の操作を行う.(Use the Windows command prompt, and do the following).

    SQLite データベース・ディレクトリ C:\SQLite に移る.

     
    C:
    cd C:\SQLite  
    

  2. SQLite の起動 (Start the SQLite).

    このとき,データベース論理名として mydb を指定する.(The logical database name is 'mydb').

    ※ データベース論理名はなんでも良いが、アルファベットのみを使うのが良い.

    .\sqlite3.exe mydb 
    

  3. ヘルプの表示 (display the help)

    .help」で,ヘルプが表示されます.

  4. 現在使用中のデータベースについての,文字のエンコーディングの確認 (encoding)

    PRAGMA encoding;」で,エンコーディングが表示されます.

  5. SQLite の終了 (End SQLite)

    .exit」で終了.

使い方の詳しい説明は http://www.sqlite.org/sqlite.html を見てください.


SQLite データベースの新規作成 (Create a new SQLite database), SQL を用いたテーブル定義と一貫性制約の記述 (Table defintion and integrity constrant specification using SQL)

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

リレーショナル・スキーマ (relational schema): score_records(name, score, student_name)

CREATE TABLE score_records (
    name          TEXT     NOT NULL,
    score         INTEGER  NOT NULL CHECK ( score >= 0 AND score <=100 ),
    student_name  TEXT     NOT NULL,
    created_at    DATETIME NOT NULL,
    updated_at    DATETIME,
    UNIQUE (name, student_name) );

このとき C:\SQLite に,データベースファイル mydb ができる.(At the time, database file is generated)

※ SQLite では,データベースが始めて使うときに,自動的にデータベースファイルが生成される.データベースファイル名は,データベース論理名と同じになる.

※ データベースファイルが生成されるのは,テーブルを定義するなど,データベースの更新を行ったときなので,最初,sqlite3 を起動したとき,データベースファイルが無くてもあわてないこと.


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

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

name        | score |  student_name
--------------------------------------
Database    |   80  |  KK
Database    |   95  |  AA
Database    |   80  |  LL
Programming |   85  |  KK
Programming |   75  |  LL

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

挿入の前に BEGIN TRANSACTION; を実行し,一連の挿入が終わったら COMMIT; を実行する.(Issue "BEGIN TRANSACTION" before database update and "COMMIT" after database update).

datetime('now') は現在日時の取得.DATETIME型は、YYYY-MM-DD HH:MM:SS形式.

BEGIN TRANSACTION;
INSERT INTO score_records VALUES( 'Database',    80, 'KK', datetime('now'), NULL );
INSERT INTO score_records VALUES( 'Database',    95, 'AA', datetime('now'), NULL );
INSERT INTO score_records VALUES( 'Database',    80, 'LL', datetime('now'), NULL );
INSERT INTO score_records VALUES( 'Programming', 85, 'KK', datetime('now'), NULL );
INSERT INTO score_records VALUES( 'Programming', 75, 'LL', datetime('now'), NULL );
COMMIT;

この挿入では,属性の値を,テーブル定義の順に全て並べる (List all attribute values. The order is the same as its table definition) 


SQL 問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the results)

SQL 問い合わせの詳細については,別の Web ページで説明する.ここでは,テーブルの中身を確認して欲しい.

テーブルの全ての行の表示 (List all rows of a table)

SELECT * FROM score_records;

条件を満足する行のみの表示 (List the rows which satisfy a given condition)

SELECT * FROM score_records WHERE name = 'Database';

SELECT score FROM score_records WHERE name = 'Database';


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

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

リレーショナル・スキーマ (relational schema): order_records(id, year, month, day, customer_name, product_name, unit_price, qty)

CREATE TABLE order_records (
    id            INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
    year          INTEGER  NOT NULL CHECK ( year > 2008 ),
    month         INTEGER  NOT NULL CHECK ( month >= 1 AND month <= 12 ),
    day           INTEGER  NOT NULL CHECK ( day >= 1 AND day <= 31 ),
    customer_name TEXT  NOT NULL,
    product_name  TEXT  NOT NULL,
    unit_price    REAL     NOT NULL CHECK ( unit_price > 0 ),
    qty           INTEGER  NOT NULL DEFAULT 1 CHECK ( qty > 0 ),
    created_at    DATETIME NOT NULL,
    updated_at    DATETIME,
    CHECK ( ( unit_price * qty ) < 200000 ) );


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

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

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

挿入の前に BEGIN TRANSACTION; を実行し,一連の挿入が終わったら COMMIT; を実行する.(Issue "BEGIN TRANSACTION" before database update and "COMMIT" after database update).

BEGIN TRANSACTION;
INSERT INTO order_records VALUES( 1, 2009, 10, 26,  'kaneko', 'orange A', 1.2, 10, datetime('now'), NULL );
INSERT INTO order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) VALUES( 2009, 10, 26,  'miyamoto', 'Apple M',  2.5, 2, datetime('now') );
INSERT INTO order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) VALUES( 2009, 10, 27,  'kaneko',   'orange B', 1.2, 8, datetime('now') );
INSERT INTO order_records (year, month, day, customer_name, product_name, unit_price, created_at) VALUES( 2009, 10, 28,  'miyamoto',   'Apple L', 3, datetime('now') );
COMMIT;

INSERT INTO には 2つの方法がある.(Two styles of "INSERT INTO")

■ 属性の値を,テーブル定義の順に全て並べる (List all attribute values. The order is the same as its table definition)

INSERT INTO order_records VALUES( 1, 2009, 10, 26,  'kaneko', 'orange A', 1.2, 10, datetime('now'), NULL );

■ 属性の値の並び方を,属性名を使って明示的に指定する (Specify the order of attribute values using attribute name list)

このとき,属性値を省略すると,テーブル定義のときに指定されたデフォルト値が使われる (defaults values are used)

INSERT INTO order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) VALUES( 2009, 10, 26,  'miyamoto', 'Apple M',  2.5, 2, datetime('now') );
INSERT INTO order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) VALUES( 2009, 10, 27,  'kaneko',   'orange B', 1.2, 8, datetime('now') );
INSERT INTO order_records (year, month, day, customer_name, product_name, unit_price, created_at) VALUES( 2009, 10, 28,  'miyamoto',   'Apple L', 3, datetime('now') );

SQL 問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the results)

SQL 問い合わせの詳細については,別の Web ページで説明する.ここでは,テーブルの中身を確認して欲しい.

テーブルの全ての行の表示 (List all rows of a table)

SELECT * FROM order_records;

条件を満足する行のみの表示 (List the rows which satisfy a given condition)

SELECT * FROM order_records WHERE day = 26;

SELECT * FROM order_records WHERE customer_name = 'kaneko';

SELECT * FROM order_records WHERE unit_price > 2;


一貫性制約に違反する更新ができないことの確認 (Integrity constraint violation is not permitted when database update)

ここでは,一貫性制約に違反するような更新を試みる.データベース管理システムソフトウエアが一貫性を維持するので, 一貫性制約に違反するような更新はできない.

一意制約 (UNIQUE)

BEGIN TRANSACTION; 
INSERT INTO score_records VALUES( 'Database', 90, 'KK', datetime('now'), NULL );
ROLLBACK; 

※ すでに「'Database', 80, 'KK'」という行がある. 一意制約「UNIQUE(name, student_name)」に違反.

主キー制約 (PRIMARY KEY)

BEGIN TRANSACTION; 
INSERT INTO order_records VALUES( 3, 2009, 10, 29,  'kaneko', 'orange C', 1.1, 6, datetime('now'), NULL );
ROLLBACK; 

※ すでに属性 id には 3 という値がある. 主キー制約「PRIMARY KEY」に違反.

非空制約 (NOT NULL)

BEGIN TRANSACTION; 
INSERT INTO order_records VALUES( 5, 2009, 10, 30,  NULL, 'melon', 10, 3, datetime('now'), NULL );
ROLLBACK; 

※ 非空制約「NOT NULL」. 属性 customer_name には NULL を入れることができない.

その他の一貫性制約

一貫性制約に違反する例

BEGIN TRANSACTION; 
INSERT INTO order_records VALUES( 6, 1009, 10, 30,  kaneko, 'melon', 10, 3, datetime('now'), NULL );
ROLLBACK; 

※ 制約「CHECK ( year > 2008 )」に違反

一貫性制約に違反する例

BEGIN TRANSACTION; 
INSERT INTO order_records VALUES( 7, 2009, 10, 31,  kaneko, 'strawberry', 4.6, 100000, datetime('now'), NULL );
ROLLBACK; 

※ 制約「CHECK ( ( unit_price * qty ) < 200000 ) )」に違反