LF6-AE-LS-3.2 Autovermietung Database

CREATE DATABASE IF NOT EXISTS Autovermietung_DEV DEFAULT CHARACTER SET utf8;
USE Autovermietung_DEV;

CREATE TABLE IF NOT EXISTS Ort
(Id INTEGER NOT NULL,
Postleitzahl VARCHAR(10) NOT NULL,
Ortsname VARCHAR(50) NOT NULL,
PRIMARY KEY (Id)
);

CREATE TABLE IF NOT EXISTS Zentrale
(Id INTEGER NOT NULL,
Ort INTEGER NOT NULL,
PRIMARY KEY (Id),
FOREIGN KEY (Ort) REFERENCES Ort(Id)
)
ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Filiale
(Id INTEGER NOT NULL,
Ort INTEGER NOT NULL,
Zentrale INTEGER NOT NULL,
PRIMARY KEY (Id),
FOREIGN KEY (Ort) REFERENCES Ort(Id),
FOREIGN KEY (Zentrale) REFERENCES Zentrale(Id)
)
ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Fahrzeug_Status
(Id INTEGER NOT NULL,
Beschreibung VARCHAR(20) NOT NULL,
PRIMARY KEY (Id)
)
ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Fahrzeug
(Id INTEGER NOT NULL,
Hersteller VARCHAR(50) NOT NULL,
Modell VARCHAR(50) NOT NULL,
Kilometerstand FLOAT(8,2) DEFAULT 0.00,
Status INTEGER NOT NULL,
Filiale INTEGER,
Zentrale INTEGER,
PRIMARY KEY (Id),
FOREIGN KEY (Status) REFERENCES Fahrzeug_Status(Id),
FOREIGN KEY (Filiale) REFERENCES Filiale(Id),
FOREIGN KEY (Zentrale) REFERENCES Zentrale(Id)
)
ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Kunde
(Id INTEGER NOT NULL,
Name VARCHAR(50) NOT NULL,
Wohnort INTEGER NOT NULL,
Stammfiliale INTEGER NOT NULL,
PRIMARY KEY (Id),
FOREIGN KEY (Wohnort) REFERENCES Ort(Id),
FOREIGN KEY (Stammfiliale) REFERENCES Filiale(Id)
)
ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Vermietvorgang
(Id INTEGER NOT NULL,
Verliehen_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
Rueckgabe_am TIMESTAMP,
Fahrzeug INTEGER NOT NULL,
Kunde INTEGER NOT NULL,
Filiale INTEGER NOT NULL,
PRIMARY KEY (Id),
FOREIGN KEY (Fahrzeug) REFERENCES Fahrzeug(Id),
FOREIGN KEY (Kunde) REFERENCES Kunde(Id),
FOREIGN KEY (Filiale) REFERENCES Filiale(Id)
)
ENGINE=InnoDB;