UV Base de données A4 / Oracle

1. UV Base de données A4 / Oracle Pourquoi Oracle ? Découverte d’un SGBD/R Apprentissage du SQL Parts de marché importante du SGBD par aux autres acteurs

2. Pourquoi le SQL ? Démocratisation du SQL. Contrairement à d’autres langages informatiques comme le Cou le Cobol qui sont du domaine exclusif des programmeurs, SQL est employé par une grande variété de profils professionnels : des programmeurs, des administrateurs de bases de données, des analystes d’Infocentre. Enrichissement permanent du SQL. 1974 - 23 pages de description. 1992 - SQL-92 600 pages. 1999 quelques nouveautés ... Adaptation à différents éditeurs Oracle, Microsoft, Sybase, … Pas besoin d’être un expert pour être productif !!! UV Base de données A4 / SQL

3. Qu’est-ce qu’une base de données ? Définition Une base de données est un ensemble d’informations (Annuaire téléphonique, Carnet d’adresse, …). C’est une Intégration de toutes les données gérées par une organisation dans une même structure. La base de données peut être partagée par un ensemble d’applications et d’utilisateurs. Le système assurant la gestion de ces données est appelé Système de Gestion de Base de Données (SGBD). Les SGBD constituent aujourd’hui le cœur des systèmes d’information des entreprises. Son choix reste donc une décision stratégique ayant des conséquences à moyen et long terme sur le fonctionnement de l’entreprise.

4. Qu’est-ce qu’une base de données ? Les objectifs La définition des données. La manipulation des données. La sécurité et l’intégrité des données. La gestion des transactions et des accès concurrents. Une base de données doit être administrée et sauvegardée. Une base de données peut être centralisée, distribuée et répliquée.

5. Qu’est-ce qu’une base de données ? Les objectifs La globalisation par la gestion du National Language Support La gestion de flux des données entre bases de données (Oracle Streams) . La gestion de files d’attentes avancées dans la bases de données. L’exploitabilité par la manipulation des données ‘type XML’ (Oracle XDB) et la prise en charge de Java (incorporation d ’une JVM).

6. Qu’est-ce qu’une base de données ? Les objectifs La robustesse du noyau (reprise après incidents). La disponibilité permanente de la base de données (7 jours sur 7 et 24 heures sur 24). Haute disponibilité avec Oracle Data Guard Standby Database La clusterisation

7. Qu’est-ce qu’une base de données ? Les objectifs La montée en charge avec Oracle Real Application Clusters RAC La gestion des VLDB (Very Large DataBase) Oracle Partitioning. Le parallélisme des requêtes. La programmation d’un plan de ressources afin d’allouer spécifiquement le temps CPU / Utilisateurs. La traçabilité des opérations par le visualiseur Oracle LogMiner.

8. Qu’est-ce qu’une base de données ? Les objectifs La gestion d ’une base de données Data Warehouse (Dimensions d’un Star Schéma, Vues matérialiées, …). Oracle OLAP. Oracle Data Mining. Gestion des statistiques de l’optimiseur. Fonctions analytiques avancées.

9. Qu’est-ce qu’une base de données ? OLTP/OLAP Deux implémentations différentes d ’un SGBD/R OLTP : On Line Transactional Processing OLAP : On Line Analytical Processing OLTP : Informatique Transactionnelle de production Interactif : Saisie de commandes téléphiques, Internet, Saisie des règlements, ... Batch : Chaîne de facturation, livraison, règlement, … OLAP : Informatique Décisionnelle Analyse marketing (comportement d’achats par exemple en fonctions d ’indicateurs type d ’article, région ou secteur, CSP, …) Audit, Prospection, … Enquêtes statistiques (épidémiologiques par exemple).

10. Qu’est-ce qu’une base de données ? OLTP/OLAP OLTP : Informatique Transactionnelle de production Un grand nombre d ’utilisateurs. Beaucoup de requêtes de type insert, Update, Delete et Select simple. Beaucoup d’utilisateurs connectés : Chaîne de facturation, livraison, règlement, … Des temps de réponses inférieurs à la seconde. Schéma physique de données traditionnel. OLAP : Informatique Décisionnelle Un grand nombre d ’utilisateurs. Beaucoup de requêtes de type Select complexes (2 pages A4) avec beaucoup de jointures et des volumes de données important à traiter (Historique de données). Beaucoup d’utilisateurs connectés : Chaîne de facturation, livraison, règlement, … Des temps de réponses inférieurs à la seconde. VLDB (Very Large Data Base) Schéma physique de données de type en étoile ou en flocon (Star schema ou snowflake schema).

