-- credits: André Restivo https://web.fe.up.pt/~arestivo
-- Revised by Gonçalo Gonçalves

PRAGMA foreign_keys = ON;
.headers on
.mode columns


-- Different kinds of fish
CREATE TABLE fish (
  ref INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,    -- name of the species
  average_weight REAL NOT NULL, -- average weight in kg
  price REAL NOT NULL           -- price on the market per kg
);

-- Boats on the fleet
CREATE TABLE boat (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,          -- name of the boat
  max_weight INTEGER NOT NULL, -- max weight of fish it can carry
  crew INTEGER NOT NULL,       -- max number of fishermen
  length INTEGER NOT NULL -- length of the boat in meters
);

-- Fishermen
CREATE TABLE fisherman (
  cod INTEGER PRIMARY KEY,
  name TEXT NOT NULL,      -- name of the fisherman
  city TEXT NOT NULL,      -- city of origin
  age INTEGER NOT NULL     -- age of the fisherman
);

-- Fishing trips
CREATE TABLE trip (
  num INTEGER PRIMARY KEY,
  date TEXT NOT NULL,                 -- date of the trip
  time TEXT NOT NULL,                 -- time of departure
  duration INTEGER NOT NULL,          -- duration in hours
  id INTEGER NOT NULL REFERENCES boat -- boat used on the trip
);

-- What fishermen went on each trip
CREATE TABLE went (
  cod INTEGER REFERENCES fisherman, -- this fishermen
  num INTEGER REFERENCES trip,      -- went on this trip
  PRIMARY KEY (cod, num)
);

-- Fishes caught on a trip
CREATE TABLE caught (
  num INTEGER REFERENCES trip, -- on this trip
  ref INTEGER REFERENCES fish, -- this species was caught
  weight INTEGER NOT NULL,     -- and the total weighted this much
  PRIMARY KEY (num, ref)
);

INSERT INTO fish (ref, name, average_weight, price) VALUES (1, 'Anchovy', 0.2, 5);
INSERT INTO fish (ref, name, average_weight, price) VALUES (2, 'Catfish', 18.5, 6);
INSERT INTO fish (ref, name, average_weight, price) VALUES (3, 'Dorade', 3.5, 12.5);
INSERT INTO fish (ref, name, average_weight, price) VALUES (4, 'Herring', 1.2, 7.5);
INSERT INTO fish (ref, name, average_weight, price) VALUES (5, 'Mackerel', 2, 8);
INSERT INTO fish (ref, name, average_weight, price) VALUES (6, 'Salmon', 12, 15);
INSERT INTO fish (ref, name, average_weight, price) VALUES (7, 'Sturgeon', 100, 12);
INSERT INTO fish (ref, name, average_weight, price) VALUES (8, 'Swordfish', 200, 14.5);

INSERT INTO boat (id, name, max_weight, crew, length) VALUES (1, 'Amerlin', 100000, 2, 10);
INSERT INTO boat (id, name, max_weight, crew, length) VALUES (2, 'Bait Master', 20000, 3, 6);
INSERT INTO boat (id, name, max_weight, crew, length) VALUES (3, 'Chum Lord', 50000, 3, 8);
INSERT INTO boat (id, name, max_weight, crew, length) VALUES (4, 'Debaitable', 40000, 2, 10);
INSERT INTO boat (id, name, max_weight, crew, length) VALUES (5, 'Fishfull Thinking', 45000, 2, 6);
INSERT INTO boat (id, name, max_weight, crew, length) VALUES (6, 'Get Reel', 30000, 3, 8);

INSERT INTO fisherman (cod, name, city, age) VALUES (1, 'Mark Davis', 'Mount Ida', 52);
INSERT INTO fisherman (cod, name, city, age)VALUES (2, 'Michael Iaconelli', 'Philadelphia', 44);
INSERT INTO fisherman (cod, name, city, age)VALUES (3, 'Skeet Reese', 'Auburn', 46);
INSERT INTO fisherman (cod, name, city, age)VALUES (4, 'Jay Yelas', 'Auburn', 45);
INSERT INTO fisherman (cod, name, city, age)VALUES (5, 'Takahiro Omori', 'Tokyo', 45);
INSERT INTO fisherman (cod, name, city, age)VALUES (6, 'Denny Brauer', 'Seward', 66);
INSERT INTO fisherman (cod, name, city, age)VALUES (7, 'Rick Clunn', 'Seward', 68);
INSERT INTO fisherman (cod, name, city, age)VALUES (8, 'Bobby Murray', 'Mount Ida', 35);

