Ασκηση 7 - Ενδεικτική επίλυση

DROP TABLE IF EXISTS "ΝΟΣΟΚΟΜΕΙΟ" CASCADE;

DROP TABLE IF EXISTS "ΑΣΘΕΝΗΣ" CASCADE;

DROP TABLE IF EXISTS "ΙΑΤΡΟΣ" CASCADE;

DROP TABLE IF EXISTS "ΕΞΕΤΑΣΗ" CASCADE;

DROP TABLE IF EXISTS "ΔΙΑΓΝΩΣΗ" CASCADE;

DROP TABLE IF EXISTS "ΕΠΙΣΚΕΨΗ_ΑΣΘΕΝΗ" CASCADE;

DROP TABLE IF EXISTS "ΕΞΕΤΑΣΗ_ΑΣΘΕΝΗ" CASCADE;

DROP TABLE IF EXISTS "ΔΙΑΓΝΩΣΗ_ΑΣΘΕΝΗ" CASCADE;

-- Δημιουργία Πίνακα { ΝΟΣΟΚΟΜΕΙΟ }:

"ΠΟΛΗ_ΝΟΣ" VARCHAR(30) CHECK ("ΠΟΛΗ_ΝΟΣ" = 'ΠΑΤΡΑ' OR "ΠΟΛΗ_ΝΟΣ" = 'ΑΘΗΝΑ' OR "ΠΟΛΗ_ΝΟΣ" = 'ΘΕΣΣΑΛΟΝΙΚΗ' OR "ΠΟΛΗ_ΝΟΣ" = 'ΡΙΟ'),

-- Δημιουργία του Πίνακα { ΑΣΘΕΝΗΣ }:

-- Δημιουργία Πίνακα { ΙΑΤΡΟΣ }:

FOREIGN KEY ("ΚΩΔ_ΝΟΣ_ΕΡΓ") REFERENCES "ΝΟΣΟΚΟΜΕΙΟ"("ΚΩΔΙΚΟΣ_ΝΟΣ"),

-- Δημιουργία του Πίνακα { ΕΞΕΤΑΣΗ }:

-- Δημιουργία του Πίνακα { ΔΙΑΓΝΩΣΗ }:

-- Δημιουργία του Πίνακα { ΕΠΙΣΚΕΨΗ_ΑΣΘΕΝΗ }:

CREATE TABLE "ΕΠΙΣΚΕΨΗ_ΑΣΘΕΝΗ"

FOREIGN KEY ("ΚΩΔΙΚΟΣ_ΑΣΘ") REFERENCES "ΑΣΘΕΝΗΣ"("ΚΩΔΙΚΟΣ_ΑΣΘ"),

FOREIGN KEY ("ΚΩΔΙΚΟΣ_ΝΟΣ") REFERENCES "ΝΟΣΟΚΟΜΕΙΟ"("ΚΩΔΙΚΟΣ_ΝΟΣ"),

-- Δημιουργία του Πίνακα { ΕΞΕΤΑΣΗ_ΑΣΘΕΝΗ }:

CREATE TABLE "ΕΞΕΤΑΣΗ_ΑΣΘΕΝΗ"

"ΗΜΕΡΟΜΗΝΙΑ_ΕΞΕ" DATE NOT NULL,

"ΑΠΟΤΕΛΕΣΜΑ_ΕΞΕ" VARCHAR(20),

FOREIGN KEY ("ΚΩΔΙΚΟΣ_ΕΠΙ") REFERENCES "ΕΠΙΣΚΕΨΗ_ΑΣΘΕΝΗ"("ΚΩΔΙΚΟΣ_ΕΠΙ"),

FOREIGN KEY ("ΚΩΔΙΚΟΣ_ΕΞΕ") REFERENCES "ΕΞΕΤΑΣΗ"("ΚΩΔΙΚΟΣ_ΕΞΕ"),

PRIMARY KEY("ΚΩΔΙΚΟΣ_ΕΠΙ", "ΚΩΔΙΚΟΣ_ΕΞΕ"));

-- Δημιουργία του Πίνακα { ΔΙΑΓΝΩΣΗ_ΑΣΘΕΝΗ }:

CREATE TABLE "ΔΙΑΓΝΩΣΗ_ΑΣΘΕΝΗ"

