Bases de données

Bases de données Alexandre Abellardalexandre.abellard@univ-tln.fr IUT Toulon – GEIISemestre 3 / 2008-09

Sommaire Définitions (Bases De Données, Système de Gestion de Bases de Données) Classifications des SGBD Bases de données relationnelles Modèle Relationnel (Entité – Association) Méthode MERISE Langage SQL

Définition Base de données (BDD ; ang. : database) = ensemble structuré et organisé permettant le stockage de données (informations) liées entre elles, afin d'en faciliter l'exploitation (ajout, mise à jour, recherche de données). Exemple : données concernant les livres d’une bibliothèque, le personnel d’une entreprise, les comptes clients dans une banque… Elles sont stockées sur des mémoires secondaires (disques) Notion apparue en 1964 (BDD informatisée).

Objectifs • Mémoriser des données • Structurées (livres, personnes, employés, disques…) • Documents (texte, images, films …) • Retrouver en ligne la bonne donnée au bon moment • Le salaire de Dupont, sa photo • L’avoir de votre compte en banque • Quels sont les livres que Gérard Bouchard a emprunté ? • Mettre à jour les données variant dans le temps • Volume de données de plus en plus grands • Giga, Tera, Péta bases (1015 octets) • Numériques, Textuelles, Multimédia (images, films,...) • De plus en plus de données archivées

Données • Les données sont structurées et identifiées • Données élémentaires ex: Votre salaire, Votre note de BDD • Données composées ex: Votre CV, vos résultats de l'année • Identifiant (n° Sécurité Sociale, n° Machine…) • De plus en plus de données faiblement structurées • Textes libres, documents audios, vidéos, images • Il faut aussi les stocker et les interroger Ces données sont stockées, créées, modifiées… via un Système de Gestion de Bases de Données (SGBD ; ang : database management system). Constitué de parties matérielles et logicielles. Permet la communication entre l’utilisateur et la base proprement dite.

Exemples de SGBD sur Internet E-commerce Moteur de recherche Ecoute de musique Films, cinéma Réservation de voyages Encyclopédie Système d’Information Géographique

Bases de Données Une BDD doit contenir toutes les données nécessaires à son organisation. Les données effectives et les programmes qui les gèrent ne sont pas dans le même ensemble logiciel. Une BDD doit toujours être conçue pour un public particulier et dans un but précis. L’accent est mis sur des données pertinentes concernant un ou plusieurs objets ou entités.

On ne sélectionne que des caractéristiques pertinentes pour l’utilisateur: attributs. Bases de Données Une BDD ne peut contenir que des informations partielles sur les entités. Exemple : Intéressons-nous à une entité « Compact Disc ». Un CD peut être caractérisé par :- nom du groupe/de l’artiste- nom de l’album- nombre de titres- durée- maison de disque- distributeur- style- année- société pressage CD- studio d’enregistrement- période d’enregistrement- nom de l’ingénieur du son- numéro du code-barres- etc.

Bases de Données Pour un même type d’entité, on choisira des attributs suivant les souhaits de l’utilisateur. • Ex : être humain : • Nom- Prénom- Date de naissance- Taille- Poids- Mensurations- Diplôme le plus élevé- Expérience professionnelle- Compétences en bases de données- Langages informatiques maîtrisés- Langues étrangères parlées- Connaissances en philosophie Gestion de personnelAgence de mannequins Gestion de personnelSociété d’informatique

Bases de Données • Les attributs peuvent avoir des valeurs différentes : • type : entier, chaîne de caractères, réel, booléen, date • Et des domaines de définition différents : de 2 à n valeurs possibles. • => Domaine des attributs Exemple : Nom : n’importe quelle chaîne de caractères (limitée à 30 lettres) Date de naissance 01/01/1940 < x < 01/01/1990 Diplôme : « Baccalauréat », « DUT », « BTS », « Licence », « Mastère », « Doctorat » Salaire : 1000 < y < 10000 Chef d’équipe : Oui, Non

