TED609U1

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(Listen_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);
68
69ALTER TABLE Kunde ADD
70    Mob_Tel int not null
71;
72
73ALTER TABLE Kunde MODIFY
74    Mob_Tel varchar(255) not null
75;
76
77ALTER TABLE Kunde drop
78    Mob_Tel
79;
80
81ALTER TABLE Kunde drop
82    CONSTRAINT kunde_ibfk_1
83;
84
85ALTER TABLE Kunde ADD 
86    foreign key(PLZ_ID) references PLZ(PLZ_ID)
87;