"ΗΜΕΡΟΜΗΝΙΑ_ΔΙΑ" DATE NOT NULL,

FOREIGN KEY ("ΚΩΔΙΚΟΣ_ΕΠΙ") REFERENCES "ΕΠΙΣΚΕΨΗ_ΑΣΘΕΝΗ"("ΚΩΔΙΚΟΣ_ΕΠΙ"),

FOREIGN KEY ("ΚΩΔΙΚΟΣ_ΔΙΑ") REFERENCES "ΔΙΑΓΝΩΣΗ"("ΚΩΔΙΚΟΣ_ΔΙΑ"),

FOREIGN KEY ("ΚΩΔΙΚΟΣ_ΙΑΤ") REFERENCES "ΙΑΤΡΟΣ"("ΚΩΔΙΚΟΣ_ΙΑΤ"),

PRIMARY KEY("ΚΩΔΙΚΟΣ_ΕΠΙ", "ΚΩΔΙΚΟΣ_ΔΙΑ", "ΚΩΔΙΚΟΣ_ΙΑΤ"));

-- Εισαγωγή Στοιχείων στον Πίνακα { ΝΟΣΟΚΟΜΕΙΟ }:

INSERT INTO "ΝΟΣΟΚΟΜΕΙΟ" VALUES (100, 'ΑΓΙΟΣ ΑΝΔΡΕΑΣ', 'ΠΑΤΡΑ', '2610456223', 'ΓΟΥΝΑΡΗ 54');

INSERT INTO "ΝΟΣΟΚΟΜΕΙΟ" VALUES (101, 'ΝΟΣΗΜΑΤΩΝ ΘΩΡΑΚΟΣ', 'ΠΑΤΡΑ', '2610223444', 'ΕΓΛΥΚΑΔΑΣ 33');

INSERT INTO "ΝΟΣΟΚΟΜΕΙΟ" VALUES (102, 'ΕΥΑΓΓΕΛΙΣΜΟΣ', 'ΑΘΗΝΑ', '2103342256', 'ΒΑΣΙΛΙΣΗΣ ΣΟΦΙΑΣ 123');

INSERT INTO "ΝΟΣΟΚΟΜΕΙΟ" VALUES (104, 'ΑΧΕΠΑ', 'ΘΕΣΣΑΛΟΝΙΚΗ', '2310224466', 'ΤΣΙΜΙΣΚΗ 22');

INSERT INTO "ΝΟΣΟΚΟΜΕΙΟ" VALUES (105, 'ΠΑΝΕΠΙΣΤΗΜΙΑΚΟ ΝΟΣΟΚΟΜΕΙΟ ΡΙΟΥ', 'ΡΙΟ', '2610999887', 'ΗΡΩΩΝ ΠΟΛΥΤΕΧΝΕΙΟΥ 2');

INSERT INTO "ΝΟΣΟΚΟΜΕΙΟ" VALUES (106, 'ΑΓΛΑΪΑ ΚΥΡΙΑΚΟΥ', 'ΑΘΗΝΑ', '2102345678', 'ΒΑΣΙΛΕΩΣ ΓΕΩΡΓΙΟΥ 23');

INSERT INTO "ΝΟΣΟΚΟΜΕΙΟ" VALUES (107, 'ΚΑΡΑΜΑΝΔΑΝΕΙΟ', 'ΠΑΤΡΑ', '2610666554', 'ΣΟΥΛΙΟΥ 34');

INSERT INTO "ΝΟΣΟΚΟΜΕΙΟ" VALUES (108, 'ΠΑΠΑΝΙΚΟΛΑΟΥ', 'ΘΕΣΣΑΛΟΝΙΚΗ', '2310234512', 'ΛΕΩΦΟΡΟΣ ΑΛΕΞΑΝΔΡΟΥ 2');

-- Εισαγωγή Στοιχείων στον Πίνακα { ΑΣΘΕΝΗΣ }:

INSERT INTO "ΑΣΘΕΝΗΣ" VALUES (30, 'ΧΡΙΣΤΟΠΟΥΛΟΣ', 'ΣΠΥΡΙΔΩΝ', 'ΠΑΤΡΑ, ΡΗΓΑ ΦΕΡΑΙΟΥ 15', '6944478933');

