こんばんは。佐久間です。
先週告知した連載
「Reporting ServicesとBing MapsでWEB上のHTMLデータを可視化するレポートを作成する」
第1回目はデータベースの作成です。（目次はこちら）
早速ですが手順を追いながらいってみましょう。
1. データベースを作成する：SQL Server
- まずはデータベースを作ります。 -
1. Management Studioのメニューから「新しいデータベース」を選択する
2. データベース名を入力する
3. 復旧モデルを「単純」にする
あとはOKを押せばデータベースが作成されます。
さすがはSQL Server。楽ちんです。
さすがはSQL Server。楽ちんです。
ほぼデフォルト設定のままDBを作成していますが、サンプルなので復旧モデルを「単純」にしました。
万が一DB側でデータ障害が発生してもデータをまたWeb上から取り直せばよいということで。
- 次にテーブルを作成します。 -
作成するテーブルの一覧はこちら↓↓↓
| テーブル名 | 用途 |
|---|---|
| Work | データの取り込みと変換に使用します。 |
| 地震データ | レポートのデータソースとなるメインのテーブルです。 |
| 都道府県マスタ | 地震データと関連づける都道府県情報のマスタです。 |
| 震度マスタ | 地震データと関連づける震度情報のマスタです。 |
今回必要なテーブルはこの４つだけ。
意外と少ないでしょうか？
ここから先は淡々と進みますが、
がんばってCreate文とInsert文を実行してください。
では順番に作っていきましょう。
1. Work
WorkにはHTMLから取得したデータを格納する列と、
型変換やID化を行った後のデータを格納する列を作ります。
このテーブルに対してID指定でSELECTやUPDATEを行うことはないのでID列は設けていません。
型変換やID化を行った後のデータを格納する列を作ります。
このテーブルに対してID指定でSELECTやUPDATEを行うことはないのでID列は設けていません。
※今回データソースとする気象庁のHTMLには次のデータがあります。
「日時」「北緯」「東経」「震源の深さ」「マグニチュード」「震源」「最大震度」
「日時」「北緯」「東経」「震源の深さ」「マグニチュード」「震源」「最大震度」
CREATE TABLE dbo.Work(
日時 datetime NULL,
北緯 real NULL,
東経 real NULL,
深さ int NULL,
マグニチュード real NULL,
震源 nvarchar(50) NULL,
最大震度 nvarchar(5) NULL,
座標 geography NULL,
都道府県ID smallint NULL,
最大震度ID smallint NULL
)
2. 地震データ
地震データはレポートと直結するテーブルです。
※座標さえあれば北緯と東経はなくても問題ありません。
geography型の座標から取り出さなくても簡単に扱えるように残しています。
※座標さえあれば北緯と東経はなくても問題ありません。
geography型の座標から取り出さなくても簡単に扱えるように残しています。
CREATE TABLE dbo.地震データ(
地震データID decimal(18, 0) IDENTITY(1,1) NOT NULL,
日時 datetime NULL,
北緯 real NULL,
東経 real NULL,
座標 geography NULL,
震源 nvarchar(50) NULL,
都道府県ID smallint NULL,
深さ int NULL,
マグニチュード real NULL,
最大震度ID smallint NULL,
CONSTRAINT PK_地震データ PRIMARY KEY CLUSTERED (地震データID ASC)
)
3. 都道府県マスタ
都道府県マスタにはIDと名称を持たせます。
CREATE TABLE dbo.都道府県マスタ(
都道府県ID smallint NOT NULL,
都道府県名 nvarchar(4) NOT NULL,
CONSTRAINT PK_都道府県 PRIMARY KEY CLUSTERED (都道府県ID ASC)
)
続いて初期データです。
INSERT INTO 都道府県マスタ (都道府県ID, 都道府県名)
VALUES ('1', '北海道')
, ('2', '青森県')
, ('3', '岩手県')
, ('4', '宮城県')
, ('5', '秋田県')
, ('6', '山形県')
, ('7', '福島県')
, ('8', '茨城県')
, ('9', '栃木県')
, ('10', '群馬県')
, ('11', '埼玉県')
, ('12', '千葉県')
, ('13', '東京都')
, ('14', '神奈川県')
, ('15', '新潟県')
, ('16', '富山県')
, ('17', '石川県')
, ('18', '福井県')
, ('19', '山梨県')
, ('20', '長野県')
, ('21', '岐阜県')
, ('22', '静岡県')
, ('23', '愛知県')
, ('24', '三重県')
, ('25', '滋賀県')
, ('26', '京都府')
, ('27', '大阪府')
, ('28', '兵庫県')
, ('29', '奈良県')
, ('30', '和歌山県')
, ('31', '鳥取県')
, ('32', '島根県')
, ('33', '岡山県')
, ('34', '広島県')
, ('35', '山口県')
, ('36', '徳島県')
, ('37', '香川県')
, ('38', '愛媛県')
, ('39', '高知県')
, ('40', '福岡県')
, ('41', '佐賀県')
, ('42', '長崎県')
, ('43', '熊本県')
, ('44', '大分県')
, ('45', '宮崎県')
, ('46', '鹿児島県')
, ('47', '沖縄県')
, ('99', 'その他')
4. 震度マスタ
震度マスタにはIDと表示文字列と、
後のデータ変換処理で使用する置換用の文字列を格納する列を持たせます。
全角文字列のままの表示でもかまわないのであれば置換文字列の列は不要です。
後のデータ変換処理で使用する置換用の文字列を格納する列を持たせます。
全角文字列のままの表示でもかまわないのであれば置換文字列の列は不要です。
CREATE TABLE dbo.震度マスタ(
震度ID smallint NOT NULL,
震度 nvarchar(5) NOT NULL,
置換文字列 nvarchar(5) NULL,
CONSTRAINT PK_震度 PRIMARY KEY CLUSTERED (震度ID ASC)
)
データはこちら。
INSERT INTO 震度マスタ (震度ID, 震度, 置換文字列)
VALUES ('0', '0', '０')
, ('1', '1', '１')
, ('2', '2', '２')
, ('3', '3', '３')
, ('4', '4', '４')
, ('5', '5弱', '５弱')
, ('6', '5強', '５強')
, ('7', '6弱', '６弱')
, ('8', '6強', '６強')
, ('9', '7', '７')
, ('99', 'その他', '')
5. リレーション
レポートと直結する地震データと２つのマスタのIDにリレーションを設定します。
- 地震データと震度マスタ -
ALTER TABLE dbo.地震データ ADD CONSTRAINT
FK_地震データ_震度マスタ FOREIGN KEY (最大震度ID)
REFERENCES dbo.震度マスタ (震度ID)
ON UPDATE NO ACTION
ON DELETE NO ACTION
- 地震データと都道府県マスタ -
ALTER TABLE dbo.地震データ ADD CONSTRAINT
FK_地震データ_都道府県マスタ FOREIGN KEY (都道府県ID)
REFERENCES dbo.都道府県マスタ (都道府県ID)
ON UPDATE NO ACTION
ON DELETE NO ACTION
以上をER図で表すとこんな感じになります。
データベースの作成はここまで。
お疲れ様でした。
お疲れ様でした。
いかがでしょう？
イメージが少し湧いてきたでしょうか？まだでしょうか？
今回は完全にデータベースの定義の回だったのでとても地味ですね。
しかしこれで準備が整いました。
次回はデータをHTMLから抽出しWorkに取り込む処理を行います。
ではまた。
