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

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

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

3、訂購數(shù)量，訂購日期）（ 7）銷售訂單（銷售訂單號(hào)，商品編號(hào)，員工號(hào)，客戶號(hào)，訂購數(shù)量，訂購日3/10.期）五、物理結(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)定義請(qǐng)看后面創(chuàng)建對(duì)象的SQL語句。部門表列名數(shù)據(jù)類型寬度為空性說明部門編號(hào)int主關(guān)鍵字部門名稱varchar30唯一鍵部門主管char8備注varchar50員工表列名數(shù)據(jù)類型寬度為空性說明員工編號(hào)int主關(guān)鍵字姓名varchar20性別char2取值為男或女出生年月

4、smalldatetime聘任日期smalldatetime工資money取值范圍800-100000部門編號(hào)int來自“部門表”的外鍵供應(yīng)商表列名數(shù)據(jù)類型寬度為空性說明供應(yīng)商編號(hào)int主關(guān)鍵字供應(yīng)商名稱varchar50聯(lián)系人姓名char8電話varchar13電話長(zhǎng)度為7-13 位字符地址varchar100E-mailvarchar30E-mail地址包含“ ”客戶表列名數(shù)據(jù)類型寬度為空性說明客戶編號(hào)int主關(guān)鍵字公司名稱varchar50聯(lián)系人姓名char8電話varchar13電話長(zhǎng)度為7-13 位字符地址varchar100E-mailvarchar30E-mail地址包4/10.

5、含“ ”商品表列名數(shù)據(jù)類型寬度為空性說明商品編號(hào)int主關(guān)鍵字商品名稱varchar50單價(jià)Decimal(8,2)單價(jià)大于 0庫存量int庫存量為非負(fù)數(shù)已售數(shù)量int已售數(shù)量為非負(fù)數(shù)采購訂單表列名數(shù)據(jù)類型寬度為空性說明采購訂單號(hào)int主關(guān)鍵字商品編號(hào)int來自商品表的外鍵員工編號(hào)int來自員工表的外鍵供應(yīng)商編號(hào)int來自供應(yīng)商表的外鍵訂購數(shù)量int訂購日期smalldatetime銷售訂單表列名數(shù)據(jù)類型寬度為空性說明銷售訂單號(hào)int主關(guān)鍵字商品編號(hào)int來自商品表的外鍵員工編號(hào)int來自員工表的外鍵客戶編號(hào)int來自客戶表的外鍵訂購數(shù)量int訂購日期smalldatetime六、數(shù)據(jù)庫的實(shí)施

6、（共65 分）創(chuàng)建數(shù)據(jù)庫各種對(duì)象和執(zhí)行查詢的SQL語句1. 創(chuàng)建數(shù)據(jù)庫（ 5 分）createdatabase 銷售管理on primary( name=Sales_data ,filename ='d:dataSale_data.mdf',size =10mb,maxsize=unlimited,5/10.filegrowth=10%)logon( name=Sales_log ,filename ='d:dataSale_log.ldf',size =3mb,maxsize=5mb,filegrowth=1mb)2. 創(chuàng)建數(shù)據(jù)表并給表添加數(shù)據(jù)（ 20 分）

