CREATE TABLE Hafen (
    HNR INT PRIMARY KEY,
    ORT VARCHAR(50) NOT NULL,
    Grundsteinlegung DATE NOT NULL
);

CREATE TABLE Matrose (
    MNR INT PRIMARY KEY,
    Nachname VARCHAR(50) NOT NULL,
    Geburtsdatum DATE NOT NULL,
    Ausbildungsort INT NOT NULL,
    CONSTRAINT fk_person_hafen FOREIGN KEY (Ausbildungsort) REFERENCES Hafen (HNR)
);

CREATE TABLE Schiff (
    SNR INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Bruttoregistertonnen INT NOT NULL,
    Stabellauf DATE NOT NULL,
    Heimathafen INT NOT NULL,
    CONSTRAINT fk_schiff_hafen FOREIGN KEY (Heimathafen) REFERENCES Hafen (HNR)
);

CREATE TABLE anheuern(
   Matrose INT,
   Schiff INT,
   Dienstbeginn DATE NOT NULL,
   Jahresgehalt INT NOT NULL,
   CONSTRAINT pk_anheuern PRIMARY KEY (Matrose, Schiff),
   CONSTRAINT fk_anheuer_matrose FOREIGN KEY (Matrose) REFERENCES Matrose (MNR),
   CONSTRAINT fk_anheuer_schiff FOREIGN KEY (Schiff) REFERENCES Schiff (SNR)
);

INSERT INTO Hafen (HNR, ORT, Grundsteinlegung) VALUES
(14, "Annapolis", "1649-01-30"),
(13,"Qingdao", "1600-12-01"),
(12, "Oslo", "1735-09-16"),
(11, "London", "103-11-12"),
(10, "Rotterdam", "1400-01-01"),
(9,"Dubai", "700-12-01"),
(8,"Newport ", "1886-10-02"),
(7,"Guangzhou", "1711-02-28"),
(6,"Ningbo-Zhoushan-Hafen","1849-04-30"),
(5,"Busan", "1512-03-01"),
(4,"Shenzhen", "900-11-02"),
(3,"Hongkong", "1517-05-04"),
(2,"Singapur", "201-01-01"),
(1,"Schanghai", "960-01-10" );

INSERT INTO Matrose (MNR, Nachname, Geburtsdatum, Ausbildungsort) VALUES
(6, "Poix", "1916-08-13",14),
(5, "Drake", "1530-03-23",11),
(4, "Hawkins", "1532-02-11", 11),
(3, "Heyerdahl",  "1914-10-06", 12),
(2, "Franklin", "1786-04-15", 11),
(1, "Popeye", "1929-01-17",10);



INSERT INTO Schiff (SNR, Name, Bruttoregistertonnen, Stabellauf, Heimathafen) VALUES
(6, "USS Enterprise (CVN-65)", 75000, "1957-02-04", 8),
(5, "Jesus of Luebeck", 700, "1540-12-24", 11),
(4, "Swan",25,"1568-02-11",10),
(3, "HMS Erebus",300,"1822-02-17",11),
(2, "Ra",1,"1975-03-23",12),
(1, "HMS Terror", 326 , "1814-08-09", 11);

INSERT INTO anheuern (Matrose, Schiff, Dienstbeginn, Jahresgehalt) VALUES
(6, 6, "1957-04-01", 159075),
(4, 5, "1567-04-01", 130000),
(5, 4, "1570-10-04", 100000),
(2, 3,  "1846-03-11", 6237),
(3, 2,  "1957-04-01", 0),
(2, 1,  "1845-05-19", 6023);
 

/*
Drop Table anheuern;
Drop Table Schiff;
Drop Table Matrose;
Drop Table Hafen;
*/
