TP Base de données

Chapitre 2

1. Contraintes d'intégrité

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 :

• On remplace un médecin référent par un autre : dans ce cas on fait comme pour un DELETE suivd'un INSERT, on retire 1 au nombre de patients de l'ancien médecin et on ajoute 1 au nombre de patients du nouveau médecin. Pas de problème sl'ancien médecin référent est NULL, on ne rentre même pas dans le premier IF.
• On remplace le médecin référent par NULL : dans ce cas, on fait comme pour un delete, on retire 1 au nombre de patients de l'ancien médecin.

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;

2. Clé primaire

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;

3. Contraintes d'intégrité

1. Alter table

ALTER TABLE MEDECIN ADD nombrePrescriptions NUMBER(10);

2. Procédure

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;

3. Trigger

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;

4. Vues modifiables

1. Vue sur médecin et consultations

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;

2. Supprimer les médécins ayant pas plus de 3 visites (à partir des données de la vue créée précédemment)

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

5. Requêtes hiérarchiques

Ajouter la colonne superieur dans la table MEDECIN

ALTER TABLE MEDECIN ADD superieur NUMBER(10);

Update de la table MEDECIN

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

Lister pour un médecin tous les médecins sous ses ordres directement ou indirectement (priorité au medecin_id)

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;

Lister pour un médecin, ses supérieurs hiérarchiques directs ou indirects (priorité au 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;

Chapitre 3

1. Consulter le dictionnaire des données

1. Combien d'utilisateurs sont affectés à la partition USER ? Réponse : 457

SELECT COUNT(*) 
FROM DBA_USERS;

2. Quels sont leurs privilèges ? Réponse via requête

SELECT GRANTEE, PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS);

3. Combien de roles sont présents dans la base ? Réponse : 91

SELECT COUNT(*)
FROM DBA_ROLES;

4. Quels sont les utilisateurs qupossèdent ces rôles ? Réponse via requête

SELECT GRANTEE AS "UTILISATEUR", 
GRANTED_ROLE AS "ROLE"
FROM DBA_ROLE_PRIVS;

5. Existe-t-il des profils prédéfinis ? Réponse : Oui

SELECT * 
FROM DBA_PROFILES
WHERE PROFILE = 'DEFAULT';

2. Gestion des utilisateurs

1. Création utilisateur et son mot de passe

CREATE USER E224359TBidochon 
IDENTIFIED BY password;

2. Connexion (ajout droit de création de session à l'utilisateur)

GRANT CREATE SESSION TO E224359TBIDOCHON;

3. Donner droit d'accès aux tables et les affichées depuis l'utilisateur E224359TBidochon (depuis E224359T)

GRANT SELECT ON MEDECIN TO E224359TBIDOCHON;
GRANT SELECT ON PATIENT TO E224359TBIDOCHON;
GRANT SELECT ON VISITE TO E224359TBIDOCHON;
GRANT SELECT ON PRESCRIPTION TO E224359TBIDOCHON;

4. Test consultation (depuis E224359TBidochon)

SELECT * from E224359T.MEDECIN;
SELECT * from E224359T.PATIENT;
SELECT * from E224359T.VISITE;
SELECT * from E224359T.PRESCRIPTION;

5. Donner accès aux visites là où Bidochon est concerné

insert d'un medecin (bidochon) et d'une visite liée

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);

enlever droits d'accès aux tables puis créer et donné à accès à une vue avec uniquement les visites concernées par Bidochon

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;

test consultation de la vue (depuis E224359TBidochon)

SELECT * from E224359T.VISITES_BIDOCHON;

6. suppresion de E224359TBidochon et élements liés

DROP USER E224359TBidochon CASCADE;

Chapitre 4

1. Stocker les données dans la base

1. Lister les identifiants physiques des patients, leur no d’ordre (rownum) et leur identifiant logique (clé primaire)

SELECT PATIENT_ID, ROWNUM, ROWID
FROM PATIENT;

2. Dans quel tablespace de la base se trouve la table patients, dans quel schéma et dans quel fichier physique est-elle stockée

-- 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;

3. Quelle est la taille en blocs et en octets de la table Patients ? Pouvez-vous le vérifier directement sans utiliser les commandes SQL ?

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

4. Exécutez et expliquez cette requete

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

2. Manipuler les structures d'accès

1. Quel est le nom de l'index définsur la clé primaire de la table patients ?

SELECT INDEX_NAME
FROM USER_INDEXES
WHERE TABLE_NAME='PATIENT';

2. Créer un index sur la colonne nom de la table patients.

CREATE INDEX idx_patients_nom ON PATIENT(NOM);

3. Formulez deux questions permettant de mettre en oeuvre chacun de ces deux index

-- 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);

4. Quelle est la taille de chacun de vos segments d'index ?

SELECT BYTES/1024 AS "Taille (ko)",
SEGMENT_NAME AS "Nom segment"
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE='INDEX';

5. Quelle est l'espace occupé par votre schéma ?

SELECT SUM(BYTES)/1024/1024 AS "Taille totale (Mo)"
FROM USER_SEGMENTS;

6. Créer une table index à partir de la table Patients. Quelle est sa taille ?

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)