情報システム基礎II（スクーリング前半）

情報システム基礎IIのスクーリング（前半）に参加しました。

講義のメモをもとに書いていますが、調べて追記している部分もあります。

講義の内容

この講義では、主にデータベースの仕組みとデータの取り出し方について学び、最終的にProcessingとSQLiteを使って、ある程度の規模のデータのビジュアライゼーションを行いました。

情報システム基礎IIでは情報システム基礎Iを受講している前提があるからか、Processingによるプログラミングの解説はほとんど無く、データベースの解説と、SQLiteよるリレーショナルDBの操作（ターミナルでの操作）がメインでした。

また、今まで受けた情報系の講義ではWindowsでの作業が多かった印象がありますが、今回はMacでの作業でした。

全体の大まかなスケジュールは以下の通りでした。

１日半で概要説明、データベースについて、SQLについて、Processingについて（使用するライブラリの解説）の講義
２日間で作品制作
最後の半日で講評

１日目

概要説明
過去のスクーリングの作品紹介

データベースが使われる理由

スプレッドシートでの管理と比較し、どのような違いがあるか。

表記揺れ
カテゴリのルール
数値の全角半角
新旧データの混在
編集の衝突

SQLiteとは

RDBMS（リレーショナルDB）に分類されるデータベース
Macには標準でインストールされている
スマートフォンなどに使用されている

その他のRDBMS

Oracle（ヤマト運輸 / 製造関係など）
MySQL（FaceBook / Youtube / Netflix / Spotify / GitHubなど）
PostgreSQL（NEC / NTT / HITACHI / 富士通 / 三菱など）
MariaDB（Google / Mozilla / Wikipediaなど）

リレーショナルデータベース（関係データベース）とは

・コードに紐づいた情報を管理することで、データ変更の手間が減る・フィルタによって情報の抽出が可能

データベースの種類

データベースには大きく分けてRDBMSとNoSQLがあります。NoSQLは更にKey-Value Store型、列指向型、ドキュメント指向型、グラフ指向型があります。

RDBMSは1969年に提唱され、1977年のOracle発売以来古くから使用されていますが、非常に多いデータを活用するためにデータベースが多様化しているそうです。（WEBのアクセスデータ、メッセージアプリなど）

データベースの歴史については下記記事が参考になりました。

qiita.com

こちらにそれぞれの特徴などがまとめられていました。

qiita.com

用語説明

用語はデータベースの種類によって微妙に異なるようですので、ここではSQLiteでの意味をまとめます。

スキーマ（schema）

テーブルの定義や構造のことです。データベース内のテーブルの名称やテーブルの中のカラムの名前、カラムの型、主キーなどの情報です。

テーブル（table）

データベースの中にあるデータの塊です。

カラム（column）

テーブルの中にある列のことです。列はnameやidなどの意味を持ちます。

レコード（record）

テーブルの中にある行のことです。

データベースを見る

Macでは標準でSQLiteがインストールされているので、データベースのファイルがあれば表示することができます。

Terminalを起動（デスクトップ右上の虫眼鏡マークをクリックし、Spotlightでterminalと検索すると表示される）
sqlite3と入力したあとに開きたいデータベースファイルのパスを入力(ファイルをドロップすると自動でパスが入力される)
sqlite> と表示される

テーブル一覧の表示

データベース内に存在するテーブルの名前を列挙します。

.tablesと入力

デーブルの内容の表示

テーブルの中に定義されているカラムの情報を表示します。

.schemaと入力

特定のテーブルの内容を表示する場合は、半角スペースのあとに対象のテーブル名を入力します。.schema shop

グループワーク

5人程度のグループに分かれ、今回使用するデータベースのテーブル間の関連を調べながら、わかりやすいようにまとめる作業を行いました。まとめには模造紙と付箋を使用し、それぞれのグループごとにかんたんに発表を行いました。ここでまとめたものは、SQLを考える際に使用します。

SQL文（クエリ）

SQLとはデータの操作や定義を行うための問い合わせ言語で、新しいテーブルを作ったり、テーブルにデータを格納する際に使用しますが、今回は取り出しのみ学びました。

