データは、データ構造の中に入れるだけではなく、現実世界をモデル化して、
コンピュータで扱うためのデータモデルを構築する基になるものです。
データモデルを図に表現すると以下のようになります。
■データモデル
現実の世界をモデル化するにあたって、現実世界にあるデータが、時々刻々変化することは、
現実世界の仮想的な操作（Operation）によってなされると考えられます。その現実世界の状態の変化にシンクロナイズしてデータベースモデルの状態も変化しなければなりません。その時にはどうしても歪み（Distortion）が生まれます。それをできるだけ回避するために、制約条件（Constraint）を与えて、データベースモデルでの状態間の変化を制約条件に整合性がとれるDatabase Operationによって行われます。
ＲがＲ’に変化することを表現するために、データベースモデルの状態Ａを状態Ａ’に変化させなければなりません。
もちろん、現実世界そのものを完全に表現することは不可能なので、制限された仮想世界を想定してマッピングを行います。
これを実装するために、様々なData Modelが提案されてきましたが、この回では、データベースモデルを設計するときによく利用されているEntity-Relationship Model (ERM)と、Database Management System (DBMS)の基礎になっているRelational Data Model(RM)紹介します。
■ERM
まず、設計ツールとして使われているERMから紹介しましょう。
HLPでのオブジェクト指向のデータ構造のときに出てきたObject、Classの概念とよく似たものです。
抽象化されたObject(Entity)とオブジェクト間の関係を表すRelationshipという概念を導入します。
HLPのClassの定義と大きく違うところは、ProcedureやFunctionを定義しないで、抽象的なデータ構造のみ定義します。
個別のClassに特有のOperationを定義しなくていいように共通のOperationを利用することが完成されたData Modelを使う利点です。
しかし、ERMはその点ではOperationが未定義であるために、設計ツールとしてのみ利用されています。
したがって、実装の場合には、下位にあるData Modelのスキーマに変換する必要があります。
簡単な学校のモデルの例を考えてみます。
■学校のモデル
Entityは、学生、先生、科目があり、Relationshipは、科目を教える、科目をとる、を考えています。
設計時には、文で表現された仕様から、名詞をEntityに動詞をRelationshipと考えることが容易なERMの設計方法です。
それぞれ、EnitityにもRelationshipにも属性が考えられます。
上記の図はそれを表現したもので、仮想的な記述を試みます。
Entity
Entityの定義では、そのインスタンスがユニークになるようにIDを加えておきます。
先生：
先生ID
名前
専門分野
学生：
学生ID
名前
住所
科目：
科目ID
科目名
分類
Relationship
Relationshipも同様に、そのIDと属性と、それぞれ関係する各EntityのIDを加えます。
Teach：
TeachID
先生ID
科目ID
時間数
スケジュール
Take：
TakeID
学生ID
科目ID
成績
また、（この図では、前回にでてきた ISーA というRelationship（Super ClassとSub Classの関係）もありますがそれは省略します。）
このように、上位の抽象化レベルから始めて、下位のレベルにブレークダウンすることにより、整合性のとれたデータベースモデルを設計できる利点があります。
ERMの詳細は、次回に詳しく説明します。
■Relational Data Model
次に、実際にDatabase Operationも定義されているRelational Database Model（RM)を紹介します。
RMの利点は、数学的な枠組み（Relation）を使ってOperationも含めた完全なデータモデルの定義がなされていることです。
したがって、下位のモデルを仮想化する必要がありません。
RDBMSを開発するチームは、共有できる明確な概念に基ずく開発が可能になります。
最初に数学的に定義したのはCoddでIBMのフェローでした。
これにより、DBMSの市場は、＄Billionの大きさに成長しました。
（IBMの社内資料を参照）
その時の論文は参考に添付しておきます。
以下、基本的な定義をその論文を引用しながら説明します。
Relation(リレーション)は以下のように数学のRelationとして定義できます。
nタプルとは、n個の各Domainに属する値の組（v1,v2,v3,...,vn）と定義します。
S1からSnまでの集合が与えられたとき
Rはｎタプルの集合でありこれらの集合のRelationです。最初の要素は、S1 次はS2というようにSjは、Ｒのｊ番目のDomainであり、このRのDegreeはｎです。
正確には、ＲはS1 X S2 X S3…X SnのCartesianProduct（直積）の部分集合と定義されます。
わかりやすいようにRelationを配列表現として説明します。
（１） 各行は、Rタプル。
（２） 行の順序はない。
（３） 各行はユニーク。（重複しない）
（４） 各列の順序は、Domainの順序として重要。
（５） 各列は、属性としての名前を持つ。
以下は、Relationの定義を完全にするためのOperation（関係代数演算）を数学的に定義しています。
基本的な関係代数演算
- 射影（Projection） π
- スキーマ: π_X : R → R' で X ⊆ A
- 定義: π_X(r) = { t|_X | t ∈ r } （t|_X は t を X に制限した写像）
- ここで集合性より同一タプルは重複除去される。
- 選択（Selection） σ
- スキーマ: σ_φ : R → R （φ は述語：タプル上の真偽条件）
- 定義: σ_φ(r) = { t ∈ r | φ(t) = true }
- 直積（Cartesian product）
- R(A) と S(B) に対して A ∩ B = ∅（属性名の重複を避ける）なら
- R × S = { t : A ∪ B → ⋃ dom | t|_A ∈ R ∧ t|_B ∈ S }
- 自然結合（Natural join） ⋈
- R ⋈ S = { t : A ∪ B → ⋃dom | t|_A ∈ R ∧ t|_B ∈ S ∧ ∀c ∈ A∩B, t|_A(c) = t|_B(c) }
- すなわち共通属性で値が等しいタプルを結合する。
- 和集合（Union） ∪
- 定義: R ∪ S は A = B の同スキーマに対して r_R ∪ r_S
- 差（Difference） −
- 定義: R − S = { t ∈ r_R | t ∉ r_S }
- 交差（Intersection） ∩
- 定義: R ∩ S = { t | t ∈ r_R ∧ t ∈ r_S }
Coddの論文に使われている例題です。
Supply（Supplier, Part, Project, Quantity）
2 3 7 9
2 7 5 4
4 1 1 12
1 2 5 17
1 3 5 23
同じ名前の属性の場合はRelationを定義するときの順序が重要である例
Component (Part, Part, Quantity)
2 5 7
1 5 9
3 5 2
2 6 12
3 6 3
4 7 1
6 7 1
以下は、関係代数演算を使った結果の例
Supplyに(Project,Supplier)でProjectionをしたもの
R1(Project, Supplier)
5 1
5 2
1 4
7 2
新しいRelation
R2(Supplier,part)
2 1
2 2
1 1
新しいRelation
R3(part, Project)
2 1
1 1
1 2
R2,R3をJOIN
R2*R3(Supplier,part,project)
2 1 1
2 1 2
2 2 1
1 1 1
1 1 2
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
Relation DBMS（例えば、PostgreSQL）には、データベースにアクセスするためのPSQLという言語が準備されています。
データ入力：Insert
データ削除：Delete
データ検索：Select
その他、関数：avg, count, その他
以下は、簡単なクリニックの例題を作成し、以下のようなRelationを定義しPSQLにてCreateします。
create table patients(id serial, name char varying,birthday date,gender char varying, address char varying,phone char varying);
create table users(id serial, username char varying, password_hash char varying, role char varying);
create table visits(id serial, patient_id integer, visit_date date, note text);
この操作でどのようなRelational Schemaが生成されたか参考1で詳しく見ることができます。
____________________________________________________________________
データを入力した後に、PSQL言語によりコマンドで検索を行います。以下の操作により画面に出てくる
結果を表示しています。
emr_sample=# select * from patients;
id | name | birthday | gender | address | phone
----+----------+------------+--------+---------+-------
1 | matsuo | 1949-01-01 | M | |
2 | 山田太郎 | 1980-01-01 | male | |
3 | masa | 1950-01-01 | male | |
4 | tanaka | 2000-10-10 | female | |
(4 rows)
以下は、ログインのユーザ（医師）のRelationですが、
password_hashは暗号化されて保存されています。
emr_sample=# select * from users;
id | username | password_hash | role
----+----------+--------------------------------------------------------------+--------
3 | higenobu | $2b$12$a162MCZVMEEfsx6QCl.Kye0cBBPxUJyIWs2wrFOKAdaOTxOZ98s9e | doctor
4 | itsumi | $2b$12$eoqteOhypbA11QGXnwvAk.vl9Oectj6bZT85.m978aIwsU5UZDBzy | doctor
5 | amiko | $2b$12$AfQX6NuHqcxHdSKxXFho/uuJ0aRNCH7rjlVWE85SqwibIuVXFuXgK | doctor
6 | mayu | $2b$12$jNgy3tY0BfXFFWrqw6jtgOkEvsO4v02XSsSs/wv6dO0fcaQRZZzom | doctor
7 | masa | $2b$12$2QWDid7L7ftY83.VKJC6ieRMofAz1NRs0TKLedfu0CTQCFE2S0LZa | doctor
(5 rows)
これは、患者がクリニックで診察を受けるときの入力のデータです。
insert into visits (patient_id,visit_date,note) values (1,'2025-11-02','Good condition');
insert into visits (patient_id,visit_date,note) values (2,'2025-11-02','Flue');
emr_sample=# select * from visits;
id | patient_id | visit_date | note
----+------------+------------+----------------
1 | 1 | 2025-11-02 | Good condition
2 | 2 | 2025-11-02 | Flue
(2 rows)
SelectでのJoinの操作例は
visitsとpatientsのRelationを共通のpatient_idとidでJOINします。
select * from visits as v join patients as p on p.id=v.patient_id ;
その結果は
id | patient_id | visit_date | note | id | name | birthday | gender | address | phone
----+------------+------------+----------------+----+----------+------------+--------+---------+-------
1 | 1 | 2025-11-02 | Good condition | 1 | matsuo | 1949-01-01 | M | |
2 | 2 | 2025-11-02 | Flue | 2 | 山田太郎 | 1980-01-01 | male | |
(2 rows)
■Python & PSQL
Pythonのプログラムによりこのデータベースにアクセスしてみます。
PSQL言語をコードの中に組み込みます。
import psycopg2
import psycopg2.extras
conn = psycopg2.connect(dbname="emr_sample", user="matsuo", password="masanobu",host="localhost",port=5432)
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute("SELECT * FROM patients;")
rows = cur.fetchall()
for row in rows:
print(dict(row)) # Each row is a dictionary-like object
これらのデータベースを使った実際のアプリケーションのバックエンドとフロントのサンプルコードは参考２に添付します。
ファイルシステムを使うことなくデータベースを使うことで安全で確実なアプリケーションが開発できます。
_____________________________________________________________________________________
以下は、上記のERMから学校DBのスキーマ生成をPSQLでコマンドで入力している場面です。
emr_sample=# create table teacher(id serial, name char varying, speciarity char varying);
emr_sample=# create table student(stno serial, name char varying, address char varying);
emr_sample=# create table subject(subno serial, subname char varying, category char varying); ^
emr_sample=# create table teach(tno serial, teacher integer, student integer, time char varying, koma integer);
emr_sample=# create table take(takeno serial, student integer, subject integer, grade char varying);
■参考
Coddの論文
参考１ Database shcemaの例
Relations: patients, Users, visits
\d public.*
の出力を参考に添付します。
_____________________________________________________________________________________
Table "public.patients"
Column | Type | Collation | Nullable | Default
----------+------------------------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('patients_id_seq'::regclass)
name | character varying(128) | | not null |
birthday | date | | |
gender | character varying(8) | | |
address | character varying(255) | | |
phone | character varying(32) | | |
Indexes:
"patients_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "visits" CONSTRAINT "visits_patient_id_fkey" FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE
Sequence "public.patients_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
integer | 1 | 1 | 2147483647 | 1 | no | 1
Owned by: public.patients.id
Index "public.patients_pkey"
Column | Type | Key? | Definition
--------+---------+------+------------
id | integer | yes | id
primary key, btree, for table "public.patients"
Table "public.users"
Column | Type | Collation | Nullable | Default
---------------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
username | character varying(64) | | not null |
password_hash | character varying(255) | | not null |
role | character varying(32) | | not null | 'doctor'::character varying
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_username_key" UNIQUE CONSTRAINT, btree (username)
Sequence "public.users_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
integer | 1 | 1 | 2147483647 | 1 | no | 1
Owned by: public.users.id
Index "public.users_pkey"
Column | Type | Key? | Definition
--------+---------+------+------------
id | integer | yes | id
primary key, btree, for table "public.users"
Index "public.users_username_key"
Column | Type | Key? | Definition
----------+-----------------------+------+------------
username | character varying(64) | yes | username
unique, btree, for table "public.users"
Table "public.visits"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+------------------------------------
id | integer | | not null | nextval('visits_id_seq'::regclass)
patient_id | integer | | |
visit_date | date | | |
note | text | | |
Indexes:
"visits_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"visits_patient_id_fkey" FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE
Sequence "public.visits_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
integer | 1 | 1 | 2147483647 | 1 | no | 1
Owned by: public.visits.id
Index "public.visits_pkey"
Column | Type | Key? | Definition
--------+---------+------+------------
id | integer | yes | id
primary key, btree, for table "public.visits"
■参考２ アプリケーション例
relation-app.py
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import csv
import os
import psycopg2
app = Flask(__name__)
# PostgreSQL接続情報
conn = psycopg2.connect(
dbname="emr_sample", user="matsuo", password="masanobu", host="localhost", port=5432
)
@app.route('/register', methods=['POST'])
def register():
data = request.get_json()
username = data.get('username')
password = data.get('password')
role = data.get('role', 'doctor')
hash_pw = bcrypt.hashpw(password.encode(), bcrypt.gensalt()).decode()
cur = conn.cursor()
try:
cur.execute(
"INSERT INTO users (username, password_hash, role) VALUES (%s, %s, %s) RETURNING id, username, role",
(username, hash_pw, role)
)
user = cur.fetchone()
conn.commit()
return jsonify({'id': user[0], 'username': user[1], 'role': user[2]})
except psycopg2.errors.UniqueViolation:
conn.rollback()
return jsonify({'error': 'User exists'}), 400
except Exception as e:
conn.rollback()
return jsonify({'error': str(e)}), 500
finally:
cur.close()
@app.route('/login', methods=['POST'])
def login():
data = request.get_json()
username = data.get('username')
password = data.get('password')
cur = conn.cursor()
cur.execute("SELECT id, username, password_hash, role FROM users WHERE username=%s", (username,))
user = cur.fetchone()
cur.close()
if not user:
return jsonify({"error": "ユーザーが存在しません"}), 401
user_id, db_username, db_password_hash, role = user
if bcrypt.checkpw(password.encode(), db_password_hash.encode()):
# 本来はJWT等を返すべきですが、ここではダミートークン
return jsonify({"token": f"dummy-token-for-{db_username}", "username": db_username, "role": role})
else:
return jsonify({"error": "パスワードが違います"}), 401
@app.route('/patients', methods=['GET'])
def get_patients():
cur = conn.cursor()
cur.execute("SELECT id, name, birthday, gender FROM patients")
patients = [
{"id": row[0], "name": row[1], "birthday": str(row[2]), "gender": row[3]}
for row in cur.fetchall()
]
cur.close()
return jsonify(patients)
@app.route('/users', methods=['GET'])
def get_users():
cur = conn.cursor()
cur.execute("SELECT id, username, role FROM users")
users = [
{"id": row[0], "username": row[1], "role": row[2]}
for row in cur.fetchall()
]
cur.close()
return jsonify(users)
@app.route('/patients/<int:patient_id>', methods=['GET'])
def get_patient(patient_id):
cur = conn.cursor()
cur.execute("SELECT id, name,birthday,gender FROM patients where id=%s",(patient_id,))
row = cur.fetchone()
#patients = [{'id': row[0], 'name': row[1]} for row in cur.fetchall()]
cur.close()
if not row:
return jsonify({"error": "no patients"}),404
return jsonify({
"id":row[0],
"name": row[1],
"birthday":str(row[2]),
"gender":row[3]
})
@app.route('/patients', methods=['POST'])
def create_patient():
data = request.get_json()
name = data.get('name')
birthday = data.get('birthday') # 'YYYY-MM-DD' 形式で
gender = data.get('gender')
cur = conn.cursor()
try:
cur.execute(
"INSERT INTO patients (name, birthday, gender) VALUES (%s, %s, %s) RETURNING id, name, birthday, gender",
(name, birthday, gender)
)
patient = cur.fetchone()
conn.commit()
return jsonify({
'id': patient[0],
'name': patient[1],
'birthday': str(patient[2]),
'gender': patient[3]
})
except Exception as e:
conn.rollback()
return jsonify({'error': str(e)}), 500
finally:
cur.close()
if __name__ == '__main__':
app.run(port=3001)
_____________________________________________________________________
dash_users_app.py
_____________________________________________________________________
import dash
from dash import html, dcc
from dash.dependencies import Input, Output, State
import requests
app = dash.Dash(__name__)
app.layout = html.Div([
html.H2("ユーザー登録"),
dcc.Input(id='username', type='text', placeholder='ユーザー名'),
dcc.Input(id='password', type='password', placeholder='パスワード'),
dcc.Dropdown(id='role', options=[
{'label': 'Doctor', 'value': 'doctor'},
{'label': 'Admin', 'value': 'admin'}
], value='doctor', placeholder='役割'),
html.Button('登録', id='register-btn'),
html.Div(id='register-result'),
html.Hr(),
html.H2("ユーザー一覧"),
html.Button('更新', id='refresh-btn'),
html.Ul(id='user-list')
])
@app.callback(
Output('register-result', 'children'),
Input('register-btn', 'n_clicks'),
State('username', 'value'),
State('password', 'value'),
State('role', 'value')
)
def register_user(n_clicks, username, password, role):
if not n_clicks:
return ""
if not username or not password:
return "ユーザー名とパスワードを入力してください"
resp = requests.post("http://localhost:3001/register", json={
'username': username,
'password': password,
'role': role
})
if resp.ok:
data = resp.json()
return f"登録成功: {data['username']}（{data['role']}）"
else:
return f"登録失敗: {resp.text}"
@app.callback(
Output('user-list', 'children'),
Input('refresh-btn', 'n_clicks')
)
def show_users(n_clicks):
resp = requests.get("http://localhost:3001/users")
if resp.ok:
users = resp.json()
return [html.Li(f"{u['username']} ({u['role']})") for u in users]
else:
return [html.Li("ユーザー一覧取得失敗")]
if __name__ == "__main__":
app.run(debug=True)
著者：松尾正信
株式会社京都テキストラボ代表取締役