----------------------------------------------------------
-- Installation der BIKE-Datenbank fuer Oracle-SQL 
--       Version Kaskadierendes Löschen
--
--                      zum Starten unter SQL*PLUS ab Oracle V9
--                         E. Schicker, 2008, 2013
----------------------------------------------------------

SET TERMOUT ON
PROMPT Installation der BIKE-Cascade-Datenbank. Bitte etwas Geduld
SET TERMOUT OFF
SET FEEDBACK OFF
PROMPT

-- Es werden eventuell noch existierende Tabellen geloescht
DROP TABLE CascAuftragsposten;
DROP TABLE CascAuftrag;
DROP TABLE CascPersonal;
DROP TABLE CascKunde;

------------------------------------
-- Anlegen der Tabellen:
-----------------------------------


CREATE TABLE CascKunde (
     Nr          INTEGER    Constraint PK_CascKunde PRIMARY KEY,
     Name        CHAR(30)   NOT NULL,
     Strasse     CHAR(30),
     PLZ         INTEGER,
     Ort         CHAR(20),
     Sperre      CHAR (1)       
);

CREATE TABLE CascPersonal (
     Persnr      INTEGER    Constraint PK_CascPersonal PRIMARY KEY,
     Name        CHAR(30)   NOT NULL,
     Strasse     CHAR(30),
     PLZ         INTEGER,
     Ort         CHAR(20),
     GebDatum    DATE       NOT NULL,
     Stand       CHAR(6),
     Vorgesetzt  INTEGER    Constraint FK_CascPersonal REFERENCES CascPersonal ON DELETE CASCADE,
     Gehalt      NUMERIC(8,2),
     Beurteilung CHAR(1),
     Aufgabe     CHAR(18)
);

CREATE TABLE CascAuftrag (
     AuftrNr     INTEGER    Constraint PK_CascAuftrag PRIMARY KEY,
     Datum       DATE,
     Kundnr      INTEGER    NOT NULL Constraint FK_CascAuftrag_Kunde REFERENCES CascKunde ON DELETE CASCADE,
     Persnr      INTEGER    Constraint FK_CascAuftrag_Personal REFERENCES CascPersonal ON DELETE CASCADE
);

CREATE TABLE CascAuftragsposten (
     PosNr       INTEGER    Constraint PK_CascAuftragsposten PRIMARY KEY,
     AuftrNr     INTEGER    NOT NULL Constraint FK_CascAuftrag_Auftrag REFERENCES CascAuftrag ON DELETE CASCADE,
     Artnr       INTEGER    NOT NULL,
     Anzahl      SMALLINT,
     Gesamtpreis NUMERIC(7,2),
     Constraint AK_CascAuftragsposten UNIQUE (AuftrNr, Artnr)
);


-- Jetzt werden die Tabellen mit Inhalt versorgt

INSERT INTO CascKunde
  VALUES ( 1, 'Fahrrad Shop'          , 'Obere Regenstr. 4'        ,  93059, 'Regensburg' , '0' );
INSERT INTO CascKunde
  VALUES ( 2, 'Zweirad-Center Staller', 'Kirschweg 20'             ,  44267, 'Dortmund'   , '0' );
INSERT INTO CascKunde
  VALUES ( 3, 'Maier Ingrid'          , 'Universitätsstr. 33'      ,  93055, 'Regensburg' , '1' );
INSERT INTO CascKunde
  VALUES ( 4, 'Rafa - Seger KG'       , 'Liebigstr. 10'            ,  10247, 'Berlin'     , '0' );
INSERT INTO CascKunde
  VALUES ( 5, 'Biker Ecke'            , 'Lessingstr. 37'           ,  22087, 'Hamburg'    , '0' );
INSERT INTO CascKunde
  VALUES ( 6, 'Fahrräder Hammerl'     , 'Schindlerplatz 7'         ,  81739, 'München'    , '0' );

INSERT INTO CascPersonal
  VALUES ( 1, 'Maria Forster'   , 'Ebertstr. 28'     , 93051, 'Regensburg', DATE '1979-07-05', 'verh', NULL, 4800.00 , '2' , 'Manager'    );
