目次
成績表をつくる
しかし、これをそのままデータベースにするのは間違いだ。集計表は、あくまで〈レポート〉（アウトプット）の1形態であって、データベースはありのままの〈生データ〉を保存するものだからだ。
逆に考えると、〈生データ〉を保存しているデータベースからSQL文を使ってデータを取捨選択し、目的にあった〈レポート〉として表示するのがPHPプログラムということになる。
あなたは回収した答案用紙を、1枚ずつ採点していくことだろう。この作業の結果をExcelに入力するとしたら、左図のようになるだろう。これこそが成績の〈生データ〉であり、これをデータベースに格納していけばいい。
正規化
表2では、出席番号、氏名、科目の3つがキーになって、ユニーク行になっている。
ここで、Excelに慣れているあなたは、生徒の氏名や科目名をいちいち入力せずコピー＆ペーストすることだろう。ところが人間のやることだから、コピー＆ペーストのミスが無いとは限らない。
表2では、じつは氏名は必要ない。出席番号と氏名は同じ意味だから、出席番号と科目だけでユニーク行にすることができる。出席番号と氏名の紐付けは、別に児童データを用意することで解決する。児童データの方には、最初の表にあった氏名読みや性別も加えておこう。
さらに、教科名も日本語である必要はない。1：国語、2：算数、3：理科、4：社会――と科目番号を振っておけば、いちいち日本語を入力するミスを防ぐことができる。これも、別に教科データを用意しておく。
これまでに述べた処理を〈正規化〉と呼ぶ。正規化には第1～第3（または第4）の3段階ないし4段階の手順があるが、すべての手順が必要ないケースもある。詳しくはデータベースの参考書をご覧いただきたい。
ここで覚えておいてほしいのは、リアルワールドをバーチャルワールドに展開するために、冗長な部分を取り除き、正規化する作業が必要だということだ。
スコープ（範囲）
このデータベースを来年度も運用するのなら、2022年度3学期の期末テストであることを明らかにする試験番号を加えておく必要がある。
また、このデータベースを学年や学校全体で運用するのなら、学年やクラスも必要だろう。
ここでは、5年3組で行った2022年度3学期の期末テスト（2023年3月実施）というデータを加え、表4のようなデータにした。表4の1行は、この学校の中で、年度を問わずユニークな行になるから、学校データベースとして運用することができる。
最後に、氏名や教科名を紐付けるのに必要となる、児童データ、教科データを掲げておく。
リレーショナルデータベース
| 名前 | データ型 | 内容 |
|---|---|---|
| grade | 整数 | 学年 |
| class | 整数 | クラス |
| id | テキスト | 試験番号 |
| id_student | 整数 | 出席番号 |
| id_subject | 整数 | 教科番号 |
| score | テキスト ※欠席は空文字にするので文字列型 | 得点 |
| 名前 | データ型 | 内容 |
|---|---|---|
| grade | 整数 | 学年 |
| class | 整数 | クラス |
| id | 整数 | 出席番号 |
| student | テキスト | 氏名 |
| kana | テキスト | 氏名よみ |
| sex | テキスト | 性別 |
| 名前 | データ型 | 内容 |
|---|---|---|
| id | 整数 | 教科番号 |
| subject | テキスト | 教科名 |
テーブル TableScore の項目 grade, class, id_student と MasterStudent の項目 grade, class, id が紐付いて、児童の情報を与える。
この関係を示したのが左図である。
このように、複数のテーブルの項目を紐付けたものをリレーショナルデータベースと呼ぶ。
リアルワールドの情報をバーチャルワールドのデータとして正規化すると、たいていの場合、リレーショナルデータベースの形になる。
参照される側のテーブルを、とくにマスター（マスターテーブル）と呼ぶ。ここでは、MasterStudent と MasterSubject がマスターである。マスターの更新頻度は通常のテーブルに比べて少ない。
ExcelのVLOOKUP関数を使ってできないことはないのだが、この場合、学年、クラス、出席番号の3つが数値データとして重なってしまうため、元データを加工する必要がある。
リレーショナルデータベースを使うことになったら、Excelに見切りを付けて、このコーナーで使っている SQLite、もしきは MySQL、PostgreSQL、Oracle、SQL Server などのRDBMS（リレーショナルデータベース・マネジメントシステム）の導入を考えよう。OracleやSQL Serverは市販RDBMSであるが、機能制約はあるものの無償版が配布されている。
サンプル・プログラムの実行例
サンプル・プログラム
| reportCard1.php | サンプル・プログラム本体。 |
| Report_TableScore.csv | 成績表 |
| Report_MasterStudent | 児童マスタ |
| Report_MasterSubject | 教科マスタ |
解説：初期値
45: //表示幅（ピクセル）
46: define('WIDTH', 600);
47:
48: //SQLite DBファイル名：各自の環境に合わせて変更すること
49: define('DBFILE', 'reportCard1.sqlite3');
50:
51: //CSVファイル名
52: define('CSV_MASTER_STUDENT', 'Report_MasterStudent.csv'); //児童マスタ
53: define('CSV_MASTER_SUBJECT', 'Report_MasterSubject.csv'); //教科マスタ
54: define('CSV_TABLE_SCORE', 'Report_TableScore.csv'); //テスト結果
55:
56: //実行するSQL
57: define('PRE_INSERT_STUDENT', 'INSERT INTO master_student (grade, class, id, student, kana, sex) VALUES (:grade, :class, :id, :student, :kana, :sex)');
58: define('PRE_INSERT_SUBJECT', 'INSERT INTO master_subject (id, subject) VALUES (:id, :subject)');
59: define('PRE_INSERT_SCORE', 'INSERT INTO table_score (grade, class, id, id_student, id_subject, score) VALUES (:grade, :class, :id, :id_student, :id_subject, :score)');
60:
61: define('SQL_SELECT_SUBJECT', 'SELECT * FROM master_subject WHERE 1');
62: define('SQL_SELECT_SCORE', 'SELECT table_score.grade, table_score.class, id_student, student, id_subject, subject, score FROM table_score INNER JOIN master_student, master_subject ON table_score.grade = master_student.grade AND table_score.class = master_student.class AND table_score.id_student = master_student.id AND table_score.id_subject = master_subject.id ORDER BY id_student, id_subject');
63:
64: /**
解説：内部結合
リレーションを張るために、SQL文の INNER JOIN句を使った内部結合を行う。
SELECT table_score.grade, table_score.class, id_student, student, id_subject, subject, score
FROM table_score
INNER JOIN master_student, master_subject
ON table_score.grade = master_student.grade
AND table_score.class = master_student.class
AND table_score.id_student = master_student.id
AND table_score.id_subject = master_subject.id
ORDER BY id_student, id_subject
長いSQL文であるが、基本的は、これまで説明した SELECT文であり、抽出対象テーブルは FROM句で示す table_score である。
つまり、左図のように、master_student と table_score の指定したカラムが一致している要素を積集合で表し、この積集合の部分を取り出すのが INNER JOIN句である。
上のSQL文のように INNER JOIN句の中で、一致するカラム名をイコール = で結んでやると積集合を求めることができる。
解説：テーブルの存在チェック
162: /**
163: * テーブルの存在チェック
164: * @param string $table テーブル名
165: * @param string $errmsg エラーメッセージ格納用
166: * @return bool TRUE：存在する／FALSE：しない
167: */
168: function isTable($table) {
169: $res = FALSE;
170: try {
171: $pdo = new PDO('sqlite:' . DBFILE);
172: $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
173: $sql = 'SELECT * FROM ' . $table;
174: $query = $pdo->query($sql);
175: $res = TRUE;
176: } catch (PDOException $e) {
177: $res = FALSE;
178: }
179: $pdo = NULL;
180:
181: return $res;
182: }
解説：テーブル作成＋CSVファイルからのデータ読み込み
184: /**
185: * 初期化：テーブル作成＋CSVファイルからのデータ読み込み
186: * @param string $errmsg エラーメッセージ格納用
187: * @return bool TRUE：成功／FALSE：失敗
188: */
189: function createTables(&$errmsg) {
190: $res = FALSE;
191: try {
192: $pdo = new PDO('sqlite:' . DBFILE);
193: $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
194:
195: //児童マスタ
196: //id:出席番号, name:氏名，kana:よみ，sex:性別
197: $table = 'master_student';
198: if (! isTable($table, $errmsg)) {
199: $pdo->exec('CREATE TABLE master_student (
200: grade INTEGER,
201: class INTEGER,
202: id INTEGER PRIMARY KEY,
203: student TEXT,
204: kana TEXT,
205: sex TEXT
206: )');
207: try {
208: $file = new SplFileObject(CSV_MASTER_STUDENT);
209: $file->setFlags(SplFileObject::READ_CSV);
210: $row = 0;
211: foreach ($file as $line) {
212: //空行、先頭行は読み飛ばし
213: if (! isset($line[5]) || ($row == 0)) {
214: $row++;
215: continue;
216: }
217: //データの挿入
218: $stmt = $pdo->prepare(PRE_INSERT_STUDENT);
219: $stmt->bindValue(':grade', $line[0], PDO::PARAM_INT);
220: $stmt->bindValue(':class', $line[1], PDO::PARAM_INT);
221: $stmt->bindValue(':id', $line[2], PDO::PARAM_INT);
222: $stmt->bindValue(':student', $line[3], PDO::PARAM_STR);
223: $stmt->bindValue(':kana', $line[4], PDO::PARAM_STR);
224: $stmt->bindValue(':sex', $line[5], PDO::PARAM_STR);
225: $stmt->execute();
226: $row++;
227: }
228: $res = TRUE;
229: $file = NULL;
230: } catch (Exception $e) {
231: $errmsg = CSV_MASTER_STUDENT . ' を読み込むことができません';
232: $res = FALSE;
233: $pdo = NULL;
234: return $res;
235: }
236: }
237:
238: //テーブル作成：教科マスタ
239: //id:教科ID, name:教科名
240: $table = 'master_subject';
241: if (! isTable($table, $errmsg)) {
242: $pdo->exec('CREATE TABLE master_subject (
243: id INTEGER PRIMARY KEY,
244: subject TEXT
245: )');
246: try {
247: $file = new SplFileObject(CSV_MASTER_SUBJECT);
248: $file->setFlags(SplFileObject::READ_CSV);
249: $row = 0;
250: foreach ($file as $line) {
251: //空行、先頭行は読み飛ばし
252: if (! isset($line[1]) || ($row == 0)) {
253: $row++;
254: continue;
255: }
256: //データの挿入
257: $stmt = $pdo->prepare(PRE_INSERT_SUBJECT);
258: $stmt->bindValue(':id', $line[0], PDO::PARAM_INT);
259: $stmt->bindValue(':subject', $line[1], PDO::PARAM_STR);
260: $stmt->execute();
261: $row++;
262: }
263: $res = TRUE;
264: $file = NULL;
265: } catch (Exception $e) {
266: $errmsg = CSV_MASTER_SUBJECT . ' を読み込むことができません';
267: $res = FALSE;
268: $pdo = NULL;
269: return $res;
270: }
271: }
272:
273: //テーブル作成：テスト結果
274: //id_name:出席番号, id_subject:教科ID, score:点数
275: $table = 'table_score';
276: if (! isTable($table, $errmsg)) {
277: $pdo->exec('CREATE TABLE table_score (
278: grade INTEGER,
279: class INTEGER,
280: id TEXT,
281: id_student INTEGER,
282: id_subject INTEGER,
283: score TEXT
284: )');
285: try {
286: $file = new SplFileObject(CSV_TABLE_SCORE);
287: $file->setFlags(SplFileObject::READ_CSV);
288: $row = 0;
289: foreach ($file as $line) {
290: //空行、先頭行は読み飛ばし
291: if (! isset($line[2]) || ($row == 0)) {
292: $row++;
293: continue;
294: }
295: //データの挿入
296: $stmt = $pdo->prepare(PRE_INSERT_SCORE);
297: $stmt->bindValue(':grade', $line[0], PDO::PARAM_INT);
298: $stmt->bindValue(':class', $line[1], PDO::PARAM_INT);
299: $stmt->bindValue(':id', $line[2], PDO::PARAM_STR);
300: $stmt->bindValue(':id_student', $line[3], PDO::PARAM_INT);
301: $stmt->bindValue(':id_subject', $line[4], PDO::PARAM_INT);
302: $stmt->bindValue(':score', $line[5], PDO::PARAM_STR);
303: $stmt->execute();
304: $row++;
305: }
306: $res = TRUE;
307: $file = NULL;
308: } catch (Exception $e) {
309: $errmsg = 'ファイル "' . CSV_TABLE_SCORE . '" を読み込むことができません';
310: $res = FALSE;
311: $pdo = NULL;
312: return $res;
313: }
314: }
315: $res = TRUE;
316: } catch (PDOException $e) {
317: $pdo = NULL;
318: $errmsg = 'データベース "' . DBFILE . '" を作成できません';
319: $res = FALSE;
320: }
321:
322: $pdo = NULL;
323: return $res;
324: }
解説：成績表を作成する
326: /**
327: * 成績表を作成する
328: * @param string $errmsg エラーメッセージ格納用
329: * @return string 表示用文字列(HTML)
330: */
331: function selectItems(&$errmsg) {
332: try {
333: $pdo = new PDO('sqlite:' . DBFILE);
334: $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
335:
336: //科目
337: $str = '';
338: foreach ($pdo->query(SQL_SELECT_SUBJECT) as $row) {
339: $str .= '<th>' . $row['subject'] . "</th>\n";
340: }
341:
342: $html =<<< EOT
343: <table class="stripe_table">
344: <tr class="index">
345: <th>出席番号</th>
346: <th>名前</th>
347: {$str}
348: <th>合計</th>
349: <th>平均</th>
350: </tr>
351:
352: EOT;
353:
354: $score = array();
355: $col = 0;
356: foreach ($pdo->query(SQL_SELECT_SCORE) as $row) {
357: if ($col % 4 == 0) {
358: $html .=<<< EOT
359: <tr>
360: <td class="index">{$row['id_student']}</td>
361: <td class="name">{$row['student']}</td>
362:
363: EOT;
364: }
365: $html .=<<< EOT
366: <td class="index">{$row['score']}</td>
367:
368: EOT;
369: if ($row['score'] != '') {
370: $score[$row['id_student']][$col % 4] = (int)$row['score'];
371: }
372: if ($col % 4 == 3) {
373: $sum = array_sum($score[$row['id_student']]);
374: $avg = sprintf('%.1f', array_sum($score[$row['id_student']]) / count($score[$row['id_student']]));
375: $html .=<<< EOT
376: <td class="index">{$sum}</td>
377: <td class="index">{$avg}</td>
378: </tr>
379:
380: EOT;
381: }
382: $col++;
383: }
384: //科目毎の集計
385: $score2 = array();
386: foreach ($score as $id=>$arr) {
387: foreach ($arr as $key=>$val) {
388: $score2[$key][$id] = $val;
389: }
390: $score2[999][$id] = array_sum($arr);
391: }
392: //科目毎の合計
393: $html .=<<< EOT
394: <tr>
395: <td class="index"> </td>
396: <td class="name">合計</td>
397:
398: EOT;
399: foreach ($score2 as $val) {
400: $sum = array_sum($val);
401: $html .=<<< EOT
402: <td class="index">{$sum}</td>
403:
404: EOT;
405: }
406: //科目毎の平均
407: $html .=<<< EOT
408: <td class="index"> </td>
409: </tr>
410: <tr>
411: <td class="index"> </td>
412: <td class="name">平均</td>
413:
414: EOT;
415: foreach ($score2 as $val) {
416: $avg = sprintf('%.1f', array_sum($val) / count($val));
417: $html .=<<< EOT
418: <td class="index">{$avg}</td>
419:
420: EOT;
421: }
422: $avg = sprintf('%.1f', $avg / 4);
423: $html .=<<< EOT
424: <td class="index">{$avg}</td>
425: </tr>
426: </table>
427:
428: EOT;
429:
430: } catch (PDOException $e) {
431: $pdo = NULL;
432: $errmsg = 'データベース "' . DBFILE . '" をオープンできません';
433: $html = FALSE;
434: }
435:
436: $pdo = NULL;
437: return $html;
438: }
参考サイト
- SQLiteを用意する：ぱふぅ家のホームページ
現実世界（リアルワールド）の情報を、可能な限り忠実にコンピュータの中の仮想世界（バーチャルワールド）のデータとして構築するとき、リレーショナルデータベースが役に立つ。
最近話題の DX（Digital Transformation）は、リアルワールドの情報をバーチャルワールドのデータに変換し、その処理を可能な限り自動化することを目指しており、SQL文が不要になるとしても、リレーショナルデータベースの考え方が非常に役に立つ。