數(shù)據(jù)庫大作業(yè)報告

1、學(xué)校代碼： 學(xué) 號： Hefei University實(shí)驗(yàn)設(shè)計報告 BACHELOR DISSERTATION項目名稱： UPS物流配送系統(tǒng)系 別： 電子信息與電氣工程系專業(yè)班級： 14電子二班項目類型： 數(shù)據(jù)庫報告指導(dǎo)教師： 方小紅學(xué)生姓名： 張穎（1405012028）完成時間： 2015.12.10目錄一.數(shù)據(jù)庫說明2二.目的和作用3三.數(shù)據(jù)庫設(shè)計31.系統(tǒng)主要業(yè)務(wù)分析32.數(shù)據(jù)庫結(jié)構(gòu)設(shè)計33.數(shù)據(jù)庫邏輯設(shè)計43.1數(shù)據(jù)庫表名匯總43.2數(shù)據(jù)庫實(shí)體函數(shù)依賴與規(guī)范43.3表結(jié)構(gòu)設(shè)計53.4數(shù)據(jù)庫表設(shè)計：將ER圖轉(zhuǎn)化為數(shù)據(jù)表11四.實(shí)驗(yàn)小結(jié)24一.數(shù)據(jù)庫說明UPS物流配送系統(tǒng)采用的是課程實(shí)驗(yàn)

2、中使用的SQL Sever，使用的版本是2012版。設(shè)計的數(shù)據(jù)庫的全局?jǐn)?shù)據(jù)庫名為UPS數(shù)據(jù)庫。二.目的和作用將業(yè)務(wù)需求分析，系統(tǒng)設(shè)計中對信息的描述進(jìn)一步分析并加以總計，抽象出數(shù)據(jù)集合（數(shù)據(jù)庫表）。對數(shù)據(jù)集合做進(jìn)一步分析，確定集合之間的關(guān)系并最終形成數(shù)據(jù)庫物理模型，以便開發(fā)人員建立物理數(shù)據(jù)庫。三.數(shù)據(jù)庫設(shè)計1.系統(tǒng)主要業(yè)務(wù)分析UPS物流配送公司的主要業(yè)務(wù)是為用戶配送貨物；物流公司的組織結(jié)構(gòu)分為轉(zhuǎn)運(yùn)中心和配送單位；轉(zhuǎn)運(yùn)中心主要負(fù)責(zé)車輛的維護(hù)、配送點(diǎn)、配送線路、配送價格的維護(hù)、權(quán)限的管理；配送點(diǎn)主要是接收客戶訂單，并聯(lián)系轉(zhuǎn)運(yùn)中心將貨物運(yùn)送到目的地；配送點(diǎn)同時還負(fù)責(zé)貨物的配送工作。按照業(yè)務(wù)及系統(tǒng)功能簡

3、單總結(jié)數(shù)據(jù)對象：l 訂單l 路線信息l 運(yùn)輸工具l 發(fā)件人l 收件人l 員工l 轉(zhuǎn)運(yùn)中心2.數(shù)據(jù)庫結(jié)構(gòu)設(shè)計根據(jù)系統(tǒng)的分布式部署設(shè)計，數(shù)據(jù)庫將部署到一部獨(dú)立的計算機(jī)中。根據(jù)前期的分析，由于此次作業(yè)中不涉及大量數(shù)據(jù)的存儲。預(yù)留數(shù)據(jù)庫空間3M，日增長約1M，日志空間2G，日增長10%。3.數(shù)據(jù)庫邏輯設(shè)計3.1數(shù)據(jù)庫表名匯總表名描述發(fā)件人用于存儲發(fā)件人信息發(fā)件人_訂單(關(guān)系表)發(fā)件人與訂單之間的關(guān)系表收件人用于存儲收件人信息收件人_訂單(關(guān)系表)收件人與訂單之間的關(guān)系表訂單用于存儲訂單的詳細(xì)信息客戶表用于存儲客戶的基本信息情況員工登陸用于存儲員工登錄信息員工用于存儲員工的基本情況貨物用于存儲貨物的基本

4、情況智慧標(biāo)簽用于存儲貨物的基本情況運(yùn)輸器用于存儲運(yùn)送貨物的運(yùn)輸器信息路線信息_運(yùn)輸器(關(guān)系表)路線信息和運(yùn)輸器生成的關(guān)系表路線信息_中轉(zhuǎn)站(關(guān)系表)路線信息和中轉(zhuǎn)站生成的關(guān)系表路線信息用于存儲路線信息中轉(zhuǎn)站用于存儲中轉(zhuǎn)站的基本信息智慧標(biāo)簽_中轉(zhuǎn)站(關(guān)系表)智慧標(biāo)簽和中轉(zhuǎn)站生成的關(guān)系表中轉(zhuǎn)站_員工(關(guān)系表)中轉(zhuǎn)站和員工生成的關(guān)系表智慧標(biāo)簽_員工(關(guān)系表)智慧標(biāo)簽和員工生成的關(guān)系表訂單狀態(tài)的信息用于存儲訂單狀態(tài)的基本信息異常訂單用于存儲異常訂單的信息總體關(guān)系表3.2數(shù)據(jù)庫實(shí)體函數(shù)依賴與規(guī)范實(shí)體名函數(shù)依賴滿足范式訂單1. 訂單號（主鍵）運(yùn)單類型.始發(fā)地.目的地.費(fèi)用.訂單生成時間3NF發(fā)件人1. 發(fā)

