Aufgabe 2 Teil B

Datenbank anlegen

Erstellen Sie eine Datenbank projektverwaltung mit der Kollation utf-8-general-ci.

SQL Statements


                                CREATE DATABASE projektverwaltung

Tabellen anlegen

Erstellen Sie die vorliegenden Datenbanktabellen unter dem jeweils angegebenen Namen. Ermitteln Sie anhand der Tabelle der Zugehörigkeiten und Abhängigkeiten die entsprechenden Schlüsselfelder sowie notwendigen Feldtypen/Feldeigenschaften.

SQL Statements


                                CREATE TABLE projektverwaltung.abteilung (
	id INT(50) NOT NULL AUTO_INCREMENT,
	name VARCHAR(30) NOT NULL,
	Leiter INT(50) NOT NULL,
	PRIMARY KEY (id)
) ENGINE = InnoDB

Datensätze anlegen

Erstellen Sie die vorliegenden Datenbanktabellen unter dem jeweils angegebenen Namen. Ermitteln Sie anhand der Tabelle der Zugehörigkeiten und Abhängigkeiten die entsprechenden Schlüsselfelder sowie notwendigen Feldtypen/Feldeigenschaften.

SQL Statements


                                INSERT INTO abteilung (
	`id`, `name`, `Leiter`
) VALUES (
	'1', 'Raumfahrt', '2'
), (
	'2', 'Fuhrpark', '4'
), (
	'3', 'Verwaltung', '3'
)

Die Ergebnisse vom SQL-Statement

id	name	Leiter
1	Raumfahrt	2
2	Fuhrpark	4
3	Verwaltung	3

Tabellen anlegen

Erstellen Sie die vorliegenden Datenbanktabellen unter dem jeweils angegebenen Namen. Ermitteln Sie anhand der Tabelle der Zugehörigkeiten und Abhängigkeiten die entsprechenden Schlüsselfelder sowie notwendigen Feldtypen/Feldeigenschaften.

SQL Statements


                                CREATE TABLE projektverwaltung.arbeitet_an (
	id INT(50) NOT NULL AUTO_INCREMENT,
	mitarbeiter INT(50) NOT NULL,
	projekt INT(50) NOT NULL,
	PRIMARY KEY (id)
) ENGINE = InnoDB

Datensätze anlegen

Erstellen Sie die vorliegenden Datenbanktabellen unter dem jeweils angegebenen Namen. Ermitteln Sie anhand der Tabelle der Zugehörigkeiten und Abhängigkeiten die entsprechenden Schlüsselfelder sowie notwendigen Feldtypen/Feldeigenschaften.

SQL Statements


                                INSERT INTO arbeitet_an (
	id, mitarbeiter, projekt
) VALUES (
'1', '2', '1'
), (
	'2', '2', '2'
), (
	'3', '3', '3'
), (
	'4', '5', '1'
), (
	'5', '1', '2'
), (
	'6', '1', '3'
)

Die Ergebnisse vom SQL-Statement

id	mitarbeiter	projekt
1	2	1
2	2	2
3	3	3
4	5	1
5	1	2
6	1	3

Tabellen anlegen

Erstellen Sie die vorliegenden Datenbanktabellen unter dem jeweils angegebenen Namen. Ermitteln Sie anhand der Tabelle der Zugehörigkeiten und Abhängigkeiten die entsprechenden Schlüsselfelder sowie notwendigen Feldtypen/Feldeigenschaften.

SQL Statements


                                CREATE TABLE projektverwaltung.mitarbeiter (
	id INT(50) NOT NULL AUTO_INCREMENT,
	name VARCHAR(30) NOT NULL,
	vorname VARCHAR(30) NOT NULL,
    abt INT(50) NULL,
	PRIMARY KEY (id)
) ENGINE = InnoDB

Datensätze anlegen

Erstellen Sie die vorliegenden Datenbanktabellen unter dem jeweils angegebenen Namen. Ermitteln Sie anhand der Tabelle der Zugehörigkeiten und Abhängigkeiten die entsprechenden Schlüsselfelder sowie notwendigen Feldtypen/Feldeigenschaften.

