はじめに
続きです。
今回は、データベースにテーブルを定義し、アプリケーションから利用できるようにします。 利用するデータベースは先の日記に記載のとおり、SQL Serverです。
データベースの作成ツール
SQL Server Express Edition（またはDeveloper Edition）がインストールされた環境で開発を進めます。 データベースの新規作成、テーブルの新規作成は、SSMS(SQL Server Management Studio）から行えます。 作成後のカラムのデータ型変更などもできるので、設計が固まるまではSSMSで作業します。
ER図を試行錯誤で作成しながらテーブルの設計をしましたが、今回はER図は頭の整理のためのスケッチとしての役割に留まっています。
SQL Serverのコマンド環境は、SQL Serverをインストールしたマシンであれば、コマンドプロンプトからsqlcmdコマンドを実行すると利用できます。対話モードとSQLファイルを実行するモードがあります。
データベース作成
データベース領域の作成
テーブルを定義するために、まずデータベースを1個作成します。ここにテーブル群を定義していきます。
スペクトラムデータのテーブル
まずはデータファイルの内容をデータベースのテーブルに定義します。項目としては次を入れます。
- 名前（元のファイル名）
- 周波数の開始値
- 周波数の終了値
- 電力値の点列
かなり絞った項目としています。データの取得日時、取得に使用した計測器・配線などの計測系、取得した信号の素性、などなど盛り込みたい情報があります。計測条件、計測対象、計測記録はそれぞれ別テーブル（別エンティティ）として定義する方針とします。
テーブルのスキーマ（現時点の結果）
| 列名 | データ型 | NOT NULL制約 | 備考 |
|---|---|---|---|
| id | bigint | あり | 主キー（自動インクリメント） |
| name | nvarchar(50) | なし | スペクトラム名 |
| startFrequency | real | あり | 周波数の開始値 |
| stopFrequency | real | あり | 周波数の終了値 |
| spectrum | varbinary(max) | あり | スペクトラム点列 |
先に挙げた項目の他に、idを追加しています。RDBMSでは、レコードを一意に識別する主キーが必要です。名前を主キーにするとしたら、名前は任意に付与できるので一意を保つにはアプリケーション側（ユーザー側）で頑張る必要があります。これは運用がやっかいなので避けたいところです。また、他の項目でレコードを一意に表現できるものはありません。そこで、サロゲートキーを設けることとします。
NOT NULL制約は、データを成立させるのに不可欠な項目に付与します。
idの設計
idは、整数でインサートごとに自動でインクリメントして採番します。データ型は最初int（32bit符号付整数）としていましたが、intだとレコード数が21億（）を超えられないので、多数のデータを保持する可能性のあるテーブルではbigintにしておきます。
自動インクリメントにするには、SSMS上では列のプロパティから、IDENTITYの指定でIDであるを[いいえ]から[はい]に変更します。
nameの設計
スペクトラムデータの名称を保持します。SQL Serverの文字列は、ユニコード文字と非ユニコード文字とそれぞれのデータ型が用意されています。nchar、nvarcharがユニコード文字のデータ型で、char、varcharが非ユニコード文字のデータ型です。
日本語Windows環境でデフォルトで構築した場合、非ユニコードはデフォルト（既定）ではコードページ932（Windows_31J）で扱われます。アプリケーション側の文字コードはユニコードなので、データベース側で非ユニコードのデータ型を使うと文字コード変換が生じていろいろ面倒になりそうです。そこで、ASCII文字に限定できない場合はユニコード文字を扱うデータ型とします。
nvarcharでサイズ50を指定した場合は、最大50文字（UCS-2の範囲）となります。補助文字を使用する照合順序を指定した場合は、UTF-16となるので、サロゲートペア文字が使われた場合は50文字より少なくなります。
なお、SQL Serverでは、文字列を格納するデータ型にtext、ntextもありますが、これらは非推奨となっています。
周波数の開始値、終了値
小数点の数値は、float、real、decimal のデータ型が使えます。（numeric型もありますが、SQL Serverではdecimalと同一です。）
floatとrealは浮動小数点数のデータ型で、decimalは固定長の有効桁数（10進数）と小数点以下の桁数を指定したデータ型です。 floatは有効桁数（仮数部のbit数）を1～53で指定しますが、内部では24もしくは53のどちらかで扱います。 realは、float(24)と同義です。 周波数の値はそれほど有効桁数を必要としないので、今回はストレージ上のサイズが小さくてすむrealを使用します。
spectrumの設計
スペクトラムデータの点列を保持します。バイナリデータですが点数がまちまちのため可変長のバイナリとします。サイズを数値指定する場合は1～8000の範囲なので最大8000バイトとなります。しかし、格納したい点列は1万点を超えることがあるので、この場合はサイズにmaxを指定します。maxを指定すると、最大で バイトまで格納可能です。
なお、binary、image は非推奨となっています。
計測記録テーブルへの参照を含めるか
後で作成する計測記録のテーブルには、スペクトラムデータを参照する外部キーを設ける予定です。 その逆方向としてスペクトラムのテーブルに計測記録への外部キーを設けるかどうか悩みました。 双方向の関連を持つか、片方向の関連のみとするか、現時点では明確な根拠を持てないでいます。必要性を感じるまでは、スペクトラムのテーブルには計測記録への外部キーは持たせないでおきます。