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 :

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)