project3.sql

-- Customer relation (strong entity)
CREATE TABLE domfarolino.customer, FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL
(
    custID int NOT NULL,
    custName char(25)
)
UNIQUE PRIMARY INDEX(custID);

-- Technician relation (strong entity)
CREATE TABLE domfarolino.technician, FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL
(
    techID int NOT NULL,
    unit char(25),
    name char(25)
)
UNIQUE PRIMARY INDEX(techID);

-- ProductCategory relation (strong entity)
CREATE TABLE domfarolino.productCategory, FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL
(
    pcatID int NOT NULL,
    modelName char(25)
)
UNIQUE PRIMARY INDEX(pcatID);

-- SoldProduct relation (strong entity)
CREATE TABLE domfarolino.soldProduct, FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL
(
    prodID int NOT NULL,
    techID int,
    pcatID int,
    productName char(256),
    stdPrice int,
    warrantyStartDate char(25),
    warrantyLength int,
    repairDate char(25),
    repairAmount int
)
UNIQUE PRIMARY INDEX(prodID);

-- ServiceAgreement relation (weak entity)
CREATE TABLE domfarolino.serviceAgreement, FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL
(
    agreeType int NOT NULL,
    pcatID int NOT NULL,
    conditions char(256)
)
UNIQUE PRIMARY INDEX(agreeType, pcatID);

-- Skill relation (multivalue attribute)
CREATE TABLE domfarolino.skill, FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL
(
    skillID int NOT NULL,
    techID int NOT NULL,
    skillName char(256)
)
UNIQUE PRIMARY INDEX(skillID, techID);

-- CanRepair relation (m-n binary relationship)
CREATE TABLE domfarolino.canRepair, FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL
(
    techID int NOT NULL,
    pcatID int NOT NULL,
    certification char(256)
)
UNIQUE PRIMARY INDEX(techID, pcatID);

-- Sells relation (ternary relationship)
CREATE TABLE domfarolino.sells, FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL
(
    techID int NOT NULL,
    prodID int NOT NULL,
    custID int NOT NULL,
    sAmount int
)
UNIQUE PRIMARY INDEX(techID, prodID, custID);

--------------------------------------- Inserts ------------------------------------------

-- Customers
INSERT INTO domfarolino.customer (custID, custName) VALUES (1, 'Dominic Farolino');
INSERT INTO domfarolino.customer (custID, custName) VALUES (2, 'Will Severson');
INSERT INTO domfarolino.customer (custID, custName) VALUES (3, 'Karen Davis');
INSERT INTO domfarolino.customer (custID, custName) VALUES (4, 'Zak Kauth');
INSERT INTO domfarolino.customer (custID, custName) VALUES (5, 'Ian Moore');

-- Technicians
INSERT INTO domfarolino.technician (techID, unit, name) VALUES (1, 'Data Science', 'Claude Shannon');
INSERT INTO domfarolino.technician (techID, unit, name) VALUES (2, 'Data Science', 'Kenneth Berman');
INSERT INTO domfarolino.technician (techID, unit, name) VALUES (3, 'Data Science', 'John Franco');
INSERT INTO domfarolino.technician (techID, unit, name) VALUES (4, 'Data Science', 'Karen Davis');
INSERT INTO domfarolino.technician (techID, unit, name) VALUES (5, 'Data Science', 'Fred Annexstein');
INSERT INTO domfarolino.technician (techID, unit, name) VALUES (6, 'Data Science', 'Raj Batnagar');
INSERT INTO domfarolino.technician (techID, unit, name) VALUES (7, 'sales', 'Rob Schweitzer');
INSERT INTO domfarolino.technician (techID, unit, name) VALUES (8, 'comedy', 'Jerry Seinfeld');
INSERT INTO domfarolino.technician (techID, unit, name) VALUES (9, 'comedy', 'Larry David');

-- ProductCategory
INSERT INTO domfarolino.productCategory (pcatID, modelName) VALUES (1, 'Home goods');
INSERT INTO domfarolino.productCategory (pcatID, modelName) VALUES (2, 'Outdoor');
INSERT INTO domfarolino.productCategory (pcatID, modelName) VALUES (3, 'Automobile');
INSERT INTO domfarolino.productCategory (pcatID, modelName) VALUES (4, 'Books');
INSERT INTO domfarolino.productCategory (pcatID, modelName) VALUES (5, 'Programming');
INSERT INTO domfarolino.productCategory (pcatID, modelName) VALUES (6, 'Utility & Maintenance');

