Musterlösung - Die DBS

Institut für Informationssysteme
Dr. C. Türker
Eidgenössische Technische Hochschule Zürich
Swiss Federal Institute of Technology Zurich
Objektrelationale, erweiterbare Datenbanken WS 04/05
Übung 1
Beispiellösung
Person
{abstract}
Name
Geburtstag
Adresse
Telefone
Email
Übungsmodus: Die Übungsblätter werden in der Vorlesung ausgeteilt. Mit Ausnahme des
ersten Übungsblattes werden die Übungsblätter in der jeweils darauffolgenden Woche in der
Übung besprochen.
Im Rahmen der Übungen wollen wir die verschiedenen Konzepte, die in der Vorlesung eingeführt
werden, anhand eines durchgehenden Anwendungsszenarios vertiefen. Insbesondere sollen damit
die Unterschiede zwischen den verschiedenen Ansätzen verdeutlicht und der Einsatz kommerzieller objektrelationaler Datenbanken diskutiert werden. Die Aufgaben sollen soweit es geht,
praktisch an Datenbanksystemen umgesetzt werden. Wir werden hier hauptsächlich IBM DB2
und Oracle verwenden.
Informationen über den Zugang zu DB2 bzw. Oracle sind der Web-Seite der Vorlesung zu
entnehmen: http://www.dbs.ethz.ch/∼ordb
{overlapping, complete}
Bestellung
Bestellnummer
Eingangsdatum
Status
Anwendungsszenario: Wir gehen von einem einfachen Informationssystem für einen Lieferservice für Lebensmittel und Haushaltwaren aus. Die Modellierung des Anwendungsszenarios in
UML ist in Abbildung 1 zu sehen.
Einzelne Aspekte sollen im folgenden genauer vorgestellt werden:
4. Artikel werden in Katalogen aufgeführt, die gelöscht werden können. Artikel müssen allerdings immer in einem Katalog angeboten werden, sonst sind sie zu löschen.
Kundennummer
Kreditlimit
{Kreditlimit < 5000}
Personalnummer
Gehalt {Gehalt > 2000}
0..*
1
Nummer
Bezeichnung
Preis
Beschreibung
Bilder
1..*
Position
1. Relevant sind Personen, wobei es eine nicht-disjunkte Unterteilung in Kunden und in
Angestellte gibt. Jede in der Datenbank erfaßte Person ist entweder ein Kunde oder
ein Angestellter. Eine Person kann mehrere Telefone besitzen, d.h. unter verschiedenen
Telefonnummern erreichbar sein.
3. Eine Bestellung besteht aus einer oder mehreren Positionen, welche angeben, wie oft der
Artikel gewünscht wird.
Angestellter
1
1
Anzahl
2. Kunden dürfen/sollen Bestellungen aufgeben. Da aber nur an Personen über 18 Jahren
geliefert werden soll, wird das Geburtsdatum vorher geprüft. Weiterhin ist eine Bestellung
zurückzuweisen, wenn der Kunde nicht kreditwürdig ist. Eine Bestellung eines Kunden
wird nur ausgeliefert, falls die Summe der Werte der Bestellung und aller ausgelieferten,
aber noch nicht bezahlten Waren, kleiner als das Kreditlimit ist.
Kunde
0..*
Artikel
2..*
1..*
Titel
Jahr
Sommer/Winter
{disjoint, incomplete}
0..1
ArtikelSet
Lebensmittel
Gewicht
Zusammensetzung
Haushaltswaren
Garantie
Farbe
Abbildung 1: UML-Schema für das Anwendungsszenario
5. Der Lieferservice konzentriert sich auf Lebensmittel, bietet aber auch andere Artikel an.
Ein Sonderfall dieser übrigen Artikel stellen die Haushaltswaren dar, weil hier bei der Bestellung die Farbe angegeben werden muss. Weiterhin können mehrere Artikel zusammen
als Set angeboten werden. Ein solches kann aus beliebigen, aber mindestens zwei Artikeln
bestehen.
1
Katalog
0..*
2
Aufgabe 1: Relationale Umsetzung von Klassenhierarchien
Setzen Sie die Klassenhierarchie Personen“ bestehend aus den Klassen Person, Kunde und
”
Angestellter in einer relationalen Datenbank um. Berücksichtigen Sie dabei
1. die Mengenwertigkeit des Attributs Telefone und
2. die modellierten Integritätsbedingungen (inkl. die Bedingungen an die Klassenhierarchie:
abstrakte Wurzelklasse und extensional überlappende Subklassen).
Überlegen Sie sich genau, wie Sie die gegebene Tabellenhierarchie nur mit Hilfe von Tupeltabellen und zusätzlichen Integritätsbedingungen am besten abbilden, ohne dabei die modellierte
Semantik zu verlieren bzw. zu ändern. Geht das überhaupt vollständig, ohne weitere Konstrukte?
Setzen Sie Ihre Lösung mit Hilfe von DB2 um. Weisen Sie den Tabellenspalten passende (sinnvolle) Datentypen zu.
Lösungsvariante:
------------------------------------------------------------------ Erzeugen eines Sequenzgenerators zur Generierung der
-- künstlichen OID-Werte
----------------------------------------------------------------CREATE SEQUENCE OIDGEN
START WITH 1000
INCREMENT BY 1
MAXVALUE 9999
MINVALUE 1000
CYCLE $
------------------------------------------------------------------ Vertikale Partitionierung der Personen-Klassenhierarchie
------------------------------------------------------------------ Tabelle für die personenspezifischen Daten
----------------------------------------------------------------CREATE TABLE Person (
OID
INT NOT NULL PRIMARY KEY,
Name
VARCHAR(30) NOT NULL,
GebDatum Date,
Adresse VARCHAR(40),
Email
VARCHAR(40)
) $
------------------------------------------------------------------ Tabelle für das kollektionswertige Attribut Telefone
----------------------------------------------------------------CREATE TABLE TelNums (
OID
INT NOT NULL REFERENCES Person(OID) ON DELETE CASCADE,
TelNr VARCHAR(20) NOT NULL,
CONSTRAINT TelNumsUnique UNIQUE(OID, TelNr)
) $
3
------------------------------------------------------------------ Tabelle für die kundenspezifischen Daten
----------------------------------------------------------------CREATE TABLE Kunde (
OID
INT NOT NULL PRIMARY KEY REFERENCES Person(OID)
ON DELETE CASCADE,
KundenNr INT NOT NULL,
Limit
DECIMAL(12,2),
CONSTRAINT KundenNrUnique UNIQUE(KundenNr),
CONSTRAINT LimitZuHoch CHECK(Limit < 5000)
) $
------------------------------------------------------------------ Tabelle für die angestelltenspezifischen Daten
----------------------------------------------------------------CREATE TABLE Angestellter (
OID
INT NOT NULL PRIMARY KEY REFERENCES Person(OID)
ON DELETE CASCADE,
PersonalNr INT NOT NULL,
Gehalt
DECIMAL(12,2),
CONSTRAINT PersonalNrUnique UNIQUE(PersonalNr),
CONSTRAINT GehaltZuHoch CHECK(Gehalt > 2000)
) $
------------------------------------------------------------------ Die Fremdschlüssel mit der ON DELETE CASCADE Semantik sorgen
-- dafür, dass mit dem Löschen von Daten aus der "Supertabelle"
-- auch die abhängigen Daten aus den "Subtabellen" bzw. aus der
-- Kollektionstabelle automatisch gelöscht werden. Um das
-- Löschen in der anderen Richtung, das heisst von der Sub- zur
-- Supertabelle zu propagieren, könnten Trigger der folgenden
-- Form eingesetzt werden:
----------------------------------------------------------------CREATE TRIGGER KDeleteTrigger
AFTER DELETE ON Kunde
REFERENCING OLD AS OLD
FOR EACH ROW
MODE DB2SQL
DELETE FROM Person p WHERE p.OID = OLD.OID $
CREATE TRIGGER ADeleteTrigger
AFTER DELETE ON Angestellter
REFERENCING OLD AS OLD
FOR EACH ROW
MODE DB2SQL
DELETE FROM Person p WHERE p.OID = OLD.OID $
-----------------------------------------------------------------
4
-- Die folgenden Trigger sichern die Unveränderbarkeit der OIDs:
----------------------------------------------------------------CREATE TRIGGER PUpdateTrigger
NO CASCADE BEFORE UPDATE OF OID ON Person
FOR EACH ROW
MODE DB2SQL
SIGNAL SQLSTATE ’75000’ (’OID-Update on Person not allowed’) $
CREATE TRIGGER KUpdateTrigger
NO CASCADE BEFORE UPDATE OF OID ON Kunde
FOR EACH ROW
MODE DB2SQL
SIGNAL SQLSTATE ’75000’ (’OID-Update on Kunde not allowed’) $
CREATE TRIGGER AUpdateTrigger
NO CASCADE BEFORE UPDATE OF OID ON Angestellter
FOR EACH ROW
MODE DB2SQL
SIGNAL SQLSTATE ’75000’ (’OID-Update on Angestellter not allowed’) $
Das Problem ist allerdings, dass in diesem Fall aufgrund der Mehrfachspezialisierung komplexe, zyklische Abhängigkeiten entstehen, die DB2 und einige andere Systeme (auch Oracle) nicht unterstützen.
Das heißt, das Löschen wäre hier überhaupt nicht mehr möglich, weil DB2 jedes Löschen mit einer
Fehlermeldung abbrechen würde.
Folglich müssen alle Anwendungsprogramme sicherstellen, dass beim Löschen aus der Subtabelle die
zugehörigen Daten aus der Supertabelle ebenfalls gelöscht werden.
Aufgabe 2: Erzeugen von Objekten
Schreiben Sie SQL-Anweisungen, die Personen-Instanzen erzeugen. Die einzelnen Attributwerte
sind frei wählbar. Es gilt lediglich zu beachten, dass zu jedem der folgenden Personen-Gruppen“
”
mindestens eine Instanz erzeugt wird:
• Person, die nur ein Kunde ist.
• Person, die nur ein Angestellter ist.
• Person, die sowohl ein Kunde als auch ein Angestellter ist.
Alle Personen sollen mindestens eine Telefonnummer haben. Mindestens einer davon soll sogar
mehrere Telefonnummern besitzen.
Lösungsvariante: Das Erzeugen von Kunden und Angestellten erfordert in diesem Fall mindestens
zwei INSERT-Anweisungen. Wir schreiben hier Prozeduren, die genau dies tun.
------------------------------------------------------------------ Prozedur erzeugt Daten für Personen, die nur
-- Kunden sind (ohne Telefonnummern)
-----------------------------------------------------------------
5
CREATE PROCEDURE InsertKunde(KundenNr INT,
Limit DECIMAL(12,2),
Name VARCHAR(30),
GebDatum Date,
Adresse VARCHAR(40),
Email VARCHAR(40))
LANGUAGE SQL
BEGIN
DECLARE OID INTEGER;
SET OID = NEXTVAL FOR oidgen;
INSERT INTO Person VALUES (OID, Name, GebDatum, Adresse, Email);
INSERT INTO Kunde VALUES (OID, KundenNr, Limit);
END $
------------------------------------------------------------------ Prozedur erzeugt Daten für Personen, die nur
-- Angestellten sind (ohne Telefonnummern)
----------------------------------------------------------------CREATE PROCEDURE InsertAngestellter(PersonalNr INT,
Gehalt DECIMAL(12,2),
Name VARCHAR(30),
GebDatum Date,
Adresse VARCHAR(40),
Email VARCHAR(40))
LANGUAGE SQL
BEGIN
DECLARE OID INTEGER;
SET OID = NEXTVAL FOR oidgen;
INSERT INTO Person VALUES (OID, Name, GebDatum, Adresse, Email);
INSERT INTO Angestellter VALUES (OID, PersonalNr, Gehalt);
END $
------------------------------------------------------------------ Prozedur erzeugt Daten iPersonen, die Kunden und
-- Angestellte zugleich sind (ohne Telefonnummern)
----------------------------------------------------------------CREATE PROCEDURE InsertAngKunde(PersonalNr INT,
Gehalt DECIMAL(12,2),
KundenNr INT,
Limit DECIMAL(12,2),
Name VARCHAR(30),
GebDatum Date,
Adresse VARCHAR(40),
Email VARCHAR(40))
LANGUAGE SQL
BEGIN
DECLARE OID INTEGER;
SET OID = NEXTVAL FOR oidgen;
INSERT INTO Person VALUES (OID, Name, GebDatum, Adresse, Email);
6
INSERT INTO Angestellter VALUES (OID, PersonalNr, Gehalt);
INSERT INTO Kunde VALUES (OID, KundenNr, Limit);
END $
------------------------------------------------------------------ Erzeugen von Personen, die nur Kunden sind
----------------------------------------------------------------CALL InsertKunde(170470, 2750, ’Black’, ’1976-04-17’,
’Zürich’, ’[email protected]’) $
------------------------------------------------------------------ Erzeugen von Personen, die nur Angestellte sind
----------------------------------------------------------------CALL InsertAngestellter(1003, 5000, ’Rose’, ’1980-03-10’,
’Bad Schwalbach’, ’[email protected]’) $
------------------------------------------------------------------ Erzeugen von Personen, die Kunden und Angestellte sind
----------------------------------------------------------------CALL InsertAngKunde(9823, 4500, 210501, 2000, ’Jim’,
’2001-05-21’, ’München’, ’[email protected]’) $
------------------------------------------------------------------ Erzeugen von Telefonnummern
----------------------------------------------------------------INSERT INTO TelNums VALUES ((SELECT OID
FROM Person
WHERE Name = ’Black’),
’0041-1-2347658’) $
INSERT INTO TelNums VALUES ((SELECT OID
FROM Person
WHERE Name = ’Black’),
’0041-79-9912134’) $
INSERT INTO TelNums VALUES ((SELECT OID
FROM Person
WHERE Name = ’Jim’),
’0049-172-4539712’) $
INSERT INTO TelNums VALUES ((SELECT OID
FROM Person
WHERE Name = ’Rose’),
’0041-1-8565331’) $
INSERT INTO TelNums VALUES ((SELECT OID
FROM Person
WHERE Name = ’Rose’),
’0049-611-4512845’) $
Aufgabe 3: Anfragen an Klassenhierarchien
7
Schreiben Sie SQL-Anfragen, die folgendes ermitteln und ausgeben:
• Alle Personen mit den personenspezifischen Attributen.
• Alle Kunden mit den personenspezifischen Attributen.
• Alle Angestellten mit ihren Namen und den angestelltenspezifischen Attributen.
• Alle Personen, die zugleich Kunde und Angestellte sind. Für diese Personen sind sowohl
die kunden- als auch die angestelltenspezifischen Attribute auszugeben.
• Alle Personen, die mindestens eine ’schweizerische’ Telefonnummer besitzen. Geben Sie
für diese Personen ihren Namen und alle zugehörigen Telefonnummern aus.
• Alle Personen, die zwei oder mehr Telefonnummern besitzen. Geben Sie den Namen dieser
Personen mit der Gesamtanzahl ihrer Telefonnummern aus.
• Alle Paare von Personen, die exakt dieselbe Menge an Telefonnummern haben. Geben Sie
die Namen dieser Personen mit den ’deutschen’ Telefonnummern aus.
Lösungsvariante:
------------------------------------------------------------------ Alle Personen mit den personenspezifischen Attributen.
----------------------------------------------------------------SELECT Name, GebDatum, Adresse, Email, TelNr
FROM Person LEFT OUTER JOIN TelNums ON (Person.OID = TelNums.OID) $
------------------------------------------------------------------ Alle Kunden mit den personenspezifischen Attributen.
----------------------------------------------------------------SELECT Name, GebDatum, Adresse, Email, TelNr
FROM Person LEFT OUTER JOIN TelNums ON (Person.OID = TelNums.OID)
WHERE Person.OID IN (SELECT OID FROM Kunde) $
------------------------------------------------------------------ Alle Angestellten mit ihren Namen und den
-- angestelltenspezifischen Attributen.
----------------------------------------------------------------SELECT Name, PersonalNr, Gehalt
FROM Person JOIN Angestellter ON (Person.OID = Angestellter.OID) $
------------------------------------------------------------------ Alle Personen, die zugleich Kunde und Angestellte sind. Für
-- diese Personen sind sowohl die kunden- als auch die
-- angestelltenspezifischen Attribute auszugeben.
----------------------------------------------------------------SELECT Name, GebDatum, Adresse, Email, KendenNr, Limit,
PersonalNr, Gehalt
FROM Person JOIN
(SELECT Kunde.OID, KundenNr, Limit, PersonalNr, Gehalt
FROM Kunde JOIN Angestellter
8
ON (Kunde.OID = Angestellter.OID)) ka
ON (Person.OID = ka.OID) $
------------------------------------------------------------------ Alle Personen, die mindestens eine "schweizerische"
-- Telefonnummer besitzen. Geben Sie für diese Personen ihren
-- Namen und alle zugehörigen Telefonnummern aus.
----------------------------------------------------------------SELECT Name, TelNr
FROM Person JOIN TelNums ON (Person.OID =TelNums.OID)
WHERE Person.OID IN (SELECT OID
FROM TelNums
WHERE TelNr LIKE ’0041%’) $
------------------------------------------------------------------ Alle Personen, die zwei oder mehr Telefonnummern besitzen.
-- Geben Sie den Namen dieser Personen mit der Gesamtanzahl
-- ihrer Telefonnummern aus.
----------------------------------------------------------------SELECT Name, COUNT(TelNr)
FROM Person JOIN TelNums ON (Person.OID =TelNums.OID)
GROUP BY Person.OID, Name
HAVING COUNT(TelNr) >= 2 $
------------------------------------------------------------------ Alle Paare von Personen, die exakt dieselbe Menge an
-- Telefonnummern haben. Geben Sie die Namen dieser Personen
-- mit den "deutschen" Telefonnummern aus.
----------------------------------------------------------------SELECT DISTINCT p1.Name AS Name1,
CASE SUBSTR(t1.TelNr, 1, 4) WHEN ’0049’
THEN t1.TelNr ELSE NULL END AS TelNr1,
p2.Name AS Name2,
CASE SUBSTR(t2.TelNr, 1, 4) WHEN ’0049’
THEN t2.TelNr ELSE NULL END AS TelNr2
FROM Person p1, Person p2, TelNums t1, TelNums t2
WHERE p1.OID <> p2.OID AND
p1.OID = t1.OID AND
p2.OID = t2.OID AND
(t1.TelNr LIKE ’0049%’ OR t2.TelNr LIKE ’0049%’) AND
((SELECT COUNT(TelNr)
FROM Person p3 JOIN TelNums t ON (p3.OID = t.OID)
WHERE p3.OID = p1.OID)
= (SELECT COUNT(TelNr)
FROM Person p4 JOIN TelNums t ON (p4.OID = t.OID)
WHERE p4.OID = p2.OID)) $
Alternativlösung:
Statt der vertikalen Partitionierung könnte auch ein Universaltabellenansatz verfolgt werden, in
dem die jeweiligen Zeilen mit einer zusätzlichen Spalte ausgestattet werden, die genau angibt,
welchen Typ“ die Zeile hat.
”
------------------------------------------------------------------ Universaltabelle Person enthält alle Personen-Daten, auch die
-- kunden- und angestelltenspezifischen.
-- Spalte Typ gibt an, ob die Person
-- (K) ein Kunde, aber kein Angestellter ist,
-- (A) ein Angestellter, aber kein Kunde ist,
-- (P) ein Kunde und Angestellter zugleich ist.
-- Identitätsspalte generiert eindeutigen, unveränderlichen Wert
----------------------------------------------------------------CREATE TABLE Person (
OID INT UNIQUE GENERATED ALWAYS AS IDENTITY (START WITH 1000
INCREMENT BY 1
MAXVALUE 9999
MINVALUE 1000
CYCLE),
Name VARCHAR(30) NOT NULL,
GebDatum Date,
Adresse VARCHAR(40),
Email VARCHAR(40),
KundenNr INT,
Limit DECIMAL(12,2),
PersonalNr INT,
Gehalt DECIMAL(12,2),
Typ Char(1) NOT NULL,
CONSTRAINT LimitZuHoch CHECK(Limit < 5000),
CONSTRAINT GehaltZuHoch CHECK(Gehalt > 2000),
CONSTRAINT PersonTyp CHECK(Typ IN (’K’, ’A’, ’P’)),
CONSTRAINT KunTyp CHECK (Typ <> ’K’ OR (PersonalNr IS NULL AND
Gehalt IS NULL)),
CONSTRAINT AngTyp CHECK (Typ <> ’A’ OR (KundenNr IS NULL AND
Limit IS NULL))
) $
CREATE TABLE TelNums (
OID INT NOT NULL REFERENCES Person(OID) ON DELETE CASCADE,
TelNr VARCHAR(20) NOT NULL,
CONSTRAINT TelNumsUnique UNIQUE(OID, TelNr)
) $
-----------------------------------------------------------------
9
10
-- Wir haben bisher noch nicht sichergestellt, dass die KundenNr
-- und PersonalNr eindeutig sein müssen. Ein UNIQUE kann jedoch
-- in DB2-SQL nicht ohne ein NOT NULL angegebenen werden. Ein
-- NOT NULL dürfen wir aber nicht verwenden, weil sonst jede
-- Person eine Kunden- und eine Personalnummer haben müsste.
-- Daher müssen wir die beiden Eindeutigkeitsbedingung prozedural
-- mit Hilfe von Triggern lösen.
----------------------------------------------------------------CREATE TRIGGER KUpdateTrigger
NO CASCADE BEFORE UPDATE OF KundenNr ON Person
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
MODE DB2SQL
WHEN (NEW.KundenNr <> OLD.KundenNr AND
EXISTS(SELECT * FROM Person WHERE KundenNr = NEW.KundenNr))
SIGNAL SQLSTATE ’75000’ (’Update not allowed since it creates
duplicate of KundenNr’) $
------------------------------------------------------------------ Erzeugen von Personen, die nur Kunden sind
----------------------------------------------------------------INSERT INTO Person VALUES (DEFAULT, ’Black’, ’1976-04-17’,
’Zürich’, ’[email protected]’, 170470, 2750, NULL, NULL, ’K’) $
CREATE TRIGGER KInsertTrigger
NO CASCADE BEFORE INSERT ON Person
REFERENCING NEW AS NEW
FOR EACH ROW
MODE DB2SQL
WHEN (EXISTS(SELECT * FROM Person WHERE KundenNr = NEW.KundenNr))
SIGNAL SQLSTATE ’75000’ (’Insert not allowed since it creates
duplicate of KundenNr’) $
------------------------------------------------------------------ Erzeugen von Telefonnummern
----------------------------------------------------------------INSERT INTO TelNums VALUES ((SELECT OID
FROM Person
WHERE Name = ’Black’),
’0041-1-2347658’) $
INSERT INTO TelNums VALUES ((SELECT OID
FROM Person
WHERE Name = ’Black’),
’0041-79-9912134’) $
INSERT INTO TelNums VALUES ((SELECT OID
FROM Person
WHERE Name = ’Jim’),
’0049-172-4539712’) $
INSERT INTO TelNums VALUES ((SELECT OID
FROM Person
WHERE Name = ’Rose’),
’0041-1-8565331’) $
INSERT INTO TelNums VALUES ((SELECT OID
FROM Person
WHERE Name = ’Rose’),
’0049-611-4512845’) $
CREATE TRIGGER AUpdateTrigger
NO CASCADE BEFORE UPDATE OF PersonalNr ON Person
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
MODE DB2SQL
WHEN (NEW.PersonalNr <> OLD.PersonalNr AND
EXISTS(SELECT * FROM Person WHERE PersonalNr = NEW.PersonalNr))
SIGNAL SQLSTATE ’75000’ (’Update not allowed since it creates
duplicate of PersonalNr’) $
CREATE TRIGGER AInsertTrigger
NO CASCADE BEFORE INSERT ON Person
REFERENCING NEW AS NEW
FOR EACH ROW
MODE DB2SQL
WHEN (EXISTS(SELECT * FROM Person WHERE PersonalNr = NEW.PersonalNr))
SIGNAL SQLSTATE ’75000’ (’Insert not allowed since it creates
duplicate of PersonalNr’) $
11
------------------------------------------------------------------ Erzeugen von Personen, die nur Angestellte sind
----------------------------------------------------------------INSERT INTO Person VALUES (DEFAULT, ’Rose’, ’1980-03-10’,
’Bad Schwalbach’, ’[email protected]’, NULL, NULL, 1003, 5000, ’A’) $
------------------------------------------------------------------ Erzeugen von Personen, die Kunden und Angestellte sind
----------------------------------------------------------------INSERT INTO Person VALUES(DEFAULT, ’Jim’, ’2001-05-21’,
’München’, ’[email protected]’, 210501, 2000, 9823, 4500, ’P’) $
------------------------------------------------------------------ Alle Personen mit den personenspezifischen Attributen.
----------------------------------------------------------------SELECT Name, GebDatum, Adresse, Email, TelNr
12
FROM Person LEFT OUTER JOIN TelNums ON (Person.OID = TelNums.OID) $
HAVING COUNT(TelNr) >= 2 $
------------------------------------------------------------------ Alle Kunden mit den personenspezifischen Attributen.
----------------------------------------------------------------SELECT Name, GebDatum, Adresse, Email, TelNr
FROM Person LEFT OUTER JOIN TelNums ON (Person.OID = TelNums.OID)
WHERE Typ = ’K’ $
------------------------------------------------------------------ Alle Paare von Personen, die exakt dieselbe Menge an
-- Telefonnummern haben. Geben Sie die Namen dieser Personen
-- mit den "deutschen" Telefonnummern aus.
----------------------------------------------------------------SELECT DISTINCT p1.Name AS Name1,
CASE SUBSTR(t1.TelNr, 1, 4) WHEN ’0049’
THEN t1.TelNr ELSE NULL END AS TelNr1,
p2.Name AS Name2,
CASE SUBSTR(t2.TelNr, 1, 4) WHEN ’0049’
THEN t2.TelNr ELSE NULL END AS TelNr2
FROM Person p1, Person p2, TelNums t1, TelNums t2
WHERE p1.OID <> p2.OID AND
p1.OID = t1.OID AND
p2.OID = t2.OID AND
(t1.TelNr LIKE ’0049%’ OR t2.TelNr LIKE ’0049%’) AND
((SELECT COUNT(TelNr)
FROM Person p3 JOIN TelNums t ON (p3.OID = t.OID)
WHERE p3.OID = p1.OID)
= (SELECT COUNT(TelNr)
FROM Person p4 JOIN TelNums t ON (p4.OID = t.OID)
WHERE p4.OID = p2.OID)) $
------------------------------------------------------------------ Alle Angestellten mit ihren Namen und den
-- angestelltenspezifischen Attributen.
----------------------------------------------------------------SELECT Name, PersonalNr, Gehalt
FROM Person
WHERE Typ = ’A’ $
------------------------------------------------------------------ Alle Personen, die zugleich Kunde und Angestellte sind. Für
-- diese Personen sind sowohl die kunden- als auch die
-- angestelltenspezifischen Attribute auszugeben.
----------------------------------------------------------------SELECT Name, GebDatum, Adresse, Email, KundenNr, Limit,
PersonalNr, Gehalt
FROM Person
WHERE Typ = ’A’ $
------------------------------------------------------------------ Alle Personen, die mindestens eine "schweizerische"
-- Telefonnummer besitzen. Geben Sie für diese Personen ihren
-- Namen und alle zugehörigen Telefonnummern aus.
----------------------------------------------------------------SELECT Name, TelNr
FROM Person JOIN TelNums ON (Person.OID = TelNums.OID)
WHERE Person.OID IN (SELECT OID
FROM TelNums
WHERE TelNr LIKE ’0041%’) $
------------------------------------------------------------------ Alle Personen, die zwei oder mehr Telefonnummern besitzen.
-- Geben Sie den Namen dieser Personen mit der Gesamtanzahl
-- ihrer Telefonnummern aus.
----------------------------------------------------------------SELECT Name, COUNT(TelNr)
FROM Person JOIN TelNums ON (Person.OID = TelNums.OID)
GROUP BY Person.OID, Name
13
14