テーブル定義と一貫性制約

• 直積集合（「直積」とも呼ぶ）

  n 個の集合 S1, S2, … , Sn が与えられたとき， {(X1, X2, … ,Xn) | X1∈S1, X2∈S2, …, Xn∈Sn} を S1, S2, …, Sn の直積集合と呼び， S1 × S2 × … × Sn と記す．例えば，S1 = {1, 2}, S2 = {a, b, c} とするとき S1 × S2 = {(1, a), (1, b), (1, c), (2, a), (2, b), (2, c)}

  ※ S2 × S1 = {(a, 1), (b, 1), (c, 1), (a, 2), (b, 2), (c, 2)} となり，S1×S2 とは違う集合である.

• ドメイン (domain)

  ドメインとは属性が取りえる値の集合 (a set of permitted value)のこと．例えば，属性「年齢」のドメインは，整数値の集合 {0,1,2. … , 120} のようになるだろう．

• リレーション (relation) （「関係」とも呼ぶ）

  D1, D2, … , Dn をドメインとするとき，D1, D2, …, Dn 上のリレーションとは，直積集合「D1×D2× … ×Dn」の任意の有限部分集合を言う．

  例えば，8つの属性 id, year, month, day, customer_name, product_id, unit_price, qry があり，それぞれのドメインが次のようになっているとする．

  • id のドメイン: 整数
  • year のドメイン: {2008, 2009, 2010, ...}
  • month のドメイン: {1, 2, ..., 12}
  • day のドメイン: {1, 2, ..., 31}
  • customer_name のドメイン: 文字列
  • product_id のドメイン: 文字列
  • unit_price のドメイン: 0 よりも大きい実数
  • qty のドメイン: 0 よりも大きい整数

  このとき，次の図で表される集合は 1つのリレーションである．ここでは，ドメインの直積集合の要素を１つの行として書いている．

  
• タップル （「タプル」とも呼ぶ）

  「タップル」というときは，次の２つの意味を持つ．

  1. 直積集合の各要素のこと．
  2. リレーションの各要素のこと．
  　
• 多重集合 (multi-set)

  要素の重複を許すような集合のことを多重集合という．リレーショナルデータベースにおいて多重集合という概念が導入されている理由を，具体例で説明すると下記の通りである．

  id | name        | teacher_name | student_name | score
  ----------------------------------------------------
  1  | Database    | K            | KK           | 85
  2  | Database    | K            | AA           | 75
  3  | Database    | K            | LL           | 90
  4  | Programming | A            | KK           | 85
  5  | Programming | A            | LL           | 75
  

  score (得点) は {85, 75, 90, 85, 75} という多重集合である． この多重集合を見て，平均点83 ということが分かる．

  多重集合という概念がない場合とする。 score (得点) は {85, 75, 90, 85} という集合である． 平均点が分からず困ったことになる．

• テーブル (table)

  リレーショナルデータベースでは，データベースをテーブルの集まり (collection of tables) として記述する． リレーショナルデータベースでのテーブルは，ヘッダと本体からなる．

  • ヘッダ: 属性名が並ぶ．
  • 本体: 属性のドメインの直積集合の要素からなる多重集合

  【テーブルの例（本体に重複値がない）】

  

  【テーブルの例（本体に重複値がある）】

  

  以上のように，テーブルの本体にはリレーションを格納することができる（リレーショナルデータベースに「リレーショナル」という言葉が付いているのはそのため）し， 重複値を持つような多重集合を格納することもできる．

• テーブル名 (table name)

  テーブル名とは，個々のテーブルに付けられた名前のこと．

• 属性 (attribute)

  リレーションデータベースのテーブルでは，テーブルの各列が１つの属性をなす． 属性名は各列に付けられた名前であり，テーブルのヘッダに書かれる． 例えば，下記のテーブルには，id, year, month, day, customer_name, product_id, unit_price, qty という名前の属性がある．

  

  ※ テーブルのタップルが更新（例えば，テーブルに新しいタップルが挿入されたり，テーブルからタップルが削除されたり，タップルの値が変化したり）されても，属性名は不変である（変化しない）．

