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

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