MySQL數據庫項目化教程微課版V2項目一數據庫的設計項目任務知識目標學習目標任務1：技能競賽管理系統數據庫需求分析任務2：技能競賽數據庫ER模型設計任務3：使用MySQLWorkBench設計技能競賽數據庫（1）了解數據庫基本概念、基本理論知識（2）掌握數據庫設計方法（3）學會用工具軟件設計ER圖（4）數據庫的概念設計能力目標素質目標學習目標（1）使用工具軟件設計繪制ER圖的能力（2）具有關系型數據庫分析能力（3）具有數據庫邏輯設計能力（4）具有數據庫管理員管理數據庫能力（1）培養學生解決實際問題的獨立思考的素養（2）培養學生有團隊協作精神（3）培養學生思考分析解決問題的思維習慣（4）培養學生良好的心理素質（5）培養學生具有數據庫設計人員職業素養課程思政（素養提示）學習目標（1）了解操作系統等基礎設施軟件的國產化，理解基礎設施軟件的自主可控對我國的重大意義（2）數據庫設計的需要在需求雙方的共同溝通下完成，理解溝通與協作在數據庫設計階段的重要性，培養團隊協助能力知識導圖學習目標任務分析任務完成任務描述Part

2Part

3Part

1任務一 技能競賽管理系統數據庫需求分析過渡頁任務分析任務完成Part

1Part

2Part

3任務描述本次任務是依據技能競賽管理系統項目來分析其需求情況，依據需求情況來分析其數據庫設計任務，為技能競賽管理系統設計一套合理易用的數據庫。任務描述過渡頁任務分析任務完成Part

1Part

2Part

