Aufg02 - oth
Werbung
2. Aufgabenblatt Die folgenden Tabellen beschreiben eine relationalen Datenbank: Teil ---------------------------------------------------------------------------------------------| TeilNr | TBez | TMBest | TBest | TFarbe | Tgewicht | ---------------------------------------------------------------------------------------------| T01 | Teil_1 | 20 | 22 | rot | 12 | ---------------------------------------------------------------------------------------------| T02 | Teil_2 | 20 | 27 | gruen | 17 | ---------------------------------------------------------------------------------------------| T03 | Teil_3 | 20 | 25 | blau | 17 | ---------------------------------------------------------------------------------------------| T04 | Teil_4 | 20 | 34 | rot | 17 | ---------------------------------------------------------------------------------------------| T05 | Teil_5 | 40 | 45 | blau | 14 | ---------------------------------------------------------------------------------------------| T06 | Teil_6 | 40 | 42 | rot | 12 | ---------------------------------------------------------------------------------------------| T07 | Teil_7 | 40 | 46 | gruen | 19 | ---------------------------------------------------------------------------------------------| T08 | Teil_8 | 40 | 43 | blau | 7 | ---------------------------------------------------------------------------------------------| T09 | Teil_9 | 40 | 47 | rot | 8 | ---------------------------------------------------------------------------------------------| T10 | Teil_10 | 96 | 100 | rot | 2 | ---------------------------------------------------------------------------------------------| T11 | Teil_11 | 40 | 50 | gelb | 3 | ---------------------------------------------------------------------------------------------| T12 | Teil_12 | 20 | 23 | gelb | 5 | ---------------------------------------------------------------------------------------------- Teilestruktur ------------------------------------------------------------| OTeil | UTeil | Menge | ------------------------------------------------------------| T01 | T03 | 1 | ------------------------------------------------------------| T01 | T05 | 1 | ------------------------------------------------------------| T01 | T06 | 1 | ------------------------------------------------------------| T02 | T04 | 1 | ------------------------------------------------------------| T02 | T05 | 1 | ------------------------------------------------------------| T02 | T06 | 1 | ------------------------------------------------------------| T05 | T07 | 1 | ------------------------------------------------------------| T05 | T09 | 1 | ------------------------------------------------------------| T05 | T10 | 120 | ------------------------------------------------------------| T06 | T08 | 1 | ------------------------------------------------------------| T06 | T09 | 1 | ------------------------------------------------------------| T06 | T10 | 120 | ------------------------------------------------------------| T07 | T11 | 1 | ------------------------------------------------------------| T08 | T11 | 1 | ------------------------------------------------------------| T08 | T12 | 1 | ------------------------------------------------------------- Lieferant Datenbanken --------------------------------------------------------------------------------| LiefNr | LiefName | LiefPLZ | LiefOrt | --------------------------------------------------------------------------------| L01 | Lieferant_1 | 93047 | Regensburg | --------------------------------------------------------------------------------| L02 | Lieferant_2 | 92224 | Amberg | --------------------------------------------------------------------------------| L03 | Lieferant_3 | 92224 | Amberg | --------------------------------------------------------------------------------| L04 | Lieferant_4 | 80997 | M•nchen | --------------------------------------------------------------------------------| L05 | Lieferant_5 | 84028 | Landshut | --------------------------------------------------------------------------------- Projekt ----------------------------------------| ProjNr | ProjBez | ----------------------------------------| P01 | Projekt_1 | ----------------------------------------| P02 | Projekt_2 | ----------------------------------------| P03 | Projekt_3 | ----------------------------------------| P04 | Projekt_4 | ----------------------------------------| P05 | Projekt_5 | ----------------------------------------| P06 | Projekt_6 | ----------------------------------------| P07 | Projekt_7 | ----------------------------------------- Beziehungsmenge: Lieferant, Teil, Projekt --------------------------------------------------------------------------------| LiefNr | TeilNr | ProjNr | Menge | --------------------------------------------------------------------------------| L01 | T01 | P01 | 20 | --------------------------------------------------------------------------------| L01 | T01 | P04 | 70 | --------------------------------------------------------------------------------| L02 | T02 | P01 | 40 | --------------------------------------------------------------------------------| L02 | T02 | P02 | 20 | --------------------------------------------------------------------------------| L02 | T03 | P04 | 50 | --------------------------------------------------------------------------------| L02 | T03 | P06 | 40 | --------------------------------------------------------------------------------| L02 | T03 | P07 | 80 | --------------------------------------------------------------------------------| L02 | T05 | P02 | 10 | --------------------------------------------------------------------------------| L03 | T03 | P01 | 20 | --------------------------------------------------------------------------------| L03 | T04 | P02 | 50 | --------------------------------------------------------------------------------| L04 | T06 | P03 | 30 | --------------------------------------------------------------------------------| L04 | T06 | P07 | 30 | --------------------------------------------------------------------------------| L05 | T01 | P04 | 10 | --------------------------------------------------------------------------------| L05 | T02 | P02 | 20 | --------------------------------------------------------------------------------| L05 | T02 | P04 | 10 | --------------------------------------------------------------------------------| L05 | T03 | P04 | 20 | --------------------------------------------------------------------------------| L05 | T04 | P04 | 40 | --------------------------------------------------------------------------------| L05 | T05 | P02 | 20 | --------------------------------------------------------------------------------| L05 | T05 | P04 | 40 | --------------------------------------------------------------------------------| L05 | T05 | P07 | 10 | --------------------------------------------------------------------------------| L05 | T06 | P04 | 50 | --------------------------------------------------------------------------------- 2 Datenbanken 1 Gib die SQL-Anweisungen1 an, die das Schema für eine relationale Oracle Datenbank erzeugen, die diese Tabellen umfassen Das Schema dieser Datenbank soll alle Sicherheitsvorkehrungen für relationale Integrität mit CONSTRAINTs bestimmen. 1 Hinweise zu den Aufgabenstellungen bzgl der SQL-Angaben: Entry-SQL (entspricht dem ANSI-89-Standard, auch SQL/89 genannt) und kennzeichnet die untere Ebene des SQL/92 (auch SQL2 genannt). Full-Level-SQL umfaßt die vollständige Realisierung von SQL/92 Ohne spezielle Hinweise sind SQL-Anweisungen in Oracle-SQL (SQL*Plus) anzugeben. Bezugspunkt ist Oracle Version 7. 3 Datenbanken 2. Gib alle zum Füllen der Datenbank-Tabellen mit den Daten dergegebenen Tabellen nötigen INSERT-Befehle an. Die Angaben der SQL-Anweisungen sollen in einer Textdatei erfolgen (Extension: .SQL), die in SQL*PLUS gestartet werden kann und die gewünschte Datenbank erzeugt. 4 Datenbanken 3. Bilde das soeben angegebene bzw. durch die Aufgabenstellung vorgegebene Schema der relationalen Datenbank in ein Entity-Relationship-Diagramm2 ab. Die Darstellung soll alle in der Aufgabenstellung angegebenen Tabellen- bzw. Feldnamen wiedergeben, Schlüssel von Nichtschlüsselattributen unterscheiden und den Zusammenhang zwischen Entitäts- und Beziehungsmengen deutlich herausstellen. 4. Bestimme zu den folgenden Angaben (Anfragen an die relationale Datenbank) die SELECT-Anweisungen! a) Gib zu der folgenden SELECT-Anweisung eine Alternative SELECT-Anweisung an. Die Alternative soll zum gleichen Ergebnis führen wie die gegebene SELECT-Anweisung und zusätzlich noch „teilenr“ und „liefnr“ der Tabelle „ltp“ ausgeben. SELECT SUM(MENGE) FROM LTP WHERE TeilNr = ‘T01’ AND LiefNr = ‘L01’; Alternative: _____________________________________________ _____________________________________________ _____________________________________________ _____________________________________________ _____________________________________________ _____________________________________________ 2 Hinweise zur Darstellung der Entity-Relationship- bzw. Datenbankstruktur-Diagramme: Entity-Relationship-Diagramme, Datenbankstruktur-Diagramme sind nach den im Skriptum und in der Vorlesung vereinbarten Gestaltungsrichtlinien anzugeben 5 Datenbanken b) Welchem Lieferanten („liefnr, liefname“) sind keine Teile zugeordnet? ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ c) Bestimme die Projektnummern („projnr“) der Projekte, die nicht mit irgendwelchen roten Teilen von irgendwelchen Lieferanten beliefert werden, die in „Regensburg“ ansässig sind. ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ d) Bestimme die Lieferanten („liefnr, name“), die mindestens ein Teil liefern, das auch der Lieferant liefert, der mindestens ein rotes Teil liefert. ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ ______________________________________________ e) Gib unterschiedliche Teilenummern paarweise so an, daß einige Lieferanten die paarweise zusammengestellten Teile liefern. Die Ausgabe soll in eine Tabelle mit folgenden Spaltenüberschriften erfolgen: LTP1.TeileNr LTP2.TeileNr LTP1.LiefNr 1) Formuliere die zugehörige SELECT-Anweisung in Oracle-SQL. 6 Anzahl Datenbanken ____________________________________________ ____________________________________________ ____________________________________________ ____________________________________________ 2) Formuliere die zugehörige Anweisung in SQL-92 (SQL2) ____________________________________________ ____________________________________________ ____________________________________________ ____________________________________________ ____________________________________________ ____________________________________________ ____________________________________________ ____________________________________________ ____________________________________________ f) Gib „LiefNr“, „LiefName“, „TeileNr“ und die Menge an gelieferten Teilen der Lieferanten an. 1) Gib die zugehörige SQL-Anweisung in Oracle-SQL an! ____________________________________________ ____________________________________________ ____________________________________________ ____________________________________________ ____________________________________________ 2) Zu der vorliegenden SELECT-Anweisung gib die korrespondierende Anweisung in SQL-92 (Full Level) an. ____________________________________________ ____________________________________________ ____________________________________________ ____________________________________________ ____________________________________________ ____________________________________________ ____________________________________________ ____________________________________________ g) Gib die „teilenr“ mit den zugehörigen „tbest“-Größen aus, deren „tbest“-Größe kleiner ist als die durchschnittliche „tbest“-Größe. ____________________________________________ ____________________________________________ ____________________________________________ ____________________________________________ 7 Datenbanken h) Gib die (Stücklisten)-Tiefe der Erzeugnisstruktur an. ___________________________________________ ___________________________________________ ___________________________________________ i) Von welchen Teilen ist der Mindestbestand kleiner als 20? ___________________________________________ ___________________________________________ ___________________________________________ ___________________________________________ ___________________________________________ k) Welcher Lieferant liefert keine Teile bzw. beliefert keine Projekte? Gib 2 alternative Lösungen an. Eine Alternative soll den Outer-Join im Rahmen von SQL/92 (Full Level) verwenden. 1. Lösung ___________________________________________ ___________________________________________ ___________________________________________ ___________________________________________ ___________________________________________ 2. Lösung (mit Outer Join) ___________________________________________ ___________________________________________ ___________________________________________ ___________________________________________ k) Was bewirken die folgenden SELECT-Anweisungen? 1) SELECT DISTINCT LiefNr FROM LTP LTPX WHERE EXISTS (SELECT TeileNr FROM LTP LTPY WHERE NOT EXISTS (SELECT ProjNr FROM Projekt WHERE NOT EXISTS (SELECT * FROM LTP LTPZ WHERE LTPZ.LiefNr = LTPX.LiefNr AND LTPZ.TeileNr = LTPY.TeileNr AND LTPZ.ProjNr = Projekt.ProjNr))); 8 Datenbanken __________________________________________ __________________________________________ __________________________________________ 2) SELECT DISTINCT ProjNr FROM LTP LTPX WHERE NOT EXISTS (SELECT * FROM LTP LTPY WHERE EXISTS (SELECT * FROM LTP LTPA WHERE LTPA.LiefNr = LTPY.LiefNr AND LTPA.TeileNr IN (SELECT TeileNr FROM Teil WHERE Tfarbe = ‘rot’) AND NOT EXISTS (SELECT * FROM LTP LTPB WHERE LTPB.LiefNr = LTPY.LiefNr AND LTPB.ProjNr = LTPX.ProjNr))); ____________________________________________ ____________________________________________ ____________________________________________ 5. Gib zur gegebenen relationalen Datenbank das Entity-Relationship-Diagramm an. Die Diagrammdarstellung soll den gleichen Detailierungsgrad besitzen, den das Schema der relationalen Datenbank aufweist. Zusätzlich sind in die Diagrammdarstellung folgende Sachverhalte aufzunehmen: - Projekte werden durch Projektarbeiten von Mitarbeitern bearbeitet. Zwischen den Objekttypen Mitarbeiter und Projekt besteht eine M:M-Beziehung. - Mehrere Mitarbeiter sind in einer Abteilung zusammengefaßt und einem Mitarbeiter, der ihr Vorgesetzter ist, unterstellt. - Mitarbeiter sind durch die Attribute „mitarbnr“, „mitarbname“ und „gehalt“ beschrieben, eine Abteilung durch „abtnr“ und „abtbez(eichnung)“. - Ein Mitarbeiter kann mehrere Projekte als Projektleiter betreuen. Ein Projekt wird von einem Projektleiter geführt. Das jetzt gewünschte Entity-Relationship-Diagramm ergänzt das unter 3. angegebene Diagramm um die vorliegenden Sachverhalte. 9 Datenbanken 6. Das unter 5. entwickelte Entity-Relationship-Diagramm soll in das Datenbankstruktur-Diagramm einer CODASYL-Datenbank überführt werden. Dieses Diagramm soll alle Satztypen bzw. Sets umfasssen, Attribute (Beschreibung von Feldern innerhalb der Satztypen) können weggelassen werden. 10 Datenbanken 7. Die neuen Tabellen sollen in die vorliegende relationale Datenbank aufgenommen werden. Gib die OracleSQL-Anweisungen zur Erzeugung der neuen Tabellen an. Die Datenbank soll über Constraint-Klauseln Entitätsintegritäten und referentielle Integritäten sicherstellen. Die Constraint-Klauseln sollen eindeutig bezeichnet sein. Für alle Schlüssel sollen Indextabellen festgelegt sein, außerdem sollen Schlüssel nicht den Wert NULL erhalten können. _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ _______________________________________________ 11
