/********************************************************************
* Dieses Skript dient zum Erstellen der Vorlesungsdatenbank,        *
* und dem anschliessenden Eintragen der neuen Daten in die Tabellen *
/********************************************************************
/*
Zeile mit $ beginnend: Kommando in der Shell/Befehlszeile eingeben.
Zeile mit mysql> beginnend: Kommando im MySQL-Client eingeben.

Zum Laden der Firmen-Datenbank müssen zwei Voraussetzungen erfüllt sein:

Voraussetzungen: 
    1) Es ist eine Datenbank firma vohanden:
        $ mysql -u root 
        mysql> create database firma;

    2) Es ist ein Benutzer firma@localhost vorhanden, der auf die 
        Datenbank firma zugreifen darf.
        $ mysql -u root
        mysql> create user 'firma'@'localhost';
        mysql> grant all privileges on FIRMA.* to 'firma'@'localhost';

Laden der Firma-Datenbank:

        $ mysql -u firma <Firma_MySQL.sql

        ODER

        mysql> source Firma_MySQL.sql

Anmerkung: In Produktionsumgebungen würde sich der Benutzer mit 
einem Passwort anmelden müssen.

*/
 
/*
************Loeschen der Tabellen
*/

drop database if exists firma;
create database firma;
use firma;


drop table if exists maschine;
drop table if exists praemie;
drop table if exists kind;
drop table if exists personal;
drop table if exists expersonal;
drop table if exists abteilung;
drop table if exists gehalt;

/*
/*************Anlegen der Tabellen
*/
create table gehalt (
  geh_stufe varchar(4),
  betrag smallint not null,
  constraint Schluessel_Gehalt primary key (geh_stufe)
) comment 'Staffelung der Gehaelter';

create table abteilung (
  abt_nr char(3), 
  name char(20),
  constraint Schluessel_Abteilung primary key (abt_nr)
) comment 'Auflistung der Abteilungsnamen';

create table personal ( 
   pnr integer(4),
   name char(20) not null,
   vorname char(20),
   geh_stufe varchar(4),
   abt_nr char(3),
   krankenkasse char(3) comment 'Kuerzel der gesetzlichen Krankenversicherung des Mitarbeiters',
   constraint Schluessel_Personal primary key (pnr)
) comment 'Auflistung aller Betriebsangehoerigen';

create table kind (
  pnr integer(4),
  k_name char(20) not null,
  k_vorname char(20),
  k_geb smallint
) comment 'Kinder der Betriebsangehoerigen';

create table praemie (
  pnr integer(4),
  p_betrag smallint not null
) comment 'Praemien fuer ausgesuchte Mitarbeiter';
   
create table maschine ( 
    mnr integer(4),
    name char(20) not null,
    pnr integer(4),
    ansch_datum date,
    neuwert integer,
    zeitwert integer,
    constraint Schluessel_Maschine primary key (mnr)
) comment 'Gesamter Maschinenpark mit personeller Zuordnung';


/*
****************************Eintragen in die Tabelle Gehalt
*/
insert into gehalt
 (Geh_Stufe, Betrag)
 VALUES
 ('it1', 2523);
insert into gehalt
 (Geh_Stufe, Betrag)
 VALUES
 ('it2', 2873);
insert into gehalt
 (Geh_Stufe, Betrag)
 VALUES
 ('it3', 3027);
insert into gehalt
 (Geh_Stufe, Betrag)
 VALUES
 ('it4', 3341);
insert into gehalt
 (Geh_Stufe, Betrag)
 VALUES
 ('it5', 3782);

/*
**************************Eintragen in die Tabelle Abteilung
*/
insert into abteilung
 (abt_nr, Name)
 VALUES
 ('d11', 'Verwaltung');
insert into abteilung
 (abt_nr, Name)
 VALUES
 ('d12', 'Projektierung');
insert into abteilung
 (abt_nr, Name)
 VALUES
 ('d13', 'Produktion');
insert into abteilung
 (abt_nr, Name)
 VALUES
 ('d14', 'Lagerung');