3任務描述數據庫（Database，DB）是按照一定的數據結構對數據進行組織、存儲和管理的容器，存儲和管理數據的倉庫。數據庫里存儲著數據庫的對象，如數據表、索引、視圖、存儲過程、函數、觸發器、事件等。數據庫管理系統（DataBaseManagementSystem，DBMS）是一個安裝在操作系統之上的用來管理、控制數據庫中各種數據庫對象的系統。任務分析12DBMS與DB的關系任務分析13數據庫系統（DatabaseSystem，DBS）任務分析14任務分析數據模型是對現實世界中的事物相應的特征進行抽象，并對相關的數據進行邏輯描述，為后續數據庫的設計提供參考。從應用的角度看，可以將數據模型分為三種類型：概念模型、邏輯模型和物理模型，這三種模型分別代表了在數據庫設計的中的三個階段。15任務分析概念模型是在需求分析人員了解清楚用戶的需求后，通過分析、總結和提煉，最終定義出來的一系列需求概念。讓數據庫設計人員準確的理解用戶的具體業務流程，以及這個流程中所涉及的名詞的作用及范圍。16任務分析E-R圖是比較典型的概念模型的表達形式，它剛好能夠滿足設計人員的需求：簡潔而又清晰，能有效的反映業務中的各個概念及概念之間的業務關系，又能反映業務關系中所涉及的相關數據。17任務分析從數據庫管理系統的角度看，邏輯模型是對概念模型進行具體化后的一個新階段。邏輯模型設計的關鍵是根據前述設計的概念模型，進行規劃和設計各項數據的組織結構，形成數據庫的設計文檔，為后續數據庫管理系統中實現物理模型提供文檔形式的標準參考依據。建立數據庫的邏輯模型，實際上就是根據現有的概念模型，進行規劃、設計出相應數據庫設計文檔，形成一套完整的“數據表”。這些表的集合，對具體的字段名稱、字段含義，數據類型以及約束條件等進行了詳細的定義。18任務分析物理模型可以理解為對數據的最底層抽象，它與具體的數據庫系統直接相關，也和數據庫系統使用的操作系統和軟硬件環境相關。從具體的實施角度看，就是根據上述的邏輯模型，綜合考慮數據庫管理系統的類型、操作系統環境、存儲空間的讀寫性能以及數據庫的擴展方案和優化方案等多方面的因素，結合實際運行條件，在實際的計算機環境中，將數據庫最終實現。用戶不是直接通過操作系統來存取數據庫中的數據，而是需要通過數據庫管理系統來調用操作系統的進程來管理、控制數據庫對象。關系數據庫管理系統（RelationalDataBaseManagementSystem，RDBMS），關系模型是數據庫管理系統的一種模型，是較常用的數據庫管理系統模型，數據庫模型除常用的關系模型外還有層次模型、網狀模型、面向對象模型等模式。任務分析關系數據庫管理系統(RelationalDatabaseManagementSystem)的特點：數據存儲以數據表的形式進行存放在數據庫中；數據表中的每一行稱為記錄，是記錄一個個體的相關屬性；數居表中的每一列稱為屬性，是一條記錄中記錄個體的某一屬性；一個數據表是由許多的行和列組成的，構成數據表，一個數據表記錄一個實體集；任務分析若干數據表組成數據庫（Database），數據庫中的數據表與數據表之間存在一定的聯系。技能競賽管理系統所使用到的數據庫是用來存儲和管理參賽選手信息、參賽成績等相關信息。具體數據涉及參賽選手學生信息、指導老師信息、賽前培訓信息、比賽信息、管理員信息、指導學生參賽的老師信息、學生報名參賽的信息、以及培訓指導參賽信息。這些數據信息要按照一定的規則存儲在數據庫中各個數據表內，并且數據表與數據表之前又存在一定的關聯。任務分析如多個年級多個專業的學生參加多項技能比賽，一個學生可參賽多項技能競賽，而每項技能競賽又可以有多個學生參加，每學生參加競賽有指導老師進行指導，老師可以指導多個技能競賽，這些關聯關系需要經過分析來進行提取，所以就需要進行數據庫設計，理順這些數據表之間的關系。任務分析數據庫在設計過程種需要遵循一定的原則，如實體的屬性應該僅存在某一實體中，如果存在多個實體中就會造成數據冗余，在數據庫設計時應該避免數據冗余，因為數據冗余會造成數據存儲容量增大，造成存儲空間的浪費。也不能因為擔心數據冗余使數據不完整，實體是一個單獨的個體，不能存在于另一個實體中成為其屬性，即一個數據表中不能包含另一個數據表。數據庫如果設計不完美，將會直接影響后期對數據操作的操作，如數據查詢、數據添加、數據修改、數據刪除等操作。任務分析學生表1-1中，存在學生實體（學號、姓名、性別、專業、班級名、所在院系）等屬性，學生實體中出現了表中套表的現象。因為班級名、所在院系聯系緊密，應該將班級名、所在院系屬性抽取出來分別放入班級實體、院系實體中。任務分析任務分析關系數據庫基本概念：?實體（Entity）：客觀存在并可相互區別的事物稱為實體，可以是具體的人、事、物或抽象的概念；?屬性（Attribute）：實體所具有的某一特性稱為屬性，一個實體可以由若干個屬性來描述；?碼（Key）：唯一標識實體的屬性集稱為碼，也成為鍵；?域（Domain）：屬性的取值范圍稱為該屬性的域；任務分析關系數據庫基本概念：?實體型（EntityType）：用實體名及其屬性名集合來抽象和刻畫同類實體稱為實體型；?實體集（EntitySet）：同一類型實體的集合稱為實體集；?聯系（Relationship）：現實世界中事物內部以及事物之間的聯系，在信息世界中反映為實體內部的聯系和實體之間的聯系；任務分析過渡頁任務分析任務完成Part

1Part

2Part

3任務描述實體型Entity）：具有相同屬性的實體具有相同的特征和性質，用實體名及其屬性名集合來抽象和刻畫同類實體。在E-R圖中用矩形表示，矩形框內寫明實體名。

