數(shù)據(jù)庫系統(tǒng)概論課程設計工廠數(shù)據(jù)庫管理系統(tǒng)

1、一、課程設計的目的掌握數(shù)據(jù)庫的基礎知識、基本理論、原理和實現(xiàn)技術。二、設計題目：工廠數(shù)據(jù)庫管理系統(tǒng)已知技術參數(shù)和設計要求：需求說明及要求題目：工廠數(shù)據(jù)庫管理系統(tǒng)某工廠需建立一個管理數(shù)據(jù)庫存儲以下信息：1、 工廠包括廠名和廠長名。2、 一個廠內有多個車間，每個車間有車間號、車間主任姓名、地址和電話。3、 一個車間有多個工人，每個工人有職工號、姓名、年齡、性別和工種。4、 一個車間生產多種產品，產品有產品號、產品名稱和價格。5、 一個車間生產多種零件，一個零件也可能為多個車間制造。零件有零件號、重量和價格。6、 一個產品由多種零件組成，一種零件也可裝配出多種產品。7、 產品與零件均存入倉庫中。8、

2、 廠內有多個倉庫，倉庫有倉庫號、倉庫主任姓名和電話。各階段具體要求：1、需求分析階段l 定義數(shù)據(jù)項的含義和取值l 定義目標系統(tǒng)的數(shù)據(jù)流2、概念結構設計階段l 畫出實體模型e-r圖3、邏輯結構設計階段l 將實體模型轉化為關系模型l 給出每個關系的主關鍵字和函數(shù)依賴集l 分析你所設計的關系數(shù)據(jù)庫模式是否屬于3nf4、物理設計階段l 確定所有字段的名稱、類型、寬度、小數(shù)位數(shù)及完整性約束 l 確定數(shù)據(jù)庫及表的名稱及其組成 l 確定索引文件和索引關鍵字5、數(shù)據(jù)庫安全及維護設計階段l 設計一個適合的數(shù)據(jù)庫安全策略（用戶身份認證、訪問權限、視圖）l 為了實現(xiàn)復雜的數(shù)據(jù)完整性約束，設計適當?shù)挠|發(fā)器l 設計一個

3、適合的數(shù)據(jù)庫備份策略6、實施階段l 要求所有操作必須在查詢分析器中用sql語句或系統(tǒng)存儲過程完成。三、設計內容1將概念模型轉化為關系模型從e-r圖中可以看到有6個實體，所以我建立了6個實體關系表，并且把實體鍵作為表的關鍵字，4個一對多的聯(lián)系表，在建立關系模型的過程中，我是把一這邊的實體鍵放到多這邊做為外鍵，4個多對多的聯(lián)系，所以我建立了4個聯(lián)系表，并把相互聯(lián)系的兩實體的實體鍵，分別作為聯(lián)系表的外鍵。在處理倉庫與零件多對多的聯(lián)系和倉庫與產品多對多的聯(lián)系過程中有以下兩種方法：（1） 建立兩個存儲表，倉庫零件存儲表和倉庫產品存儲表，并把倉庫號，零件號和倉庫號，產品號分別作為這兩個聯(lián)系表的外鍵，這樣就

4、保持了參照完整性的約束條件，而且建立兩個聯(lián)系表清晰明了，更容易使人明白。（2） 建立一個存儲表，倉庫物品存儲表，它包含倉庫號，物品號（零件號和產品號），庫存量3個屬性，建立一個存儲表更簡潔，但是在建表的過程中丟失的參照完整性。綜合考慮以上兩種方法，我覺得第（1）中更好。四、設計步驟4.1需求分析階段(1)定義數(shù)據(jù)項的含義和取值數(shù)據(jù)項名含義 類型 寬度 小數(shù)位數(shù)facname工廠名char 10facmname廠長名 char 8cno車間主任編號 char 8workno車間號char 4workaddress車間地址 char 12workphone車間電話 char 5workerno職工