Plusieurs types d’utilisateurs peuvent accéder à la BDD : Utilisateurs •  L’administrateur de la BDD • possède tous les droits (privilèges) sur la BDD = créer, modifier, supprimer, consulter… des données • peut donner et supprimer des droits aux autres utilisateurs •  Les utilisateurs finaux • ceux qui sont devant leur poste de travail, et qui vont émettre des requêtes pour extraire des informations (consulter des données) : • quels livres d’une bibliothèque parlent des bases de données ?- quels ingénieurs d’une société se sont déjà servis de Access ?- etc. • peuvent parfois modifier les données du système  Les programmeurs d’applications accèdent aux données grâce à des programmes qu’ils développent. Celles-ci permettent d’accéder aux données et de les modifier.

Système de Gestion de Bases de Données • Constitution : • Partie matérielle : système informatique sur lequel est installée la BDD. • Partie logicielle : programmes au niveau client (avec différentes possibilités d’afficher, modifier… suivant les droits utilisateurs) ou au niveau serveur (outil de gestion de données)

Systèmes de Gestion de Bases de Données Architecture client/serveur

Systèmes de Gestion de Bases de Données Trois rôles importants : • conserver les données – elles doivent rester précises, cohérentes et avec le minimum de redondance possible • fournir l’accès aux données – en lecture et en écriture, facilement et rapidement (et gérer le fait que plusieurs personnes peuvent en même temps accèder à la BDD). • maintenir la sécurité des données– niveaux d’autorisation ne donnent que certains droits aux utilisateurs, éviter les modifications simultanées d’une même donnée par plusieurs utilisateurs… (gérer la confidentialité) Avant de programmer, il faut savoir comment ces données vont s ’organiser, comment elles vont être interrogées…  Modélisation

Modèles de données Moyen « schématique » d’expliquer la disposition logique des données, et les relations entre les différentes parties qui les composent. • Objectifs • Permettre une meilleure compréhension • Le monde réel est trop complexe • Omission des détails • Permettre une conception progressive • Abstractions et raffinements successifs • Découpage en modules • Génération des structures de données (et de traitements) • Faciliter la visualisation du système • - Diagrammes avec notations simples et précises • - Compréhension visuelle Nous étudierons le modèle relationnel

Bases de Données Relationnelles Organisation d’une BDDR (ang. : Relational database) sous forme de table. Une ligne par élément, chaque colinne représentant un attribut. On travaille sur un modèle abstrait : on n’a aucune information sur l’implantation physique (concrète) des données. La partie logicielle du SGBD prend en charge cette implantation.

Quand on doit gérer plusieurs tables au sein d’une même BDD, on utilise un langage de définition de données (ang. Data Definition Language, DDL) qui permet de décrire les différents champs de chaque table. Bases de Données Relationnelles Remplissage/Mise à jour de la BDD : via un langage de manipulation de données (ang. Data Manipulation Language, DML).

Chaque table a un nom Lignes : Cardinalité (ici = 5) Colonnes : Attributs Domaine : valeurs possibles de l’attribut. Noté Dom(Nom), Dom(Prénom)… Exemple Dom(Nom) < 20 caractères ; 18 < Dom(Age) < 500 ; etc. Bases de Données Relationnelles FICHIER_CLIENTS On suppose que chaque case ne comporte qu’au plus une seule valeur.

Les entités regroupent des données (ce sont les propriétés) afin de former des types d'objets. Les associations définissent les relations ou interactions entre les entités (« nécessite », « est incluse dans », « est responsable de »…). Modèle Entité-Association Outil d’analyse permettant de construire des schémas théoriques de raisonnement, afin de montrer les relations qu’il existe entre les différentes entités, combien d’entités entrent en jeu avec une autre… Formalisé en 1976 (P. Chen). • Exemple, dans la gestion d’un emploi du temps : • entités « professeurs » • entités « enseignements » • entités « horaires » • Un enseignement nécessite un professeur (un et un seul). • Un enseignement nécessite 2 h (4 h…) à un moment dans l’emploi du temps. • Un professeur réalise au moins un enseignement. • etc.