INSERT INTO "ΑΣΘΕΝΗΣ" VALUES (31, 'ΓΚΟΤΣΟΠΟΥΛΟΣ', 'ΒΑΣΙΛΗΣ', 'ΠΑΤΡΑ, ΑΓΥΑΣ 23', '6978845622');

INSERT INTO "ΑΣΘΕΝΗΣ" VALUES (37, 'ΣΑΚΟΠΟΥΛΟΣ', 'ΕΥΑΓΓΕΛΟΣ', 'ΠΑΤΡΑ, ΘΕΣΣΑΛΟΝΙΚΗΣ 34', '6973422555');

INSERT INTO "ΑΣΘΕΝΗΣ" VALUES (36, 'ΑΚΟΥΜΙΑΝΑΚΗΣ', 'ΔΗΜΟΣΘΕΝΗΣ', 'ΗΡΑΚΛΕΙΟ, ΚΡΗΤΗΣ', '6978122555');

INSERT INTO "ΑΣΘΕΝΗΣ" VALUES (38, 'ΧΥΖ', 'ΚΛΠ', 'ΗΡΑΚΛΕΙΟ, ΚΡΗΤΗΣ', '2381022555');

-- Εισαγωγή Στοιχείων στον Πίνακα { ΙΑΤΡΟΣ }:

INSERT INTO "ΙΑΤΡΟΣ" VALUES (1, 'ΓΕΩΡΓΟΠΟΥΛΟΣ', 'ΓΕΩΡΓΙΟΣ', 'ΠΑΤΡΕΩΣ 12', '6977856422', 100);

INSERT INTO "ΙΑΤΡΟΣ" VALUES (2, 'ΠΑΠΑΛΟΥΚΟΠΟΥΛΟΣ', 'ΙΩΑΝΝΗΣ', 'ΓΟΥΝΑΡΗ 34', '6783412655',101);

INSERT INTO "ΙΑΤΡΟΣ" VALUES (12, 'ΒΑΛΣΑΜΙΔΗΣ', 'ΣΤΑΥΡΟΣ', 'ΛΕΩΦΟΡΟΣ ΑΛΕΞΑΝΔΡΟΥ 256', '6973612988',108);

-- Εισαγωγή Στοιχείων στον Πίνακα { ΕΞΕΤΑΣΗ }:

INSERT INTO "ΕΞΕΤΑΣΗ" VALUES (1000, 'ΓΕΝΙΚΕΣ ΕΞΕΤΑΣΕΙΣ ΑΙΜΑΤΟΣ');

INSERT INTO "ΕΞΕΤΑΣΗ" VALUES (1001, 'ΑΡΘΡΟΣΚΟΠΗΣΗ');

INSERT INTO "ΕΞΕΤΑΣΗ" VALUES (1010, 'ΕΓΚΕΦΑΛΟΓΡΑΦΗΜΑ');

-- Εισαγωγή Στοιχείων στον Πίνακα { ΔΙΑΓΝΩΣΗ }:

INSERT INTO "ΔΙΑΓΝΩΣΗ" VALUES (1000, 'ΧΟΛΥΣΤΕΡΙΝΗ-ΤΡΙΓΛΥΚΕΡΙΔΙΑ');

INSERT INTO "ΔΙΑΓΝΩΣΗ" VALUES (1001, 'ΑΡΘΡΙΤΙΔΑ');

INSERT INTO "ΔΙΑΓΝΩΣΗ" VALUES (1010, 'ΜΗ ΒΡΑΧΥΚΥΚΛΩΜΕΝΟΙ ΝΕΥΡΩΝΕΣ');

-- Εισαγωγή Στοιχείων στον Πίνακα { ΕΠΙΣΚΕΨΗ_ΑΣΘΕΝΗ }:

INSERT INTO "ΕΠΙΣΚΕΨΗ_ΑΣΘΕΝΗ" VALUES (301, 30, 100, TO_DATE('2/2/2009', 'dd/mm/yyyy'));

INSERT INTO "ΕΠΙΣΚΕΨΗ_ΑΣΘΕΝΗ" VALUES (311, 31, 101, TO_DATE('2/3/2009', 'dd/mm/yyyy'));

INSERT INTO "ΕΠΙΣΚΕΨΗ_ΑΣΘΕΝΗ" VALUES (371, 37, 106, TO_DATE('6/5/2008', 'dd/mm/yyyy'));