SQL Statements


                                INSERT INTO mitarbeiter (
	`id`, `name`, `vorname`, `abt`
	) VALUES (
		'1', 'Müller', 'Anton', NULL
	), (
		'2', 'Geiger', 'Sven', '1'
	), (
		'3', 'Schwab', 'Anita', '3'
	), (
		'4', 'Görgens', 'Margit', '2'
	), (
		'5', 'Hurz', 'Willy', NULL
)

Die Ergebnisse vom SQL-Statement

id	name	vorname	abt
1	Müller	Anton
2	Geiger	Sven	1
3	Schwab	Anita	3
4	Görgens	Margit	2
5	Hurz	Willy

Tabellen anlegen

Erstellen Sie die vorliegenden Datenbanktabellen unter dem jeweils angegebenen Namen. Ermitteln Sie anhand der Tabelle der Zugehörigkeiten und Abhängigkeiten die entsprechenden Schlüsselfelder sowie notwendigen Feldtypen/Feldeigenschaften.

SQL Statements


                                CREATE TABLE projektverwaltung.projekt (
		id INT(50) NOT NULL AUTO_INCREMENT,
		bezeichner VARCHAR(30) NOT NULL,
		zugeord_abt INT(50) NOT NULL,
	    verantw_mitarb INT(50) NOT NULL,
		PRIMARY KEY (id)
		) ENGINE = InnoDB

Datensätze anlegen

Erstellen Sie die vorliegenden Datenbanktabellen unter dem jeweils angegebenen Namen. Ermitteln Sie anhand der Tabelle der Zugehörigkeiten und Abhängigkeiten die entsprechenden Schlüsselfelder sowie notwendigen Feldtypen/Feldeigenschaften.

SQL Statements


                                INSERT INTO projekt(
	    id, bezeichner, zugeord_abt, verantw_mitarb
	)
	VALUES(
		'1', 'Apollo13', '1', '5'
	),(
		'2', 'Challenger', '1', '4'
	),(
		'3', 'WebSeiten', '3', '1'
	)

Die Ergebnisse vom SQL-Statement

id	bezeichner	zugeord_abt	verantw_mitarb
1	Apollo13	1	5
2	Challenger	1	4
3	WebSeiten	3	1

ERM-Modell erstellen

Erstellen Sie anhand der vorliegenden Tabelle der Abhängigkeiten und Zugehörigkeiten ein entsprechendes ERM-Modell. Verwenden Sie die gängigen Kennungssymboliken.

SQL Statements


                                Jede Abteilung hat 1 Leiter (n:1-Kardinalität).
Jede Abteilung hat ein oder mehrere Projekte (m:n-Kardinalität).
Jedes Projekt hat einen Verantwortlichen (n:1-Kardinalität).
Jedes Projekt hat ein oder mehrere Mitarbeiter (m:n-Kardinalität).

Einfaches ERM-Modell als pdf

ERM-Modell erstellen

Erstellen Sie anhand der vorliegenden Tabelle der Abhängigkeiten und Zugehörigkeiten ein entsprechendes ERM-Modell. Verwenden Sie die gängigen Kennungssymboliken.

SQL Statements


                                Ausführliches ERM Modell, siehe pdf

Ausführliches ERM-Modell als pdf

Datensatz liefern

Wer ist der Leiter der Raumfahrtabteilung?

SQL Statements


                                SELECT mitarbeiter.name, mitarbeiter.vorname 
	FROM mitarbeiter 
	INNER JOIN abteilung ON abteilung.Leiter = mitarbeiter.id 
	WHERE abteilung.name = 'Raumfahrt'

Die Ergebnisse vom SQL-Statement

name	vorname
Geiger	Sven

Datensätze liefern

Geben Sie alle Abteilungsleiter aus

SQL Statements


                                SELECT mitarbeiter.name, mitarbeiter.vorname 
	FROM mitarbeiter 
	INNER JOIN abteilung ON abteilung.Leiter = mitarbeiter.id

Die Ergebnisse vom SQL-Statement

name	vorname
Geiger	Sven
Schwab	Anita
Görgens	Margit

Datensätze liefern

Welche Projekte gehören zur Verwaltungsabteilung

SQL Statements


                                SELECT bezeichner 
	FROM projekt
	INNER JOIN abteilung ON projekt = abteilung.id
	WHERE abteilung.name = 'Verwaltung'

Die Ergebnisse vom SQL-Statement