5、件人姓名，發(fā)件人電話（主鍵）發(fā)件人地址.發(fā)件人郵編2. 發(fā)件人地址發(fā)件人郵編2NF收件人1. 收件人姓名，收件人電話（主鍵）收件人地址.收件人郵編2. 收件人地址收件人郵編2NF貨物1. 訂單號，標(biāo)簽ID(主鍵)類型.重量3NF員工1. 員工編號（主鍵）員工姓名.員工性別.員工年齡.員工聯(lián)系電話.員工家庭住址.員工部門.員工職位2. 員工聯(lián)系電話員工姓名.員工性別.員工年齡.員工家庭住址.員工部門.員工職位3. 員工聯(lián)系電話員工部門員工職位3NF運(yùn)輸器1. 運(yùn)輸器編號（主鍵）運(yùn)輸器類型.出發(fā)地.目的地.裝載時間.到達(dá)時間3NF路線信息1. 標(biāo)簽ID（主鍵）出發(fā)地.目的地.中轉(zhuǎn)站1ID.中轉(zhuǎn)站2

6、ID.中轉(zhuǎn)站3ID.中轉(zhuǎn)站4ID3NF智慧標(biāo)簽1. 標(biāo)簽ID,訂單號（主鍵）二維碼.尺寸.重量2. 二維碼尺寸.重量2NF員工登陸1. 員工登錄名，員工編號員工登陸密碼3NF訂單狀態(tài)信息1. 訂單號（主鍵）所在地.掃面時間.上一站.下一站.是否到達(dá).是否正常3NF異常訂單1. 訂單號，標(biāo)簽ID(主鍵) 異常信息.異常時間.異常地點(diǎn)3NF中轉(zhuǎn)站1. 中轉(zhuǎn)站ID(主鍵) 中轉(zhuǎn)站名.中轉(zhuǎn)站地址.到達(dá)時間.出發(fā)時間2. 中轉(zhuǎn)站地址中轉(zhuǎn)站名2NF客戶表3. 客戶電話客戶姓名.客戶地址.客戶郵編3NF3.3表結(jié)構(gòu)設(shè)計l 訂單l 客戶l 智慧標(biāo)簽l 發(fā)件人l 收件人l 員工l 運(yùn)輸器l 貨物l 員工登陸l