11. Qu’est-ce qu’une base de données ? OLTP/OLAP Un SGBD/R hybride est une implémentation mixte du type OLTP et du type OLAP. Ce type d’implémentation reste à éviter même si de nouvelles fonctionnalités sont implémentées afin d ’éviter certaines colisions. Le mode hybride ne pouvant satisfaire les particularités de chacun la base de données ne pourra que mal remplir sa mission.

12. Qu’est-ce qu’un Data Warehouse Un Data Warehouse (entrepôt de données) est une Very Large DataBase dont l’objectif principal est la collection de données de l’entreprise avec conservation de l’historique en vue d’analyses décisionnelles. Best Buy : 100 000 requêtes et rapports quotidiens, 3 000 utilisateurs et 1 900 magasinsBest Buy, le géant de la distribution de produits électroniques grand public, gère un entrepôt de données Oracle de 4,2 téraoctets de données, sous une instance Oracle unique utilisée par 3 000 personnes. L'application de gestion des performances dans les entreprises de distribution qu'utilise Best Buy gère 100 000 requêtes et rapports quotidiens, assistant ainsi 2 500 utilisateurs répartis dans plus de 1 900 magasins. Telecom Italia Mobile : 6 téraoctets de données, jusqu'à 250 millions d'enregistrements quotidiens et trois administrateurs de bases de données à plein tempsAvec plus de 50 millions de lignes téléphoniques à travers le monde, l'entrepôt de données Oracle de 6 téraoctets de Telecom Italia Mobile reçoit chaque jour jusqu'à 250 millions d'enregistrements détaillés sur les appels.

13. Qu’est-ce qu’un Data Warehouse France Télécom : 500 millions d'enregistrements quotidiens, 8 000 utilisateurs et 91 millions de clientsActeur de premier plan parmi les opérateurs internationaux de télécommunications, France Télécom utilise Oracle pour son entrepôt de données nommé Symphonie, qui recueille chaque jour pas moins de 500 millions d'enregistrements détaillés sur les appels. Symphonie comprend une base de données Oracle de 32 téraoctets, laquelle contient environ 180 milliards d'enregistrements détaillés sur les appels. Amazon.com : 5 téraoctets de données, 2 000 requêtes quotidiennes et 500 utilisateursLe plus grand site commercial en ligne, avec un chiffre d'affaires pour 2001 qui dépasse les 3 milliards de dollars, fonctionne avec un entrepôt de données Oracle de 5 téraoctets, qu'utilisent 500 personnes soumettant quotidiennement quelque 2 000 requêtes.

14. Qu’est-ce qu’une base de données ? Les objectifs La définition des données Utilisation d’un langage de définition des données (LDD). Ensemble de commandes permettant la création, modification, suppression des objets (Tables, Index, Vues, …). L’ensemble des description d’objets d’une base de données constitue ce que l’on appelle un dictionnaire des données. Ce dictionnaire de données est généralement structuré et géré comme une base de données. Il est appelé dans ce cas une métabase. La description des objets de la base peuvent être manipulées de la même façon que leurs valeurs. D’où la possibilité d’éditer le contenu de ce dictionnaire pour des besoins de développement ou de documentation.

15. Qu’est-ce qu’une base de données ? Les objectifs La manipulation des données Toutes les opérations d’échange de données entre les programmes des utilisateurs et la base de données (Insertion, Modification, Suppression) et les opérations de manipulation de la base de données telles que l’ouverture et la fermeture d ’une base. Utilisation d’un langage de manipulation des données (LMD). Un SGBD de type réseau ou hiérarchique n’offre pas les mêmes possibilités de manipulation de données qu’un système relationnel. Les LMD des SGBD réseaux et hiérarchiques sont composés d ’un certain nombre de primitives permettant d ’accéder à un type de données. De plus, le programmeur doit spécifier la procédure à suivre pour accéder à telle ou telle donnée. « Il ne suffit pas de dire ce qu’on veut avoir, mais aussi comment on y accède ». Le modèle relationnel dispose d’un ensemble d’opérateurs ensemblistes, dont l’application à des tables constitue l’algèbre relationnelle. Le langage relationnel le plus connu est le SQL.

