CREATE TABLE Kunde (
  UserID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Vorname VARCHAR(15),
  Nachname VARCHAR(15),
  Email VARCHAR(30) ,
  Username VARCHAR(15) NOT NULL UNIQUE,
  Passwort VARCHAR(15) NOT NULL,
  CHECK(Passwort REGEXP '([A-Z]+[0-9]+[a-z]*)*'),
  CHECK(LENGTH(Passwort) > 5),
  CHECK(Email LIKE '%@%.%')
)ENGINE=InnoDB;

CREATE TABLE Mitarbeiter(
  UserID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  Vorname VARCHAR(15) NOT NULL,
  Nachname VARCHAR(15) NOT NULL,
  Email VARCHAR(30) NOT NULL DEFAULT 'support@online-booking.com',
  Gehalt INT NOT NULL DEFAULT 1500,
  CHECK(Email LIKE '%@%.%')
  )ENGINE=InnoDB;

CREATE TABLE Telefonat(
  KundenID INT NOT NULL,
  MitarbeiterID INT NOT NULL,
  DatumZeit DATETIME NOT NULL,
  Dauer TIME NOT NULL,
  PRIMARY KEY(KundenID, MitarbeiterID),
  CONSTRAINT FOREIGN KEY (KundenID) REFERENCES Kunde(UserID),
  CONSTRAINT FOREIGN KEY (MitarbeiterID) REFERENCES Mitarbeiter(UserID)
  )ENGINE=InnoDB;

CREATE TABLE Hotel(
  HotelID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  Ort VARCHAR(30) NOT NULL,
  PLZ INT NOT NULL,
  Adresse VARCHAR(40) NOT NULL
  )ENGINE=InnoDB;

CREATE TABLE Hotel_Telefon(
  HotelID INT NOT NULL,
  TelNr INT NOT NULL,
  PRIMARY KEY (HotelID, TelNr),
  CONSTRAINT fk_ht FOREIGN KEY(HotelID) REFERENCES Hotel(HotelID)
  )ENGINE=InnoDB;

CREATE TABLE Hotel_Email(
  HotelID INT NOT NULL,
  Email VARCHAR(30) NOT NULL,
  PRIMARY KEY (HotelID, Email),
  CONSTRAINT fk_hi FOREIGN KEY(HotelID) REFERENCES Hotel(HotelID),
  CHECK(Email LIKE '%@%')
  )ENGINE=InnoDB;

CREATE TABLE Zimmer(
  HotelID INT NOT NULL,
  ZimmerNr INT NOT NULL,
  Kategorie VARCHAR(20),
  Preis INT NOT NULL,
  MaxPersonen INT NOT NULL,
  SuiteRäume INT,
  PRIMARY KEY (HotelID, ZimmerNr),
  CONSTRAINT fk_hiz FOREIGN KEY(HotelID) REFERENCES Hotel(HotelID)
  )ENGINE=InnoDB;

CREATE TABLE Mietauto(
  AutoID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  Typ VARCHAR(50),
  Baujahr INT,
  Preis INT NOT NULL
  )ENGINE=InnoDB;

CREATE TABLE Foto(
  FotoID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  Titel VARCHAR(20),
  Beschreibung VARCHAR(50),
  Filename VARCHAR(100) NOT NULL,
  HotelID INT NOT NULL,
  AutoID INT NOT NULL,
  CONSTRAINT hif FOREIGN KEY(HotelID) REFERENCES Hotel(HotelID),
  CONSTRAINT aif FOREIGN KEY(AutoID) REFERENCES Mietauto(AutoID)
  )ENGINE=InnoDB;

CREATE TABLE Abrechnungsart(
  UserID INT NOT NULL,
  ArtID INT NOT NULL UNIQUE AUTO_INCREMENT,
  Besitzername VARCHAR(30),
  PRIMARY KEY (UserID, ArtID),
  CONSTRAINT FOREIGN KEY(UserID) REFERENCES Kunde(UserID)
  )ENGINE=InnoDB;