實(shí)驗(yàn)1-數(shù)據(jù)庫設(shè)計(jì)與實(shí)現(xiàn)

《管理信息系統(tǒng)》實(shí)驗(yàn)指導(dǎo)15/15實(shí)驗(yàn)1數(shù)據(jù)庫設(shè)計(jì)與實(shí)現(xiàn)目的和要求根據(jù)實(shí)際場景，設(shè)計(jì)E-R模型；將E-R模型轉(zhuǎn)換為數(shù)據(jù)庫表；在SqlServer2000上實(shí)現(xiàn)數(shù)據(jù)庫理解SQL語言背景知識E-R模型設(shè)計(jì)1．E-R模型簡介E-R模型（EntityRelationshipModel，實(shí)體聯(lián)系模型）是P.P.Chen于1976年提出的。這個模型直接從現(xiàn)實(shí)世界中抽象出實(shí)體類型及實(shí)體間聯(lián)系，然后用實(shí)體聯(lián)系圖（E-R圖）表示數(shù)據(jù)模型。E-R圖中有下面四個基本成分：（1）矩形框，表示實(shí)體類型；（2）菱形框，表示聯(lián)系類型；（3）橢圓形框，表示實(shí)體類型或聯(lián)系類型的屬性；（4）直線，用來連接實(shí)體與屬性、聯(lián)系與屬性，聯(lián)系與其涉及的實(shí)體，并在直線端部標(biāo)聯(lián)系的類型（1:1，1:N或M:N）。2．E-R圖的建立過程（1）首先確定實(shí)體類型。（2）確定聯(lián)系類型。（3）將實(shí)體類型和聯(lián)系類型組合成ER圖。（4）確定實(shí)體類型和聯(lián)系類型的屬性，并為實(shí)體類型確定能唯一確定實(shí)體的實(shí)體標(biāo)識符（標(biāo)記為在該屬性下面劃線）。示例1：根據(jù)下面語義設(shè)計(jì)ER模型。某單位有多個職工，每個職工只能參加一個工程項(xiàng)目，但一個工程項(xiàng)目中可以有很多職工參與；有多個供應(yīng)商為各個工程項(xiàng)目供應(yīng)不同零件。分析：本問題有四個實(shí)體類型：職工，工程項(xiàng)目，供應(yīng)商，零件。實(shí)體之間的聯(lián)系類型：工程項(xiàng)目與職工是1:N聯(lián)系，工程項(xiàng)目與零件是M:N聯(lián)系，供應(yīng)商和設(shè)備是M:N聯(lián)系。組合成ER圖。如圖1-1所示。確定屬性。職工的屬性有：職工編號，職工姓名，性別，出生年月，職工編號為標(biāo)識符。零件的屬性有：零件編號，零件名稱，顏色，重量，零件編號為標(biāo)識符。工程項(xiàng)目的屬性有：項(xiàng)目編號，項(xiàng)目名稱，項(xiàng)目開工日期，項(xiàng)目編號為標(biāo)識符。供應(yīng)商的屬性有：供應(yīng)商編號，供應(yīng)商名稱，地址，供應(yīng)商編號為標(biāo)識符。參與聯(lián)系的屬性是某職工在某工程項(xiàng)目中的擔(dān)任職務(wù)。使用聯(lián)系的屬性是某項(xiàng)目需要某零件的數(shù)量。供應(yīng)聯(lián)系的屬性是某供應(yīng)商供應(yīng)某程零件的數(shù)量。注：如果ER圖比較復(fù)雜，也可以將屬性單獨(dú)以文字的方式加以說明。MMN使用零件供應(yīng)商供應(yīng)MN職工工程項(xiàng)目參與1N擔(dān)任職務(wù)數(shù)量數(shù)量圖1-1ER圖實(shí)例ER模型轉(zhuǎn)換為數(shù)據(jù)庫表ER模型中的主要成分是實(shí)體類型和聯(lián)系類型，轉(zhuǎn)換規(guī)則就是如何把實(shí)體類型、聯(lián)系類型轉(zhuǎn)換成關(guān)系模式（即表）。1．實(shí)體類型的轉(zhuǎn)換：將每個實(shí)體類型轉(zhuǎn)換成一個表（關(guān)系模式），實(shí)體的屬性即為表的屬性，實(shí)體標(biāo)識符即為表的鍵。2．聯(lián)系類型的轉(zhuǎn)換：根據(jù)聯(lián)系的類型所不同的處理。（1）1:1的聯(lián)系：在聯(lián)系的兩個實(shí)體類型轉(zhuǎn)換成的表中的任意一個表的屬性中加入另一個表的鍵和聯(lián)系類型的屬性。（2）1:N的聯(lián)系：在N端實(shí)體類型轉(zhuǎn)換成的表中加入1端實(shí)體類型轉(zhuǎn)換成的表的鍵和聯(lián)系類型的屬性。（3）M:N的聯(lián)系：將聯(lián)系類型也轉(zhuǎn)換成表，其屬性為兩端實(shí)體類型的鍵加上聯(lián)系類型的屬性，而聯(lián)系類型生成的表的鍵為兩端實(shí)體鍵的組合。示例1的ER模型轉(zhuǎn)換成數(shù)據(jù)庫表時，四個實(shí)體“職工”、“零件”、“工程項(xiàng)目”和“供應(yīng)商“分別轉(zhuǎn)換為四個表，工程項(xiàng)目與職工的1:N聯(lián)系通過將N端的工程項(xiàng)目的鍵“項(xiàng)目編號”加入1端的職工表實(shí)現(xiàn)，“使用”與“供應(yīng)”兩個聯(lián)系為M:N的聯(lián)系，則分別轉(zhuǎn)換成表。轉(zhuǎn)換后的表為：職工表（職工編號，職工姓名，性別，出生年月，項(xiàng)目編號）零件表（零件編號，零件名稱，顏色，重量）工程項(xiàng)目表（項(xiàng)目編號，項(xiàng)目名稱，項(xiàng)目開工日期）供應(yīng)商表（供應(yīng)商編號，供應(yīng)商名稱，地址）使用表（項(xiàng)目編號，零件編號，數(shù)量）供應(yīng)表（零件編號，供應(yīng)商編號，數(shù)量）SQL語言結(jié)構(gòu)化查詢語言（SQL）是在多數(shù)數(shù)據(jù)庫環(huán)境下使用的標(biāo)準(zhǔn)的第四代查詢語言。SQL組成（1）SQLDDL：數(shù)據(jù)定義。用于定義SQL模式、基本表、視圖和索引。（2）SQLDML：數(shù)據(jù)操縱。分為數(shù)據(jù)查詢和數(shù)據(jù)更新，數(shù)據(jù)更新又分為插入、刪除和修改三種操作。（3）SQLDCL：數(shù)據(jù)控制。包括對基本表和視圖的授權(quán)，完整性規(guī)則的描述，事務(wù)控制語句等。（4）EmbeddedSQL：嵌入式SQL，涉及到SQL語句嵌入在宿主語言程序中的使用規(guī)則。日常使用較多的語句是數(shù)據(jù)操縱，下面介紹的也是數(shù)據(jù)查詢和數(shù)據(jù)更新語句。數(shù)據(jù)查詢SQL的數(shù)據(jù)查詢是使用最廣泛的語句，而且查詢語句只有一條SELECT語句，功能大常強(qiáng)大。其基本語法格式為：Select字段名表From表名WHERE<條件表達(dá)式>在SELECT之后要列出待查詢的字段名稱，F(xiàn)ROM之后要指明使用哪些邏輯關(guān)系，WHERE后面描述選擇的條件。示例：1．查詢參與工程編號為“P03”Select職工姓名，性別From職工表Where工程編號=’p03查詢供應(yīng)了紅色零件的供應(yīng)商的姓名和地址。Select供應(yīng)商姓名，地址From供應(yīng)商表，零件表，供應(yīng)表Where供應(yīng)表.供應(yīng)商編號=供應(yīng)表.供應(yīng)商編號and零件表.零件編號=供應(yīng)表.零件編號數(shù)據(jù)插入數(shù)據(jù)插入的語法結(jié)構(gòu)為：insertinto表名（列名表）values（元組值）示例：向職工表中插入一條記錄（’a09’,’張?zhí)煲弧?’男’,’1978-01-02’,’P02insertinto職工表（職工編號，職工姓名，性別，出生年月，項(xiàng)目編號）values（’a09’,’張?zhí)煲弧?’男’,’1978-01-02’,’數(shù)據(jù)刪除數(shù)據(jù)刪除的語法結(jié)構(gòu)為：deletefrom表名where<條件表達(dá)式>示例：將所有參加項(xiàng)目編號為’p05’deletefrom職工表where項(xiàng)目編號=’p05數(shù)據(jù)更新數(shù)據(jù)更新的語法結(jié)構(gòu)為：update表名set列名=值表達(dá)式[，列名=值表達(dá)式…][where條件表達(dá)式]示例：所有女性職工都參加項(xiàng)目編號為p10項(xiàng)目，也就是將所有女職工的項(xiàng)目編號改為p10，更新語名為：Update職工表set項(xiàng)目編號=’p10Where性別=’女’內(nèi)容和步驟創(chuàng)建數(shù)據(jù)庫數(shù)據(jù)庫實(shí)際上就是在硬盤中為諸如表、視圖及存儲結(jié)構(gòu)等預(yù)先分配空間的系列文件。在SQLServer2000數(shù)據(jù)庫中存在主要數(shù)據(jù)文件、次要數(shù)據(jù)文件和事務(wù)日志3種數(shù)據(jù)文件。在SQLServer2000下創(chuàng)建數(shù)據(jù)庫有兩種方法：使用企業(yè)管理器直接創(chuàng)建數(shù)據(jù)庫；用Transact-SQL語句創(chuàng)建數(shù)據(jù)庫。下面介紹使用企業(yè)管理器直接創(chuàng)建數(shù)據(jù)庫。打開企業(yè)管理器，在左窗格中雙擊要建立數(shù)據(jù)庫的服務(wù)器節(jié)點(diǎn)。展開該節(jié)點(diǎn)，右鍵點(diǎn)擊“數(shù)據(jù)庫”節(jié)點(diǎn)，從彈出的快捷菜單中選擇“新建數(shù)據(jù)庫”命令。如圖1-2所示。圖1-2新建數(shù)據(jù)庫彈出對話框，在“名稱”文本框中輸入正確的數(shù)據(jù)庫名稱，如圖1-3所示。圖1-3新建數(shù)據(jù)庫對話框點(diǎn)擊“數(shù)據(jù)文件”選項(xiàng)卡，可以輸入數(shù)據(jù)文件的名稱、位置、大小，也可以使用SQLServer2000的默認(rèn)值（例如，數(shù)據(jù)文件名就是在用戶輸入的文件后面增加上_Data）。點(diǎn)擊“事務(wù)日志”選項(xiàng)卡，可以輸入日志文件的名稱、位置、大小，也可以使用SQLServer2000的默認(rèn)值（例如，數(shù)據(jù)文件名就是在用戶輸入的文件后面增加上_Log）。創(chuàng)建表在關(guān)系數(shù)據(jù)庫中，每個關(guān)系都體現(xiàn)為一張表，表是數(shù)據(jù)庫的一個對象之一，是用來存儲數(shù)據(jù)和操作數(shù)據(jù)的邏輯結(jié)構(gòu)，使用數(shù)據(jù)庫時，絕大多時間都是在與表打交道。表中有兩個基本的對象：字段和記錄。字段也叫屬性，也就是表中的列。記錄是字段的有序集合。一般用記錄描述一個實(shí)體，因此有的時候記錄也可以定義為能完整地描述一個實(shí)體的字段集，在二維表格中就是行。在SQLServer2000下創(chuàng)建表有兩種方法：使用企業(yè)管理器創(chuàng)建表；用Transact-SQL語句創(chuàng)建數(shù)據(jù)庫。下面介紹使用企業(yè)管理器直接創(chuàng)建表。打開企業(yè)管理器，展開左窗格中樹形結(jié)構(gòu)上的數(shù)據(jù)庫節(jié)點(diǎn)。選中創(chuàng)建好的數(shù)據(jù)為，右鍵點(diǎn)擊數(shù)據(jù)庫中的表對象，如圖1-4所示。圖1-4新建表彈出窗口。在這個窗口中，需要對表的內(nèi)容進(jìn)行定義，內(nèi)容有：輸入列名，列名必須唯一，列的命名規(guī)則必須以字母和下劃線開頭，后面跟字母、數(shù)字和下劃線。選擇正確的數(shù)據(jù)類型、數(shù)據(jù)長度和精度。規(guī)定該列的值是否可以為空。設(shè)置表格的主鍵和索引。注：創(chuàng)建主鍵工作比較簡單，首先選中要建立主鍵的列，然后右擊鼠標(biāo)，從彈出的快捷菜單中選擇“設(shè)置主鍵”命令即可。建立為主鍵的列的旁邊會出現(xiàn)一個小鑰匙形的標(biāo)志。也可以從快捷菜單中選擇“屬性”命令，彈出“屬性”對話框，然后打開“索引/鍵”選項(xiàng)卡。輸入了“客戶表”各列以及設(shè)置了“客戶編號”為主鍵的界面如圖1-5所示。圖1-5新建表示例修改表從樹形結(jié)構(gòu)上展開要修改定義的表所在的數(shù)據(jù)庫。選擇該數(shù)據(jù)庫節(jié)點(diǎn)下的表節(jié)點(diǎn)，則企業(yè)管理器顯示該數(shù)據(jù)庫下全部的表格。右鍵點(diǎn)擊要修改的表名，從彈出的快捷菜單中選擇“設(shè)計(jì)表”命令，彈出類似1-4的窗口。數(shù)據(jù)更新數(shù)據(jù)更新包括數(shù)據(jù)插入、刪除與更新?？梢岳闷髽I(yè)管理器的窗口與在查詢分析器中執(zhí)行SQL語句來完成。三種語句的執(zhí)行步驟類似，不同的只是SQL語句的構(gòu)建。下面以數(shù)據(jù)插入為例來介紹在查詢分析器中的操作。創(chuàng)建表格的目的是為了利用表格來存儲和管理業(yè)務(wù)數(shù)據(jù)。實(shí)現(xiàn)數(shù)據(jù)存儲的前提是向表格中添加數(shù)據(jù)。下面介紹使用Transact-SQL插入數(shù)據(jù)。1．打開“查詢分析器”，如圖1-6所示。圖1-6打開查詢分析器2．彈出窗品，在查詢窗口中輸入插入語句，單擊F5鍵或點(diǎn)擊“工具”—>“執(zhí)行”。如圖1-7所示。圖1-7利用查詢分析器執(zhí)行插入語句練習(xí)題為保險公司案例設(shè)計(jì)E-R模型，進(jìn)行設(shè)計(jì)數(shù)據(jù)庫并實(shí)現(xiàn)。案例數(shù)據(jù)庫技術(shù)在沃凱(Vulcan)保險公司業(yè)務(wù)中的應(yīng)用沃凱保險公司的銷售主要采用三種方式，即保險公司業(yè)務(wù)網(wǎng)點(diǎn)銷售、保險公司業(yè)務(wù)人員銷售、通過代理機(jī)構(gòu)或代理人進(jìn)行銷售。保險公司業(yè)務(wù)網(wǎng)點(diǎn)銷售即由顧客自己上門到保險公司業(yè)務(wù)網(wǎng)點(diǎn)進(jìn)行投保，這是一直以來保險銷售采取的一種“坐商”方式。保險公司業(yè)務(wù)人員銷售即保險公司通過自己的業(yè)務(wù)人員聯(lián)系客戶，進(jìn)行保險產(chǎn)品銷售，這也是保險的一種主要銷售方式。通過代理機(jī)構(gòu)或代理人進(jìn)行銷售即保險公司通過大量的代理機(jī)構(gòu)和代理人將保險產(chǎn)品推銷出去，保險公司再給代理商一筆不菲的傭金，這在競爭日趨激烈的保險行業(yè)是一種較為普遍的銷售方式。不管哪種銷售方式，其流程都是相似的。而且存儲在數(shù)據(jù)庫的數(shù)據(jù)也是大致相同的。參考答案：1．保險公司案例的E-R模型設(shè)計(jì)那么我們來分析一下，有哪些信息需要存儲到數(shù)據(jù)庫中呢？（1）客戶的相關(guān)信息，包括客戶的姓名，性別，聯(lián)系方式等；（2）保險產(chǎn)品信息，包括產(chǎn)品名稱，保險金額，保險年限等；（3）公司業(yè)務(wù)人員信息，包括業(yè)務(wù)人員的基本信息，還有所在部門等；（4）客戶的保單信息，包括客戶投保的日期，辦理該保單的業(yè)務(wù)人員，投保了哪些保險產(chǎn)品等?？蛻艨蛻舯ｋU產(chǎn)品業(yè)務(wù)人員投保1234圖1-8保險公司數(shù)據(jù)庫的ER圖注：各實(shí)體及聯(lián)系的屬性在下面詳細(xì)說明。（1）客戶實(shí)體屬性：客戶編號，姓名，性別，出生年月，聯(lián)系電話，地址（2）保險產(chǎn)品實(shí)體屬性：險種編號，險種名稱，投保金額，保險年限，繳納金額（3）業(yè)務(wù)人員實(shí)體屬性：營銷員編號，營銷員姓名，營管處編號，職級，出生年月，聯(lián)系電話，地址（4）投保聯(lián)系屬性：保單編號，保單狀態(tài)，到期日，核保通過日，生效日，被保人編號，投保人編號，簽單的營銷員編號，險種編號2．保險公司案例的數(shù)據(jù)庫設(shè)計(jì)客戶表（客戶編號，姓名，性別，出生年月，聯(lián)系電話1，聯(lián)系電話2，地址，類型）注：類型值為I時代表INSURED，即被保人，類型值為P時代表POLICYOWNER，即投保人險種表（險種編號，險種名稱，投保金額，保險年限，繳納金額）注：險種編號如果表示主合同和附加合同,則為01時代表主合同,02,03都為附加合同等營銷員個人信息表（營銷員編號，營銷員姓名，營管處編號，職級，出生年月，聯(lián)系電話1，聯(lián)系電話2，地址，狀態(tài)）注：（1）狀態(tài)為A時為有效的營銷員，否則為離職的營銷員。（2）職級大致可分為普通營銷員業(yè)務(wù)主管業(yè)務(wù)經(jīng)理區(qū)處經(jīng)理營管處總監(jiān)（3）營管處編號先分為業(yè)務(wù)發(fā)展一區(qū)部和業(yè)務(wù)發(fā)展二區(qū)部,業(yè)務(wù)發(fā)展一區(qū)部又可分為虹橋區(qū)部,普陀區(qū)部,長寧區(qū)部等,然后再分為長寧一處,進(jìn)德四區(qū)處等,名字可根據(jù)路名等來命名保單主表（保單編號，保單狀態(tài)，到期日，核保通過日，生效日，被保人編號，投保人編號，簽單的營銷員編號）注：（1）保單編號A100013417根據(jù)A開頭可知道為意外險產(chǎn)品H200023418H開頭為健康險產(chǎn)品C300028438C開頭為壽險產(chǎn)品（2）保單狀態(tài)的編碼含義

10通過核保但保費(fèi)未相配

12

生效后退保

13

拒保

14支票退票

15

延期

16

未生效退保

17

通過核保待體檢

18已通過核保,尚