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

需求分析，數(shù)據(jù)庫分析與設(shè)計(jì)完成后，公司的數(shù)據(jù)庫工程師接到創(chuàng)建政務(wù)平臺(tái)數(shù)據(jù)庫(policyplatformdatabase)的任務(wù)，現(xiàn)要求創(chuàng)建一個(gè)名為zw的數(shù)據(jù)庫，包括Information、Organization、User三個(gè)表，

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