Informatique Bases de données et SGBD ISFATES – L1 : UE Informatique1 Par Julien Brancher UFR MIM Université Paul Verlaine – METZ Version sept. 2006

InformatiqueBases de données et SGBD

ISFATES – L1 : UE Informatique1

Par Julien Brancher

UFR MIM Université Paul Verlaine – METZ

Version sept. 2006

Introduction Modélisation Exemples Langage SQL Requêtes SQL

Bases de données et SGBDPLAN DU COURS

Introduction aux bases de données - SGBD La modélisation de données

Les tables Les cardinalités Les associations

Exemples et exercice simple Les transactions SQL

Principe et syntaxe générale Création d’objets Commandes de manipulations SQL Procédés de filtrage et de triage

Exemple de requêtes SQL

Les enregistrementsLa clé primaireLes clés étrangères


Bases de données et SGBDIntroduction aux bases de données

Coeur du système d'information des entreprises

Mainframes ordinateurs personnels

BD = grande quantité de données, centralisées ou non, servant pour les besoins d'une ou plusieurs applications, interrogeables et modifiables par un groupe d'utilisateurs travaillant en parallèle

Système de Gestion de Bases de Données (SGBD) = logiciel Interface entre la base de données et les utilisateurs/programmes

Les limites à l'utilisation des fichiers Connaître l'organisation des fichiers (séquentielle, indexée, ...) Ecriture des programmes pour manipuler ces informations Rigidité et

long et coûteux à mettre en œuvre Risque de création de fichiers qui contiennent des informations déjà

présentes (redondance des données) Peu fiables : risque d’utiliser les mêmes fichiers

Introduction aux bases de données



Définitions Collection de données inter-reliées de façon structurée et

cohérente

Système de Gestion de Bases de Données (SGBD) : • Ensemble de programmes qui permettent à des utilisateurs de créer et

maintenir une base de données :– Définition d'une base de données (spécification des types de données à

stocker),– Construction d'une base de données (stockage des données)– Manipulation des données (principalement ajouter, supprimer, retrouver des

données)

• Oracle, Sybase, Informix, DB2, MySQL, Access, InterBase, OooBase

Le SGBD sépare la partie description des données, des données elles mêmes. Cette description est stockée dans un dictionnaire de données et peut être consultée par les utilisateurs.



Pourquoi le modèle relationnel ? Ensemble de concepts permettant de décrire la

structure d'une base de données• Opérations de mise à jour• Opérations de questionnement

Modèle de données le plus utilisé Schéma de base de données = description des

données à gérer• Phase de spécification• Peu évolutif



Fonctionnalités des SGBD Description/récupération des données à l’aide de langages (SQL) Contrôler/supprimer la redondance d'informations

• Coût en temps• Coût en volume• Risque d'incohérence

Partage des données• Accès à la même information par plusieurs utilisateurs en même temps• Mécanisme de contrôle de la concurrence basé sur des techniques de

verrouillage des données Gérer les autorisations d'accès

• BD multiutilisateurs confidentialité des données• Droits de lecture, mise à jour, création, ...

Représenter des relations complexes entre les données Vérifier les contraintes d'intégrité Assurer la sécurité et la reprise après panne

• Pannes disques : mécanisme de journalisation qui permet de régénérer une base de données automatiquement à partir d'une version de sauvegarde et du journal des mouvements.


Bases de données et SGBDLa modélisationde données

C’est quoi la modélisation ? Un « modèle » est la représentation mentale d’un être

du monde réel et de son fonctionnement : quand on dispose d’un modèle, on peut simuler mentalement le comportement de cet être.

La modélisation, ce n’est donc rien d’autre que la pensée organisée en vue d’une finalité pratique modèle est synonyme de théorie, mais avec une connotation pratique : un modèle, c’est une théorie orientée vers l’action qu’elle doit servir.

Autrement dit, modéliser un objet c’est définir :

• Les concepts qui permettent de le décrire• Les relations fonctionnelles qu’entretiennent ces concepts.

La modélisationde données



Vocabulaire indispensable : Base de données : ensemble des tables Table (ou entité) : se compose d'enregistrements. Chaque

enregistrement regroupe un certain nombre de champs

Nom

Attribut1

Attribut2

Attribut3

…

Nom de la table

Liste des attributs de cette table (caractéristiques)



Exemple A : Un directeur de garage de voitures désire une base de

données pour la gestion de ses clients et de leurs voitures.

Voiture

N° d’immatriculation

Marque

Gamme

Modèle

Puissance

Place

Client

Modélisation de l’objet « voiture »

Caractéristiques amenées à être manipuler dans la base



Exemple A : Attribut : (ou champ) caractéristique de l’objet Clef primaire : champ identifiant d’une table (un ou plusieurs)

Nom

Attribut1

Attribut2

