<sub class="descriptionSection">20-01-2025 10:32:am // #Tag // [[Databases]]</sub>
____
```sql
START TRANSACTION;
DROP DATABASE IF EXISTS ArzneiVersand;
CREATE DATABASE ArzneiVersand;
USE ArzneiVersand;
CREATE TABLE Kunde (
K_ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
Vorname VARCHAR(50) NOT NULL,
Nachname VARCHAR(50) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_german1_ci;
CREATE TABLE Paket(
P_ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
L_ID INT NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_german1_ci;
CREATE TABLE Zusteller(
Z_ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
Name VARCHAR(50) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_german1_ci;
CREATE table Arzneimittel(
A_ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
Name VARCHAR(50) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_german1_ci;
CREATE TABLE ArzneimittelPaketLink(
A_ID INT NOT NULL,
P_ID INT NOT NULL,
Menge INT NOT NULL,
CONSTRAINT FOREIGN KEY (A_ID) REFERENCES Arzneimittel(A_ID),
CONSTRAINT FOREIGN KEY (P_ID) REFERENCES Paket(P_ID),
CONSTRAINT PRIMARY KEY (A_ID, P_ID)
)ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_german1_ci;
CREATE TABLE Lieferung(
L_ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
K_ID INT NOT NULL,
Z_ID INT NOT NULL,
L_Datum DATE NOT NULL,
CONSTRAINT FOREIGN KEY (K_ID) REFERENCES Kunde(K_ID),
CONSTRAINT FOREIGN KEY (Z_ID) REFERENCES Zusteller(Z_ID)
)ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_german1_ci;
ALTER TABLE Paket ADD CONSTRAINT FOREIGN KEY (L_ID) REFERENCES Lieferung(L_ID);
COMMIT;
```