9FPfRXpg

1CREATE DATABASE IF NOT EXISTS Autovermietung_DEV DEFAULT CHARACTER SET utf8;
2USE Autovermietung_DEV;
3
4CREATE TABLE IF NOT EXISTS Ort
5(Id INTEGER NOT NULL,
6Postleitzahl VARCHAR(10) NOT NULL,
7Ortsname VARCHAR(50) NOT NULL,
8PRIMARY KEY (Id)
9);
10
11CREATE TABLE IF NOT EXISTS Zentrale
12(Id INTEGER NOT NULL,
13Ort INTEGER NOT NULL,
14PRIMARY KEY (Id),
15FOREIGN KEY (Ort) REFERENCES Ort(Id)
16)
17ENGINE=InnoDB;
18
19CREATE TABLE IF NOT EXISTS Filiale
20(Id INTEGER NOT NULL,
21Ort INTEGER NOT NULL,
22Zentrale INTEGER NOT NULL,
23PRIMARY KEY (Id),
24FOREIGN KEY (Ort) REFERENCES Ort(Id),
25FOREIGN KEY (Zentrale) REFERENCES Zentrale(Id)
26)
27ENGINE=InnoDB;
28
29CREATE TABLE IF NOT EXISTS Fahrzeug_Status
30(Id INTEGER NOT NULL,
31Beschreibung VARCHAR(20) NOT NULL,
32PRIMARY KEY (Id)
33)
34ENGINE=InnoDB;
35
36CREATE TABLE IF NOT EXISTS Fahrzeug
37(Id INTEGER NOT NULL,
38Hersteller VARCHAR(50) NOT NULL,
39Modell VARCHAR(50) NOT NULL,
40Kilometerstand FLOAT(8,2) DEFAULT 0.00,
41Status INTEGER NOT NULL,
42Filiale INTEGER,
43Zentrale INTEGER,
44PRIMARY KEY (Id),
45FOREIGN KEY (Status) REFERENCES Fahrzeug_Status(Id),
46FOREIGN KEY (Filiale) REFERENCES Filiale(Id),
47FOREIGN KEY (Zentrale) REFERENCES Zentrale(Id)
48)
49ENGINE=InnoDB;
50
51CREATE TABLE IF NOT EXISTS Kunde
52(Id INTEGER NOT NULL,
53Name VARCHAR(50) NOT NULL,
54Wohnort INTEGER NOT NULL,
55Stammfiliale INTEGER NOT NULL,
56PRIMARY KEY (Id),
57FOREIGN KEY (Wohnort) REFERENCES Ort(Id),
58FOREIGN KEY (Stammfiliale) REFERENCES Filiale(Id)
59)
60ENGINE=InnoDB;
61
62CREATE TABLE IF NOT EXISTS Vermietvorgang
63(Id INTEGER NOT NULL,
64Verliehen_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
65Rueckgabe_am TIMESTAMP,
66Fahrzeug INTEGER NOT NULL,
67Kunde INTEGER NOT NULL,
68Filiale INTEGER NOT NULL,
69PRIMARY KEY (Id),
70FOREIGN KEY (Fahrzeug) REFERENCES Fahrzeug(Id),
71FOREIGN KEY (Kunde) REFERENCES Kunde(Id),
72FOREIGN KEY (Filiale) REFERENCES Filiale(Id)
73)
74ENGINE=InnoDB;