ELwzZP2f

1DROP TYPE IF EXISTS country_location CASCADE;
2CREATE TYPE country_location AS ENUM ('America', 'Europe');
3
4DROP TABLE IF EXISTS country CASCADE;
5CREATE TABLE country (
6  country_id        SERIAL  PRIMARY KEY,
7  country_name      TEXT    UNIQUE NOT NULL,
8  country_location  country_location NOT NULL
9);
10
11DROP TABLE IF EXISTS port CASCADE;
12CREATE TABLE port (
13  port_id     SERIAL    PRIMARY KEY,
14  port_name   TEXT      NOT NULL,
15  country_id  INTEGER   NOT NULL REFERENCES country(country_id),
16  gps_coord   POINT     NOT NULL /* (lat, lan) */
17);
18
19DROP TABLE IF EXISTS plantation CASCADE;
20CREATE TABLE plantation (
21  id                SERIAL    PRIMARY KEY,
22  country_id        INTEGER   NOT NULL REFERENCES country(country_id),
23  manager           TEXT      NOT NULL,
24  delivery_port_id  INTEGER   NOT NULL REFERENCES port(port_id)
25);
26
27DROP TABLE IF EXISTS delivery CASCADE;
28CREATE TABLE delivery (
29  plantation_id INTEGER   NOT NULL REFERENCES plantation(id),
30  weight_tones  INTEGER   NOT NULL,
31  date          DATE      NOT NULL
32);
33
34DROP TABLE IF EXISTS ship CASCADE;
35CREATE TABLE ship (
36  ship_id     SERIAL PRIMARY KEY,
37  ship_name   TEXT NOT NULL,
38  carrying    INTEGER NOT NULL
39);
40
41DROP TABLE IF EXISTS customer CASCADE;
42CREATE TABLE customer (
43  customer_id   SERIAL  PRIMARY KEY,
44  customer_name TEXT    NOT NULL,
45  price         INTEGER NOT NULL
46);
47
48DROP TABLE IF EXISTS sea_delivery CASCADE;
49CREATE TABLE sea_delivery (
50  departure_port_id   INTEGER   NOT NULL REFERENCES port(port_id),
51  destination_port_id INTEGER   NOT NULL REFERENCES port(port_id),
52  ship_id             INTEGER   NOT NULL REFERENCES ship(ship_id),
53  departure_date      DATE      NOT NULL,
54  customer            INTEGER   NOT NULL REFERENCES customer(customer_id)
55);