CREATE TABLE TblCustomer(
CustomerID	char(5)		PRIMARY KEY,
LastName	varchar(30)	NOT NULL,
FirstName	varchar(20),
Address		varchar(30)	NOT NULL,
City		varchar(20)	NOT NULL,
State		char(2)		NOT NULL,
Zip		varchar(12)	NOT NULL,
Country		varchar(15),
FirstBuyDate	datetime,
Email		varchar(60),
Phone		char(15)	NOT NULL
);


CREATE TABLE TblOrder(
OrderID		char(6)		PRIMARY KEY,
CustomerID	char(5)		NOT NULL,
OrderDate	datetime	NOT NULL,
DiscountCode	char(2)		CHECK(DiscountCode in('02','03','04','06','08','10','A1','B3')),
CreditCode	char(3)		NOT NULL,
ShipName	varchar(30),
ShipAddress	varchar(30),
ShipZip		varchar(12),
ShipCountry	varchar(30),
ShipPhone	char(15),
CONSTRAINT TblCustomer_fk FOREIGN KEY(CustomerID) REFERENCES TblCustomer(CustomerID)
);


CREATE TABLE tblItem(
ItemID		char(6)	PRIMARY KEY,
Description	varchar(300),
ListPrice	money	CHECK(ListPrice > 5)
);


CREATE TABLE tblOrderLine(
OrderID		char(6),
ItemID		char(6),
Quantity	int	CHECK(Quantity > 0),
ListPrice	money	CHECK(ListPrice > 0),
CONSTRAINT tblOrderLine_pk PRIMARY KEY(OrderID, ItemID),
CONSTRAINT TblOrder_fk FOREIGN KEY(OrderID) REFERENCES TblOrder(OrderID),
CONSTRAINT TblItem_fk FOREIGN KEY(ItemID) REFERENCES TblItem(ItemID)
);

CREATE TABLE tblItemCostHistory(
ItemID		char(6),
LastCostDate	datetime	NOT NULL,
LastCost	money		NOT NULL,
CONSTRAINT tblItemCostHistory_pk PRIMARY KEY(ItemID, LastCostDate),
CONSTRAINT TblItemCost_fk FOREIGN KEY(ItemID) REFERENCES TblItem(ItemID)
);

CREATE TABLE TblItemLocation(
ItemID		char(6),
LocationID	char(2),
QtyOnHand	int,
CONSTRAINT TblItemLocation_pk PRIMARY KEY(ItemID, LocationID),
CONSTRAINT TblItemLocation_fk FOREIGN KEY(ItemID) REFERENCES TblItem(ItemID)
);

CREATE TABLE TblShipLine(
DateShipped	datetime,
OrderID		char(6),
ItemID		char(6),
LocationID	char(2),
QtyShipped	int	NOT NULL,
MethodShipped	varchar(30) NOT NULL,
CONSTRAINT TblShipLine_pk PRIMARY KEY(DateShipped, OrderID, ItemID, LocationID),
CONSTRAINT TblShipLine1_fk FOREIGN KEY(OrderID, ItemID) REFERENCES TblOrderLine(OrderID, ItemID),
CONSTRAINT TblShipLine2_fk FOREIGN KEY(LocationID, ItemID) REFERENCES TblItemLocation(LocationID, ItemID)
);

// in CONSTRAINT TblShipLine2_fk FOREIGN KEY(LocationID, ItemID) REFERENCES TblItemLocation(LocationID, ItemID)
didn't work, BUT
CONSTRAINT TblShipLine2_fk FOREIGN KEY(ItemID, LocationID) REFERENCES TblItemLocation(ItemID, LocationID)
worked


INSERT INTO tblItem VALUES('A23441', 'New York City Monopoly Game Collector’s Edition', '29.95');

INSERT INTO tblItem VALUES
('B11111', 'New', '19.95');


A23441	New York City Monopoly Game Collector’s Edition	29.95

INSERT INTO	tblItem	VALUES('	A23441	','	New York City Monopoly Game Collector’s Edition	','	29.95	');