CREATE TABLE Flughafen(
FlughafenName varchar(256) NOT NULL,
AdressID INTEGER NOT NULL,
primary key(FlughafenName),
CONSTRAINT fk_Flughafen FOREIGN KEY (AdressID) REFERENCES Adresse(AdressID),
CONSTRAINT ak_Flughafen UNIQUE (AdressID)
);

CREATE TABLE Fluggesellschaft(
Name varchar(256) NOT NULL,
Servicekennzahl INTEGER NOT NULL,
CHECK(0<Servicekennzahl),
CHECK(Servicekennzahl>11),
primary key(Name)
);

CREATE TABLE Ort(
OrtsID INTEGER NOT NULL,
Name varchar(256) NOT NULL,
FlughafenName varchar(256) NOT NULL,
Landname varchar(256) NOT NULL,
primary key(OrtsID),
CONSTRAINT fk1_Ort FOREIGN KEY(FlughafenName) REFERENCES Flughafen (FlughafenName),
CONSTRAINT fk2_Ort FOREIGN KEY(Landname) REFERENCES Land(Landname)
);

CREATE TABLE Adresse(
AdressID INTEGER NOT NULL,
Straße varchar(256) NOT NULL,
PLZ varchar(5) NOT NULL,
OrtsID INTEGER NOT NULL,
primary key(AdressID)
--CONSTRAINT fk_Adresse FOREIGN KEY(OrtsID) REFERENCES Ort(OrtsID)
);

CREATE TABLE Kunde(
KundenNr INTEGER NOT NULL,
TelefonNr varchar(256) NOT NULL,
Geburtsdatum DATE NOT NULL,
Email varchar(256) NOT NULL,
Name varchar(256) NOT NULL,
Vorname varchar(256) NOT NULL,
AdressID INTEGER NOT NULL,
IBAN INTEGER NOT NULL,
primary key(KundenNr),
CONSTRAINT fk1_Kunde FOREIGN KEY(AdressID) REFERENCES Adresse(AdressID),
CONSTRAINT fk2_Kunde FOREIGN KEY(IBAN) REFERENCES Bankverbindung(IBAN),
CONSTRAINT ak1_Kunde UNIQUE (AdressID),
CONSTRAINT ak2_Kunde UNIQUE (IBAN)
);

CREATE TABLE Touristenattraktion(
Name varchar(256) NOT NULL,
Beschreibung varchar(256) NOT NULL,
AdressID INTEGER NOT NULL,
Landname varchar(256) NOT NULL,
primary key(Name),
CONSTRAINT fk1_Touristenattraktion FOREIGN KEY(AdressID)REFERENCES Adresse(AdressID),
CONSTRAINT fk2_Touristenattraktion FOREIGN KEY(Landname) REFERENCES Land(Landname),
CONSTRAINT ak1_Touristenattraktion UNIQUE(AdressID)
--CONSTRAINT ak2_Touristenattraktion UNIQUE(Landname)
);

CREATE TABLE Land(
ISOcode varchar(256) NOT NULL,
Landname varchar(256) NOT NULL,
primary key(Landname)
);

CREATE TABLE Ferienwohnung(
FerienwohnungsID INTEGER NOT NULL,
Beschreibung varchar(256) NOT NULL,
Größe INTEGER NOT NULL,
Zimmeranzahl INTEGER NOT NULL,
Preis INTEGER NOT NULL,
AdressID INTEGER NOT NULL,
BilderID INTEGER,
ZusatzausstattungsID INTEGER,
CHECK(Zimmeranzahl>0),
CHECK(Größe>0),
CHECK(Preis>0),
primary key(FerienwohnungsID),
CONSTRAINT fk1_Ferienwohnung FOREIGN KEY (AdressID)REFERENCES Adresse(AdressID),
CONSTRAINT fk2_Ferienwohnung FOREIGN KEY (BilderID)REFERENCES Bilder(BilderID),
CONSTRAINT fk3_Ferienwohnung FOREIGN KEY (ZusatzausstattungsID) REFERENCES Zusatzausstattung(ZusatzausstattungsID),
CONSTRAINT ak_Ferienwohnung UNIQUE (AdressID)
);