INSERT INTO CascPersonal
  VALUES ( 2, 'Anna Kraus'      , 'Kramgasse 5'      , 93047, 'Regensburg', DATE '1975-07-09', 'led' , 1   , 2300.00 , '3' , 'Vertreter'  );
INSERT INTO CascPersonal
  VALUES ( 4, 'Heinz Rolle'     , 'In der Au 5'      , 90455, 'Nürnberg'  , DATE '1957-10-12', 'led' , 1   , 3300.00 , '3' , 'Sekretär'   );
INSERT INTO CascPersonal
  VALUES ( 5, 'Johanna Köster'  , 'Wachtelstr. 7'    , 90427, 'Nürnberg'  , DATE '1984-02-07', 'ges' , 1   , 2100.00 , '5' , 'Vertreter'  );
INSERT INTO CascPersonal
  VALUES ( 6, 'Marianne Lambert', 'Fraunhofer Str 3' , 92224, 'Landshut'  , DATE '1974-05-22', 'verh', NULL, 4100.00 , '1' , 'Meister'    );
INSERT INTO CascPersonal
  VALUES ( 3, 'Ursula Rank'     , 'Dreieichstr. 12'  , 60594, 'Frankfurt' , DATE '1967-09-04', 'verh', 6   , 2700.00 , '1' , 'Facharbeiterin' );
INSERT INTO CascPersonal
  VALUES ( 7, 'Thomas Noster'   , 'Mahlergasse 10'   , 93047, 'Regensburg', DATE '1972-09-17', 'verh', 6   , 2500.00 , '5' , 'Arbeiter'   );
INSERT INTO CascPersonal
  VALUES ( 8, 'Renate Wolters'  , 'Lessingstr. 9'    , 86159, 'Augsburg'  , DATE '1979-07-14', 'led' , 1   , 3300.00 , '4' , 'Sachbearbeiterin'  );
INSERT INTO CascPersonal
  VALUES ( 9, 'Ernst Pach'      , 'Olgastr. 99'      , 70180, 'Stuttgart' , DATE '1992-03-29', 'led' , 6   , 800.00  , NULL, 'Azubi'      );

INSERT INTO CascAuftrag
  VALUES ( 1, DATE '2008-08-04', 1, 2);
INSERT INTO CascAuftrag
  VALUES ( 2, DATE '2008-09-06', 3, 5);
INSERT INTO CascAuftrag
  VALUES ( 3, DATE '2008-10-07', 4, 2);
INSERT INTO CascAuftrag
  VALUES ( 4, DATE '2008-10-18', 6, 5);
INSERT INTO CascAuftrag
  VALUES ( 5, DATE '2008-11-03', 1, 2);

INSERT INTO CascAuftragsposten
  VALUES ( 101, 1, 200002, 2,  800.00);
INSERT INTO CascAuftragsposten
  VALUES ( 201, 2, 100002, 3, 1950.00);
INSERT INTO CascAuftragsposten
  VALUES ( 202, 2, 200001, 1,  400.00);
INSERT INTO CascAuftragsposten
  VALUES ( 301, 3, 100001, 1,  700.00);
INSERT INTO CascAuftragsposten
  VALUES ( 302, 3, 500002, 2,  100.00);
INSERT INTO CascAuftragsposten
  VALUES ( 401, 4, 100001, 1,  700.00);
INSERT INTO CascAuftragsposten
  VALUES ( 402, 4, 500001, 4,   30.00);
INSERT INTO CascAuftragsposten
  VALUES ( 403, 4, 500008, 1,   94.00);
INSERT INTO CascAuftragsposten
  VALUES ( 501, 5, 500010, 1,   40.00);
INSERT INTO CascAuftragsposten
  VALUES ( 502, 5, 500013, 1,   30.00);

COMMIT;

SET TERMOUT ON
SET FEEDBACK ON

PROMPT Installation der Beispieldatenbank BIKE Cascade ist abgeschlossen