7、訂單狀態(tài)信息l 異常訂單l 路線信息l 中轉(zhuǎn)站l 收件人-訂單l 發(fā)件人-訂單l 運(yùn)輸器-員工l 智慧標(biāo)簽-員工l 智慧標(biāo)簽-中轉(zhuǎn)站l 中轉(zhuǎn)站-員工l 路線信息-中轉(zhuǎn)站l 路線信息-運(yùn)輸器3.4數(shù)據(jù)庫表設(shè)計：將ER圖轉(zhuǎn)化為數(shù)據(jù)表3.5實(shí)驗(yàn)要求1.員工登陸 proc(username ,password分開判斷并給出提示信息)createprocyg_denglu(yonghumingchar(20),koulingchar(20)asifexists(select*from員工登陸where員工登陸名=yonghumingand員工登陸密碼=kouling)print'登陸成功'

8、;elseifnotexists(select*from員工登陸where員工登陸名=yonghuming)print'員工登陸名不存在'elseifexists(select*from員工登陸where員工登陸名=yonghumingand員工登陸密碼!=kouling)print'員工登陸密碼錯誤'execyg_dengluyonghuming=luchenyang,kouling=123456execyg_dengluyonghuming='wanger002',kouling='002'execyg_dengluyongh

9、uming='zhaoyi001',kouling='123456'2.統(tǒng)計員工工作量 proc/view(全體員工和單個員工)統(tǒng)計全體員工工作量：createviewall_ygworkloadasselectcount(distinct訂單號)as全體員工工作量from智慧標(biāo)簽_員工select*fromall_ygworkload查看單個員工工作量createviewevery_ygworkloadasselect員工編號,count(標(biāo)簽ID)as工作量from智慧標(biāo)簽_員工groupby員工編號select*fromevery_ygworkload3.

10、統(tǒng)計中轉(zhuǎn)站的運(yùn)送情況（正常包裹 AND 異常報告的數(shù)量等） 對于異常情況查看詳情（交通，損壞，丟失）訂單狀態(tài)信息表異常訂單表創(chuàng)建視圖查詢正常包裹數(shù)量createview正常包裹數(shù)量asselectcount(訂單號)as正常包裹數(shù)量from訂單狀態(tài)信息where是否正常='是'創(chuàng)建視圖查詢異常報告數(shù)量createview異常包裹數(shù)量asselectcount(訂單號)as異常包裹數(shù)量from訂單狀態(tài)信息where是否正常='否'創(chuàng)建視圖查看異常訂單詳情createview異常訂單詳情asselect*from異常訂單4.用戶查看運(yùn)送狀態(tài)（列表顯示時間、地點(diǎn)，員工

11、、中轉(zhuǎn)站）訂單狀態(tài)信息表創(chuàng)建視圖查看訂單運(yùn)送狀態(tài)，時間，地點(diǎn)，員工，中轉(zhuǎn)站createview查看訂單狀態(tài)信息asselect*from訂單狀態(tài)信息5.trigger運(yùn)單一旦開始派送，則不允許修改運(yùn)單號、發(fā)件人、收件人。由于數(shù)據(jù)庫設(shè)計的原因，我們需要多個觸發(fā)器完成這項工作對發(fā)件人表：createtriggerlimit_發(fā)件人on發(fā)件人forupdateasifexists(select*fromdeleted,發(fā)件人where發(fā)件人.發(fā)件人姓名=deleted.發(fā)件人姓名and發(fā)件人.發(fā)件人電話=deleted.發(fā)件人電話)beginrollbackprint'訂單已生成,不能修改表

12、中任何信息'end對收件人表：createtriggerlimit_收件人on收件人forupdateasifexists(select*fromdeleted,收件人where收件人.收件人姓名=deleted.收件人姓名and收件人.收件人電話=deleted.收件人電話)beginrollbackprint'訂單已生成,不能修改表中任何信息'end對訂單號：createtriggerlimit_訂單on訂單forupdateasifexists(select*fromdeleted,訂單where訂單.訂單號=deleted.訂單號)beginrollbackpr

13、int'訂單已生成,不能修改表中任何信息'end嘗試修改發(fā)件人表中信息，提示“訂單已生成，不能修改表中任何信息”嘗試修改收件人表中信息，提示“訂單已生成，不能修改表中任何信息”嘗試修改訂單表中信息，提示“訂單已生成，不能修改表中信息”6.一旦新建運(yùn)單，就判斷發(fā)件人是否在用戶之中，若是則轉(zhuǎn)用戶表，顯示用戶信息，如不是，自動增加用戶。 triggercreatetriggeradd_useron發(fā)件人afterinsertasdeclare客戶姓名varchar(30)declare客戶電話varchar(20)declare客戶地址varchar(40)declare客戶郵編va

14、rchar(20)ifnotexists(select*frominserted,客戶表whereinserted.發(fā)件人姓名=客戶表.客戶姓名andinserted.發(fā)件人電話=客戶表.客戶電話)beginselect客戶姓名=inserted.發(fā)件人姓名,客戶電話=inserted.發(fā)件人電話,客戶地址=inserted.發(fā)件人地址,客戶郵編=inserted.發(fā)件人郵編frominsertedinsertinto客戶表(客戶姓名,客戶電話,客戶地址,客戶郵編)values(客戶姓名,客戶電話,客戶地址,客戶郵編)endelseifexists(select*frominserted,客戶

15、表whereinserted.發(fā)件人姓名=客戶表.客戶姓名andinserted.發(fā)件人電話=客戶表.客戶電話)beginprint'客戶信息：'select客戶姓名,客戶電話,客戶地址,客戶郵編from客戶表,insertedwhereinserted.發(fā)件人姓名=客戶表.客戶姓名andinserted.發(fā)件人電話=客戶表.客戶電話end發(fā)件人表_訂單客戶表現(xiàn)在，我往發(fā)件人訂單表中插入數(shù)據(jù)insertinto發(fā)件人values('王五','#39;,'湖南省常德市','642801')insert

16、into發(fā)件人_訂單values('7492004','王五','#39;)插入后的客戶表自動添加了“王五”的數(shù)據(jù)現(xiàn)在我要插一條數(shù)據(jù)客戶表中有，發(fā)件人表和發(fā)件人_訂單表中沒有?？蛻舯碇邢炔迦脍w六的數(shù)據(jù)insertinto發(fā)件人values('趙六','#39;,'云南昆明','650500')insertinto發(fā)件人_訂單values('7492005','趙六','#39;)可以看到，顯示出了客戶表中趙六的信息。四.實(shí)驗(yàn)小結(jié)即使是上了一學(xué)期的數(shù)據(jù)庫課程，但這些都是針對數(shù)據(jù)庫的一些基本操作，沒有深入了解到數(shù)據(jù)庫的內(nèi)部世界。SQL Sever的操作和編程在本次項目中都讓我得到了很好的鍛煉。在當(dāng)初定實(shí)體時，對每一個可能要實(shí)現(xiàn)功能的內(nèi)容要進(jìn)行詳細(xì)的考慮，每一個實(shí)體之間的關(guān)系是一對一，還是多對一，還是多對多，實(shí)體這樣定下之后，管理上會不會出現(xiàn)問題，信息存儲會不會有冗余，考慮問