Aufgabe 1-4: Projektverwaltung

Eine Stadtverwaltung möchte alle ressortübergreifenden Projekte in einer Datenbank erfassen.

Anforderungsdefinition

Jedem Projekt muss ein Mitarbeiter zugeordnet werden, der das Projekt leitet. Mitarbeiter können zeitgleich oder im Laufe der Zeit verschiedene Projekte leiten.

Außerdem sollen einem Projekt Mitarbeiter zugewiesen werden könnnen, die an dem Projekt mitarbeiten sollen. Dies muss jedoch nicht bereits bei der Neuanlage eines Projekts erfolgen. Mitarbeiter können zeitgleich oder im Laufe der Zeit an verschiedenen Projekten mitarbeiten.

Für alle beteiligten Mitarbeiter muss außerdem das Ressort angegeben sein, in dem sie arbeiten.

Daten

• Für jedes Projekt sollen die Projektbezeichnung, eine Kurzbeschreibung, das Start- und das Abschlussdatum, der Projektleiter sowie alle weiteren Projektmitarbeiter verfügbar sein.
• Für jeden Mitarbeiter sollen Personalnummer, Vor- und Nachname sowie das Ressort, in dem er arbeitet, verfügbar sein.
• Für jedes Ressort soll dessen Bezeichnung gespeichert werden können.
• In der Datenbank sollen alle Ressorts der Stadt verfügbar sein, auch wenn ein Ressort bisher noch keinen Mitarbeiter für ein solches Projekt abgestellt hat.

Aufgaben

1. Erstellen Sie ein ER-Modell. Lösung

   Lösung

2. Überführen Sie das ER-Modell in ein Relationenmodell. Lösung

   Lösung

   • Projekt(ID, Bezeichnung, Beschreibung, Beginn, Abschluss, ↑Mitarbeiter_ID_Projektleiter)
   • Mitarbeiter(ID, Personalnummer, Vorname, Nachname, ↑Ressort_ID)
   • Ressort(ID, Bezeichnung)
   • Mitarbeiter_arbeitet_an_Projekt(↑Mitarbeiter_ID, ↑Projekt_ID)

3. Formulieren Sie die SQL-Anweisungen zum Erstellen der Tabellen. Lösung

   Lösung

   • SQL

     CREATE TABLE Ressort(
     ID INTEGER NOT NULL,
     Bezeichnung VARCHAR(30),
     PRIMARY KEY(ID));

   • Die Tabelle Ressort muss vorhanden sein, bevor diese Tabelle erzeugt werden kann.

     SQL

     CREATE TABLE Mitarbeiter(
     ID INTEGER NOT NULL,
     Personalnummer INTEGER,
     Vorname VARCHAR(30),
     Nachname VARCHAR(30),
     Ressort_ID INTEGER NOT NULL,
     PRIMARY KEY(ID),
     FOREIGN KEY(Ressort_ID) REFERENCES Ressort(ID));

   • Die Tabelle Mitarbeiter muss vorhanden sein, bevor diese Tabelle erzeugt werden kann.

     SQL

     CREATE TABLE Projekt(
     ID INTEGER NOT NULL,
     Bezeichnung VARCHAR(30),
     Beschreibung VARCHAR(500),
     Beginn DATE,
     Abschluss DATE,
     Mitarbeiter_ID_Projektleiter INTEGER NOT NULL,
     PRIMARY KEY(ID),
     FOREIGN KEY(Mitarbeiter_ID_Projektleiter) REFERENCES Mitarbeiter(ID));

   • Die Tabellen Mitarbeiter und Projekt müssen vorhanden sein, bevor diese Tabelle erzeugt werden kann.

     SQL

     CREATE TABLE Mitarbeiter_arbeitet_an_Projekt(
     Mitarbeiter_ID INTEGER NOT NULL,
     Projekt_ID INTEGER NOT NULL,
     PRIMARY KEY(Mitarbeiter_ID, Projekt_ID),
     FOREIGN KEY(Mitarbeiter_ID) REFERENCES Mitarbeiter(ID),
     FOREIGN KEY(Projekt_ID) REFERENCES Projekt(ID));

Aufgabe 1-5: Rechnungsverwaltung

Ein Handelsunternehmen möchte seine Kundenrechnungen in einer Datenbank erfassen.

Aufgaben

1. Abb. 1-52 zeigt beispielhaft eine Kundenrechnung. Erstellen Sie auf dieser Grundlage ein ER-Modell. Lösung

   Lösung