CREATE TABLE Zusatzausstattung(
ZusatzausstattungsID INTEGER NOT NULL,
Beschreibung varchar(256) NOT NULL,
primary key(ZusatzausstattungsID)
);

CREATE TABLE Bilder(
BilderID INTEGER NOT NULL,
Dateipfad1 varchar(256) NOT NULL,
Dateipfad2 varchar(256),
Dateipfad3 varchar(256),
Dateipfad4 varchar(256),
primary key(BilderID)
--CONSTRAINT fk_Bilder FOREIGN KEY (FerienwohnungsID) REFERENCES Ferienwohnung(FerienwohnungsID)
);

CREATE TABLE Reservierung(
ReservierungsNr INTEGER NOT NULL,
Buchungsdatum DATE NOT NULL,
von DATE NOT NULL,
bis DATE NOT NULL,
Status varchar(256),
FerienwohnungsID INTEGER NOT NULL,
RechnungsNr INTEGER NOT NULL,
KundenNr INTEGER NOT NULL,
CHECK(von<bis),
CHECK(Status IN('verbindlich','unverbindlich')),
primary key(ReservierungsNR),
CONSTRAINT fk1_Reservierung FOREIGN KEY(FerienwohnungsID) REFERENCES Ferienwohnung(FerienwohnungsID),
CONSTRAINT fk2_Reservierung FOREIGN KEY(RechnungsNr) REFERENCES Rechnung (RechnungsNr),
CONSTRAINT fk3_Reservierung FOREIGN KEY(KundenNr) REFERENCES Kunde(KundenNr),
CONSTRAINT ak_Reservierung UNIQUE(RechnungsNr)
);

CREATE TABLE Rechnung(
RechnungsNR INTEGER,
Betrag INTEGER,
Rechnungsdatum DATE,
Zahlungseingang DATE,
CHECK(Betrag>0),
primary key(RechnungsNR)
);

CREATE TABLE Bankverbindung(
Kontonummer INTEGER NOT NULL,
BLZ INTEGER NOT NULL,
IBAN INTEGER NOT NULL,
BIC INTEGER NOT NULL,
CHECK(BLZ>0),
primary key(IBAN)
);

CREATE TABLE bietet_Flug_zu(
StartFlughafen varchar(256) NULL,
Name varchar(256) NOT NULL,
ZielFlughafen varchar(256) NOT NULL,
CHECK(StartFlughafen != ZielFlughafen),
primary key(StartFlughafen, Name, ZielFlughafen),
CONSTRAINT fk1_bietet_Flug_zu FOREIGN KEY (StartFlughafen) REFERENCES Flughafen (FlughafenName),
CONSTRAINT fk2_bietet_Flug_zu FOREIGN KEY (ZielFlughafen) REFERENCES Flughafen(FlughafenName),
CONSTRAINT fk3_bietet_Flug_zu FOREIGN KEY (Name) REFERENCES Fluggesellschaft(Name)
);

CREATE TABLE Entfernt_von(
Ort1 INTEGER NOT NULL,
Ort2 INTEGER NOT NULL,
km INTEGER NOT NULL,
CHECK(Ort1 != Ort2),
CHECK(km>0),
primary key(Ort1, Ort2),
CONSTRAINT fk_Entfernt_von FOREIGN KEY(Ort1) REFERENCES Ort(OrtsID),
CONSTRAINT fk2_Entfernt_von FOREIGN KEY(Ort2) REFERENCES Ort(OrtsID)
);

CREATE TABLE besitzt(
FerienwohnungsID INTEGER NOT NULL,
ZusatzausstattungsID INTEGER NOT NULL,
primary key(ZusatzausstattungsID, FerienwohnungsID),
CONSTRAINT fk1_besitzt FOREIGN KEY(FerienwohnungsID) REFERENCES Ferienwohnung(FerienwohnungsID),
CONSTRAINT fk2_besitzt FOREIGN KEY(ZusatzausstattungsID) REFERENCES Zusatzausstattung(ZusatzausstattungsID)
);

ALTER TABLE Adresse
ADD CONSTRAINT fk_Adresse FOREIGN KEY(OrtsID) REFERENCES Ort(OrtsID);

ALTER TABLE Adresse
DROP CONSTRAINT fk_Adresse;

SELECT * FROM Kunde;