• 行 (row)

  リレーショナルデータベースで「行」というときは，テーブルの本体の各行のこと．「レコード」ともいう．

• 列 (column)

  リレーショナルデータベースで「列」というときは，テーブルの本体の各列のこと．「カラム」ともいう．

• リレーショナルデータモデル

  リレーショナルデータモデルとは，次の特徴を持ったデータモデルである．

  1. データベースを，テーブルの集まりとして記述
  2. データ操作の体系は，リレーショナル代数演算やリレーショナル論理（リレーショナル代数演算とリレーショナル論理は等価であるといってよい）．
  3. リレーショナルデータモデルの一貫性制約には，主キー制約，一意制約，非空制約，ドメイン制約，参照整合性制約などがある．

• リレーショナルデータベース

  リレーショナルデータモデルで記述されたデータベース

• リレーショナルデータベース管理システム

  リレーショナルデータモデルの機能を持つデータベース管理システム

• データ型 (data type)

  データベースで「データ型」というときは，普通，データベースが扱う属性のデータ型のことをいう．

• 空値 （「NULL」とも呼ぶ）

  空値はさまざまな意味で使われる．

  • 値が存在しない (does not exist) ・・・ 例えば，自由席の場合には「予約座席番号」が存在しない．「送付を希望しない」ときは住所が空欄．
  • 未定 (not yet decided) ・・・ 例えば，「データベース」の授業の担当者がまだ決まっていないようなとき
  • 値が未知< (unknown) ・・・ 例えば，住所がまだ分からないとき（住所は存在するが、まだ知らない）

• リレーションスキーマ (relation schema)

  リレーションにはリレーション名と属性名が付与される． （「リレーション」とは，直積集合「D1×D2× … ×Dn」の任意の有限部分集合のことであった)． R システムをリレーション名，A1, A2, …, An を属性名とするとき， リレーションスキーマを「R(A1, A2, …, An)」のように書く．

  リレーションスキーマは，個々のテーブルの枠組みを記述している．リレーションのタップルが更新（リレーションに新しいタップルが挿入されたり，リレーションからタップルが削除されたり，リレーションの値が変化したり）されても，リレーション名と属性名は不変であるから，リレーションスキーマも不変である．

• リレーションスキーマのインスタンス　 （「リレーションスキーマのインスタンス」のことを，「リレーション」や「関係」とも呼ぶ）

  リレーションスキーマ R(A1, A2, … , An) について（リレーション名はR であり，属性名はA1, A2, … Anである），これら属性のドメインをD1, D2, … , Dn とする． D1, D2, … , Dn上のリレーションは，直積集合「D1×D2× … ×Dn」の任意の有限部分集合のことである．このリレーションのことを，「リレーションスキーマ R(A1, A2, … , An) のインスタンス」と呼ぶ（あるいは「リレーション」や「関係」とも呼ぶ）．

  • 「ドメイン」とは属性が取りえる値の集合のこと．
  • リレーションスキーマ R(A1, A2, … , An) のインスタンスのことを，「リレーションR(A1, A2, … , An)」と呼んだり「リレーションR」と呼ぶことが多い．同じ記号「R(A1, A2, … , An)」と「R」を，リレーションスキーマの意味で使ったり，リレーションの意味で使ったりすることがあるので注意が必要．リレーションスキーマとリレーションの違いを理解していれば，混乱はしないだろう．
  • 上で説明したように，属性名とリレーション名は不変であるから，リレーションスキーマ R(A1, A2, … , An) は不変である．一方で，リレーションR(A1, A2, … , An) は変化する．
  　
• 単純値　（単純のことを「シンプル」ともいう）

  分解不可能な値のこと．普通は「数値や文字や文字列や時刻や日付や論理値などの値」という意味で使う．直積集合の要素や，べき集合の要素は単純値ではないと考えるのが普通である．

  • 直積集合の例：　属性名「family name」のドメインと，属性名「given name」のドメインの直積集合
  • べき集合の例：　ドメイン {みかん，りんご，バナナ}から構成されるべき集合は，{φ, {みかん}, {りんご}, {バナナ}, {みかん, りんご}, {りんご, バナナ}, {バナナ, みかん}, {みかん, りんご, バナナ}}
  　