屬性（Attribute）實體所具有的某一特性，一個實體可由若干個屬性來刻畫。在ER圖中用橢圓形表示，并用無向邊將其與相應的實體連接起來；任務完成任務完成任務完成聯系（Relationship）：數據對象彼此之間相互連接的方式稱為聯系，也稱為關系。聯系可分為一對一聯系、一對多聯系、多對多聯系3種類型。（1）一對一聯系（1:1）定義：如果對于實體集A中的每個實體，實體集B中至多有一個（也可以沒有）實體與之聯系，反之亦然，則稱實體集A與實體集B具有一對一聯系，記為1:1。如圖1-4所示。例如，一個班級只有一個正班長，而每個正班長只屬于一個班級，則班級與班長的聯系是一對一的聯系。（2）一對多聯系（1:N）定義：如果對于實體集A中的每個實體，實體集B中有n個實體（n≥0）與之聯系，反之，對于實體集B中的每個實體，實體集A中至多只有一個實體與之聯系，則稱實體集A與實體集B有一對多聯系，記為1:N。任務完成（3）多對多聯系（M:N）定義：如果對于實體集A中的每個實體，實體集B中有n個實體（n≥0）與之聯系，反之，對于實體集B中的每個實體，實體集A中也有m個實體（m≥0）與之聯系，則稱實體集A與實體B具有多對多聯系，記為M:N。一對一聯系一對多聯系多對多聯系任務完成技能競賽管理系統E-R實體模型，一個學生可以參加多項競賽，一個競賽項目也可以有多個學生來參加，故參賽學生與競賽項目之間是屬于多對多的關系。教師指導學生參加比賽，一個老師可以指導多個學生參加技能比賽，一個學生可以參加多項競賽，可以參加多個老師指導的培訓，學生參加競賽與老師指導競賽之間也是屬于多對多的關系。根據實現情況分析出技能競賽管理系統E-R圖，如下圖所示。任務完成35任務完成注意事項MySQL是一種開源的關系型數據庫管理系統（RDBMS），目前中小型企業大多數使用MySQL數據庫來存儲管理企業的數據，其系統使用最常用的數據庫管理語言–結構化查詢語言（StructuredQueryLanguage，SQL）進行數據庫管理。任務總結在本任務中介紹關系數據庫管理系統基本概念、相關理論知識，如何把客觀世界的事務轉換成信息世界的關系模型。在對技能競賽管理系統中的數據庫進行需求分析，根據需求分析規格說明書完成定義實體集及屬性，實體集的主碼，用E-R圖描述被實體集；E-R圖三要素：實體集、屬性和聯系集，并用長方形表示實體，橢圓形表示屬性，并用無向邊把實體與其屬性連接起來，菱形表示實體間的聯系，來建立技能競賽管理系統的數據庫關系。任務分析任務完成任務描述Part

2Part

3Part

1任務二 技能競賽數據庫E-R模型設計過渡頁任務分析任務完成Part

1Part

2Part

3任務描述技能競賽管理系統的數據庫中涉及的實體主要有參賽學生、班級信息、指導教師、系部信息、參賽信息、寒前培訓信息等相關實體。根據設計完成的E-R圖來為每個實體建立一張數據表。任務描述過渡頁任務分析任務完成Part

1Part

2Part

3任務描述關系數據庫中的數據表是二維表，是由列和行構成的，二維表是規范表，要求每行有相同的列數，每列要有相同的行數，并且數據表中的每行記錄都必須是唯一的，即在同一張數據表中不允許出現完全相同的兩條記錄。關系數據表中必須存在關鍵字段（Key）來唯一標識表中的每行記錄，關鍵字實際上能夠唯一標識表記錄字段或字段組合。任務分析例如：在學生表中，由于學號字段不允許重復且學號字段不允許取空值（NULL），故學號可以作為學生表的關鍵字。在所有的關鍵字中選擇一個關鍵字作為該數據表的主關鍵字，簡稱主鍵（PrimaryKey）。數據表中的主鍵可以是一個字段，也可以是多個字段組合。表中的主鍵的值具有唯一性且不能取空值（NULL）。一張數據表中可以有多個關鍵字，但只能有一個主鍵，且主鍵肯定屬于關鍵字。任務分析定義數據表中主鍵時，一般把取值簡單的關鍵字作為主鍵。在設計數據表時，慎用復合主鍵，復合主鍵會給維護數據表時帶來不便。數據庫開發人員如果不能從已有的字段中選擇一個主鍵，可以向數據表中添加一個對記錄不會產生影響，也沒有實際意義的字段作為該表的主鍵，如給數據表中添加一個記錄序號，通過記錄序號來確定每個個體，并該記錄序號可以設置為由程序自動生成，以免人工錄入時出錯。任務分析過渡頁任務分析任務完成Part

