PMG2426 4/DB Beleg/Sophia
Aus ProgrammingWiki
Wiki-Quelltext dieser Seite.
<language>SQL</language>
=='''Datenbankprojekt: Datenbank einer Arztpraxis'''==
==Ziel der Datenbank==
Die Datenbank dient dazu, Informationen über Arztpraxen, Ärzte, Patienten und deren Diagnosen strukturiert zu verwalten. So können z.B. mehrere Ärzte und deren Patienten sowie die gestellten Diagnosen erfasst und verwaltet werden.
==Modellierung in einem ERM-Modell==
[[Bild:SoPau_ERM_Modell_2.0..jpg]]
==Transformation in das Relationenmodell==
[[Bild:SoPau_ERM_Modell_2.0..jpg]]
==Merkmale des Modells==
<font color=darkgreen><u>'''1) Entitäten:'''</u></font>
'''Arzt:'''
* Primärschlüssel: Arzt_ID
* Attribute: Vorname, Nachname, Geburtsjahr
'''Arztpraxis:'''
* Primärschlüssel: Arztpraxis_ID
* Attribute: Telefonnummer, Anschrift, Postleitzahl, Spezialisierung
'''Patienten:'''
* Primärschlüssel: Patient_ID
* Attribute: Vorname, Name, Geburtsjahr, Wohnort, Krankenkasse
'''Diagnose'''
* Primärschlüssel: Diagnose_ID
* Attribute: Informationen
<font color=darkgreen><u>'''2) Beziehungen und Kardinalitäten'''</u></font>
'''Arzt besitzt Arztpraxis: 1:1'''
* Ein Arzt ist genau einer Arztpraxis zugeordnet
* Eine Arztpraxis gehört genau zu einem Arzt
'''Arzt behandelt Patienten: n:m '''
* Ein Arzt kann viele Patienten behandeln
* Ein Patient kann von vielen Ärzten behandelt werden
* Konsequenz fürs Datenmodell: Zwischentabelle, ArztBehandeltPatient mit Arzt_ID und Patient_ID als Fremdschlüssel, mit zusammengesetzten Primärschlüssel aus beiden
'''Patient erhält Diagnose: 1:n '''
* Ein Patient kann mehrere Diagnosen haben
* Jede einzelne Diagnose gehört genau zu einem Patienten
==Erstellen der DB==
<code id="52dcdf16795a2">
CREATE TABLE Arztpraxis (
Arztpraxis_ID INT PRIMARY KEY,
Telefonnummer VARCHAR(15),
Anschrift VARCHAR(255),
Postleitzahl VARCHAR(10),
Spezialisierung VARCHAR(50)
);
CREATE TABLE Arzt (
Arzt_ID INT PRIMARY KEY,
Vorname VARCHAR(50),
Nachname VARCHAR(50),
Geburtsjahr INT,
Arztpraxis_ID INT,
FOREIGN KEY (Arztpraxis_ID) REFERENCES Arztpraxis(Arztpraxis_ID)
);
CREATE TABLE Patient (
Patient_ID INT PRIMARY KEY,
Vorname VARCHAR(50),
Nachname VARCHAR(50),
Geburtsjahr INT,
Wohnort VARCHAR(50),
Krankenkasse VARCHAR(50)
);
CREATE TABLE Diagnose (
Diagnose_ID INT PRIMARY KEY,
Informationen TEXT,
Patient_ID INT,
FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID)
);
CREATE TABLE ArztBehandeltPatient (
Arzt_ID INT,
Patient_ID INT,
PRIMARY KEY (Arzt_ID, Patient_ID),
FOREIGN KEY (Arzt_ID) REFERENCES Arzt(Arzt_ID),
FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID)
);
INSERT INTO Arztpraxis VALUES (1, '030-1234567', 'Musterstraße 1, Berlin', '10115', 'Allgemeinmedizin');
INSERT INTO Arztpraxis VALUES (2, '040-2345678', 'Lindenweg 5, Hamburg', '20095', 'Dermatologie');
INSERT INTO Arztpraxis VALUES (3, '089-3456789', 'Kirchplatz 3, München', '80331', 'Pädiatrie');
INSERT INTO Arztpraxis VALUES (4, '069-4567890', 'Wiesenweg 10, Frankfurt', '60311', 'Orthopädie');
INSERT INTO Arztpraxis VALUES (5, '0221-5678901', 'Bahnhofstraße 8, Köln', '50667', 'Neurologie');
INSERT INTO Arzt VALUES (1, 'Hans', 'Müller', 1978, 1);
INSERT INTO Arzt VALUES (2, 'Maria', 'Schmidt', 1973, 2);
INSERT INTO Arzt VALUES (3, 'Thomas', 'Meier', 1988, 3);
INSERT INTO Arzt VALUES (4, 'Sabine', 'Schneider', 1983, 4);
INSERT INTO Arzt VALUES (5, 'Michael', 'Weber', 1968, 5);
INSERT INTO Patient VALUES (1, 'Peter', 'Becker', 1993, 'Berlin', 'AOK');
INSERT INTO Patient VALUES (2, 'Anna', 'Schulz', 1998, 'Hamburg', 'TK');
INSERT INTO Patient VALUES (3, 'Julia', 'Fischer', 1983, 'München', 'Barmer');
INSERT INTO Patient VALUES (4, 'Markus','Weiß', 1968, 'Frankfurt', 'DAK');
INSERT INTO Patient VALUES (5, 'Laura', 'Wagner', 1963, 'Köln', 'Barmer');
INSERT INTO Diagnose VALUES (1, 'Grippe', 1);
INSERT INTO Diagnose VALUES (2, 'Erkältung', 1);
INSERT INTO Diagnose VALUES (3, 'Allergie', 2);
INSERT INTO Diagnose VALUES (4, 'Diabetes Typ 2', 3);
INSERT INTO Diagnose VALUES (5, 'Bluthochdruck', 4);
INSERT INTO ArztBehandeltPatient VALUES (1, 1);
INSERT INTO ArztBehandeltPatient VALUES (1, 2);
INSERT INTO ArztBehandeltPatient VALUES (2, 3);
INSERT INTO ArztBehandeltPatient VALUES (3, 4);
INSERT INTO ArztBehandeltPatient VALUES (4, 5);
</code>
----
==Erstellen von verschiedenen Abfragen==
Abfrage aller Arztpraxen mit Spezialisierung "Pädiatrie"
<run id="52f89f4ff1818">
SELECT * FROM Arztpraxis
WHERE Spezialisierung = 'Pädiatrie';
</run>
Abfrage aller Patienten, die in Berlin leben
<run id="92f89f4ff1818">
SELECT Vorname, Nachname
FROM Patient
WHERE Wohnort = 'Berlin';
</run>
Abfrage von Patienten und ihren behandelnden Ärzten
<run id="52f89f4ff1811">
SELECT P.Vorname AS PatientVorname, P.Nachname AS PatientNachname,
A.Vorname AS ArztVorname, A.Nachname AS ArztNachname
FROM Patient P
JOIN ArztBehandeltPatient ABP ON P.Patient_ID = ABP.Patient_ID
JOIN Arzt A ON ABP.Arzt_ID = A.Arzt_ID;
</run>
Abfrage der Diagnose von Peter Becker
<run id="52f89f4fg1818">
SELECT D.Informationen
FROM Diagnose D
JOIN Patient P ON D.Patient_ID = P.Patient_ID
WHERE P.Vorname = 'Peter' AND P.Nachname = 'Becker';
</run>
Abfrage der Diagnosen pro Patient
<run id="52f89f4fg1813">
SELECT p.Patient_ID, p.Vorname, p.Nachname, COUNT(d.Diagnose_ID) AS AnzahlDiagnosen
FROM Patient p
LEFT JOIN Diagnose d ON p.Patient_ID = d.Patient_ID
GROUP BY p.Patient_ID, p.Vorname, p.Nachname;
</run>
==Kritische Reflexion zu Modellierung und Umsetzung==
Die Datenbank hat eine klare Tabellenstruktur, in der jede Entität eigenständig modelliert ist. Die Trennung in Arztpraxis, Arzt, Patient und Diagnose verhindert Datenwiederholungen und unterstützt die Normalisierung.
Die Felder sind atomar, wie es die 1. Normalform verlangt, weil Informationen in passenden Spalten getrennt gespeichert werden. Jede Tabelle besitzt einen eindeutigen Primärschlüssel, wodurch keine Teilabhängigkeiten entstehen und die zweite Normalform erfüllt ist. Es sind keine offensichtlichen transitiven Abhängigkeiten erkennbar, wodurch die Struktur weitgehend zur dritten Normalform passt.
Verbesserungen:
Es fehlen teilweise sinnvolle Attribute, zum Beispiel zusätzliche Kontaktdaten für Ärzte und Patienten.
Für eine realistischere Anwendung wären weitere Tabellen sinnvoll, etwa für Termine, Medikamente oder Behandlungen.
Wenn Diagnosebezeichnungen häufig identisch vorkommen, könnte das auf Redundanz hindeuten und man könnte eine eigene Tabelle für Diagnosearten ergänzen.
==Verwendete Quellen, Tools, etc.==
* ERDplus