Feuille Mémoire BDD2 - Révision Complète
1. Désimbrication et Opérations Relationnelles
Union, Intersection, Différence
Union : Combine les résultats de deux requêtes (élimine les doublons).
(SELECT deptno FROM emp WHERE job = 'Développeur')
UNION
(SELECT deptno FROM dept WHERE dname = 'SERVICE_INFORMATIQUE');
Différence (MINUS) : Trouve les lignes d'une requête qui ne sont pas dans une autre.
SELECT * FROM emp WHERE hiredate > '12/10/2020'
MINUS
SELECT * FROM emp WHERE hiredate > '01/07/2021' AND job = 'Période essai';
Partitionnement et GROUP BY
Identifier les groupes respectant des conditions spécifiques.
SELECT ename, job, salaire
FROM emp
WHERE deptno IN (
SELECT deptno
FROM emp
GROUP BY deptno
HAVING COUNT(*) >= 2
);
Monotonie des Requêtes
Une requête est monotone si ajouter des données dans la base ne fait pas disparaître des résultats existants.
Exemple monotone :
SELECT deptno FROM emp WHERE salaire > 2000;
Exemple non monotone (avec MINUS) :
SELECT deptno
FROM emp
WHERE job = 'Manager'
MINUS
SELECT deptno
FROM dept
WHERE dname = 'Comptabilité';
OUTER JOIN
Exemple : Les employés qui ne sont pas chefs d’un département.
SELECT e.ename
FROM emp e
LEFT OUTER JOIN dept d ON e.empno = d.chef
WHERE d.chef IS NULL;
2. Transactions
Notions Importantes
Conflit-sérialisabilité : Absence de cycle dans le graphe de précédence.
Lecture répétable : Empêche les changements dans les données lues.
Niveaux d’isolation :
READ COMMITTED : Évite les lectures sales.
SERIALIZABLE : Garantit une cohérence stricte.
Exemple d'Exécution
1. Dessinez le graphe de précédence avec :
Lecture : (L_i(X))
Écriture : (E_i(X))
2. Exemple de graphe à analyser :
T1: L3(Y), E1(X), E1(Z)
T2: L2(Y), E2(Y), E2(Z)
3. Index et Optimisation
Utilisation des Index
Un index n’est pas utilisé si la requête nécessite des transformations non indexées.
CREATE INDEX emp_hiredate_idx ON emp(salaire);
SELECT ename, job
FROM emp
WHERE ABS(salaire - 1000) = 500; -- Pas d'index utilisé ici.
Nombre d'Index
Nombre minimal : Aucun (aucun index obligatoire).
Nombre maximal : Un index possible pour chaque combinaison unique des colonnes, mais déconseillé en pratique pour éviter des coûts élevés d’entretien.
4. PL/SQL
Déclencheurs
Exemple : Contrôle des droits de publication.
CREATE OR REPLACE TRIGGER check_publication_validity
BEFORE INSERT ON Publication_mur
FOR EACH ROW
BEGIN
IF NOT EXISTS (
SELECT 1 FROM Amis
WHERE utilisateur = :NEW.auteur
AND ami_utilisateur = :NEW.id_dest_mur
AND droit_mur = TRUE
) THEN
RAISE_APPLICATION_ERROR(-20001, 'Publication non autorisée.');
END IF;
END;
/
Utilisation des Curseurs
Avec FOR UPDATE pour verrouiller les lignes.
DECLARE
CURSOR c_emp IS
SELECT empno, salaire
FROM emp
WHERE job = 'Développeur'
FOR UPDATE;
BEGIN
FOR rec IN c_emp LOOP
UPDATE emp
SET salaire = salaire + 100
WHERE CURRENT OF c_emp;
END LOOP;
END;
/
5. Modélisation et Contraintes
Création de Tables avec Contraintes
Exemple : Table Plat avec des contraintes.
CREATE TABLE Plat (
idPlat INT PRIMARY KEY,
nom VARCHAR(50) CHECK (REGEXP_LIKE(nom, '^[a-z ]+$')),
tpsPreparation INT NOT NULL,
totalCalorie INT CHECK (totalCalorie BETWEEN 100 AND 2000),
végétarien CHAR(1) CHECK (végétarien IN ('V', 'F')),
prixPlat DECIMAL(5, 2) NOT NULL
);
Symétrie dans les Relations
Exemple : Relation Amis symétrique.
CREATE OR REPLACE TRIGGER ensure_symmetry
AFTER INSERT ON Amis
FOR EACH ROW
BEGIN
IF NOT EXISTS (
SELECT 1 FROM Amis
WHERE utilisateur = :NEW.ami_utilisateur
AND ami_utilisateur = :NEW.utilisateur
) THEN
INSERT INTO Amis (utilisateur, ami_utilisateur, droit_mur)
VALUES (:NEW.ami_utilisateur, :NEW.utilisateur, FALSE);
END IF;
END;
/
6. Évaluation de Requêtes et Plans d’Exécution
Optimisation des Plans
Appliquez les sélections avant les jointures pour réduire les volumes.
Évitez les produits cartésiens non nécessaires.
7. Cas Pratiques
Requêtes SQL
1. Trouver l’ingrédient le plus cher :
SELECT nom, MAX(prix)
FROM Ingredient;
2. Tous les plats végétariens
SELECT DISTINCT p.nom
FROM Plat p
JOIN Plat_Ingredient pi ON p.idPlat = pi.idPlat
JOIN Ingredient i ON pi.idIngredient = i.idIngredient
WHERE i.végétarien = 'V'
GROUP BY p.idPlat
HAVING COUNT(DISTINCT pi.idIngredient) = COUNT(DISTINCT i.idIngredient);
