88 lines
2.0 KiB
SQL
88 lines
2.0 KiB
SQL
USE MI;
|
|
|
|
CREATE TABLE EMPLOYEE(
|
|
EmployeeID INT IDENTITY(101,1),
|
|
LastName CHAR(25),
|
|
FirstName CHAR(25),
|
|
Department CHAR(25),
|
|
Position CHAR(25),
|
|
Supervisor INT NOT NULL,
|
|
OfficePhone VARCHAR(32),
|
|
OfficeFax VARCHAR(32),
|
|
EmailAddress Char(50),
|
|
PRIMARY KEY(EmployeeID)
|
|
);
|
|
|
|
CREATE TABLE STORE(
|
|
StoreID INT IDENTITY(1000,50),
|
|
StoreName CHAR(25),
|
|
City CHAR(25),
|
|
Country CHAR(25),
|
|
Phone VARCHAR(32),
|
|
Fax VARCHAR(32),
|
|
EmailAddress Char(50),
|
|
Contact CHAR(25),
|
|
PRIMARY KEY(StoreID)
|
|
);
|
|
|
|
CREATE TABLE PURCHASE_ITEM(
|
|
PurchaseItemID INT IDENTITY(500,5),
|
|
StoreID INT FOREIGN KEY REFERENCES STORE(StoreID),
|
|
PurchasingAgentID INT FOREIGN KEY REFERENCES EMPLOYEE(EmployeeID),
|
|
PurchaseDate DATE,
|
|
ItemDescription CHAR(25),
|
|
Category CHAR(25),
|
|
PriceUSD Char(25),
|
|
PRIMARY KEY(PurchaseItemID)
|
|
);
|
|
|
|
CREATE TABLE SHIPPER(
|
|
ShipperID INT IDENTITY(1,1),
|
|
ShipperName CHAR(25),
|
|
Phone VARCHAR(32),
|
|
Fax VARCHAR(32),
|
|
EmailAddress CHAR(50),
|
|
Contact CHAR(25),
|
|
PRIMARY KEY(ShipperID)
|
|
);
|
|
|
|
CREATE TABLE SHIPMENT(
|
|
ShipmentID INT IDENTITY(100,1),
|
|
ShipperID INT FOREIGN KEY REFERENCES SHIPPER(ShipperID),
|
|
PurchasingAgentID INT FOREIGN KEY REFERENCES EMPLOYEE(EmployeeID),
|
|
ShipperInvoiceNumber INT,
|
|
Origin CHAR(25),
|
|
Destination CHAR(25),
|
|
ScheduledDepartureDate DATE,
|
|
ActualDepartureDate DATE,
|
|
EstimatedArrivalDate DATE,
|
|
PRIMARY KEY(ShipmentID)
|
|
);
|
|
|
|
CREATE TABLE SHIPMENT_ITEM(
|
|
ShipmentID INT FOREIGN KEY REFERENCES SHIPMENT(ShipmentID),
|
|
ShipmentItemID INT,
|
|
PurchaseItemID INT FOREIGN KEY REFERENCES PURCHASE_ITEM(PurchaseItemID),
|
|
InsuredValue INT,
|
|
PRIMARY KEY(ShipmentID, ShipmentItemID)
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE SHIPMENT_RECEIPT(
|
|
ReceiptNumber INT IDENTITY(200001,1),
|
|
ShipmentID INT FOREIGN KEY REFERENCES SHIPMENT(ShipmentID),
|
|
PurchaseItemID INT FOREIGN KEY REFERENCES PURCHASE_ITEM(PurchaseItemID),
|
|
ReceivingAgentID INT FOREIGN KEY REFERENCES EMPLOYEE(EmployeeID),
|
|
ReceiptDate DATE,
|
|
ReceiptTime TIME,
|
|
ReceiptQuantity INT,
|
|
isReceivedUndamaged BIT,
|
|
DamageNotes CHAR(50),
|
|
PRIMARY KEY(ReceiptNumber)
|
|
);
|
|
|
|
/* *** CS7-I *** */
|
|
/*UPDATE SHIPMENT SET InsuredValue = PURCHASE_ITEM.PriceUSD */
|
|
|