16. Qu’est-ce qu’une base de données ? Les objectifs La sécurité et l’intégrité des données « Le but est de maintenir un état cohérent de la base de données ». Utilisation d’un langage de contrôle des données (LCD). Gestion des utilisateurs accédant à la base de données (création, suppression). Gestion des types d’opérations qu’ils sont autorisés à effectuer (privilèges des utilisateurs). Contrôle sur la validité des opérations effectuées : Contraintes d’intégrité temporelles Contraintes d’intégrité référentielles Protection contre les accès malveillants et les pannes Encryptage Reprise après panne (Tension, Disque Dur, …) Fonction d’audit avancée Exploitation Log Miner du journal des transactions

17. Qu’est-ce qu’une base de données ? Les objectifs La gestion des transactions et des accès concurrents « Garantir la cohérence des données lors des manipulations effectuées par les différents utilisateurs ». Le concept de transaction C’est une unité logique de transaction qui, appliquée à un état cohérent de la base de données, restitue un nouvel état cohérent, mais modifié de la base. Elle doit être exécutée complètement : on dit qu’elle est validée (COMMIT), ou pas du tout, on dit qu’elle est annulée (ROLLBACK). Le concept d’accès concurrents Verrouillage momentané des données utilisées par une transaction jusqu’à la fin de mise à jour. Attention au risque d’interblocage (verrou mortel ou étreinte mortelle). Le SGBD doit être capable de détecter une telle situation et de la débloquer automatiquement (Annuler l’une des transactions).

18. Qu’est-ce qu’une base de données ? Les objectifs Une base de données doit être administrée et sauvegardée !!! Le double rôle de l’administrateur d’une base de données (DBA) Le rôle organisationnel consiste concerne la définition du schéma conceptuel des données et le partage de ces données par les utilisateurs. Le rôle technique, il consiste à mettre en œuvre ce schéma et ce partage à l'aide des capacités techniques du SGBD.

19. Qu’est-ce qu’une base de données ? Les objectifs Installation du SGBD et des outils associés La première tâche de l'administrateur est d'installer le SGBD ainsi que les outils qui lui sont associés. Création de la base de données et assurer son évolution L'administrateur a la tâche de créer la base de données et ses composantes conformément à un schéma conceptuel. Il doit assurer aussi l'évolution de cette base en modifiant, en créant ou en supprimant certaines structures. Gestion des privilèges d'accès L'administrateur attribue et retire des privilèges d'accès aux données aux différents utilisateurs de la base de données. Amélioration des performances L'administrateur doit choisir l'implantation optimale des données de façon à obtenir les meilleures performances. Pour cela, il doit tenir compte des utilisations qui seront faites des données. Sécurité et cohérence des données L'administrateur doit mettre en place les structures et les procédures permettant de faire face à tous les incidents et de retrouver l'intégrité et la cohérence des données. Echange de données entre la base de données et le monde extérieur L'administrateur doit assurer l'intégration des données en provenance d'autres applications ou bases de données et faire migrer les données de la base vers d'autres applications ou bases de données.

20. Qu’est-ce qu’une base de données ? Les objectifs Une base de données peut être centralisée, distribuée et répliquée. Voir la présentation sur la réplication des données.

21. Qu’est-ce qu’une base de données ? Les acteurs Les acteurs du marché en 1998

22. Qu’est-ce qu’une base de données ? Les acteurs Les acteurs du marché actuel RACHAT de Informix par IBM Deux acteurs principaux ORACLE et IBM avec pour chacun environ 40 % des parts de marché. Un SQLServer qui ne tourne que sur les plateformes Microsoft. Le développement du monde de l’Open Source. Deux logiciels libres dans la catégorie SGBD offrant les meilleurs capacités en production : MySQL : Très bon pour l ’entrée de gamme PostgreSQL : dont les fonctionnalités se rapprochent de plus en plus d’un SGBD commercial.

23. Qu’est-ce qu’une base de données ? Les acteurs Oracle un acteur majeur Oracle 7 il y a presque 10 ans déjà … Oracle 8 depuis 4 ans Oracle 9 depuis 2 ans Oracle 9i (i pour Internet) Aujourd’hui Oracle 10g (g pour Grid Computing) www.oracle.fr / www.oracle.com

