數(shù)據(jù)庫(kù)大作業(yè)報(bào)告

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

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

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

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

5、件人姓名，發(fā)件人電話（主鍵）發(fā)件人地址.發(fā)件人郵編2. 發(fā)件人地址發(fā)件人郵編2NF收件人1. 收件人姓名，收件人電話（主鍵）收件人地址.收件人郵編2. 收件人地址收件人郵編2NF貨物1. 訂單號(hào)，標(biāo)簽ID(主鍵)類(lèi)型.重量3NF員工1. 員工編號(hào)（主鍵）員工姓名.員工性別.員工年齡.員工聯(lián)系電話.員工家庭住址.員工部門(mén).員工職位2. 員工聯(lián)系電話員工姓名.員工性別.員工年齡.員工家庭住址.員工部門(mén).員工職位3. 員工聯(lián)系電話員工部門(mén)員工職位3NF運(yùn)輸器1. 運(yùn)輸器編號(hào)（主鍵）運(yùn)輸器類(lèi)型.出發(fā)地.目的地.裝載時(shí)間.到達(dá)時(shí)間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,訂單號(hào)（主鍵）二維碼.尺寸.重量2. 二維碼尺寸.重量2NF員工登陸1. 員工登錄名，員工編號(hào)員工登陸密碼3NF訂單狀態(tài)信息1. 訂單號(hào)（主鍵）所在地.掃面時(shí)間.上一站.下一站.是否到達(dá).是否正常3NF異常訂單1. 訂單號(hào)，標(biāo)簽ID(主鍵) 異常信息.異常時(shí)間.異常地點(diǎn)3NF中轉(zhuǎn)站1. 中轉(zhuǎn)站ID(主鍵) 中轉(zhuǎn)站名.中轉(zhuǎn)站地址.到達(dá)時(shí)間.出發(fā)時(shí)間2. 中轉(zhuǎn)站地址中轉(zhuǎn)站名2NF客戶表3. 客戶電話客戶姓名.客戶地址.客戶郵編3NF3.3表結(jié)構(gòu)設(shè)計(jì)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ù)庫(kù)表設(shè)計(jì)：將ER圖轉(zhuǎn)化為數(shù)據(jù)表3.5實(shí)驗(yàn)要求1.員工登陸 proc(username ,password分開(kāi)判斷并給出提示信息)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'員工登陸密碼錯(cuò)誤'execyg_dengluyonghuming=luchenyang,kouling=123456execyg_dengluyonghuming='wanger002',kouling='002'execyg_dengluyongh

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

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

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

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

13、int'訂單已生成,不能修改表中任何信息'end嘗試修改發(fā)件人表中信息，提示“訂單已生成，不能修改表中任何信息”嘗試修改收件人表中信息，提示“訂單已生成，不能修改表中任何信息”嘗試修改訂單表中信息，提示“訂單已生成，不能修改表中信息”6.一旦新建運(yùn)單，就判斷發(fā)件人是否在用戶之中，若是則轉(zhuǎn)用戶表，顯示用戶信息，如不是，自動(dòng)增加用戶。 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;)插入后的客戶表自動(dòng)添加了“王五”的數(shù)據(jù)現(xiàn)在我要插一條數(shù)據(jù)客戶表中有，發(fā)件人表和發(fā)件人_訂單表中沒(méi)有?？蛻舯碇邢炔迦脍w六的數(shù)據(jù)insertinto發(fā)件人values('趙六','#39;,'云南昆明','650500')insertinto發(fā)件人_訂單values('7492005','趙六','#39;)可以看到，顯示出了客戶表中趙六的信息。四.實(shí)驗(yàn)小結(jié)即使是上了一學(xué)期的數(shù)據(jù)庫(kù)課程，但這些都是針對(duì)數(shù)據(jù)庫(kù)的一些基本操作，沒(méi)有深入了解到數(shù)據(jù)庫(kù)的內(nèi)部世界。SQL Sever的操作和編程在本次項(xiàng)目中都讓我得到了很好的鍛煉。在當(dāng)初定實(shí)體時(shí)，對(duì)每一個(gè)可能要實(shí)現(xiàn)功能的內(nèi)容要進(jìn)行詳細(xì)的考慮，每一個(gè)實(shí)體之間的關(guān)系是一對(duì)一，還是多對(duì)一，還是多對(duì)多，實(shí)體這樣定下之后，管理上會(huì)不會(huì)出現(xiàn)問(wèn)題，信息存儲(chǔ)會(huì)不會(huì)有冗余，考慮問(wèn)