• 　第一正規形

  ドメインとして，分解不可能な「単純値」のみを対象とすること．

• キー（「候補キー」ともいう）

  リレーショナルデータモデルで「キー」というときは，次の意味である．

  テーブルのタップルを唯一に識別するのに使える属性あるいは属性の組のうち極小なものをキーという．

  キーである属性，あるいは，属性の組については，テーブルの2つ以上のタップルが,いかなる場合であっても,同一の属性値を持つことはありえない． 例えば，職員のデータベースでは，住所と氏名のペアがキーになるだろう（住所，氏名の片方だけではキーにはならない）．あるいは，職員に固有のIDが付いている場合には，そのIDがキーになるだろう．

• 主キー

  リレーショナルデータモデルにおいて，ある１個のリレーションスキーマに対して，キーは複数種類ありえる．その中で，データベース管理者が，データベースの管理上最も適当と判断し，かつ，空値をとることがありえないものを，リレーションスキーマの設計時に選んだものが主キーである．あるリレーションスキーマに対して「キー」が１種類しかないときは，それが必然的に主キーになる．

  ※ 主キーとして選ばれたキーの属性あるいは属性の組には，空値を格納することはできないという規則がある

• 外部キー

  リレーションスキーマ R(…, Ai, …), S(…, Bj, …)について，属性BjがリレーションSの主キーであるとする．RとSのインスタンスrおよびsについて，以下の条件が成り立つとき，属性AiをリレーションR の外部キーであるという．

  r中の任意のタップルが持つ Ai の値が，

  　
  1. Sのインスタンスs中のタップルが持つBjの値になっている．あるいは
  2. 空値である．
  　

  ※ 上の定義では，キーや主キーになっている属性の個数が1個だと仮定しているが，実際には，キーや主キーは複数の属性の組であってよい．

• リレーショナルデータモデルでの一貫性制約

  一貫性制約とは，データベースが実世界を正しく反映しているときに満足せねばならない制約条件のこと． 例えば，誕生年として「3009」が記録されているようなデータベースは，とても実世界を正しく反映しているとは言えず，信用出来ない． 誕生年には，例えば，「1889以上2014以下」のような一貫性制約があるはず．但し， 一貫性制約の中身は．データベースの用途によっても変わってくることに注意．今後10年間データベースを使い続けると分かっているなら「1889以上2024以下」のようになるだろう．

  リレーショナルデータモデルでの一貫性制約とは，リレーションスキーマのインスタンスの任意のタップル（言い換えると，リレーションスキーマのインスタンスの各要素）の各成分の値が，満足せねばならない制約条件のこと． リレーショナルデータベース管理システムには，普通，一貫性制約を記述できる機能，記述された一貫性制約に反するような更新を受け付けなくする機能がある． リレーショナルデータモデルでの一貫性制約は種々の種類がある．例示すると下記の通り．

  • キー制約

    主キーであると指定された属性（あるいは属性の組）については，主キーとしての条件を満足せねばならないという制約

  • 外部キー制約

    外部キーであると指定された属性（あるいは属性の組）が，外部キーとしての条件を満足せねばならないという制約

  • 一意制約

    一意であると指定された属性（あるいは属性の組）については，同じ値が2回以上現れないという制約．

  • 非空制約

    非空であると指定された属性は，空値（NULL）になってはいけないという制約

  • ドメイン制約

    テーブルのタップルの各成分の値は，対応する属性のドメインの要素でなければならないという制約． （例）「試験の点数」という属性のドメインが「{x|xは0以上100以下の整数}」であると定められているとき，試験の点数の値が，この範囲外になってはいけない．

  • 参照整合性制約 (referential integrity)

    外部キーであると指定された属性（あるいは属性の組）が，外部キーとしての条件を満足せねばならないという制約

