Rami Swailem FH Gießen-Friedberg Datenbanksysteme Übungsblatt3 Übungen Datenbanksysteme Teil 3 (Datenbank-Design) 1. Autowerkstatt ERD Entwurf des Entity-Relationship-Diagramms in UML-Notation Kunde 1 gehört * kundennr {pk} name vorname adresse strasse plz ort telefonnr KFZ 1 hat fahrgestllnr {pk} kennzeichen marke * Reparatur rechnungsnr {pk} datum km_stand 1 1..* Arbeiten Bezeichnung preis 2. Autowerkstatt Datenbankschema Transformation des vorigen Entity-Relationship-Diagramms in ein Datenbankschema Erstellen der SQL-Anweisungen zum Erzeugen der Datenbankstruktur Listing 1: Kunde DbSysI.Uebung3.tex,v,1.2,January 14, 2006 at 22:48:28 CET create t a b l e Kunde ( kundennr dec ( 6 ) primary key , name varchar ( 4 0 ) not n u l l , vorname varchar ( 4 0 ) , s t r a s s e varchar ( 4 0 ) , p l z char ( 1 0 ) , o r t varchar ( 4 0 ) , t e l e f o n : varchar ( 2 0 ) ) Listing 2: KFZ create t a b l e KFZ ( f a h r g e s t e l l n r char ( 1 5 ) primary key , kennzeichen varchar ( 1 2 ) , marke varchar ( 2 0 ) , kundennr dec ( 6 ) r e f e r e n c e s Kunde ( kundennr ) ) Wintersemester 2005/06 1 Rami Swailem FH Gießen-Friedberg Datenbanksysteme Übungsblatt3 Listing 3: Reparatur create t a b l e Reparatur ( rechnungsnr char ( 9 ) primary key , datum date , f a h r g e s t e l l n r char ( 1 5 ) r e f e r e n c e s KFZ ( f a h r g e s t e l l n r ) ) Listing 4: Arbeiten create t a b l e A r b e i t e n ( rechnungsnr char ( 9 ) r e f e r e n c e s Reparatur ( rechnungsnr ) , LfdNr dec ( 2 ) , Bezeichnung varchar ( 1 2 0 ) not n u l l , p r e i s money , primary key ( rechnungsnr , LfdNr ) ) 3. Autowerkstatt SQL Formulieren Sie SQL-Anweisungen für die folgenden Anfragen an die Datenbank der Autowerkstatt: 1. Bestimmen Sie die Anzahl der Werkstattbesuche eines bestimmten, vom Namen her bekannten Kunden. 2. Liste mit der Reparaturhäufigkeit der verschiedenen Automarken 3. Liste mit dem Umsatz je Kunde mit Kundennr und Name 4. Liste mit den mittleren Reparaturkosten je Automarke DbSysI.Uebung3.tex,v,1.2,January 14, 2006 at 22:48:28 CET 5. Liste der Kunden mit Angabe der Anzahl ihrer KFZ 4. Autowerkstatt Diskussion Die Fakten über die Autowerkstatt, die dem Entity-Relationship zugrundeliegen, sind stark vereinfacht. Finden Sie 3 Gegebenheiten, die in einer wirklichen Autowerkstatt anders sind und stellen Sie dar, wie diese Gegebenheiten einbauen könnten. 5. Belegsystem einer Hochschule ERD Entwurf des Entity-Relationship-Diagramms in UML-Notation Wintersemester 2005/06 2 Rami Swailem FH Gießen-Friedberg Dozent 1 macht Datenbanksysteme Übungsblatt3 * Name Vorname Veranstaltung 1..* besucht 1..* Wochentag Block Raum Student MatrikelNr {PK} Name Vorname * 6 beschreibt 1 Modul ModulNr Bezeichnung CrPoints Transformation des vorigen Entity-Relationship-Diagramms in ein Datenbankschema Dozent Veranstaltung DozNr = DozNr DozNr:int {PK} Name:varchar(40) Vorname:varchar(40) Teilnahme VeranstNr = VeranstNr VeranstNr:int{PK} Wochentag:char(2) Block:dec(1) Raum:char(4) DozNr:int {not null} ModulNr:char(6) {not null} VeranstNr:int{PK} MatrikelNr:dec(6) {PK} MatrikelNr = MatrikelNr ? Student 6 ModulNr = ModulNr Modul MatrikelNr:dec(6) {PK} Name:varchar(40) Vorname:varchar(40) DbSysI.Uebung3.tex,v,1.2,January 14, 2006 at 22:48:28 CET ModulNr:char(6) {PK} Bezeichnung:varchar(120) CrPoints:int Erstellen der SQL-Anweisungen zum Erzeugen der Datenbankstruktur Listing 5: Teilnahme Create t a b l e Teilnahme ( VeranstNr i n t r e f e r e n c e s V e r a n s t a l t u n g ( VeranstNr ) , M a t r i k e l N r dec ( 6 ) r e f e r e n c e s Student ( M a t r i k e l N r ) , primary key ( VeranstNr , M a t r i k e l N r ) Wintersemester 2005/06 3 Rami Swailem FH Gießen-Friedberg Datenbanksysteme Übungsblatt3 6. KFZ-Zulassung ERD Entwurf des Entity-Relationship-Diagramms in UML-Notation Halter 1 Name Vorname GebDatum Adresse * 1 KFZ * Kennzeichen {pk} Datum AbmeldeDatum FahgestellNr {pk} Baujahr 1 Strasse Ort PLZ Zulassung 1 ..... .. Typ KFZ kann erst einen Tag nach der Zulass. abgemeldet werden! Typcode Hesteller Modell Schadstoffkl 7. Bankniederlassung ERD Entwurf des Entity-Relationship-Diagramms in UML-Notation Niederl 1 zuständig * Zweigstelle für NiederlNr {pk} Bezeichnung Name Ort Strasse 1 ist bei * Kunde KundenNr {pk} Name Vorname 1 * hat - Konto KtoNr {pk} Datum Saldo GebDatum Adresse DbSysI.Uebung3.tex,v,1.2,January 14, 2006 at 22:48:28 CET 8. Bibliothek einer Hochschule ERD Entwurf des Entity-Relationship-Diagramms in UML-Notation Wintersemester 2005/06 4 Rami Swailem FH Gießen-Friedberg BuchTitel Datenbanksysteme Übungsblatt3 * Student * ISBN {pk} Autor Titel Verlag Ort Jahr MatrikelNr {pk} Name Vorname Adresse E-Mail Vormerkung Vormerkdatum 1 1 beschreibt * * BuchExemplar 1 Ausleihe * Signatur {pk} Kaufsdaturm Ausleihedatum Rückgabedatum Transformation des vorigen Entity-Relationship-Diagramms in ein Datenbankschema BuchTitel ISBN= ISBN ISBN:char(13) {pk} Autor:varchar(80) Titel:varchar(80) Verlag:varchar(40) Ort:varchar(40) Jahr:char(4) Vormerkung MatrikelNr= MatrikelNr Vormerkdatum ISBN:char(13) {pk} MatrikelNr:dec(6) {pk} Student MatrikelNr:dec(6) {pk} Name:varchar(40) Vorname:varchar(40) Adresse:varchar(120) E-Mail:vsrchar {unique} 6 MatrikelNr= MatrikelNr 6 ISBN= ISBN DbSysI.Uebung3.tex,v,1.2,January 14, 2006 at 22:48:28 CET BuchExemplar Signatur:char(10) {pk} Kaufsdaturm:date ISBN:char(13) Ausleihe Signatur= Signatur Ausleihedatum:date {pk} Rückgabedatum:date MatrikelNr:dec(6) {pk} Signatur:char(10) {pk} Erstellen der SQL-Anweisungen zum Erzeugen der Datenbankstruktur Listing 6: Tabelle Vormerkung Create t a b l e Vormerkung ( vormerkdatum date not n u l l , Wintersemester 2005/06 5 Rami Swailem FH Gießen-Friedberg Datenbanksysteme Übungsblatt3 ISBN char ( 1 3 ) r e f e r e n c e s B u c h T i t e l ( ISBN ) , M a t r i k e l N r dec ( 6 ) r e f e r e n c e s Student ( M a t r i k e l N r ) , primary key ( ISBN , M a t r i k e l N r ) ) 11. Juristisches Informationssystem ERD Gericht 1 arbeitet an Richter * Name Adresse strasse plz Ort Art Name Vorname ........ ... Titel TelNr *..1 leitet * Ein Richter kann nur Verfahren seines Gerichtes leiten Verfahren Aktenzeichen {pk} Sache Richter Begin DbSysI.Uebung3.tex,v,1.2,January 14, 2006 at 22:48:28 CET 13. Ausschnitt eines ERD für eine Klinikorganisation Wintersemester 2005/06 6 Rami Swailem FH Gießen-Friedberg Mitarbeiter Datenbanksysteme Übungsblatt3 arbeitet in * Abteilung 1 Name Vorname Name ..... ..... ..... ..... ..... 14. Artikel in einer betrieblichen Anwendung „Fehler“ des Diagramms: verletz (den Geist der) 1NF Artikel 1 * Verkauf ArtikelNr {pk} Menge Bezeichnung Jahr DbSysI.Uebung3.tex,v,1.2,January 14, 2006 at 22:48:28 CET Wintersemester 2005/06 7