Attribut3

…

Clé primaire

Voiture


Marque

Gamme

Modèle

Puissance

Places

Client



Vocabulaire indispensable : Enregistrements : présente toute l'information concernant un

"membre " unique d'une table

AttributA AttributB AttributC … AttributX

Enregistrement1 valeur1A valeur1B valeur1C … valeur1X



…



Exemple B : Il y a 5 voitures dans le garage :

N° d’imma-triculation

Marque Gamme Modèle Puissance Places Client

1 4561XU57 Renault Mégane 1.9 Diesel RN

115 5 Honoré DUPUIS

2 654WT54 Peugeot 407 Coupé 140 5 Pauline RAVAL

3 5021AAA75 Jeep Wrangler - 150 4 Mélissa BARTH

4 9812XY57 Toyota Celica 1.8l 110 5 Simon LAVANDIER

5 7743OP57 Peugeot 206 XS 90 4 Pauline RAVAL

6

7

Redondance d’informations



Exemple A (corrigé) : Redondance des données : un client peut avoir plusieurs voitures!! Séparer les données du client aux données de la voiture Pas assez d’informations sur le client :

• Nom

• Prénom

• Adresse postale

• E-mail, … Problème d’homonymes :

• Identification UNIQUE d’un client

• Si plusieurs clients ont le même nom

• Si plusieurs clients ont le même nom et prénom, …

• Référence unique du client



Exemple A (corrigé) : Un directeur de garage de voitures désire une base de

données pour la gestion de ses clients et de leurs voitures.

Voiture


Marque

Gamme

Modèle

Puissance

Place

Client

Modélisation de l’objet « voiture »

Caractéristiques amenées à être manipuler dans la base



Exemple C : Création d’une 2ème table :

Voiture


Marque

Gamme

Modèle

Puissance

Place

Client

Référence client

Nom

Prénom

Adresse

E-Mail Lien ?



Exemple C : Clé étrangère (ou clé secondaire) : utilisée dans une table pour

représenter la valeur de la clé primaire dans une table connexe

Voiture


Marque

Gamme

Modèle

Puissance

Place

Référence client*

Client

Référence client

Nom

Prénom

Adresse

E-Mail

possède



Exemple C : Il y a 4 clients du garage :Réf.

clientNom Prénom Adresse E-mail

1 C0001 DUPUIS Honoré 10 rue … 57000 METZ

[email protected]

2 C0002 RAVAL Pauline 15 bd …

54000 NANCY

[email protected]

3 C0003 BARTH Mélissa 117 avenue …

57000 METZ

[email protected]

4 C0004 LAVANDIER Simon 3 rue …

57000 METZ

[email protected]

5



Exemple C : Il y a 5 voitures dans le garage :


Marque Gamme Modèle Puissance Places Réf. Client


115 5 C0001

2 654WT54 Peugeot 407 Coupé 140 5 C0002

3 5021AAA75 Jeep Wrangler - 150 4 C0003

4 9812XY57 Toyota Celica 1.8l 110 5 C0004

5 7743OP57 Peugeot 206 XS 90 4 C0002

6

7

Référence au client



Exemple D : Association : relie de 2 à n entités (tables) Cardinalité : Nombre (maximum, minimum) d'entités

concernées par une relation Les cardinalités dans l’exemple précédent :



Exemple D : Une voiture appartient au moins un client mais à un seul Un client a au moins une voiture mais peut en avoir une infinité (n)

Voiture


Marque

Gamme

Modèle

Puissance

Place

Référence client*

Client

Référence client

Nom

Prénom

Adresse

E-Mail

possède

1 1,

1 n,



Exemple E : Ajoutons une entité « Mécano » afin de garder une trace des

mécaniciens qui ont réparé telle ou telle voiture :

Mécano

Référence employé

Nom

Prénom

Adresse

Ancienneté

Age

Salaire

Voiture


Marque

Gamme

Modèle

Puissance

Place

Référence client*

a réparé


Marque

Gamme

Modèle

Puissance

Place

Référence client*

Référence employé*



Exemple E : Il y a 3 employés dans le garage :

Réf. employé

Nom Prénom Adresse Ancienneté Age Salaire

1 M0001 SAHNOUNE Bertrand 10 rue … 57000 METZ

12 34 10512

2 M0002 MICHEL Barnabé 15 bd …

54000 NANCY

2 27 7905

3 M0003 KACED Youssouf 117 avenue …

57000 METZ

25 50 13420

4

5



Exemple E : Cas où les mécaniciens ont réparé les mêmes voitures


Marque Gamme Modèle Puissance Places Réf. Client

Réf. mécanos


115 5 C0001 M0001


115 5 C0001 M0002

3 654WT54 Peugeot 407 Coupé 140 5 C0002 M0002