Modèle Entité-Association Se jeter sur son écran et son clavier (comportement typique chez le développeur débutant) amènera à bien des déceptions voire au découragement. La méthode consiste à se poser une série de questions dans un ordre bien précis et à y répondre complètement avant de passer à l'étape suivante. Une étape bâclée obligera, au mieux, à reprendre toutes les étapes suivantes. D'où l'importance d'un travail bien préparé. Nous allons illustrer cette méthode par une étude de cas: Le plan de cette étude de cas est dérivé de la méthode MERISE.

Méthode MERISE Née en 1979 (appel d’offre du Ministère de l’Industrie français). Nombreuses évolutions. • Principes Fondateurs • La méthode MERISE est basée sur la séparation des données et des traitements à effectuer. Elle décompose l'analyse du système en plusieurs modèles conceptuels, logiques et physiques. • Etapes essentielles • Le MCC (Modèle Conceptuel de la Communication) • Le MCD (Modèle Conceptuel des Données) • Le MLD (Modèle Logique des Données) • Le MPD (Modèle Physique des Données) • Le MCT (Modèle Conceptuel des Traitements) • Le MOT (Modèle Organisationnel des Traitements)

Méthode MERISE : Le MCD Le MCD (Modèle Conceptuel des Données) A pour but d'écrire de façon formelle les données qui seront utilisées par l'application, d’où une représentation des données, facilement compréhensible, permettant de décrire l'application à l'aide d'entités. Le MCD fait appel à deux notions principales: les entités et les associations. Les entités regroupent des données (ce sont les propriétés) afin de former des types d'objets. Les associations définissent les relations ou interactions entre les entités. Cette étape est la plus importante. ll s'agit d'une représentation graphique qui permet de décrire toutes les relations existantes entre les entités.

La définition des données Méthode MERISE : Le MCD Réalisation d’une description exhaustive des informations à traiter dans l'application. Cette description doit être organisée et structurée sans redites. Exemple : Réaliser un BDD qui permette de gérer les séances de TP à l’IUT GEII de Toulon, en prenant en compte : enseignants responsables, matière, promotion et matériels. On suppose qu’il n’y a qu’une matière par série de TP, et qu’une série de TP ne peut concerner qu’une promotion. Une série de TP peut être constituée de plusieurs séances de TP, et une caractéristique peut être commune à plusieurs matériels.

La définition des données Méthode MERISE : Le MCD

Méthode MERISE : Le MCD La définition des données : Restructuration On restructure les données pour ne garder que les informations les plus « signifiantes » pour notre application (on supprime tout ce qui est « unique »).

Séries de TP Promo. Matière Libellé complet S1 II1P S1 – TP de langage C S1 EN1P S1 – TP d’electronique Matériels S2 ARS2P S2 – TP d’API Catégorie Type Libellé S3 ET3P S3 – TP d’électrotechnique PC PC-E200 PC – salle E200 Promotion Matière Numéro Intitulé C CALT Carte Altera DE2 Séances de TP Initiation au C S1 II1P 1 A A30A Ampèremètre de table Lang. C : les « if » S1 II1P 2 Lang. C : boucles S1 II1P 3 Initiation grafcet S2 ARS2P 1 TP Ascenseur S2 ARS2P 2 Caractéristiques Catégorie Type Val. A Continu 20 FPGA Nb_broches 672 FPGA Nb_elts_log 15000 Méthode MERISE : Le MCD

Entité Séances de TP Séries de TP attributs … … Exemple : (1,n) contient (0,1) Une série de TP contient 1 ou plusieurs séances de TP => (1,n). Une séance de TP est incluse dans au maximum une série de TP => (0,1). Représentation du modèle entité-association : Méthode MERISE : Le MCD cardinalité (min,max) association attribut