bezeichner
WebSeiten

Datensatz liefern

Wer ist für das Apollo 13 Projekt verantwortlich?

SQL Statements


                                SELECT mitarbeiter.name, mitarbeiter.vorname 
	FROM mitarbeiter 
	INNER JOIN projekt ON projekt.verantw_mitarb = mitarbeiter.id
	WHERE projekt.bezeichner = 'Apollo13'

Die Ergebnisse vom SQL-Statement

name	vorname
Geiger	Sven
Schwab	Anita
Hurz	Willy
Müller	Anton

Datensatz liefern

Wer arbeitet am Challenger Projekt?

SQL Statements


                                SELECT mitarbeiter.name, mitarbeiter.vorname
FROM mitarbeiter
INNER JOIN arbeitet_an ON arbeitet_an.mitarbeiter = mitarbeiter.id
INNER JOIN projekt ON arbeitet_an.projekt = projekt.id
WHERE projekt.bezeichner = 'Challenger'

Die Ergebnisse vom SQL-Statement

name	vorname
Geiger	Sven
Müller	Anton

Datensätze liefern

Geben Sie alle Projektverantwortlichen aus

SQL Statements


                                SELECT mitarbeiter.name, mitarbeiter.vorname 
	FROM mitarbeiter 
	INNER JOIN projekt ON projekt.verantw_mitarb = mitarbeiter.id

Die Ergebnisse vom SQL-Statement

name	vorname
Müller	Anton
Görgens	Margit
Hurz	Willy

Datensätze liefern

Wer arbeitet am Challenger Projekt und ist gleichzeitig Leiter einer Abteilung? (Name, Vorname)

SQL Statements


                                SELECT mitarbeiter.name, mitarbeiter.vorname
	FROM mitarbeiter
	INNER JOIN arbeitet_an ON arbeitet_an.mitarbeiter = mitarbeiter.id
	INNER JOIN projekt ON arbeitet_an.projekt = projekt.id
	INNER JOIN abteilung ON abteilung.id = projekt.zugeord_abt
	WHERE projekt.bezeichner = 'Challenger' AND mitarbeiter.abt IS NOT NULL

Die Ergebnisse vom SQL-Statement

name	vorname
Geiger	Sven

Datensätze liefern

Wer arbeitet am Apollo 13 Projekt und am WebSeiten Projekt?

SQL Statements


                                SELECT mitarbeiter.name, mitarbeiter.vorname
	FROM mitarbeiter
	INNER JOIN arbeitet_an ON arbeitet_an.mitarbeiter = mitarbeiter.id
	INNER JOIN projekt ON arbeitet_an.projekt = projekt.id
	WHERE projekt.bezeichner = 'WebSeiten' OR projekt.bezeichner = 'Apollo13'

Die Ergebnisse vom SQL-Statement

name	vorname
Geiger	Sven
Schwab	Anita
Hurz	Willy
Müller	Anton

Benutzer anlegen

Legen Sie als neuen Benutzer den Benutzer superadmin an mit der Passwortzuordnung topsecret.

SQL Statements


                                CREATE USER 'superadmin'@'localhost' IDENTIFIED BY 'topsecret'

Benutzerrechte vergeben

Vergeben Sie dem Benutzer superadmin alle Berechtigungen für die Datenbank projektverwaltung.

SQL Statements


                                GRANT ALL PRIVILEGES ON projektverwaltung . * TO 'superadmin'@'localhost

Benutzer anlegen

Legen Sie als neuen Benutzer den Benutzer verwalter an mit der Passwortzuordnung secret.

SQL Statements


                                CREATE USER 'verwalter'@'localhost' IDENTIFIED BY 'secret'

Benutzerrechte vergeben

Vergeben Sie dem Benutzer verwalter die Berechtigungen für die Datenbank projektverwaltung nur bezüglich der Tabelle abteilung. Die Beschränkung soll sich auf das Einfügen und Löschen von Datensätzen der Tabelle Abteilung beschränken.

SQL Statements


                                GRANT SELECT, INSERT, DELETE ON projektverwaltung.abteilung TO 'verwalter'@'localhost'

Benutzer löschen

Löschen Sie anschließend den Benutzer verwalter.

SQL Statements


                                DROP USER 'verwalter'@'localhost'