Skip to content

Instantly share code, notes, and snippets.

@domfarolino
Last active April 19, 2017 20:55
Show Gist options
  • Save domfarolino/43e0fd0090ccdc89bbc8e33b2ee33a56 to your computer and use it in GitHub Desktop.
Save domfarolino/43e0fd0090ccdc89bbc8e33b2ee33a56 to your computer and use it in GitHub Desktop.
-- 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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment