ZFSL/MS/velo2000

Wiki-Quelltext dieser Seite.

<language>SQL</language>
[[Bild:Velo-logo.jpg]]

'''Einsatz einer Personaldatenbank für den Fahrradproduzenten Velo2000 GmbH''' 

Für die Mitarbeiterverwaltung der Velo2000 GmbH liegt die folgende Datenbank vor:

[[Bild:Velo-relationenmodell.gif]]

[[Bild:Velo-tabellenstruktur.gif]]


<code id="53a2dc4695c30"  hidden>
Create Table abteilung (
AbteilungsID varchar(3),
Abteilungsbez varchar(50),
AbteilungsleiterID integer,
Primary key (AbteilungsID));

INSERT INTO abteilung (AbteilungsID,Abteilungsbez, AbteilungsleiterID) VALUES
('EDV', 'Elektonische Datenverarbeitung', 1060),
('EGF', 'Erweiterte Geschäftsführung', 1040),
('EK', 'Einkauf', 4020),
('GF', 'Geschäftsführung', 1001),
('LA', 'Lager', 4145),
('LW', 'Lehrwerkstatt', 3020),
('MF', 'Mechanische Fertigung', 3010),
('ORG', 'Organisation', 4060),
('PA', 'Personalangelegenheiten', 1050),
('RW', 'Rechnungswesen', 4040),
('TE', 'test', 222),
('VE', 'Vertrieb', 4065);
;



CREATE TABLE ort (
  PLZ varchar(5) NOT NULL,
  Ortsname varchar(50) NOT NULL,
  PRIMARY KEY (PLZ)
) ;


INSERT INTO ort (PLZ, Ortsname) VALUES
('45721', 'Haltern am See'),
('45768', 'Marl'),
('48143', 'Münster'),
('48155', 'Münster'),
('48301', 'Nottuln'),
('48329', 'Havixbeck'),
('48727', 'Billerbeck');

CREATE TABLE mitarbeiter (
  PersonalNr integer NOT NULL,
  Nachname varchar(30) NOT NULL,
  Vorname varchar(30) NOT NULL,
  Strasse varchar(80) NOT NULL,
  Hausnummer varchar(10) NOT NULL, 
  PLZ varchar(5) NOT NULL,
  istTarif integer NOT NULL,
  Geburtsdatum date NOT NULL,
  Eintrittsdatum date NOT NULL,
  Geschlecht varchar(1) NOT NULL,
  Steuerklasse integer NOT NULL,
  Monatsentgelt double NOT NULL,
  AbteilungsID varchar(3) NOT NULL,
PRIMARY KEY (PersonalNr)

);

INSERT INTO mitarbeiter (PersonalNr, Nachname, Vorname, Strasse, Hausnummer, PLZ, istTarif, Geburtsdatum, Eintrittsdatum, Geschlecht, Steuerklasse, Monatsentgelt,AbteilungsID) VALUES
(1001, 'Birkhohl', 'Rolf', 'Toni-Turek-Straße', '19', '48301', 0, '1958-09-07', '1990-01-01', 'm', 3, 6200,'GF'),
(1020, 'Birkhohl', 'Heinz ', 'Am Bagno', '2', '48301', 0, '1951-01-27', '1990-01-01', 'm', 1, 6200,'GF'),
(1030, 'Starke', 'Klaus', 'Am Bagno', '10', '48301', 0, '1946-08-02', '1990-01-01', 'm', 4, 4875,'EGF'),
(1040, 'Kunze', 'Patrick', 'Am Sportplatz', '32', '48727', 0, '1958-07-05', '1990-01-01', 'm', 3, 5160,'EGF'),
(1050, 'Jochmann', 'Silke', 'Olympiastraße', '22', '48301', 1, '1970-10-16', '2000-03-01', 'w', 4, 4380,'PA'),
(1060, 'Zimmer', 'Michael', 'Kolpingstraße', '18', '48301', 0, '1972-12-16', '1997-07-02', 'm', 3, 4750,'EDV'),
(2010, 'Kaes', 'Sarah', 'Carl-Diem-Ring', '3', '48301', 1, '1964-11-27', '1990-01-01', 'w', 1, 3312,'EK'),
(2020, 'Rose', 'Daniela', 'Mondstraße', '12', '48143', 1, '1960-12-29', '1998-10-01', 'w', 5, 3211,'RW'),
(2025, 'Hinze', 'Herbert', 'Schillerstraße', '23', '48143', 1, '1963-05-02', '1992-06-15', 'm', 3, 2915,'ORG'),
(2030, 'Zurni', 'Kevin', 'Hauptstraße', '33', '48329', 1, '1968-07-19', '1990-07-01', 'm', 3, 3204,'VE'),
(3010, 'Breke', 'Ernst', 'Hauptstraße', '34', '48329', 0, '1967-08-25', '2010-07-01', 'm', 3, 4558,'MF'),
(3020, 'Lätzler', 'Sabine', 'Grauten Ihl', '24', '48301', 1, '1973-05-20', '1990-08-01', 'w', 4, 3239,'LW'),
(3030, 'Kraus', 'Steffen', 'Krägerweg', '3', '48155', 1, '1964-09-13', '1991-07-01', 'm', 1, 2641,'LA'),
(4005, 'Griese', 'Anette', 'Rappaportstaße', '4', '45768', 1, '1969-03-27', '1992-01-01', 'w', 4, 2258,'EK'),
(4010, 'Kohn', 'Edgar', 'Recklinghäuser Straße', '28', '45721', 1, '1972-08-22', '1992-07-01', 'm', 3, 3482,'EK'),
(4015, 'Müller', 'Albrecht', 'Toni-Turek-Straße', '20', '48301', 1, '1973-12-25', '1993-01-01', 'm', 3, 2746,'EK'),
(4020, 'Beyer', 'Bernhard', 'Am Bagno', '18', '48301', 1, '1969-07-25', '1993-01-01', 'm', 3, 3954,'EK'),
(4025, 'Zachow', 'Barbara', 'Am Bagno', '26', '48301', 1, '1972-06-05', '1996-06-01', 'w', 3, 720,'EK'),
(4030, 'Ostendorf', 'Anne', 'Am Sportplatz', '33', '48727', 1, '1992-12-26', '2009-08-01', 'w', 1, 3207,'RW'),
(4035, 'Kramer', 'Alfred', 'Olympiastraße', '23', '48301', 1, '1960-02-20', '1994-02-01', 'm', 1, 3391,'RW'),
(4040, 'Elten', 'Eckhard', 'Kolpingstraße', '19', '48301', 1, '1962-04-09', '1994-03-14', 'm', 4, 3845,'RW'),
(4045, 'Krieg', 'Birgit', 'Carl-Diem-Ring', '4', '48301', 1, '1994-09-01', '2010-08-01', 'w', 1, 680,'RW'),
(4050, 'Blume', 'Dagmar', 'Mondstraße', '13', '48143', 1, '1976-12-01', '1995-08-01', 'w', 5, 3107,'ORG');




</code>
'''Handlungssituation 2: Orientierung in der Datenbank'''

[[Bild:Neuer.jpg]]


Die InSoft GmbH hat die Personaldatenbank fristgerecht fertiggestellt und an den Auftrag-geber übergeben. Für die Verwaltung der Personaldatenbank bei der Velo2000 GmbH ist die Informatikkauffrau Petra Neuer zuständig. Zu ihrem Aufgabengebiet gehört unter anderem die Pflege der Stammdaten. Bevor sie ihre ersten Aufgaben erledigt, verschafft sie sich einen Überblick über die Datenbank. Frau Neuer lässt sich u. a. die folgenden Listen der Personaldatenbank anzeigen: 

- alle Mitarbeiterinnen und Mitarbeiter

- die Personal-Nr und den Nachnamen aller Mitarbeiterinnen und Mitarbeiter

- alle Mitarbeiterinnen und Mitarbeiter der Abteilung Einkauf

- den Nach- und Vornamen sowie das Geburtsdatum alle weibl. Mitarbeiter

- alle Mitarbeiterinnen und Mitarbeiter  aus Haltern am See

- alle Mitarbeiterinnen und Mitarbeiter, deren Nachnamen mit „S“ beginnen

- alle Mitarbeiterinnen und Mitarbeiter aus dem PLZ-Bereich 45

- den Namen und das Monatsentgelt aller AT-Mitarbeiterinnen und Mitabteiter 

- alle männlichen Mitarbeiter, die mehr als 3000,00 Euro Monatsentgelt haben

- alle Mitarbeiter, die nach 1990 geboren sind

- alle Mitarbeiterinnen und Mitarbeiter, die seit dem 01.01.2005 im Unternehmen sind

- alle Mitarbeiter, die zwischen 1980 und 1999 geboren sind

- alle Mitarbeiterinnen und Mitarbeiter, deren Personalnummern mit 4 beginnen

- alle Mitarbeiterinnen und Mitarbeiter der Abteilungen EDV, LA und VE

<run id="53a2f94015cf3"></run>

'''Ergänzung:'''

Zusätzlich versucht Frau Neuer, die folgenden Informationen aus der Datenbank zu gewinnen:

- Das Alter aller Mitarbeiterinnen und Mitarbeiter

- Alle Mitarbeiter, die in den letzten 5 Jahren in das Unternehmen eingetreten sind. 

- Alle tariflichen Mitarbeiter aus den Abteilungen Rechnungswesen, Organisation und EDV, die nach dem 15. Juli 2009 in das Unternehmen eingetreten sind

<run id="53a2f94015d87"></run>


'''Handlungssituation 3:'''

[[Bild:Starke.gif]]

Frau Neuer hat nicht nur die Aufgabe, regelmäßig benötigte Personaldaten bereit zu stellen. Es kommt auch immer wieder vor, dass sie für einzelne Anlässe Daten zur Verfügung stellen muss. Heute erhält sie kurz vor Feierabend noch einen Anruf von Herrn Starke aus der Erweiterten Geschäftsführung. 


'''Herr Starke:''' Hallo Frau Neuer, tut mir leid, dass ich Sie kurz vor Feierabend noch stören muss. Aber für die morgige Abteilungsleitersitzung benötige ich noch dringend einige Daten. 

'''Frau Neuer:''' Kein Problem Herr Starke, worum geht es denn? 

'''Herr Starke:''' Prima Frau Neuer. Dann versuche ich es kurz zu machen. Wir haben vor, alle Mitarbeiter aus den Abteilungen Rechnungswesen, Organisation und EDV, die nach dem 15. Juli 2009 in das Unternehmen eingetreten sind, auf eine Softwareschulung zu schicken. Dafür bräuchten wir eine entsprechende Mitarbeiterliste. 
'''Frau Neuer:''' Soll die Fortbildung für alle Mitarbeiter sein oder nur für die tariflichen Angestellten? 


'''Herr Starke:''' Gut dass Sie nachfragen. Die Fortbildung soll nur für die tariflichen Angestellten angeboten werden. 

'''Frau Neuer:''' Alles klar, Herr Starke, die Liste ist schnell erstellt. Was kann ich sonst noch für Sie tun? 

'''Herr Starke:''' Ich bräuchte noch eine Liste aller Mitarbeiter der Fertigungsabteilung, die zwi-schen 40 und 50 Jahre alt sind. Denen wollen wir ein kostenloses Gesundheitsprogramm an-bieten.
Für die nächste Unternehmensfeier benötige ich noch eine Liste aller Mitarbeiter, die ihr 10-jähriges Dienstjubiläum feiern. 
Wir wollen eventuell die Modalitäten für die Einstellungsverfahren überarbeiten. Könnten Sie mir dazu bitte eine Auflistung der Termine erstellen, an denen wir bislang eingestellt haben. 

Dann habe ich noch eine letzte Bitte. Sie wissen ja, dass die Tarifparteien ab kommenden Monat eine Entgelterhöhung um einen Sockelbetrag von 50,00 € und zusätzlich 3 % auf das erhöhte Entgelt vereinbart haben. Könnten Sie mir eine Liste anfertigen, aus der das bisherige und das zukünftige Entgelt der Mitarbeiter hervorgeht? Meinen Sie, Sie bekommen die ganzen Auflistungen heute noch hin?

'''Frau Neuer:''' Da sehe ich auch keine Schwierigkeiten. Wozu hat man denn schließlich eine Datenbank. In einer Viertelstunde müsste alles fertig sein. 

'''Herr Starke:''' Das ist prima, Frau Neuer. Vielen Dank und schon jetzt einen schönen Feierabend.  



- Liste aller tariflichen Mitarbeiter der Einkaufsabteilung, die zwischen 40 und 50 Jahre alt sind.

- Liste aller Mitarbeiter, die ihr 10-jähriges Dienstjubiläum feiern. 

- Auflistung der Termine erstellen, an denen wir bislang eingestellt haben

- Liste vor und nach Entgelterhöhung um Sockelbetrag von 50,00 und zusätzlich 3 % auf das erhöhte Entgelt. 

<run id="53a2f94015e11"></run>



'''Handlungssituation 5: Mitarbeiterlisten'''

1)	Mitarbeiterlisten, die nach unterschiedlichen Kriterien sortiert sind 

- Alle Mitarbeiter nach PersonalNr sortiert

- Alle Mitarbeiter nach Nachname sortiert

- Alle Mitarbeiter nach Abteilung, Name und Vorname sortiert

- Alle Mitarbeiter nach Alter sortiert, beginnend bei den jüngsten.

- Alle Mitarbeiter, sortiert nach Wohnort



2)	Mitarbeiterlisten für tarifl. Mitarbeiter (gesamt und f. eine einzelne, mehrere Abteilungen)

- Alle tariflichen Mitarbeiter

- Alle nicht tariflichen Mitarbeiter

- Alle tariflichen Mitarbeiter der Abteilung Einkauf

- Alle tariflichen Mitarbeiter der Abteilungen Einkauf und Vertrieb

- Alle tariflichen Mitarbeiter der Abteilungen Einkauf und Vertrieb (mit Abteilungsbezeichnungen)



- Alle Mitarbeiter, die heute Geburtstag haben

- Alle Mitarbeiter, die seit 2008 neu eingestellt wurden
<run id="53a2fa839c369"></run>


'''Handlungssituation 6: Mitarbeiterstatistiken'''

1) Anzahl Mitarbeiter

- Die Anzahl aller Mitarbeiter

- Die Anzahl der Mitarbeiter der einzelnen Abteilungen

- Die Anzahl der tariflichen und außertariflichen Mitarbeiter je Abteilung

- Die Anzahl der Mitarbeiter nach Geschlecht

- Die Anzahl der Mitarbeiter nach Geschlecht in den einzelnen Abteilungen

- Die Anzahl der Mitarbeiter nach Geschlecht und Tarif in den einzelnen Abteilungen



2)	jüngste und älteste Mitarbeiter 

Das Geburtsdatum und Alter des ältesten Mitarbeiters

Das Geburtsdatum und Alter des ältesten männlichen Mitarbeiters und der ältesten weiblichen Mitarbeiterin

Das Geburtsdatum und Alter der ältesten Mitarbeiter in den einzelnen Abteilungen 



<run id="53a2a153a8cd2"  ></run>

3)	Durchschnittsalter 

- Das Durchschnittsalter der Mitarbeiter

- Das Durchschnittsalter der männlichen und weiblichen Mitarbeiter

- Das Durchschnittsalter der Mitarbeiter in den einzelnen Abteilungen

<run id="53a30a235e273"></run>

4)	Durchschnittliche Betriebszugehörigkeit 

- Liste aller Mitarbeiter sortiert nach Jahren der Betriebszugehörigkeit 

- Die durchschnittliche Betriebszugehörigkeit aller Mitarbeiter

- Die durchschnittliche Betriebszugehörigkeit der Mitarbeiter der einzelnen Abteilungen

- Die durchschnittliche Betriebszugehörigkeit der männlichen und weiblichen Mitarbeiter der einzelnen Abteilungen
<run id="53a30a235e316"></run>


5)	Durchschnittliches monatliches Brutto- und Nettoentgelt

- Liste der Mitarbeiter mit Brutto- und Nettoentgelt (inkl. Steuern und Sozialversicherungsabgaben)

- Durchschnittliches Monatsentgelt aller tariflichen und aller außertariflichen Mitarbeiter

- Durchschnittliches Monatsentgelt aller weiblichen und aller männlichen Mitarbeiter


- Durchschnittliches Monatsentgelt der Mitarbeiter der einzelnen Abteilungen

- Die Höhe der Personalkosten je Abteilung

<run id="53a3009cf05a8"></run>