Schule - Fakultät für Mathematik und Informatik - Friedrich

5.6 Praktischer Teil
Modellierung einer Schulen-Lehrer-Verwaltung, kurz SLV (was sonst ☺)
Vorgehen
- Aufgabenbeschreibung
- Erstellung des E/R-Modells
• Entities, Attribute, Relationen, Schlüssel
• Kardinalitäten, Integritätsbedingungen
- Ableitung des relationalen Modells
- Strukturdefinition in SQL (DDL)
- Datenbefüllung mit SQL (DML)
- Datenänderung-/zugriff mit SQL (DML)
Organisatorisches
- Vergabe von Hausaufgaben, Diskussion/Vorführung in der Vorlesung
- Realisierung auf IBM DB2 (iibm08 per WLAN) oder in MS Access (lokal)
- Nutzung bestehender FRZ-Logins
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 232
5.6.1 Aufgabenbeschreibung
Eine Schule ist gekennzeichnet durch eine eindeutige Schulnummer, hat
einen Namen und eine Adresse sowie einen Schultyp (z.B. Regelschule,
Gymnasium, ...)
Innerhalb einer konkreten Schule gibt es Klassen, die über ihre Bezeichnung
(z.B. 5a, 12-III) identifiziert werden, eine Schülerzahl aufweisen und einen
Klassenraum haben können
Weiterhin werden in einer Schule eine Menge von Lehrern beschäftigt, zu
denen eine eindeutige Personalnummer sowie Name, Vorname, Geburtstag,
Adresse und Gehalt erfasst sind
Ein Lehrer ist qualifiziert für eine Menge von Fächern, z.B. Biologie (Bio) und
Sport (Sp), und kann für eine Klasse als Klassenlehrer zugeordnet werden
Die Realisierung des Stundenplans (Unterricht) besteht aus
- Eindeutiger Zuordnung von Lehrern, Fächern und Klassen
- Angabe von Wochentag, Schulstunde und einer Raumnummer
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 233
5.6.2 E/R-Modell
FID
Bezeichnung
Fach
(1,*)
PNr
(1,*)
SNr
Name
Name
Vorname
qualifiziert für
Schulform
Geburtstag
Gehalt
Adresse
(1,*)
Schule
(1,*)
beschäftigt
(1,1)
Lehrer
(1,*)
(0,1)
(1,*)
Wochentag
Adresse
unterrichtet
Stunde
RaumNr
leitet
KNr
umfasst
Datenbanksysteme
Schülerzahl
(1,1)
(1,1)
Klasse
RaumNr
(1,*)
Friedrich-Schiller-Universität Jena
Seite 234
5.6.3 Relationales Modell
Relationen, Attribute, Primärschlüssel und Fremdschlüssel
- Adresse (AdrNr, PLZ, Ort, Strasse, Hausnummer)
- Schule (SNr, Name, Schulform, AdrNr)
- Fach (FID, Bezeichnung)
- Lehrer (PNr, Schule, Name, Vorname, AdrNr, Geburtstag, Gehalt)
- Klasse (KNr, Schule, Schülerzahl, RaumNr, Klassenlehrer)
- Qualifikation (PNr, FID)
- Unterricht (PNr, FID, KNr, Schule, Wochentag, Stunde, RaumNr)
Fremdschlüssel-Beziehungen (A B = A ist Fremdschlüssel auf B)
- Schule.AdrNr Adresse.AdrNr
- Lehrer.AdrNr Adresse.AdrNr, Lehrer.Schule Schule.SNr
- Klasse.Schule Schule.SNr, Klasse.Klassenlehrer Lehrer.PNr
- Qualifikation.PNr Lehrer.PNr, Qualifikation.FID Fach.FID
- (Unterricht.PNr, Unterricht.FID) (Qualifikation.PNr, Qualifikation.FID),
(Unterricht.KNr, Unterricht.Schule) (Klasse.KNr, Klasse.Schule)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 235
5.6.3 Relationales Modell
Integritätsbedingungen:
- Ein Lehrer darf nur Fächer unterrichten, für die er qualifiziert ist
• Abdeckung durch einen 2-attributigen Fremdschlüssel (PNr, FID)
- Die Angabe des Wochentags erfolgt über 'Mo','Di','Mi','Do' oder 'Fr'
• Angabe einer CHECK-Klausel am Attribut Unterricht.Wochentag
- Die Angabe der Stunde erfolgt über die Zahlen 1 bis 12
• Angabe einer CHECK-Klausel am Attribut Unterricht.Stunde
- Für eine konkrete Kombination von Wochentag, Stunde und RaumNr darf
ein Lehrer bzw. eine Klasse nur einmal im Unterrichtsplan vorkommen
• UNIQUE-Bedingung über (Wochentag, Stunde, PNr)
• UNIQUE-Bedingung über (Wochentag, Stunde, KNr, Schule)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 236
5.6.4 Strukturdefinition in SQL (DDL)
CREATE TABLE Adresse (
AdrNr SMALLINT NOT NULL PRIMARY KEY,
PLZ CHAR(5) NOT NULL,
Ort VARCHAR(20) NOT NULL,
Strasse VARCHAR(20) NOT NULL,
Hausnummer SMALLINT NOT NULL)
CREATE TABLE Schule (
SNr SMALLINT NOT NULL PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
Schulform VARCHAR(20) NOT NULL,
AdrNr SMALLINT NOT NULL,
FOREIGN KEY (AdrNr) REFERENCES Adresse(AdrNr))
CREATE TABLE Fach (
FID CHAR(3) NOT NULL PRIMARY KEY,
Bezeichnung VARCHAR(20) NOT NULL)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 237
5.6.4 Strukturdefinition in SQL (DDL)
CREATE TABLE Lehrer (
PNr SMALLINT NOT NULL PRIMARY KEY,
Schule SMALLINT NOT NULL,
Name VARCHAR(20) NOT NULL,
Vorname VARCHAR(20) NOT NULL,
AdrNr SMALLINT NOT NULL,
Geburtstag DATE NOT NULL,
Gehalt DECIMAL(7,2),
FOREIGN KEY (Schule) REFERENCES Schule(SNr),
FOREIGN KEY (AdrNr) REFERENCES Adresse(AdrNr))
CREATE TABLE Klasse (
KNr VARCHAR(6) NOT NULL,
Schule SMALLINT NOT NULL,
Schuelerzahl SMALLINT NOT NULL,
RaumNr SMALLINT,
Klassenlehrer SMALLINT NOT NULL,
PRIMARY KEY (KNr, Schule),
FOREIGN KEY (Schule) REFERENCES Schule(SNr),
FOREIGN KEY (Klassenlehrer) REFERENCES Lehrer(PNr))
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 238
5.6.4 Strukturdefinition in SQL (DDL)
CREATE TABLE Qualifikation (
PNr SMALLINT NOT NULL,
FID CHAR(3) NOT NULL,
PRIMARY KEY (PNr, FNr),
FOREIGN KEY (PNr) REFERENCES Lehrer(PNr),
FOREIGN KEY (FID) REFERENCES Fach(FID))
CREATE TABLE Unterricht (
PNr SMALLINT NOT NULL,
FID CHAR(3) NOT NULL,
KNr VARCHAR(6) NOT NULL,
Schule SMALLINT NOT NULL,
Wochentag CHAR(2) NOT NULL CHECK (Wochentag IN ('Mo','Di','Mi','Do','Fr')),
Stunde SMALLINT NOT NULL CHECK (Stunde BETWEEN 1 AND 12),
RaumNr SMALLINT NOT NULL,
PRIMARY KEY (Wochentag, Stunde, RaumNr),
FOREIGN KEY (PNr, FID) REFERENCES Qualifikation(PNr, FID),
FOREIGN KEY (KNr, Schule) REFERENCES Klasse(KNr, Schule))
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 239
5.6.4 Strukturdefinition in SQL (DDL)
ALTER TABLE Unterricht
ADD CONSTRAINT Lehrerzeit UNIQUE (Wochentag, Stunde, PNr)
ADD CONSTRAINT Klassenzeit UNIQUE (Wochentag, Stunde, KNr, Schule)
Ausführung in DB2 auf der Datenbank SLV:
- DB2-Client starten: ssh iibm08.inf.uni-jena.de
- Datenbank anlegen: db2 create database slv
- Datenbank verbinden: db2 connect to slv
- Datenbankstrukturen aufbauen (DDL):
• Einzelne Anweisungen der Folien aus Abschnitt 5.6.4
• Bündelung per Skript: db2 -t -f db2_schule_ddl.sql
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 240
5.6.4 Strukturdefinition für IBM DB2
------------------------------------------------------------------ Skript zur Erzeugung der Relationen in SLV (IBM DB2)
----------------------------------------------------------------DROP
DROP
DROP
DROP
DROP
DROP
DROP
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
Adresse;
Schule;
Fach;
Lehrer;
Klasse;
Qualifikation;
Unterricht;
CREATE TABLE Adresse (
AdrNr SMALLINT NOT NULL PRIMARY KEY,
PLZ CHAR(5) NOT NULL,
Ort VARCHAR(20) NOT NULL,
Strasse VARCHAR(20) NOT NULL,
Hausnummer SMALLINT NOT NULL);
CREATE TABLE Schule (
SNr SMALLINT NOT NULL PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
Schulform VARCHAR(20) NOT NULL,
AdrNr SMALLINT NOT NULL,
FOREIGN KEY (AdrNr) REFERENCES Adresse(AdrNr));
CREATE TABLE Fach (
FID CHAR(3) NOT NULL PRIMARY KEY,
Bezeichnung VARCHAR(20) NOT NULL);
CREATE TABLE Lehrer (
PNr SMALLINT NOT NULL PRIMARY KEY,
Schule SMALLINT NOT NULL,
Name VARCHAR(20) NOT NULL,
Vorname VARCHAR(20) NOT NULL,
AdrNr SMALLINT NOT NULL,
Geburtstag DATE NOT NULL,
Gehalt DECIMAL(7,2),
FOREIGN KEY (Schule) REFERENCES Schule(SNr),
FOREIGN KEY (AdrNr) REFERENCES Adresse(AdrNr));
Datenbanksysteme
CREATE TABLE Klasse (
KNr VARCHAR(6) NOT NULL,
Schule SMALLINT NOT NULL,
Schuelerzahl SMALLINT NOT NULL,
RaumNr SMALLINT,
Klassenlehrer SMALLINT NOT NULL,
PRIMARY KEY (KNr, Schule),
FOREIGN KEY (Schule) REFERENCES Schule(SNr),
FOREIGN KEY (Klassenlehrer) REFERENCES Lehrer(PNr));
CREATE TABLE Qualifikation (
PNr SMALLINT NOT NULL,
FID CHAR(3) NOT NULL,
PRIMARY KEY (PNr, FID),
FOREIGN KEY (PNr) REFERENCES Lehrer(PNr),
FOREIGN KEY (FID) REFERENCES Fach(FID));
CREATE TABLE Unterricht (
PNr SMALLINT NOT NULL,
FID CHAR(3) NOT NULL,
KNr VARCHAR(6) NOT NULL,
Schule SMALLINT NOT NULL,
Wochentag CHAR(2) NOT NULL CHECK (Wochentag IN
('Mo','Di','Mi','Do','Fr')),
Stunde SMALLINT NOT NULL CHECK (Stunde BETWEEN 1 AND 12),
RaumNr SMALLINT NOT NULL,
PRIMARY KEY (Wochentag, Stunde, RaumNr),
FOREIGN KEY (PNr, FID) REFERENCES Qualifikation(PNr, FID),
FOREIGN KEY (KNr, Schule) REFERENCES Klasse(KNr, Schule));
ALTER TABLE Unterricht
ADD CONSTRAINT Lehrerzeit UNIQUE (Wochentag, Stunde, PNr)
ADD CONSTRAINT Klassenzeit UNIQUE (Wochentag, Stunde, KNr,
Schule);
Friedrich-Schiller-Universität Jena
Seite 241
5.6.4 Strukturdefinition für MS Access
------------------------------------------------------------------ Skript zur Erzeugung der Relationen in SLV (MS Access)
----------------------------------------------------------------DROP
DROP
DROP
DROP
DROP
DROP
DROP
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
Adresse;
Schule;
Fach;
Lehrer;
Klasse;
Qualifikation;
Unterricht;
CREATE TABLE Adresse (
AdrNr SMALLINT NOT NULL PRIMARY KEY,
PLZ CHAR(5) NOT NULL,
Ort VARCHAR(20) NOT NULL,
Strasse VARCHAR(20) NOT NULL,
Hausnummer SMALLINT NOT NULL);
CREATE TABLE Schule (
SNr SMALLINT NOT NULL PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
Schulform VARCHAR(20) NOT NULL,
AdrNr SMALLINT NOT NULL,
FOREIGN KEY (AdrNr) REFERENCES Adresse(AdrNr));
CREATE TABLE Fach (
FID CHAR(3) NOT NULL PRIMARY KEY,
Bezeichnung VARCHAR(20) NOT NULL);
CREATE TABLE Lehrer (
PNr SMALLINT NOT NULL PRIMARY KEY,
Schule SMALLINT NOT NULL,
Name VARCHAR(20) NOT NULL,
Vorname VARCHAR(20) NOT NULL,
AdrNr SMALLINT NOT NULL,
Geburtstag DATE NOT NULL,
Gehalt DOUBLE,
FOREIGN KEY (Schule) REFERENCES Schule(SNr),
FOREIGN KEY (AdrNr) REFERENCES Adresse(AdrNr));
Datenbanksysteme
CREATE TABLE Klasse (
KNr VARCHAR(6) NOT NULL,
Schule SMALLINT NOT NULL,
Schuelerzahl SMALLINT NOT NULL,
RaumNr SMALLINT,
Klassenlehrer SMALLINT NOT NULL,
PRIMARY KEY (KNr, Schule),
FOREIGN KEY (Schule) REFERENCES Schule(SNr),
FOREIGN KEY (Klassenlehrer) REFERENCES Lehrer(PNr));
CREATE TABLE Qualifikation (
PNr SMALLINT NOT NULL,
FID CHAR(3) NOT NULL,
PRIMARY KEY (PNr, FID),
FOREIGN KEY (PNr) REFERENCES Lehrer(PNr),
FOREIGN KEY (FID) REFERENCES Fach(FID));
CREATE TABLE Unterricht (
PNr SMALLINT NOT NULL,
FID CHAR(3) NOT NULL,
KNr VARCHAR(6) NOT NULL,
Schule SMALLINT NOT NULL,
Wochentag CHAR(2) NOT NULL,
Stunde SMALLINT NOT NULL,
RaumNr SMALLINT NOT NULL,
PRIMARY KEY (Wochentag, Stunde, RaumNr),
FOREIGN KEY (PNr, FID) REFERENCES Qualifikation(PNr, FID),
FOREIGN KEY (KNr, Schule) REFERENCES Klasse(KNr, Schule));
ALTER TABLE Unterricht
ADD CONSTRAINT Lehrerzeit UNIQUE (Wochentag, Stunde, PNr),
CONSTRAINT Klassenzeit UNIQUE (Wochentag, Stunde, KNr, Schule);
-- Gültigkeitsregeln über die Tabellen-Entwurfs-Oberfläche –-- Wochentag: In ('Mo';'Di';'Mi';'Do';'Fr')
-- Stunde: Between 1 and 12
Friedrich-Schiller-Universität Jena
Seite 242
5.6.5 Datenbefüllung mit SQL (DML)
Allgemeines für das Einfügen von Datensätzen
- Künstliche Schlüssel (z.B. AdrNr in Adresse) können frei vergeben
werden, mögliche Variante: beginnend bei 1 aufsteigende Schlüssel
- Reihenfolge der Einfüge-Anweisungen beachten (Fremdschlüssel!)
"Erbauung" einer Schule
- Name 'Zum Erfolg', Schulform Gymnasium
- Standort ist in der Schulstrasse 55, 04550 Musterstadt
Definition von Klassen in der Schule 'Zum Erfolg'
- Klasse 10a
• 21 Schüler, Klassenzimmer 47
• Klassenlehrerin Fr. Schmidt
- Klasse 5b
• 25 Schüler, Klassenzimmer 13
• Klassenlehrer Hr. Müller
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 243
5.6.5 Datenbefüllung mit SQL (DML)
Einführung von allgemeinen Schulfächern
- Mathematik (Ma), Deutsch (De), Biologie (Bio), Geschichte (Ge)
- Sport (Sp), Informatik (Inf), Geographie (Geo), Physik (Ph)
Einstellung von Lehrern
- Georg Müller
• geboren am 5.5.1960, wohnt 'Zur hohlen Gasse 12' in 04450
Musterstadt, verdient 33476.90
• beschäftigt in der Schule 'Zum Erfolg'
• Fachqualifikation für Deutsch und Geschichte
- Ulla Schmid
• geboren am 12.10.1979, wohnt in 'Hauptstrasse 7' in 04451
Nachbardorf, verdient 28670.00
• beschäftigt in der Schule 'Zum Erfolg'
• Fachqualifikation für Mathematik, Informatik und Sport
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 244
5.6.5 Datenbefüllung mit SQL (DML)
Erstellung des Stundenplans
- Herr Müller unterrichtet Geschichte in
• Klasse 10a am Dienstag, 2. Stunde im Raum 16
• Klasse 10a am Mittwoch, 4. Stunde im Raum 14
- Herr Müller unterrichtet Deutsch in
• Klasse 5b am Dienstag, 3. Stunde im Raum 15
• Klasse 10a am Freitag, 2. Stunde im Raum 4
- Frau Schmidt unterrichtet Mathematik in
• Klasse 5b am Montag, 2. Stunde im Raum 19
• Klasse 10a am Dienstag, 1. Stunde im Raum 5
- Frau Schmidt unterrichtet Informatik in
• Klasse 5b am Montag, 3. Stunde im Raum 19
• Klasse 10a am Dienstag, 5. Stunde im Raum 5
Ausführung per Skript: db2 -t -f db2_schule_dml.sql
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 245
5.6.5 Datenbefüllung mit SQL (DML)
-------------------------------------------------------------------- Skript zur Befüllung der Relationen für die Modellwelt Schule -------------------------------------------------------------------INSERT INTO Adresse (AdrNr, PLZ, Ort, Strasse, Hausnummer) VALUES (1, '04450', 'Musterstadt', 'Schulstrasse', 55);
INSERT INTO Adresse (AdrNr, PLZ, Ort, Strasse, Hausnummer) VALUES (2, '04450', 'Musterstadt', 'Zur hohlen Gasse', 12);
INSERT INTO Adresse (AdrNr, PLZ, Ort, Strasse, Hausnummer) VALUES (3, '04451', 'Nachbardorf', 'Hauptstrasse', 7);
INSERT INTO Schule (SNr, Name, Schulform, AdrNr) VALUES (1, 'Zum Erfolg', 'Gymnasium', 1);
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
Fach
Fach
Fach
Fach
Fach
Fach
Fach
Fach
(FID,
(FID,
(FID,
(FID,
(FID,
(FID,
(FID,
(FID,
Bezeichnung)
Bezeichnung)
Bezeichnung)
Bezeichnung)
Bezeichnung)
Bezeichnung)
Bezeichnung)
Bezeichnung)
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
('Ma', 'Mathematik');
('Deu', 'Deutsch');
('Bio', 'Biologie');
('Ge', 'Geschichte');
('Sp', 'Sport');
('Inf', 'Informatik');
('Geo', 'Geographie');
('Ph', 'Physik');
INSERT INTO Lehrer (PNr, Schule, Name, Vorname, AdrNr, Geburtstag, Gehalt) VALUES (1, 1, 'Müller', 'Georg', 2, '5.5.1960', 33476.90);
INSERT INTO Lehrer (PNr, Schule, Name, Vorname, AdrNr, Geburtstag, Gehalt) VALUES (2, 1, 'Schmid', 'Ulla', 3, '12.10.1979', 28670.00);
INSERT INTO Klasse (KNr, Schule, Schuelerzahl, RaumNr, Klassenlehrer) VALUES ('10a', 1, 21, 47, 2);
INSERT INTO Klasse (KNr, Schule, Schuelerzahl, RaumNr, Klassenlehrer) VALUES ('5b', 1, 25, 13, 1);
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
Qualifikation
Qualifikation
Qualifikation
Qualifikation
Qualifikation
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
Unterricht
Unterricht
Unterricht
Unterricht
Unterricht
Unterricht
Unterricht
Unterricht
Datenbanksysteme
(PNr,
(PNr,
(PNr,
(PNr,
(PNr,
(PNr,
(PNr,
(PNr,
(PNr,
(PNr,
(PNr,
(PNr,
(PNr,
FID)
FID)
FID)
FID)
FID)
FID,
FID,
FID,
FID,
FID,
FID,
FID,
FID,
VALUES
VALUES
VALUES
VALUES
VALUES
KNr,
KNr,
KNr,
KNr,
KNr,
KNr,
KNr,
KNr,
(1,
(1,
(2,
(2,
(2,
Schule,
Schule,
Schule,
Schule,
Schule,
Schule,
Schule,
Schule,
'Deu');
'Ge');
'Ma');
'Inf');
'Sp');
Wochentag,
Wochentag,
Wochentag,
Wochentag,
Wochentag,
Wochentag,
Wochentag,
Wochentag,
Stunde,
Stunde,
Stunde,
Stunde,
Stunde,
Stunde,
Stunde,
Stunde,
RaumNr)
RaumNr)
RaumNr)
RaumNr)
RaumNr)
RaumNr)
RaumNr)
RaumNr)
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
(1,
(1,
(1,
(1,
(2,
(2,
(2,
(2,
Friedrich-Schiller-Universität Jena
'Ge',
'Ge',
'Deu',
'Deu',
'Ma',
'Ma',
'Inf',
'Inf',
'10a',
'10a',
'5b',
'10a',
'5b',
'10a',
'5b',
'10a',
1,
1,
1,
1,
1,
1,
1,
1,
'Di',
'Mi',
'Di',
'Fr',
'Mo',
'Di',
'Mo',
'Di',
2,
4,
3,
2,
2,
1,
3,
5,
16);
14);
15);
4);
19);
5);
19);
6);
Seite 246
5.6.6 Datenänderung-/zugriff mit SQL (DML)
1. Anzeige aller Fächer, die auf 'ik' enden
2. Anzeige der Anzahl von Unterrichtsstunden für die Klasse 5b (in der Schule
'Zum Erfolg') am Montag
3. Anzeige aller Informationen für die Schule 'Zum Erfolg', wobei die Adresse
vollständig aufgeschlüsselt dargestellt werden soll
4. Ausgabe des Gesamtstundeplans für die Klasse 10a, aufsteigend sortiert
nach Wochentag und Stunde mit Anzeige des unterrichtenden Lehrers
5. Anzeige der Anzahl von Unterrichtsstunden je Wochentag für Herrn Müller
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 247
5.6.6 Datenänderung-/zugriff mit SQL (DML)
6. Änderung der Adresse von Herrn Müller, da er aus 'Zur hohlen Gasse 12' in
die Strasse 'Holzweg 5' umgezogen ist
Änderung des bestehenden Adress-Eintrages?
Zuordnung eines neuen Adress-Eintrages?
7. Entfernen der Fächer 'Sport' und 'Informatik', weil sie im neuen Lehrplan
nicht mehr gebraucht werden
Was passiert und warum?
Was passiert, wenn nur das Fach 'Sport' gelöscht wird?
8. Änderung im Stundenplan: die Mathematik-Stunde in der Klasse 10a am
Dienstag zur ersten Stunde soll auf die zweite Stunde verlegt werden
Was passiert und warum?
Was passiert bei einer Verlegung auf die vierte Stunde?
9. Zuordnung von Herrn Müller als Klassenlehrer für Klasse 10a
Änderung zulässig?
Lösung zur Absicherung der 1:1-Zuordnung Klasse/Klassenlehrer?
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 248
5.6.6 Datenänderung-/zugriff mit SQL (DML)
--[ 6.
UPDATE
SET
WHERE
----------------------------------- Skript mit Aufgaben-Lösungen ------------------------------------[ 1.
SELECT
FROM
WHERE
Aufgabe]-*
Fach
Bezeichnung LIKE '%ik';
--[ 2.
SELECT
FROM
WHERE
AND
AND
Aufgabe]-COUNT(*)
Unterricht
KNr = '5b'
Wochentag = 'Mo'
Schule = 1;
--[ 7. Aufgabe]-DELETE
FROM Fach
WHERE Bezeichnung IN ('Sport','Informatik');
--[ 3. Aufgabe]-SELECT S.SNr, S.Name, S.Schulform,
A.PLZ, A.Ort, A.Strasse, A.Hausnummer
FROM Schule S INNER JOIN Adresse A ON S.AdrNr = A.AdrNr
WHERE S.Name = 'Zum Erfolg';
--[ 4. Aufgabe]-SELECT L.Name, U.FID, U.KNr, U.Wochentag, U.Stunde,
U.RaumNr
FROM Unterricht U INNER JOIN Lehrer L ON U.PNr = L.PNR
WHERE U.KNr = '10a'
AND U.Schule = 1
ORDER BY U.Wochentag, U.Stunde;
--[ 5.
SELECT
FROM
WHERE
GROUP
Aufgabe]-Adresse
Strasse = 'Holzweg', Hausnummer = 5
AdrNr = (SELECT AdrNr
FROM Lehrer
WHERE Name = 'Müller');
--[ 8.
UPDATE
SET
WHERE
AND
AND
AND
Aufgabe]-Unterricht
Stunde = 2
KNr = '10a'
FID = 'Ma'
Wochentag = 'Di'
Stunde = 1;
--[ 9. Aufgabe]-UPDATE Klasse
SET Klassenlehrer = (SELECT PNr
FROM Lehrer
WHERE Name = 'Müller')
WHERE KNr = '10a';
Aufgabe]-Wochentag, COUNT(*) AS Stundenzahl
Unterricht U INNER JOIN Lehrer L ON U.PNr = L.PNr
L.Name = 'Müller'
BY Wochentag;
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 249
Übungsfragen
Einleitung
1. Nennen Sie 7 Anforderungen an eine moderne Datenhaltung!
2. In welcher Beziehung stehen die Begriffe DBVS und DB zueinander?
Welche Informationen werden neben den eigentlichen Nutzdaten
ebenfalls in einer Datenbank persistiert?
3. Nennen Sie die 3 Ebenen ANSI-/SPARC-Architektur und ordnen Sie
folgende Begriffe zu: Tabelle, Satz, Index, Relation, Sicht,
Integritätsbedingung
E/R-Datenmodellierung
4. Grenzen Sie die Begriffe Entity, Entitytyp und Entityset voneinander ab!
5. Was bedeutet Schlüsselminimalität?
6. Nennen Sie zwei mögliche Notationsformen für Kardinalitäten von
Beziehungstypen und erklären Sie kurz die Unterschiede!
7. Was kennzeichnet virtuelle Attribute und wie können diese später mit
SQL realisiert werden?
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 250
Übungsfragen
Relationales Modell
8. Erstellen Sie das relationale Modell (Relationssschema, Schlüssel,
Beziehungen) zu folgendem Beispiel:
• Eine Abteilung hat eine eindeutige Abteilungsnummer, einen Ort und
ein Budget
• Ein Mitarbeiter hat eine eindeutige Personalnummer, Name und
Vorname
• Mitarbeiter sind genau einer Abteilung zugeordnet, Abteilungen
können beliebig viele Mitarbeiter umfassen
Relationenalgebra
9. Formulieren Sie in der Relationen-Algebra auf der bekannten Tabelle
Buch die folgende Anfrage:
• "Gesucht sind Inventarnumer und Titel aller Bücher, deren Autor
'Wirth' oder 'Heuer' ist"
10. Welche Eigenschaft kennzeichnet eine minimale Relationenalgebra
bezüglich der (Basis-)Operationen?
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 251
Übungsfragen
SQL
11. Zeigen Sie die Unterschiede zwischen Relationen des relationen Modells
und Tabellen in SQL auf. Gehen Sie dabei auf die Mengeneigenschaft
und Attributmenge ein.
12. Erläutern Sie die Begriffe DDL und DML! Ordnen Sie diesen beiden
Gruppen folgende SQL-Konstrukte zu: INSERT, CREATE TABLE,
SELECT, CREATE INDEX, DROP TABLE, DELETE
13. Formulieren Sie in SQL auf Basis des Relationsschemas
Buch(InvNr, Titel, ISBN, Autor)
die folgende Anfrage
• "Gesucht sind Inventarnumer und Titel aller Bücher, deren Autor
'Wirth' oder 'Heuer' ist"
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 252