--Section 5.
1:
CREATE TABLE patients (
patient_id SERIAL PRIMARY KEY,
nom VARCHAR(100),
prenom VARCHAR(100),
sexe VARCHAR(10),
date_naissance DATE,
adresse TEXT,
numero_telephone VARCHAR(20),
numero_assurance VARCHAR(50)
);
CREATE TABLE services (
service_id SERIAL PRIMARY KEY,
nom_service VARCHAR(100),
description TEXT
);
CREATE TABLE medecins (
medecin_id SERIAL PRIMARY KEY,
nom VARCHAR(100),
prenom VARCHAR(100),
specialite VARCHAR(100),
heure_disponibilite TIME,
service_id INT,
FOREIGN KEY (service_id) REFERENCES services(service_id)
);
CREATE TABLE consultations (
consultation_id SERIAL PRIMARY KEY,
date_consultation DATE,
heure_arrivee TIME,
heure_depart TIME,
medecin_id INT,
patient_id INT,
statut VARCHAR(20),
FOREIGN KEY (medecin_id) REFERENCES medecins(medecin_id),
FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
);
CREATE TABLE stock (
medicament_id SERIAL PRIMARY KEY,
quantite INT,
quantite_minimale INT
);
CREATE TABLE medicaments (
medicament_id SERIAL PRIMARY KEY,
nom VARCHAR(100),
dosage VARCHAR(50),
composition TEXT,
indications TEXT,
quantite_minimale INT
);
CREATE TABLE factures (
facture_id SERIAL PRIMARY KEY,
numero_facture VARCHAR(50),
nom_patient VARCHAR(100),
date_facture DATE,
heure_facture TIME,
montant DECIMAL,
total_facture DECIMAL,
patient_id INT,
FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
);
CREATE TABLE rendez_vous (
rendez_vous_id SERIAL PRIMARY KEY,
date_rendez_vous DATE,
heure_rendez_vous TIME,
statut VARCHAR(20),
patient_id INT,
FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
);
CREATE TABLE salles (
salle_id SERIAL PRIMARY KEY,
numero_identifiant VARCHAR(20),
capacite INT,
service_id INT,
FOREIGN KEY (service_id) REFERENCES services(service_id)
);
CREATE TABLE prescriptions (
prescription_id SERIAL PRIMARY KEY,
date_prescription DATE,
posologie VARCHAR(100),
duree_traitement INT,
consultation_id INT,
medicament_id INT,
FOREIGN KEY (consultation_id) REFERENCES consultations(consultation_id),
FOREIGN KEY (medicament_id) REFERENCES medicaments(medicament_id)
);
--le patient dont le numero de telephone=999-999-9999
select nom_patient from patients where (
numero_telephone=(999)-999-99999
)
--le prenom d'un medecin dont il est specialise en infirmiere
select prenom from medecins where(
specialite=infirmiére
)
--la duree du traitement ainsi que la posologie d'une prescrption d'un medicament
dont la duree est superieure à 31 jours
select duree_traitement, posologie from prescriptions where(
duree_traitement>31jours
)
--Section 5.2: Trigger permettant de gérer plus d’une table :
--1- Mise à jour du stock des médicaments après chaque prescription, mais
seulement si la quantité en stock est suffisante pour la durée de traitement
prescrite.
CREATE OR REPLACE FUNCTION check_stock() RETURNS TRIGGER AS $$
BEGIN
IF EXISTS (SELECT 1 FROM stock WHERE medicament_id = NEW.medicament_id AND
quantite >= NEW.duree_traitement) THEN
PERFORM update_stock();
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_stock_after_prescription
AFTER INSERT ON prescriptions
FOR EACH ROW
EXECUTE FUNCTION check_stock();
--2- le nombre de consultations par jour pour un patient spécifique avant
d'insérer une nouvelle consultation.
--Si le nombre de consultations pour ce patient à la même date dépasse 3, une
exception est levée.
CREATE OR REPLACE FUNCTION check_consultation_limit() RETURNS TRIGGER AS $$
DECLARE
consultation_count INT;
BEGIN
SELECT COUNT(*) INTO consultation_count
FROM consultations
WHERE patient_id = NEW.patient_id
AND date_consultation = NEW.date_consultation;
IF consultation_count >= 3 THEN
RAISE EXCEPTION 'Limite de consultations par jour atteinte pour ce
patient.';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER limit_consultations_per_day
BEFORE INSERT ON consultations
FOR EACH ROW
EXECUTE FUNCTION check_consultation_limit();
--Section 5.3: trigger dont l'événement est dû a une seule table ( AFTER et BEFORE
)
--1- BEFORE: Le Trigger est déclenché avant l'insertion d'une nouvelle
prescription.
-- Il vérifie si la quantité demandée d'un médicament est disponible en stock. Si
la quantité demandée n'est pas disponible, une exception est levée.
CREATE OR REPLACE FUNCTION check_medicament_availability() RETURNS TRIGGER AS $$
BEGIN
IF (SELECT quantite FROM stock WHERE medicament_id = NEW.medicament_id) <
NEW.quantite_prescrite THEN
RAISE EXCEPTION 'La quantité demandée de ce médicament n''est pas
disponible en stock.';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER validate_medicament_availability
BEFORE INSERT ON prescriptions
FOR EACH ROW
EXECUTE FUNCTION check_medicament_availability();
--2- AFTER: le Trigger AFTER est déclenché après l'insertion d'un nouveau rendez-
vous.
--Il met à jour le statut du rendez-vous en fonction de la date du rendez-vous :
'Manqué' si la date est passée, 'À venir' si c'est aujourd'hui, et 'Planifié'
sinon.
CREATE OR REPLACE FUNCTION update_rendez_vous_status() RETURNS TRIGGER AS $$
BEGIN
IF NEW.date_rendez_vous < CURRENT_DATE THEN
UPDATE rendez_vous
SET statut = 'Manqué'
WHERE rendez_vous_id = NEW.rendez_vous_id;
ELSIF NEW.date_rendez_vous = CURRENT_DATE THEN
UPDATE rendez_vous
SET statut = 'À venir'
WHERE rendez_vous_id = NEW.rendez_vous_id;
ELSE
UPDATE rendez_vous
SET statut = 'Planifié'
WHERE rendez_vous_id = NEW.rendez_vous_id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_rendez_vous_status_trigger
AFTER INSERT ON rendez_vous
FOR EACH ROW
EXECUTE FUNCTION update_rendez_vous_status();