-- ------------------ town -- -------------------------------
DROP TABLE IF EXISTS town;
CREATE TABLE town (
townId VARCHAR(3) NOT NULL PRIMARY KEY,
townName VARCHAR(20),
State VARCHAR(20),
Country VARCHAR(20),
townNickname VARCHAR(80),
townMotto VARCHAR(80)
);
-- order matches table creation:
-- id name state country
-- nickname motto
INSERT INTO town VALUES ('p', 'Philadelphia', 'PA', 'United States',
'Philly', 'Let brotherly love endure');
INSERT INTO town VALUES ('a', 'Anoka', 'MN', 'United States',
'Halloween Capital of the world', NULL);
INSERT INTO town VALUES ('be', 'Blue Earth', 'MN', 'United States',
'Beyond the Valley of the Jolly Green Giant',
'Earth so rich the city grows!');
INSERT INTO town VALUES ('b', 'Bemidji', 'MN', 'United States',
'B-town', 'The first city on the Mississippi');
INSERT INTO town VALUES ('d', 'Duluth', 'MN', 'United States',
'Zenith City', NULL);
INSERT INTO town VALUES ('g', 'Greenville', 'MS', 'United States',
'The Heart & Soul of the Delta',
'The Best Food, Shopping, & Entertainment In The South');
INSERT INTO town VALUES ('t', 'Tokyo', 'Kanto', 'Japan', NULL, NULL);
INSERT INTO town VALUES ('as', 'Asgard', NULL, NULL,
'Home of Odin''s vault',
'Where magic and science are one in the same');
INSERT INTO town VALUES ('mv', 'Metroville', NULL, NULL,
'Home of the Incredibles',
'Still Standing');
INSERT INTO town VALUES ('le', 'London', 'England', 'United Kingdom',
'The Smoke',
'Domine dirige nos');
INSERT INTO town VALUES ('sw', 'Seattle', 'Washington', 'United States',
'The Emerald City',
'The City of Goodwill');
-- ------------------ creature -- -------------------------------
DROP TABLE IF EXISTS creature;
CREATE TABLE creature (
creatureId INTEGER NOT NULL PRIMARY KEY,
creatureName VARCHAR(20),
creatureType VARCHAR(20),
reside_townId VARCHAR(3) REFERENCES town(townId), -- foreign key
idol_creatureId INTEGER,
FOREIGN KEY(idol_creatureId) REFERENCES creature(creatureId)
);
INSERT INTO creature VALUES (1,'Bannon','person','p',10);
INSERT INTO creature VALUES (2,'Myers','person','a',9);
INSERT INTO creature VALUES (3,'Neff','person','be',NULL);
INSERT INTO creature VALUES (4,'Neff','person','b',3);
INSERT INTO creature VALUES (5,'Mieska','person','d', 10);
INSERT INTO creature VALUES (6,'Carlis','person','p',9);
INSERT INTO creature VALUES (7,'Kermit','frog','g',8);
INSERT INTO creature VALUES (8,'Godzilla','monster','t',6);
INSERT INTO creature VALUES (9,'Thor','superhero','as',NULL);
INSERT INTO creature VALUES (10,'Elastigirl','superhero','mv',13);
INSERT INTO creature VALUES (11,'David Beckham','person','le',9);
INSERT INTO creature VALUES (12,'Harry Kane','person','le',11);
INSERT INTO creature VALUES (13,'Megan Rapinoe','person','sw',10);
-- ------------------ skill -- -------------------------------
DROP TABLE IF EXISTS skill;
CREATE TABLE skill (
skillCode VARCHAR(3) NOT NULL PRIMARY KEY,
skillDescription VARCHAR(40),
maxProficiency INTEGER, -- max score that can be achieved for this skill
minProficiency INTEGER, -- min score that can be achieved for this skill
origin_townId VARCHAR(3) REFERENCES town(townId) -- foreign key
);
INSERT INTO skill VALUES ('A', 'float', 10, -1,'b');
INSERT INTO skill VALUES ('E', 'swim', 5, 0,'b');
INSERT INTO skill VALUES ('O', 'sink', 10, -1,'b');
INSERT INTO skill VALUES ('U', 'walk on water', 5, 1,'d');
INSERT INTO skill VALUES ('Z', 'gargle', 5, 1,'a');
INSERT INTO skill VALUES ('B2', '2-crew bobsledding', 25, 0,'d');
INSERT INTO skill VALUES ('TR4', '4x100 meter track relay', 100, 0,'be');
INSERT INTO skill VALUES ('C2', '2-person canoeing', 12, 1,'t');
INSERT INTO skill VALUES ('THR', 'three-legged race', 10, 0,'g');
INSERT INTO skill VALUES ('D3', 'Australasia debating', 10, 1,NULL);
INSERT INTO skill VALUES ('PK', 'soccer penalty kick', 10, 1, 'le');
-- Note that no skill originates in Philly or Metroville or Asgaard
-- ------------------ teamSkill -- -------------------------------
DROP TABLE IF EXISTS teamSkill;
CREATE TABLE teamSkill (
skillCode VARCHAR(3) NOT NULL PRIMARY KEY references skill (skillCode),
teamSize INTEGER
);
INSERT INTO teamSkill VALUES ('B2', 2);
INSERT INTO teamSkill VALUES ('TR4', 4);
INSERT INTO teamSkill VALUES ('C2', 2);
INSERT INTO teamSkill VALUES ('THR', 2);
INSERT INTO teamSkill VALUES ('D3', 3);
-- ------------------ achievement -- -------------------------------
DROP TABLE IF EXISTS achievement;
CREATE TABLE achievement (
achId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
creatureId INTEGER,
skillCode VARCHAR(3),
proficiency INTEGER,
achDate TEXT,
test_townId VARCHAR(3) REFERENCES town(townId), -- foreign key
FOREIGN KEY (creatureId) REFERENCES creature (creatureId),
FOREIGN KEY (skillCode) REFERENCES skill (skillCode)
);
-- Bannon floats in Anoka (where he aspired)
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (1, 'A', 3, datetime('now'), 'a');
-- Bannon swims in Duluth (he aspired in Bemidji)
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (1, 'E', 3, datetime('2017-09-15 15:35'), 'd');
-- Bannon floats in Anoka (where he aspired)
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (1, 'A', 3, datetime('2018-07-14 14:00'), 'a');
-- Bannon swims in Duluth (he aspired in Bemidji)
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (1, 'E', 3, datetime('now'), 'd');
-- Bannon doesn't gargle
-- Mieska gargles in Tokyo (had no aspiration to)
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (5, 'Z', 6, datetime('2016-04-12 15:42:30'), 't');
-- Neff #3 gargles in Blue Earth (but not to his aspired proficiency)
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (3, 'Z', 4, datetime('2018-07-15'), 'be');
-- Neff #3 gargles in Blue Earth (but not to his aspired proficiency)
-- on same day at same proficiency, signifying need for arbitrary id
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (3, 'Z', 4, datetime('2018-07-15'), 'be');
-- Neff #3 keeps trying to gargle on the same day, with varying results
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (3, 'Z', 4, datetime('2018-07-15'), 'be');
-- Neff #4 gargles in Anoka
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (4, 'Z', 3, datetime('2018-06-10'), 'a');
-- Beckham achieves PK in London
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (11, 'PK', 10, datetime('1998-08-15'), 'le');
-- Kane achieves PK in London
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (12, 'PK', 10, datetime('2016-05-24'), 'le');
-- Rapinoe achieves PK in London
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (13, 'PK', 10, datetime('2012-08-06'), 'le');
-- Godzilla achieves PK in Tokyo poorly with no date
-- had not aspiration to do so- did it on a dare ;)
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (8, 'PK', 1, NULL, 't');
-- -------------------- -------------------- -------------------
-- Thor achieves three-legged race in Metroville (with Elastigirl)
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (9, 'THR', 10, datetime('2018-08-12 14:30'), 'mv');
-- Elastigirl achieves three-legged race in Metroville (with Thor)
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (10, 'THR', 10, datetime('2018-08-12 14:30'), 'mv');
-- Kermit 'pilots' 2-person bobsledding (pilot goes into contribution)
-- with Thor as brakeman (brakeman goes into contribution) in Duluth,
-- achieve at 76% of maxProficiency
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (7, 'B2', 19, datetime('2017-01-10 16:30'), 'd');
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (9, 'B2', 19, datetime('2017-01-10 16:30'), 'd');
-- 4 people form track realy team in London:
-- Neff #4, Mieska, Myers, Bannon
-- achieve at 85% of maxProficiency
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (4, 'TR4', 85, datetime('2012-07-30'), 'le');
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (5, 'TR4', 85, datetime('2012-07-30'), 'le');
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (2, 'TR4', 85, datetime('2012-07-30'), 'le');
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (1, 'TR4', 85, datetime('2012-07-30'), 'le');
-- Thor, Rapinoe, and Kermit form debate team in Seattle, WA and
-- achieve at 80% of maxProficiency
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (9, 'D3', 8, datetime('now', 'localtime'), 'sw');
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (13, 'D3', 8, datetime('now', 'localtime'), 'sw');
INSERT INTO achievement (creatureId, skillCode, proficiency,
achDate, test_townId)
VALUES (7, 'D3', 8, datetime('now', 'localtime'), 'sw');
-- no 2-person canoeing achievements, but some have aspirations
-- ------------------ aspiration -- -------------------------------
DROP TABLE IF EXISTS aspiration;
CREATE TABLE aspiration
( -- foreign key
creatureId INTEGER NOT NULL REFERENCES creature(creatureId),
-- foreign key
skillCode VARCHAR(3) NOT NULL REFERENCES skill(skillCode),
aspiredProficiency INTEGER,
desired_townId VARCHAR(3) REFERENCES town(townId), -- foreign key
PRIMARY KEY (creatureId, skillCode)
);
-- Bannon aspires float in Anoka with proficiency of 3
INSERT INTO aspiration VALUES (1,'A',3,'a');
-- Bannon aspires swim in Bemidji with proficiency of 4
INSERT INTO aspiration VALUES (1,'E',4,'b');
-- Bannon aspires gargling in Blue Earth with proficiency of 3
INSERT INTO aspiration VALUES (1,'Z',3,'be');
-- Myers aspires float with proficiency of 3
INSERT INTO aspiration VALUES (2,'A',3,NULL);
-- Neff #3 aspires float in Bemidji with proficiency of 8
INSERT INTO aspiration VALUES (3,'A',8,'b');
-- Neff #3 aspires gargling in Blue Earth with proficiency of 5
INSERT INTO aspiration VALUES (3,'Z',5,'be');
-- Neff #4 aspires swim in Greenville with proficiency of 3
INSERT INTO aspiration VALUES (4,'E',3,'g');
-- Mieska aspires gargling in Duluth with proficiency of
INSERT INTO aspiration VALUES (5,'Z',10,'d');
-- Carlis aspires gargling in London with proficiency of
INSERT INTO aspiration VALUES (6,'Z',3,'le');
-- Kermit aspires swim in Bemidji with proficiency of
INSERT INTO aspiration VALUES (7,'E',3,'b');
-- Godzilla aspires sink in Tokyo with proficiency of
INSERT INTO aspiration VALUES (8,'O',4,'t');
-- Beckham, Kane, and Rapinoe aspire to achieve PK at maxProficiency in London
INSERT INTO aspiration VALUES (11,'PK',10,'le');
INSERT INTO aspiration VALUES (12,'PK',10,'le');
INSERT INTO aspiration VALUES (13,'PK',10,'le');
-- Kermit aspires to achieve 2-person bobsledding at proficiency 20 in Duluth
INSERT INTO aspiration VALUES (7,'B2',20,'d');
-- Bannon and Mieska aspire to achieve 4x100 meter track relay at
-- proficiency of 85 in Seattle, WA.
INSERT INTO aspiration VALUES (1,'TR4',85,'sw');
INSERT INTO aspiration VALUES (5,'TR4',85,'sw');
-- Thor, Rapinoe, and Kermit form debate team in Seattle, WA and
-- asppire to achieve at 80% of maxProficiency
INSERT INTO aspiration VALUES (9,'D3',8,'sw');
INSERT INTO aspiration VALUES (13,'D3',8,'sw');
INSERT INTO aspiration VALUES (7,'D3',8,'sw');
-- no 2-person canoeing achievements, but some have aspirations
-- Carlis and Bannon aspire to achieve 2-person canoeing in Bemidji
-- with proficiency of 9
INSERT INTO aspiration VALUES (6,'C2',9,'b');
INSERT INTO aspiration VALUES (1,'C2',9,'b');
-- Thor, Elastigirl do not aspire to anything
-- ------------------ role -- -------------------------------
DROP TABLE IF EXISTS role;
CREATE TABLE role
(
roleName VARCHAR(20) NOT NULL PRIMARY KEY
);
INSERT INTO role VALUES ('first leg'); -- 4x100 track
INSERT INTO role VALUES ('second leg'); -- 4x100 track
INSERT INTO role VALUES ('third leg'); -- 4x100 track
INSERT INTO role VALUES ('anchor leg'); -- 4x100 track
INSERT INTO role VALUES ('pilot'); -- 2-crew bobsled
INSERT INTO role VALUES ('brakeman'); -- 2-crew bobsled
INSERT INTO role VALUES ('right leg'); -- 3-legged race
INSERT INTO role VALUES ('left leg'); -- 3-legged race
INSERT INTO role VALUES ('stern paddler'); -- 2-person canoeing
INSERT INTO role VALUES ('bow paddler'); -- 2-person canoeing
INSERT INTO role VALUES ('first speaker'); -- Australasia debating
INSERT INTO role VALUES ('second speaker');-- Australasia debating
INSERT INTO role VALUES ('team captain'); -- Australasia debating
-- ------------------ contribution -- -------------------------------
DROP TABLE IF EXISTS contribution;
CREATE TABLE contribution (
creatureId INTEGER NOT NULL REFERENCES creature(creatureId),
achId INTEGER NOT NULL REFERENCES achievement(achId),
skillCode VARCHAR(3) NOT NULL REFERENCES skill(skillCode),
roleName VARCHAR(20) REFERENCES role(roleName),
PRIMARY KEY (creatureId, achId)
);
-- Thor (right leg) achieves three-legged race in Metroville (with Elastigirl (left leg))
INSERT INTO contribution VALUES (9, 12, 'THR', 'right leg');
INSERT INTO contribution VALUES (10, 13, 'THR', 'left leg');
-- Kermit 'pilots' 2-crew bobsledding
-- with Thor as brakeman
INSERT INTO contribution VALUES (7, 14, 'B2', 'pilot');
INSERT INTO contribution VALUES (9, 15, 'B2', 'brakeman');
--
-- keep track relay, have 4 people:
-- Neff #4 (first leg), Mieska(second leg), Myers (third leg), Bannon (anchor leg)
INSERT INTO contribution VALUES (4, 16, 'TR4', 'first leg');
INSERT INTO contribution VALUES (5, 17, 'TR4', 'second leg');
INSERT INTO contribution VALUES (2, 18, 'TR4', 'third leg');
INSERT INTO contribution VALUES (1, 19, 'TR4', 'anchor leg');
-- Thor (second speaker), Rapinoe (team captain), and Kermit (first speaker) form debate team
INSERT INTO contribution VALUES (7, 22, 'D3', 'first speaker');
INSERT INTO contribution VALUES (9, 20, 'D3', 'second speaker');
INSERT INTO contribution VALUES (13, 21, 'D3', 'team captain');
--
-- no 2-person canoeing contributions, but some have aspirations
-- ------------------ aspiredContribution -- -------------------------------
DROP TABLE IF EXISTS aspiredContribution;
CREATE TABLE aspiredContribution (
creatureId INTEGER NOT NULL REFERENCES creature(creatureId),
skillCode VARCHAR(3) NOT NULL REFERENCES skill(skillCode),
roleName VARCHAR(20) REFERENCES role(roleName),
PRIMARY KEY (creatureId, skillCode)
);
-- no 2-person canoeing contributions, but Carlis and Bannon have aspirations
INSERT INTO aspiredContribution VALUES (6, 'C2', 'stern paddler');
INSERT INTO aspiredContribution VALUES (1, 'C2', 'bow paddler');
-- Bannon and Mieska aspire to contribute to achieve 4x100 meter track relay
-- Bannon contributed in his aspired to role, Mieska had a different
-- aspired to role than he ultimately contributed to
INSERT INTO aspiredContribution VALUES (1, 'TR4', 'anchor leg');
INSERT INTO aspiredContribution VALUES (5, 'TR4', 'third leg');
-- Kermit aspires to contribute to piloting bobsled
INSERT INTO aspiredContribution VALUES (7, 'B2', 'pilot');
-- Thor, Rapinoe and Kermit aspire to contribute to debate
INSERT INTO aspiredContribution VALUES (7, 'D3', 'first speaker');
INSERT INTO aspiredContribution VALUES (9, 'D3', 'second speaker');
INSERT INTO aspiredContribution VALUES (13, 'D3', 'team captain');
-- Elastigirl, others not aspiring to contribute to anything