1Part

2Part

3任務描述定義數據表時需要確定字段的數據類型，表中字段類型設計的是否恰當關系到數據庫的存儲空間，為每張數據表中的字段選擇最合適的數據類型是數據庫設計過程中一個重要的步驟，切記為字段隨意設置數據類型。為字段設置合適的數據類型既可以有效地節省數據庫的存儲空間，也可以提升數據的計算性能，節省數據檢索時間，提高效率。MySQL數據庫管理系統中常用的數據類型包括：數值類型、字符串類型和日期類型。任務完成任務完成數據庫完整性（DatabaseIntegrity）是指數據庫中數據在邏輯上的一致性、正確性、有效性和相容性。數據庫完整性由各種各樣的完整性約束來保證，因此可以說數據庫完整性設計就是數據庫完整性約束的設計。MySQL數據庫管理系統定義約束（Constraint）條件主要有主鍵（PrimaryKey）約束、外鍵（ForeignKey）約束、唯一性（Unique）約束、默認值（Default）約束、非空（NotNULL）約束、檢查（Check）約束等6種常用的約束條件。任務完成（1）主鍵（PrimaryKey）能夠唯一標識表中的每行記錄。一個表只能有一個主鍵，但可以有多個候選鍵。主鍵常常與外鍵構成參照完整性約束，防止出現數據不一致。主鍵可以保證記錄的唯一和主鍵域非空，數據庫管理系統對于主鍵自動生成唯一索引，所以主鍵也是一個特殊的索引。如學生表里有學號和姓名，姓名可能有重名的，但學號確是唯一的，要從學生表中搜索一條紀錄如查找一個人，就只能根據學號去查找，這才能找出唯一的一個學生，這就是主鍵。可以把主鍵設為自動增長的類型，如：idint（10）notnullprimarykeyauto_increment。任務完成（2）外鍵（ForeignKey）是用于建立和加強兩個表數據之間的鏈接的一列或多列。外鍵約束主要用來維護兩個表之間數據的一致性。一張數據表的外鍵就是另一張數據表的主鍵，外鍵將兩表聯系起來。一般情況下，要刪除一張表中的主鍵必須首先要確保其他表中的沒有相同記錄值外鍵（即該表中的主鍵沒有一個外鍵和它相關聯）。如果表A中的一個字段a對應于表B的主鍵b，則字段a稱為表A的外鍵，此時存儲在表A中字段a的值，要么是NULL，要么是來自于表B主鍵b的值。任務完成（3）唯一性（Unique）約束，是對數據表的列字段進行強制執行唯一性值，例如學生表中學生的學號必須具有唯一性，學生的姓名可以不具有唯一性，也就是允許一張數據表中有相同名字的學生，但為了區分學生實體集間的個體信息，可以將學生的學號設置為唯一性約束，通過唯一性來區分相同姓名的學生。MySQL數據庫用UNIQUE約束來對列字段進行約束，它定義了限制列或一組列中的值為唯一的規則。限制數據表中的字段值不重復，則可將該字段添加唯一性約束。與主鍵約束不同，一張表中可以存在多個唯一性約束，并且滿足唯一性約束的字段可以取NULL值。任務完成（4）默認值（Default）約束，數據表在創建列字段時可以指定默認值，當插入數據時如果未主動錄入值時，則自動添加默認值，DEFAULT（默認值）與NOTNULL配合使用，如學生表中學生的性別有男或女兩種情況，但機電專業的學生男同學比較多，則可以將該性別字段設為黙認值“男”，在錄入學生性別信息時，如果沒有錄入數據，則系統自動設置其性別信息為“男”。任務完成（5）非空（NotNULL）約束，限制數據表中的字段值不能取NULL值，如學生表中學生的姓名不能為空，則可以考慮將該字段添加非空約束。（6）檢查（Check）約束，檢查約束是用于檢查字段的輸入值是否滿足指定的條件，輸入（或者修改）數據時，若字段值不符合檢查約束指定的條件，則數據不能寫入該字段。如在學生表中學生的年齡字段設置成在15—35之間，設為檢查約束后如果在錄入學生年齡超35或低于15時，該條記錄則是一條無效記錄，不能錄入數據表中。任務完成student表任務完成

