Spacecraft database sample #2

--
-- Stepik Course.
-- Spacecraft database sample #2.
--

DROP TABLE Flight;
DROP TABLE Planet;
DROP TABLE Commander;

CREATE TABLE Planet(
  id SERIAL PRIMARY KEY,
  name TEXT UNIQUE,
  distance NUMERIC(5,2),
  galaxy INT CHECK(galaxy > 0)
);

CREATE TABLE Commander(
  id SERIAL PRIMARY KEY,
  name TEXT
);

CREATE TABLE Flight(
  id INT PRIMARY KEY,
  planet_id INT REFERENCES Planet,
  commander_id INT REFERENCES Commander,
  start_date DATE,
  UNIQUE(commander_id, start_date)
);

-- Table Planet
INSERT INTO planet (id, name, distance, galaxy) VALUES (1,  'Carehigh',    172.01, (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (2,  'Medredfan',   130.84, (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (3,  'Dandindox',   13.37,  (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (4,  'Kanron',      3.10,   (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (5,  'Tanway',      75.28,  (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (6,  'Vilakix',     40.57,  (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (7,  'Bioflex',     89.08,  (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (8,  'Goldenflex',  131.70, (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (9,  'Opeholding',  90.44,  (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (10, 'Tanzone',     52.83,  (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (11, 'Flexplus',    105.87, (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (12, 'Volholdings', 5.99,   (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (13, 'Hotla',       195.26, (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (14, 'Trioline',    15.58,  (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (15, 'Stimex',      164.43, (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (16, 'Hotanbam',    126.37, (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (17, 'Rancare',     57.87,  (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (18, 'Doubleice',   131.20, (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (19, 'Geocom',      114.40, (random() * 4 + 1)::int);
INSERT INTO planet (id, name, distance, galaxy) VALUES (20, 'Techiplus',   164.72, (random() * 4 + 1)::int);

-- Table Commander
INSERT INTO commander VALUES (1, 'Johnny Silverbeard');
INSERT INTO commander VALUES (2, 'Salty Ravenbeard');
INSERT INTO commander VALUES (3, 'Henri Shelley');
INSERT INTO commander VALUES (4, 'Bloody Ravenbeard');
INSERT INTO commander VALUES (5, 'Hungry Redblade');
INSERT INTO commander VALUES (6, 'Gold Stoker');
INSERT INTO commander VALUES (7, 'Thomas Silvergrim');
INSERT INTO commander VALUES (8, 'Donna Gull');
INSERT INTO commander VALUES (9, 'Billy Dreadbeard');
INSERT INTO commander VALUES (10, 'James Scarlet');

-- Table Flight
INSERT INTO flight VALUES (1,  2,  8,  '2047-10-28');
INSERT INTO flight VALUES (2,  5,  6,  '2047-12-16');
INSERT INTO flight VALUES (3,  13, 2,  '2048-02-20');
INSERT INTO flight VALUES (4,  6,  2,  '2048-03-02');
INSERT INTO flight VALUES (5,  12, 5,  '2048-03-20');
INSERT INTO flight VALUES (6,  9,  8,  '2048-03-26');
INSERT INTO flight VALUES (7,  14, 9,  '2048-05-01');
INSERT INTO flight VALUES (8,  3,  2,  '2048-05-22');
INSERT INTO flight VALUES (9,  6,  8,  '2048-05-28');
INSERT INTO flight VALUES (10, 6,  7,  '2048-06-04');
INSERT INTO flight VALUES (11, 4,  7,  '2048-06-17');
INSERT INTO flight VALUES (12, 2,  10, '2048-08-10');
INSERT INTO flight VALUES (13, 4,  8,  '2048-08-18');
INSERT INTO flight VALUES (14, 6,  3,  '2048-09-03');
INSERT INTO flight VALUES (15, 5,  10, '2048-10-08');
INSERT INTO flight VALUES (16, 8,  6,  '2048-10-20');
INSERT INTO flight VALUES (17, 9,  6,  '2048-11-23');
INSERT INTO flight VALUES (18, 7,  7,  '2048-12-17');
INSERT INTO flight VALUES (19, 13, 1,  '2048-12-23');
INSERT INTO flight VALUES (20, 10, 5,  '2048-12-28');