《~_coursedesignmodule_studentputinfile_2012022624109》由会员分享，可在线阅读，更多相关《~_coursedesignmodule_studentputinfile_2012022624109（9页珍藏版）》请在金锄头文库上搜索。
1、通过工人名查询工人相关信息create proc proc_Worker1WorkerName varchar(8)asselect WorkerNo 职工号,WoerkerAge 年龄,WorkerSex性别,WorkerType工种,WorkerNo车间号from workerwhere WorkerName=WorkerNameproc_Worker1 刘德华通过产品号查询产品相关信息create proc proc_Product1ProductName varchar(20)asselect product.ProductNo产品号,ProductPrice产品价格,WorkNo车间
2、号,WareNo仓库号,Snuml产品库存量from product,Wapwhere product.ProductNo=Wap.ProductNo and product.ProductName=ProductNamedrop proc proc_Product1select * from productproc_Product1 电风扇通过零件号查询零件相关信息create proc proc_component1ComNo varchar(10)asselect ComPrice零件价格,ComWeight重量,WareNo仓库号,Snum2零件库存量from component,Wa
3、cwhere component.ComNo=Wac.ComNo and component.ComNo=ComNodrop proc proc_component1proc_component1 10通过产品名查询产品生产相关信息create proc proc_pc1productName varchar(20)asselect product.ProductNo产品号,ComNo所需零件号,ComNum2零件数from product,PCwhere product.ProductNo=PC.ProductNo and ProductName=productNamedrop proc p
4、roc_pc1proc_pc1 电冰箱通过车间号查询车间信息create proc proc_workshop1workNo varchar(10)asselect CNo车间主任编号,WorkAddress地址,Workphone电话,FacName厂名from Workshopwhere WorkNO=workNoproc_workshop1 2通过生产日期查询零件生产信息create proc proc_wc1Timel varchar(10)asselect WorkNo车间号,ComNo零件号,ComNuml零件数from WCwhere Timel=Timelproc_wc1 20
5、12drop proc proc_wc1create proc proc_wap1Time2 varchar(10)asselect WareNo车间号,ProductNo产品号,Snuml产品数from Wapwhere Time2=Time2proc_wap1 2012-仓库零件触发器create trigger TnsertWC_triggeron WCfor insertas declare a intdeclare b varchar(10)declare c varchar(10)select a=ComNuml from insertedselect b=ComNo from i
6、nsertedselect c=WorkNo from insertedupdate Wacset Snum2=Snum2+awhere Wac.ComNo=b and Wac.WorkNo=cgodrop trigger InsertPC_triggercreate trigger InsertPC_triggeron PCfor insertas declare a intdeclare b varchar(10)select a=ComNum2 from insertedselect b=ProductNo from insertedupdate Wapset Snuml=Snuml+a
7、where Wap.ProductNo=bgoinsert into PC VALUES(2013,1001,10,300,30)drop trigger TnsertWC_triggerinsert into WC values(2012,2,10,1000)delete from WCupdate WCset ComNuml=1500where ComNo=11insert into wac values(200,10,1000)select * from waccreate trigger insertWap_triggeron Wapfor insertasupdate Wacset
8、Snum2=Snum2-(select Snumlfrom inserted)*(select ComNum2from PCwhere ProductNo in(select ProductNofrom Inserted)and PC.ComNo=Wac.ComNo)CREATE table factory(FacName varchar(30) primary key,FMname char(8) not null,)create table workshop(WorkNO varchar(10)primary key,CNo varchar(8),Workphone varchar(12)
9、,WorkAddress varchar(20),FacName varchar(30)not null,foreign key(FacName)references factory(FacName);create table worker(WorkerNo varchar(10) primary key,WorkerName varchar(8),WoerkerAge int,WorkerSex varchar(2) check(WorkerSex in (男,女),WorkerType varchar(20),WorkNo varchar(10)not null,foreign key(W
10、orkNo)references Workshop(WorkNo);create table product(ProductNo varchar(10)primary key,ProductName varchar(20),ProductPrice numeric(5,1) default 0,check(ProductPrice0),WorkNo varchar(10)not null,foreign key(WorkNo)references workshop(WorkNo);create table component(ComNo varchar(10)primary key,ComPr
11、ice numeric(5,1)default 0,check(ComPrice0),ComWeight int);create table Warehouse (WareNo varchar(10)primary key, WNo varchar(8), Warephone varchar(12), FacName varchar(30)not null, foreign key(FacName)references factory(FacName) ); create table WC (Timel char(10) not null, WorkNo varchar(10) not nul
12、l, ComNo varchar(10) not null, ComNuml int, primary key(Timel,WorkNo, ComNo), foreign key(WorkNo)references workshop(WorkNo), foreign key(ComNo)references component(ComNo), ); drop table WC create table PC (Time2 char(10) not null, ProductNo varchar(10) not null, ComNo varchar(10) not null, ComNum2
13、int, ComNum1 int, primary key(Time2,ProductNo, ComNo), foreign key(ProductNo)references product(ProductNo), foreign key(ComNo)references component(ComNo), ); drop table PC create table Wap (WareNo varchar(10) not null,ProductNo varchar(10) not null,Snuml int,primary key(WareNo, ProductNo), foreign k
14、ey(ProductNo)references product(ProductNo), foreign key(WareNo)references warehouse(WareNo) ); drop table Wap create table Wac (WareNo varchar(10) not null,ComNo varchar(10) not null,Snum2 int,primary key(WareNo, ComNo), foreign key(WareNo)references warehouse(WareNo), foreign key(ComNo)references component(ComNo) ); drop table Wac
