1. Conception BD
1.1. 3 niveaux d'abstraction
1.1.1. Conceptuel
1.1.2. Logique
1.1.3. Physique
1.2. Classe
1.3. Attributs, identifiant
1.4. Association
1.4.1. Multiplicité
1.4.2. Binaire
1.4.3. Réflexive
1.4.4. Ternaire
1.4.5. N-aire
1.4.6. Classe-association
2. SQL de base
2.1. SQL : Structured Query Language
2.2. LDD : Langage de Définition des Données
2.2.1. But : Création, Modification et Suppression des structures de données
2.2.2. CREATE
2.2.2.1. Type de données
2.2.2.1.1. Number
2.2.2.1.2. Decimal
2.2.2.1.3. Varchar
2.2.2.1.4. Char
2.2.2.1.5. Date
2.2.2.1.6. ...
2.2.3. DROP
2.2.3.1. destruction dans l'ordre inverse de la création
2.2.3.2. option CASCADE CONSTRAINTS
2.2.4. ALTER
2.2.4.1. Ajout, modification, suppression de colonnes
2.2.4.2. Ajout, suppression de contraintes
2.2.4.3. Activation, désactivation de contraintes
2.2.5. Contraintes
2.2.5.1. Type de contraintes
2.2.5.1.1. Primary Key
2.2.5.1.2. Unique
2.2.5.1.3. Foreign Key
2.2.5.1.4. Not Null
2.2.5.1.5. Check
2.2.5.2. Différables ?
2.2.5.2.1. NOT DEFERRABLE (par défaut)
2.2.5.2.2. DEFERRABLE
2.2.6. INDEX
2.2.6.1. Buts : Accélération des sélections sur les colonnes, Optimisation des jointures
2.2.6.2. Types
2.2.6.2.1. Unique
2.2.6.2.2. Multiple
2.3. LMD : Langage de Manipulation des Données
2.3.1. But : Ajout, Modification et Suppression des données
2.3.2. INSERT
2.3.2.1. SEQUENCE
2.3.2.1.1. Curval
2.3.2.1.2. Nextval
2.3.3. UPDATE
2.3.4. DELETE
2.3.5. Transaction
2.3.5.1. Commit
2.3.5.2. Rollback
2.4. LID : Langage d’Interrogation des Données
2.4.1. But : Recherche de l’information
2.4.2. Mono table
2.4.2.1. SELECT { DISTINCT | * |col1 [,col2,….] [AS nom_col] } FROM nom_de_table WHERE <prédicat sur les lignes> GROUP BY col1 [,col2,….] HAVING <prédicat sur les groupages> ORDER BY { col1 { ASC | DESC } [, col2 …] | n°col }
2.4.2.2. Opérateurs
2.4.2.2.1. =, >, <, >=, <=, !=
2.4.2.2.2. IS NULL / IS NOT NULL
2.4.2.2.3. LIKE
2.4.2.2.4. BETWEEN
2.4.2.2.5. IN / NOT IN
2.4.2.3. Fonctions
2.4.2.3.1. NVL(dtn, ’n/a’)
2.4.2.3.2. TO_CHAR(dtN, ’YYYY’)
2.4.2.3.3. SUBSTR
2.4.2.3.4. COUNT(*), SUM(nt), AVG(nt), MIN(nt), MAX(nt)...
2.4.2.3.5. UPPER / LOWER (chaine)
2.4.2.3.6. SOUNDEX
2.4.2.3.7. TO_DATE(chaine, format)
2.4.2.4. Mise en forme de l'affichage
2.4.2.4.1. COLUMN
2.4.2.4.2. BREAK
2.4.2.4.3. TTITLE
2.4.2.4.4. BTITLE
2.4.2.4.5. AS 'Titre Colonne'
2.4.2.4.6. Variables d'environnement
2.4.2.5. Variables paramétrées : '&chaîne'
2.4.3. Multi Tables
2.4.3.1. Jointure Ensembliste
2.4.3.1.1. UNION
2.4.3.1.2. MINUS
2.4.3.1.3. INTERSECT
2.4.3.1.4. PRODUIT CARTESIEN
2.4.3.2. Jointure Forme Procédurale Déconnectée
2.4.3.2.1. IN, NOT IN
2.4.3.2.2. =, >, <, >=, <=, !=
2.4.3.3. Jointure Forme Relationnelle
2.4.3.3.1. N Tables --> N-1 Jointures
2.4.3.3.2. Equi-Jointure : T1.col1 = T2.col4
2.4.3.3.3. Jointure Externe : T1.col2 = T2.col4 (+)
2.4.3.4. Tables dynamiques
2.4.3.4.1. select ... from (select ... from t1) where...
2.4.3.5. Jointure Forme Procédurale Synchronisée
2.4.3.5.1. EXISTS, NOT EXISTS
2.4.3.5.2. =, >, <, >=, <=, !=
2.4.3.5.3. Mise en oeuvre de la Division
2.5. LCD : Langage de Contrôle des Données
2.5.1. Sous-Schéma
2.5.1.1. Restriction de la vision : VIEW
2.5.1.1.1. VUE = Table Virtuelle reconstruite à chaque appel
2.5.1.1.2. Buts
2.5.1.1.3. Mono-Table
2.5.1.1.4. Multi-Tables
2.5.1.1.5. CREATE [( OR REPLACE )] VIEW nom_vue [( liste des colonnes de la vue )] AS SELECT ………… [WITH CHECK OPTION [CONSTRAINT nom_cont] ];
2.5.1.1.6. DROP VIEW nom_vue;
2.5.2. Privilèges
2.5.2.1. Restriction des actions : GRANT / REVOKE
2.5.2.2. Systèmes
2.5.2.2.1. Régit l'utilisation de la BD au niveau du système (nom de l'utilisateur et mot de passe, espace disque alloué aux utilisateurs et opérations système autorisées par l'utilisateur)
2.5.2.2.2. Exemples de privilèges système
2.5.2.2.3. GRANT { priv_syst | rôle } [ , { priv2 | rôle2 }...] TO {utilisateur1 | rôle | PUBLIC} [,{utilisateur2 ...}] [WITH ADMIN OPTION] ;
2.5.2.2.4. REVOKE { priv_syst | rôle } [ , { priv | rôle2 }...] FROM { utilisateur1 | rôle | PUBLIC } [, { utilisateur2 ...} ] ;
2.5.2.3. Objets
2.5.2.3.1. Contrôle les actions (insert, execute,...) sur les objets (tables, vues,..)
2.5.2.3.2. GRANT privilège1 [, privilège2 ...] [ (colonne [,colonne2.])] ON schéma.objet TO { utilisateur1 | rôle | PUBLIC} [, { utilisateur2 ...} ] [ WITH GRANT OPTION ] ;
2.5.2.3.3. REVOKE privilège1 [, privilège2 ...] [ ( colonne [, colonne2...] ) ] ON schéma.objet FROM { utilisateur1 | rôle | PUBLIC} [, { utilisateur2 ...} ] [ CASCADE CONSTRAINTS ] ;
2.5.2.4. Rôles
2.5.2.4.1. Regroupement de privilèges par famille d'utilisateur
2.5.2.4.2. {CREATE | ALTER} ROLE nom_rôle { NOT IDENTIFIED | IDENTIFIED {BY mot_de_passe | EXTERNALLY} };
2.5.2.4.3. GRANT {privilège1 | rôle1} TO nom_rôle; GRANT {privilège2 | rôle2} TO nom_rôle; GRANT ROLE nom_role TO user;
2.5.2.4.4. REVOKE ROLE nom_rôle FROM user;
2.5.2.4.5. DROP ROLE nom_rôle;
2.5.3. Contraintes évènementielles
2.5.3.1. cf. SQL Avancé : Trigger
3. SQL avancé
3.1. TRIGGER
3.1.1. Programme événementiel associé à une table
3.1.2. 12 Types : Row ou Statement, Before ou After, Update / Insert / Delete
3.1.2.1. Before
3.1.2.1.1. But : mise en place de contraintes complexes
3.1.2.1.2. RAISE_APPLICATION_ERROR(n°erreur,’texte erreur’)
3.1.2.2. After
3.1.2.2.1. Buts : mise à jour de colonnes dérivées, génération d'évènements
3.1.2.2.2. Exemple Maj colonne dérivée : .... UPDATE produits p SET p.qtstock = p.qtstock - :NEW.qteCdee WHERE idprod = :NEW.idprod; ......
3.1.2.2.3. Exemple Génération d'evt : .... IF :NEW.qteStock <= :NEW.seuil THEN INSERT INTO reapprovisionnement VALUES (:NEW.idprod,:NEW.nomprod,:NEW.qtstock, :NEW.seuil);
3.1.3. Cas particulier : Trigger Instead Of
3.1.4. Syntaxe
3.1.4.1. DROP TRIGGER <nomtrigger> ;
3.1.4.2. CREATE [OR REPLACE] TRIGGER <nom_trigger> {BEFORE | AFTER} {INSERT | DELETE | UPDATE [OF colonnes] } ON <nom_table> [ FOR EACH ROW ] [ DECLARE ] -- déclaration de variables, exceptions -- curseurs BEGIN -- bloc action -- ordres SQL et PL/SQL END; /
3.1.4.3. ALTER TRIGGER <nomtrigger> ENABLE | DISABLE;
3.2. PL/SQL
3.2.1. ‘Procédural Language’ : sur-couche procédurale à SQL --> boucles, contrôles, affectations, exceptions, ….
3.2.2. Programmation adaptée pour : • Transactions • Une architecture Client - Serveur
3.2.3. Syntaxe
3.2.3.1. DECLARE -- déclarat° BEGIN -- exécutions EXCEPTION -- erreurs END; /
3.2.4. Tableaux dynamiques
3.2.5. package DBMS_OUTPUT
3.2.6. Curseurs
3.2.6.1. DECLARE CURSOR c1 IS SELECT ……; BEGIN OPEN c1; FETCH c1 INTO ………; WHILE (c1%FOUND) LOOP ……… ……… FETCH c1 INTO ………; END LOOP; CLOSE c1; END;
3.2.7. Exceptions
3.2.7.1. Exceptions SQL
3.2.7.1.1. Déja définies (NO_DATA_FOUND...)
3.2.7.1.2. Non définies
3.2.7.2. Exceptions applicatives
3.3. Procédures & Fonctions stockées
3.3.1. Programme (PL/SQL) stocké dans la base
3.3.2. Le programme est compilé et optimisé (INDEX,...)
3.3.3. Le programme client exécute une procédure stockée en lui passant des paramètres (par valeur) : EXECUTE Proc1 (param1, param2);
3.3.4. Avantages :
3.3.4.1. - Vitesse : programme compilé et optimisé - Intégrité : encapsulation des données - Performance : moins de transfert réseau - Abstraction : augmentation du niveau d’abstraction des développeurs - Performance : Extensibilité, Modularité, Réutilisation, Maintenance
3.3.5. CREATE [OR REPLACE] PROCEDURE <nom_procédure> [(variable1 type1, ..., variablen typen [OUT])] AS ... -- déclarations des variables et -- curseurs utilisées dans le corps de la procédure BEGIN .... -- instructions SQL ou PL/SQL EXCEPTION .... END; /
3.3.6. Fonctions stockées
3.3.6.1. - Comme une procédure mais qui ne retourne qu’un seul résultat - Même structure que procédure - RETURN pour retourner le résultat
3.4. Packages
3.4.1. Ensemble de programmes ayant un lien logique entre eux
3.4.2. • Partie ‘visible’ (spécification) – Interface accessible au programme appelant – Ne contient que les déclarations des procédures ou fonctions publiques
3.4.2.1. -- Partie Spécification CREATE [OR REPLACE] PACKAGE nom_package AS PROCEDURE Procédure1(liste des paramètres); ………… FUNCTION Fonction1(liste des paramètres); ………… END nom_package; /
3.4.3. • Partie ‘cachée’ ou body – Corps des procédures ou des fonctions citées dans la partie spécification – Nouvelles procédures ou fonctions privées
3.4.3.1. -- Partie body CREATE [OR REPLACE] PACKAGE BODY nom_package AS PROCEDURE Procédure1(liste des paramètres)IS ………… BEGIN ………… END Procédure1; FUNCTION Fonction1(liste des paramètres) RETURN type IS ………… BEGIN ………… RETURN(……); END Fonction1; END nom_package; /
3.4.4. ACCEPT vnom PROMPT 'Entrer le nom : ' EXECUTE etu.inscrire('&vnom',……);
3.5. SQL encapsulé dans un langage externe (Pro*C, VB...)
4. Passage au schéma relationnel
4.1. Niveau conceptuel vers niveau logique = DC vers SR
4.2. SR = ensemble de relations
4.3. Clé primaire
4.4. Clé étrangère
4.5. 4 règles de passage
4.5.1. 1 - transformation des classes
4.5.2. 2 - transformation des asso 1..*
4.5.3. 3 - transformation des asso N..M
4.5.4. 4 - transformation des asso 1..1
5. Normalisation
5.1. Buts : minimiser redondance infos, préserver intégrité infos
5.2. Dépendances Fonctionnelles (DF) : a --> b
5.2.1. DF Elémentaire
5.2.2. DF Directe
5.2.3. Propriétés
5.2.3.1. Réflexivité
5.2.3.2. Transitivité
5.2.3.3. Pseudo-transitivité
5.2.3.4. Additivité
5.2.3.5. Décomposition
5.2.3.6. Augmentation
5.3. Fermeture, Couverture minimale
5.4. 4 Formes Normales
5.5. Approche par décomposition
5.6. Approche par synthèse
6. Algèbre Relationnelle
6.1. But : Recherche d’informations sur le SR
6.2. Opérateurs
6.2.1. Unaires
6.2.1.1. Sélection
6.2.1.2. Projection
6.2.1.3. Agrégation
6.2.2. Binaires
6.2.2.1. Ensemblistes
6.2.2.1.1. Union
6.2.2.1.2. Intersection
6.2.2.1.3. Différence
6.2.2.1.4. Produit cartésien
6.2.2.2. Jointure
6.2.2.2.1. Semi-jointure
6.2.2.3. Division