Une séance de TP est contenue dans 0 à 1 séries de TP Une série de TP contient 1 à n séances de TP Une caractéristique peut correspondre à 0 jusqu’à n matériels Un matériel possède 1 à n caractéristiques Une séance de TP nécessite 1 à n matériels Un matériel en stock est nécessaire pour 0 à n séances de TP Le MCD (Modèle Conceptuel des Données): Méthode MERISE : Le MCD Exemple : contient 1,n 0,1 Séances Séries ... ... 1,n possède nécessite 0,n 1,n 0,n Caractéristiques Matériels possèdent ... ...

Entité 1 Entité 2 propr1propr2... Associe propr1’propr2’... 1,1 1,1 Entité 1+2 propr1propr2… propr1’propr2’ Méthode MERISE : cas particulier • Relation binaire aux cardinalités (1,1) - (1,1) Dès le MCD, on peut fusionner les 2 entités en une seule, en regroupant leurs propriétés.

Méthode MERISE : Le MCDAssociations multiples Il peut y avoir plusieurs associations entre 2 entités si la sémantique est différente. Est propriétaire de 0,n 1,n Personne Appartement NomPrénomNuméro Sécu. Sociale NuméroEtage 0,n 0,n Est locataire de Une personne peut ne pas être propriétaire, ou posséder 1 à n appartements Un appartement est la propriété d ’au moins 1 personne

Etudiant Examen ……... Passe ……... 1,n 1,n Méthode MERISE :Les attributs d’association Il peut arriver qu’un paramètre ne puisse pas être associé à une seule entité, car il est en fait lié à 2 entités (ou plus). Exemple : une entité « Etudiant », une entité « Examen ». Où ranger la note ? • Pas dans l’entité « Etudiant » : cela signifierait que quelque soit l’examen passé, l’étudiant a une note qui sera toujours la même. • Pas dans l’entité « Examen » : cela signifierait que l’examen donnerait toujours lieu à la même note, quelque soit l’étudiant qui le passe. Alors ?

Etudiant Examen ……... Passe ……... 1,n 1,n Méthode MERISE :Les attributs d’association Solution : la note est à la fois liée à l’examen et à l’étudiant. On place donc la note dans l’association reliant « Etudiant » à « Examen » : attribut d’association Note

Personne (0,n) Chef ……... Commande (0,1) Subordonné Méthode MERISE : Relation réflexive Il peut arriver qu’une entité puisse être impliquée elle-même plusieurs fois dans une relation (boucle). Exemple : Une personne peut être le chef de 0 à n personnes, ou le subordonné de 0 à 1 personne. Mais ce subordonné peut lui même commander de 0 à n personnes. Et le chef peut lui même être commandé par un autre chef… Note : Les cardinalités minimales sont toujours égales à 0, sinon on aurait une boucle « sans fin ».

Le MLD (Modèle Logique des Données) Méthode MERISE : Le MLD • Permet de mettre en forme le MCD. • Le MLD fait appel à deux notions principales : • les tables • les relations Elles sont définies à partir de la modélisation « entité – association » établie lors de la phase précédente. Les tables sont la réalisation informatique des entités. Les relations définissent les liaisons entre tables ainsi que les règles d'intégrité des données inscrites dans les tables. Elles auront une influence considérable sur le fonctionnement du moteur de base de données. La conception du MLD consiste essentiellement à: Traduire les entités en Tables (et les propriétés en Champs) Traduire les associations en Relations (en portant attention à leur Cardinalité)

Méthode MERISE : Le MLD Le MLD (Modèle Logique des Données) : • Transformation des entités en tables • La transformation des entités en tables se fait trois étapes: • Transformation des propriétés en champs • Mise en évidence de l'index principal grâce à l'identifiant de l'entité • Création de la clef primaire de la table.

Nom de la table EtreHumain NumSecu entierNom char(30)Prénom char(20)DateNaiss dateLieuNaiss char(30)Sexe booléen types Nom des champs Méthode MERISE : Le MLD Le MLD (Modèle Logique des Données) : Transformation des propriétés en champ : On donne un nom de champ à chaque propriété. On prendra garde à donner des noms simples permettant de l'identifier clairement parmi tous les autres champs de la base. On doit donner à chaque champ un type qui permettra de stocker correctement les données: type texte dont on spécifie le nombre maximum de caractères; type numérique (entiers, flottants, etc.); type time (date, heure, etc.); etc.

