ER TO SQL語句

ER TO 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
);      

 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表中增加一列與主鍵列類型、長度相同的外鍵列指向實體本身。外鍵列的命名需與主鍵列不同，表明其用意。外鍵列的限制根據語義進行确定。

1.  “一對一”，兩實體都為可選存在

“一對一”，兩實體都為可選存在

圖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
);      

2. “一對多”，“一”端為強制存在，“多”端為可選存在

  “一對多”，“一”端為強制存在，“多”端為可選存在

圖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。

3. “多對多”，兩端都為可選存在

“多對多”，兩端都為可選存在

圖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元關系的轉化

無論哪種形式的三元關系在轉化時都會建立一張關系表包含所有實體的主鍵。三元關系中，“一”端實體的個數決定了函數依賴的數量。是以，“一對一對一”關系有三個函數依賴式，“一對一對多”關系有兩個函數依賴式，“一對多對多”關系有一個函數依賴式。“多對多對多”關系的主鍵為所有外鍵的聯合。

1. “一對一對一”三元關系

“一對一對一”三元關系

以上表示的語義為：

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

2. “一對一對多”三元關系

“一對一對多”三元關系

表示的語義為：

參與1個項目的1名員工隻會在1個地點做該項目

1名員工在1個地點隻能做1個項目

1個地點的1個項目可能有多名員工參與

注：1名員工可以在不同的地點做不同的項目

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

3. “一對多對多”三元關系

“一對多對多”三元關系

1個項目中的1名工程師隻會有1名經理

1個項目中的1名經理會帶領多名工程師做該項目

1名經理和他手下的1名工程師可能參與多個項目

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

4. “多對多對多”三元關系

（圖13 “多對多對多”三元關系）

1名員工在1個項目中可以運用多種技能

1名員工的1項技能可以在多個項目中運用

1個項目中的1項技能可以被參與該項目的多名員工運用

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表示的語義為：

個人可能是一名員工，或是一位顧客，或同時是員工與顧客，或兩者都不是

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限制。

你們的評論、回報，及對你們有所用，是我整理材料和博文寫作的最大的鼓勵和唯一動力。歡迎讨論和關注！

沒有整理與歸納的知識，一文不值！高度概括與梳理的知識，才是自己真正的知識與技能。

永遠不要讓自己的自由、好奇、充滿創造力的想法被現實的架構所束縛，讓創造力自由成長吧！

多花時間，關心他（她）人，正如别人所關心你的。理想的騰飛與實作，沒有别人的支援與幫助，是萬萬不能的。