BASES DE DONNÉES RELATIONNELLES CARACTÉRISTIQUES Bases de données Stockage et gestion de gros volumes de données Gros volumes de données Données persistantes partagées Technologies : recherche efficace mises à jour fiables Requêtes Efficacité de l’accès Accès à des données - persistantes, - partagées, - résistantes aux pannes, - pouvant être accédées et mises à jour de façon concurrente par d’autres applications. Contraintes Exprimées dans le langage de définition de données. Langage de définition et de manipulation de données orienté utilisateurs : SQL. I-1 Moonesh RAMBURRUN sources : divers et Modèles de données Modèle du système dans Modèle EA du client l’esprit du client Modèle de tables du modèle EA Serveur Tables sur disques I-2 Moonesh RAMBURRUN sources : divers et STRUCTURE RELATIONNELLE . Forme normale d'une relation : 1FN : tous les attributs sont simples et la relation possède une clé primaire. 2FN : 1FN et tous les attributs non clé sont en DF élémentaire avec la clé. 3FN : 2FN et tous les attributs non clé sont en DF directe avec la clé. Ex. Dept( deptno , dname, loc ) est en 3FN . Une liaison 1,n et 1,1 entre 2 entités -> 2 relations en 3FN Dans un département, il y a n employés et 1 employé appartient à 1 seul département D’où les 2 relations en 3FN suivantes : Dept( deptno , dname, loc ) -- deptno est clef primaire Emp (empno,ename,job,mgr,hiredate,sal,comm,deptno ) -- deptno est clef étrangère . Une liaison m,n entre 2 entités -> 3 relations en 3FN Un livre peut être écrit par plusieurs auteurs et un auteur peut écrire plusieurs livres. On se ramène à 2 liaisons 1,n et 1,1 en utilisant une association Auteur_livre: Auteur( numauteur, nom ) -- numauteur est clef primaire Livre( numlivre, titre, nb_de_pages, editeur) -- numlivre est clef primaire Auteur_Livre(numauteur,numlivre) --ici(numauteur, numlivre)est clef primaire I-3 Moonesh RAMBURRUN sources : divers et ALGEBRE RELATIONNELLE . Cas d’une seule table PROJECTION : .(horizontale) on diminue le nombre de colonnes. select empno, ename from emp; SELECTION : .(verticale à l’aide de where) on diminue le nombre de lignes. select * from emp where deptno = 10; TRI : (order by) tri sur critère(en croissant(ASCENDING) par défaut). select * from emp order by empno; REGROUPEMENT : (group by) tri sur critère puis constitution de sous-groupes. select deptno from emp group by deptno; . Cas de 2 tables ou plus PRODUIT CARTESIEN : select * from emp, dept; Ø-JOINTURE où Ø € { ‘=’, ‘!=’, ‘<’ ‘<=’, ‘>’, ‘>=’ }. Ex. EQUI-JOINTURE où Ø = ‘=’ select * from emp e ,dept d where e.deptno = d.deptno; UNION , INTERSECTION et DIFFERENCE( avec des colonnes compatibles) : select coli, colj from table1 UNION (respectivement INTERSECT ou MINUS ) select colk, colm from table2; I-4 Moonesh RAMBURRUN sources : divers et Définition d’une Base de Données Relationnelle Une BD relationnelle est une collection de relations ou de tables bi-dimensionnelles BD Nom de table : EMP EMPNO ENAME JOB 7839 7698 7782 KING BLAKE CLARK 7566 JONES I-5 Nom de table : DEPT DEPTNO DEPTNO PRESIDENT MANAGER MANAGER 10 30 10 MANAGER 20 Moonesh RAMBURRUN DNAME LOC 10 20 30 ACCOUNTING RESEARCH SALES NEW YORK DALLAS CHICAGO 40 OPERATIONS BOSTON sources : divers et Lier plusieurs tables • Chaque ligne dans une table est identifiée de façon unique par une clé primaire. • Vous pouvez lier logiquement des données de plusieurs tables en utilisant les clés étrangères Nom de la table : EMP EMPNO 7839 7698 7782 7566 ENAME KING BLAKE CLARK JONES Clé primaire I-6 JOB PRESIDENT MANAGER MANAGER MANAGER Nom de la table : DEPT DEPTNO 10 30 10 20 Clé étrangère Moonesh RAMBURRUN DEPTNO 10 20 30 40 DNAME ACCOUNTING RESEARCH SALES OPERATIONS Clé primaire sources : divers et LOC NEW YORK DALLAS CHICAGO BOSTON Terminologie des BD relationnelles 2 3 EMPNO ENAME JOB 4 MGR HIREDATE SAL COMM DEPTNO 6 ------------- ------------ --------------------- -------- ---------------- ----------- -------------- 1 I-7 7839 KING PRESIDENT 7698 BLAKE MANAGER 7782 CLARK ----------- 17-NOV-81 5000 10 7839 01-MAY-81 2850 30 MANAGER 7839 09-JUN-81 2450 10 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7900 JAMES CLERK 7698 03-DEC-81 950 7521 WARD SALESMAN 7698 22-FEB-81 1250 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 Moonesh RAMBURRUN 5 sources : divers et 30 500 30 Communiquer avec un SGBDR en utilisant SQL Un ordre SQL est entré SQL> SELECT loc 2 FROM dept; L’ordre est envoyé à la BD BD Les données sont affichées LOC ------------NEW YORK DALLAS CHICAGO BOSTON I-8 Moonesh RAMBURRUN sources : divers et SGBDR Serveur Tables utilisateur I-9 Moonesh RAMBURRUN sources : divers et Dictionnaire des données Solution Oracle Complète Oracle Developer Applications Finance Fabrication ... Discoverer Oracle Designer SQL Oracle10G PL/SQL Database SQL* Plus Dictionnaire des données Tables de données I-10 Moonesh RAMBURRUN sources : divers et ORDRES SQL I-11 SELECT Recherche de données INSERT UPDATE DELETE Langage de manipulation de données (DML) CREATE ALTER DROP RENAME TRUNCATE Langage de définition de Données (DDL) COMMIT ROLLBACK SAVEPOINT Contrôle des transactions GRANT REVOKE Langage de contrôle des données (DCL) Moonesh RAMBURRUN sources : divers et Tables Utilisées dans le Cours EMP EMPNO --------7839 7698 ENAME ---------KING BLAKE 7782 CLARK 7566 JONES 7654 MARTIN 7499 ALLEN 7844 TURNER DEPT 7900 JAMES DEPTNO 7521 DNAMEWARD FORD --------- 7902 -------------SMITH 10 7369 ACCOUNTING SCOTT 20 7788 RESEARCH 30 7876 SALESADAMS MILLER 40 7934 OPERATIONS I-12 JOB MGR HIREDATE SAL COMM DEPTNO --------- --------- --------- --------- --------- --------PRESIDENT 17-NOV-81 5000 10 MANAGER 7839 01-MAY-81 2850 30 MANAGER MANAGER SALESMAN SALESMAN SALESMAN CLERK SALESMAN LOC 7839 7839 7698 7698 7698 7698 7698 09-JUN-81 02-APR-81 28-SEP-81 20-FEB-81 08-SEP-81 03-DEC-81 22-FEB-81 2450 2975 1250 1600 1500 950 1250 ANALYST ---------CLERK NEW YORK ANALYST DALLAS CLERK CHICAGO CLERK BOSTON 7566 7902 7566 7788 7782 03-DEC-81 17-DEC-80 09-DEC-82 12-JAN-83 23-JAN-82 SALGRADE 3000 Moonesh RAMBURRUN 1400 300 0 500 800 GRADE LOSAL 3000 --------- --------1100 1 700 1300 2 1201 3 1401 4 2001 5 3001 sources : divers et 10 20 30 30 30 30 30 20 20 HISAL 20 --------20 1200 10 1400 2000 3000 9999 Interaction entre SQL and SQL*Plus SQL Statements Buffer SQL Statements Server SQL*Plus SQL*Plus Commands Query Results Formatted Report I-13 Moonesh RAMBURRUN sources : divers et Ordres SQL / Commandes SQL*Plus SQL • Un langage • Standard ANSI • Mots clés : pas d’abréviation • Les ordres manipulent des définitions de données et de tables de la base de données SQL statements I-14 SQL buffer Moonesh RAMBURRUN SQL*Plus • Un environnement • Propriété d’Oracle • Abréviation possible des mots clés • Les commandes ne permettent pas la manipulation des valeurs de la base de données. SQL*Plus commands sources : divers et SQL*Plus buffer Aperçu de SQL*Plus • Ouvrir une session SQL*Plus. • Décrire la structure de la table. • Éditer vos ordres SQL. • Exécuter SQL à partir de SQL*Plus. • Sauvegarder des ordres SQL dans des fichiers et ajouter des ordres SQL aux fichiers. • Exécuter des fichiers sauvegardés. • Charger des commandes à partir d’un fichier vers un buffer pour les éditer. I-15 Moonesh RAMBURRUN sources : divers et Se connecter à SQL*Plus • DIRECTEMENT sous sqlplus : Démarrer->programmes->oracle->sqlplus Ou sur le WEB par : http://iuta1.univ-lyon1.fr:5560/isqlplus Nom d’utilisateur : « prénom.nom » Mot de passe : « prénom.nom » Identificateur de connexion : iuta I-16 Moonesh RAMBURRUN sources : divers et Afficher la structure des tables Commande SQL*Plus : DESCRIBE DESC[RIBE] tablename I-17 Moonesh RAMBURRUN sources : divers et Afficher la structure des tables SQL> DESC dept Name Null? ----------------- -------DEPTNO NOT NULL DNAME LOC I-18 Moonesh RAMBURRUN Type -----------NUMBER(2) VARCHAR2(14) VARCHAR2(13) sources : divers et