銷(xiāo)售管理系統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì)及說(shuō)明書(shū)

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

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

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

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

5、列名數(shù)據(jù)類(lèi)型寬度為空性說(shuō)明采購(gòu)訂單號(hào)int主關(guān)鍵字商品編號(hào)int來(lái)自商品表的外鍵員工編號(hào)int來(lái)自員工表的外鍵供應(yīng)商編號(hào)int來(lái)自供應(yīng)商表的外鍵訂購(gòu)數(shù)量int訂購(gòu)日期smalldatetime銷(xiāo)售訂單表列名數(shù)據(jù)類(lèi)型寬度為空性說(shuō)明銷(xiāo)售訂單號(hào)int主關(guān)鍵字商品編號(hào)int來(lái)自商品表的外鍵員工編號(hào)int來(lái)自員工表的外鍵客戶編號(hào)int來(lái)自客戶表的外鍵訂購(gòu)數(shù)量int訂購(gòu)日期smalldatetime六、數(shù)據(jù)庫(kù)的實(shí)施（共65分）創(chuàng)建數(shù)據(jù)庫(kù)各種對(duì)象和執(zhí)行查詢的SQL語(yǔ)句1.創(chuàng)建數(shù)據(jù)庫(kù)（5分）createdatabase銷(xiāo)售管理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 銷(xiāo)售管理gocreatetable 部門(mén)(部門(mén)編號(hào)intprimarykey, 部門(mén)名稱(chēng)varchar(30)notnullconstraint UQ_DName unique, 部門(mén)主管char(8), 備注varchar(50)gocreatetable 員工(員工編

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

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

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

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

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

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

13、客戶(2) 條件查詢（3分）：在銷(xiāo)售管理數(shù)據(jù)庫(kù)的銷(xiāo)售訂單表中，查詢員工編號(hào)為1、5和7的員工接受訂單信息。 select * from 銷(xiāo)售訂單 where 員工編號(hào)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分）：查詢國(guó)皓科技”訂購(gòu)的商品信息，包括客戶編號(hào)，公司名稱(chēng)，商品編號(hào)，商品名稱(chēng)，商品價(jià)格，訂購(gòu)數(shù)