SQL – 京都テキストラボ情報＆教育サイト

データは、データ構造の中に入れるだけではなく、現実世界をモデル化して、 コンピュータで扱うためのデータモデルを構築する基になるものです。 データモデルを図に表現すると以下のようになります。 ■データモデル 現実の世界をモデル化するにあたって、現実世界にあるデータが、時々刻々変化することは、現実世界の仮想的な操作（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にも属性が考えられます。上記の図はそれを表現したもので、仮想的な記述を試みます。EntityEntityの定義では、そのインスタンスがユニークになるようにIDを加えておきます。先生： 先生ID 名前 専門分野 学生： 学生ID 名前 住所科目： 科目ID 科目名 分類RelationshipRelationshipも同様に、その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 92 7 5 44 1 1 121 2 5 171 3 5 23同じ名前の属性の場合はRelationを定義するときの順序が重要である例Component (Part, Part, Quantity)2 5 71 5 93 5 22 6 123 6 34 7 16 7 1以下は、関係代数演算を使った結果の例Supplyに(Project,Supplier)でProjectionをしたもの R1(Project, Supplier)5 15 21 47 2新しいRelationR2(Supplier,part)2 12 21 1新しいRelationR3(part, Project)2 11 11 2R2,R3をJOINR2*R3(Supplier,part,project)2 1 12 1 22 2 11 1 11 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 &amp; PSQL Pythonのプログラムによりこのデータベースにアクセスしてみます。PSQL言語をコードの中に組み込みます。import psycopg2import psycopg2.extrasconn = 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の論文 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 | 1Owned by: public.patients.id Index "public.patients_pkey" Column | Type | Key? | Definition--------+---------+------+------------ id | integer | yes | idprimary 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 varyingIndexes: "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 | 1Owned by: public.users.id Index "public.users_pkey" Column | Type | Key? | Definition--------+---------+------+------------ id | integer | yes | idprimary key, btree, for table "public.users" Index "public.users_username_key" Column | Type | Key? | Definition----------+-----------------------+------+------------ username | character varying(64) | yes | usernameunique, 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 | 1Owned by: public.visits.id Index "public.visits_pkey" Column | Type | Key? | Definition--------+---------+------+------------ id | integer | yes | idprimary key, btree, for table "public.visits" ■参考２ アプリケーション例relation-app.py＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿#!/usr/bin/env python# -*- coding: utf-8 -*-import csvimport osimport psycopg2app = 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/&lt;int:patient_id&gt;', 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 dashfrom dash import html, dccfrom dash.dependencies import Input, Output, Stateimport requestsapp = 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) 著者：松尾正信株式会社京都テキストラボ代表取締役

プログラミング学習 Day 6：DataBase, Relational Data Model