Pour la première contrainte "Un patient ne peut avoir qu'un médecin référent", aucune modification à effectuer. En effet dans la table Patient, le médecin référent est soit nul, soit référence un id d'un seul médecin (foreign key).
ALTER TABLE MEDECIN
ADD CONSTRAINT nbPatientValid CHECK (nombrepatients <= 100)
Pour la deuxième contrainte : "Un médecin ne peut être le référent que de 100 patients au maximum", on créee une procédure qucompte le nombre le nombre des patients de chaque médecin, et une contrainte sur médecin.
CREATE OR REPLACE PROCEDURE update_All_NbPatient IS
BEGIN
UPDATE MEDECIN m
SET m.nombrePatients = (
SELECT COUNT(*)
FROM Patient p
WHERE p.medecin_referent = m.medecin_id
);
END update_All_NbPatient;
Enfin, on crée un trigger portant sur INSERT, UPDATE et DELETE. Grâces aux variables INSERTING, UPDATING et DELETING, on peut faire au cas par cas.
Dans le cas d'un INSERT, on ajoute simplement 1 au nombre de patients pour le médecin référent, s'il n'est pas NULL.
Dans le cas d'un DELETE, on retire 1 au nombre de patients du médecin référent.
Enfin, dans le cas d'un update, 2 possibilités :
CREATE OR REPLACE TRIGGER update_medecin_referent
AFTER DELETE OR INSERT OR UPDATE OF medecin_referent ON PATIENT
FOR EACH ROW
BEGIN
IF DELETING THEN
IF :OLD.medecin_referent IS NOT NULL THEN
UPDATE MEDECIN
SET nombrePatients = nombrePatients - 1
WHERE medecin_id = :OLD.medecin_referent;
END IF;
ELSIF INSERTING THEN
IF :NEW.medecin_referent IS NOT NULL THEN
UPDATE MEDECIN
SET nombrePatients = nombrePatients + 1
WHERE medecin_id = :NEW.medecin_referent;
END IF;
ELSIF UPDATING THEN
IF :OLD.medecin_referent != :NEW.medecin_referent OR (:NEW.medecin_referent IS NULL) THEN
UPDATE MEDECIN
SET nombrePatients = nombrePatients - 1
WHERE medecin_id = :OLD.medecin_referent;
IF :NEW.medecin_referent IS NOT NULL THEN
UPDATE MEDECIN
SET nombrePatients = nombrePatients + 1
WHERE medecin_id = :NEW.medecin_referent;
END IF;
END IF;
END IF;
END;
On crée un trigger qusélectionne la valeur max des id de médecin, et assigne un id+1 au nouveau médecin. Il faut cependant que la valeur de l'id lors de l'insert soit nulle (ou du moins, il faut qu'elle existe).
CREATE OR REPLACE TRIGGER auto_increment_medecin
BEFORE INSERT ON MEDECIN
FOR EACH ROW
DECLARE
max_id INT;
BEGIN
-- On récupère la valeur maximale de medecin_id et on la met dans max_id. Scette valeur est NULL,
-- elle devient 0 grâce à NVL.
SELECT NVL(MAX(m.MEDECIN_ID), 0) INTO max_id FROM MEDECIN m;
-- L'id du nouveau médecin sera le max_id + 1
:NEW.MEDECIN_ID := max_id + 1;
END;
ALTER TABLE MEDECIN ADD nombrePrescriptions NUMBER(10);
CREATE OR REPLACE PROCEDURE UPDATE_ALL_NBPRESCRIPTION AS
BEGIN
UPDATE MEDECIN m
SET m.nombrePrescriptions = (
SELECT COUNT(*)
FROM PRESCRIPTION p
WHERE p.MEDECIN_ID = m.MEDECIN_ID
);
END UPDATE_ALL_NBPRESCRIPTION;
CREATE OR REPLACE TRIGGER UPDATE_NBPRESCRIPTIONS
AFTER DELETE or INSERT or UPDATE OF medecin_id ON PRESCRIPTION
FOR EACH ROW
BEGIN
-- Sle médecin référent a changé (comparaison des anciens et nouveaux médecins)
-- Décrémentation du nombre de patients pour l'ancien médecin référent
IF DELETING THEN
IF :OLD.medecin_id IS NOT NULL THEN
UPDATE MEDECIN
SET nombrePrescriptions = nombrePrescriptions - 1
WHERE medecin_id = :OLD.medecin_id;
END IF;
ELSIF INSERTING THEN
IF :NEW.medecin_id IS NOT NULL THEN
UPDATE MEDECIN
SET nombrePrescriptions = nombrePrescriptions + 1
WHERE medecin_id = :NEW.medecin_id;
END IF;
ELSIF UPDATING THEN
IF :OLD.medecin_id != :NEW.medecin_id OR (:NEW.medecin_id IS NULL AND :OLD.medecin_id IS NOT NULL) THEN
UPDATE MEDECIN
SET nombrePrescriptions = nombrePrescriptions - 1
WHERE medecin_id = :OLD.medecin_id;
END IF;
IF (:NEW.medecin_id IS NOT NULL AND :OLD.medecin_id IS NULL) OR (:OLD.medecin_id != :NEW.medecin_id) THEN
UPDATE MEDECIN
SET nombrePrescriptions = nombrePrescriptions + 1
WHERE medecin_id = :NEW.medecin_id;
END IF;
END IF;
END;
CREATE OR REPLACE VIEW VUE_CONSULT_MEDECIN AS
SELECT
m.MEDECIN_ID,
m.NOM as "Nom Médecin",
m.PRENOM as "Prénom Médecin",
v.DATE_VISITE,
v.PRIX,
p.PATIENT_ID,
p.NOM as "Nom Patient",
p.PRENOM as "Prénom Patient"
FROM MEDECIN m, VISITE v, PATIENT p
WHERE m.MEDECIN_ID = v.MEDECIN_ID and p.PATIENT_ID = v.patient_id
ORDER BY 1 ASC;
create or replace TRIGGER suppr_medecins_3_consultations
INSTEAD OF DELETE ON vue_consult_medecin
FOR EACH ROW
DECLARE
v_count INTEGER;
BEGIN
-- Comptage des consultations du médecin
SELECT COUNT(*)
INTO v_count
FROM vue_consult_medecin
WHERE medecin_id = :OLD.medecin_id;
-- Suppression des médecins n'ayant pas plus de 3 consultations
IF v_count <= 3 THEN
DELETE FROM medecin
WHERE medecin_id = :OLD.medecin_id;
END IF;
END;
NB : mettre les clés étrangères en ON DELETE CASCADE sur les foreign pour ne pas avoir de problème
ALTER TABLE MEDECIN ADD superieur NUMBER(10);
update medecin set superieur = 1 where medecin_id = 2
update medecin set superieur = 2 where medecin_id = 3
update medecin set superieur = 5 where medecin_id = 4
update medecin set superieur = 3 where medecin_id = 5
update medecin set superieur = 1 where medecin_id = 6
SELECT medecin_id AS Subordonne,
nom AS Nom_Subordonne
FROM MEDECIN
WHERE medecin_id != 1
START WITH medecin_id = 1
CONNECT BY PRIOR medecin_id = superieur;
SELECT medecin_id AS Superieur,
nom AS Nom_Superieur
FROM MEDECIN
WHERE medecin_id != 1
START WITH medecin_id = 1
CONNECT BY PRIOR superieur = medecin_id;
SELECT COUNT(*)
FROM DBA_USERS;
SELECT GRANTEE, PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS);
SELECT COUNT(*)
FROM DBA_ROLES;
SELECT GRANTEE AS "UTILISATEUR",
GRANTED_ROLE AS "ROLE"
FROM DBA_ROLE_PRIVS;
SELECT *
FROM DBA_PROFILES
WHERE PROFILE = 'DEFAULT';
CREATE USER E224359TBidochon
IDENTIFIED BY password;
GRANT CREATE SESSION TO E224359TBIDOCHON;
GRANT SELECT ON MEDECIN TO E224359TBIDOCHON;
GRANT SELECT ON PATIENT TO E224359TBIDOCHON;
GRANT SELECT ON VISITE TO E224359TBIDOCHON;
GRANT SELECT ON PRESCRIPTION TO E224359TBIDOCHON;
SELECT * from E224359T.MEDECIN;
SELECT * from E224359T.PATIENT;
SELECT * from E224359T.VISITE;
SELECT * from E224359T.PRESCRIPTION;
INSERT INTO MEDECIN (medecin_id, nom, prenom, adresse, tel, specialite, nombrePatients) VALUES (1, 'Bidochon', 'test', 'Paris', '0625125845', 'generaliste', 0);
INSERT INTO VISITE (medecin_id, patient_id, date_visite, prix) VALUES (11, 5, TO_DATE('2024-12-31', 'YYYY-MM-DD'), 75);
REVOKE SELECT ON MEDECIN FROM E224359TBIDOCHON;
REVOKE SELECT ON PATIENT FROM E224359TBIDOCHON;
REVOKE SELECT ON VISITE FROM E224359TBIDOCHON;
REVOKE SELECT ON PRESCRIPTION FROM E224359TBIDOCHON;
CREATE OR REPLACE VIEW VISITES_BIDOCHON AS
SELECT v.*
FROM VISITE v
LEFT JOIN MEDECIN m ON v.MEDECIN_ID = m.MEDECIN_ID
LEFT JOIN PATIENT p ON v.PATIENT_ID = p.PATIENT_ID
WHERE m.NOM = 'Bidochon' OR p.NOM = 'Bidochon';
GRANT SELECT ON VISITES_BIDOCHON TO E224359TBIDOCHON;
SELECT * from E224359T.VISITES_BIDOCHON;
DROP USER E224359TBidochon CASCADE;
SELECT PATIENT_ID, ROWNUM, ROWID
FROM PATIENT;
-- table space
SELECT TABLESPACE_NAME
FROM USER_TABLES
WHERE TABLE_NAME = 'PATIENT';
-- schema => owner
SELECT HEADER_FILE
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'PATIENT' and OWNER = USER;
-- USER remplace le nom de l'utilisateur connecté (ex : E224359T)
-- name file via le header_file
SELECT *
FROM DBA_DATA_FILES
WHERE FILE_ID = 13;
Sans requête : Statistiques de la table -> (blocks)
-- nb de blocks utilisés par la table patient
SELECT BLOCKS
FROM DBA_TABLES
WHERE TABLE_NAME = 'PATIENT' AND OWNER = USER;
--nb blocks aloués pour la table patient et la taille des blocks en question
SELECT BLOCKS, BYTES/BLOCKS as BLOCK_SIZE
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'PATIENT' AND OWNER = USER;
--soit la taille = nb blocks utilisés x taille block
SELECT
TABLESPACE_NAME AS "TABLESPACE", -- Nom du tablespace
SEGMENT_TYPE AS "TYPE OBJET", -- Type d'objet stocké (par exemple : TABLE, INDEX, LOB, etc.)
SUM(BYTES) / 1024 / 1024 AS "TAILLE (Mb)" -- Taille totale en mégaoctets (Mo)
FROM
DBA_EXTENTS
WHERE
OWNER = USER -- Requête uniquement pour l'utilisateur connecté
GROUP BY
OWNER, TABLESPACE_NAME, SEGMENT_TYPE -- Groupement par propriétaire, tablespace et type d'objet
ORDER BY
OWNER, TABLESPACE_NAME -- Trier par propriétaire et tablespace
SELECT INDEX_NAME
FROM USER_INDEXES
WHERE TABLE_NAME='PATIENT';
CREATE INDEX idx_patients_nom ON PATIENT(NOM);
-- Index sur la clé primaire
EXPLAIN PLAN FOR SELECT * FROM PATIENT WHERE PATIENT_ID=1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Index sur nom
EXPLAIN PLAN FOR SELECT * FROM PATIENT WHERE NOM='Skywalker';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
SELECT BYTES/1024 AS "Taille (ko)",
SEGMENT_NAME AS "Nom segment"
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE='INDEX';
SELECT SUM(BYTES)/1024/1024 AS "Taille totale (Mo)"
FROM USER_SEGMENTS;
CREATE TABLE INDEX_PATIENT (
patient_id NUMBER(10) NOT NULL PRIMARY KEY,
nom VARCHAR(20),
prenom VARCHAR(20),
numsecu NUMBER(13),
rattachement NUMBER(10) REFERENCES PATIENT(patient_id),
medecin_referent NUMBER(10) REFERENCES MEDECIN(medecin_id)
) ORGANIZATION INDEX;
-- Taille de la table
SELECT BLOCKS
FROM USER_TABLES
WHERE TABLE_NAME='INDEX_PATIENT';
Il n'y a aucun bloc (null)