7、use 銷售管理gocreatetable部門( 部門編號(hào)intprimarykey,部門名稱varchar ( 30)not nullconstraintUQ_DNameunique ,部門主管char ( 8),備注 varchar ( 50)gocreatetable員工( 員工編號(hào)intprimarykey,姓名varchar ( 20) notnull,性別char ( 2) default' 男 'check( 性別 =' 男' or 性別 =' 女' ),出生年月smalldatetime,聘任日期smalldatetime,工資m

8、oney check( 工資 between 800and 100000),部門編號(hào)intforeignkey( 部門編號(hào) ) references部門 ( 部門編號(hào) )gocreatetable供應(yīng)商( 供應(yīng)商編號(hào)intprimarykey,供應(yīng)商名稱varchar ( 50) notnull,聯(lián)系人姓名char ( 8),電話 char ( 13) check( len ( 電話 )>=7 and len ( 電話 )<=13),地址 varchar ( 100),"E-mail"varchar ( 30) check( "E-mail"

9、like'%')gocreatetable客戶( 客戶編號(hào)int primarykey,公司名稱varchar ( 50)not null,聯(lián)系人姓名char ( 8),電話 char ( 13) check( len ( 電話 )>=7 and len ( 電話 )<=13),地址 varchar ( 100),"E-mail"varchar ( 30)check( "E-mail"like'%')gocreatetable商品( 商品編號(hào)intprimarykey,商品名稱varchar ( 50)not

10、null,6/10.單價(jià)decimal ( 8, 2) check( 單價(jià) >0),庫存量intcheck( 庫存量 >=0),已銷售量intcheck( 已銷售量 >=0)gocreatetable采購訂單( 采購訂單號(hào)intprimarykey,商品編號(hào)intreferences商品 ( 商品編號(hào) ),員工編號(hào)intreferences員工 ( 員工編號(hào) ),供應(yīng)商編號(hào) int references 供應(yīng)商 ( 供應(yīng)商編號(hào) ), 訂購數(shù)量 int ,訂購日期smalldatetimedefaultgetdate ()gocreatetable銷售訂單( 銷售訂單號(hào)intp

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

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

13、器 ')returns intbegindeclare 銷售次數(shù) intselect 銷售次數(shù) =count(*)from 商品 join銷售訂單on 商品 . 商品編號(hào) =銷售訂單 . 商品編號(hào)where 商品名稱 =商品名稱8/10.return 銷售次數(shù)end執(zhí)行函數(shù) ：select dbo.銷售次數(shù) (' 鼠標(biāo) ') as銷售次數(shù)select dbo. 銷售次數(shù) (default)d. 創(chuàng)建存儲(chǔ)過程（ 5 分） : 創(chuàng)建一個(gè)名為客戶訂購信息存儲(chǔ)過程，用于查詢指定客戶的聯(lián)系人姓名、聯(lián)系方式、以及該公司訂購產(chǎn)品的明細(xì)表。create proc客戶訂購信息(客戶名稱

14、varchar(50)='通恒機(jī)械有限公司 ')asselect 公司名稱 , 聯(lián)系人姓名 , 商品名稱 , 單價(jià) , 訂購數(shù)量 , 訂購日期 from 客戶 join 銷售訂單On 客戶 . 客戶編號(hào) =銷售訂單 . 客戶編號(hào)join商品on 商品 . 商品編號(hào) =銷售訂單 . 商品編號(hào)where 公司名稱 =客戶名稱執(zhí)行輸入?yún)?shù)為默認(rèn)值的存儲(chǔ)過程：exec 客戶訂購信息執(zhí)行帶輸入?yún)?shù)的存儲(chǔ)過程：exec 客戶訂購信息 ' 三川實(shí)業(yè)有限公司e. 創(chuàng)建觸發(fā)器（ 5 分） : 創(chuàng)建一個(gè)觸發(fā)器實(shí)現(xiàn)當(dāng)向銷售訂單表添加一條記錄時(shí)，自動(dòng)修改商品表的庫存數(shù)量和已售數(shù)量。create

15、 trigger銷售on 銷售訂單after insertasupdate 商品set庫存數(shù)量 =庫存數(shù)量 - 訂購數(shù)量 ,已售數(shù)量 =已售數(shù)量 +訂購數(shù)量from 商品 join insertedon 商品 . 商品編號(hào) =inserted.商品編號(hào)4. 執(zhí)行查詢（共 15 分）(1) 簡(jiǎn)單查詢（ 2 分）：從客戶表中檢索所有客戶的公司名稱、 聯(lián)系人姓名和地址。 select 公司名稱 , 聯(lián)系人姓名 , 地址 from 客戶(2) 條件查詢（ 3 分）：在銷售管理數(shù)據(jù)庫的銷售訂單表中，查詢員工編號(hào)為1、5 和 7 的員工接受訂單信息。select * from銷售訂單 where 員工編號(hào) in(1,5,7)（ 3）統(tǒng)計(jì)查詢（ 2 分）：統(tǒng)計(jì)所有員工的平均工資、最高工資、最低工資和工資總和。select avg(工資 ) as平均工資 ,max( 工資 ) as最高工資 ,min( 工資 ) as最低工資 ,sum( 工資 ) as工資總和 from員工（ 4）分組查詢（ 3 分）：查詢男女員工的平均工資。select性別 ,avg( 工資 ) as平均工資 from員工 group by性別9/10.（ 5）多表查詢（ 2 分）：查詢“國(guó)皓科技有限公司”訂購的商品信息，包括客戶編號(hào)，公司名稱，商品編號(hào)，商品名稱，商品價(jià)格，訂購數(shù)量。s