5、號 char 5workername職工名 char 8woerkerage職工年齡 int 4workersex性別 char 2workertype工種 char 10productno產品號 char 5productname產品名稱 char 12productprice產品價格 numeric 5 1comno零件號 char 5comweight零件重量 int 4comprice零件價格 numeric 51wareno倉庫號 char 5wno倉庫主任編號 char 8warephone倉庫電話 char 12(2)定義數(shù)據(jù)結構數(shù)據(jù)結構含義組成工廠 工廠情況表facname+f

6、acmname車間車間情況表workname+cno+ workno+workaddress+ workphone+facname 職工 職工情況表workerno+workername+workersex+ workertype+workerage+workno 產品 產品情況表 productno+productname+ productprice+snum1 零件零件情況表 comno+comweight+comprice+snum2 倉庫倉庫情況表wareno+wno+warephone+facname(3)定義數(shù)據(jù)流數(shù)據(jù)流名 含義 組成 factory 工廠情況表 facname+f

7、acmname workshop 車間情況表workno+cname+workaddress+ workphone+facname workerqk職工情況表workerno+ workername+ workersex+ workertype productqk產品情況表productno+ productname+ productprice+workno compentsqk零件情況表comno + comweight+ compricewareqk倉庫情況表wareno+wname+warephone+facname4.2概念結構設計階段畫出實體模型e-r圖倉庫工廠產品車間零件工人倉庫主

8、任編號倉庫號電話工廠名廠長名車間主任編號地址電話工種車間號姓名年齡性別職工號產品號產品名稱價格數(shù)量零件號重量價格有存入存入組裝擁有有有有有生產庫存量庫存量生產數(shù)量生產日期4.3邏輯結構設計階段（1）將實體模型轉化為關系模型實體轉化成關系表factoryfacnamefacmnameworkshop worknocnoworkaddressworkphonefacnameworkerworkernoworkertypeworkersexworkernameworkerageproductproductnoproductnameproductpriceworknocompoentscomnocom

9、weightcompricewarehouse warenownowarephonefacname多對多的聯(lián)系轉化成實體表w-c（生產）worknocomnocomnum1p-c（組裝）productnocomnopronumcomnum2wac（倉庫-零件）存儲warenocomnosnum2wap（倉庫-產品）存儲warenoproductnosnum1(2)每個關系的主關鍵字和函數(shù)依賴集factory（facname，facmname）facname facmnameworkshop（workno，cno，workaddress，workphone，facname）worknoworka

10、ddress, worknocno,worknoworkphone, worknofacnameworker（workerno，workertype，workersex，workername，workerage，workno）workernoworkertype, workernoworkersex, workernoworkernameworkernoworkerage, workernoworknoproduct（productno，productname，productprice，workno）productno productname, productno productprice,

11、productno worknocomponents（comno，comweight，comprice）comnocomweight, comnocompricewarehouse（wareno，facname，warephone，wno）wareno facname, wareno warephone, wareno wnowc（comno，workno, comnum1）(comno，workno)comnum1pc（productno，comno，pronum，comnum2）（productno，comno）pronum, （productno，comno）comnum2wac（com

12、no，wareno，sum2）（comno，wareno）sum2wap（wareno，productno，sum1）（wareno，productno）sum1以上的10個關系模式沒有非主屬性對主關鍵字的部分依賴和傳遞依賴，也不存在屬于主屬性對主關鍵字的部分依賴和傳遞依賴，所以以上的10個關系模式都屬于3nf4.4物理設計階段（1）確定所有字段的名稱、類型、寬度、小數(shù)位數(shù)及完整性約束數(shù)據(jù)項名含義 類型 寬度 小數(shù)位數(shù) 完整性要求facname工廠名varchar 30primary keyfacmname廠長名 char 8not nullcno車間主任編號 varchar 8參照職工號wo

13、rkno車間號varchar 10primary keyworkaddress車間地址 varchar 20workphone車間電話 varchar 12workerno職工號 char 5primary keyworkername職工名 char 8woerkerage職工年齡 int 4workersex性別 char 2（男，女）workertype工種 char 10productno產品號 char 5primary keyproductname產品名稱 char 12productprice產品價格 numeric 51 =0comno零件號 char 5primary keyc

