Relationales Modell eines Zoos:

Relationales Modell eines Zoos: Übungsaufgaben Erstellen Sie SQL-­‐Abfragen zu folgenden Aufgabenstellungen: 1. Ausgabe einer Telefonliste aller Mitarbeiter, die im Freigehege arbeiten, mit TierpflegerID, Anrede, Vorname, Name und Telefon geordnet nach Name 2. Die Tabelle Tierpfleger soll um die Emailadresse (email) erweitert werden. 3. Bestimmen Sie alle Schimpansen mit TierID, Name, Geschlecht und Geburtsdatum, die im Affenhaus untergebracht sind. 4. Bestimmen Sie die Gehegenamen und die Anzahl der Tierpfleger, die in den einzelnen Gehegen arbeiten, geordnet nach Gehegename! 5. Richten Sie ein Elefantenhaus ein. 6. Welche Tiere sind im Affenhaus untergebracht (geordnet nach Gattung)? 7. Welche Tiere im Zoo bekommen um 9 Uhr Bananen (mit TierID, Name und Gattungsname)? 8. Die Fortpflanzungsversuche im Gehege Streichelzoo waren erfolgreich. Am 28.11.2007 wurde das Eselbaby Eisbein (weiblich) geboren. Aktualisieren Sie die Datenbank entsprechend. 9. Ermitteln Sie alle Gehege, für die noch keine Gehegebetreuung festgelegt wurde. 10. Welche Tiere (mit Gattungsangabe) erhalten kein Futter? 11. Welche Tiergehege sind nicht belegt? 12. Welche Tiergehege sind mit mehr als 2 Tieren belegt? Die Datenbank Zoo ausprobieren Erstellen Sie die Datenbank ‚zoo’ in phpMyAdmin von XAMPP. In dieser Datenbank wählen sie dann den Reiter ‚SQL’, kopieren Sie alles folgende in das Textfeld und bestätigen mit ‚OK’. -- Datenbank: `zoo`
-- ---------------------------------------------------------- Tabellenstruktur für Tabelle `futter`
-DROP TABLE IF EXISTS `futter`;
CREATE TABLE IF NOT EXISTS `futter` (
`FutterID` int(11) NOT NULL auto_increment,
`Futtername` varchar(30) collate latin1_german1_ci NOT NULL,
`Futterbeschreibung` varchar(100) collate latin1_german1_ci NOT NULL,
PRIMARY KEY (`FutterID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
--- Daten für Tabelle `futter`
-INSERT INTO `futter` (`FutterID`, `Futtername`, `Futterbeschreibung`)
VALUES (1, 'Brot', ''),
(2, 'Obst', ''),
(3, 'Fischfutter trocken', ''),
(4, 'Fleisch', ''),
(5, 'Gemuese', ''),
(6, 'Fisch', ''),
(7, 'Hafer', ''),
(8, 'Heu', ''),
(9, 'Maus', ''),
(10, 'Banane', '');
-- ---------------------------------------------------------- Tabellenstruktur für Tabelle `futterration`
-DROP TABLE IF EXISTS `futterration`;
CREATE TABLE IF NOT EXISTS `futterration` (
`FutterrationsID` int(11) NOT NULL auto_increment,
`TierID` int(11) NOT NULL,
`FutterID` int(11) NOT NULL,
`Menge` varchar(30) collate latin1_german1_ci NOT NULL,
`Fuetterungszeit` time NOT NULL,
PRIMARY KEY (`FutterrationsID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
--- Daten für Tabelle `futterration`
-INSERT INTO `futterration` (`FutterrationsID`, `TierID`, `FutterID`,
`Menge`, `Fuetterungszeit`) VALUES (1, 2, 3, 'klein', '11:00:00'),
(2, 1, 3, 'klein', '11:00:00'),
(3, 3, 2, '0.5 kg', '09:00:00'),
(4, 4, 2, '0.5 kg', '09:00:00'),
(5, 3, 5, '1 kg', '14:00:00'),
(6, 4, 5, '1kg', '14:00:00'),
(7, 5, 7, '1 Fuder', '11:00:00'),
(8, 6, 7, '1 Fuder', '11:00:00'),
(9, 5, 8, '1 Gabel', '16:00:00'),
(10, 6, 8, '1 Gabel', '16:00:00'),
(11, 7, 6, '0.5 kg', '09:00:00'),
(12, 8, 6, '0.5 kg', '09:00:00'),
(13, 7, 9, '2', '13:00:00'),
(14, 8, 9, '2', '13:00:00');
-- ---------------------------------------------------------- Tabellenstruktur für Tabelle `gattung`
-DROP TABLE IF EXISTS `gattung`;
CREATE TABLE IF NOT EXISTS `gattung` (
`GattungsID` int(11) NOT NULL auto_increment,
`Gattungsname` varchar(30) collate latin1_german1_ci NOT NULL,
`Beschreibung` varchar(200) collate latin1_german1_ci NOT NULL,
PRIMARY KEY (`GattungsID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
--- Daten für Tabelle `gattung`
-INSERT INTO `gattung` (`GattungsID`, `Gattungsname`, `Beschreibung`)
VALUES
(1, 'Loewe', ''),
(2, 'Goldfisch', ''),
(3, 'Elefant', ''),
(4, 'Weissstorch', ''),
(5, 'Orang-Utan', ''),
(6, 'Schimpanse', ''),
(7, 'Haengebauchschwein', ''),
(9, 'Hausgans', ''),
(10, 'Esel', '');
-- ---------------------------------------------------------- Tabellenstruktur für Tabelle `gehege`
-DROP TABLE IF EXISTS `gehege`;
CREATE TABLE IF NOT EXISTS `gehege` (
`GehegeID` int(11) NOT NULL auto_increment,
`Gehegename` varchar(30) collate latin1_german1_ci NOT NULL,
PRIMARY KEY (`GehegeID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
--- Daten für Tabelle `gehege`
-INSERT INTO `gehege` (`GehegeID`, `Gehegename`) VALUES (1,
'Affenhaus'),
(2, 'Streichelzoo'),
(3, 'Aquarium'),
(4, 'Freigehege');
-- ---------------------------------------------------------- Tabellenstruktur für Tabelle `gehegebetreuung`
-DROP TABLE IF EXISTS `gehegebetreuung`;
CREATE TABLE IF NOT EXISTS `gehegebetreuung` (
`BetreuungsID` int(11) NOT NULL auto_increment,
`MitarbeiterID` int(11) NOT NULL,
`GehegeID` int(11) NOT NULL,
PRIMARY KEY (`BetreuungsID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
--- Daten für Tabelle `gehegebetreuung`
-INSERT INTO `gehegebetreuung` (`BetreuungsID`, `MitarbeiterID`,
`GehegeID`)
VALUES (1, 1, 1),
(2, 2, 2),
(3, 3, 3),
(4, 4, 4),
(5, 2, 1),
(6, 5, 1),
(7, 5, 4);
-- ---------------------------------------------------------- Tabellenstruktur für Tabelle `tier`
-DROP TABLE IF EXISTS `tier`;
CREATE TABLE IF NOT EXISTS `tier` (
`TierID` mediumint(9) NOT NULL auto_increment,
`Name` varchar(30) collate latin1_german1_ci NOT NULL,
`Geschlecht` varchar(10) collate latin1_german1_ci NOT NULL,
`Geburtsdatum` date NOT NULL,
`GattungsID` int(11) NOT NULL,
`GehegeID` int(11) NOT NULL,
PRIMARY KEY (`TierID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
--- Daten für Tabelle `tier`
-INSERT INTO `tier` (`TierID`, `Name`, `Geschlecht`, `Geburtsdatum`,
`GattungsID`, `GehegeID`) VALUES (1, 'Nemo', 'm', '2006-11-20', 2, 3),
(2, 'Eva', 'w', '2005-05-12', 2, 3),
(3, 'Hugo', 'm', '2004-01-01', 5, 1),
(4, 'Elise', 'w', '2004-03-03', 5, 1),
(5, 'Hansi', 'm', '2000-01-02', 10, 2),
(6, 'Wilhelmine', 'w', '0000-00-00', 10, 2),
(7, 'Omo', 'm', '1999-01-01', 3, 4),
(8, 'Uli', 'm', '2000-01-01', 4, 4),
(12, 'Horst', 'w', '2007-12-06', 2, 0);
-- ---------------------------------------------------------- Tabellenstruktur für Tabelle `tierpfleger`
-DROP TABLE IF EXISTS `tierpfleger`;
CREATE TABLE IF NOT EXISTS `tierpfleger` (
`TierpflegerID` int(11) NOT NULL auto_increment,
`Anrede` varchar(10) collate latin1_german1_ci NOT NULL,
`Name` varchar(30) collate latin1_german1_ci NOT NULL,
`Vorname` varchar(30) collate latin1_german1_ci NOT NULL,
`Strasse` varchar(30) collate latin1_german1_ci NOT NULL,
`PLZ` varchar(6) collate latin1_german1_ci NOT NULL,
`Ort` varchar(30) collate latin1_german1_ci NOT NULL,
`Telefon` varchar(30) collate latin1_german1_ci NOT NULL,
`email` varchar(30) collate latin1_german1_ci default NULL,
PRIMARY KEY (`TierpflegerID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
--- Daten für Tabelle `tierpfleger`
-INSERT INTO `tierpfleger` (`TierpflegerID`, `Anrede`, `Name`,
`Vorname`, `Strasse`, `PLZ`, `Ort`, `Telefon`, `email`) VALUES
(1, 'Herr', 'Meier', 'Otto', 'Otto-Strasse 10', '47259', 'Duisburg',
'0203-889911', NULL),
(2, 'Frau', 'Werter', 'Iris', 'Werter Platz 5', '47045', 'Duisburg',
'0203-778822', NULL),
(3, 'Frau', 'Heizmann', 'Eva', 'Heizstr. 6', '47200', 'Duisburg',
'0203-60889', NULL),
(4, 'Herr', 'Mieser', 'Gerd', 'Mieser Weg 1', '57300', 'Duisburg',
'0203-456789', NULL),
(5, 'Herr', 'Zeller', 'Karl', 'Oststr. 10', '45430', 'Bergheim', '02904711', NULL);
Lösungen 1. Ausgabe einer Telefonliste aller Mitarbeiter, die im Freigehege arbeiten, mit TierpflegerID, Anrede, Vorname, Name und Telefon geordnet nach Name SELECT tierpfleger.tierpflegerid, tierpfleger.anrede,
tierpfleger.vorname, tierpfleger.name, tierpfleger.telefon
FROM tierpfleger, gehege, gehegebetreuung
WHERE tierpfleger.tierpflegerid=gehegebetreuung.mitarbeiterid
AND gehege.gehegeid=gehegebetreuung.gehegeid
AND gehege.gehegename='Freigehege'
ORDER BY tierpfleger.name
2. Die Tabelle Tierpfleger soll um die Emailadresse (email) erweitert werden. ALTER TABLE tierpfleger ADD COLUMN ( email varchar(30) )
3. Bestimmen Sie alle Schimpansen mit TierID, Name, Geschlecht und Geburtsdatum, die im Affenhaus untergebracht sind. SELECT tier.tierid, tier.name, tier.geschlecht, tier.geburtsdatum
FROM tier, gehege, gattung
WHERE tier.gehegeid = gehege.gehegeid
AND gattung.gattungsid = tier.gattungsid
AND gehege.gehegename = 'Affenhaus'
AND gattung.gattungsname = 'Schimpanse'
4. Bestimmen Sie die Gehegenamen und die Anzahl der Tierpfleger, die in den einzelnen Gehegen arbeiten, geordnet nach Gehegename! SELECT gehege.gehegename, count( tierpfleger.tierpflegerid )
FROM gehege, tierpfleger, gehegebetreuung
WHERE tierpfleger.tierpflegerid = gehegebetreuung.mitarbeiterid
AND gehegebetreuung.gehegeid = gehege.gehegeid
GROUP BY (gehege.gehegeid)
ORDER BY gehege.gehegename
5. Richten Sie ein Elefantenhaus ein. INSERT INTO gehege
VALUES(NULL,'Elefantenhaus')
6. Welche Tiere sind im Affenhaus untergebracht (geordnet nach Gattung)? SELECT tier.tierid, tier.name, gattung.gattungsname
FROM tier, gattung, gehege
WHERE tier.gattungsid=gattung.gattungsid
AND tier.gehegeid=gehege.gehegeid
AND gehege.gehegename='Affenhaus'
ORDER BY gattung.gattungsname
7. Welche Tiere im Zoo bekommen um 9 Uhr Bananen (mit TierID, Name und Gattungsname)? SELECT tier.tierid, tier.name, gattung.gattungsname
FROM tier, gattung, futterration, futter
WHERE tier.gattungsid = gattung.gattungsid
AND tier.tierid = futterration.tierid
AND futter.futterid = futterration.futterid
AND futterration.fuetterungszeit = '09:00'
AND futter.futtername = 'Banane'
8. Die Fortpflanzungsversuche im Gehege Streichelzoo waren erfolgreich. Am 28.11.2007 wurde das Eselbaby Eisbein (weiblich) geboren. Aktualisieren Sie die Datenbank entsprechend. INSERT INTO tier
VALUES(NULL, 'Eisbein','w','2007-11-28',
(SELECT gattungsid FROM gattung WHERE gattungsname='Esel'),
(SELECT gehegeid FROM gehege WHERE gehegename='Streichelzoo')
)
9. Ermitteln Sie alle Gehege, für die noch keine Gehegebetreuung festgelegt wurde. SELECT gehege.gehegename
FROM gehege LEFT JOIN gehegebetreuung
ON gehege.gehegeid=gehegebetreuung.gehegeid
WHERE gehegebetreuung.betreuungsid IS NULL
10. Welche Tiere (mit Gattungsangabe) erhalten kein Futter? SELECT tier.tierid, tier.name, gattung.gattungsname
FROM tier LEFT JOIN futterration ON ( tier.tierid =
futterration.tierid ), gattung
WHERE gattung.gattungsid=tier.gattungsid
and futterration.futterrationsid IS NULL
11. Welche Tiergehege sind nicht belegt? SELECT gehege.gehegeid, gehege.gehegename
FROM gehege LEFT JOIN tier ON ( tier.gehegeid = gehege.gehegeid )
WHERE tier.gehegeid IS NULL
12. Welche Tiergehege sind mit mehr als 2 Tieren belegt? SELECT gehege.gehegeid, gehege.gehegename, count( gehege.gehegeid
) AS 'Anzahl Tiere'
FROM gehege, tier
WHERE tier.gehegeid = gehege.gehegeid
GROUP BY ( gehege.gehegeid )
HAVING ( count( gehege.gehegeid ) >2 )
Quellennachweis Dieses Übungsblatt wurde erstellt nach einer Vorlage von Prof. Dr. Stephan Karczewski von der Hochschule Darmstadt.