Aufgabe_1_3

--** Teilaufgabe 3.1
ALTER TABLE auftrag
    ADD fk_kunde SMALLINT UNSIGNED ZEROFILL,
    ADD FOREIGN KEY (fk_kunde)
    REFERENCES kunde(kd_nr)
    ON UPDATE CASCADE ON DELETE SET NULL;

--** Teilaufgabe 3.2
ALTER TABLE artikel
    ADD fk_kategorie TINYINT(5) UNSIGNED,
    ADD FOREIGN KEY (fk_kategorie)
    REFERENCES kategorie(kat_nr)
    ON UPDATE CASCADE ON DELETE CASCADE;

--** Teilaufgabe 3.3
ALTER TABLE artikel
    ADD fk_hersteller TINYINT(3) UNSIGNED,
    ADD FOREIGN KEY (fk_hersteller)
    REFERENCES hersteller(herst_nr)
    ON UPDATE CASCADE ON DELETE SET NULL;

--** Teilaufgabe 3.4
CREATE TABLE stadt(
    stadt_nr TINYINT UNSIGNED,
    stadt VARCHAR(30),
    lat DOUBLE(9,6),
    lon DOUBLE(9,6),
    PRIMARY KEY (stadt_nr)
);

CREATE TABLE shoptyp(
    typ_nr TINYINT UNSIGNED,
    shoptyp VARCHAR(30),
    PRIMARY KEY (typ_nr)
);

CREATE TABLE shop(
    shop_nr SMALLINT UNSIGNED,
    strasse VARCHAR(30),
    plz CHAR(5),
    fk_shoptyp TINYINT UNSIGNED,
    FOREIGN KEY (fk_shoptyp)
    REFERENCES shoptyp(typ_nr)
    ON DELETE CASCADE,
    fk_stadt TINYINT UNSIGNED,
    FOREIGN KEY (fk_stadt)
    REFERENCES stadt(stadt_nr)
    ON DELETE CASCADE,
    PRIMARY KEY (shop_nr)
);