2. Überführen Sie das ER-Modell in ein Relationenmodell. Lösung

   Lösung

   • Ort(ID, Name, Plz)
   • Kunde(ID, KdNr, Vorname, Nachname, Strasse, HausNr, ↑Ort_ID)
   • Rechnung(ID, RechnungsNr, Rechnungsdatum, ↑Kunde_ID)
   • Artikel(ID, ArtNr, Bezeichnung, Listenpreis)
   • Rechnungsposition(ID, PosNr, Anzahl, ↑Artikel_ID, ↑Rechnung_ID)

3. Formulieren Sie die SQL-Anweisungen zum Erstellen der Tabellen. Lösung

   Lösung

   • SQL

     CREATE TABLE Ort(
     ID INTEGER NOT NULL,
     Name VARCHAR(30),
     Plz VARCHAR(5),
     PRIMARY KEY(ID));

   • SQL

     CREATE TABLE Kunde(
     ID INTEGER NOT NULL,
     KdNr INTEGER,
     Vorname VARCHAR(30),
     Nachname VARCHAR(30),
     Strasse VARCHAR(30),
     HausNr VARCHAR(5),
     Ort_ID INTEGER NOT NULL,
     PRIMARY KEY(ID),
     FOREIGN KEY(Ort_ID) REFERENCES Ort(ID));

   • SQL

     CREATE TABLE Rechnung(
     ID INTEGER NOT NULL,
     RechnungsNr INTEGER,
     Rechnungsdatum DATE,
     Kunde_ID INTEGER NOT NULL,
     PRIMARY KEY(ID),
     FOREIGN KEY(Kunde_ID) REFERENCES Kunde(ID));

   • SQL

     CREATE TABLE Artikel(
     ID INTEGER NOT NULL,
     ArtikelNr INTEGER,
     Bezeichnung VARCHAR(30),
     Listenpreis Decimal(11, 2),
     PRIMARY KEY(ID));

   • SQL

     CREATE TABLE Rechnungsposition(
     ID INTEGER NOT NULL,
     PosNr INTEGER,
     Anzahl INTEGER,
     Artikel_ID INTEGER NOT NULL,
     Rechnung_ID INTEGER NOT NULL,
     PRIMARY KEY(ID),
     FOREIGN KEY(Artikel_ID) REFERENCES Artikel(ID),
     FOREIGN KEY(Rechnung_ID) REFERENCES Rechnung(ID));

Aufgabe 1-6: Fertigung

Ein Maschinenbau-Unternehmen möchte seine Fertigung in einer zentralen Datenbank verwalten.

Anforderungsdefinition

Das Unternehmen hat verschiedene Fertigungsstraßen eingerichtet, in denen jeweils genau ein Produkt hergestellt wird. Manche Produkte werden nicht mehr hergestellt, andere sind dagegen so gefragt, dass sie sogar in mehreren Fertigungsstraßen gefertigt werden.

Eine Fertigungsstraße setzt sich aus verschiedenen Fertigungsstationen zusammen. Es soll jedoch auch möglich sein, eine Fertigungsstraße zu speichern, der noch keine Fertigungsstationen zugewiesen sind. Eine Fertigungsstation gehört stets zu genau einer Fertigungsstraße.

An einer Fertigungsstation arbeiten in der Regel mehrere Mitarbeiter. In der Planungsphase ist es jedoch auch möglich, dass ihr noch kein Mitarbeiter zugeordnet ist. Ein Mitarbeiter ist höchstens einer Fertigungsstation zugeordnet.

In einer Fertigungsstation können für die einzelnen Arbeitsschritte Maschinen zum Einsatz kommen. Jede Maschine wird in höchstens einer Fertigungsstation verwendet werden. Maschinen, die gerade nicht in einer Fertigungsstraße eingesetzt sind, werden eingelagert, bis sie wieder in einer Fertigungsstraße benötigt bzw. verkauft oder verschrottet werden.

Für jede Maschine wird außerdem erfasst, welche Mitarbeiter prinzipiell in der Lage sind, sie zu bedienen. Es besteht auch die Möglichkeit, dass eine neu angeschaffte Maschine zunächst noch von keinem Mitarbeiter bedient werden kann. Es gibt auch Mitarbeiter, die (noch) nicht in der Lage sind, eine Maschine zu bedienen. Andere wiederum haben Schulungen für verschiedene Maschinen erhalten.