24. Qu’est-ce qu’une base de données ? Les acteurs Une adéquation importante aux systèmes d’exploitation : Oracle 9i / OS

25. Qu’est-ce qu’une base de données ? Les acteurs Une adéquation importante aux systèmes d’exploitation : Oracle 1Og / OS

26. Qu’est-ce qu’une base de données ? Le relationnel Le modèle relationnel Structure des données Manipulation des données Langages relationnels

27. Qu’est-ce qu’une base de données ? Le relationnel Structure des données Une base de données relationnelle se présente comme un ensemble de relations d’où le nom du modèle. Ces relations sont aussi appelées tables. Toute relation comprend Un schéma qui décrit sa structure Une extension qui correspond à l’état de cette relation à un instant donné.

28. Qu’est-ce qu’une base de données ? Le relationnel Structure des données Toute relation comprend un schéma Le schéma d’une relation décrit sa structure. Le schéma d’une table est composé d’un ensemble d’attributs (ou colonnes). Attributs (Colonnes) Domaine (Ensemble de valeurs atomiques de même type)

29. Qu’est-ce qu’une base de données ? Le relationnel Structure des données Toute relation comprend une extension. L’extension d’une relation est constituée par un ensemble de n-uplets (ou lignes). Un n-uplet correspond à un ensemble de valeurs prises par les colonnes d ’une table pour représenter un objet ou un lien entre des objets du monde réel. N-uplets (Lignes) Valeurs nulles

30. Qu’est-ce qu’une base de données ? Le relationnel Manipulation des données Le modèle relationnel dispose d’un ensemble d’opérateurs ensemblistes, dont l ’application à des tables constitue l’algèbre relationnelle. Les opérateurs unaires (Application à une seule table) La projection La sélection Les opérateurs binaires (Application à une deux tables) L’union L’intersection La différence (Les trois premiers ne peuvent s’appliquer qu’aux tables de même schéma). Le produit cartésien La jointure La division

31. Qu’est-ce qu’une base de données ? Le relationnel Langages relationnels Les opérateurs ensemblistes de l’algèbre relationnelle ne peuvent pas constituer à eux seuls un langage de manipulation de données. Un tel langage doit permettre : de préciser le type d’opération à exécuter (consultation, insertion, modification ou suppression) d’effectuer des calculs arithmétiques (somme, moyenne, maximum, ...) d’effectuer des conversions (caractère-numérique, majuscule-minuscule, ...)

32. La conceptualisation d’une base de données Etape primordiale L’analyse des flux et l’analyse des règles de gestion Le dictionnaire de données Il rassemble, sous un forme normalisée, l’ensemble des données et des types de données du projet. Ceux-ci sont stockés et gérés dans le Dictionnaire de Données avec leurs caractéristiques : Code Libellé Définition Liste de valeurs possibles Contraintes de valeur (unicité, vérification, intégrité référentielle, ...) Contraintes de confidentialité Type Longueur Format Obligatoire

33. La conceptualisation d’une base de données Le MCD (Modèle Conceptuel de Données) Le type de donnée L’entité L’association La patte et ses cardinalités Les règles de validation d’un modèle Le MLD (Modèle Logique de Données) Le MPD (Modèle Physique de Données) Les règles de normalisation

34. La conceptualisation d’une base de données Une étude de cas simple : Modélisation d’un client sachant que ce client peut se décliner en plusieurs types : L pour Local R pour Régional N pour National I pour International Le dictionnaire de données de cette étude de cas pourrait être : NUM_CLIENT Number(7) NOM_CLIENT Varchar2(30) ADRESSE_1_CLIENT Varchar2(40) ADRESSE_2_CLIENT Varchar2(40) CP_CLIENT Varchar2(5) VILLE_CLIENT Varchar2(30) DATE_CREATION Date LIBELLE_TYPE_CLIENT Varchar2(30)

35. Un exemple de MCD Identifiant Entité Relation Cardinalité

36. Un exemple de MCD 0 : Un client peut ne pas avoir de type de client 1 : Un client peut avoir au maximum un type de client

37. Un exemple de MCD 0 : Un type client peut ne pas être utilisé par un client n : Un type de client peut être utilisé par plusieurs clients

