CREATE TABLE town
( t_id    VARCHAR(2)   PRIMARY KEY,
  t_name  VARCHAR(20)
);

INSERT INTO town VALUES ('a','Anoka');
INSERT INTO town VALUES ('b','Bemidji');
INSERT INTO town VALUES ('bl','Blue Earth');
INSERT INTO town VALUES ('c','Chaska');
INSERT INTO town VALUES ('d','Duluth');
INSERT INTO town VALUES ('em','Embarrass');
INSERT INTO town VALUES ('e','Edina');
INSERT INTO town VALUES ('h','Hollywood');
INSERT INTO town VALUES ('p','Phily');
INSERT INTO town VALUES ('s','Swampville');
INSERT INTO town VALUES ('t','Toyko');

CREATE TABLE creature
( c_id          INTEGER      PRIMARY KEY,
  c_name        VARCHAR(20),
  c_type        VARCHAR(20),
  reside_t_id   VARCHAR(2)   REFERENCES town(t_id)
);

INSERT INTO creature VALUES (1,'Bannon','person','p');
INSERT INTO creature VALUES (2,'Myers','person','a');
INSERT INTO creature VALUES (3,'Neff','person','b');
INSERT INTO creature VALUES (4,'Neff','person','c');
INSERT INTO creature VALUES (5,'Mieska','person','d');
INSERT INTO creature VALUES (6,'Carlis','person','p');
INSERT INTO creature VALUES (7,'Kermit','frog','h');
INSERT INTO creature VALUES (8,'Godzilla','monster','t');


CREATE TABLE skill
( s_code          VARCHAR(1)    PRIMARY KEY,
  s_description   VARCHAR(20),
  origin_t_id     VARCHAR(2)    REFERENCES town(t_id)
);

INSERT INTO skill VALUES ('A','float','b');
INSERT INTO skill VALUES ('E','swim','b');
INSERT INTO skill VALUES ('O','sink','t');
INSERT INTO skill VALUES ('U','walk on water','em');
INSERT INTO skill VALUES ('Z','gargle','p');


CREATE TABLE achievement
( c_id        INTEGER       REFERENCES creature(c_id),
  s_code      VARCHAR(1)    REFERENCES skill(s_code),
  score       INTEGER,
  test_t_id   VARCHAR(2)    REFERENCES town(t_id),
  PRIMARY KEY (c_id, s_code)
);

INSERT INTO achievement VALUES (1,'A',1,'a');
INSERT INTO achievement VALUES (1,'E',3,'a');
INSERT INTO achievement VALUES (1,'Z',3,'p');
INSERT INTO achievement VALUES (2,'A',3,'b');
INSERT INTO achievement VALUES (3,'A',2,'b');
INSERT INTO achievement VALUES (3,'Z',1,'p');
INSERT INTO achievement VALUES (4,'A',2,'c');
INSERT INTO achievement VALUES (4,'E',2,'c');
INSERT INTO achievement VALUES (5,'Z',3,'d');
INSERT INTO achievement VALUES (7,'E',1,'s');
INSERT INTO achievement VALUES (8,'O',1,'t');

CREATE TABLE aspiration
( c_id        INTEGER       REFERENCES creature(c_id),
  s_code      VARCHAR(1)    REFERENCES skill(s_code),
  score       INTEGER,
  test_t_id   VARCHAR(2)    REFERENCES town(t_id),
  PRIMARY KEY (c_id, s_code)
);

INSERT INTO aspiration VALUES (1,'A',1,'a');
INSERT INTO aspiration VALUES (1,'E',3,'b');
INSERT INTO aspiration VALUES (1,'Z',1,'bl');
INSERT INTO aspiration VALUES (2,'A',3,null);
INSERT INTO aspiration VALUES (3,'A',2,'b');
INSERT INTO aspiration VALUES (3,'Z',2,'bl');
INSERT INTO aspiration VALUES (4,'E',2,'c');
INSERT INTO aspiration VALUES (5,'Z',3,'d');
INSERT INTO aspiration VALUES (6,'Z',3,'e');
INSERT INTO aspiration VALUES (7,'E',3,'s');
INSERT INTO aspiration VALUES (8,'O',1,'t');

COMMIT;