【正文】 0),Gname varchar(30),Empnumber varchar(10),Sendliang int not null,Senddate date,foreign key (Supnumber) references Supplier(Supnumber), foreign key (Gname) references Goods(Gname),foreign key (Empnumber) references Employer(Empnumber))create table Sbacklist(Sbacknumber varchar(10) primary key,Supnumber varchar(10),Gname varchar(30),Empnumber varchar(10),Sbackling int not null,Sbackreas varchar(30),foreign key (Supnumber) references Supplier(Supnumber), foreign key (Gname) references Goods(Gname),foreign key (Empnumber) references Employer(Empnumber))create table Dyaolist(Dynumber varchar(10) primary key,Gname varchar(30),Cusmumber varchar(10),Empnumber varchar(10),Dyliang int not null,Dydate date,foreign key (Cusmumber) references Customer(Cusnumber), foreign key (Gname) references Goods(Gname),foreign key (Empnumber) references Employer(Empnumber))create table Salelist(Salenumber varchar(10) primary key,Gname varchar(30),Cusmumber varchar(10),Empnumber varchar(10),Saleliang int not null,Saledate date,foreign key (Cusmumber) references Customer(Cusnumber), foreign key (Gname) references Goods(Gname),foreign key (Empnumber) references Employer(Empnumber))create table Cbacklist(Cbacknumber varchar(10) primary key,Gname varchar(30),Cusnumber varchar(10),Empnumber varchar(10),Cbackliang int not null,Cbackreas varchar(30),foreign key (Cusnumber) references Customer(Cusnumber), foreign key (Gname) references Goods(Gname),foreign key (Empnumber) references Employer(Empnumber))create table Inlist(Innumer varchar(10) primary key,Gname varchar(30),Hounnumber varchar(10),Empnumber varchar(10),Inliang int not null,Indate date,foreign key (Hounnumber) references Hourse(Hounumber), foreign key (Gname) references Goods(Gname),foreign key (Empnumber) references Employer(Empnumber))create table Outlist(Outnumber varchar(10) primary key,Gname varchar(30),Hounnumber varchar(10),Empnumber varchar(10),Outliang int not null,Outdate date,foreign key (Hounnumber) references Hourse(Hounumber), foreign key (Gname) references Goods(Gname),foreign key (Empnumber) references Employer(Empnumber))create table Lostlist(Lostnumber varchar(10) primary key,Gname varchar(30),Hounnumber varchar(10),Empnumber varchar(10),Lostliang int not null,Lostreas varchar(30),foreign key (Hounnumber) references Hourse(Hounumber), foreign key (Gname) references Goods(Gname),foreign key (Empnumber) references Employer(Empnumber))create table Panclist(Panumber varchar(10) primary key,Gname varchar(30),Hounnumber varchar(10),Empnumber varchar(10),Pancliang int not null,foreign key (Hounnumber) references Hourse(Hounumber), foreign key (Gname) references Goods(Gname),foreign key (Empnumber) references Employer(Empnumber))視圖create view Vi_supplierasselect *from Suppliercreate view Vi_goodsasselect *from Goodscreate view Vi_saleasselect Gname,Saleliangfrom Salelistcreate view Vi_baosunasselect Gname,Lostliangfrom Lostlistcreate view Vi_pancunasselect Gname,Pancliangfrom Panclist索引create nonclustered index In_Supnameon Supplier(SupName)觸發(fā)器限制刪除C02客戶的退貨記錄create trigger tri_C02on Cbacklistinstead of deleteas begin if exists( select * from deleted where Cusnumber=39。, 39。6．附錄 附錄一 數(shù)據(jù)字典 數(shù)據(jù)項(xiàng) 數(shù)據(jù)項(xiàng)編號數(shù)據(jù)項(xiàng)名數(shù)據(jù)項(xiàng)含義與其他數(shù)據(jù)項(xiàng)關(guān)系類型Dl1Supnumber供應(yīng)商編號　varchar(10)Dl2Supname供應(yīng)商名稱　varchar(30)Dl3Supadress供應(yīng)商地址　varchar(30)Dl4Supphone供應(yīng)商電話　varchar(20)Dl5Gsname藥品名稱　varchar(20)Dl6Gkind藥品分類　varchar(20)Dl7Gtype藥品劑型　varchar(20)Dl8Gprice藥品單價(jià)　floatDl9Gshelf藥品保質(zhì)期　dateDl10Empnumber職員編號　varchar(10)Dl11Empkind職員類別　varchar(10)Dl12Empname職員姓名　varchar(10)Dl13Hounumer倉庫編號　varchar(10)Dl14Houkind倉庫類別　varchar(10)Dl15Houaddr倉庫地址　varchar(20)Dl16Buynumber采購單編號　varchar(10)Dl17Buydate采購日期　dateDl18Buyliang采購量　intDl19Sendnumber發(fā)貨單編號　varchar(10)Dl20Sendliang發(fā)貨量　intDl21Senddate發(fā)貨日期　dateDl22Lostnumber報(bào)損單編號　varchar(10)Dl23Lostreas報(bào)損原因　varchar(30)Dl24Lostliang報(bào)損量　intDl25Swnumber實(shí)物清單編號　varchar(10)Dl26Swliang實(shí)物數(shù)量　intDl27Jumber盤查記錄單編號　varchar(10)Dl28Jcliang記錄量　intDl29Panumber盤存單編號　varchar(10)Dl30Pancliang盤存實(shí)際量　intDl31Dynumber訂藥單編號　varchar(10)Dl32Dydate訂藥日期　dateDl33Dyliang訂藥數(shù)量　intDl34Salenumber銷售單編號　varchar(10)Dl35Saledate銷售日期　dateDl36Saleliang銷售量　intDl37Cusnumber客戶編號　varchar(10)Dl38Cusname客戶姓名　varchar(10)Dl39Cussex客戶性別　varchar(2)Dl40Cusphone客戶電話　varchar(20)Dl41Innumber藥品入庫單編號　varchar(10)Dl42Indate藥品入庫日期　dateDl43Inliang藥品入庫量　intDl44Outnumber藥品出庫單編號　varchar(10)Dl45Outreas藥品出庫原因　varchar(30)Dl46Outdate藥品出庫時(shí)間　dateDl47Outliang藥品出庫量　intDl48CBacknum客戶退貨單編號　varchar(10)Dl49Cbackliang客戶退貨單量　intDl50Cbackreas客戶退貨原因　varchar(30)Dl51Sbacknum藥店退貨單編號　varchar(10)Dl52Sbackliang藥店退貨單數(shù)量　intDl53Sbackreas藥店退貨原因　varchar(30)數(shù)據(jù)結(jié)構(gòu)編號數(shù)據(jù)結(jié)構(gòu)名稱數(shù)據(jù)結(jié)構(gòu)含義 數(shù)據(jù)結(jié)構(gòu)組成DS1Supplier供應(yīng)商Supnumber，Supname，Supadress，SupphoneDS2Customer客戶Cusnumber，Cusname，Cussex，CusphoneDS3Goods藥品Gname，Gkind，Gtype，Gprice，GshelfDS4Hourse倉庫Hounumber，Houkind，HouaddrDS5Employer職員Empnumber，Empname，EmpkindDS7Buylist采購單Buynumber，Buydate，Buyliang,Gname,Supnumber,EmpnumberDS8Sendlist發(fā)貨單Sendnumber，Sendliang，Senddate,Gname,Supnumber,Empnum