techer表任務完成

project表任務完成class表任務完成department表任務完成

st_project表任務完成tc_project表任務完成

admin表任務完成第一范式（1NF）：第一范式是指數據庫表中的每列都是不可分割的基本數據項，同一列中不能有多個值，即實體中的某個屬性不能有多個值或者不能有重復的屬性。如果一張數據表內同類字段不重復出現，則該表滿足第一范式，如果數據庫不滿足第一范式，則對數據庫操作將會出現諸如插入異常、刪除異常、修改復雜等操作問題。任務完成第二范式（2NF）：第二范式（2NF）是在第一范式（1NF）的基礎上建立起來的，即滿足第二范式（2NF）必須先滿足第一范式（1NF）。第二范式（2NF）要求數據庫表中的每個實例或行必須可以被唯一地區分。一張數據表滿足第一范式的基礎上，如果每個“非關鍵字”字段僅僅函數依賴于主鍵，那該數據表滿足第二范式。第二范式首先是滿足1NF，另外包含兩部分內容，一是表必須有一個主鍵；二是沒有包含在主鍵中的列必須完全依賴于主鍵，而不能只依賴于主鍵的一部分。任務完成鍵字為組合關鍵字（學號，競賽項目號），但由于非主屬性競賽項目名稱僅依賴于競賽項目號，對關鍵字（學號，競賽項目號）只是部分依賴，而不是完全依賴，因此此種方式會導致數據冗余以及更新異常等問題，解決辦法是將其分為兩個關系模式：學生表（學號，競賽項目號，成績）和競賽項目表（競賽項目號，競賽項目名稱），新關系通過學生表中的外關鍵字競賽項目號聯系，在需要時進行連接。任務完成第三范式（3NF）：在一張數據表滿足第二范式的要求，并且不存在“非關鍵字”字段函數據依賴于任何其他“非關鍵字”字段，那么該數據表滿足第三范式的要求，滿足第三范式的數據庫不會出現插入異常、刪除異常、修改復雜等現象。如student（St_no學號，St_name姓名，dp_id所在系，dp_name系名稱，LOCATION系地址）。任務完成關鍵字St_no學號決定各個屬性。由于是單個關鍵字，沒有部分依賴的問題，肯定是2NF。但這關系肯定有大量的冗余，有關學生所在的幾個屬性dp_id所在系，dpname系名稱，LOCATION系地址將重復存儲，插入，刪除和修改時也將產生類似重復的情況。分析原因：關系中存在傳遞依賴造成的。即St_no學號->dp_id所在系。而dp_id所在系->St_no學號卻不存在，dp_id所在系->LOCATION系地址，因此關鍵字St_no學號對LOCATION系地址函數決定是通過傳遞依賴dp_id所在系->LOCATION系地址實現的。也就是說，St_no學號不直接決定非主屬性LOCATION系地址。解決目地：每個關系模式中不能留有傳遞依賴。解決方法：分為兩個關系student學生（St_no學號，St_name姓名，dp_name所在院系），department院系部（dp_id所在系，dp_name系名稱，LOCATION系地址）。任務完成注意事項任務總結本次任務是根據技能競賽E-R圖實體-聯系（EntityRelationshipDiagram）來設計具體的數據表。對數據表中字段的數據類型進行說明，字段類型值設置過大會導致數據庫存儲容量龐大，浪費存儲空間，字段的類型設置過小會導致數據表中的字段值存儲不進去。設計數據表時需要遵循一定的質量規范及設計原則。范式即數據庫設計范式，是符合某一種級別的關系模式的集合。構造數據庫必須遵循一定的規則。在關系數據庫中，這種規則就是范式。關系數據庫中的關系必須滿足一定的要求，即滿足不同的范式。特別強調設計過程中遵守第一范式、第二范式、第三范式。任務分析任務完成任務描述Part

2Part

3Part