• SQL SQL は，リレーショナルデータベース言語の国際標準である． 平易な英文として，簡単に読み下せるようになっている．
  • 単純にリレーショナル代数やリレーショナル論理を忠実にデータベース言語化したものではなく，平易な英文として，簡単に読み下せるようになっている．
  • リレーショナル代数やリレーショナル論理には無い演算として，集約演算などがある．
  • リレーショナル代数やリレーショナル論理がリレーショナルデータベースの操作を体系化しているのに対して，SQL は，リレーショナルデータベースのデータ操作以外にも，データベーススキーマの定義，トランザクション管理などの機能を持つ．

  ※　SQL では「リレーション」ではなく「テーブル」を扱うことに注意して欲しい．「リレーション」と「テーブル」はともに2次元の表の形になっているという意味では似ている．「リレーション」はドメインの直積の有限部分集合なので，同じタップルが重複して現れることはない．一方で，「テーブル」は，同じ値をもった行が複数回現れることが許される．

• SQLite

  SQLite とは，演習で使用するデータベース管理システムの名前である．

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

  create table  (  [ ...], ...);
  

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

• 　SQL 列制約 (column-constraint) の例

  create-table-statement の中に含める制約など指定のこと．制約の代表的なものを下に列挙する．

  • primary key ・・・ 主キー
  • not null ・・・ 非空
  • unique ・・・ 一意
  • references ・・・ 参照整合性制約
  • check () ・・・ 更新時にチェックされる式 (expression)
• 　SQL テーブル制約 (table-constraint) の例

  create-table-statement の中に含める制約のうち複数の属性に関わるものは table-constraint の形で記述することになる．代表的なものを下に列挙する．

  • primary key(, ...)
  • unique(, ...)
  • check()
  • foreign key () references (, ...) ・・・ 参照整合性制約（つまり外部キー）
  　

演習で行うこと

• SQLite 3 データベースの新規作成 (Create a new SQLite database)

  この資料では, データベース名 は次の通り

  • Windows の場合: C:\SQLite\mydb
  • Ubuntu の場合: ~/mydb
• SQL を用いたテーブル定義と制約の記述 (Table defintion and constraint specification using SQL)

  リレーショナル・スキーマ (relational schema): scores(id, name, teacher_name, student_name, score, created_at, updated_at)

  SQL プログラム:

  create table scores (
      id            integer  primary key autoincrement not null,
      name          text     not null,
      teacher_name  text     not null,
      student_name  text     not null,
      score         integer  not null check ( score >= 0 AND score <=100 ),
      created_at    datetime not null,
      updated_at    datetime,
      unique (name, student_name) );
  

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

  begin transaction;
  insert into scores values( 1, 'Database',    'K', 'KK', 85, datetime('now', 'localtime'), NULL );
  insert into scores values( 2, 'Database',    'K', 'AA', 75, datetime('now', 'localtime'), NULL );
  insert into scores values( 3, 'Database',    'K', 'LL', 90, datetime('now', 'localtime'), NULL );
  insert into scores values( 4, 'Programming', 'A', 'KK', 85, datetime('now', 'localtime'), NULL );
  insert into scores values( 5, 'Programming', 'A', 'LL', 75, datetime('now', 'localtime'), NULL );
  commit;
  

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

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

  SELECT * FROM scores;
  SELECT * FROM scores WHERE name = 'Database';
  SELECT score FROM scores WHERE name = 'Database';
  