14、omweight零件重量 int 4 =0comprice零件價格 numeric 51=0number數(shù)量int 4 =0wareno倉庫號 varchar 10primary keywno倉庫主任編號 varchar 8參照職工號warephone倉庫電話 varchar 12snum1倉庫存儲產品數(shù)量 int 4 =0snum2倉庫存儲零件數(shù)量int 4 =0(2)確定數(shù)據(jù)庫及表的名稱及其組成 關系名table數(shù)據(jù)結構含義組成字段工廠factory工廠基本信息facname，fmname車間workshop車間基本信息workno,workaddress,workphone,cno,fa

15、cname職工worker工人基本信息workerno,workername,workersex,workertype, workage,workno產品product產品基本信息productno，productname， productprice，workno零件compents零件基本信息comno，comweight， comprice倉庫warehous倉庫基本信息wareno，wname，warephone，facname生產wc零件與車間的聯(lián)系workno ，comno , comnum1組裝pc零件與產品的聯(lián)系productno，comno，comnum2 倉庫-零件wac零件

16、與倉庫的聯(lián)系wareno，comno，snum2倉庫-產品wap產品與倉庫的聯(lián)系wareno，productno，snum1工廠表 列名數(shù)據(jù)類型能否空值默認值鍵/索引說明facnamevarchar(30)否主鍵、索引廠名fmnamevarchar(8)否廠長名車間表 列名數(shù)據(jù)類型能否空值默認值鍵/索引說明workno varchar（10）否主鍵、索引 車間號cno varchar（8）否車間主任編號workaddress varchar（20）否 車間地址workphonevarchar(12)否 電話fcanamevarchar(30)否工廠名零件表 列名數(shù)據(jù)類型能否空值默認值鍵/索引說

17、明comno varchar（10）否主鍵、索引零件號comweight int否重量compricenumeric(5,1) 否價格職工表 列名數(shù)據(jù)類型能否空值默認值鍵/索引說明workerno varchar（10）否主鍵、索引 職工號workername varchar（8）否 姓名workerage int否年齡workersex varchar（2）否性別workertype varchar（20）否 工種 worknovarchar(10)否 車間號產品表 列名數(shù)據(jù)類型能否空值默認值鍵/索引 說明productno varchar（10）否主鍵、索引產品號productname v

18、archar（20）否產品名稱productpricenumeric(5，1) 否價格worknovarchar(10)否車間號倉庫表 列名數(shù)據(jù)類型能否空值默認值鍵/索引說明wareno varchar（10）否主鍵、索引倉庫號wname varchar（8）否倉庫主任姓名warephonevarchar(12)否倉庫電話facnamevarchar(30)否工廠名生產表 列名數(shù)據(jù)類型能否空值默認值鍵/索引說明worknovarchar（10）否主鍵車間號 comnovarchar（10）否主鍵零件號 comnum1int否生產零件數(shù)組裝表 列名數(shù)據(jù)類型能否空值默認值鍵/索引說明 produc

19、tnovarchar（10）否主鍵產品號 comno varchar（10）否主鍵零件號 comnum2int否組裝零件數(shù) pronumint否組裝產品數(shù)產品存儲表 列名數(shù)據(jù)類型能否空值默認值鍵/索引說明 warenovarchar（10）否主鍵倉庫號 productnovarchar（10）否主鍵產品號 snum2int否產品存放量零件存儲表 列名數(shù)據(jù)類型能否空值默認值鍵/索引說明 warenovarchar（10）否主鍵倉庫號 comnovarchar（10）否主鍵零件號 snum1int否零件存放量創(chuàng)建數(shù)據(jù)庫和關系表1.創(chuàng)建factory數(shù)據(jù)庫2創(chuàng)建factory（工廠）表3創(chuàng)建work

