數據庫設計Step by Step (9)——ER-to-SQL轉化
2011-07-25 21:06 知行思新 閱讀(20929) 評論(13) 收藏 舉報引言：前文（數據庫設計 Step by Step (8)——視圖集成）討論了如何把局部ER圖集成為全局ER圖。有了全局ER圖后，我們就可以把ER圖轉化為關系數據庫中的SQL表了。俯瞰整個數據庫生命周期（如下圖所示），找到我們的“坐標”。
把ER圖轉化為關系數據庫中的表結構是一個非常自然的過程。許多ER建模工具除了輔助繪制ER圖外，還能自動地把ER圖轉化為SQL表。
從ER模型到SQL表
從ER圖轉化得到關系數據庫中的SQL表，一般可分為3類。
1. 轉化得到的SQL表與原始實體包含相同信息內容。該類轉化一般適用于：
二元“多對多”關系中，任何一端的實體
二元“一對多”關系中，“一”一端的實體
二元“一對一”關系中，某一端的實體
二元“多對多”回歸關系中，任何一端的實體（注：關系兩端都指向同一個實體）
三元或n元關系中，任何一端的實體
層次泛化關系中，超類實體
2. 轉化得到的SQL表除了包含原始實體的信息內容之外，還包含原始實體父實體的外鍵。該類轉化一般適用于：
二元“一對多”關系中，“多”一端的實體
二元“一對一”關系中，某一端的實體
二元“一對一”或“一對多”回歸關系中，任何一端的實體
該轉化是處理關系的常用方法之一，即在子表中增加指向父表中主鍵的外鍵信息。
3. 由“關系”轉化得到的SQL表，該表包含“關系”所涉及的所有實體的外鍵，以及該“關系”自身的屬性信息。該類轉化一般適用于：
二元“多對多”關系
二元“多對多”回歸關系
三元或n元關系
該轉化是另一種常用的關系處理方法。對于“多對多”關系需要定義為一張包含兩個相關實體主鍵的獨立表，該表還能包含關系的屬性信息。
轉化過程中對于NULL值的處理規則
1. 當實體之間的關系是可選的，SQL表中的外鍵列允許為NULL。
2. 當實體之間的關系是強制的，SQL表中的外鍵列不允許為NULL。
3. 由“多對多”關系轉化得到的SQL表，其中的任意外鍵列都不允許為NULL。
一般二元關系的轉化
1. “一對一”，兩實體都為強制存在
當兩個實體都是強制存在的（如圖1所示），每一個實體都對應轉化為一張SQL表，并選擇兩個實體中任意一個作為主表，把它的主鍵放入另一個實體對應的SQL表中作為外鍵，該表稱為從表。
（圖1 “一對一”，兩實體都為強制存在）
圖1表示的語義為：每一張報表都有一個縮寫，每一縮寫只代表一張報表。轉化得到的SQL表定義如下：
create table report ( report_no integer, report_name varchar(256), primary key(report_no) ); create table abbreviation ( abbr_no char(6), report_no integer not null unique, primary key(abbr_no), foreign key(report_no) references report on delete cascade on update cascade );
注：本節中所有SQL代碼在SQL Server 2008環境中測試通過。
2. “一對一”，一實體可選存在，另一實體強制存在
當兩個實體中有一個為“可選的”，則“可選的”實體對應的SQL表一般作為從表，包含指向另一實體的外鍵（如圖2所示）。
（圖2 “一對一”，一實體可選存在，另一實體強制存在）
圖2表示的語義為：每一個部門必須有一位經理，大部分員工不是經理，一名員工最多只能是一個部門的經理。轉化得到的SQL表定義如下：
create table employee ( emp_id char(10), emp_name char(20), primary key(emp_id) ); create table department ( dept_no integer, dept_name char(20), mgr_id char(10) not null unique, primary key(dept_no), foreign key(mgr_id) references employee on update cascade );
另一種轉化方式是把“可選的”實體作為主表，讓“強制存在的”實體作為從表，包含外鍵指向“可選的”實體，這種方式外鍵列允許為NULL。以圖2為例，可把實體Employee轉化為從表，包含外鍵列dept_no指向實體Department，該外鍵列將允許為NULL。因為Employee的數量遠大于Department的數量，故會占用更多的存儲空間。
3. “一對一”，兩實體都為可選存在
當兩個實體都是可選的（如圖3所示），可選任意一個實體包含外鍵指向另一實體，外鍵列允許為NULL值。
（圖3 “一對一”，兩實體都為可選存在）
圖3表示的語義為：部分臺式電腦被分配給部分工程師，一臺電腦只能分配給一名工程師，一名工程師最多只能分配到一臺電腦。轉化得到的SQL表定義如下：
create table engineer ( emp_id char(10), emp_name char(20), primary key(emp_id) ); create table desktop ( desktop_no integer, emp_id char(10), primary key(desktop_no), foreign key(emp_id) references engineer on delete set null on update cascade );
4. “一對多”，兩實體都為強制存在
在“一對多”關系中，無論“多”端是強制存在的還是可選存在的都不會影響其轉化形式，外鍵必須出現在“多”端，即“多”端轉化為從表。當“一”端實體是可選存在時，“多”端實體表中的外鍵列允許為NULL。
（圖4 “一對多”，兩實體都為強制存在）
圖4表示的語義為：每名員工都屬于一個部門，每個部門至少有一名員工。轉化得到的SQL表定義如下：
create table department ( dept_no integer, dept_name char(20), primary key(dept_no) ); create table employee ( emp_id char(10), emp_name char(20), dept_no integer not null, primary key(emp_id), foreign key(dept_no) references department on update cascade );
5. “一對多”，一實體可選存在，另一實體強制存在
（圖5 “一對多”，一實體可選存在，另一實體強制存在）
圖5表示的語義為：每個部門至少發布一張報表，一張報表不一定由某個部門來發布。轉化得到的SQL表定義如下：
create table department ( dept_no integer, dept_name char(20), primary key(dept_no) ); create table report ( report_no integer, dept_no integer, primary key(report_no), foreign key(dept_no) references department on delete set null on update cascade );
注：解釋一下report表創建腳本的最后一行“on delete set null on update cascade”的用處。當沒有這一行時，更新department表中dept_no字段會失敗，刪除department中記錄也會失敗，報出與外鍵約束沖突的提示。如果有了最后一行，更新department表中dept_no字段，report表中對應記錄的dept_no也會同步更改，刪除department中記錄，會使report表中對應記錄的dept_no值變為NULL。
6. “多對多”，兩實體都為可選存在
在“多對多”關系中，需要一張新關系表包含兩個實體的主鍵。無論兩邊實體是否為可選存在的，其轉化形式一致，關系表中的外鍵列不能為NULL。實體可選存在，在關系表中表現為是否存在對應記錄，而與外鍵是否允許NULL值無關。
（圖6 “多對多”，兩實體都為可選存在）
圖6表示的語義為：一名工程師可能是專業協會的會員且可參加多個專業協會。每一個專業協會可能有多位工程師參加。轉化得到的SQL表定義如下：
create table engineer ( emp_id char(10), primary key(emp_id) ); create table prof_assoc ( assoc_name varchar(256), primary key(assoc_name) ); create table belongs_to ( emp_id char(10), assoc_name varchar(256), primary key(emp_id, assoc_name), foreign key(emp_id) references engineer on delete cascade on update cascade, foreign key(assoc_name) references prof_assoc on delete cascade on update cascade );
二元回歸關系的轉化
對于“一對一”或“一對多”回歸關系的轉化都是在SQL表中增加一列與主鍵列類型、長度相同的外鍵列指向實體本身。外鍵列的命名需與主鍵列不同，表明其用意。外鍵列的約束根據語義進行確定。
7. “一對一”，兩實體都為可選存在
（圖7 “一對一”，兩實體都為可選存在）
圖7表示的語義為：公司員工之間可能存在夫妻關系。轉化得到的SQL表定義如下：
create table employee ( emp_id char(10), emp_name char(20), spouse_id char(10), primary key(emp_id), foreign key(spouse_id) references employee );
8. “一對多”，“一”端為強制存在，“多”端為可選存在
（圖8 “一對多”，“一”端為強制存在，“多”端為可選存在）
圖8表示的語義為：工程師被分為多個組，每個組有一名組長。轉化得到的SQL表定義如下：
create table engineer ( emp_id char(10), leader_id char(10) not null, primary key(emp_id), foreign key(leader_id) references engineer );
“多對多”回歸關系無論是可選存在的還是強制存在的都需新增一張關系表，表中的外鍵列須為NOT NULL。
9. “多對多”，兩端都為可選存在
（圖9 “多對多”，兩端都為可選存在）
圖9表示的語義為：社交網站中人之間的朋友關系，每個人都可能有很多朋友。轉化得到的SQL表定義如下：
create table person ( person_id char(10), person_name char(20), primary key(person_id) ); create table friend ( person_id char(10), friend_id char(10), primary key(person_id, friend_id), foreign key(person_id) references person, foreign key(friend_id) references person, check(person_id < friend_id) );
三元和n元關系的轉化
無論哪種形式的三元關系在轉化時都會創建一張關系表包含所有實體的主鍵。三元關系中，“一”端實體的個數決定了函數依賴的數量。因此，“一對一對一”關系有三個函數依賴式，“一對一對多”關系有兩個函數依賴式，“一對多對多”關系有一個函數依賴式。“多對多對多”關系的主鍵為所有外鍵的聯合。
10. “一對一對一”三元關系
（圖10 “一對一對一”三元關系）
圖10表示的語義為：
1名技術員在1個項目中使用特定的1本記事簿
1本記事簿在1個項目中只屬于1名技術員
1名技術員的1本記事簿只用于記錄1個項目
注：1名技術員仍可以做多個項目，對于不同的項目維護不同的記事簿。
轉化得到的SQL表定義如下：
create table technician ( emp_id char(10), primary key(emp_id) ); create table project ( project_name char(20), primary key(project_name) ); create table notebook ( notebook_no integer, primary key(notebook_no) ); create table uses_notebook ( emp_id char(10), project_name char(20), notebook_no integer not null, primary key(emp_id, project_name), foreign key(emp_id) references technician on delete cascade on update cascade, foreign key(project_name) references project on delete cascade on update cascade, foreign key(notebook_no) references notebook on delete cascade on update cascade, unique(emp_id, notebook_no), unique(project_name, notebook_no) );
函數依賴
emp_id, project_name -> notebook_no
emp_id, notebook_no -> project_name
project_name, notebook_no -> emp_id
11. “一對一對多”三元關系
（圖11 “一對一對多”三元關系）
圖11表示的語義為：
參與1個項目的1名員工只會在1個地點做該項目
1名員工在1個地點只能做1個項目
1個地點的1個項目可能有多名員工參與
注：1名員工可以在不同的地點做不同的項目
轉化得到的SQL表定義如下：
create table employee ( emp_id char(10), emp_name char(20), primary key(emp_id) ); create table project ( project_name char(20), primary key(project_name) ); create table location ( loc_name char(15), primary key(loc_name) ); create table assigned_to ( emp_id char(10), project_name char(20), loc_name char(15) not null, primary key(emp_id, project_name), foreign key(emp_id) references employee on delete cascade on update cascade, foreign key(project_name) references project on delete cascade on update cascade, foreign key(loc_name) references location on delete cascade on update cascade, unique(emp_id, loc_name) );
函數依賴：
emp_id, loc_name -> project_name
emp_id, project_name -> loc_name
12. “一對多對多”三元關系
（圖12 “一對多對多”三元關系）
圖12表示的語義為：
1個項目中的1名工程師只會有1名經理
1個項目中的1名經理會帶領多名工程師做該項目
1名經理和他手下的1名工程師可能參與多個項目
轉化得到的SQL表定義如下：
create table project ( project_name char(20), primary key(project_name) ); create table manager ( mgr_id char(10), primary key(mgr_id) ); create table engineer ( emp_id char(10), primary key(emp_id) ); create table manages ( project_name char(20), mgr_id char(10) not null, emp_id char(10), primary key(project_name, emp_id), foreign key(project_name) references project on delete cascade on update cascade, foreign key(mgr_id) references manager on delete cascade on update cascade, foreign key(emp_id) references engineer on delete cascade on update cascade );
函數依賴：
project_name, emp_id -> mgr_id
13. “多對多對多”三元關系
（圖13 “多對多對多”三元關系）
圖13表示的語義為：
1名員工在1個項目中可以運用多種技能
1名員工的1項技能可以在多個項目中運用
1個項目中的1項技能可以被參與該項目的多名員工運用
轉化得到的SQL表定義如下：
create table employee ( emp_id char(10), emp_name char(20), primary key(emp_id) ); create table skill ( skill_type char(15), primary key(skill_type) ); create table project ( project_name char(20), primary key(project_name) ); create table sill_used ( emp_id char(10), skill_type char(15), project_name char(20), primary key(emp_id, skill_type, project_name), foreign key(emp_id) references employee on delete cascade on update cascade, foreign key(skill_type) references skill on delete cascade on update cascade, foreign key(project_name) references project on delete cascade on update cascade );
函數依賴：
無
泛化與聚合
泛化抽象結構中的超類實體和各子類實體分別轉化為對應的SQL表。超類實體轉化得到的表包含超類實體的鍵和所有公共屬性。子類實體轉化得到的表包含超類實體的鍵和子類實體特有的屬性。
要保證泛化層次中數據的完整性就必須保證某些操作在超類表和子類表的之間的同步。若超類表的主鍵需做更新，則子類表中對應記錄的外鍵必須一起更新。若需刪除超類表中的記錄，子類表中對應記錄也需一起刪除。我們可以在定義子類表時加入外鍵級聯約束。這一規則對于覆蓋與非覆蓋的子類泛化都適用。
14. 泛化層次關系
（圖14 泛化層次關系）
圖14表示的語義為：
個人可能是一名員工，或是一位顧客，或同時是員工與顧客，或兩者都不是
轉化得到的SQL表定義如下：
create table individual ( indiv_id char(10), indiv_name char(20), indiv_addr char(20), primary key(indiv_id) ); create table employee ( emp_id char(10), job_title char(15), primary key(emp_id), foreign key(emp_id) references individual on delete cascade on update cascade ); create table customer ( cust_no char(10), cust_credit char(12), primary key(cust_no), foreign key(cust_no) references individual on delete cascade on update cascade );
有些數據庫開發者還會在超類表中增加一個鑒別屬性。鑒別屬性對于每一種子類有不同的值，表示從哪一個子類中能獲得進一步的信息。
聚合抽象的轉化方式也是為超類實體和每一個子類實體生成SQL表，但聚合中的超類與子類沒有公共屬性和完整性約束。聚合的主要功能是提供一種抽象來輔助視圖集成的過程。
轉化步驟
以下總結了從ER圖到SQL表的基本轉化步驟
1. 把每一個實體轉化為一張表，其中包含鍵和非鍵屬性。
2. 把每一個“多對多”二元或二元回歸關系轉化為一張表，其中包含實體的鍵和關系的屬性。
3. 把三元及更高元（n元）關系轉化為一張表。
讓我們一一對這三個步驟進行討論。
實體轉化
若兩個實體之間是“一對多”關系，把“一”端實體的主鍵加入到“多”端實體表中作為外鍵。若兩實體間是“一對一”關系，把某個“一”端實體的主鍵放入另一實體表中作為外鍵，加入外鍵的實體理論上可以任選，但一般會遵循如下原則：按照實體間最為自然的父子關系，把父實體的鍵放入子實體中；另一種策略是基于效率，把外鍵加入到具有較少行的表中。
把泛化層次中的每一個實體轉化為一張表。每張表都會包含超類實體的鍵。事實上子類實體的主鍵同時也是外鍵。超類表中還包含所有相關實體的公共非鍵屬性，其他表包含每一子類實體特有的非鍵屬性。
轉化得到的SQL表可能會包含not null, unique, foreign key等約束。每一張表必須有一個主鍵（primary key），主鍵隱含著not null和unique約束。
“多對多”二元關系轉化
每一個“多對多”二元關系能轉化為一張表，包含兩個實體的鍵和關系的屬性。
這一轉化得到的SQL表可能包含not null約束。在這里沒有使用unique約束的原因是關系表的主鍵是由各實體的外鍵復合組成的，unique約束已隱含。
三元關系轉化
每一個三元（或n元）關系轉化為一張表，包含相關實體的n個主鍵以及該關系的屬性。
這一轉化得到的表必須包含not null約束。關系表的主鍵由各實體的外鍵復合組成。n元關系表具有n個外鍵。除主鍵約束外，其他候選鍵(candidate key)也應加上unique約束。
ER-to-SQL轉化步驟示例
把數據庫設計Step by Step (7)——概念數據建模中最后得到的公司人事和項目數據庫的全局ER圖（圖9）轉化為SQL表。
1. 直接由實體生成的SQL表有：
Division Department Employee Manager Secretary Engineer
Technician Skill Project Location Prof_assoc Desktop
Workstation
2. 由“多對多”二元關系及“多對多”二元回歸關系生成的SQL表有：
belongs_to
3. 由三元關系生成的SQL表有：
skill_used assigned_to
總結與回顧
1. 通過一些簡單的規則就能把ER模型中的實體、屬性和關系轉化為SQL表。
2. 實體在轉化為表的過程中，其中的屬性一一被映射為表的屬性。
3. “一對一”或“一對多”關系中的“子”端實體轉化成的SQL表必須包含另一端實體的主鍵，作為外鍵。
4. “多對多”關系轉化為一張表，包含相關實體的主鍵，復合組成其自身的主鍵。同時這些鍵在SQL中定義為外鍵分別指向各自的實體。
5. 三元或n元關系被轉化為一張表，包含相關實體的主鍵。這些鍵在SQL中定義為外鍵。這些鍵中的子集定義為主鍵，其基于該關系的函數依賴。
6. 泛化層次的轉化規則要求子類實體從超類實體繼承主鍵。
7. ER圖中的可選約束在轉化為SQL時，表現為關系的某一端實體允許為null。在ER圖中沒有明確標識可選約束時，創建表時默認not null約束。
浙公網安備 33010602011771號