/*GrowFlower Farms - DDL Schema and DML Population*/
/*Nick Maffattone and Samantha Yeats*/
/*November 12 2013*/
/*------------------------------------------------------------------------------
-- Drop Section
-- Necessary to be able to run the script multiple times
------------------------------------------------------------------------------*/
/* "DROP" Foreign Key Constraints --------------------------------------------*/
ALTER TABLE Contacts
DROP FOREIGN KEY fk_vendor_contact;
ALTER TABLE Contacts
DROP FOREIGN KEY fk_farmer_contact_vend;
ALTER TABLE Field
DROP FOREIGN KEY fk_field_soil;
ALTER TABLE Field
DROP FOREIGN KEY fk_current_plant;
ALTER TABLE Harvest
DROP FOREIGN KEY fk_field_location;
ALTER TABLE Harvest
DROP FOREIGN KEY fk_harvested_plant;
ALTER TABLE Harvest
DROP FOREIGN KEY fk_distributor_buyer;
ALTER TABLE IsResponsibleFor
DROP FOREIGN KEY fk_farmer_responsible;
ALTER TABLE IsResponsibleFor
DROP FOREIGN KEY fk_field_responsibility;
ALTER TABLE Material_Bundle
DROP FOREIGN KEY fk_material_vendor;
ALTER TABLE Material_Bundle
DROP FOREIGN KEY fk_material_provided;
ALTER TABLE Seed_Bundle
DROP FOREIGN KEY fk_seed_vendor;
ALTER TABLE Seed_Bundle
DROP FOREIGN KEY fk_seed_provided;
/* DROP Tables ---------------------------------------------------------------*/
DROP TABLE Contacts;
DROP TABLE Distributor;
DROP TABLE Farmer;
DROP TABLE Field;
DROP TABLE Harvest;
DROP TABLE IsResponsibleFor;
DROP TABLE Material;
DROP TABLE Material_Bundle;
DROP TABLE Seed;
DROP TABLE Seed_Bundle;
DROP TABLE Soil;
DROP TABLE Vendor;
/* CREATE Tables -------------------------------------------------------------*/
CREATE TABLE Contacts(
VendorID INTEGER NOT NULL,
FarmerID INTEGER NOT NULL,
CONSTRAINT pk_contacts PRIMARY KEY (VendorID, FarmerID)
);
CREATE TABLE Distributor(
DistributorID INTEGER NOT NULL,
FarmerContact INTEGER NOT NULL,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(100) NOT NULL,
PhoneNum VARCHAR(20) NOT NULL,
CONSTRAINT pk_distributor PRIMARY KEY (DistributorID)
);
CREATE TABLE Farmer(
FarmerID INTEGER NOT NULL,
Name VARCHAR(50) NOT NULL,
PhoneNum VARCHAR(20) NOT NULL,
Address VARCHAR(100) NOT NULL,
Salary INTEGER NOT NULL,
HireDate DATE NOT NULL,
CONSTRAINT pk_farmer PRIMARY KEY (FarmerID)
);
CREATE TABLE Field (
Location VARCHAR(15) NOT NULL,
SoilType INTEGER NOT NULL,
CurrentPlant VARCHAR(50) NOT NULL,
DatePlanted DATE NULL,
CONSTRAINT pk_field PRIMARY KEY (Location)
);
CREATE TABLE Harvest (
HarvestDate DATE NOT NULL,
FieldLocation VARCHAR(15) NOT NULL,
PlantType VARCHAR(50) NOT NULL,
PlantCount INTEGER NOT NULL,
ExpirationDate DATE NULL,
PurchaseDate DATE NULL,
PriceSold FLOAT NULL,
DistributorSold INTEGER,
ReturnDate DATE NULL,
CONSTRAINT pk_harvest PRIMARY KEY (HarvestDate, FieldLocation)
);
CREATE TABLE IsResponsibleFor (
FarmerID INTEGER NOT NULL,
FieldLocation VARCHAR(15) NOT NULL,
CONSTRAINT pk_is_responsible_for PRIMARY KEY (FarmerID, FieldLocation)
);
CREATE TABLE Material(
MaterialType VARCHAR(50) NOT NULL,
CONSTRAINT pk_material PRIMARY KEY (MaterialType)
);
CREATE TABLE Material_Bundle(
VendorID INTEGER NOT NULL,
DatePurchased DATE NOT NULL,
MaterialType VARCHAR(50) NOT NULL,
Quantity INTEGER NOT NULL,
Price FLOAT NOT NULL,
ReturnDate DATE NULL,
CONSTRAINT pk_material_bundle PRIMARY KEY (VendorID, DatePurchased, MaterialType)
);
CREATE TABLE Seed(
SeedType VARCHAR(50) NOT NULL,
CONSTRAINT pk_seed PRIMARY KEY (SeedType)
);
CREATE TABLE Seed_Bundle(
VendorID INTEGER NOT NULL,
DatePurchased DATE NOT NULL,
SeedType VARCHAR(50) NOT NULL,
Quantity INTEGER NOT NULL,
Price FLOAT NOT NULL,
ReturnDate DATE NULL,
CONSTRAINT pk_seed_bundle PRIMARY KEY (VendorID, DatePurchased, SeedType)
);
CREATE TABLE Soil(
TypeID INTEGER NOT NULL,
SoilType VARCHAR(50) NOT NULL,
CONSTRAINT pk_soil PRIMARY KEY (TypeID)
);
CREATE TABLE Vendor(
VendorID INTEGER NOT NULL,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(100) NOT NULL,
PhoneNum VARCHAR(20) NOT NULL,
CONSTRAINT pk_vendor PRIMARY KEY (VendorID)
);
/* "CREATE" Foreign Key Constraints ------------------------------------------*/
ALTER TABLE Contacts
ADD CONSTRAINT fk_vendor_contact
FOREIGN KEY (VendorID)
REFERENCES Vendor (VendorID);
ALTER TABLE Contacts
ADD CONSTRAINT fk_farmer_contact_vend
FOREIGN KEY (FarmerID)
REFERENCES Farmer (FarmerID);
ALTER TABLE Field
ADD CONSTRAINT fk_field_soil
FOREIGN KEY (SoilType)
REFERENCES Soil (TypeID);
ALTER TABLE Field
ADD CONSTRAINT fk_current_plant
FOREIGN KEY (CurrentPlant)
REFERENCES Seed (SeedType);
ALTER TABLE Harvest
ADD CONSTRAINT fk_field_location
FOREIGN KEY (FieldLocation)
REFERENCES Field (Location);
ALTER TABLE Harvest
ADD CONSTRAINT fk_harvested_plant
FOREIGN KEY (PlantType)
REFERENCES Seed (SeedType);
ALTER TABLE Harvest
ADD CONSTRAINT fk_distributor_buyer
FOREIGN KEY (DistributorSold)
REFERENCES Distributor (DistributorID);
ALTER TABLE IsResponsibleFor
ADD CONSTRAINT fk_farmer_responsible
FOREIGN KEY (FarmerID)
REFERENCES Farmer (FarmerID);
ALTER TABLE IsResponsibleFor
ADD CONSTRAINT fk_field_responsibility
FOREIGN KEY (FieldLocation)
REFERENCES Field (Location);
ALTER TABLE Material_Bundle
ADD CONSTRAINT fk_material_vendor
FOREIGN KEY (VendorID)
REFERENCES Vendor (VendorID);
ALTER TABLE Material_Bundle
ADD CONSTRAINT fk_material_provided
FOREIGN KEY (MaterialType)
REFERENCES Material (MaterialType);
ALTER TABLE Seed_Bundle
ADD CONSTRAINT fk_seed_vendor
FOREIGN KEY (VendorID)
REFERENCES Vendor (VendorID);
ALTER TABLE Seed_Bundle
ADD CONSTRAINT fk_seed_provided
FOREIGN KEY (SeedType)
REFERENCES Seed (SeedType);
/* --------------------------------------------------------------------------*/
/*-- DELETE Section --------------------------------------------------------------
-- Rows in tables with foreign keys must be deleted first. ---------------------
------------------------------------------------------------------------------*/
DELETE FROM Contacts;
DELETE FROM Distributor;
DELETE FROM Farmer;
DELETE FROM Field;
DELETE FROM Harvest;
DELETE FROM IsResponsibleFor;
DELETE FROM Material;
DELETE FROM Material_Bundle;
DELETE FROM Seed;
DELETE FROM Seed_Bundle;
DELETE FROM Soil;
DELETE FROM Vendor;
/*------------------------------------------------------------------------------
-- INSERT Section ------------------------------------------------------------
-- Rows must be inserted into tables with foreign keys last. -------------------
------------------------------------------------------------------------------*/
/*-- Populate Farmer_T ------------------------------------------------------*/
INSERT INTO Farmer (FarmerID, Name, Address, PhoneNum, Salary, HireDate)
VALUES (13, 'Chris Slover', '43 West Millford', '555-8394', 65000, '2004-10-06');
INSERT INTO Farmer (FarmerID, Name, Address, PhoneNum, Salary, HireDate)
VALUES (12, 'Brian Smith', '22 Lincon Park', '555-9384', 70000, '1996-10-08');
INSERT INTO Farmer (FarmerID, Name, Address, PhoneNum, Salary, HireDate)
VALUES (14, 'Nate Litvak', '84 Sunset Grove', '555-2317', 60000, '2008-04-03');
INSERT INTO Farmer (FarmerID, Name, Address, PhoneNum, Salary, HireDate)
VALUES (15, 'Marcus Ford', '32 Sawmil Rd', '555-0976', 58000, '2010-05-23');
INSERT INTO Farmer (FarmerID, Name, Address, PhoneNum, Salary, HireDate)
VALUES (16, 'Daisy Dukes', '28 Highland Blvd', '555-1233', 63000, '2012-05-11');
INSERT INTO Farmer (FarmerID, Name, Address, PhoneNum, Salary, HireDate)
VALUES (17, 'Helen Hay', '3 Turner Way', '555-4195', 70000, '1995-03-19');
/*-- Populate Distributor_T -------------------------------------------------------*/
INSERT INTO Distributor (DistributorID, FarmerContact, Name, Address, PhoneNum)
SELECT 24, F.FarmerID, 'Plants Plants Plants!', '43 Haven Rd', '555-6783'
FROM Farmer AS F
WHERE F.FarmerID = 16;
INSERT INTO Distributor (DistributorID, FarmerContact, Name, Address, PhoneNum)
SELECT 25, F.FarmerID, 'The Outdoor Store', '56 South Orange Ln', '555-6734'
FROM Farmer AS F
WHERE F.FarmerID = 12;
INSERT INTO Distributor (DistributorID, FarmerContact, Name, Address, PhoneNum)
SELECT 26, F.FarmerID, 'Farmhills Farm Stand', '6 Farmhills Rd', '555-9834'
FROM Farmer AS F
WHERE F.FarmerID = 17;
/*-- Populate Vendor --------------------------------------------------*/
INSERT INTO Vendor (VendorID, Name, Address, PhoneNum)
VALUES (53, 'Lauren Wentworth', '5 Fox Hill Rd.', '555-2345');
INSERT INTO Vendor (VendorID, Name, Address, PhoneNum)
VALUES (54, 'george Doughtry', '15 Pleasant Valley Rd.', '555-3456');
INSERT INTO Vendor (VendorID, Name, Address, PhoneNum)
VALUES (55, 'Henrey Mullen', '34 Park Place', '555-9485');
INSERT INTO Vendor (VendorID, Name, Address, PhoneNum)
VALUES (56, 'Richard Young', '12 Regional St.', '555-3849');
/*--Populate Contacts --------------------------------*/
INSERT INTO Contacts (VendorID, FarmerID)
SELECT V.VendorID, F.FarmerID
FROM Vendor V JOIN Farmer F
WHERE V.VendorID = 55 AND F.FarmerID = 14;
INSERT INTO Contacts (VendorID, FarmerID)
SELECT V.VendorID, F.FarmerID
FROM Vendor V JOIN Farmer F
WHERE V.VendorID = 55 AND F.FarmerID = 16;
INSERT INTO Contacts (VendorID, FarmerID)
SELECT V.VendorID, F.FarmerID
FROM Vendor V JOIN Farmer F
WHERE V.VendorID = 56 AND F.FarmerID = 13;
INSERT INTO Contacts (VendorID, FarmerID)
SELECT V.VendorID, F.FarmerID
FROM Vendor V JOIN Farmer F
WHERE V.VendorID = 53 AND F.FarmerID = 12;
INSERT INTO Contacts (VendorID, FarmerID)
SELECT V.VendorID, F.FarmerID
FROM Vendor V JOIN Farmer F
WHERE V.VendorID = 53 AND F.FarmerID = 17;
INSERT INTO Contacts (VendorID, FarmerID)
SELECT V.VendorID, F.FarmerID
FROM Vendor V JOIN Farmer F
WHERE V.VendorID = 54 AND F.FarmerID = 15;
/*--Populate Soil --------------------------------*/
INSERT INTO Soil (TypeID, SoilType)
VALUES (1, 'Sandy');
INSERT INTO Soil (TypeID, SoilType)
VALUES (2, 'Silty');
INSERT INTO Soil (TypeID, SoilType)
VALUES (3, 'Clay');
INSERT INTO Soil (TypeID, SoilType)
VALUES (4, 'Peaty');
INSERT INTO Soil (TypeID, SoilType)
VALUES (5, 'Saline Soil');
/*--Populate Material -----------------------------*/
INSERT INTO Material (MaterialType)
VALUES ('Plow');
INSERT INTO Material (MaterialType)
VALUES ('Tractor');
INSERT INTO Material (MaterialType)
VALUES ('Fertilizer');
INSERT INTO Material (MaterialType)
VALUES ('Pesticide');
INSERT INTO Material (MaterialType)
VALUES ('Gas Tank');
/*--Populate Seed ---------------------------------*/
INSERT INTO Seed (SeedType)
VALUES ('Rose');
INSERT INTO Seed (SeedType)
VALUES ('Daffodil');
INSERT INTO Seed (SeedType)
VALUES ('Sunflower');
INSERT INTO Seed (SeedType)
VALUES ('Poppy');
INSERT INTO Seed (SeedType)
VALUES ('Tulip');
INSERT INTO Seed (SeedType)
VALUES ('Violet');
/*--Populate Field --------------------------------*/
INSERT INTO Field (Location, SoilType, CurrentPlant, DatePlanted)
SELECT '20E30N', Soil.TypeID, Seed.SeedType, '2013-03-11'
FROM Soil JOIN Seed
WHERE Soil.TypeID = 1 AND Seed.SeedType='Sunflower';
INSERT INTO Field (Location, SoilType, CurrentPlant, DatePlanted)
SELECT '20E25S', Soil.TypeID, Seed.SeedType, '2013-02-11'
FROM Soil JOIN Seed
WHERE Soil.TypeID = 1 AND Seed.SeedType='Daffodil';
INSERT INTO Field (Location, SoilType, CurrentPlant, DatePlanted)
SELECT '10W25N', Soil.TypeID, Seed.SeedType, '2013-04-11'
FROM Soil JOIN Seed
WHERE Soil.TypeID = 3 AND Seed.SeedType='Tulip';
INSERT INTO Field (Location, SoilType, CurrentPlant, DatePlanted)
SELECT '5E5S', Soil.TypeID, Seed.SeedType, '2013-05-11'
FROM Soil JOIN Seed
WHERE Soil.TypeID = 5 AND Seed.SeedType='Violet';
INSERT INTO Field (Location, SoilType, CurrentPlant, DatePlanted)
SELECT '30W30S', Soil.TypeID, Seed.SeedType, '2013-06-11'
FROM Soil JOIN Seed
WHERE Soil.TypeID = 2 AND Seed.SeedType='Rose';
/*--- Populate IsResponsibleFor ----------*/
INSERT INTO IsResponsibleFor (FarmerID, FieldLocation)
SELECT Farmer.FarmerID, Field.Location
FROM Farmer JOIN Field
WHERE Farmer.FarmerID = 12 AND Field.Location = '10W25N';
INSERT INTO IsResponsibleFor (FarmerID, FieldLocation)
SELECT Farmer.FarmerID, Field.Location
FROM Farmer JOIN Field
WHERE Farmer.FarmerID = 12 AND Field.Location = '30W30S';
INSERT INTO IsResponsibleFor (FarmerID, FieldLocation)
SELECT Farmer.FarmerID, Field.Location
FROM Farmer JOIN Field
WHERE Farmer.FarmerID = 13 AND Field.Location = '20E30N';
INSERT INTO IsResponsibleFor (FarmerID, FieldLocation)
SELECT Farmer.FarmerID, Field.Location
FROM Farmer JOIN Field
WHERE Farmer.FarmerID = 15 AND Field.Location = '5E5S';
INSERT INTO IsResponsibleFor (FarmerID, FieldLocation)
SELECT Farmer.FarmerID, Field.Location
FROM Farmer JOIN Field
WHERE Farmer.FarmerID = 17 AND Field.Location = '20E25S';
/*----Populate Material_Bundle-------------------------*/
INSERT INTO Material_Bundle (VendorID, DatePurchased, MaterialType, Quantity, Price)
SELECT Vendor.VendorID, '2000-11-12', Material.MaterialType, 1, 10000
FROM Vendor JOIN Material
WHERE Vendor.VendorID = 53 AND Material.MaterialType = 'Plow';
INSERT INTO Material_Bundle (VendorID, DatePurchased, MaterialType, Quantity, Price)
SELECT Vendor.VendorID, '2000-11-12', Material.MaterialType, 1, 5000
FROM Vendor JOIN Material
WHERE Vendor.VendorID = 55 AND Material.MaterialType = 'Tractor';
INSERT INTO Material_Bundle (VendorID, DatePurchased, MaterialType, Quantity, Price)
SELECT Vendor.VendorID, '2000-11-12', Material.MaterialType, 100, 2000
FROM Vendor JOIN Material
WHERE Vendor.VendorID = 56 AND Material.MaterialType = 'Fertilizer';
INSERT INTO Material_Bundle (VendorID, DatePurchased, MaterialType, Quantity, Price)
SELECT Vendor.VendorID, '2000-11-12', Material.MaterialType, 500, 10000
FROM Vendor JOIN Material
WHERE Vendor.VendorID = 55 AND Material.MaterialType = 'Pesticide';
INSERT INTO Material_Bundle (VendorID, DatePurchased, MaterialType, Quantity, Price)
SELECT Vendor.VendorID, '2000-11-12', Material.MaterialType, 5, 25000
FROM Vendor JOIN Material
WHERE Vendor.VendorID = 54 AND Material.MaterialType = 'Gas Tank';
INSERT INTO Material_Bundle (VendorID, DatePurchased, MaterialType, Quantity, Price, ReturnDate)
SELECT Vendor.VendorID, '2012-05-20', Material.MaterialType, 1, 10000, '2012-06-05'
FROM Vendor JOIN Material
WHERE Vendor.VendorID = 53 AND Material.MaterialType = 'Plow';
/*----Populate Seed_Bundle-----------------------------*/
INSERT INTO Seed_Bundle (VendorID, DatePurchased, SeedType, Quantity, Price, ReturnDate)
SELECT Vendor.VendorID, '2011-05-18', Seed.SeedType, 10000, 500, '2011-06-05'
FROM Vendor JOIN Seed
WHERE Vendor.VendorID = 56 AND Seed.SeedType = 'Daffodil';
INSERT INTO Seed_Bundle (VendorID, DatePurchased, SeedType, Quantity, Price)
SELECT Vendor.VendorID, '2011-06-29', Seed.SeedType, 4000, 100
FROM Vendor JOIN Seed
WHERE Vendor.VendorID = 56 AND Seed.SeedType = 'Daffodil';
INSERT INTO Seed_Bundle (VendorID, DatePurchased, SeedType, Quantity, Price)
SELECT Vendor.VendorID, '2012-04-18', Seed.SeedType, 1000, 50
FROM Vendor JOIN Seed
WHERE Vendor.VendorID = 53 AND Seed.SeedType = 'Sunflower';
INSERT INTO Seed_Bundle (VendorID, DatePurchased, SeedType, Quantity, Price)
SELECT Vendor.VendorID, '2012-05-18', Seed.SeedType, 3000, 200
FROM Vendor JOIN Seed
WHERE Vendor.VendorID = 53 AND Seed.SeedType = 'Tulip';
INSERT INTO Seed_Bundle (VendorID, DatePurchased, SeedType, Quantity, Price)
SELECT Vendor.VendorID, '2012-06-18', Seed.SeedType, 20000, 600
FROM Vendor JOIN Seed
WHERE Vendor.VendorID = 55 AND Seed.SeedType = 'Violet';
INSERT INTO Seed_Bundle (VendorID, DatePurchased, SeedType, Quantity, Price)
SELECT Vendor.VendorID, '2012-07-18', Seed.SeedType, 2000, 100
FROM Vendor JOIN Seed
WHERE Vendor.VendorID = 54 AND Seed.SeedType = 'Rose';
INSERT INTO Seed_Bundle (VendorID, DatePurchased, SeedType, Quantity, Price)
SELECT Vendor.VendorID, '2012-08-18', Seed.SeedType, 5000, 450
FROM Vendor JOIN Seed
WHERE Vendor.VendorID = 54 AND Seed.SeedType = 'Poppy';
/*----Populate Harvest --------------------------------*/
INSERT INTO Harvest (HarvestDate, FieldLocation, PlantType, PlantCount, PurchaseDate, DistributorSold, PriceSold)
SELECT '2013-10-11', Field.Location, Seed.SeedType, 150, '2013-10-15', Distributor.DistributorID, 5000
FROM Field JOIN Seed JOIN Distributor
WHERE Field.Location = '5E5S' AND Seed.SeedType = 'Violet' AND Distributor.DistributorID=24;
INSERT INTO Harvest (HarvestDate, FieldLocation, PlantType, PlantCount)
SELECT '2013-10-12', Field.Location, Seed.SeedType, 203
FROM Field JOIN Seed
WHERE Field.Location = '30W30S' AND Seed.SeedType = 'Rose';
INSERT INTO Harvest (HarvestDate, FieldLocation, PlantType, PlantCount, ExpirationDate)
SELECT '2013-10-13', Field.Location, Seed.SeedType, 157, '2014-01-22'
FROM Field JOIN Seed
WHERE Field.Location = '20E30N' AND Seed.SeedType = 'Sunflower';
INSERT INTO Harvest (HarvestDate, FieldLocation, PlantType, PlantCount)
SELECT '2013-10-14', Field.Location, Seed.SeedType, 50
FROM Field JOIN Seed
WHERE Field.Location = '10W25N' AND Seed.SeedType = 'Tulip';
INSERT INTO Harvest (HarvestDate, FieldLocation, PlantType, PlantCount)
SELECT '2013-10-15', Field.Location, Seed.SeedType, 303
FROM Field JOIN Seed
WHERE Field.Location = '20E25S' AND Seed.SeedType = 'Daffodil';
/*Nick Maffattone and Samantha Yeats*/
/*November 12 2013*/
/*------------------------------------------------------------------------------
-- Drop Section
-- Necessary to be able to run the script multiple times
------------------------------------------------------------------------------*/
/* "DROP" Foreign Key Constraints --------------------------------------------*/
ALTER TABLE Contacts
DROP FOREIGN KEY fk_vendor_contact;
ALTER TABLE Contacts
DROP FOREIGN KEY fk_farmer_contact_vend;
ALTER TABLE Field
DROP FOREIGN KEY fk_field_soil;
ALTER TABLE Field
DROP FOREIGN KEY fk_current_plant;
ALTER TABLE Harvest
DROP FOREIGN KEY fk_field_location;
ALTER TABLE Harvest
DROP FOREIGN KEY fk_harvested_plant;
ALTER TABLE Harvest
DROP FOREIGN KEY fk_distributor_buyer;
ALTER TABLE IsResponsibleFor
DROP FOREIGN KEY fk_farmer_responsible;
ALTER TABLE IsResponsibleFor
DROP FOREIGN KEY fk_field_responsibility;
ALTER TABLE Material_Bundle
DROP FOREIGN KEY fk_material_vendor;
ALTER TABLE Material_Bundle
DROP FOREIGN KEY fk_material_provided;
ALTER TABLE Seed_Bundle
DROP FOREIGN KEY fk_seed_vendor;
ALTER TABLE Seed_Bundle
DROP FOREIGN KEY fk_seed_provided;
/* DROP Tables ---------------------------------------------------------------*/
DROP TABLE Contacts;
DROP TABLE Distributor;
DROP TABLE Farmer;
DROP TABLE Field;
DROP TABLE Harvest;
DROP TABLE IsResponsibleFor;
DROP TABLE Material;
DROP TABLE Material_Bundle;
DROP TABLE Seed;
DROP TABLE Seed_Bundle;
DROP TABLE Soil;
DROP TABLE Vendor;
/* CREATE Tables -------------------------------------------------------------*/
CREATE TABLE Contacts(
VendorID INTEGER NOT NULL,
FarmerID INTEGER NOT NULL,
CONSTRAINT pk_contacts PRIMARY KEY (VendorID, FarmerID)
);
CREATE TABLE Distributor(
DistributorID INTEGER NOT NULL,
FarmerContact INTEGER NOT NULL,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(100) NOT NULL,
PhoneNum VARCHAR(20) NOT NULL,
CONSTRAINT pk_distributor PRIMARY KEY (DistributorID)
);
CREATE TABLE Farmer(
FarmerID INTEGER NOT NULL,
Name VARCHAR(50) NOT NULL,
PhoneNum VARCHAR(20) NOT NULL,
Address VARCHAR(100) NOT NULL,
Salary INTEGER NOT NULL,
HireDate DATE NOT NULL,
CONSTRAINT pk_farmer PRIMARY KEY (FarmerID)
);
CREATE TABLE Field (
Location VARCHAR(15) NOT NULL,
SoilType INTEGER NOT NULL,
CurrentPlant VARCHAR(50) NOT NULL,
DatePlanted DATE NULL,
CONSTRAINT pk_field PRIMARY KEY (Location)
);
CREATE TABLE Harvest (
HarvestDate DATE NOT NULL,
FieldLocation VARCHAR(15) NOT NULL,
PlantType VARCHAR(50) NOT NULL,
PlantCount INTEGER NOT NULL,
ExpirationDate DATE NULL,
PurchaseDate DATE NULL,
PriceSold FLOAT NULL,
DistributorSold INTEGER,
ReturnDate DATE NULL,
CONSTRAINT pk_harvest PRIMARY KEY (HarvestDate, FieldLocation)
);
CREATE TABLE IsResponsibleFor (
FarmerID INTEGER NOT NULL,
FieldLocation VARCHAR(15) NOT NULL,
CONSTRAINT pk_is_responsible_for PRIMARY KEY (FarmerID, FieldLocation)
);
CREATE TABLE Material(
MaterialType VARCHAR(50) NOT NULL,
CONSTRAINT pk_material PRIMARY KEY (MaterialType)
);
CREATE TABLE Material_Bundle(
VendorID INTEGER NOT NULL,
DatePurchased DATE NOT NULL,
MaterialType VARCHAR(50) NOT NULL,
Quantity INTEGER NOT NULL,
Price FLOAT NOT NULL,
ReturnDate DATE NULL,
CONSTRAINT pk_material_bundle PRIMARY KEY (VendorID, DatePurchased, MaterialType)
);
CREATE TABLE Seed(
SeedType VARCHAR(50) NOT NULL,
CONSTRAINT pk_seed PRIMARY KEY (SeedType)
);
CREATE TABLE Seed_Bundle(
VendorID INTEGER NOT NULL,
DatePurchased DATE NOT NULL,
SeedType VARCHAR(50) NOT NULL,
Quantity INTEGER NOT NULL,
Price FLOAT NOT NULL,
ReturnDate DATE NULL,
CONSTRAINT pk_seed_bundle PRIMARY KEY (VendorID, DatePurchased, SeedType)
);
CREATE TABLE Soil(
TypeID INTEGER NOT NULL,
SoilType VARCHAR(50) NOT NULL,
CONSTRAINT pk_soil PRIMARY KEY (TypeID)
);
CREATE TABLE Vendor(
VendorID INTEGER NOT NULL,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(100) NOT NULL,
PhoneNum VARCHAR(20) NOT NULL,
CONSTRAINT pk_vendor PRIMARY KEY (VendorID)
);
/* "CREATE" Foreign Key Constraints ------------------------------------------*/
ALTER TABLE Contacts
ADD CONSTRAINT fk_vendor_contact
FOREIGN KEY (VendorID)
REFERENCES Vendor (VendorID);
ALTER TABLE Contacts
ADD CONSTRAINT fk_farmer_contact_vend
FOREIGN KEY (FarmerID)
REFERENCES Farmer (FarmerID);
ALTER TABLE Field
ADD CONSTRAINT fk_field_soil
FOREIGN KEY (SoilType)
REFERENCES Soil (TypeID);
ALTER TABLE Field
ADD CONSTRAINT fk_current_plant
FOREIGN KEY (CurrentPlant)
REFERENCES Seed (SeedType);
ALTER TABLE Harvest
ADD CONSTRAINT fk_field_location
FOREIGN KEY (FieldLocation)
REFERENCES Field (Location);
ALTER TABLE Harvest
ADD CONSTRAINT fk_harvested_plant
FOREIGN KEY (PlantType)
REFERENCES Seed (SeedType);
ALTER TABLE Harvest
ADD CONSTRAINT fk_distributor_buyer
FOREIGN KEY (DistributorSold)
REFERENCES Distributor (DistributorID);
ALTER TABLE IsResponsibleFor
ADD CONSTRAINT fk_farmer_responsible
FOREIGN KEY (FarmerID)
REFERENCES Farmer (FarmerID);
ALTER TABLE IsResponsibleFor
ADD CONSTRAINT fk_field_responsibility
FOREIGN KEY (FieldLocation)
REFERENCES Field (Location);
ALTER TABLE Material_Bundle
ADD CONSTRAINT fk_material_vendor
FOREIGN KEY (VendorID)
REFERENCES Vendor (VendorID);
ALTER TABLE Material_Bundle
ADD CONSTRAINT fk_material_provided
FOREIGN KEY (MaterialType)
REFERENCES Material (MaterialType);
ALTER TABLE Seed_Bundle
ADD CONSTRAINT fk_seed_vendor
FOREIGN KEY (VendorID)
REFERENCES Vendor (VendorID);
ALTER TABLE Seed_Bundle
ADD CONSTRAINT fk_seed_provided
FOREIGN KEY (SeedType)
REFERENCES Seed (SeedType);
/* --------------------------------------------------------------------------*/
/*-- DELETE Section --------------------------------------------------------------
-- Rows in tables with foreign keys must be deleted first. ---------------------
------------------------------------------------------------------------------*/
DELETE FROM Contacts;
DELETE FROM Distributor;
DELETE FROM Farmer;
DELETE FROM Field;
DELETE FROM Harvest;
DELETE FROM IsResponsibleFor;
DELETE FROM Material;
DELETE FROM Material_Bundle;
DELETE FROM Seed;
DELETE FROM Seed_Bundle;
DELETE FROM Soil;
DELETE FROM Vendor;
/*------------------------------------------------------------------------------
-- INSERT Section ------------------------------------------------------------
-- Rows must be inserted into tables with foreign keys last. -------------------
------------------------------------------------------------------------------*/
/*-- Populate Farmer_T ------------------------------------------------------*/
INSERT INTO Farmer (FarmerID, Name, Address, PhoneNum, Salary, HireDate)
VALUES (13, 'Chris Slover', '43 West Millford', '555-8394', 65000, '2004-10-06');
INSERT INTO Farmer (FarmerID, Name, Address, PhoneNum, Salary, HireDate)
VALUES (12, 'Brian Smith', '22 Lincon Park', '555-9384', 70000, '1996-10-08');
INSERT INTO Farmer (FarmerID, Name, Address, PhoneNum, Salary, HireDate)
VALUES (14, 'Nate Litvak', '84 Sunset Grove', '555-2317', 60000, '2008-04-03');
INSERT INTO Farmer (FarmerID, Name, Address, PhoneNum, Salary, HireDate)
VALUES (15, 'Marcus Ford', '32 Sawmil Rd', '555-0976', 58000, '2010-05-23');
INSERT INTO Farmer (FarmerID, Name, Address, PhoneNum, Salary, HireDate)
VALUES (16, 'Daisy Dukes', '28 Highland Blvd', '555-1233', 63000, '2012-05-11');
INSERT INTO Farmer (FarmerID, Name, Address, PhoneNum, Salary, HireDate)
VALUES (17, 'Helen Hay', '3 Turner Way', '555-4195', 70000, '1995-03-19');
/*-- Populate Distributor_T -------------------------------------------------------*/
INSERT INTO Distributor (DistributorID, FarmerContact, Name, Address, PhoneNum)
SELECT 24, F.FarmerID, 'Plants Plants Plants!', '43 Haven Rd', '555-6783'
FROM Farmer AS F
WHERE F.FarmerID = 16;
INSERT INTO Distributor (DistributorID, FarmerContact, Name, Address, PhoneNum)
SELECT 25, F.FarmerID, 'The Outdoor Store', '56 South Orange Ln', '555-6734'
FROM Farmer AS F
WHERE F.FarmerID = 12;
INSERT INTO Distributor (DistributorID, FarmerContact, Name, Address, PhoneNum)
SELECT 26, F.FarmerID, 'Farmhills Farm Stand', '6 Farmhills Rd', '555-9834'
FROM Farmer AS F
WHERE F.FarmerID = 17;
/*-- Populate Vendor --------------------------------------------------*/
INSERT INTO Vendor (VendorID, Name, Address, PhoneNum)
VALUES (53, 'Lauren Wentworth', '5 Fox Hill Rd.', '555-2345');
INSERT INTO Vendor (VendorID, Name, Address, PhoneNum)
VALUES (54, 'george Doughtry', '15 Pleasant Valley Rd.', '555-3456');
INSERT INTO Vendor (VendorID, Name, Address, PhoneNum)
VALUES (55, 'Henrey Mullen', '34 Park Place', '555-9485');
INSERT INTO Vendor (VendorID, Name, Address, PhoneNum)
VALUES (56, 'Richard Young', '12 Regional St.', '555-3849');
/*--Populate Contacts --------------------------------*/
INSERT INTO Contacts (VendorID, FarmerID)
SELECT V.VendorID, F.FarmerID
FROM Vendor V JOIN Farmer F
WHERE V.VendorID = 55 AND F.FarmerID = 14;
INSERT INTO Contacts (VendorID, FarmerID)
SELECT V.VendorID, F.FarmerID
FROM Vendor V JOIN Farmer F
WHERE V.VendorID = 55 AND F.FarmerID = 16;
INSERT INTO Contacts (VendorID, FarmerID)
SELECT V.VendorID, F.FarmerID
FROM Vendor V JOIN Farmer F
WHERE V.VendorID = 56 AND F.FarmerID = 13;
INSERT INTO Contacts (VendorID, FarmerID)
SELECT V.VendorID, F.FarmerID
FROM Vendor V JOIN Farmer F
WHERE V.VendorID = 53 AND F.FarmerID = 12;
INSERT INTO Contacts (VendorID, FarmerID)
SELECT V.VendorID, F.FarmerID
FROM Vendor V JOIN Farmer F
WHERE V.VendorID = 53 AND F.FarmerID = 17;
INSERT INTO Contacts (VendorID, FarmerID)
SELECT V.VendorID, F.FarmerID
FROM Vendor V JOIN Farmer F
WHERE V.VendorID = 54 AND F.FarmerID = 15;
/*--Populate Soil --------------------------------*/
INSERT INTO Soil (TypeID, SoilType)
VALUES (1, 'Sandy');
INSERT INTO Soil (TypeID, SoilType)
VALUES (2, 'Silty');
INSERT INTO Soil (TypeID, SoilType)
VALUES (3, 'Clay');
INSERT INTO Soil (TypeID, SoilType)
VALUES (4, 'Peaty');
INSERT INTO Soil (TypeID, SoilType)
VALUES (5, 'Saline Soil');
/*--Populate Material -----------------------------*/
INSERT INTO Material (MaterialType)
VALUES ('Plow');
INSERT INTO Material (MaterialType)
VALUES ('Tractor');
INSERT INTO Material (MaterialType)
VALUES ('Fertilizer');
INSERT INTO Material (MaterialType)
VALUES ('Pesticide');
INSERT INTO Material (MaterialType)
VALUES ('Gas Tank');
/*--Populate Seed ---------------------------------*/
INSERT INTO Seed (SeedType)
VALUES ('Rose');
INSERT INTO Seed (SeedType)
VALUES ('Daffodil');
INSERT INTO Seed (SeedType)
VALUES ('Sunflower');
INSERT INTO Seed (SeedType)
VALUES ('Poppy');
INSERT INTO Seed (SeedType)
VALUES ('Tulip');
INSERT INTO Seed (SeedType)
VALUES ('Violet');
/*--Populate Field --------------------------------*/
INSERT INTO Field (Location, SoilType, CurrentPlant, DatePlanted)
SELECT '20E30N', Soil.TypeID, Seed.SeedType, '2013-03-11'
FROM Soil JOIN Seed
WHERE Soil.TypeID = 1 AND Seed.SeedType='Sunflower';
INSERT INTO Field (Location, SoilType, CurrentPlant, DatePlanted)
SELECT '20E25S', Soil.TypeID, Seed.SeedType, '2013-02-11'
FROM Soil JOIN Seed
WHERE Soil.TypeID = 1 AND Seed.SeedType='Daffodil';
INSERT INTO Field (Location, SoilType, CurrentPlant, DatePlanted)
SELECT '10W25N', Soil.TypeID, Seed.SeedType, '2013-04-11'
FROM Soil JOIN Seed
WHERE Soil.TypeID = 3 AND Seed.SeedType='Tulip';
INSERT INTO Field (Location, SoilType, CurrentPlant, DatePlanted)
SELECT '5E5S', Soil.TypeID, Seed.SeedType, '2013-05-11'
FROM Soil JOIN Seed
WHERE Soil.TypeID = 5 AND Seed.SeedType='Violet';
INSERT INTO Field (Location, SoilType, CurrentPlant, DatePlanted)
SELECT '30W30S', Soil.TypeID, Seed.SeedType, '2013-06-11'
FROM Soil JOIN Seed
WHERE Soil.TypeID = 2 AND Seed.SeedType='Rose';
/*--- Populate IsResponsibleFor ----------*/
INSERT INTO IsResponsibleFor (FarmerID, FieldLocation)
SELECT Farmer.FarmerID, Field.Location
FROM Farmer JOIN Field
WHERE Farmer.FarmerID = 12 AND Field.Location = '10W25N';
INSERT INTO IsResponsibleFor (FarmerID, FieldLocation)
SELECT Farmer.FarmerID, Field.Location
FROM Farmer JOIN Field
WHERE Farmer.FarmerID = 12 AND Field.Location = '30W30S';
INSERT INTO IsResponsibleFor (FarmerID, FieldLocation)
SELECT Farmer.FarmerID, Field.Location
FROM Farmer JOIN Field
WHERE Farmer.FarmerID = 13 AND Field.Location = '20E30N';
INSERT INTO IsResponsibleFor (FarmerID, FieldLocation)
SELECT Farmer.FarmerID, Field.Location
FROM Farmer JOIN Field
WHERE Farmer.FarmerID = 15 AND Field.Location = '5E5S';
INSERT INTO IsResponsibleFor (FarmerID, FieldLocation)
SELECT Farmer.FarmerID, Field.Location
FROM Farmer JOIN Field
WHERE Farmer.FarmerID = 17 AND Field.Location = '20E25S';
/*----Populate Material_Bundle-------------------------*/
INSERT INTO Material_Bundle (VendorID, DatePurchased, MaterialType, Quantity, Price)
SELECT Vendor.VendorID, '2000-11-12', Material.MaterialType, 1, 10000
FROM Vendor JOIN Material
WHERE Vendor.VendorID = 53 AND Material.MaterialType = 'Plow';
INSERT INTO Material_Bundle (VendorID, DatePurchased, MaterialType, Quantity, Price)
SELECT Vendor.VendorID, '2000-11-12', Material.MaterialType, 1, 5000
FROM Vendor JOIN Material
WHERE Vendor.VendorID = 55 AND Material.MaterialType = 'Tractor';
INSERT INTO Material_Bundle (VendorID, DatePurchased, MaterialType, Quantity, Price)
SELECT Vendor.VendorID, '2000-11-12', Material.MaterialType, 100, 2000
FROM Vendor JOIN Material
WHERE Vendor.VendorID = 56 AND Material.MaterialType = 'Fertilizer';
INSERT INTO Material_Bundle (VendorID, DatePurchased, MaterialType, Quantity, Price)
SELECT Vendor.VendorID, '2000-11-12', Material.MaterialType, 500, 10000
FROM Vendor JOIN Material
WHERE Vendor.VendorID = 55 AND Material.MaterialType = 'Pesticide';
INSERT INTO Material_Bundle (VendorID, DatePurchased, MaterialType, Quantity, Price)
SELECT Vendor.VendorID, '2000-11-12', Material.MaterialType, 5, 25000
FROM Vendor JOIN Material
WHERE Vendor.VendorID = 54 AND Material.MaterialType = 'Gas Tank';
INSERT INTO Material_Bundle (VendorID, DatePurchased, MaterialType, Quantity, Price, ReturnDate)
SELECT Vendor.VendorID, '2012-05-20', Material.MaterialType, 1, 10000, '2012-06-05'
FROM Vendor JOIN Material
WHERE Vendor.VendorID = 53 AND Material.MaterialType = 'Plow';
/*----Populate Seed_Bundle-----------------------------*/
INSERT INTO Seed_Bundle (VendorID, DatePurchased, SeedType, Quantity, Price, ReturnDate)
SELECT Vendor.VendorID, '2011-05-18', Seed.SeedType, 10000, 500, '2011-06-05'
FROM Vendor JOIN Seed
WHERE Vendor.VendorID = 56 AND Seed.SeedType = 'Daffodil';
INSERT INTO Seed_Bundle (VendorID, DatePurchased, SeedType, Quantity, Price)
SELECT Vendor.VendorID, '2011-06-29', Seed.SeedType, 4000, 100
FROM Vendor JOIN Seed
WHERE Vendor.VendorID = 56 AND Seed.SeedType = 'Daffodil';
INSERT INTO Seed_Bundle (VendorID, DatePurchased, SeedType, Quantity, Price)
SELECT Vendor.VendorID, '2012-04-18', Seed.SeedType, 1000, 50
FROM Vendor JOIN Seed
WHERE Vendor.VendorID = 53 AND Seed.SeedType = 'Sunflower';
INSERT INTO Seed_Bundle (VendorID, DatePurchased, SeedType, Quantity, Price)
SELECT Vendor.VendorID, '2012-05-18', Seed.SeedType, 3000, 200
FROM Vendor JOIN Seed
WHERE Vendor.VendorID = 53 AND Seed.SeedType = 'Tulip';
INSERT INTO Seed_Bundle (VendorID, DatePurchased, SeedType, Quantity, Price)
SELECT Vendor.VendorID, '2012-06-18', Seed.SeedType, 20000, 600
FROM Vendor JOIN Seed
WHERE Vendor.VendorID = 55 AND Seed.SeedType = 'Violet';
INSERT INTO Seed_Bundle (VendorID, DatePurchased, SeedType, Quantity, Price)
SELECT Vendor.VendorID, '2012-07-18', Seed.SeedType, 2000, 100
FROM Vendor JOIN Seed
WHERE Vendor.VendorID = 54 AND Seed.SeedType = 'Rose';
INSERT INTO Seed_Bundle (VendorID, DatePurchased, SeedType, Quantity, Price)
SELECT Vendor.VendorID, '2012-08-18', Seed.SeedType, 5000, 450
FROM Vendor JOIN Seed
WHERE Vendor.VendorID = 54 AND Seed.SeedType = 'Poppy';
/*----Populate Harvest --------------------------------*/
INSERT INTO Harvest (HarvestDate, FieldLocation, PlantType, PlantCount, PurchaseDate, DistributorSold, PriceSold)
SELECT '2013-10-11', Field.Location, Seed.SeedType, 150, '2013-10-15', Distributor.DistributorID, 5000
FROM Field JOIN Seed JOIN Distributor
WHERE Field.Location = '5E5S' AND Seed.SeedType = 'Violet' AND Distributor.DistributorID=24;
INSERT INTO Harvest (HarvestDate, FieldLocation, PlantType, PlantCount)
SELECT '2013-10-12', Field.Location, Seed.SeedType, 203
FROM Field JOIN Seed
WHERE Field.Location = '30W30S' AND Seed.SeedType = 'Rose';
INSERT INTO Harvest (HarvestDate, FieldLocation, PlantType, PlantCount, ExpirationDate)
SELECT '2013-10-13', Field.Location, Seed.SeedType, 157, '2014-01-22'
FROM Field JOIN Seed
WHERE Field.Location = '20E30N' AND Seed.SeedType = 'Sunflower';
INSERT INTO Harvest (HarvestDate, FieldLocation, PlantType, PlantCount)
SELECT '2013-10-14', Field.Location, Seed.SeedType, 50
FROM Field JOIN Seed
WHERE Field.Location = '10W25N' AND Seed.SeedType = 'Tulip';
INSERT INTO Harvest (HarvestDate, FieldLocation, PlantType, PlantCount)
SELECT '2013-10-15', Field.Location, Seed.SeedType, 303
FROM Field JOIN Seed
WHERE Field.Location = '20E25S' AND Seed.SeedType = 'Daffodil';