演習

演習で行うこと

• Sqliteman の起動と終了 (Start Sqliteman)
• Sqliteman で既存のデータベースを開く (Open an existing database using Sqliteman)
• SQL を用いたテーブル定義と一貫性制約の記述 (Table defintion and integrity 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形式．

• Sqliteman を用いたデータのブラウズ (Browse Data using Sqliteman)
• DISTINCT を含む SQL の例 (SQL with DISTINCT)

  SELECT DISTINCT name, teacher_name
  FROM scores;
  
  SELECT name, teacher_name
  FROM scores;
  
  SELECT DISTINCT score
  FROM scores;
  
  SELECT score
  FROM scores;
  

• SQL を用いたテーブルの分解 (table decomposition using SQL)

  create table A AS
  select distinct name, teacher_name
  FROM scores;
  
  create table B AS
  select distinct id, name, student_name, score
  FROM scores;
  

• 結合問い合わせ (join query)

  SELECT B.id, A.name, A.teacher_name, B.student_name, B.score
  FROM A, B
  WHERE A.name = B.name;
  

• 情報無損失分解にならない分解 (Inspect lossless decomposition)

  create table C AS
  select distinct name, student_name
  FROM scores;
  
  create table D AS
  select distinct id, teacher_name, student_name, score
  FROM scores;
  
  SELECT D.id, C.name, D.teacher_name, C.student_name, D.score
  FROM C, D
  WHERE C.student_name = D.student_name;
  

SQLite 3 の SQL 演習に関連する部分

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

• select distinct ...

  同一の値を持つ行が複数あるとき，1つを残して除去する

• create table AS <table-name> SELECT ...

  SQL 問い合わせ結果から新しいテーブルを作る

Sqliteman の起動と終了 (Start Sqliteman)

1. Sqliteman の起動 (Start Sqliteman)

   * Ubuntu での SQLiteman の起動例

   「プログラミング」→「Sqliteman」と操作する．

   

   SQLiteman の新しいウインドウが開く．(A New window appears)

   

   * Lubuntu での SQLiteman の起動例

   「プログラミング」→「Sqliteman」と操作する

   

   SQLiteman の新しいウインドウが開く．(A New window appears)

   

   * Windows での SQLiteman の起動例

   「Sqliteman」 のアイコンをダブルクリック (double click "Sqliteman.exe")

   

   Sqliteman のウインドウが開く．(A New window appears)

   

SQLiteman で新しいデータベースを作成する (Create a new database)

前回の授業で作成したデータベースを使うときは、ここの手順は行わなくて良い

以下の手順で，新しいデータベースを作成する．その結果，データベースファイルができる． (Create a new database)

1. 「File」→ 「New」
2. データベースの新規作成を開始する (Start a new databae creation)

   データベースファイル名 を指定

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

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

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

1. 「File」→ 「Open」
   
2. データベースファイルを開く (Open Database File)

   * Ubuntu での実行例（「mydb」を開く場合）

   データベースファイル mydb を選び， 「開く」をクリック (Click '開く' after choosing the database file "mydb")

   

   * Windows での実行例（「C:\SQLite\mydb」を開く場合）

   データベースファイル C:\SQLite\mydb を選び， 「開く」をクリック (Click '開く' after choosing the database file "C:\SQLite\mydb")

   要するに，/home/＜ユーザ名＞/SQLite 3の下の mydb を選ぶ．　

   

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

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

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

1. scores テーブルの定義 (Define a table)

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

   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 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)

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

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

1. SQL プログラムの記述

   「insert into ...」は行の挿入．ここには 5つの SQL 文を書き， 「begin transaction」と「commit」で囲む． ("insert into ..." means inserting a row into a table. Four SQL statements are wrote).

   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;
   

   
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)

• scores テーブル

  まず，オブジェクト・ブラウザ (Object Browser) の中の「Tables」を展開 (Click 'Tables')

  

  次に，テーブル scoresを選ぶ (Select table 'scores'). テーブル scoresが表示される (table 'scores' appears)

  

  * もし，データに間違いがあれば，このウインドウで修正できる (If you find any mistakes, you can modify the data using this window).

DISTINCT を含む SQL の例 (SQL with DISTINCT)

DISTINCT を付ける (with DISTINCT)

SELECT DISTINCT name, teacher_name
FROM scores;

DISTINCT を付けない (without DISTINCT)

SELECT name, teacher_name
FROM scores;

DISTINCT を付ける (with DISTINCT)

SELECT DISTINCT score
FROM scores;

DISTINCT を付けない (without DISTINCT)

SELECT score
FROM scores;

SQL を用いたテーブルの分解 (table decomposition using SQL)

scores( name, teacher_name, student_name, score )を 2つのテーブル

• A(name, teacher_name)
• B(name, student_score, score)

に分解．テーブルを分解するときは重複行を除去するという規則があることに注意．DISTINCTを使う．

'scores' table is decomposed into two tables A and B. Duplicate rows in A and B are eliminated when decomposition (because of the definition of "table decomposition"). Use 'DISTINCT'.

SQL の実行 (Execute SQL)

create table A AS
select distinct name, teacher_name
FROM scores;

データのブラウズ機能を使いテーブル A を確認 (Browse Table A)

SQL の実行 (Execute SQL)

create table B AS
select distinct id, name, student_name, score
FROM scores;

データのブラウズ機能を使いテーブル B を確認 (Browse Table B)

結合問い合わせ (join query)

元のテーブル scores が，分解後の2つのテーブル A, B から復元できることを確認しておこう． テーブル scores は，テーブル A と B から復元できるから，データベース設計として．

• テーブル scores をデータベースに格納するか，あるいは，
• テーブル scores でなく，テーブル A と B をデータベースに格納する

という 2つの案がありえることになる．（では，どちらが良いのか？　今度の授業で明らかにしていく）

Examine that the original table 'scores' can be constructed from the two tables A and B. It means that there are two alternatives in database design.

1. Store 'scores' into database, or
2. Store 'A' and 'B' instead of 'scores' in database.

SELECT B.id, A.name, A.teacher_name, B.student_name, B.score
FROM A, B
WHERE A.name = B.name;

情報無損失分解にならない分解 (Inspect lossless decomposition)

今度は，テーブルの分解の仕方を変える． 分解後のテーブルから，もとのテーブルに戻らない場合がある． このことを確認しておく．

Table decompositions are not always LOSSLESS.

SQL の実行 (Execute SQL)

create table C AS
select distinct name, student_name
FROM scores;

データのブラウズ (Browse Data)

SQL の実行 (Execute SQL)

create table D AS
select distinct id, teacher_name, student_name, score
FROM scores;

データのブラウズ (Browse Data)

テーブル C, D からは，テーブル scores を構築できない ('scores' table can not be constructed from C and D).

SELECT D.id, C.name, D.teacher_name, C.student_name, D.score
FROM C, D
WHERE C.student_name = D.student_name;