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

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

10通過核保但保費未相配

12

生效后退保

13

拒保

14支票退票

15

延期

16

未生效退保

17

通過核保待體檢

18已通過核保,尚