– —————— 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