38. Un exemple de MPD Clé primaire Table Relation La cardinalité 0 du MCD (Un client peut ne pas avoir de type de client) rend le champ type_client dans la table client de type facultatif (non obligatoire).

39. Un exemple de MCD (Suite) Objectif : Conserver un historique de la relation

40. Un exemple de MCD (Suite) Objectif : Conserver un historique de la relation Relation porteuse d’informations

41. Un exemple de MPD (Suite) La relation porteuse d’informations se transforme en une table dont la clé primaire est composée

42. La création des tables Définition d’une table La notion de clé primaire (Primary Key) La notion de clé étrangère (Foreign Key)

43. La création des tables Définition d’une table La notion de table primaire (TABLE CLIENT) La notion de table référençante (TABLE TYPE_CLIENT)

44. La création des tables La clé étrangère permet de conserver l’intégrité référentielle entre ces tables dans la mesure ou : On ne pourra pas renseigner le champ type_client de la table client sans que préalablement cette valeur ne soit pas déclarer comme un enregistrement dans la table type_client.

45. La création des tables La clé étrangère permet de conserver l’intégrité référentielle entre ces tables dans la mesure ou : On ne pourra pas supprimer un enregistrement dans la table type_client tant qu ’il existera un client avec le même type client.

46. La création des tables Un mécanisme de suppression en cascade peut être ajouté à la clé étrangère: Attention cependant : la suppression d’un enregistrement dans la table type_client supprimera en cascade l’ensemble des clients ayant la même valeur.

47. La création des tables Définition d’une table Appartenance à un schéma

48. La création des tables Définition d’un d’index Un index permet d’accélérer l’accès aux enregistrements (recherche, tri, …). Oracle utilise les index d'une table de la même manière que vous utilisez l'index d'un livre. Pour rechercher des données, il recherche l'endroit où elles se situent dans l'index. Vous pouvez créer des index basés sur un seul ou plusieurs champs. Les index multichamps vous permettent de faire la distinction entre des enregistrements dont le premier champ peut contenir la même valeur. Les clés primaires sont automatiquement indéxées. Les index peuvent être définis avec doublons ou sans doublons. Vous souhaiterez sans doute indexer des champs interrogés fréquemment, triés ou joints à des champs d'autres tables dans des requêtes. Cependant, les index peuvent ralentir certaines requêtes Action telles que les requêtes Ajout, lorsque les index de plusieurs champs doivent être mis à jour en même temps que ces opérations sont exécutées.

49. La clé primaire d'une table est automatiquement indexée. Pour les autres champs, indexez un champ lorsque tous les éléments suivants s'appliquent : Les données du champ sont de type Texte, Numérique, Monétaire ou Date/Heure. Vous anticipez la recherche de valeurs stockées dans ce champ. Vous anticipez le tri de valeurs de ce champ. Vous anticipez le tri de nombreuses valeurs différentes de ce champ. Si de nombreuses valeurs de ce champ sont identiques, l'index n'accélérera sans doute pas de manière significative les requêtes. L’Index multichamp Si vous pensez que vous rechercherez ou trierez souvent les champs par deux ou plus à la fois, créez un index pour cette combinaison de champs. Par exemple, si vous définissez souvent des critères pour les champs Nom et Prénom de la même requête, il est recommandé de créer un index multichamp sur ces deux champs. Lorsque vous triez une table à l'aide d'un index multichamp, Oracle trie d'abord sur le premier champ défini pour cet index. Si des enregistrements contiennent des doublons dans le premier champ, Oracle trie ensuite sur le second champ défini pour cet index, etc. La création des tables

50. Le langage SQL ? SQL est un langage non procédural dédié, qui supporte la définition, la manipulation et le contrôle de données au sein de systèmes de gestion des bases de données relationnelles. C'est un langage dédié parce qu'il ne peut être utilisé que pour manipuler des bases de données. Autrement dit, impossible d'écrire une application d'ordre général dans ce langage. La construction d'applications implique l'insertion de SQL au sein d'un autre langage. C'est pour cela que SQL est souvent décrit comme un data sublanguage (sous-langage de données). Un sous-langage peut-être utilisé avec d'autres langages mais il ne se suffit pas à lui-même pour la réalisation d'applications. D'autant qu'un vrai langage intègre généralement une sémantique procédurale, ce qui n'est pas le cas du SQL.