20、shop（車間）表4創(chuàng)建worker（職工）表5創(chuàng)建product（產品）表6創(chuàng)建component（零件）表7創(chuàng)建warehouse（倉庫）表8創(chuàng)建wc（生產）表9創(chuàng)建pc（組裝）表10創(chuàng)建wap（存儲表1）倉庫-產品11創(chuàng)建wac（存儲表2）倉庫-零件(3)確定索引文件和索引關鍵字table索引文件索引關鍵字factoryfactory_idxfacnoworkshopworkshop_idxworknoworkerworker_idxworkernoproductproduct_idxproductnocompoentcompents_idxcomnowarehousewarehouse

21、_idxwarenowcwc_idxprotime+workno+comnopcpc_idxproductno+comnowacwac_idxwareno+comnowapwap_idxwareno+productno4.5數(shù)據(jù)庫安全及維護設計階段(1)設計一個適合的數(shù)據(jù)庫安全策略（用戶身份認證、訪問權限、視圖）添加新用戶hh (2)添加角色(3)授權1把factory表的所有權限授給hh2把workshop表的所有權限授給hh3把worker表的所有權限授給hh4把product表的所有權限授給hh5把component表的所有權限授給hh6把warehouse表的所有權限授給hh（4）創(chuàng)建

22、索引1為工廠表在工廠名facname上建立索引2為車間表在車間號workno上建立索引3為職工表在職工編號workerno上建立索引4為產品表在產品號productno上建立索引5為零件表在零件號comno上建立索引6為倉庫表在倉庫號wareno上建立索引（5）創(chuàng)建視圖（6）創(chuàng)建存儲過程1在工廠表上創(chuàng)建存儲過程2在車間表上創(chuàng)建存儲過程3在職工表上創(chuàng)建存儲過程4在產品表上創(chuàng)建存儲過程5在零件表上創(chuàng)建存儲過程6在倉庫表上創(chuàng)建存儲過程7在生產表上創(chuàng)建存儲過程8在組裝表上創(chuàng)建存儲過程9在零件存儲表上創(chuàng)建存儲過程10在產品存儲表上創(chuàng)建存儲過程執(zhí)行存儲過程1向工廠表中插入數(shù)據(jù)2向車間表中插入數(shù)據(jù)3向職工表

23、中插入數(shù)據(jù)4向產品表中插入數(shù)據(jù)5向零件表中插入數(shù)據(jù)6向倉庫表中插入數(shù)據(jù)7向生產表中插入數(shù)據(jù)8向組裝表中插入數(shù)據(jù)9向零件存儲表中插入數(shù)據(jù) 10向產品存儲表中插入數(shù)據(jù) 4.6.2查詢存儲過程1功能是通過某一職工的姓名查詢他的全部信息2功能是通過某一產品的名稱查詢該產品的基本信息3功能是通過某一零件的名稱查詢該零件的基本信息4功能是通過某一產品的名稱查詢該產品所需要的零件種類與數(shù)量5通過某一車間號查詢改車間的所有信息6通過輸入某一日期查詢該日生產零件的種類和零件的數(shù)目以及生產該零件的車間號4.6.3創(chuàng)建觸發(fā)器1在生產表上建立觸發(fā)器，當上生產表中插入一行數(shù)據(jù)時，零件存儲表中零件的庫存量就會增加，零件的

24、庫存量等于先前的零件庫存量加上生產表中生產零件的數(shù)量觸發(fā)前觸發(fā)后2在產品存儲表上建立觸發(fā)器，當向產品存儲表中插入數(shù)據(jù)，即產品的庫存量增加時，零件的庫存量就會減少，某種零件的庫存量等于先前的庫存量減去該種產品所需要的該零件數(shù)乘以生產產品的數(shù)量五設計一個適合的數(shù)據(jù)庫備份策略創(chuàng)建一個磁盤備份的設備use factorygoexec sp_addumpdevice disk,factory_full,f:haofactory.bakgo完全備份use factory gobackup database factory to factory_full with initgo1完全備份工廠數(shù)據(jù)庫增量備份use factory gobackup database factory to disk=f:hufactory_diff.bskwith noinit,differentialgo還原整個university數(shù)據(jù)庫restore database factory from factory_full還原差異備份restore database factory from factory_differ with norecovery還原日志備份restore database log from factory_log with recovery總結這次課程設計加深了