Musterklausur
Werbung
DB1 für Medizininformatiker Musterlösung zur Hauptklausur Aufgabe 1. Kreuzen Sie die richtigen Antworten an und geben Sie eine Begründung in Stichworten an. Ohne Begründung gilt Ihre Antwort als falsch. ( ) Richtig ( x ) Falsch 1. Bei Relationalen Datenbanken gilt das konzeptuelle Schema als externes Schema, weil es nicht im DBMS abgespeichert wird. Grund: Rs gehört zur Logischen, nicht zur externen Ebene der 3-Ebenen-Schema-Architektur (x ) ( ) Richtig Falsch 2. Ein DB-Benutzer muss das logische Schema der DB kennen, um korrekte SQL-Anweisungen schreiben zu können. Grund: Die SQL-Anweisungen beziehen Sich auf die Relationen des Logischen Schemas. ( ) (x ) Richtig Falsch 3. Beim Erzeugen der Tabellen einer relationalen DB ist die Reihenfolge beliebig, beim Einfügen von Datensätzen ist die Reihenfolge evtl. durch Constraints eingeschränkt. Grund: Eine Tabelle mit Fremdschlüsseln kann nur erzeugt werden, wenn die referierte Tabelle bereits existiert. ( ) (x ) Richtig Falsch 4. Im ERM erkennt man Existenzabhängigkeit an der Maximum-Angabe der Kardinalitäten. Grund: An einer 1 als Minimum. ( ) (x ) Richtig Falsch 5. Relationale Datenbanken müssen redundanzfrei sein. Grund: Durch Normalisierung wird versucht, sie redundanzarm zu machen. ( x ) Richtig 6. M:N-Beziehungstypen eines ERM sind im Relationalen Modell nicht abbildbar. ( x ) Falsch Grund: Nicht als Fremdschlüssel, wohl aber als eigene Relation. ( ) Richtig ( x ) Falsch 7. Update-Anomalie bedeutet, dass der Datenbestand einer Relationalen DB nach jeder UpdateAnweisung konsistent sein muss. Grund: Anomalie bedeutet Gefährdung der Konsistenz, in diesem Fall durch Update-Operationen ( ) Richtig ( x ) Falsch 8. Um ein DB-Schema vollständig zu implementieren, muss man es mit Testdatensätzen füllen. Grund: Eine DB-implementierung, die mit Testdaten gefüllt ist, nennt man Prototyp. ( x ) Richtig 9. Das Data Dictionary enthält das Datenbankschema. ( ) Falsch Grund: .. in derForm von Tabellenbeschreibungen ______________________________________________________________________________ ( ) Richtig 10. Folgende SQL-Anweisung ist unzulässig, wenn eine der Tabellen a und b eine View ist: (x ) SELECT a.*, x FROM a, b WHERE a.z =b.u AND b.v IS NOT NULL; Falsch Grund: Für Select-Anweisungen gibt es keine Einschränkungen für VIEWS. Aufgabe 2: Legen Sie nicht Ihre Lösung zur Übungsaufgabe 3, sondern das untenstehende rudimentätere Ersatz-ERM zugrunde, fügen Sie einige geeignete Attribute hinzu und ergänzen Sie die Kardinalitäten in (min,max)-Notation. Sie können die Änderungen direkt ins Diagramm eintragen. Nach einiger Zeit soll die Datenbasis um folgende Angaben erweitert werden: Um die Konserven-Rückverfolgung zu sichern, werden zukünftig bei jeder Blutkonserve folgende Zusatzinformationen gespeichert: Lagerort, Abrufdatum, abrufende Einrichtung, einrichtungsinterne Vorgangsnummer. Als besonderen Anreiz für Spender wird zugesagt, dass bei plötzlichem Bedarf soweit wie möglich auf eigene Konserven oder solche von nahen Verwandten zurückgegriffen wird. Dazu werden auf Wunsch Verwandschafts- I. Schmiedecke Sommersemester 06 Seite 1/5 Musterlösung zur Hauptklausur DB1 für Medizininformatiker beziehungen 1. Grades zu anderen Spendern in die Datenbank eingetragen (Eltern-Kind-Beziehungen). a) (35P) Ändern sie das ERM entsprechend. Vermeiden Sie dabei s oweit wie möglich Tabellenänderungen, damit möglichst die alten Datensätze und auch vorhandene Views gültig bleiben. b) (20 P.) Woran erkannt man, ob eine Konserve noch im Lager ist oder schon verwendet wurde, woran alte Datensätze, woran inkonsistente? (Schreiben Sie am besten SQL-artige Bedingungen) c) (Freiwillig, 5 Zusatzp.) Bleibt Ihr Modell für den Verwendungsnachweis geeignet, wenn als Verwendung von Konserven neben der Transfusion auch eine Präparatherstellung möglich ist, oder was müssten Sie ändern? Verwandschaft (0,1) (1,1) Spender_ID Vater Mutter Kons_Zusatz (0,1) (1,1) Auf Attribute und Kardinalitäten wurde verzichtet... b) Konserve noch am Lager: Kons_id Lagerort Abruf Einrichtg Vorg_Nr Lagerort NOT NULL alte Datensätze: konserve.Kons_id NOT IN (select kons_id from Kons_Zusatz) inkonsistent: sowohl am Lager als auch abgerufen und weitergegeben: Lagerort NOT NULL AND Vorg_Nr NOT NULL c)Ja, denn der Verwendungsnachweis läuft über die Kons_Zusatz.-Relationund die Beziehung zw. Konserve und Spender. Beide Beziehungen bleiben eindeutig, selbst wenn die Zahl der Konserven aus einer Spende größer als 1 ist. I. Schmiedecke Sommersemester 06 Seite 2/5 Musterlösung zur Hauptklausur DB1 für Medizininformatiker Aufgabe 3: a) (15P.) Formen sie das Ersatz-ERM aus Aufgabe 3 in ein RDM um (Tupel-Schreibweise), kennzeichnen Sie Primärund Fremdschlüssel. Sperrung (SPERR_NR (PK), Grund, Beginn, Ende,SPENDER_ID (FK)) Spender(SPENDER_ID (PK), Name, Geb_Dat, Blutgruppe) Spende(SPENDEN_NR (PK), Termin,SPENDER_ID(FK), PFLEGEPERSON (FK)) Laborwert(SPENDEN_NR (PK; FK), Wert, Text) Konserve(Kons_ID (PK), Typ,FREIGABE (FK), SPENDEN-NR(FK)) Pflegeperson (P_ID (PK) , Status) b) (15 P.) Schreiben Sie Ihre Änderungen in Tupel-Schreibweise und schreiben Sie die SQL-Anweisungen zum Ändern des Datenbank-Schemas. Verwandschaft (Sp_ID (PK, FK), VATER (FK), MUTTER (FK)) Kons_Zusatz (Kons_Id (PK, FK), Lagerort, Abruf, Einrichtung ,Vorgang) CREATE TABLE verwandschaft (SP_ID NUMBER PRIMARY KEY REFERENCES spender, vater NUMBER REFERENCES spender, mutter NUMBER REFERENCES spender); CREATE TABLE kons_zusatz (Kons_ID NUMBER PRIMARY KEY REFERENCES konserve, lagerort VARCHAR2, Abruf DATE, Einrichtung VARCHAR2(20), Vorgang NUMBER); c) (5 P.) Nennen Sie eine Existenzabhängigkeit im Modell und erläutern Sie deren Umsetzung im RDM. Eine Sperrung kann es nur zu einem Spender geben. Kardinaltät (1,1), im RDM erhält derFremdschlüssel in Sperrung die Constraint NOT NULL. d) (15 P.) Schreiben Sie eine Check-Bedingung, die inkonsistente Datensätze im Sinne von Aufgabe 3 ausschließt. Welcher Tabelle ordnen Sie die Check-Bedingung zu? (Benutzen Sie "xxx IS NOT NULL") ALTER TABLE konserve ADD CONSTRAINT check_konserve CHECK NOT( lagerort IS NOT NULL AND einrichtung IS NOT NULL); _______________________________________________________________________________________________ Aufgabe 4: (wahlweise 4 oder 5 bearbeiten!) a) (15 P.) Definieren Sie eine View, die für eine bestimmte (feste) abrufende Einrichtung die Spendendetails, d.h. alle Angaben im Spenden-Datensatz, für alle Vorgänge des aktuellen Tages auflistet (bentuzen Sie "DATE = SYSDATE"). Erfinden Sie den Namen bzw. die Bezeichnung der Einrichtung. CREATE VIEW einr_details AS SELECT spende.* from spende, konserve, kons_zusatz WHERE konserve.spenden_nr = kons-zusatz.spenden_nr AND konserve.spenden_nr = spende.spenden_nr AND kons_zusatz.einrichtung = ‚Klinkum Nord’ AND kons_zusatz.abbruf = SYSDATE; b) (10 P.) Schreiben Sie ein SQL-Anweisungen, die einen neuen Spender eintragen, der angibt, dass seine Mutter auch als Spender registriert ist. (Erfinden Sie passende Daten). I. Schmiedecke Sommersemester 06 Seite 3/5 Musterlösung zur Hauptklausur DB1 für Medizininformatiker INSERT INTO spenderVALUES( 1001,‘Peter Meier’, ’01-JAN-1980’,’AB+’); INSERT INTO verwandschaft VALUES(1001, null, 507); c) (10 P.)Schreiben Sie eine SQL-Anweisung, die Liste aller für den Spender aus a) verfügbaren eigenen Konserven in der Form Spender_ID, Konserven_ID, Lagerort erstellt . SELECT kons_id FROM spende, konserve WHERE konserve.spenden_nr = spende.spenden_nr AND spende.spender_id = 1001; d) (15 P) Ergänzen Sie c) so, dass die Konserven von Eltern und Kindern hinzukommen. SELECT kons_id FROM konserve, spende WHERE konserve.spenden_nr = spende.spenden_nr AND spende.spender_id IN (1001, (SELECT vater FROM verwandschaft WHERE sp_id=1001), (SELECT mutter FROM verwandschaft WHERE sp_id=1001), (SELECT sp_id FROM verwandschaft WHERE vater=1001), (SELECT sp_id FROM verwandschaft WHERE mutter =1001) ); e) - (freiwillig, 5 Zusatzpunkte) Ergänzen Sie d) weiter, so dass Geschwisterspenden auch erfasst werden. zur IN-Klausel hinzufügen: (SELECT sp_id FROM verwandschaft WHERE vater = (SELECT vater FROM verwandschaft WHERE sp_id=1001) AND mutter = (SELECT mutter FROM verwandschaft WHERE sp_id=1001) ) Lösung auf dem Extrablatt Nr. _________ ( 0 5 10 15 20 25 30 35 40 45) _______________________________________________________________________________________________ Aufgabe 5: (wahlweise 4 oder 5 bearbeiten!) a) (15 P.) Erklären Sie die Begriffe DBMS, Datenbank, Datenbankschema und Datenmodell und grenzen Sie sie gegeneinander ab. DBMS: Laufzeitumbegung für Datenbanken, ermöglicht die Implementierung von DB-Schemata und die Verwaltung der Datenbatände Datenbank: Datenbestand in einem DB-Schema DB-Schema: Informationsstruktur gemäß einem Datenmodell DB-Modell: Mathematisches Modell zur Darstellung von Daten und ihren Beziehungen. b) (15 P.) Skizzieren Sie den Prozess einer Datebankentwicklung anhand der 3-Ebenen-Schema-Architektur oder des Phasenmodells. Wenn eine Relationale Datenbank angestrebt wird, welche Datenmodelle und Transformationen komme nzum Einsatz? 3-Ebenen-Schema-Architektur: Externe Ebene - -externe Schemata, Logische Ebene – konzeptuelles Schema, Logisches Schema Interne Ebene – Internes Schema I. Schmiedecke Sommersemester 06 Seite 4/5 Musterlösung zur Hauptklausur DB1 für Medizininformatiker Entwickluung: 1. Informationsbedarf als Konzeptuelles Modell ( ERM) modellieren und auf Externe Views abbilden 2. In logisches Modell transformieren (RDM) 3.Im Data Dictionary implementieren (in SQL tranformieren und eingeben) 4. Prototyp bilden und testen (Testdaten eingeben) 5 In Betrieb nehmen. Phasenmodell: Anforderungsanalyse - Ermittlung des Informationsbedarfs Konzeptueller Entwurf – Erstellung des Konzeptuellen und der Externen Schemata Modell: ERM Logischer Entwurf – Transformation des konzeptuellen in ein Logisches Schema Transformation ERM RDM Implementierung - Installation des DB-Schemas im DBMS (Datendefinition) Transformation RDMSQL Physischer Entwurf – Umsetzung des DB-Schemas Transformation geschieht im DBMS, Einflussnahme durch DB-Admi n (Indexe etc) c) (15 P.) Woran erkennt man im ERM eine Existenzabhängigkeit, wie wird sie im RDM realisiert. Warum ergeben sich aus Existenzabhängigkeiten oft praktische Schwierigkeiten bei der Arbeit mit der Datenbank, und welche Möglichkeiten sieht SQL vor, dum diese Schwierigkeiten zu „umschiffen“? Existenzabhängigkeit erkennt man am 1-er-Minimum der Kardinalität. Im RDM wird daraus typischerweise eine NOT NULL-Bedingung für eine Fremdschlüssel. NOT_NULL-Bedingungen erschweren das Eintragen und Ändern von Datensätzen, insbesondere, wenn gegenseitige Abhängigkeiten zwischen den Entitätstypen bestehen. SQL sieht vor, dass man Bedingungen verzögert überprüfen kann (DEFERRED) oder zeitweise außer Kraft setzt (DISABLED). d) (5 P.) Wozu dient die Normalisierung eines DB-Schemas, und warum wird es manchmal auch denomaliesert? Normalisierung soll Redundanzen sukzessive eliminieren. Denormalisierung wird zur Perfromanceverbesserung eingesetzt, um häufige Joins zu vermeinden. e) (Freiwillig, 5 Zusatzpunkte) Warum stellen künstliche Primärschlüssel (Stichwort Auto-Increment) oft ein Normalisierungsproblem dar? Weil dadurch zumeist alle Nicht-Schlüsselattributenicht nicht nur vom Primärschlüssel funktional abhängig sind (widerspricht 3NF) I. Schmiedecke Sommersemester 06 Seite 5/5