Daten

• Für jedes Produkt soll eine Artikelnummer und eine Bezeichnung verfügbar sein.
• Für jede Fertigungsstraße soll ein Kürzel und eine Bezeichnung verfügbar sein.
• Für jede Fertigungsstation soll ein Kürzel und eine Bezeichnung verfügbar sein.
• Für jede Maschine soll eine Anlagennummer und eine Bezeichnung verfügbar sein.
• Für jeden Mitarbeiter sollen Personalnummer sowie Vor- und Nachname verfügbar sein.

Aufgaben

1. Erstellen Sie ein ER-Modell. Lösung

   Lösung

2. Überführen Sie das ER-Modell in ein Relationenmodell. Lösung

   Lösung

   • Fertigungsstrasse(ID, Bezeichnung, Kuerzel, ↑Produkt_ID)
   • Produkt(ID, Artikelnummer, Bezeichnung)
   • Fertigungsstation(ID, Bezeichnung, Kuerzel, ↑Fertigungsstrasse_ID)
   • Maschine(ID, Anlagennummer, Bezeichnung, ↑Fertigungsstation_ID)
   • Mitarbeiter(ID, Personalnummer, Vorname, Nachname, ↑Fertigungsstation_ID)
   • Mitarbeiter_kann_bedienen_Maschine(↑Mitarbeiter_ID, ↑Maschine_ID)

3. Formulieren Sie die SQL-Anweisungen zum Erstellen der Tabellen. Lösung

   Lösung

   • SQL

     CREATE TABLE Produkt(
     ID INTEGER NOT NULL,
     Artikelnummer INTEGER,
     Bezeichnung VARCHAR(30),
     PRIMARY KEY(ID));

   • Die Tabelle Produkt muss vorhanden sein, bevor diese Tabelle erzeugt werden kann.

     SQL

     CREATE TABLE Fertigungsstrasse(
     ID INTEGER NOT NULL,
     Bezeichnung VARCHAR(30),
     Kuerzel VARCHAR(10),
     Produkt_ID INTEGER NOT NULL,
     PRIMARY KEY(ID),
     FOREIGN KEY(Produkt_ID) REFERENCES Produkt(ID));

   • Die Tabelle Fertigungsstrasse muss vorhanden sein, bevor diese Tabelle erzeugt werden kann.

     SQL

     CREATE TABLE Fertigungsstation(
     ID INTEGER NOT NULL,
     Bezeichnung VARCHAR(30),
     Kuerzel VARCHAR(10),
     Fertigungsstrasse_ID INTEGER NOT NULL,
     PRIMARY KEY(ID),
     FOREIGN KEY(Fertigungsstrasse_ID) REFERENCES Fertigungsstrasse(ID));

   • Die Tabelle Fertigungsstation muss vorhanden sein, bevor diese Tabelle erzeugt werden kann.

     SQL

     CREATE TABLE Mitarbeiter(
     ID INTEGER NOT NULL,
     Personalnummer INTEGER,
     Vorname VARCHAR(30),
     Nachname VARCHAR(30),
     Fertigungsstation_ID INTEGER,
     PRIMARY KEY(ID),
     FOREIGN KEY(Fertigungsstation_ID) REFERENCES Fertigungsstation(ID));

   • Die Tabelle Fertigungsstation muss vorhanden sein, bevor diese Tabelle erzeugt werden kann.

     SQL

     CREATE TABLE Maschine(
     ID INTEGER NOT NULL,
     Anlagennummer INTEGER,
     Bezeichnung VARCHAR(30),
     Fertigungsstation_ID INTEGER,
     PRIMARY KEY(ID),
     FOREIGN KEY(Fertigungsstation_ID) REFERENCES Fertigungsstation(ID));

   • Die Tabellen Mitarbeiter und Maschine müssen vorhanden sein, bevor diese Tabelle erzeugt werden kann.

     SQL

     CREATE TABLE Mitarbeiter_kann_bedienen_Maschine(
     Mitarbeiter_ID INTEGER NOT NULL,
     Maschine_ID INTEGER NOT NULL,
     PRIMARY KEY(Mitarbeiter_ID, Maschine_ID),
     FOREIGN KEY(Mitarbeiter_ID) REFERENCES Mitarbeiter(ID),
     FOREIGN KEY(Maschine_ID) REFERENCES Maschine(ID));