-- SoldProduct
INSERT INTO domfarolino.soldProduct (prodID, techID, pcatID, productName, stdPrice, warrantyStartDate, warrantyLength, repairDate, repairAmount)
VALUES (1, 3, 2, 'Super ultra fast blender', 80, 'May 05th, 2015', 30, 'May 10th, 2015', 600);
INSERT INTO domfarolino.soldProduct (prodID, techID, pcatID, productName, stdPrice, warrantyStartDate, warrantyLength, repairDate, repairAmount)
VALUES (2, 1, 6, 'Belt Sander', 99, 'August 1st, 2015', 90, 'May 10th, 2016', 55);
INSERT INTO domfarolino.soldProduct (prodID, techID, pcatID, productName, stdPrice, warrantyStartDate, warrantyLength, repairDate, repairAmount)
VALUES (3, 3, 3, 'Tool #5', 200, 'January 21st, 2016', 45, 'August 12th, 2016', 300);
INSERT INTO domfarolino.soldProduct (prodID, techID, pcatID, productName, stdPrice, warrantyStartDate, warrantyLength, repairDate, repairAmount)
VALUES (4, 5, 4, 'Drill bit', 350, 'January 22nd, 2016', 60, 'July 04th, 2016', 288);
INSERT INTO domfarolino.soldProduct (prodID, techID, pcatID, productName, stdPrice, warrantyStartDate, warrantyLength, repairDate, repairAmount)
VALUES (5, 4, 4, 'Plexiglass', 750, 'March 06th, 2016', 30, 'August 10th, 2016', 60);
INSERT INTO domfarolino.soldProduct (prodID, techID, pcatID, productName, stdPrice, warrantyStartDate, warrantyLength, repairDate, repairAmount)
VALUES (6, 2, 2, 'Buckle', 20, 'February 01st, 2016', 0, 'August 11th, 2016', 10);
INSERT INTO domfarolino.soldProduct (prodID, techID, pcatID, productName, stdPrice, warrantyStartDate, warrantyLength, repairDate, repairAmount)
VALUES (7, 3, 2, 'Hammock', 95, 'February 02nd, 2016', 10, 'July 8th, 2016', 60);
INSERT INTO domfarolino.soldProduct (prodID, techID, pcatID, productName, stdPrice, warrantyStartDate, warrantyLength, repairDate, repairAmount)
VALUES (8, 3, 2, 'Tent', 120, 'February 03nd, 2016', 90, 'February 8th, 2016', 20);
INSERT INTO domfarolino.soldProduct (prodID, techID, pcatID, productName, stdPrice, warrantyStartDate, warrantyLength, repairDate, repairAmount)
VALUES (9, 3, 2, 'Mechanical Keyboard', 110, 'February 03nd, 2016', 90, 'February 8th, 2016', 30);

-- ServiceAgreement
INSERT INTO domfarolino.serviceAgreement (agreeType, pcatID, conditions) VALUES (1, 2, 'Buy one get one free');
INSERT INTO domfarolino.serviceAgreement (agreeType, pcatID, conditions) VALUES (2, 1, 'Buy one get one free');
INSERT INTO domfarolino.serviceAgreement (agreeType, pcatID, conditions) VALUES (3, 3, 'Extra coupon available after purchase');
INSERT INTO domfarolino.serviceAgreement (agreeType, pcatID, conditions) VALUES (4, 1, 'Limit one per customer');
INSERT INTO domfarolino.serviceAgreement (agreeType, pcatID, conditions) VALUES (5, 4, 'Extra coupon available after purchase');

-- Skills
INSERT INTO domfarolino.skill (skillID, techID, skillName) VALUES (1, 1, 'SQL');
INSERT INTO domfarolino.skill (skillID, techID, skillName) VALUES (2, 1, 'R');
INSERT INTO domfarolino.skill (skillID, techID, skillName) VALUES (3, 2, 'SQL');
INSERT INTO domfarolino.skill (skillID, techID, skillName) VALUES (4, 2, 'R');
INSERT INTO domfarolino.skill (skillID, techID, skillName) VALUES (5, 3, 'SQL');
INSERT INTO domfarolino.skill (skillID, techID, skillName) VALUES (6, 3, 'R');
INSERT INTO domfarolino.skill (skillID, techID, skillName) VALUES (7, 4, 'SQL');
INSERT INTO domfarolino.skill (skillID, techID, skillName) VALUES (8, 4, 'R');
INSERT INTO domfarolino.skill (skillID, techID, skillName) VALUES (9, 5, 'SQL');
INSERT INTO domfarolino.skill (skillID, techID, skillName) VALUES (10, 5, 'R');
INSERT INTO domfarolino.skill (skillID, techID, skillName) VALUES (11, 6, 'Analysis');
INSERT INTO domfarolino.skill (skillID, techID, skillName) VALUES (12, 6, 'Data mining');
INSERT INTO domfarolino.skill (skillID, techID, skillName) VALUES (12, 7, 'SQL');
INSERT INTO domfarolino.skill (skillID, techID, skillName) VALUES (13, 8, 'SQL');
INSERT INTO domfarolino.skill (skillID, techID, skillName) VALUES (14, 8, 'Sales');
INSERT INTO domfarolino.skill (skillID, techID, skillName) VALUES (15, 9, 'R');
INSERT INTO domfarolino.skill (skillID, techID, skillName) VALUES (16, 8, 'Management');

-- CanRepair
INSERT INTO domfarolino.canRepair (techID, pcatID, certification) VALUES (1, 2, 'Valve repair cert');
INSERT INTO domfarolino.canRepair (techID, pcatID, certification) VALUES (2, 1, 'Circuit repair cert');
INSERT INTO domfarolino.canRepair (techID, pcatID, certification) VALUES (3, 4, 'Networking repair cert');
INSERT INTO domfarolino.canRepair (techID, pcatID, certification) VALUES (4, 4, 'Valve repair cert');
INSERT INTO domfarolino.canRepair (techID, pcatID, certification) VALUES (5, 3, 'Gas like repair cert');

-- Sells
INSERT INTO domfarolino.sells (techID, prodID, custID, sAmount) VALUES (1, 1, 2, 600);
INSERT INTO domfarolino.sells (techID, prodID, custID, sAmount) VALUES (2, 4, 1, 200);
INSERT INTO domfarolino.sells (techID, prodID, custID, sAmount) VALUES (3, 4, 3, 250);
INSERT INTO domfarolino.sells (techID, prodID, custID, sAmount) VALUES (4, 3, 4, 180);
INSERT INTO domfarolino.sells (techID, prodID, custID, sAmount) VALUES (5, 5, 5, 99);