insert into abteilung
 (abt_nr, Name)
 VALUES
 ('d15', 'Verkauf');

/*
******************************Eintragen in die Tabelle Personal
*/
insert into personal
 (PNR, Name, Vorname, Geh_Stufe, Abt_Nr, Krankenkasse)
 VALUES 
 (167, 'Krause', 'Gustav', 'it3', 'd12', 'dak');
insert into personal
 (PNR, Name, Vorname, Geh_Stufe, Abt_Nr, Krankenkasse)
 VALUES 
 (168, 'Hahn', 'Egon', 'it4', 'd11', 'bek');
insert into personal
 (PNR, Name, Vorname, Geh_Stufe, Abt_Nr, Krankenkasse)
 VALUES 
 (123, 'Lehmann', 'Karl', 'it3', 'd13', 'aok');
insert into personal
 (PNR, Name, Vorname, Geh_Stufe, Abt_Nr, Krankenkasse)
 VALUES 
 (133, 'Schulz', 'Harry', 'it1', 'd13', 'aok');
insert into personal
 (PNR, Name, Vorname, Geh_Stufe, Abt_Nr, Krankenkasse)
 VALUES 
 (124, 'Meier', 'Richard', 'it5', 'd13', 'aok');
insert into personal
 (PNR, Name, Vorname, Geh_Stufe, Abt_Nr, Krankenkasse)
 VALUES 
 (125, 'Wutschke', 'Oskar', 'it3', 'd13', 'aok');
insert into personal
 (PNR, Name, Vorname, Geh_Stufe, Abt_Nr, Krankenkasse)
 VALUES 
 (126, 'Schroeder', 'Karl-Heinz', 'it4', 'd13', 'aok');
insert into personal
 (PNR, Name, Vorname, Geh_Stufe, Abt_Nr, Krankenkasse)
 VALUES 
 (227, 'Wagner', 'Walter', 'it2', 'd13', 'dak');
insert into personal
 (PNR, Name, Vorname, Geh_Stufe, Abt_Nr, Krankenkasse)
 VALUES 
 (234, 'Krohn', 'August', 'it4', 'd13', 'aok');
insert into personal
 (PNR, Name, Vorname, Geh_Stufe, Abt_Nr, Krankenkasse)
 VALUES 
 (135, 'Tietze', 'Lutz', 'it2', 'd13', 'tkk');
insert into personal
 (PNR, Name, Vorname, Geh_Stufe, Abt_Nr, Krankenkasse)
 VALUES 
 (156, 'Hartmann', 'Juergen', 'it1', 'd14', 'bek');
insert into personal
 (PNR, Name, Vorname, Geh_Stufe, Abt_Nr, Krankenkasse)
 VALUES 
 (127, 'Ehlert', 'Siegfried', 'it1', 'd15', 'kkh');
insert into personal
 (PNR, Name, Vorname, Geh_Stufe, Abt_Nr, Krankenkasse)
 VALUES 
 (157, 'Schultze', 'Hans', 'it1', 'd14', 'aok');
insert into personal
 (PNR, Name, Vorname, Geh_Stufe, Abt_Nr, Krankenkasse)
 VALUES 
 (159, 'Osswald', 'Petra', 'it2', 'd15', 'bak');
insert into personal
 (PNR, Name, Vorname, Geh_Stufe, Abt_Nr, Krankenkasse)
 VALUES 
 (137, 'Haase', 'Gert', 'it1', 'd11', 'kkh');
insert into personal
 (PNR, Name, Vorname, Geh_Stufe, Abt_Nr, Krankenkasse)
 VALUES 
 (134, 'Meier', 'Gerd', 'it5', 'd11', 'tkk');


/*
*******************************Eintragen in die Tabelle Kind
*/
insert into kind
 (pnr, k_name, k_vorname, k_geb)
  VALUES
 (167, 'Krause', 'Fritz', 1997);
insert into kind
 (pnr, k_name, k_vorname, k_geb)
  VALUES
 (167, 'Krause', 'Ida', 1999);
