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

データベースの設計で重要なのは、現実のモデルの意味を正確に表現できているかどうかです。 Relational Database Model（RM)では、Functional Dependency(FD)と言われる属性の間での関係を スキーマの設計時に考えます。これは現実の属性の関係に意味を与えるものです。 ■Functional Dependency(FD) 会社の簡単な例をあげて説明しましょう。社員、部門、所属、管理者、プロジェクトを考えてみます。社員、管理者は、それぞれ、名前、住所、家族が考えられます。また、所属する部門があります。管理者には部下がいます。社員には参加するプロジェクトがあります。プロジェクトは一人の管理者が管理をしています。部門には、複数の事務所があるかもしれません。これらをＲＭを使ってスキーマを設計するときに、まず、FDを考えます。ある属性に対してユニークに一つの値とマッピングする関係をFDと呼びます。F(X) = Yというように、関数にXを与えると一つの値のみが生成される、ことと同じ意味です。わかりやすいように、上記のに記述の中で名詞で表現できるオブジェクトになると思われるものには、それぞれユニークなＩＤを属性としてアサインすることにします。社員に対して社員の属性として、名前、住所はFDです。同様に、それぞれの属性間の関係を見つけます。 FD::emp-name: emp-no -&gt; nameaddress: emp-no -&gt; address社員は、複数のプロジェクトに参加しない、という制約（意味）を入れると、この関係はFDになります。FD::work: emp-no-&gt; project-noまた、社員は、複数の直接の上司を持たない、という意味も入れます。これもFDになります。FD::Boss: emp-no -&gt; manager-no管理者は、複数の部下がいますが、上記のBossはFDであっても、その逆はFDではありません。以下は、管理者も社員であるという意味です。これはIS-Aの関係とも言われています。クラスとサブクラスと近似です。FD::Manager: manager-no -&gt; emp-no上記のIS-Aの関係があれば、employeeの持つ属性を継承するはずですから以下の不要です。FD::man-name: manager-no -&gt; manager-name部門が複数の事務所を持つときは、事務所から部門への関係はFDになるので、以下のように考えます。FD:: office-no -&gt; dept それでは、複数と複数の関係はFDになるのか？どのようなRelationを作るのか？複数と複数の関係の場合は、それぞれの属性間では、FDの関係にならないのでFDを見つけるには工夫がいります。社員が複数のプロジェクトに参加するときには、上記のFD::work:emp-no -&gt; project-noは成立しなくなります。社員がいつからプロジェクトに参加したかを属性として追加することによりFDを見つけることができます。FD:: project-no, emp-no -&gt; start-dateというFDが成り立ちます。これは、複数の属性をFDの左側に入れるという方法です。FDのみならず、どのようなデータを保存したいか、を考えるときに、できるだけ属性をかきだし、FDにならないものも考えます。 ■FDはどんな役割を持つのか？ Relationは任意の属性を組み合わせても成り立つものですが、現実のモデルが持つ意味をマッピングするものを設計しなければなりません。そのために、FDを考慮したスキーマ（Relation)を設計するために正規化という概念をCoddは提案しました。 First Normal Form(1NF)は、Relationの定義にある、属性は単独のDomain（値の集合）の要素でなければならない、ということです。 値が、複数の値の組み合わせ（Listやタプルや配列など）ではいけません。 たとえば、 Childrenを属性にするようなことはできません。なぜなら複数の子供がいる場合があるからです。 しかし、FirstChildという属性であれば、１NFを満たしています。 家族は、複数の値を持つ場合があるので１NFに違反する例です。 emp-family(emp-no,(child-nm1,child-nm2,child-nm3)) あるいは、 salary-history(sal-date,salary)に対して Salaries(emp-no,salary-history)を作ることはできません。 salary-historyは、日付と金額の配列になるからです。 ■Second and Third Normal Form FDの左側が複数の属性である時、その右にある属性が、左側にある一つで、すでにＦＤがなりたつときは、左と右の属性を持つRelationは、Second Normal Formに違反する、といいます。例をあげるとテーブル orderitems(order-iid, product-id, product-name, quantity) (order-id, product-id)が複合キーFD:: product_id → product_nameが成り立つ product-name は キーの一部のproduct_idに依存している → 2NF 違反となる重要な正規化はThird Normal Form(3NF)です。FDをみつけることにより、Third Normal Form(3NF）と呼ばれる第3正規化を満たすスキーマを設計することができます。プライマリーキーとは、Relationを定義する（設計）ときに、そのタプル（インスタンス）をユニークに決めるとこができる属性のことです。FDの左にある属性が共通のものを選んで、その共通の属性をプライマリキーとして考えて、FDの関係を同じRelationにいれるような設計をすることを3NFと言います。その目的は、データの冗長性をなくすことです。それによりデータの更新の操作時に、制約（意味）が失われないようにするためです。たとえば、employee( emp-no,name,address)これは3NFです。管理者も社員であるので、IS-A関係をRelationで表現すると、manager(manager-no, emp-no)だからです。これも3NFです。boss(emp-no,manager-no)BossというFDが成り立つからこれも３NFです。しかし、この中に、FDでもない属性をいれてはいけません。たとえば、管理者の住所（manager-address)とか。boss-no3nf(emp-no,manager-no,manager-address)は、3NFではありません。この場合の例を考えると、管理者の住所が変わると、boss-no3nfの中のそのmanager-noを持つタプルをすべて変更しなければなりません。それは、その管理者の部下のタプルになります。しかし、bossのRelationだと、変更なしで、employeeのRelationで社員（管理者）の住所の変更のみですみます。3NFの利点は、データの冗長性を最小限にすると同時に、変更時の操作も最小限にすむことです。弱点は、以下のような検索時には、JOINを必要になります。管理者の住所が、”osaka”の部下を検索するときbossのmanager-noとemployeeのemp-noをJOINして、employeeのaddressが”osaka”である条件をつけることになります。boss-no3nfでは、このRelationで、manager-address=”osaka”で結果が出てきます。次に、project (project-no,project-name)office(office-no, dept-no)dept(dept-no, dept-name)複数と複数の関係は、どちらもプライマリでないRelationを設計し3NFを維持します。work-m2n(emp-no, project-no)もし、社員も複数のプロジェクトに参加する場合は、以下のように、社員とプロジェクトの結合したプライマリキーを使って、参加した日とはずれた日の属性を作ってFDをつくることもいいアイディアです。work-3nf(emp-no,project-no,start-date,end-date)社員の家族の場合も、複数の家族員はいる場合があるので、FDではありませんので、プライマリーキーのないRelationfamily(emp-no, member-name,relationship)というように設計します。まとめると、アプリケーションを考えると、JOINをせずに検索ができるので３NFにしない設計をする人がいますが、そのかわり、データの変更や削除のときに、複数のRelationにまたがって操作をしなければならないため、誤りを生みやすくなります。モデルに内包される意味を正確に表現するためにもNormal Formが重要です。ERMから設計を始めると、RDMの３NFへの変換が容易になることは、前回でも例をあげて説明しました。以下は、ERMの図を表現するためのツールを参考で紹介しておきます。 ■参考：会社の例 https://www.plantuml.com/plantuml/ の「ライブ編集」サイトを使うと以下のような定義形式を使うとレンダリングできます。 @startuml CompanyERMleft to right directionskinparam linetype orthoskinparam componentStyle uml2' Entities with attributes (PK marked with *, FK annotated)entity "Department" as Dept { * dept_id : PK -- dept_name head_manager_id : FK (nullable)}entity "Office" as Office { * office_id : PK -- office_address office_phone dept_id : FK}entity "Employee" as Emp { * emp_id : PK -- name address dept_id : FK manager_id : FK (nullable) '直属上司（同テーブルへの自己参照）}entity "Project" as Proj { * proj_id : PK -- proj_name start_date end_date proj_desc}entity "EmployeeProject" as EP { * emp_id : FK * proj_id : FK -- role assigned_from assigned_to ' composite PK(emp_id, proj_id)}entity "FamilyMember" as Fam { * family_id : PK -- emp_id : FK fm_name fm_relation birthdate}' Relationships (crow's foot style using PlantUML notation)' Department 1 --- N OfficeDept ||--o{ Office : "has"' Department 1 --- N Employee (employees belong to a department)Dept ||--o{ Emp : "employs"' Employee (manager) 1 --- N Employee (self-reference)Emp ||--o{ Emp : "manages / reports_to"' Employee N --- M Project via EmployeeProjectEmp ||--o{ EP : "assigned_as"Proj ||--o{ EP : "has_assignment"' Employee 1 --- N FamilyMemberEmp ||--o{ Fam : "has_family"' Optional/annotative relation: Department headed by an EmployeeDept }o--|| Emp : "headed_by (optional)"@enduml ■病院の例 @startuml CompanyERM left to right direction skinparam linetype ortho skinparam componentStyle uml2 ' Entities with attributes (PK marked with *, FK annotated) entity "Department" as Dept { * dept_id : PK -- dept_name head_manager_id : FK (nullable) } entity "Office" as Office { * office_id : PK -- office_address office_phone dept_id : FK } entity "Employee" as Emp { * emp_id : PK -- name address dept_id : FK manager_id : FK (nullable) '直属上司（同テーブルへの自己参照） } entity "Project" as Proj { * proj_id : PK -- proj_name start_date end_date proj_desc } entity "EmployeeProject" as EP { * emp_id : FK * proj_id : FK -- role assigned_from assigned_to ' composite PK(emp_id, proj_id) } entity "FamilyMember" as Fam { * family_id : PK -- emp_id : FK fm_name fm_relation birthdate } ' Relationships (crow's foot style using PlantUML notation) ' Department 1 --- N Office Dept ||--o{ Office : "has" ' Department 1 --- N Employee (employees belong to a department) Dept ||--o{ Emp : "employs" ' Employee (manager) 1 --- N Employee (self-reference) Emp ||--o{ Emp : "manages / reports_to" ' Employee N --- M Project via EmployeeProject Emp ||--o{ EP : "assigned_as" Proj ||--o{ EP : "has_assignment" ' Employee 1 --- N FamilyMember Emp ||--o{ Fam : "has_family" ' Optional/annotative relation: Department headed by an Employee Dept }o--|| Emp : "headed_by (optional)" @enduml 著者：松尾正信株式会社京都テキストラボ代表取締役

プログラミング学習 Day 7：Database Semantics