1任務三 使用ER/Studio設計技能競賽數據庫過渡頁任務分析任務完成Part

1Part

2Part

3任務描述使用MySQLWorkbench設計技能競賽數據庫。任務描述過渡頁任務分析任務完成Part

1Part

2Part

3任務描述使用MySQLWorkbench軟件，可以從MySQL的官方網站中下載相應的軟件包，具體的網址為：//products/workbench/，然后進行安裝。在本節的演示過程中，將使用Windows版本的安裝包，下載當前最新的軟件包，下載后完成安裝，即可啟動該軟件，如需要其他操作系統環境的安裝包，選擇合適的系統環境的軟件包即可。任務分析任務分析

啟動工具

創建新的模型新模型的設計界面任務分析74模型參數設置任務分析圖表設計界面（E-R圖）添加新的E-R圖任務分析表的參數設置任務分析修改默認的存儲數據庫任務分析表的常用操作（單擊右鍵）任務分析表的定制界面任務分析外鍵設置任務分析創建關系一個數據庫中的實體與實體之間是有一定的聯系或關聯的，要實現關系模型中的聯系可以按照相關關系建產關聯。如下圖，將實體entity與另一個實體test建立關聯關系，是將test和entity創建關系；先單擊如下圖標中的工具欄創建關聯關系，再將兩者連接起來，先單擊主表，然后在單擊關聯表，這樣關聯關系就建立起來了，如后所示。任務分析82以典型的班級和學生的關系為例：任務分析83班級表與學生表任務分析84以班級編號為關聯，實現兩個表的關系連接。班級與學生的關系任務分析85完成班級信息和學生信息兩個數據表的關聯后，可以通過MySQLWorkBench進行導出操作，創建所需SQL腳本。導出SQL設置步驟任務分析86SQL內容任務分析87

以前述的班級學生模型為例，在完成模型設計后，可以將其直接導入到數據庫系統中。但是，在啟動正向工程前，需要注意導入的目標數據庫中，是否有重名的數據表，是否已經存儲了相應的數據記錄，避免實施正向工程后，丟失數據庫系統中的數據，如果需要調整導入數據庫的名稱，進行調整。通常，使用MySQLWorkBench工具，主要以下兩個步驟完成：首先，連接到指定的數據庫，在本次操作過程中，以本地的數據庫為例。可以通過三種種方式打開數據庫連接操作。任務分析88數據庫連接操作任務分析89SQL連接測試其他SQL連接方式任務分析90數據庫狀態信息任務分析91打開已創建的模型，實施正向工程操作。可以使用快捷鍵【Ctrl】+【G】，或者點擊菜單【Database】【ForwardEngineer】，啟動向導。如圖1-35所示，啟動正向工程向導，啟動后，可以選擇創建一個臨時性連接，或者已存儲的連接。在本次實踐中，使用前面創建的“local-database”連接，然后點解【Next】進入下一步。任務分析92正向工程的過濾器設置與腳本預覽任務分析93完成導入設置任務分析94正向工程實施效果任務分析過渡頁任務分析任務完成Part

1Part

2Part

3任務描述96根據前述的ER模型設計，采用任務二中的表1-2至表1-9的內容，完成技能競賽管理系統數據庫表的設計。任務完成97student與teacher實體任務完成98Project與class實體任務完成99department與st_project實體任務完成100tc_project與admin實體任務完成101外鍵關聯示例在根據各個數據表之間的關系，添加相應外鍵時，要注意外鍵的命名不能重復，否則在正向工程的向導中，SQL腳本轉化為物理模型的步驟時，將提示錯誤。任務完成102關系圖任務完成103正向工程實施效果任務完成注意事項任務總結通過MySQLWorkBench工具，可以通過可視化的方式，設計技能競賽管理系統數據庫，根據E-R模型來創建實體，并且為實體添加實體的屬性，并正確選擇實體屬性的數據類型，再根據實體與實體之間的關系來建立關聯關系，根據建立的關系創建技能競賽數據庫實體E-R圖，通過正向工程轉化為物理模型。1051.國產化軟件與信創領域的發展自2018年以來，中美貿易摩擦持續不斷，軟