INSERT INTO "ΕΠΙΣΚΕΨΗ_ΑΣΘΕΝΗ" VALUES (381, 36, 100, TO_DATE('12/11/2024', 'dd/mm/yyyy'));

-- Εισαγωγή Στοιχείων στον Πίνακα { ΕΞΕΤΑΣΗ_ΑΣΘΕΝΗ }:

INSERT INTO "ΕΞΕΤΑΣΗ_ΑΣΘΕΝΗ" VALUES (301, 1000, TO_DATE('5/2/2009', 'dd/mm/yyyy'), 'ΚΑΛΟ');

INSERT INTO "ΕΞΕΤΑΣΗ_ΑΣΘΕΝΗ" VALUES (311, 1001, TO_DATE('5/3/2009', 'dd/mm/yyyy'), 'ΜΕΤΡΙΟ');

INSERT INTO "ΕΞΕΤΑΣΗ_ΑΣΘΕΝΗ" VALUES (381, 1010, TO_DATE('12/11/2024', 'dd/mm/yyyy'), 'ΑΝΕΠΑΡΚΗ ΕΥΡΗΜΑΤΑ');

INSERT INTO "ΕΞΕΤΑΣΗ_ΑΣΘΕΝΗ" VALUES (381, 1000, TO_DATE('12/11/2024', 'dd/mm/yyyy'), 'ΑΣΑΦΗ ΕΥΡΗΜΑΤΑ');

-- Εισαγωγή Στοιχείων στον Πίνακα { ΔΙΑΓΝΩΣΗ_ΑΣΘΕΝΗ }:

INSERT INTO "ΔΙΑΓΝΩΣΗ_ΑΣΘΕΝΗ" VALUES (301, 1000, TO_DATE('6/2/2009', 'dd/mm/yyyy'), 'ΕΛΑΦΡΑ ΔΙΑΙΤΑ', 1);

INSERT INTO "ΔΙΑΓΝΩΣΗ_ΑΣΘΕΝΗ" VALUES (311, 1001, TO_DATE('6/3/2009', 'dd/mm/yyyy'), 'ΌΧΙ ΑΛΑΤΙ ΣΤΑ ΦΑΓΗΤΑ', 2);

INSERT INTO "ΔΙΑΓΝΩΣΗ_ΑΣΘΕΝΗ" VALUES (371, 1010, TO_DATE('9/5/2008', 'dd/mm/yyyy'), 'Γυμναστική - Φυσιοθεραπείες', 1);

INSERT INTO "ΔΙΑΓΝΩΣΗ_ΑΣΘΕΝΗ" VALUES (381, 1010, TO_DATE('12/11/2024', 'dd/mm/yyyy'), 'ΝΑ ΑΠΟΦΕΥΓΟΝΤΑΙ ΤΟ ΑΓΧΟΣ ΚΑΙ Η ΣΩΜΑΤΙΚΗ ΚΟΠΩΣΗ', 1);

INSERT INTO "ΔΙΑΓΝΩΣΗ_ΑΣΘΕΝΗ" VALUES (381, 1010, TO_DATE('12/11/2024', 'dd/mm/yyyy'), 'ΑΠΑΙΤΕΙΤΑΙ ΑΣΚΗΣΗ ΗΜΕΡΗΣΙΩΣ', 2);

1. (Περιορισμός πεδίου τιμών γνωρίσματος): Τροποποιήστε το script όπου χρειάζεται έτσι

ώστε η πόλη που βρίσκεται κάποιο νοσοκομείο της βάσης να είναι είτε 'ΠΑΤΡΑ' είτε

'ΑΘΗΝΑ' είτε 'ΘΕΣΣΑΛΟΝΙΚΗ' είτε 'RIO'

-- "ΠΟΛΗ_ΝΟΣ" VARCHAR(30) CHECK ("ΠΟΛΗ_ΝΟΣ" = 'ΠΑΤΡΑ' OR "ΠΟΛΗ_ΝΟΣ" = 'ΑΘΗΝΑ' OR "ΠΟΛΗ_ΝΟΣ" = 'ΘΕΣΣΑΛΟΝΙΚΗ' OR "ΠΟΛΗ_ΝΟΣ" = 'ΡΙΟ')

2. (Περιορισμός μοναδικότητας τιμής γνωρίσματος που δεν είναι κλειδί): Τροποποιήστε το script όπου χρειάζεται

