|
附錄一、數據庫建表代碼
use master
go
if exists (select * from dbo.sysdatabases where name = 'vendition')
drop database vendition
GO
create database vendition
go
use vendition
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TravelService_Loan]') and OBJECTPROPERTY(id, N'ZreceiverInfo') = 1)
drop table [dbo].[TravelService_Loan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TravelService_Loan]') and OBJECTPROPERTY(id, N'Dorder') = 1)
drop table [dbo].[TravelService_Loan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TravelService_Loan]') and OBJECTPROPERTY(id, N'CcheckOrder') = 1)
drop table [dbo].[TravelService_Loan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TravelService_Loan]') and OBJECTPROPERTY(id, N'ZgoodDailyOrder') = 1)
drop table [dbo].[TravelService_Loan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TravelService_Loan]') and OBJECTPROPERTY(id, N'SgainTotal') = 1)
drop table [dbo].[TravelService_Loan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TravelService_Loan]') and OBJECTPROPERTY(id, N'CstorageInfo') = 1)
drop table [dbo].[TravelService_Loan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TravelService_Loan]') and OBJECTPROPERTY(id, N'CinStorage') = 1)
drop table [dbo].[TravelService_Loan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TravelService_Loan]') and OBJECTPROPERTY(id, N'CprepareOrder') = 1)
drop table [dbo].[TravelService_Loan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TravelService_Loan]') and OBJECTPROPERTY(id, N'ZmoneyDailyOrder') = 1)
drop table [dbo].[TravelService_Loan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TravelService_Loan]') and OBJECTPROPERTY(id, N'SpayforOrder') = 1)
drop table [dbo].[TravelService_Loan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TravelService_Loan]') and OBJECTPROPERTY(id, N'SpayforOrder') = 1)
drop table [dbo].[TravelService_Loan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TravelService_Loan]') and OBJECTPROPERTY(id, N'SgoodsTotal') = 1)
drop table [dbo].[TravelService_Loan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TravelService_Loan]') and OBJECTPROPERTY(id, N'DorderItem') = 1)
drop table [dbo].[TravelService_Loan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TravelService_Loan]') and OBJECTPROPERTY(id, N'CpickUpGoodsOrder') = 1)
drop table [dbo].[TravelService_Loan]
GO
CREATE TABLE ZreceiverInfo (
ReceiverID INTEGER NOT NULL,
ReceiverName VARCHAR ( 255 ) NOT NULL,
ReceiverPassW SMALLINT NOT NULL,
Gender SMALLINT NOT NULL,
ApplyDate datetime NOT NULL,
LastLoginDate datetime NOT NULL,
CounterID SMALLINT NOT NULL,
Others SMALLINT NOT NULL,
ZreceiverInfo_ID INTEGER NOT NULL,
CONSTRAINT PK_ZreceiverInfo11 PRIMARY KEY (ZreceiverInfo_ID)
);
CREATE TABLE Dorder (
OrderNumber INTEGER NOT NULL,
OrderName VARCHAR ( 255 ) NOT NULL,
OrderFillUnit VARCHAR ( 255 ) NOT NULL,
OrderRequreUnit VARCHAR ( 255 ) NOT NULL,
OrderDate datetime NOT NULL,
OrderFillDate datetime NOT NULL,
OrderLister VARCHAR ( 255 ) NOT NULL,
Dorder_ID INTEGER NOT NULL,
CONSTRAINT PK_Dorder4 PRIMARY KEY (Dorder_ID)
);
CREATE TABLE CcheckOrder (
CheckNumber INTEGER NOT NULL,
CheckPerson VARCHAR ( 255 ) NOT NULL,
CheckResult SMALLINT NOT NULL,
COL_0 SMALLINT NOT NULL,
Dorder_ID INTEGER NOT NULL,
GoodName VARCHAR ( 255 ) NOT NULL,
InStorageOrderID INTEGER NOT NULL,
CONSTRAINT PK_CcheckOrder21 PRIMARY KEY (Dorder_ID)
);
CREATE TABLE ZgoodDailyOrder (
ZmoneyDailyOrder_ID INTEGER,
SgoodsTotal_ID INTEGER,
Dorder_ID INTEGER NOT NULL,
CONSTRAINT PK_ZgoodDailyOrder24 PRIMARY KEY (Dorder_ID)
);
CREATE TABLE SgainTotal (
Date datetime NOT NULL,
GoodOrderID INTEGER NOT NULL,
OrderId INTEGER NOT NULL,
SgainTotal_ID INTEGER NOT NULL,
TotalCash SMALLINT NOT NULL,
CONSTRAINT PK_SgainTotal18 PRIMARY KEY (SgainTotal_ID)
);
CREATE TABLE CstorageInfo (
GoodPlace VARCHAR ( 255 ) NOT NULL,
Dorder_ID INTEGER NOT NULL,
CinStorage_Dorder_ID INTEGER NOT NULL,
CONSTRAINT PK_CstorageInfo19 PRIMARY KEY (Dorder_ID)
);
CREATE TABLE CinStorage (
InStorageTime SMALLINT NOT NULL,
CpickUpGoodsOrder_ID INTEGER,
Dorder_ID INTEGER NOT NULL,
CONSTRAINT PK_CinStorage20 PRIMARY KEY (Dorder_ID)
);
CREATE TABLE CprepareOrder (
Dorder_ID INTEGER NOT NULL,
CONSTRAINT PK_CprepareOrder22 PRIMARY KEY (Dorder_ID)
);
CREATE TABLE ZmoneyDailyOrder (
OrderId INTEGER NOT NULL,
TotalCash DOUBLE PRECISION NOT NULL,
Date datetime NOT NULL,
GoodOrderId INTEGER NOT NULL,
ZmoneyDailyOrder_ID INTEGER NOT NULL,
SgainTotal_ID INTEGER,
CONSTRAINT PK_ZmoneyDailyOrder14 PRIMARY KEY (ZmoneyDailyOrder_ID)
);
CREATE TABLE SpayforOrder (
Dorder_ID INTEGER NOT NULL,
CONSTRAINT PK_SpayforOrder25 PRIMARY KEY (Dorder_ID)
);
CREATE TABLE ZgatheringOrder (
PayForm VARCHAR ( 255 ) NOT NULL,
CounterID INTEGER NOT NULL,
ZmoneyDailyOrder_ID INTEGER,
Dorder_ID INTEGER NOT NULL,
CONSTRAINT PK_ZgatheringOrder23 PRIMARY KEY (Dorder_ID)
);
CREATE TABLE SgoodsTotal (
OrderId INTEGER NOT NULL,
GoodName VARCHAR ( 255 ) NOT NULL,
GoodTotal INTEGER NOT NULL,
SgainTotalID INTEGER NOT NULL,
GoodPrice DOUBLE PRECISION NOT NULL,
SgoodsTotal_ID INTEGER NOT NULL,
CONSTRAINT PK_SgoodsTotal17 PRIMARY KEY (SgoodsTotal_ID)
);
CREATE TABLE DorderItem (
ItemID INTEGER NOT NULL,
ItemUnit VARCHAR ( 255 ) NOT NULL,
ItemNumber INTEGER NOT NULL,
ItemPrise DOUBLE PRECISION NOT NULL,
ItemTabloid VARCHAR ( 255 ) NOT NULL,
DorderItem_ID INTEGER NOT NULL,
Dorder_ID INTEGER NOT NULL,
CONSTRAINT PK_DorderItem5 PRIMARY KEY (DorderItem_ID)
);
CREATE TABLE CpickUpGoodsOrder (
PickUpID SMALLINT NOT NULL,
PickUpObject VARCHAR ( 255 ) NOT NULL,
PickUpTime datetime NOT NULL,
PickUpGoodName VARCHAR ( 255 ) NOT NULL,
PickUpGoodPrice DOUBLE PRECISION NOT NULL,
PickUpMoney DOUBLE PRECISION NOT NULL,
CpickUpGoodsOrder_ID INTEGER NOT NULL,
CONSTRAINT PK_CpickUpGoodsOrder10 PRIMARY KEY (CpickUpGoodsOrder_ID)
);
CREATE INDEX TC_ZgoodDailyOrder13 ON ZgoodDailyOrder (ZmoneyDailyOrder_ID);
CREATE INDEX TC_ZgoodDailyOrder15 ON ZgoodDailyOrder (SgoodsTotal_ID);
CREATE INDEX TC_CinStorage9 ON CinStorage (CpickUpGoodsOrder_ID);
CREATE INDEX TC_ZmoneyDailyOrder17 ON ZmoneyDailyOrder (SgainTotal_ID);
CREATE INDEX TC_ZgatheringOrder11 ON ZgatheringOrder (ZmoneyDailyOrder_ID);
CREATE INDEX TC_DorderItem5 ON DorderItem (Dorder_ID);
ALTER TABLE CcheckOrder ADD CONSTRAINT FK_CcheckOrder12 FOREIGN KEY (Dorder_ID) REFERENCES Dorder (Dorder_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE ZgoodDailyOrder ADD CONSTRAINT FK_ZgoodDailyOrder6 FOREIGN KEY (ZmoneyDailyOrder_ID) REFERENCES ZmoneyDailyOrder (ZmoneyDailyOrder_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE ZgoodDailyOrder ADD CONSTRAINT FK_ZgoodDailyOrder15 FOREIGN KEY (Dorder_ID) REFERENCES CinStorage (Dorder_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE ZgoodDailyOrder ADD CONSTRAINT FK_ZgoodDailyOrder7 FOREIGN KEY (SgoodsTotal_ID) REFERENCES SgoodsTotal (SgoodsTotal_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE CstorageInfo ADD CONSTRAINT FK_CstorageInfo10 FOREIGN KEY (CinStorage_Dorder_ID) REFERENCES CinStorage (Dorder_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE CstorageInfo ADD CONSTRAINT FK_CstorageInfo9 FOREIGN KEY (Dorder_ID) REFERENCES Dorder (Dorder_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE CinStorage ADD CONSTRAINT FK_CinStorage4 FOREIGN KEY (CpickUpGoodsOrder_ID) REFERENCES CpickUpGoodsOrder (CpickUpGoodsOrder_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE CinStorage ADD CONSTRAINT FK_CinStorage11 FOREIGN KEY (Dorder_ID) REFERENCES Dorder (Dorder_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE CprepareOrder ADD CONSTRAINT FK_CprepareOrder13 FOREIGN KEY (Dorder_ID) REFERENCES Dorder (Dorder_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE ZmoneyDailyOrder ADD CONSTRAINT FK_ZmoneyDailyOrder8 FOREIGN KEY (SgainTotal_ID) REFERENCES SgainTotal (SgainTotal_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE SpayforOrder ADD CONSTRAINT FK_SpayforOrder16 FOREIGN KEY (Dorder_ID) REFERENCES ZgatheringOrder (Dorder_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE ZgatheringOrder ADD CONSTRAINT FK_ZgatheringOrder14 FOREIGN KEY (Dorder_ID) REFERENCES Dorder (Dorder_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE ZgatheringOrder ADD CONSTRAINT FK_ZgatheringOrder5 FOREIGN KEY (ZmoneyDailyOrder_ID) REFERENCES ZmoneyDailyOrder (ZmoneyDailyOrder_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE DorderItem ADD CONSTRAINT FK_DorderItem2 FOREIGN KEY (Dorder_ID) REFERENCES Dorder (Dorder_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
|