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

1、銷售管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)班級(jí) _姓名 _學(xué)號(hào) _成績(jī) _日期 _ 裝 訂 線 考 生 答 題 不 得 超 過 此 線 銷售管理系統(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ù)流程圖三、概念結(jié)構(gòu)設(shè)計(jì)（10分）銷售管理數(shù)據(jù)庫的e-r圖商品號(hào)商品名稱單價(jià)庫存量已售數(shù)量電話客戶編號(hào)聯(lián)系人姓名公司名稱出生年月聯(lián)系人姓名供應(yīng)商名稱供應(yīng)商編號(hào)號(hào)商品員工客戶供應(yīng)商采購銷售1mnm訂

2、購日期訂購數(shù)量訂購日期訂購數(shù)量email性別員工編號(hào)姓名獎(jiǎng)金nmmnm工資電話聘任日期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)，訂購數(shù)量，訂購日期） （

3、7）銷售訂單（銷售訂單號(hào)，商品編號(hào)，員工號(hào)，客戶號(hào)，訂購數(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)定義請(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取值為男或女出生年月smalldatetime聘任日期smallda

4、tetime工資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地址包含“”商品表列名數(shù)據(jù)類型寬度為空性說明商品編號(hào)int主關(guān)鍵字商品

5、名稱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í)施（共65分）創(chuàng)建數(shù)據(jù)庫各種對(duì)象和執(zhí)行查詢的sql語句1.創(chuàng)建數(shù)據(jù)庫（

6、5分）create database 銷售管理on primary(name=sales_data, filename=d:datasale_data.mdf, size=10mb, maxsize=unlimited,filegrowth=10%)log on(name=sales_log, filename=d:datasale_log.ldf, size=3mb, maxsize=5mb,filegrowth=1mb)2.創(chuàng)建數(shù)據(jù)表并給表添加數(shù)據(jù)（20分）use 銷售管理gocreate table 部門(部門編號(hào) int primary key, 部門名稱 varchar(30) no

7、t null constraint uq_dname unique, 部門主管 char(8), 備注 varchar(50)gocreate table 員工(員工編號(hào) int primary key, 姓名 varchar(20) not null, 性別 char(2) default 男 check(性別=男or 性別=女), 出生年月 smalldatetime, 聘任日期 smalldatetime, 工資 money check(工資between 800 and 100000), 部門編號(hào) int foreign key(部門編號(hào)) references 部門(部門編號(hào))goc

8、reate table 供應(yīng)商(供應(yīng)商編號(hào) int primary key, 供應(yīng)商名稱 varchar(50) not null, 聯(lián)系人姓名 char(8), 電話 char(13) check(len(電話)=7 and len(電話)=7 and len(電話)0), 庫存量 int check(庫存量=0), 已銷售量 int check(已銷售量=0)gocreate table 采購訂單(采購訂單號(hào) int primary key, 商品編號(hào) int references 商品(商品編號(hào)), 員工編號(hào) int references 員工(員工編號(hào)),供應(yīng)商編號(hào) int refer

9、ences 供應(yīng)商(供應(yīng)商編號(hào)),訂購數(shù)量 int,訂購日期 smalldatetime default getdate() gocreate table 銷售訂單(銷售訂單號(hào) int primary key, 商品編號(hào) int references 商品(商品編號(hào)), 員工編號(hào) int references 員工(員工編號(hào)),客戶編號(hào) int references 客戶(客戶編號(hào)),訂購數(shù)量 int,訂購日期 smalldatetime default getdate()表添加的數(shù)據(jù)如下圖所示：3.創(chuàng)建數(shù)據(jù)庫各種對(duì)象（共25分）a.創(chuàng)建試圖（5分）：創(chuàng)建一個(gè)客戶訂購情況的視圖，包括客戶編號(hào)，

10、公司名稱，商品編號(hào)，商品名稱，單價(jià)和訂購數(shù)量，并對(duì)視圖進(jìn)行加密。create view 客戶訂購情況with encryptionasselect a.客戶編號(hào),公司名稱,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)量函

11、數(shù)，根據(jù)商品名稱統(tǒng)計(jì)某種商品銷售的次數(shù)create function 銷售次數(shù)(商品名稱 varchar(50)=路由器)returns intbegindeclare 銷售次數(shù) intselect 銷售次數(shù)=count(*)from 商品 join 銷售訂單on 商品.商品編號(hào)=銷售訂單.商品編號(hào)where 商品名稱=商品名稱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ì)表

12、。create proc 客戶訂購信息(客戶名稱 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 trigger 銷售on 銷售訂

13、單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 性別（5）多表查詢（2分）：查詢“國皓科技有限公司”訂購的商品信息，包括客戶編號(hào)，公司名稱，商品編號(hào)，商品名稱，商品價(jià)格，訂購