έτσι ώστε το όνομα ενός νοσοκομείου να είναι μοναδικό στη βάση δεδομένων όπως επίσης να είναι μοναδικά στη βάση

δεδομένων το τηλέφωνο του ασθενή και το τηλέφωνο του ιατρού

-- Προσθήκη της δήλωσης UNIQUE ("ΟΝΟΜΑ_ΝΟΣ") στον πίνακα ΝΟΣΟΚΟΜΕΙΟ

-- Προσθήκη της δήλωσης UNIQUE ("ΤΗΛΕΦΩΝΟ_ΑΣΘ") στον πίνακα ΑΣΘΕΝΗΣ

-- "ΤΗΛΕΦΩΝΟ_ΑΣΘ" VARCHAR(10) UNIQUE

-- Προσθήκη της δήλωσης UNIQUE ("ΤΗΛΕΦΩΝΟ_ΙΑΤ") στον πίνακα ΑΣΘΕΝΗΣ

3. (Ενημέρωση πλειάδας υπό συνθήκη): Για τον ΑΣΘΕΝΗ με κωδικο 36 (που εοσάγατε σε προηγούμενο βήμα, βλ. Ασκηση 5)

απαιτείται να ενημερώστε το script ετσι ώστε το τηλέφωνο του ασθενή να είναι 2810379716

SET "ΤΗΛΕΦΩΝΟ_ΑΣΘ" = '2810379716' WHERE "ΚΩΔΙΚΟΣ_ΑΣΘ" = 36;

SELECT * FROM "ΑΣΘΕΝΗΣ" WHERE "ΚΩΔΙΚΟΣ_ΑΣΘ" = 36;

4. (Διαγραφή πλειάδων υπό συνθήκη): Στον πίνακα ΑΣΘΕΝΗΣ διαγράψετε τις πλειάδες που έχουν ως τηλέφωνο απροσδιόριστη

τιμή και τις πλειάδες (των ασθενών) που δεν έχουν καταχωρημένη κάποια επίσκεψη μέχρι στιγμής.

DELETE FROM "ΑΣΘΕΝΗΣ" WHERE "ΤΗΛΕΦΩΝΟ_ΑΣΘ" IS NULL;

SELECT DISTINCT "ΕΠΙΣΚΕΨΗ_ΑΣΘΕΝΗ"."ΚΩΔΙΚΟΣ_ΑΣΘ"

5. (Τροποποίηση τιμής πρωτεύοντος κλειδιού): Αλλάξτε την τιμή του κωδικού του ΑΣΘΕΝΗ που εισάγατε σε προηγούμενο βήμα,

(βλ. Ασκηση 5) σε μια νέα τιμή της ασρεσκείας σας (π.χ. απο 38 σε 40).

SET "ΚΩΔΙΚΟΣ_ΑΣΘ" = 40 WHERE "ΚΩΔΙΚΟΣ_ΑΣΘ" = 36;

DROP CONSTRAINT "ΑΣΘΕΝΗΣ_ΤΗΛΕΦΩΝΟ_ΑΣΘ_key";

-- Create a new record with the desired primary key value:

INSERT INTO "ΑΣΘΕΝΗΣ" VALUES (40, 'ΑΚΟΥΜΙΑΝΑΚΗΣ', 'ΔΗΜΟΣΘΕΝΗΣ', 'ΗΡΑΚΛΕΙΟ, ΚΡΗΤΗΣ', '6978122555');

-- Updating other tables to ensure referential contraints:

ADD CONSTRAINT "ΤΗΛΕΦΩΝΟ_ΑΣΘ" UNIQUE ("ΤΗΛΕΦΩΝΟ_ΑΣΘ");

-- Ενναλακτική σωστή ακολουθία με BEGIN-COMMIT και εναλλαγή του session_replication_role

UPDATE "ΑΣΘΕΝΗΣ" SET "ΚΩΔΙΚΟΣ_ΑΣΘ" = 40 WHERE "ΚΩΔΙΚΟΣ_ΑΣΘ" = 36;

UPDATE "ΕΠΙΣΚΕΨΗ_ΑΣΘΕΝΗ" SET "ΚΩΔΙΚΟΣ_ΑΣΘ" = 40 WHERE "ΚΩΔΙΚΟΣ_ΑΣΘ" = 36;