INSERT INTO trip (num, date, time, duration, id) VALUES (1, '2017-1-1', '06:00', 2, 1);
INSERT INTO trip (num, date, time, duration, id) VALUES (2, '2017-1-1', '07:30', 1, 2);
INSERT INTO trip (num, date, time, duration, id) VALUES (3, '2017-1-1', '08:00', 1, 4);
INSERT INTO trip (num, date, time, duration, id) VALUES (4, '2017-1-2', '05:00', 4, 3);
INSERT INTO trip (num, date, time, duration, id) VALUES (5, '2017-1-2', '05:30', 6, 4);
INSERT INTO trip (num, date, time, duration, id) VALUES (6, '2017-1-2', '06:00', 2, 2);
INSERT INTO trip (num, date, time, duration, id) VALUES (7, '2017-1-2', '06:30', 3, 6);
INSERT INTO trip (num, date, time, duration, id) VALUES (8, '2017-1-3', '07:00', 4, 2);
INSERT INTO trip (num, date, time, duration, id) VALUES (9, '2017-1-3', '07:30', 5, 4);
INSERT INTO trip (num, date, time, duration, id) VALUES (10, '2017-1-4', '08:00', 1, 2);
INSERT INTO trip (num, date, time, duration, id) VALUES (11, '2017-1-4', '09:30', 1, 3);
INSERT INTO trip (num, date, time, duration, id) VALUES (12, '2017-1-5', '06:00', 3, 6);

INSERT INTO went (cod, num) VALUES (1, 1);
INSERT INTO went (cod, num) VALUES (2, 1);
INSERT INTO went (cod, num) VALUES (3, 2);
INSERT INTO went (cod, num) VALUES (4, 2);
INSERT INTO went (cod, num) VALUES (5, 3);
INSERT INTO went (cod, num) VALUES (8, 3);
INSERT INTO went (cod, num) VALUES (6, 4);
INSERT INTO went (cod, num) VALUES (5, 5);
INSERT INTO went (cod, num) VALUES (8, 5);
INSERT INTO went (cod, num) VALUES (1, 5);
INSERT INTO went (cod, num) VALUES (2, 6);
INSERT INTO went (cod, num) VALUES (4, 6);
INSERT INTO went (cod, num) VALUES (3, 7);
INSERT INTO went (cod, num) VALUES (8, 8);
INSERT INTO went (cod, num) VALUES (6, 8);
INSERT INTO went (cod, num) VALUES (5, 9);
INSERT INTO went (cod, num) VALUES (1, 9);
INSERT INTO went (cod, num) VALUES (1, 10);
INSERT INTO went (cod, num) VALUES (3, 10);
INSERT INTO went (cod, num) VALUES (4, 11);
INSERT INTO went (cod, num) VALUES (8, 11);
INSERT INTO went (cod, num) VALUES (5, 12);

INSERT INTO caught (num, ref, weight) VALUES (1, 1, 10000);
INSERT INTO caught (num, ref, weight) VALUES (1, 5, 20000);
INSERT INTO caught (num, ref, weight) VALUES (2, 6, 15000);
INSERT INTO caught (num, ref, weight) VALUES (2, 4, 25000);
INSERT INTO caught (num, ref, weight) VALUES (3, 2, 8000);
INSERT INTO caught (num, ref, weight) VALUES (4, 8, 7000);
INSERT INTO caught (num, ref, weight) VALUES (5, 7, 12000);
INSERT INTO caught (num, ref, weight) VALUES (5, 8, 1000);
INSERT INTO caught (num, ref, weight) VALUES (6, 4, 3000);
INSERT INTO caught (num, ref, weight) VALUES (6, 2, 5000);
INSERT INTO caught (num, ref, weight) VALUES (7, 1, 8500);
INSERT INTO caught (num, ref, weight) VALUES (8, 6, 9000);
INSERT INTO caught (num, ref, weight) VALUES (9, 6, 13000);
INSERT INTO caught (num, ref, weight) VALUES (10, 4, 4000);
INSERT INTO caught (num, ref, weight) VALUES (11, 7, 5000);
INSERT INTO caught (num, ref, weight) VALUES (12, 8, 20000);
