Database
Kaplan University
Dr. Michael Collins
November 27, 2012 Part 1
Part 2
Use screenimages

--Creates the Agents table create table Agent ( AgentID INT not null, FirstName VARCHAR(50) not null, LastName VARCHAR(50) not null, Agency VARCHAR(75) not null, OfficeLocation VARCHAR(50) not null, Phone VARCHAR(20) not null, Email VARCHAR(60) not null, constraint pk_agent primary key (AgentID) );
GO
Use screenimages

--Creates the Order table create table Orders ( OrderID INT not null, AgentID INT not null, OrderDate DATE not null, ShipDate DATE not null, constraint pk_orders primary key (OrderID), constraint fk_agent1 foreign key (AgentID) references Agent (AgentID), );
GO

Use screenimages

--Creates the Products table create table Products ( ProductID INT not null, Photo VARBINARY(max) null, Descript VARCHAR(max) not null, Price MONEY not null, constraint pk_Products primary key (ProductID) );
GO

Use screenimages

--Creates the Inventory table create table Inventory ( InventoryID INT not null, ProductID INT not null, InventoryQty CHAR(5) not null, ReorderPoint CHAR(5) not null, constraint pk_Inventory primary key (InventoryID), constraint fk_Products2 foreign key (ProductID) references Products (ProductID) );
GO

Use screenimages

--Creates the associate OrderLine table create table OrderLine ( OrderID INT not null IDENTITY, ProductID INT not null, Quantity CHAR(5) not null, constraint pk_OrderLine primary key (OrderID, ProductID), constraint fk_OrderID foreign key (OrderID) references Orders (OrderID), constraint fk_ProductID foreign key (ProductID) references Products (ProductID) );
GO

Part 3A
Insert Agent (AgentID, FirstName, LastName, Agency, OfficeLocation, Phone, Email)
Values
(1478, 'Tim', 'Waldron', 'Iowa Realty', 'Jordan Creek',