文字と文字の間は半角スペースで区切る
最後にはセミコロンをつける
大文字小文字は区別されないが、文法とデータを区別するために文法は大文字にした方がわかりやすい
文法が間違っている場合にはError: near "xxx": syntax errorと表示される

SELECT文

指定のテーブルから指定のカラムのデータを取り出します。

SELECT カラム名 FROM テーブル名;

shopテーブル内のnameカラムのデータを取り出し

SELECT name FROM shop;

カラム名にアスタリスク（*）を指定すると、すべてのカラムのデータを取り出します。

shopテーブル内のすべてのカラムのデータを取り出し

SELECT * FROM shop;

カラムを複数指定したい場合にはカンマで区切ります。

shopテーブル内のnameカラムとparkingのデータを取り出し

SELECT name, parking FROM shop;

特定の条件を満たすデータのみ取り出す（条件式）

カラムの中のデータで指定のものだけを取り出す場合、SELECT文にWHERE条件を追加します。

SELECT カラム名 FROM テーブル名 WHERE カラム名 = 条件;

条件には直接数値や文字列を指定したり、カラム名を指定することができます。指定する条件が文字列の場合、ダブルクォーテーション（"）もしくはシングルクォーテーション（'）で囲う必要があります。

stationテーブルからline_codeが11301であるレコードのidとnameを取り出す

SELECT id, name FROM station WHERE line_code = 11301;

複数条件の場合はAND（どちらの条件も満たす）もしくはOR（どちらか、もしくはどちらの条件を満たす）でつなぎます。

stationテーブルからline_codeが11301でidが1であるレコードのidとnameを取り出す

SELECT id, name FROM station WHERE line_code = 11301 AND id = 1;

stationテーブルからline_codeが11301もしくは11302であるレコードのidとnameを取り出す

SELECT id, name FROM station WHERE line_code = 11301 OR line_code = 11302;

一致条件以外にも数値に対して範囲指定ができます。

小なり（<）や、大なり（>）、またはそれらとイコール（=）の組み合わせます。

条件未満

SELECT カラム名 FROM テーブル名 WHERE カラム名 < 条件;

条件を超える

SELECT カラム名 FROM テーブル名 WHERE カラム名 > 条件;

条件以下

SELECT カラム名 FROM テーブル名 WHERE カラム名 <= 条件;

条件以上

SELECT カラム名 FROM テーブル名 WHERE カラム名 >= 条件;

条件以外の指定もできます。

条件以外

SELECT カラム名 FROM テーブル名 WHERE カラム名 != 条件;

もしくは

SELECT カラム名 FROM テーブル名 WHERE カラム名 <> 条件;

それ以外に、LIKEと%や_を組み合わせてあいまい検索もできます。

%は0文字以上の文字列を表し、_は1文字を表しています。'条件%'とした場合は条件のあとに0文字以上の何かしらの文字列が続いていることを表します。0文字以上なので、条件のみでも対象です。 '条件_'とした場合は条件のあとに何かしらの1文字があることを表します。こちらは1文字なので、条件のみの場合は対象外です。'条件___'のように、つなげることで任意の文字数にすることができます。

条件で始まる文字列

SELECT カラム名 FROM テーブル名 WHERE カラム名 LIKE '条件%';

条件で始終わる文字列

SELECT カラム名 FROM テーブル名 WHERE カラム名 LIKE '%条件';

条件を含む文字列

SELECT カラム名 FROM テーブル名 WHERE カラム名 LIKE '%条件%';

テーブル同士の結合

複数のテーブルから関連したデータを取り出す場合、JOINを使用します。ここで使用するのはLEFT JOINです。（shopテーブルにあるfoodの数列が何を示しているのか、foodテーブルから名称を取得する場合など）

ONのあとには、それぞれのテーブルで紐付けるために関連のあるカラムを指定します。

SELECT * FROM テーブル名A LEFT JOIN テーブル名B ON テーブル名A.カラム名 = テーブル名B.カラム名

shopテーブルにfoodテーブルの情報を紐付けて取り出す（shopテーブルのfoodカラムと、foodテーブルのcodeカラムを紐付ける）

SELECT * FROM shop LEFT JOIN food ON shop.food = food.code;

