======================================
SIR EDWARD KELLY DATABASE CREATION SQL
======================================

==========================================================================
Step 1: Creating the PurchaseContract, ContractItem and ShippingSheet Tables
==========================================================================

DROP TABLE PurchaseContract;

CREATE TABLE PurchaseContract
(ContractNo            VARCHAR(10) NOT NULL,
 ContractDate          DATE,
 ShippingDate          VARCHAR(25),
 Lens                  VARCHAR(40),
 UnitPrice             INTEGER,
 CurrencyRestrictions  VARCHAR(30),
 Description           VARCHAR(150),
 PRIMARY KEY (ContractNo));

DROP TABLE ContractItem;

CREATE TABLE ContractItem
(ContractNo            VARCHAR(10) NOT NULL,
 BillOfLadingNo        INTEGER NOT NULL,
 ShipmentDate          DATE,
 Shipment              VARCHAR(25),
 Comments              VARCHAR(30),
 PRIMARY KEY (ContractNo, BillOfLadingNo),
 FOREIGN KEY (ContractNo) REFERENCES PurchaseContract);

DROP TABLE ShippingSheet;

CREATE TABLE ShippingSheet
(ShippingSheetNo       INTEGER NOT NULL,
 ContractNo            VARCHAR(10) NOT NULL,
 Vessel                VARCHAR(30),
 ShippingAgent         VARCHAR(30),
 CustomsAgent          VARCHAR(30),
 TotalVolume           NUMERIC(8,3),
 DateShipped           DATE,
 Dock                  VARCHAR(30),        
 ExQuayPeriod          INTEGER,
 PortOfShipment        VARCHAR(30),        
 FreightCharge         INTEGER,
 Insurance             VARCHAR(10),
 ExchangeRate          VARCHAR(10),
 DateArrived           DATE,
 Berth                 VARCHAR(30),        
 ExQuayRate            INTEGER,
 PRIMARY KEY (ShippingSheetNo),
 FOREIGN KEY (ContractNo) REFERENCES PurchaseContract);


==========================================================================
Step 2: Inserting Data into the Tables
The data includes two contracts, one of which is the data from the 
Sir Edward Kelly example documents
==========================================================================

INSERT INTO PurchaseContract VALUES
('R28','01-JAN-00','April May 2000', 
'2 metres and longer. Truck bundled', 90,'$A +/- 5%. On Contract',
'Sixths and betterWhitewood including about 10% redwood, free from soft rot and practically free from worm and wormhole');

INSERT INTO PurchaseContract VALUES
('R32','01-FEB-00','June 2000', 
'1.5 metres and longer. Boxed', 120,'$A +/- 3%. On Contract',
'Sixths and better Softwood including about 5% hardwood, guaranteed free from worm and wormhole');

INSERT INTO ContractItem VALUES
('R28',519,'2000-04-01','19 X 100 30 M3', '');
INSERT INTO ContractItem VALUES
('R28',520,'2000-04-01','19 X 100 30 M3', '');
INSERT INTO ContractItem VALUES
('R28',521,'2000-04-01','19 X 100 30 M3', '');
INSERT INTO ContractItem VALUES
('R28',522,'2000-04-01','19 X 125 40 M3', '');
INSERT INTO ContractItem VALUES
('R28',523,'2000-04-01','19 X 125 40 M3', '');
INSERT INTO ContractItem VALUES
('R28',524,'2000-04-01','19 X 125 40 M3', '');
INSERT INTO ContractItem VALUES
('R28',525,'2000-04-01','19 X 150 30 M3', '');
INSERT INTO ContractItem VALUES
('R28',526,'2000-04-01','19 X 150 30 M3', '');
INSERT INTO ContractItem VALUES
('R28',527,'2000-04-01','19 X 150 30 M3', '');
INSERT INTO ContractItem VALUES
('R28',528,'2000-05-01','19 X 100 40 M3', '');
INSERT INTO ContractItem VALUES
('R28',529,'2000-05-01','19 X 100 40 M3', '');
INSERT INTO ContractItem VALUES
('R28',530,'2000-05-01','19 X 125 40 M3', '');
INSERT INTO ContractItem VALUES
('R28',531,'2000-05-01','19 X 125 40 M3', '');
INSERT INTO ContractItem VALUES
('R28',532,'2000-05-01','19 X 150 30 M3', '');
INSERT INTO ContractItem VALUES
('R28',533,'2000-05-01','19 X 150 30 M3', '');

INSERT INTO ContractItem VALUES
('R32',610,'2000-06-21','15 X 80 20 M3', '');
INSERT INTO ContractItem VALUES
('R32',611,'2000-06-21','15 X 80 20 M3', '');
INSERT INTO ContractItem VALUES
('R32',612,'2000-06-21','15 X 80 20 M3', '');
INSERT INTO ContractItem VALUES
('R32',613,'2000-06-21','15 X 100 25 M3', '');

INSERT INTO ShippingSheet VALUES
(1193,'R28','Anna Knuppel','Hawkeye and Radar','Limbo and Co',
310.613, '2000-04-13', 'Sydney', 5, 'Leningrad', 0,'OK', '+/- 3%', 
'2000-05-18', 'Mulberry Pier',5);