銷售管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)及說明書

1、-. z.銷售管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)班級 _ _* _成績 _日期 _ 裝 訂 線 考 生 答 題 不 得 超 過 此 線 銷售管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)說明書一、數(shù)據(jù)庫系統(tǒng)功能簡介（5分）:商品銷售管理數(shù)據(jù)庫是一個(gè)用來管理商品銷售信息的數(shù)據(jù)庫系統(tǒng)。該數(shù)據(jù)庫可以用來管理企業(yè)的商品信息、部門信息、員工信息、供應(yīng)商信息、客戶信息以及采購信息和銷售信息，實(shí)現(xiàn)信息的自動化管理和利用。需求分析（10分）銷售管理數(shù)據(jù)庫的數(shù)據(jù)流程圖三、概念結(jié)構(gòu)設(shè)計(jì)（10分）銷售管理數(shù)據(jù)庫的E-R圖商品號商品名稱單價(jià)庫存量已售數(shù)量客戶編號聯(lián)系人*公司名稱出生年月聯(lián)系人*供應(yīng)商名稱供應(yīng)商編號號商品員工客戶供應(yīng)商采購銷售1mnm訂購日期訂購

2、數(shù)量訂購日期訂購數(shù)量Email性別員工編號*獎金nmmnm工資聘任日期Email地址Email地址屬于部門主管部門編號1備注部門名稱部門四、邏輯結(jié)構(gòu)設(shè)計(jì)（5分）銷售管理數(shù)據(jù)庫關(guān)系模式：（1）部門（部門編號，部門名稱，部門主管，備注）（2）員工（員工編號，性別，出生年月，聘任日期，工資，部門編號）（3）商品（商品編號，商品名稱，單價(jià)，庫存量，已銷售量） （4）客戶（客戶編號，公司名稱，聯(lián)系人，地址，Email）（5）供應(yīng)商（供應(yīng)商編號，供應(yīng)商名稱，聯(lián)系人，地址，Email）（6）采購訂單（采購訂單號，商品編號，員工號，供應(yīng)商編號，訂購數(shù)量，訂購日期） （7）銷售訂單（銷售訂單號，商品編號，員工號

3、，客戶號，訂購數(shù)量，訂購日期）五、物理結(jié)構(gòu)設(shè)計(jì)（5分）為銷售管理數(shù)據(jù)庫設(shè)計(jì)一個(gè)數(shù)據(jù)文件，一個(gè)日志文件，放在D盤的data文件夾下；銷售管理數(shù)據(jù)庫里面包含7個(gè)表，分別為商品、部門、員工、供應(yīng)商、客戶以及采購訂單和銷售訂單；數(shù)據(jù)庫和數(shù)據(jù)表的結(jié)構(gòu)定義請看后面創(chuàng)建對象的SQL語句。部門表列名數(shù)據(jù)類型寬度為空性說明部門編號int主關(guān)鍵字部門名稱varchar30唯一鍵部門主管char8備注varchar50員工表列名數(shù)據(jù)類型寬度為空性說明員工編號int主關(guān)鍵字varchar20性別char2取值為男或女出生年月smalldatetime聘任日期smalldatetime工資money取值圍800-100

4、000部門編號int來自部門表”的外鍵供應(yīng)商表列名數(shù)據(jù)類型寬度為空性說明供應(yīng)商編號int主關(guān)鍵字供應(yīng)商名稱varchar50聯(lián)系人char8varchar13長度為7-13位字符地址varchar100varchar30地址包含”客戶表列名數(shù)據(jù)類型寬度為空性說明客戶編號int主關(guān)鍵字公司名稱varchar50聯(lián)系人char8varchar13長度為7-13位字符地址varchar100varchar30地址包含”商品表列名數(shù)據(jù)類型寬度為空性說明商品編號int主關(guān)鍵字商品名稱varchar50單價(jià)Decimal(8,2)單價(jià)大于0庫存量int庫存量為非負(fù)數(shù)已售數(shù)量int已售數(shù)量為非負(fù)數(shù)采購訂單表