Clé primaire EtreHumain NumSecu entier Nom char(30)Prénom texte(30)DateNaiss dateLieuNaiss texte(30)Sexe booléen Le MLD (Modèle Logique des Données) : Méthode MERISE : Le MLD On choisit dans l’ensemble des champs de l’entité un champ ou un n-uplet de champs, qui puisse différencier parfaitement une ligne d’une autre dans la table. On nomme ce champ (ou cet ensemble de champs) : clé primaire. Il est préférable que la clef primaire ne soit composée que d'un seul champ. Si plusieurs champs sont nécessités pour former la clé primaire de la table, il est recommandé de créer un champ supplémentairequi servira de clef primaire car ce type de combinaison de champs est difficile à maintenir.

Méthode MERISE : Le MLD Transformation des associations en relations • Suivant la cardinalités des relations entre entités dans le MCD, le MLD va pouvoir être construit directement via certaines règles. • Plusieurs cas peuvent se produire : • cas (X,n)-(X,n) • cas (X,1)-(X,n) • cas (0,1)-(1,1)

Entité 1 Entité 2 ……... Associe ……... X,n X,n Associer att1 entieratt1’ entieratt_asso entier MCD  MLD : précisions • Relation binaire aux cardinalités (X,n) - (X,n), X=0 ou X=1 att_asso Entite1 Entite2 att1 entier att2 char(30)att3 booléen att1’ entier att2’ timeatt3’ flottant On utilise une table de jointure ayant comme Clé Primaire une clé composée des identifiants des 2 entités. On dit que la Clé Primaire de la nouvelle table est la concaténation des Clés Primaires des deux autres tables. L’attribut d’association passe dans la table de jointure.

Entité 1 Entité 2 ……... Associe ……... att_asso X,1 X,n Entite2 att1’ entier att2’ timeatt3’ flottant MCD  MLD : précisions • Relation binaire aux cardinalités (X,1) - (X,n), X=0 ou X=1 Entite1 att1 entier att’1 entier att2 char(30)att3 booléenatt_asso entier La Clé Primaire de la table à la cardinalité (X,n) devient une Clé Etrangère dans la table à la cardinalité (X,1)  Va nous permettre de créer un lien entre les tables. L’attribut d’association passe dans la table à la cardinalité (X,1).

Entité 1 Entité 2 ……... Associe ……... 0,1 1,1 MCD  MLD : précisions • Relation binaire aux cardinalités (0,1) - (1,1) att_asso Entite1 Entite2 att1 entier att2 char(30)att3 booléenatt_asso entier att1’ entier att1 entieratt2’ timeatt3’ flottant La Clé Primaire de la table à la cardinalité (0,1) devient une Clé Etrangère dans la table à la cardinalité (1,1). L’attribut d’association passe indifféremment dans l’une ou l’autre des tables.

MCD  MLD : cas d’une relation réflexive • Relation binaire aux cardinalités (0,1) - (0,n) Un attribut clé étrangère est ajouté dans la nouvelle table, attribut éventuellement suffixé (renommé) par le rôle de la liaison à la cardinalité (0,1). Cette clé étrangère référence la clé primaire de la même table. Attention : pas applicable sous Access, obligation de créer 2 tables. • Relation binaire aux cardinalités (0,1) - (0,1) Même règle, sauf qu’il y a 2 clés étrangères. Il pourra y avoir possibilité plus tard d’éliminer l’une d’entre elles. • Relation binaire aux cardinalités (0,n) - (0,n) Création d’une 2ème table, dont la clé primaire est la concaténation de deux clés, référant à la clé primaire de la 1ère table. Ces attributs sont suffixés (renommés) par le rôle de chacune des liaisons correspondantes

MCD  MLD : cas d’une relation réflexive Exemple : relation (0,1) – (0,n) Personne NumPersonne…...NumChef Exemple : relation (0,n) – (0,n) Article Composer NumArticle…... NumArticleComposantNumArticleComposéDe