• SQL を用いたテーブル定義と制約の記述 (Table defintion and constraint specification using SQL)

  リレーショナル・スキーマ (relational schema): orders(id, year, month, day, customer_name, product_id, qty, created_at)

  SQL プログラム:

  create table orders (
      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_id    text  not null,
      qty           integer  not null default 1 check ( qty > 0 ),
      created_at    datetime not null );
  

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

  begin transaction;
  insert into orders values( 1, 2022, 1, 26,  'kaneko',   1, 10, datetime('now', 'localtime') );
  insert into orders values( 2, 2022, 1, 26,  'miyamoto', 2, 2, datetime('now', 'localtime') );
  insert into orders values( 3, 2022, 1, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
  insert into orders values( 4, 2022, 1, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
  commit;
  

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

  SELECT * FROM orders;
  SELECT * FROM orders WHERE day = 26;
  SELECT * FROM orders WHERE customer_name = 'kaneko';
  SELECT * FROM orders WHERE qty > 8;
  

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

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

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

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

1. SQLite 3のデータ型

   データ型の種類は，データベース管理システムごとに違う． SQLite では，NULL, integer, real, char, text, datetime, bool, BLOB などのデータ型を扱うことができる。 詳しい説明は https://www.sqlite.org/datatype3.html にある．要点は下の通り．

   • NULL: 空値 (a NULL value)
   • integer: 符号付きの整数 (signed integer) ※ SQLite では BIGINT と書いても integer と書いても同じ「８バイトの整数」という意味
   • real: 浮動小数点値 (floating point value)
   • char, STRONG>text: 文字列 (text string) ※ char(n) のように，最大の長さを指定することもある．
   • datetime: 日付や時刻など

     ※ 日時を示す datetimeについて．SQLite では datetime と書いても text と書いても同じ「可変長文字列」の意味．だけど使い分けるべき（その方が分かりやすい）

   • <bool: ブール値
   • BLOB: バイナリ・ラージ・オブジェクト (Binary Large Object). 入力がそのままの形で格納される (stored exactly as it was inpu ※ SQLite では LARGEBLOB と書いても BLOB と書いても同じ「長大なバイナリ・ラージ・オブジェクト」という意味

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

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 の中に含める一貫性制約やデフォルト値の指定

   • 一貫性制約
     • primary key ・・・ 主キー
     • not null ・・・ 非空
     • unique ・・・ 一意
     • references <foreign-table> (<column-name>, ...) ・・・ 参照整合性制約
     • check (<expression>) ・・・ 更新時にチェックされる式 ( expression) ※ SQLite 固有の機能
   • デフォルト値，自動インクリメント
     • default (<expression>) ・・・ デフォルト値 (default value) の指定
     • autoincrement ・・・ 自動インクリメント (auto increment)
5. SQL テーブル制約 (table-constraint) の例

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

   • primary key(<indexed-column>, ...)
   • unique(<indexed-column>, ...)
   • check(<expression>)
6. SQL 挿入文 (insert statement) の例

   テーブルへの行の挿入

   • insert into <table-name> values (<expression>, ...);
   • insert into <table-name> (<column-name>, ...) values ( <expression>, ...);
7. begin transaction, commit, ROLLBACK

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

   • commit: ・・・ 「begin transaction;」以降の全てのデータベース更新操作を確定したいとき
   • ROLLBACK: ・・・ 「begin transaction;」以降の全てのデータベース更新操作を破棄したいとき
8. SQL の SELECT, FROM, WHERE の例

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

   • SELECT * FROM <table-name>;

     table-name で指定したテーブルの全ての行を表示

   • SELECT * FROM <table-name> WHERE <expression>;

     table-name で指定したテーブルのうち expression で指定した条件を満足する行だけを抽出して表示

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

Windows の場合

1. 前もって Windows で，SQLite 3 データベース・ディレクトリ C:\SQLite を新規作成しておく
   
2. SQLite 3の起動 (Start the SQLite 3).

   ※ 「sqlite3.exe が無いよ！」というときは SQLite 3 をダウンロードし，sqlite3.exe を準備 する．

   
3. SQLite 3の画面が開くので確認する
   
4. ヘルプの表示 (display the help)

   「.help」で，ヘルプが表示されるので確認する．

   
5. SQLite 3の終了 (End SQLite)

   「.exit」で終了．

   

Ubuntu の場合

1. 端末を開く (Open a Terminal)
   
2. SQLite 3 データベース・ディレクトリに移る．(Move to the database directory).

   ※ ホームディレクトリを SQLite 3 データベース・ディレクトリとして使う場合には、 何も操作する必要はない

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

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

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

   sqlite3 mydb
   

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

   「.help」で，ヘルプが表示されるので確認する．

   
5. SQLite 3の終了 (End SQLite)

   「.exit」で終了．

   

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

前準備： SQLite 3 のインストールを終えていること

SQL を用いて，scores テーブルを定義し，制約を記述する．(Define 'scores' table and specify constrants of the table using SQL)

リレーショナル・スキーマ (relational schema): scores(id, name, teacher_name, student_name, score, created_at, updated_at)

create table scores (
    id            integer  primary key autoincrement not null,
    name          text     not null,
    teacher_name  text     not null,
    student_name  text     not null,
    score         integer  not null check ( score >= 0 AND score <=100 ),
    created_at    datetime not null,
    updated_at    datetime,
    unique (name, student_name) );

Windows の場合

1. SQLite 3の起動 (Start the SQLite 3).

   ※ 「sqlite3.exe が無いよ！」というときは SQLite 3 をダウンロードし，sqlite3.exe を準備 する．

   
2. 「.open --new」で，SQLite 3 データベースの新規作成
   
3. 次に 「create table ...」を打ち込んで，テーブル定義を行う
   • 「空白」は半角の空白にする　（全角の空白は使わない）
   • 空白を２個以上入れているのは読みやすさのため。数は何個でも良い（１個でもよい、２個でもよい、３個でもよい
   
4. SQLite 3 データベースファイル mydb の確認．(At the time, database file is generated)
   

   ◆ 「create table ...」をうちこむとき、間違ってしまったとする。今回の演習では、次の方法で回避して欲しい．

   1. 「text」を間違って「test」にしてしまった．

      Enterキーを何度押しても、入力モードから抜け出すことができない（システ ムからの反応がないので慌てそうになる）

      
   2. 半角の「;」、「Enter キー」の順に操作する．すると「syntax error」のメッセージが表示される． いま打ち込んだ「create table scores ( name TEST not null,」は破棄される
      
   3. やり直す。カーソルキーの「↑」と 「 ↓」 を活用しよう。過去に入力したコマンドを行単位で呼び出せるので便利．

      再度使いたいコマンドが出てきたら「Enter キー」を押す

      
   4. カーソルキーの「↑」と 「 ↓」 を活用して、間違いのあった行を（「name TEST not null,」）を出している。まだ Enter キーは押さない．
      
   5. カーソルキーの「←」と 「 →」 を使いカーソルを間違いのあった場所に動かす。 Del キーや BackSpace キーも使い、間違いを修正． 修正が終わったら Enter キー．
      

   ◆ テーブル定義を消してやり直したいときは、テーブルを駆除するために「drop table ＜テーブル名＞;」と操作する

   

Ubuntu の場合

1. 端末で、sqlite3 mydbを実行。「3」や「mydb」を忘れやすいので注意。

   sqlite3 mydb
   

   
2. 次に 「create table ...」を打ち込んで，テーブル定義を行う
   • 「空白」は半角の空白にする　（全角の空白は使わない）
   • 空白を２個以上入れているのは読みやすさのため。数は何個でも良い（１個でもよい、２個でもよい、３個でもよい
   
3. 別の端末を開く
4. データベースファイル mydb ができることを確認する．(At the time, database file is generated)

   ls -al
   

   

   ◆ 「create table ...」をうちこむとき、間違ってしまったとする。今回の演習では、次の方法で回避して欲しい．

   1. 「text」を間違って「TEST」にしてしまった（間違った）．Enterキーを何度押しても、入力モードから抜け出すことができない（システ ムからの反応がないので慌てそうになる）
      
   2. 半角の「;」、「Enter キー」の順に操作する．すると「syntax error」のメッセージが表示される． いま打ち込んだ「create table scores ( name TEST not null,」は破棄される
      
   3. やり直す。カーソルキーの「↑」と 「 ↓」 を活用しよう。過去に入力したコマンドを行単位で呼び出せるので便利．

      再度使いたいコマンドが出てきたら「Enter キー」を押す

      
   4. カーソルキーの「↑」と 「 ↓」 を活用して、間違いのあった行を（「name TEST not null,」）を出している。まだ Enter キーは押さない
      
   5. カーソルキーの「←」と 「 →」 を使いカーソルを間違いのあった場所に動かす。 Del キーや BackSpace キーも使い、間違いを修正． 修正が終わったら Enter キー．
      

   ◆ テーブル定義を消してやり直したいときは、テーブルを駆除するために「drop table ＜テーブル名＞;」と操作する

   

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

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

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

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

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

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

begin transaction;
insert into scores values( 1, 'Database',    'K', 'KK', 85, datetime('now', 'localtime'), NULL );
insert into scores values( 2, 'Database',    'K', 'AA', 75, datetime('now', 'localtime'), NULL );
insert into scores values( 3, 'Database',    'K', 'LL', 90, datetime('now', 'localtime'), NULL );
insert into scores values( 4, 'Programming', 'A', 'KK', 85, datetime('now', 'localtime'), NULL );
insert into scores values( 5, 'Programming', 'A', 'LL', 75, datetime('now', 'localtime'), NULL );
commit;

◆ 操作手順の例

1. 最初にbegin transaction; を実行 (Issue "begin transaction" before database update).

   末尾の「;」を忘れないこと

   begin transaction;
   

   

   ※ 末尾の「;」を忘れたとする．このとき、Enterキーを何度押しても、入力モードから抜け出すことができない（システムからの反応がないので慌てそうになる）

   

   半角の「;」、「Enter キー」の順に操作する． これで「begin transaction」のコマンドが受け付けられる．

   
2. 行の挿入

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

   insert into scores values( 1, 'Database',    'K', 'KK', 85, datetime('now', 'localtime'), NULL );
   insert into scores values( 2, 'Database',    'K', 'AA', 75, datetime('now', 'localtime'), NULL );
   insert into scores values( 3, 'Database',    'K', 'LL', 90, datetime('now', 'localtime'), NULL );
   insert into scores values( 4, 'Programming', 'A', 'KK', 85, datetime('now', 'localtime'), NULL );
   insert into scores values( 5, 'Programming', 'A', 'LL', 75, datetime('now', 'localtime'), NULL );
   

   

   ※ 何度も似たようなコマンドを繰り返すので、楽をしたい． カーソルキーの「↑」と 「 ↓」 を活用しよう。過去に入力したコマンドを行単位で呼び出せるので便利．

   

   カーソルキーの「←」と 「 →」 , Del キーや BackSpace キーを使い、簡単に変更できる． 変更が終わったら Enter キー．

   

   ※ insert into ... のとき、構文エラーがあって、エラーメッセージが出たとする． 例えば、下の例では、エラーメッセージが出ている．

   

   最初からやり直す必要はない。エラーメッセージが出た行だけをやり直す．

   
3. 行の挿入が全て終わったら commit; を実行する．(Issue "commit" after database update).

   末尾の「;」を忘れないこと

   commit;
   

   

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

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

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

SELECT * FROM scores;

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

SELECT * FROM scores WHERE name = 'Database';

SELECT score FROM scores WHERE name = 'Database';

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

SQL を用いて，orders テーブルを定義し，制約を記述する．(Define 'orders' table and specify constrants of the table using SQL)

リレーショナル・スキーマ (relational schema): orders(id, year, month, day, customer_name, product_id, qty, created_at)

create table orders (
    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_id    text  not null,
    qty           integer  not null default 1 check ( qty > 0 ),
    created_at    datetime not null );

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

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

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

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

begin transaction;
insert into orders values( 1, 2022, 1, 26,  'kaneko',   1, 10, datetime('now', 'localtime') );
insert into orders values( 2, 2022, 1, 26,  'miyamoto', 2, 2, datetime('now', 'localtime') );
insert into orders values( 3, 2022, 1, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
insert into orders values( 4, 2022, 1, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
commit;

◆ 操作手順の例

1. 最初にbegin transaction; を実行 (Issue "begin transaction" before database update).

   末尾の「;」を忘れないこと

   begin transaction;
   

   
2. 行の挿入を行う

   insert into orders values( 1, 2022, 1, 26,  'kaneko',   1, 10, datetime('now', 'localtime') );
   insert into orders values( 2, 2022, 1, 26,  'miyamoto', 2, 2, datetime('now', 'localtime') );
   insert into orders values( 3, 2022, 1, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
   insert into orders values( 4, 2022, 1, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
   

   
3. 行の挿入が全て終わったら commit; を実行する．(Issue "commit" after database update).

   末尾の「;」を忘れないこと

   commit;
   

   

insert into orders values( 1, 2014, 10, 26,  'kaneko',   1, 10, datetime('now', 'localtime') );

insert into orders(id, year, month, day, customer_name, product_id, qty, created_at) values( 1, 2014, 10, 26,  'kaneko',   1, 10, datetime('now', 'localtime') );

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

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

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

SELECT * FROM orders;

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

SELECT * FROM orders WHERE day = 26;

SELECT * FROM orders WHERE customer_name = 'kaneko';

SELECT * FROM orders WHERE qty > 8;

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

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

一意制約 (unique)

begin transaction;
insert into scores values( 6, 'Database', 'K', 'KK', 75, datetime('now', 'localtime'), NULL );
ROLLBACK;

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

主キー制約 (primary key)

begin transaction;
insert into orders values( 3, 2022, 1, 28,  'miyamoto', 4, 1, datetime('now', 'localtime') );
ROLLBACK;

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

非空制約 (not null)

begin transaction;
insert into orders values( 5, 2022, 1, 28, NULL, 4, 1, datetime('now', 'localtime') );
ROLLBACK;

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

その他の一貫性制約

一貫性制約に違反する例

begin transaction;
insert into orders values( 5, 1014, 10, 28,  'miyamoto', 4, 1, datetime('now', 'localtime') );
ROLLBACK;

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

演習問題と解答例

次の問いに答えよ．その後，下記の解答例を確認せよ． Answer the following questions. Then, inspect answers described below.

問い (Questions)

1. SQLite を使い，下記の SQL を評価させなさい (Evaluate the following SQL)

   create table SS (
       name          text     not null,
       score         integer  not null check ( score >= 0 AND score <=100 ),
       student_name  text     not null,
       unique(name, student_name) );
   

   その後，SQLite 3の SQL を使い次のテーブルを作りなさい (Create the following table using SQLite)

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

2. 次の SQL を評価させるとエラーが発生する．確認する　(Examine runtime errors of the following SQL)

   (1)
       create table AA (
           id  integer primary key,
           amount integer not null check ( amount > 1000 ) );
       begin transaction;
       insert into AA values ( 1, 100 );
       ROLLBACK;
   (2)
       create table BB (
           id  integer primary key,
           name text not null,
           course text not null );
       begin transaction;
       insert into BB values ( 1, 'A', NULL );
       ROLLBACK;
   

3. SQLite 3の SQL を使い，下記のリレーションスキーマに合致するテーブルを定義しなさい (Define tables below using SQL)

   (1) employees(id, employeename, street, city)

   (2) companies(id, companyname, city)

   (3) works(employeename, companyname)

解答例 (Answers)

1.  

   insert into SS values('Database', 80, 'KK');
   insert into SS values('Database', 95, 'AA');
   insert into SS values('Database', 80, 'LL');
   insert into SS values('Database', 85, 'KK');
   insert into SS values('Database', 75, 'LL');
   

2. 「insert into AA values ( 1, 100 );」のときに Error: constraint failed が発生する
3.  
   1. employees(id, employeename, street, city)

      create table employees (
        id integer primary key not null,
        employeename text,
        street text,
        city text );
      

   2. company(id, companyname, city)

      create table companies (
        id integer primary key not null,
        companyname text,
        city text );
      
      

   3. works(employeename, companyname)

      create table works (
        employeename text,
        companyname text );