createglobaltoyzobjects.sql

程序源码~~关于SQL的源码

USE GlobalToyz
go

/* creating required data types */
execute sp_addtype id      ,'char(6)' ,'NOT NULL'
raiserror('Now at the Create Table section ....',0,1)
Go
raiserror('Creating Table Category....',0,1)
create table Category
(
	cCategoryId	char(3) constraint ct_pk primary key,
	cCategory	char(20) not null,
	vDescription	varchar(100)
)
go
raiserror('Creating Table Wrapper....',0,1)
create Table Wrapper
(
	cWrapperId 	char(3)	constraint w_id primary key clustered,
	vDescription	varchar(20),
	mWrapperRate	money not null,
	imPhoto		image null,
	vWrapperImgPath varchar(50) null

)
go
raiserror('Creating Table ToyBrand....',0,1)
create table ToyBrand
(
	cBrandId	char(3) constraint TB_pk primary key,
	cBrandName	char(20) not null,
)
go
/***************************/
raiserror('Creating Table Country....',0,1)
create table Country
(
	cCountryId	char(3) constraint c_pk primary key,
	cCountry		char(25) not null,
)
go
raiserror('Creating Table ShippingMode....',0,1)
create table ShippingMode
(
	cModeId		char(2) constraint spm_pk primary key,
	cMode 		char(25) not null,
	iMaxDelDays	int,
)
go
raiserror('Creating Table ShippingRate....',0,1)
create table ShippingRate
(
	cCountryID	char(3) references Country(cCountryId) ,
	cModeId 		char(2) references  ShippingMode(cModeId),
	mRatePerPound	money not null,
	constraint SR_PRK primary key(cCountryID,cModeId)
/* need to create composite primary  key */
)
raiserror('Creating Table Shopper....',0,1)
create table Shopper
(
	cShopperId 	char(6)	constraint s_id primary key CLUSTERED,
	cPassword 	char(10) not null,
	
	vFirstName 	varchar(20) not null,
	vLastName 	varchar(20) not null,
	vEmailId	varchar(40) not null,
	vAddress 	varchar(40) not null,
	cCity 		char(15) not null,
	cState 		char(15) not null,
	cCountryId 	char(3) references Country(cCountryId),
	cZipCode	char(10), /*  check(cZipCode like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'), */
	cPhone 		char(15) not null ,
	cCreditCardNo 	char(16) not null,
	vCreditCardType 	varchar(15) not null,
	dExpiryDate 	datetime 
)
go

raiserror('Creating Table Toys....',0,1)
/* toys table */
create table Toys
(
	cToyId		char(6)  check(cToyId like('[0-9][0-9][0-9][0-9][0-9][0-9]') )
				constraint t_id primary key clustered,
				
	vToyName	varchar(20) not null,
	vToyDescription	varchar(250),
	cCategoryId	char(3) references Category(cCategoryId) ,
	mToyRate	money not null,
	cBrandId	char(3)references ToyBrand(cBrandId),
	imPhoto 	image,
	siToyQoh	smallint not null,
	siLowerAge	smallint not null,
	siUpperAge	smallint not null,
	siToyWeight	smallint,
	vToyImgPath	varchar(50) null

)
go
/*                         */
/* ShoppingCart */
raiserror('Creating Table ShoppingCart....',0,1)
create table ShoppingCart
(
	cCartId 	char(6) not null, 
	cToyId		char(6)REFERENCES Toys(cToyId),/* foreign key to Toys table */
	siQty		smallint not null,
	constraint SCHP_PK primary key(cCartId,cToyId)
)
go

/***********************/

/* Order */
raiserror('Creating Table Order....',0,1)
create table Orders
(
	cOrderNo 	char(6) constraint CO_PK Primary key, 
	dOrderDate 	datetime not null,
	cCartId		char(6) not null, 
	cShopperId	char(6) not null references Shopper(cShopperId),
	cShippingModeId	char(2) null references ShippingMode(cModeId),
	mShippingCharges money  null,
	mGiftWrapCharges	money null,
	cOrderProcessed	char null ,
	mTotalCost	money  null,
	dExpDelDate	DateTime null
)
go


/* OrderDetail table */

raiserror('Creating Table OrderDetails....',0,1)
create table OrderDetail
(
	cOrderNo 	char(6) references Orders(cOrderNo),
	cToyId 		char(6)  references toys(cToyId),
	siQty 		smallint  not null,
	cGiftWrap	char null, 
	cWrapperId	char(3) references Wrapper(cWrapperId) null,
	vMessage	varchar(256) null,
	mToyCost        money null,
		constraint z_key primary key(cOrderNo,cToyId)
)
go

/* shipping mode */

/* Shipment */
raiserror('Creating  Shippment....',0,1)
create table Shipment
(
	cOrderNo		char(6)  REFERENCES Orders(cOrderNo) constraint SHP_PK primary key,  
	dShipmentDate	datetime null, /* cannot be before order date */
	cDeliveryStatus	char null, 
	dActualDeliveryDate datetime null
)
go
/* Recipient table */
raiserror('Creating Table Recipient....',0,1)
create table Recipient
(
	cOrderNo		char(6) REFERENCES Orders(cOrderNo) constraint RCP_PK primary key, /* foreign key to order table */
	vFirstName 		varchar(20) not null,
	vLastName 		varchar(20) not null,
	vAddress		varchar(20) not null,
	cCity 		        char(15) not null,
	cState			char(15) not null,
	cCountryId	char(3) references Country(cCountryId),
	cZipCode		char(10) check(cZipCode like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),