同じ名前のカラムが存在し、そのカラムのデータを取り出したい場合

取り出したい別々のテーブルに同じ名前のカラムがある場合、どのテーブルのカラムかを明示する必要があります。書き方は、テーブル名とカラム名をドットでつなぎます。

SELECT name FROM shop LEFT JOIN food ON shop.food = food.code;としてしまうと、SELECTのあとにあるnameがshopテーブルを示しているのか、foodテーブルのnameを示しているのかわからないため、Error: ambiguous column name: nameとなります。

テーブル名.カラム名

shopテーブルのnameカラムとfoodテーブルのnameカラムを取り出す（shopテーブルのfoodカラムと、foodテーブルのcodeカラムを紐付ける）

SELECT shop.name, food.name FROM shop LEFT JOIN food ON shop.food = food.code;

テーブルやカラムに名前をつける

テーブルやカラムには任意の名前を指定することができます。同じテーブルの組み合わせや、長い名前の省略などに使うことができます。重複する名前や予約語（SQLの内部で使われている文字列）は指定できません。

テーブル名 AS 名前

カラム名 AS 名前

shopテーブルのnameカラムとfoodテーブルのnameカラムを取り出す（shopテーブルのfoodカラムと、foodテーブルのcodeカラムを紐付ける）

SELECT s.name, f.name FROM shop AS s LEFT JOIN food AS f ON s.food = f.code;

ProcessingでSQLiteを使用する

ライブラリを使用する（BezierSQLib）

ProcessingでSQLiteのデータベースを使用する場合、BezierSQLibというライブラリを追加します。

使用するProcessingのpdeファイルがあるディレクトリにdataフォルダを作成する
作成したdataフォルダの中にSQLiteのデータベース（.db）を保存する
SQLite型のグローバル変数を宣言する。

SQLite db;

void setup()
{
}
void draw()
{
}

データベースのファイル名を指定します。

SQLite db;

void setup()
{
    db = new SQLite( this, "shops.db" );
}
void draw()
{
}

データを取り出すためのSQLを指定します。

SQLite db;

void setup()
{
    db = new SQLite( this, "shops.db" );
    db.query("SQL文");
}
void draw()
{
}

ファイルを順番に取り出します。

SQLite db;

void setup()
{
    db = new SQLite( this, "shops.db" );
    db.query("SQL文");
    while (db.next())
    {    
        String data = db.getString("カラム名");
        println(data );
    }
}
void draw()
{
}

問題点として、古いバージョンのSQLiteが使われてるため、長いSQLを使用する場合はデータの取り出しが非常に遅くなることがあるようです。

JDBCドライバを使用する

BezierSQLibでの問題を回避する方法として、SQLite用のJDBCドライバを使用して読み込む方法があります。（講義で説明やサンプルの配布があります）

JDBCとはJava Database Connectivityの略で、Javaからデータベースにアクセスするための標準APIです。

使用するProcessingのpdeファイルがあるディレクトリにdataフォルダを作成する
作成したdataフォルダの中にSQLiteのデータベース（.db）を保存する
使用するProcessingのpdeファイルがあるディレクトリにcodeフォルダを作成する
作成したcodeフォルダの中にJDBCドライバを保存する。（今回使用したファイルはsqlite-jdbc-3.21.0.jar）
ドライバを読み込みます。

import java.sql.*;

void setup()
{
}
void draw()
{
}

データベースの接続部分を記述します。また、接続エラー時の対応部分も記述しておきます。（try~cathch）

import java.sql.*;

void setup()
{

  try
  {
        connection = DriverManager.getConnection("jdbc:sqlite:" + dataPath("shops.db"));
        Statement statement = connection.createStatement();
        statement.setQueryTimeout(30);
  }
  catch(SQLException e)
  {
    System.err.println(e.getMessage());
  }
}
void draw()
{
}

データを取り出すためのSQLを指定します。

import java.sql.*;

void setup()
{

  try
  {
        connection = DriverManager.getConnection("jdbc:sqlite:" + dataPath("shops.db"));
        Statement statement = connection.createStatement();
        statement.setQueryTimeout(30);

        ResultSet rs = statement.executeQuery("SQL文");
  }
  catch(SQLException e)
  {
    System.err.println(e.getMessage());
  }
}
void draw()
{
}