5、列名數(shù)據(jù)類型寬度為空性說明采購訂單號int主關(guān)鍵字商品編號int來自商品表的外鍵員工編號int來自員工表的外鍵供應(yīng)商編號int來自供應(yīng)商表的外鍵訂購數(shù)量int訂購日期smalldatetime銷售訂單表列名數(shù)據(jù)類型寬度為空性說明銷售訂單號int主關(guān)鍵字商品編號int來自商品表的外鍵員工編號int來自員工表的外鍵客戶編號int來自客戶表的外鍵訂購數(shù)量int訂購日期smalldatetime六、數(shù)據(jù)庫的實(shí)施（共65分）創(chuàng)建數(shù)據(jù)庫各種對象和執(zhí)行查詢的SQL語句1.創(chuàng)建數(shù)據(jù)庫（5分）createdatabase銷售管理onprimary(name=Sales_data,filename=d:dataS

6、ale_data.mdf,size=10mb,ma*size=unlimited,filegrowth=10%)logon(name=Sales_log,filename=d:dataSale_log.ldf,size=3mb,ma*size=5mb,filegrowth=1mb)2.創(chuàng)建數(shù)據(jù)表并給表添加數(shù)據(jù)（20分）use 銷售管理gocreatetable 部門(部門編號intprimarykey, 部門名稱varchar(30)notnullconstraint UQ_DName unique, 部門主管char(8), 備注varchar(50)gocreatetable 員工(員工編

7、號intprimarykey,varchar(20)notnull, 性別 char(2)default男check(性別=男or 性別=女), 出生年月smalldatetime, 聘任日期smalldatetime, 工資 moneycheck(工資between 800 and 100000), 部門編號intforeignkey(部門編號)references 部門(部門編號)gocreatetable 供應(yīng)商(供應(yīng)商編號intprimarykey, 供應(yīng)商名稱 varchar(50)notnull, 聯(lián)系人 char(8),char(13)check(len()=7 andlen()

8、=7 andlen()0), 庫存量 intcheck(庫存量=0), 已銷售量intcheck(已銷售量=0)gocreatetable 采購訂單(采購訂單號intprimarykey, 商品編號intreferences 商品(商品編號), 員工編號intreferences 員工(員工編號),供應(yīng)商編號intreferences 供應(yīng)商(供應(yīng)商編號),訂購數(shù)量int,訂購日期smalldatetimedefaultgetdate() gocreatetable 銷售訂單(銷售訂單號intprimarykey, 商品編號intreferences 商品(商品編號), 員工編號intrefe

9、rences 員工(員工編號),客戶編號intreferences 客戶(客戶編號),訂購數(shù)量int,訂購日期smalldatetimedefaultgetdate()表添加的數(shù)據(jù)如下圖所示：3.創(chuàng)建數(shù)據(jù)庫各種對象（共25分）a.創(chuàng)建試圖（5分）：創(chuàng)建一個(gè)客戶訂購情況的視圖，包括客戶編號，公司名稱，商品編號，商品名稱，單價(jià)和訂購數(shù)量，并對視圖進(jìn)行加密。create view 客戶訂購情況with encryptionasselect a.客戶編號,公司名稱,c.商品編號,商品名稱,單價(jià),訂購數(shù)量 from 客戶a join 銷售訂單as b on a.客戶編號=b.客戶編號 join 商品as

10、 c on c.商品編號=b.商品編號b.創(chuàng)建索引（5分）：在銷售管理系統(tǒng)中，經(jīng)常要按照客戶名稱查詢信息，通過給客戶表的公司名稱創(chuàng)建一個(gè)唯一非聚集索引來提高查詢速度。Create unique inde* i*_ameOn 客戶(公司名稱)c.創(chuàng)建用戶定義函數(shù)（5分）：創(chuàng)建標(biāo)量函數(shù)，根據(jù)商品名稱統(tǒng)計(jì)*種商品銷售的次數(shù)create function 銷售次數(shù)(商品名稱 varchar(50)=路由器)returns intbegindeclare 銷售次數(shù) intselect 銷售次數(shù)=count(*)from 商品 join 銷售訂單on 商品.商品編號=銷售訂單.商品編號where 商品名稱

11、=商品名稱return 銷售次數(shù)end執(zhí)行函數(shù)：select dbo.銷售次數(shù)(鼠標(biāo)) as 銷售次數(shù)select dbo.銷售次數(shù)(default)d. 創(chuàng)建存儲過程（5分）: 創(chuàng)建一個(gè)名為客戶訂購信息存儲過程，用于查詢指定客戶的聯(lián)系人、聯(lián)系方式、以及該公司訂購產(chǎn)品的明細(xì)表。create proc 客戶訂購信息(客戶名稱varchar(50)=通恒機(jī)械)asselect 公司名稱,聯(lián)系人,商品名稱,單價(jià),訂購數(shù)量,訂購日期from 客戶join 銷售訂單On 客戶.客戶編號=銷售訂單.客戶編號join 商品on 商品.商品編號=銷售訂單.商品編號where 公司名稱=客戶名稱執(zhí)行輸入?yún)?shù)為默

12、認(rèn)值的存儲過程：e*ec 客戶訂購信息執(zhí)行帶輸入?yún)?shù)的存儲過程：e*ec 客戶訂購信息 三川實(shí)業(yè)e. 創(chuàng)建觸發(fā)器（5分）: 創(chuàng)建一個(gè)觸發(fā)器實(shí)現(xiàn)當(dāng)向銷售訂單表添加一條記錄時(shí)，自動修改商品表的庫存數(shù)量和已售數(shù)量。create trigger 銷售on 銷售訂單after insertasupdate 商品set 庫存數(shù)量=庫存數(shù)量-訂購數(shù)量,已售數(shù)量=已售數(shù)量+訂購數(shù)量from 商品join insertedon 商品.商品編號=inserted.商品編號4.執(zhí)行查詢（共15分）(1)簡單查詢（2分）：從客戶表中檢索所有客戶的公司名稱、聯(lián)系人和地址。 select 公司名稱,聯(lián)系人,地址from

13、客戶(2) 條件查詢（3分）：在銷售管理數(shù)據(jù)庫的銷售訂單表中，查詢員工編號為1、5和7的員工接受訂單信息。 select * from 銷售訂單 where 員工編號in(1,5,7)（3）統(tǒng)計(jì)查詢（2分）：統(tǒng)計(jì)所有員工的平均工資、最高工資、最低工資和工資總和。select avg(工資) as 平均工資,ma*(工資) as 最高工資,min(工資) as 最低工資,sum(工資) as 工資總和 from 員工（4）分組查詢（3分）：查詢男女員工的平均工資。select 性別,avg(工資) as 平均工資 from 員工 group by 性別（5）多表查詢（2分）：查詢國皓科技”訂購的商品信息，包括客戶編號，公司名稱，商品編號，商品名稱，商品價(jià)格，訂購數(shù)