Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- -----------------------------------------------------
- -- Schema toprast
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS "allerg_prod" ;
- DROP TABLE IF EXISTS "allergene" ;
- DROP TABLE IF EXISTS "rechn_prod" ;
- DROP TABLE IF EXISTS "produkte" ;
- DROP TABLE IF EXISTS "rechnungen" ;
- DROP TABLE IF EXISTS "filialen" ;
- DROP TABLE IF EXISTS "kunden" ;
- DROP TABLE IF EXISTS "mitarbeiter" ;
- DROP TABLE IF EXISTS "personen" ;
- DROP TABLE IF EXISTS "adressen" ;
- -- -----------------------------------------------------
- -- Table "adressen"
- -- -----------------------------------------------------
- CREATE TABLE IF NOT EXISTS "adressen" (
- "addr_id" SERIAL NOT NULL,
- "strasse" VARCHAR(64) NOT NULL,
- "plz" VARCHAR(8) NOT NULL,
- "ort" VARCHAR(64) NOT NULL,
- "hausnummer" VARCHAR(10) NULL,
- PRIMARY KEY ("addr_id"))
- ;
- -- -----------------------------------------------------
- -- Table "personen"
- -- -----------------------------------------------------
- CREATE TABLE IF NOT EXISTS "personen" (
- "pers_id" SERIAL NOT NULL,
- "vorname" VARCHAR(45) NULL,
- "nachname" VARCHAR(45) NULL,
- "geschlecht" CHAR(1) CHECK (geschlecht IN('m','w')),
- "fk_addr_id" INT NULL,
- PRIMARY KEY ("pers_id"),
- CONSTRAINT "fk_personen_adressen"
- FOREIGN KEY ("fk_addr_id")
- REFERENCES "adressen" ("addr_id")
- ON DELETE SET NULL
- ON UPDATE SET NULL)
- ;
- -- -----------------------------------------------------
- -- Table "kunden"
- -- -----------------------------------------------------
- CREATE TABLE IF NOT EXISTS "kunden" (
- "fk_pers_id" INT NOT NULL,
- PRIMARY KEY ("fk_pers_id"),
- CONSTRAINT "fk_kunden_personen1"
- FOREIGN KEY ("fk_pers_id")
- REFERENCES "personen" ("pers_id")
- ON DELETE CASCADE
- ON UPDATE CASCADE)
- ;
- -- -----------------------------------------------------
- -- Table "filialen"
- -- -----------------------------------------------------
- CREATE TABLE IF NOT EXISTS "filialen" (
- "f_id" SERIAL NOT NULL,
- "uid" VARCHAR(12) NULL,
- "name" VARCHAR(45) NOT NULL,
- "telefon" VARCHAR(45) NULL,
- "fax" VARCHAR(45) NULL,
- "fk_addr_id" INT NOT NULL,
- "geschlossen" BOOLEAN NOT NULL DEFAULT FALSE,
- PRIMARY KEY ("f_id"),
- CONSTRAINT "fk_filialen_adressen1"
- FOREIGN KEY ("fk_addr_id")
- REFERENCES "adressen" ("addr_id")
- ON DELETE NO ACTION
- ON UPDATE NO ACTION)
- ;
- -- -----------------------------------------------------
- -- Table "mitarbeiter"
- -- -----------------------------------------------------
- CREATE TABLE IF NOT EXISTS "mitarbeiter" (
- "fk_pers_id" INT NOT NULL,
- "eintrittsdatum" DATE NOT NULL,
- "austrittsdatum" DATE NULL,
- PRIMARY KEY ("fk_pers_id"),
- CONSTRAINT "fk_mitarbeiter_personen1"
- FOREIGN KEY ("fk_pers_id")
- REFERENCES "personen" ("pers_id")
- ON DELETE NO ACTION
- ON UPDATE NO ACTION)
- ;
- -- -----------------------------------------------------
- -- Table "rechnungen"
- -- -----------------------------------------------------
- CREATE TABLE IF NOT EXISTS "rechnungen" (
- "rechn_nr" SERIAL NOT NULL,
- "datum" DATE NOT NULL,
- "uhrzeit" TIME NOT NULL,
- "fk_f_id" INT NOT NULL,
- "fk_kunden_pers_id" INT NULL,
- "fk_mitarbeiter_pers_id" INT NULL,
- PRIMARY KEY ("rechn_nr"),
- CONSTRAINT "fk_rechnungen_filialen1"
- FOREIGN KEY ("fk_f_id")
- REFERENCES "filialen" ("f_id")
- ON DELETE NO ACTION
- ON UPDATE NO ACTION,
- CONSTRAINT "fk_rechnungen_kunden1"
- FOREIGN KEY ("fk_kunden_pers_id")
- REFERENCES "kunden" ("fk_pers_id")
- ON DELETE SET NULL
- ON UPDATE SET NULL,
- CONSTRAINT "fk_rechnungen_mitarbeiter1"
- FOREIGN KEY ("fk_mitarbeiter_pers_id")
- REFERENCES "mitarbeiter" ("fk_pers_id")
- ON DELETE SET NULL
- ON UPDATE SET NULL)
- ;
- -- -----------------------------------------------------
- -- Table "produkte"
- -- -----------------------------------------------------
- CREATE TABLE IF NOT EXISTS "produkte" (
- "produkt_id" SERIAL NOT NULL,
- "name" VARCHAR(64) NOT NULL,
- "preis" REAL NOT NULL,
- "mwst" REAL NOT NULL,
- "ausgelaufen" BOOLEAN NOT NULL DEFAULT FALSE,
- PRIMARY KEY ("produkt_id"))
- ;
- -- -----------------------------------------------------
- -- Table "rechn_prod"
- -- -----------------------------------------------------
- CREATE TABLE IF NOT EXISTS "rechn_prod" (
- "id" SERIAL NOT NULL,
- "preis" REAL NOT NULL,
- "mwst" REAL NOT NULL,
- "fk_prod_id" INT NULL,
- "fk_rechn_nr" INT NOT NULL,
- PRIMARY KEY ("id"),
- CONSTRAINT "fk_rechn_prod_rechnungen1"
- FOREIGN KEY ("fk_rechn_nr")
- REFERENCES "rechnungen" ("rechn_nr")
- ON DELETE CASCADE
- ON UPDATE CASCADE,
- CONSTRAINT "fk_rechn_prod_produkte1"
- FOREIGN KEY ("fk_prod_id")
- REFERENCES "produkte" ("produkt_id")
- ON DELETE SET NULL
- ON UPDATE SET NULL)
- ;
- -- -----------------------------------------------------
- -- Table "allergene"
- -- -----------------------------------------------------
- CREATE TABLE IF NOT EXISTS "allergene" (
- "kuerzel" CHAR(1) NOT NULL,
- "bezeichnung" VARCHAR(64) NOT NULL,
- PRIMARY KEY ("kuerzel"))
- ;
- -- -----------------------------------------------------
- -- Table "allerg_prod"
- -- -----------------------------------------------------
- CREATE TABLE IF NOT EXISTS "allerg_prod" (
- "fk_kuerzel" CHAR(1) NOT NULL,
- "fk_produkt_id" INT NOT NULL,
- PRIMARY KEY ("fk_kuerzel", "fk_produkt_id"),
- CONSTRAINT "fk_allerg_prod_allergene1"
- FOREIGN KEY ("fk_kuerzel")
- REFERENCES "allergene" ("kuerzel")
- ON DELETE CASCADE
- ON UPDATE CASCADE,
- CONSTRAINT "fk_allerg_prod_produkte1"
- FOREIGN KEY ("fk_produkt_id")
- REFERENCES "produkte" ("produkt_id")
- ON DELETE CASCADE
- ON UPDATE CASCADE)
- ;
- -- -----------------------------------------------------
- -- Table "vornamen"
- -- -----------------------------------------------------
- CREATE TABLE IF NOT EXISTS "vornamen" (
- "vn_id" serial,
- "vorname" text NOT NULL,
- "geschlecht" CHAR(1) CHECK (geschlecht IN('m','w')),
- PRIMARY KEY ("vn_id")
- );
- -- -----------------------------------------------------
- -- Table "nachnamen"
- -- -----------------------------------------------------
- CREATE TABLE IF NOT EXISTS "nachnamen" (
- "nn_id" serial,
- "nachname" text NOT NULL,
- PRIMARY KEY ("nn_id")
- );
- -- -------------------------------------------------------------------------------------------------------
- -- Inserts
- -- -------------------------------------------------------------------------------------------------------
- -- insert nachnamen
- INSERT INTO vornamen ("vn_id", "vorname", "geschlecht") VALUES
- (DEFAULT, 'Liam', 'm'),
- (DEFAULT, 'Milan', 'm'),
- (DEFAULT, 'Elias', 'm'),
- (DEFAULT, 'Julian', 'm'),
- (DEFAULT, 'Levi', 'm'),
- (DEFAULT, 'Elias', 'm'),
- (DEFAULT, 'Henry', 'm'),
- (DEFAULT, 'Oskar', 'm'),
- (DEFAULT, 'Sven', 'm'),
- (DEFAULT, 'Levi', 'm'),
- (DEFAULT, 'Hannes', 'm'),
- (DEFAULT, 'Artur', 'm'),
- (DEFAULT, 'Noel', 'm'),
- (DEFAULT, 'Pascal', 'm'),
- (DEFAULT, 'Maurice', 'm'),
- (DEFAULT, 'David', 'm'),
- (DEFAULT, 'Jan', 'm'),
- (DEFAULT, 'Anton', 'm'),
- (DEFAULT, 'Lars', 'm'),
- (DEFAULT, 'Lias', 'm'),
- (DEFAULT, 'Jeremy', 'm'),
- (DEFAULT, 'Jonathan', 'm'),
- (DEFAULT, 'Tim', 'm'),
- (DEFAULT, 'Liam', 'm'),
- (DEFAULT, 'Tjark', 'm'),
- (DEFAULT, 'Carl', 'm'),
- (DEFAULT, 'Finn', 'm'),
- (DEFAULT, 'Luca', 'm'),
- (DEFAULT, 'Jerome', 'm'),
- (DEFAULT, 'Joey', 'm'),
- (DEFAULT, 'Felix', 'm'),
- (DEFAULT, 'Mika', 'm'),
- (DEFAULT, 'Ben', 'm'),
- (DEFAULT, 'Mauricio', 'm'),
- (DEFAULT, 'Emil', 'm'),
- (DEFAULT, 'Johannes', 'm'),
- (DEFAULT, 'Mattis', 'm'),
- (DEFAULT, 'Liam', 'm'),
- (DEFAULT, 'Fabian', 'm'),
- (DEFAULT, 'Jaden', 'm'),
- (DEFAULT, 'Vincent', 'm'),
- (DEFAULT, 'Nils', 'm'),
- (DEFAULT, 'Daniel', 'm'),
- (DEFAULT, 'Jonas', 'm'),
- (DEFAULT, 'Benjamin', 'm'),
- (DEFAULT, 'Jacob', 'm'),
- (DEFAULT, 'Michael', 'm'),
- (DEFAULT, 'Timo', 'm'),
- (DEFAULT, 'Julien', 'm'),
- (DEFAULT, 'Alexander', 'm'),
- (DEFAULT, 'Jette', 'w'),
- (DEFAULT, 'Fiona', 'w'),
- (DEFAULT, 'Lena', 'w'),
- (DEFAULT, 'Stella', 'w'),
- (DEFAULT, 'Ida', 'w'),
- (DEFAULT, 'Lotta', 'w'),
- (DEFAULT, 'Alina', 'w'),
- (DEFAULT, 'Julia', 'w'),
- (DEFAULT, 'Marie', 'w'),
- (DEFAULT, 'Finja', 'w'),
- (DEFAULT, 'Anna', 'w'),
- (DEFAULT, 'Amalia', 'w'),
- (DEFAULT, 'Lucia', 'w'),
- (DEFAULT, 'Chiara', 'w'),
- (DEFAULT, 'Merle', 'w'),
- (DEFAULT, 'Fabienne', 'w'),
- (DEFAULT, 'Jael', 'w'),
- (DEFAULT, 'Elin', 'w'),
- (DEFAULT, 'Alicia', 'w'),
- (DEFAULT, 'Leona', 'w'),
- (DEFAULT, 'Dior', 'w'),
- (DEFAULT, 'Dariah', 'w'),
- (DEFAULT, 'Johanna', 'w'),
- (DEFAULT, 'Angelina', 'w'),
- (DEFAULT, 'Joenna', 'w'),
- (DEFAULT, 'Laura', 'w'),
- (DEFAULT, 'Celina', 'w'),
- (DEFAULT, 'Joy', 'w'),
- (DEFAULT, 'Malina', 'w'),
- (DEFAULT, 'Charlott', 'w'),
- (DEFAULT, 'Paula', 'w'),
- (DEFAULT, 'Annika', 'w'),
- (DEFAULT, 'Paulina', 'w'),
- (DEFAULT, 'Kyara', 'w'),
- (DEFAULT, 'Carla', 'w'),
- (DEFAULT, 'Emilia', 'w'),
- (DEFAULT, 'Jana', 'w'),
- (DEFAULT, 'Emily ', 'w'),
- (DEFAULT, 'Mira', 'w'),
- (DEFAULT, 'Nele', 'w'),
- (DEFAULT, 'Leana', 'w'),
- (DEFAULT, 'Aurelia', 'w'),
- (DEFAULT, 'Sandra', 'w'),
- (DEFAULT, 'Elisa', 'w'),
- (DEFAULT, 'Aurelija', 'w'),
- (DEFAULT, 'Noemi', 'w'),
- (DEFAULT, 'Sophia', 'w'),
- (DEFAULT, 'Maxima', 'w'),
- (DEFAULT, 'Allegra', 'w'),
- (DEFAULT, 'Carolina', 'w');
- -- insert vornamen
- INSERT INTO nachnamen ("nn_id", "nachname") VALUES
- (DEFAULT, 'Müller'),
- (DEFAULT, 'Schmidt'),
- (DEFAULT, 'Schneider'),
- (DEFAULT, 'Fischer'),
- (DEFAULT, 'Meyer'),
- (DEFAULT, 'Weber'),
- (DEFAULT, 'Wagner'),
- (DEFAULT, 'Becker'),
- (DEFAULT, 'Schulz'),
- (DEFAULT, 'Hoffmann'),
- (DEFAULT, 'Schäfer'),
- (DEFAULT, 'Koch'),
- (DEFAULT, 'Bauer'),
- (DEFAULT, 'Richter'),
- (DEFAULT, 'Klein'),
- (DEFAULT, 'Schröder'),
- (DEFAULT, 'Wolf'),
- (DEFAULT, 'Neumann'),
- (DEFAULT, 'Schwarz'),
- (DEFAULT, 'Zimmermann'),
- (DEFAULT, 'Krüger'),
- (DEFAULT, 'Braun'),
- (DEFAULT, 'Hofmann'),
- (DEFAULT, 'Schmitz'),
- (DEFAULT, 'Hartmann'),
- (DEFAULT, 'Lange'),
- (DEFAULT, 'Schmitt'),
- (DEFAULT, 'Werner'),
- (DEFAULT, 'Krause'),
- (DEFAULT, 'Meier'),
- (DEFAULT, 'Schmid'),
- (DEFAULT, 'Lehmann'),
- (DEFAULT, 'Schulze'),
- (DEFAULT, 'Maier'),
- (DEFAULT, 'Köhler'),
- (DEFAULT, 'Herrmann'),
- (DEFAULT, 'Walter'),
- (DEFAULT, 'Körtig'),
- (DEFAULT, 'Mayer'),
- (DEFAULT, 'Huber'),
- (DEFAULT, 'Kaiser'),
- (DEFAULT, 'Fuchs'),
- (DEFAULT, 'Peters'),
- (DEFAULT, 'Möller'),
- (DEFAULT, 'Scholz'),
- (DEFAULT, 'Lang'),
- (DEFAULT, 'Weiß'),
- (DEFAULT, 'Jung'),
- (DEFAULT, 'Hahn'),
- (DEFAULT, 'Vogel'),
- (DEFAULT, 'Friedrich'),
- (DEFAULT, 'Günther'),
- (DEFAULT, 'Keller'),
- (DEFAULT, 'Schubert'),
- (DEFAULT, 'Berger'),
- (DEFAULT, 'Frank'),
- (DEFAULT, 'Roth'),
- (DEFAULT, 'Beck'),
- (DEFAULT, 'Winkler'),
- (DEFAULT, 'Lorenz'),
- (DEFAULT, 'Baumann'),
- (DEFAULT, 'Albrecht'),
- (DEFAULT, 'Ludwig'),
- (DEFAULT, 'Franke'),
- (DEFAULT, 'Simon'),
- (DEFAULT, 'Böhm'),
- (DEFAULT, 'Schuster'),
- (DEFAULT, 'Schumacher'),
- (DEFAULT, 'Kraus'),
- (DEFAULT, 'Winter'),
- (DEFAULT, 'Otto'),
- (DEFAULT, 'Krämer'),
- (DEFAULT, 'Stein'),
- (DEFAULT, 'Vogt'),
- (DEFAULT, 'Martin'),
- (DEFAULT, 'Jäger'),
- (DEFAULT, 'Groß'),
- (DEFAULT, 'Sommer'),
- (DEFAULT, 'Brandt'),
- (DEFAULT, 'Haas'),
- (DEFAULT, 'Heinrich'),
- (DEFAULT, 'Seidel'),
- (DEFAULT, 'Schreiber'),
- (DEFAULT, 'Schulte'),
- (DEFAULT, 'Graf'),
- (DEFAULT, 'Dietrich'),
- (DEFAULT, 'Ziegler'),
- (DEFAULT, 'Engel'),
- (DEFAULT, 'Kühn'),
- (DEFAULT, 'Kuhn'),
- (DEFAULT, 'Pohl'),
- (DEFAULT, 'Horn'),
- (DEFAULT, 'Thomas'),
- (DEFAULT, 'Busch'),
- (DEFAULT, 'Wolff'),
- (DEFAULT, 'Sauer'),
- (DEFAULT, 'Bergmann'),
- (DEFAULT, 'Pfeiffer'),
- (DEFAULT, 'Voigt'),
- (DEFAULT, 'Ernst');
- -- insert Produkte
- INSERT INTO produkte ("produkt_id", "name", "preis", "mwst", "ausgelaufen") VALUES
- (DEFAULT, 'Schnitzel', 9.50, 10, FALSE),
- (DEFAULT, 'Schweinsbraten', 13.50, 10, FALSE),
- (DEFAULT, 'Deo', 5.30, 20, FALSE),
- (DEFAULT, 'Shampoo', 2.50, 20, FALSE),
- (DEFAULT, 'Magazin', 4.50, 10, FALSE),
- (DEFAULT, 'Cola', 3.50, 10, FALSE),
- (DEFAULT, 'Kartoffeln', 2.50, 10, FALSE),
- (DEFAULT, 'Autobahnvignette', 89.90, 20, FALSE),
- (DEFAULT, 'Scheibenwischer', 12.50, 20, FALSE),
- (DEFAULT, 'Duftbaum', 5.00, 20, FALSE);
- -- insert allergene
- INSERT INTO allergene ("kuerzel", "bezeichnung") VALUES
- ('A','Glutenhältige Getreide'),
- ('B','Krebstiere (Krustentiere bzw. Crustaceae)'),
- ('C','Aus Ei hergestellte Produkte'),
- ('D','Fisch'),
- ('E','Erdnüsse'),
- ('F','Soja'),
- ('G','Milch (einschließlich Laktose)'),
- ('H','Schalenfrüchte (Nüsse)'),
- ('L','Sellerie'),
- ('M','Senf'),
- ('N','Sesam'),
- ('O','Schwefeldioxid und Sulphit'),
- ('P','Lupinen'),
- ('R','Weichtiere (Mollusken)');
- -- insert allerg_prod
- INSERT INTO allerg_prod ("fk_kuerzel", "fk_produkt_id") VALUES
- ('A', 3),
- ('A', 6),
- ('A', 7),
- ('C', 3),
- ('C', 5),
- ('C', 9),
- ('E', 9),
- ('G', 9),
- ('H', 9),
- ('C', 6),
- ('M', 6);
- -- insert adressen
- INSERT INTO adressen ("strasse", "plz", "ort", "hausnummer") VALUES
- ('Hauptstrasse', 2151, 'Asparn an der Zaya', 24),
- ('Breunerstrasse', 2130, 'Mistelbach', 43),
- ('Obere Landstrasse', 2130, 'Mistelbach', 5),
- ('Krauthof', 2130, 'Mistelbach', 45),
- ('Hauptplatz', 2130, 'Mistelbach', 75),
- ('Krankenhaus', 1200, 'Wien', 24),
- ('Kopfgasse', 1230, 'Wien', 87),
- ('Krautstrasse', 1010, 'Wien', 7),
- ('Kartoffelweg', 1010, 'Wien', 66),
- ('Nasenhaarstrasse', 1030, 'Wien', 57),
- ('Chinakohl', 1100, 'Wien', 12),
- ('Brandweingasse', 1020, 'Wien', 15);
- -- insert filialen
- INSERT INTO filialen ("uid", "name", "telefon", "fax", "geschlossen", "fk_addr_id")
- VALUES (24324, 'Hauptstrasse', 4363456, 9741781, FALSE, (SELECT addr_id FROM adressen WHERE strasse='Hauptstrasse'));
- INSERT INTO filialen ("uid", "name", "telefon", "fax", "geschlossen", "fk_addr_id")
- VALUES (23413, 'Krankenhaus', 6346463, 6456456, FALSE, (SELECT addr_id FROM adressen WHERE strasse='Krankenhaus'));
- INSERT INTO filialen ("uid", "name", "telefon", "fax", "geschlossen", "fk_addr_id")
- VALUES (23425, 'Kopfgasse', 7454536, 34674345, FALSE, (SELECT addr_id FROM adressen WHERE strasse='Kopfgasse'));
- -- -------------------------------------------------------------------
- -- Insert selects:
- -- -------------------------------------------------------------------
- INSERT INTO personen (vorname, nachname, geschlecht)
- SELECT v.vorname, n.nachname, v.geschlecht
- FROM vornamen v, nachnamen n;
- INSERT INTO mitarbeiter (fk_pers_id , eintrittsdatum, austrittsdatum)
- SELECT per.pers_id,
- to_date(concat('201', round(random()*7), '-', to_char(round(random()*12), 'fm00'), '-' , to_char(round(random()*28), 'fm00')), 'YYYY-MM-DD'),
- NULLIF (to_date(concat('2017-01-2' , round(random()*0.57)), 'YYYY-MM-DD'), to_date('2017-01-20', 'YYYY-MM-DD'))
- FROM personen per
- WHERE pers_id % 201 = 0;
- INSERT INTO kunden (fk_pers_id)
- SELECT per.pers_id
- FROM personen per
- WHERE pers_id % 3 != 0 OR pers_id % 5 != 0 OR pers_id % 7 != 0;
- INSERT INTO rechnungen (datum, uhrzeit, fk_f_id, fk_kunden_pers_id, fk_mitarbeiter_pers_id)
- SELECT to_date(concat('201', FLOOR((random()*7.5)), '-', to_char(round(random()*12), 'fm00'), '-' , to_char(round(random()*28), 'fm00')), 'YYYY-MM-DD'),
- '23:59:59',
- 1, -- Dummyfiliale
- kun.fk_pers_id, -- kundennummer
- (SELECT mi.fk_pers_id FROM mitarbeiter mi ORDER BY random() LIMIT 1)
- FROM kunden kun;
- INSERT INTO rechn_prod (preis, mwst, fk_prod_id, fk_rechn_nr)
- SELECT pr.preis * round(random()*10), pr.mwst, pr.produkt_id, r.rechn_nr
- FROM produkte pr, rechnungen r;
- -- -------------------------------------------------------------------
- -- drop temporary tables
- -- -------------------------------------------------------------------
- DROP TABLE IF EXISTS "vornamen" ;
- DROP TABLE IF EXISTS "nachnamen" ;
Advertisement
Add Comment
Please, Sign In to add comment