ファイルを順番に取り出します。

import java.sql.*;

void setup()
{

  try
  {
        connection = DriverManager.getConnection("jdbc:sqlite:" + dataPath("shops.db"));
        Statement statement = connection.createStatement();
        statement.setQueryTimeout(30);

        ResultSet rs = statement.executeQuery("SQL文");

        while (rs.next())
        {    
            String data = rs.getString("カラム名");
            println(data );
        }
  }
  catch(SQLException e)
  {
    System.err.println(e.getMessage());
  }
}
void draw()
{
}

構文が大きく変わることはないのですが、読み込みを複数の場所で行っていると変更が大変なので、長いSQLを使用する予定があったり、短時間に何回も読み込む予定があったり、大きなデータを取り出す予定があれば、最初からJDBCドライバで作成したほうが良いと思います。

2日目午前

集計関数

データをそのまま取り出すのではなく、データ数を数えたり、数値データは平均や合計値などを出すことができます。

データの個数を数える

count(カラム名)

shopテーブル内のidのデータを数える

SELECT count(id) FROM shop;

応用例として、GROPU BYと組み合わせると、特定条件ごとのカウントができます。

駅ごとの店舗数を数える

SELECT station, count(id) FROM shop GROUP BY station;

count関数を使用する場合、同じデータがあっても別々に数えます。重複データを取り除いた種類の数を数えたい場合、DISTINCTを使います。

最寄り駅の種類を数える

SELECT count(DISTINCT station) FROM shop;

数値を合計する

対象のカラムが数値の場合、合計したデータを取り出すことができます。

sum(カラム名)

駅からの距離を合計する

SELECT sum(distance) FROM shop;

数値を平均する

合計と同様に、対象のカラムが数値の場合、平均したデータを取り出すことができます。

avg(カラム名)

駅からの距離を平均する

SELECT avg(distance) FROM shop;

これらの関数は、JOINやWHEREとも組み合わせることができますので、複雑な条件での取り出しも可能です。

その他

SQLの構文を検証する場合などで、対象データが多すぎて取り出しに時間がかかってしまうときには、出力データ数を制限することができます。

LIMIT 取り出す数

LIMIT 取り出す位置, 取り出す数

店名を5個だけ取り出す

SELECT name FROM shop LIMIT 5;

店名を5番目の位置から5個取り出す

SELECT name FROM shop LIMIT 4,5;

※順番は0から始まるため、4と指定すると5番目から取り出します。

課題制作にあたっての注意

表現するもの、表現方法

数字を眺めているだけでは見えないもの
普通のグラフでは見えないもの
知っていることをより深く理解できるか
視覚の原理を応用する（ゲシュタルト心理学など）

グラフでの表現方法に気をつける

グラフでの表現は、作者が意図しない場合でも、見る人が勘違いしてしまうことがあるので下記の点などに気をつける。

棒グラフの太さ（太くすると多く見え、細くすると少なく見える）
棒グラフの高さ（スケールを小さくすることで差が小さく見える）
円の大きさでの表現（半径を3倍にすると面積は9倍になる）

2日目午後

2日目の午後からは、制作に入っていきました。この段階ではまだ、アイディアを出したり、方向性を定め、相談したり検証したりしました。

後半に向けての準備

スクーリングの前半と後半の間には3週間空いていますが、その間に企画案（エスキース）の提出と、作品制作の準備をする必要があります。普段の作業環境がWindowsなのでSQliteを使えるように設定を行いました。設定方法は別の記事でまとめています。

artificialarts.hatenablog.com

まず何をしたいかが見つからないと、デザインを考えることも、SQLやプログラムを書くこともできないので、プログラムの制作時間が短くなってしまいます。

情報システム基礎Iではテーマに沿った課題でしたが、今回はもととなるデータから、どのデータを使うか、どのように表現するか考えなければいけないので、より広い範囲を考える必要があります。

時間に余裕があれば、作品の下地になるようなプログラムや、データ収集を行っておくと後半が多少楽になるかと思います。