Week 4 – Transact-SQL Implementation DDL

Week 4 – Transact-SQL Implementation
DDL (Data Definition Language)

CREATE TABLE Shipping (
TrackingID varchar(50) not null,
OrderID int,
ShippingDate date,
ProductETA date,
ShippingCarrier varchar(100),
ShippingFee money,
ShippingStatus varchar(100),

We Will Write a Custom Essay Specifically
For You For Only $13.90/page!


order now

PRIMARY KEY (TrackingID)
);

CREATE INDEX FK ON Shipping (OrderID);

CREATE TABLE Purchs_Details (
PO_ Number int not null,
IngredientID int not null,
Qty int,
Price money,
Total money,
TrackingNo varchar(50),
DateRecvd date
);

CREATE INDEX FK ON Purchs_Details (PO_ Number, IngredientID);

CREATE TABLE Inventory (
IngredientID int identity (1,1) not null,
MaxInvLevel int not null,
UnitsinStock numeric(30) not null,
UnitsonOrder numeric(30) not null,
Re_OrderLevel int,);

PRIMARY KEY (IngredientID)
);

CREATE TABLE Vendors (
VendorID int identity (1,1) not null,
IngredientID int not null,
CompanyName varchar(150),
ContactName varchar(150),
ContactPhone varchar(20),
ContactEmail varchar(50),
VendorAddress varchar(250),
VendorCity varchar(100),
VendorState varchar(5),
VendorZipCode int,
NetTerm int,
PRIMARY KEY (VendorID)
);
CREATE INDEX FK ON Vendors (IngredientID);

CREATE TABLE Customers (
CustomerID int identity (1,1) not null,
CustFirstName varchar(150),
CustLastName varchar(150),
CustStreetAddress varchar(250),
CustCity varchar(100),
CustState varchar(5),
CustZipCode numeric(10),
CustPhonenumber numeric(20),
CustE-mail varchar(100),
Customerspecneeds varchar(250),
PRIMARY KEY (CustomerID)
);

CREATE TABLE Purchases (
PONumber int identity (1,1) not null,
IngredientID int not null,
VendorID int not null,
PurchaseDate date not null,
ETA date,
Priority int,
PurchaseTotal money,
PRIMARY KEY (PONumber)
);

CREATE INDEX FK ON Purchases (VendorID, IngredientID);

CREATE TABLE Products (
ProductID int identity (1,1) not null,
ProductDescription varchar(100),
Category varchar(50) not null,
Price money,
ManufactureDate date not null,
ExpirationDate date not null,
ProductIngredients varchar(150) not null,
PRIMARY KEY (ProductID)
);

CREATE TABLE Order Details (
OrderID int not null,
ProductID int not null,
TrackingID varchar(50) not null,
Quantity int,
Price money,
ShippingDate date,
ProjectedMnftDate date,

);

CREATE INDEX FK ON Order Details (OrderID, ProductID, TrackingID);

CREATE TABLE Orders (
OrderID int identity (1,1) not null,
CustomerID int not null,
ProductID int not null,
Date date not null,
Time time not null,
OrderTotal money,
PRIMARY KEY (OrderID)
);

CREATE INDEX FK ON Orders (CustomerID, ProductID);

T-SQL and DML
SELECT statements that identify which products (ingredients) should be purchased based on inventory level constraints:
SELECT * FROM Inventory
WHERE UnitsinStock

x

Hi!
I'm Delia!

Would you like to get a custom essay? How about receiving a customized one?

Check it out