MLD : autre représentation Il arrive parfois que le MLD se représente alors de manière textuelle. Exemple : Entite1( att1, att2, att3) Entite2( att1’, att1, att2’, att3’) Note : le type des données ne figure pas. est équivalent à Entite1 Entite2 att1 entier att2 char(30)att3 booléen att1’ entier att1 entieratt2’ timeatt3’ flottant

Le modèle relationnel Introduction Dans le cadre d'un projet d'informatisation, la conception d'une base de données relationnelle passe d'abord par l'identification des objets de gestion (=entités) et des règles de gestion du domaine modélisé). Une fois énoncées et validées, ces règles nous conduisent automatiquement à la structure du modèle relationnel correspondant. Des opérations vont pouvoir être effectuées sur les instances des différentes tables, afin de procéder à la recherche d ’informations dans la base de données.

SQL • Définition • SQL (Structured Query Language - Langage de requêtes structuré) est un langage utilisé pour interroger ou manipuler les Bases de Données Relationnelles. C ’est : • un langage de définition de données (DDL), • un langage de manipulation de données (DML), • un langage de contrôle de données (DCL, Data Control Language). • Début du développement dans les années 70 (IBM). Première version commerciale en 1979. Dernière normalisation en 2003. • De nombreux SGBDR utilisent SQL : Access, MySQL, Oracle, DB2, SQL Server...

Iles Nom Superficie Population Mer-océan Groix 14,82 2300 Atlantique Corse 8680 260000 Méditerranée Ouessant 15,58 932 Atlantique Mayotte 374 200000 Indien Nom Population Tahiti 1043 170000 Pacifique Groix 2300 Corse 260000 Ouessant 932 Mayotte 200000 Tahiti 170000 Opérations de base Le modèle relationnel Projection Formalisme : R = PROJECTION (R1, liste des attributs). Cet opérateur ne porte que sur 1 table. Il permet de ne retenir que certains attributs spécifiés d'une table. On obtient toutes les lignes de la table à l'exception des doublons. R = PROJECTION (Iles, Nom, Population)

Champignons Espece Categorie Conditionnement Rosé des prés Conserve Bocal Rosé des prés Sec Verrine Coulemelle Frais Boîte Rosé des prés Sec Sachet plastique Espece Categorie Rosé des prés Conserve Rosé des prés Sec Coulemelle Frais Pas de doublon Opérations de base Le modèle relationnel Projection Formalisme : R = PROJECTION (R1, liste des attributs). R = PROJECTION (Champignons, Espèce, Catégorie)

Iles Nom Superficie Population Mer-océan Groix 14,82 2300 Atlantique Corse 8680 260000 Méditerranée Ouessant 15,58 932 Atlantique Mayotte 374 200000 Indien Nom Population Tahiti 1043 170000 Pacifique Groix 2300 Corse 260000 Ouessant 932 Mayotte 200000 Tahiti 170000 SQL • Instructions • SELECT : permet de récupérer des informations dans la BDD. • SELECT[DISTINCT, ...] liste_sélection [FROM...] [ WHERE condition_recherche ][GROUP BY expression] • [ORDER BY expression]; Exemple : SELECT Nom,Population FROM Iles;

Doublon Champignons Espece Categorie Conditionnement Rosé des prés Conserve Bocal Rosé des prés Sec Verrine Espece Categorie Coulemelle Frais Boîte Rosé des prés Conserve Rosé des prés Sec Sachet plastique Rosé des prés Sec Coulemelle Frais Rosé des prés Sec SELECT  Projection SELECT Nom, Population FROM Iles;n’est pas une vraie projection, car s’il y a des doublons dans la table d ’origine, ils apparaîtront dans la table résultante. SQL SELECT Espece, Categorie FROM Champignons; Pour faire une projection, on écrit donc : SELECT DISTINCT Espèce, Categorie FROM Champignons; (DISTINCT permet d ’éliminer les doublons).

Bases de données