Figuur 3

Flamefever · updated April 02, 2021 · SQL
CREATE TABLE Container (
    co_id INTEGER PRIMARY KEY,
    Content TEXT
);

CREATE TABLE Ship (
    s_id INTEGER PRIMARY KEY,
    s_name TEXT,
    created_at TEXT
);

CREATE TABLE Captain (
    ca_id INTEGER PRIMARY KEY,
    ca_name TEXT,
    license_since INTEGER
);

CREATE TABLE transports (
    co_id INTEGER,
    ca_id INTEGER,
    s_id INTEGER,
    PRIMARY KEY (co_id, ca_id, s_id),
    FOREIGN KEY (co_id) REFERENCES Container,
    FOREIGN KEY (s_id) REFERENCES Ship,
    FOREIGN KEY (ca_id) REFERENCES Captain
);


CREATE TABLE Location (
    l_id INTEGER PRIMARY KEY,
    l_name TEXT
);

CREATE TABLE has_as_destination (
    l_id INTEGER,
    co_id INTEGER,
    s_id INTEGER,
    ca_id INTEGER,
    PRIMARY KEY(l_id, ca_id, co_id, s_id),
    FOREIGN KEY (co_id) REFERENCES Container,
    FOREIGN KEY (s_id) REFERENCES Ship,
    FOREIGN KEY (ca_id) REFERENCES Captain,
    FOREIGN KEY (l_id) REFERENCES Location
);

CREATE TABLE departs_to (
    l_id INTEGER,
    co_id INTEGER,
    s_id INTEGER,
    ca_id INTEGER,
    PRIMARY KEY(l_id, ca_id, co_id, s_id),
    FOREIGN KEY (co_id) REFERENCES Container,
    FOREIGN KEY (s_id) REFERENCES Ship,
    FOREIGN KEY (ca_id) REFERENCES Captain,
    FOREIGN KEY (l_id) REFERENCES Location
);

Comments

Please sign up or log in to contribute to the discussion.