4 5021AAA75 Jeep Wrangler - 150 4 C0003 M0001

5 9812XY57 Toyota Celica 1.8l 110 5 C0004 M0002

6 9812XY57 Toyota Celica 1.8l 110 5 C0004 M0003

7 7743OP57 Peugeot 206 XS 90 4 C0002 M0003

Redondance d’informations



Exemple E : Les cardinalités permettent de déceler un tel problème :

Mécano


Nom

Prénom

Adresse

Ancienneté

Age

Salaire

Voiture


Marque

Gamme

Modèle

Puissance

Place

Référence client*

a réparé


Marque

Gamme

Modèle

Puissance

Place

Référence client*


0 n,

0 n,



Exemple E : Nouvelle entité : « Intervention »

MécanoVoiture

Intervention

N° Intervention


N° d’immatriculation*

Date_reparation


Nom

Prénom

Adresse

Ancienneté

Age

Salaire


Marque

Gamme

Modèle

Puissance

Place

Référence client*



Exemple E : Cette 3ème table fera le lien entre la voiture réparée et

le mécanicien concerné.N° Intervention N° d’immatriculation Réf. employé Date

1 REP0001 4561XU57 M0001 22-10-2005

2 REP0002 4561XU57 M0002 15-11-2005

3 REP0003 654WT54 M0002 10-12-2005

4 REP0004 5021AAA75 M0001 13-01-2006

5 REP0005 9812XY57 M0002 27-01-2006

6 REP0006 9812XY57 M0003 07-03-2006

7 REP0007 7743OP57 M0003 12-04-2006



Un exercice simple : Une bibliothèque désire informatiser sa gestion d’ouvrages

Chaque auteur a• Un numéro unique que la bibliothèque lui attribue• Le nom et le prénom

La bibliothèque attribue également• Un numéro unique a ses ouvrages (ISBN) ainsi que• Le titre• La date de parution doit également être conservée

Cas n°1 : un auteur peut écrire plusieurs ouvrages mais un ouvrage est écrit par un seul auteur.

Cas n°2 : un auteur peut écrire plusieurs ouvrages et un ouvrage peut être écrit par plusieurs co-auteurs.



Correction : CAS N°1 : schéma E/A – Entité/Association

OuvrageAuteur

Référence ISBN

Titre

Date de parution

Référence auteur*

Référence auteur

Nom

Prénom

a écrit

0 n,

1 1,



Correction : (schéma relationnel)

CAS N°2 : Association « a écrit » Entité « Co-auteur »

Ouvrage

Auteur

Référence ISBN

Titre

Date de parution

Référence auteur*

Référence auteur

Nom

PrénomCo-auteur

Référence auteur*

Référence ISBN*



Exercice : un vidéo-club désire informatiser la gestion de ses films.

Les films sont dotés d’un numéro unique, fourni par la société ainsi que d’un titre et de la durée du film

Un film est soit sous format DVD, soit ou sous format VHS Le gérant du vidéo-club n’achète jamais qu’un seul exemplaire par

film Le prix de la location d’un DVD est toujours de 4 euros et celui d’un

VHS est de 2 euros Une personne est soit acteur, soit client A chaque personne, on attribue un numéro unique On conserve également dans la base de données ces données du

client : nom, prénom, adresse, numéro de téléphone et adresse e-mail

Un acteur peut jouer dans plusieurs films, et un film peut comporter plusieurs acteurs

Une commande du client concerne un film à la fois avec la date conservée.



Correction : Schéma relationnel

Film

Référence film

Titre

Durée

Acteur

Numéro acteur

Nom

Prénom

Employé

Référence film*

Numéro acteur*

ClientNuméro clientNomPrénomAdresseNuméro de telE-mail

Commande

Référence film*

Numéro client*

Format

Date


Bases de données et SGBDLes transactions SQL

Qu'appelle-t-on SQL? Structured Query Language Langage de requêtes structuré Modèle relationnel :

• Langage de définition de données (LDD, DDL Data Definition Language) : créer des tables dans une base de données relationnelle, ainsi que d'en modifier ou en supprimer.

• Langage de manipulation de données (LMD, DML, Data Manipulation Language) : sélectionner, insérer, modifier ou supprimer des données dans une table d'une base de données relationnelle.

• Langage de contrôle de données (LCD, DCL, Data Control Language) : permissions au niveau des utilisateurs d'une base de données.

Historique :• Standard SQL, normalisé en 1986 SQL/86• 1989 Version SQL/89• 1992 Norme SQL/92 a désormais pour nom SQL2

Les transactions SQL


Bases de données et SGBDLes transactions SQL

Bientôt disponible

Documents

Informatique Bases de données et SGBD ISFATES – L1 : UE Informatique1 Par Julien Brancher UFR MIM Université Paul Verlaine – METZ Version sept. 2006