8VQdqu77

1drop database if exists shop;
2create database shop;
3
4use shop;
5
6create table PLZ(
7    PLZ_ID int unsigned not null auto_increment,
8    PLZ smallint(4) unsigned not null,
9    Ort varchar(255) not null,
10
11    primary key (PLZ_ID),
12    unique(PLZ, Ort)
13);
14
15create table Kunde (
16    Kunden_ID int unsigned not null auto_increment,
17    PLZ_ID int unsigned not null,
18    Name varchar(255) not null,
19    Vorname varchar(255) not null,
20    adresse varchar(255) not null,
21    email varchar(255) not null unique,
22    tel varchar(255) not null,
23
24    primary key(Kunden_ID),
25    foreign key(PLZ_ID) references PLZ(PLZ_ID) on update cascade on delete cascade
26);
27
28create table Produktkategorie(
29    Kategorie_ID int unsigned not null auto_increment,
30    Kategorie_Name varchar(255) not null unique,
31    Anzahl int not null,
32
33    primary key(Kategorie_ID)
34);
35
36create table Produkt(
37    Produkt_ID int unsigned not null auto_increment,
38    Kategorie_ID int unsigned not null,
39    Produkt_Name varchar(255) not null,
40    Produkt_Beschreibung varchar(255) not null,
41    Preis decimal(6,2) not null,
42
43    primary key(Produkt_ID),
44    foreign key(Kategorie_ID) references Produktkategorie(Kategorie_ID) on update cascade on delete cascade
45);
46
47create table Bestellung(
48    Bestellung_ID int unsigned not null auto_increment,
49    Kunden_ID int unsigned not null,
50    Bestelldatum datetime not null default current_timestamp,
51    Lieferdatum datetime null,
52
53    primary key(Bestellung_ID),
54    foreign key(Kunden_ID) references Kunde(Kunden_ID) on update cascade on delete cascade
55);
56
57create table Produkt_Bestellung(
58    Prod_Best_ID int unsigned not null auto_increment,
59    Bestellung_ID int unsigned not null,
60    Produkt_ID int unsigned not null,
61    Anzahl int unsigned not null default 1,
62    preis decimal(6,2) null,
63
64    primary key(Prod_Best_ID),
65    foreign key(Produkt_ID) references Produkt(Produkt_ID) on update cascade on delete cascade,
66    foreign key(Bestellung_ID) references Bestellung(Bestellung_ID) on update cascade on delete cascade
67);