insert into kind
 (pnr, k_name, k_vorname, k_geb)
  VALUES
 (123, 'Lehmann', 'Sven', 2002);
insert into kind
 (pnr, k_name, k_vorname, k_geb)
  VALUES
 (123, 'Lehmann', 'Karl', 2004);
insert into kind
 (pnr, k_name, k_vorname, k_geb)
  VALUES
 (168, 'Hahn', 'Hans', 1993);
insert into kind
 (pnr, k_name, k_vorname, k_geb)
  VALUES
 (133, 'Wendler', 'Klaus', 1996);
insert into kind
 (pnr, k_name, k_vorname, k_geb)
  VALUES
 (124, 'Meier', 'Gustav', 1999);
insert into kind
 (pnr, k_name, k_vorname, k_geb)
  VALUES
 (124, 'Meier', 'Susi', 2002);
insert into kind
 (pnr, k_name, k_vorname, k_geb)
  VALUES
 (124, 'Meier', 'Dirk', 2004);

/*
***************************Eintragen in die Tabelle Praemie
*/
insert into praemie
 (PNR, P_Betrag)
 VALUES
 (227, 550);
insert into praemie
 (PNR, P_Betrag)
 VALUES
 (227, 610);
insert into praemie
 (PNR, P_Betrag)
 VALUES
 (227, 250);
insert into praemie
 (PNR, P_Betrag)
 VALUES
 (168, 600);
insert into praemie
 (PNR, P_Betrag)
 VALUES
 (168, 700);
insert into praemie
 (PNR, P_Betrag)
 VALUES
 (124, 250);
insert into praemie
 (PNR, P_Betrag)
 VALUES
 (234, 600);
insert into praemie
 (PNR, P_Betrag)
 VALUES
 (234, 500);
insert into praemie
 (PNR, P_Betrag)
 VALUES
 (127, 300);
/*
***********************************Eintragen in die Tabelle Maschine
*/
insert into maschine
 (MNR, Name, PNR, Ansch_Datum, Neuwert, Zeitwert)
 VALUES
 (1, 'Bohrmaschine', 123, STR_TO_DATE('01-02-99', '%d-%m-%y'), 30000, 15000);
insert into maschine
 (MNR, Name, PNR, Ansch_Datum, Neuwert, Zeitwert)
 VALUES
 (2, 'Bohrmaschine', 123, STR_TO_DATE('01-07-02', '%d-%m-%y'), 30000, 18000);
insert into maschine
 (MNR, Name, PNR, Ansch_Datum, Neuwert, Zeitwert)
 VALUES
 (3, 'Fraesmaschine', 124, STR_TO_DATE('04-01-98', '%d-%m-%y'), 40000, 10000);
insert into maschine
 (MNR, Name, PNR, Ansch_Datum, Neuwert, Zeitwert)
 VALUES
 (11, 'Hobelmaschine', 127, STR_TO_DATE('15-01-02', '%d-%m-%y'), 29000, 19000);
insert into maschine
 (MNR, Name, PNR, Ansch_Datum, Neuwert, Zeitwert)
 VALUES
 (12, 'Drehbank', 126, STR_TO_DATE('01-08-99', '%d-%m-%y'), 31000, 21000);
insert into maschine
 (MNR, Name, PNR, Ansch_Datum, Neuwert, Zeitwert)
 VALUES
 (14, 'Hobelmaschine', 123, STR_TO_DATE('01-11-98', '%d-%m-%y'), 32000, 22000);
insert into maschine
 (MNR, Name, PNR, Ansch_Datum, Neuwert, Zeitwert)
 VALUES
 (16, 'Drehbank', 134, STR_TO_DATE('25-11-01', '%d-%m-%y'), 32000, 23000);
insert into maschine
 (MNR, Name, PNR, Ansch_Datum, Neuwert, Zeitwert)
 VALUES
 (17, 'Bohrmaschine', 127, STR_TO_DATE('01-02-03', '%d-%m-%y'), 31000, 25000);