項目八設計與實現政務平臺數據庫346347任務1創建政務平臺數據庫任務2編輯政務平臺數據庫任務3查詢政務平臺數據庫任務4使用索引和視圖優化政務平臺數據庫創建政務平臺數據庫任務13481.?能對數據庫功能需求和性能需求進行分析。2.?能繪制數據庫的E-R圖，能寫出數據表的關系模式。3.?能使用CREATE?DATABSE創建數據庫，能使用CREATE?TABLE創建數據表。4.?能使用INSERT?INTO?VALUES語句，插入一條或多條記錄到表中，能選擇恰當的數據類型。5.?能在創建表時使用主鍵、外鍵等約束。349政務平臺面向廣大社會群眾，發布政府政策新聞、公開信息、機構設置等信息，其后臺需要政務數據庫的支持，在開發數據庫之前，需要對政務平臺的數據庫進行前期的

需求分析，數據庫分析與設計完成后，公司的數據庫工程師接到創建政務平臺數據庫(policyplatformdatabase)的任務，現要求創建一個名為zw的數據庫，包括Information、Organization、User三個表，

分別定義主鍵、外鍵等約束。數據庫zw的數據庫關系圖如圖所示。350351數據庫zw的數據庫關系圖一、政務平臺數據庫需求分析通過對系統的終端用戶、政府職能部門的調查分析，定義政務平臺數據庫的功能需求。政務平臺數據庫的功能需求主要包括用戶管理、機構管理及信息發布和查詢等功能。用戶管理包括用戶的注冊、登錄、權限控制等；機構管理包括機構信息的錄入和管理；信息發布和查詢功能包括發布新信息、查詢已發布信息、按機構過濾信息等。此外，用戶角色的劃分也是必要的，以便不同角色的用戶有不同的操作權限。352政務平臺數據庫的性能需求涉及查詢和更新操作的效率，尤其在信息發布頻繁的情況下，要求數據庫能夠迅速處理大量用戶登錄、信息發布和查詢的請求。由于機構與信息存在一對多關系，需要優化查詢機構發布的所有信息的性能。同時，對于信息的查詢，可能需要考慮按標題、按內容等字段進行模糊查詢，因此需要有相應的索引來提高政務平臺信息的檢索速度。353二、概念結構設計階段在需求分析的基礎上，通過實體關系模型（ER模型）等工具，將需求轉化為數據庫設計的概念模型，確定實體、關系及其屬性，并建立實體間的關系模式。在設計政務平臺數據庫時，需要考慮實體包括信息、機構、用戶及其屬性。在概念結構設計階段，繪制出數據庫的E-R圖。政務平臺數據庫的E-R圖如圖所示。354355政務平臺數據庫的E-R圖將需求轉化為概念模型，每個實體轉化為一個表，實體的屬性轉化為列，并確定主鍵。信息（信息ID、信息標題，信息內容）Information(InformationID,Tile,Content)機構（機構ID，機構名稱，負責人，聯系電話）Organization(OrganizationID,Name,Head,ContactNumber)356用戶（用戶ID，用戶名，密碼，姓名，角色，機構ID）User(UserID,Username,Password,FullName,Role,OrganizationID)其中，UserID為主鍵，OrganizationID為外鍵，指向機構的主鍵。對于聯系也進行轉化，機構與信息是一對多關系，創建如下關系模式。信息公開（信息ID、發布機構，發布日期）Information_Organization_Relation(InformationID,OrganizationID,PublishDate)357三、邏輯設計階段在概念設計的基礎上，使用關系模型，將概念模型轉化為數據庫系統所支持的關系模式，包括確定表結構、鍵的定義、約束條件等。本項目使用SQLServer數據庫系統，確定了各個表的結構，Information信息表見下表，Organization機構表見下表，User用戶表見下表，Information_Organization_Relation信息公開表見下表。358359Information信息表Organization機構表360User用戶表Information_Organization_Relation信息公開表四、實施階段1.?創建數據庫打開SSMS，連接到數據庫服務器，創建政務平臺數據庫，數據庫名為zw。3612.?創建數據表在數據庫zw中，創建數據表。（1）創建Information信息表362（2）創建Organization機構表363（3）創建User用戶表364（4）創建Information_Organization_Relation信息公開表表創建完成后，可以創建數據庫關系圖，檢測與任務描述是否一致，顯示多張表之間的主外鍵關系。3653.?插入數據在各個表中插入數據，注意外鍵關系，防止違反約束。366367368編輯政務平臺數據庫任務23691.?能使用INSERT?INTO?VALUES語句，插入一條或多條記錄到表中。2.?能使用UPDATE語句更新指定記錄。3.?通使用DELETE語句刪除符合條件的記錄。370公司的數據庫工程師已經完成了政務平臺數據庫zw的創建，各個表都已經有了數據。在平臺使用的過程中，需要添加3條新的信息記錄。環保局發布“重要通知”，內容為“環保局發布新的環境污染防治政策，加強大氣、水、土壤等環境保護工作”。教育局發布“教育政策”，內容為“教育部出臺新政策，促進教育公平和優質教育資源的均衡分配”。人社局發布“培訓通知”，內容為“關于加強新職業培訓工作的通知”。更新User表中用戶“張三”的密碼為“Zhangsan@88”。刪除環保局的所有信息發布記錄。371一、插入記錄添加3條新的信息記錄，分別是環保局、教育局和人社局的信息，在信息表中插入3條記錄。372在信息公開表中插入信息發布的時間和負責機構。373二、更新User表中用戶的密碼更新User表中用戶“張三”的密碼為“Zhangsan@88”。374三、刪除環保局的所有信息發布記錄刪除信息公開表中環保局的所有信息發布記錄，還要刪除信息表中屬于環保局的信息。因為有外鍵關系的存在，所以不能先刪除信息表的內容。實現這種有外鍵關系的刪除，一種方法是在設計信息公開表時加上語句“FOREIGNKEY(InformationID)REFERENCESInformation(InformationID)ONDELETECASCADE,FOREIGNKEY(OrganizationID)REFERENCESOrganization(OrganizationID)ONDELETECASCADE”。另一種方法是不改變表結構，臨時聲明一個表變量，用于保存待刪除信息的ID，代碼如下。375376查詢政務平臺數據庫任務33771.?能使用SELECT?WHERE語句進行條件查詢。2.?能對多表進行連接查詢。3.?能在指定的范圍內進行分組查詢。4.?能按標題、內容等字段進行模糊查詢。378政務平臺數據庫的查詢功能包括發布新信息、查詢已發布信息、按機構過濾信息記錄。對于信息的查詢，可能需要考慮按標題、內容等字段進行模糊查詢。設計3個查詢，找出內容帶有“培訓”字樣的信息；統計各個局發布信息的數量；找出教育局發布的所有信息及相關的發布日期。379一、查詢內容帶有“培訓”字樣的信息找出內容帶有“培訓”字樣的信息，需要使用信息表。380二、統計各個局發布信息的數量統計各個局發布信息的數量，需要使用信息公開表，使用GROUPBY進行分組統計查詢。381三、查詢教育局發布的所有信息及相關的發布日期找出教育局發布的所有信息及相關的發布日期，需要連接Information表和Information_Organization_Relation表。382使用索引和視圖優化政務平臺數據庫任務43831.?能使用CREATE?INDEX在指定的字段上創建索引，以加速相關的查詢。2.?能根據任務使用CREATE?VIEW語句創建視圖。384政務平臺數據庫經常需要按照機構進行查詢，為了提高查詢性能，需要創建一個索引，并設計一個視圖，展示每個用戶所屬機構的信息發布數量。在Information_Organization_Relation表的OrganizationID字段上創建索引，以提高與機構相關查詢的速度。創建一個視圖，通過連接