1

1

Concevoir un système d’information

Des données à la base de données

Bernard.Andruccioli@u-bordeaux4.frhttp://andruccioli.u-bordeaux4.fr2009 - 2010

2

Bases données mal perçues…

• « Access est difficile »• « J’ai toujours fait avec Excel, cela suffit! »• « Access ne sert à rien »• « Les bases de données rendent difficile ce qui est simple »

• « Les commerciaux utilisent TOUS Excel »• « Dans les achats on n’utilise qu’Excel ».• « On n’arrive à rien faire avec Access »• « L’informatique doit faciliter la vie, pas la compliquer »

2

3

Mauvaise image BD

• Pas de connaissance des concepts de base des systèmes d’information liés aux BD

• Improvisation dans l’emploi des BD• Méthodologie de création de BD absente• Difficultés propres à Access (Vocabulaire, outils): Données, tables, champs, nature et taille des champs, clés primaires, état, requêtes, formulaires, modules, macro, Programmation!

• ACCESS: confusion données, outils, interface• ACCESS: client-serveur & Moteur-Interface• ACCESS: création / Utilisation d’une application• Confusion Table-BD-Fichier• => Différence d’utilisation / Word, Excel, PP

4

…Sauf quand cela nous arrange.

• Environnement composé de BD• Sites Web avec BD (Transports, commerce…)• Nos comptes bancaires• Identifiants au travail, au pôle, sur des sites.• Gestion perso (collections, …)• Application déjà prête…• Recherche dans l’historique de certaines opérations• Datamining, informatique décisionnelle, ERP• Interrogation de données distantes• Sauvegarde automatique des enregistrements• Ce qu’on attend de l’encadrement:=> Participation au développement de SI automatisés et d’applications informatiques qui en découlent (en local, sur Internet): Voilà la contribution de l’encadrement.

3

5

Limites des solutions bricolées

Doublons

Redondances

Saisies multiples

MAJ délicates

Risques d’erreurs

Aucune rigueur

6

Limites des solutions bricolées

Développements ultérieurs complexes:

� Nombre de locations de monospaces de marque Renault entre deux dates?

� CA moyen de location des modèles sportifs pour les clients qui n’en louent pas régulièrement?

� Certains clients ont été parrainés par d’autres. Comment gérer ceci?

� Certains clients souhaitent acheter des véhicules tout en les laissant dans le parc.

� Certains clients se rendent mutuellement des services que nous devons gérer.

� Que se passe-t-il si nous souhaitons vendre d’autres produits et services et proposer des factures?

4

7

Objectifs d’un système d’informations

• Eviter les doublons et redondances• Faciliter la saisie et les MAJ• Ouverture aux traitements ultérieurs• Réponse appropriée au problème posé dans sa globalité• Réflexion indépendante du matériel et des logiciels

possédés• Rigueur dans la conception, la mise en œuvre, le

portage et l’utilisation• Eléments de remise en question => Résistances aux

changements…• Nécessite une démarche rigoureuse comportant

plusieurs étapes

8

Etapes de la conception d’une BD

�Dictionnaire des données (DD)Lister l’ensemble des données, en précisant leur nature, leur taille.

�Graphe des dépendances fonctionnelles (GDF)

Représenter les relations entre les données.

�Modèle Conceptuel des Données (MCD)Représentation conceptuelle SI (Entités, Associations, attributs)

�Schéma relationnel (SR) ou Modèle Logique des Données (MLD)

Description des tables à créer (tables, champs, clés permettant les relations)

�Modèle Physique (MPD)Portage sur le logiciel utilisé

5

9

Règles de gestion

• Location de voitures.• Un client réside dans une seule ville et appartient à une seule catégorie.

• Un client peut louer une ou plusieurs voiture, y compris au même moment

• Une voiture appartient à une seule marque qui est commercialisée par un seul constructeur

• Une voiture appartient à un seul type (Berline, Monospace,…)

• Chaque client est parfaitement identifié, de même que les voitures et les locations

10

Dictionnaire des données

• Récolte exhaustive d’informations• Etape ingrate, longue, faisant intervenir différents acteurs, analyse et étude de documents

• Matière première du futur SI• Tri dans toutes ces données• Préciser leurs caractéristiques• Eléments calculés ou paramètres (Ex: taux TVA) à préciser.

6

11

Dictionnaire des données

Liste des propriétés

Nom des données Caractéristiques Nom des données Caractéristiques

num_client entier long auto incrémenté code_postal entier long auto incrémenténom_raison_sociale texte 50 caractères ville texte 40 caractèresprenom texte 30 caractèresadresse texte 50 caractèrestelephone texte 10 caractères id_categorie entier long auto incrémentéfax texte 10 caractères categorie texte 15 caractèresmail texte 80 caractères commentaire textesolde numérique réel

immatriculation texte 12 caractèresmodele texte 15 caractères id_marque entier long auto incrémentédate_mise_circulation date marque texte 15 caractèresmoteur texte 12 caractèrescouleur texte 12 caractères id_contructeur entier long auto incrémentéprix_neuf numérique réel constructeur texte 15 caractèresprix_location numérique réelcomment_auto texte Taux TVA paramètre

montants HT Calculésid_type entier long auto incrémenté montants TTC Calculéstype_auto texte 15 caractères montants TVA Calculés

Net à payer Calculés

date_emprunt datedate_reservation datedate_retour datearrhes numérique réel

12

Choix des identifiants

• Identifiant (ou attribut): propriété qui permet d’identifier formellement et sans aucune ambiguïté chaque occurrence, donc chaque voiture, chaque client

• Chaque identifiant permet donc de connaître le client concerné ou la voiture identifiée.

• => Dépendance fonctionnelle entre ces identifiants et certains propriétés.

7

13

Choix des identifiants

Convention d’écriture: identifiant

14

Graphe des dépendances fonctionnelles

Non concernés par la suite.

Ne découlent pas d’une dépendance fonctionnelle

8

15

Modèle Conceptuel des Données

MCD: représentation du système d’information en utilisant deux objets/concepts: Entités et Associations.

�Entité : ensemble cohérent d’informationsUn client, une voiture, un truc, un machin,…

�Association : liens, relations entre ces entitésAppartenir, posséder, louer,…

16

Modèle Conceptuel des Données

Convention de représentation

Cardinalités: voir plus loin.

Nom de l’entité

Identifiant

Autres propriétés

9

17

Les entités découlent du GDF Nom des données Nom des données

num_client code_postalnom_raison_sociale villeprenomadressetelephone id_categoriefax categoriemail commentairesolde

immatriculationmodele id_marquedate_mise_circulation marquemoteurcouleur id_contructeurprix_neuf constructeur

prix_locationcomment_auto

id_typetype_auto

date_empruntdate_reservation

date_retourarrhes

CLIENT

VOITURE

TYPE

VILLE

CATEGORIE

MARQUE

CONSTRUCTEUR

18

MCD: placement des entités

10

19

Cardinalités: précisions dans la nature des relations entre entités

• Les cardinalités expriment le nombre de valeurs minimum et maximum que l’entité peut prendre dans l’association. Les cardinalités font partie des associations.

Cardinalités Traduction Exemple

0,1 Aucun ou un Un enfant n'a pas de père, ou il n'a qu'un père

1,1 Un et un seulType époux-épouse: un époux n'a qu'une épouse (légale) et une épouse n'a qu'un époux (légal)

0,n Aucun ou plusieursUne personne peut n'avoir aucune voiture, mais peut en avoir plusieurs.Un couple peut avoir plusieurs enfants.Un enfant n'a qu'une mère, ou qu'un père.Un enfant a plusieurs loisirsUn loisir est pratiqué par plusieurs enfants

1,n

n,n

Un ou plusieurs

Plusieurs à plusieurs

20

CardinalitésLes cardinalités découlent directement des règles et procédures de fonctionnement en vigueur dans l’organisation, et ont donc avoir par la suite des incidences dans la conception des modèles logiques et physiques des données.

1,n1,1ETUDIANT FORMATION

INSCRIT

1,n1,nETUDIANT FORMATION

INSCRIT

Un étudiant est inscrit dans une seule formation (1,1) (IUT)

Un étudiant est inscrit dans une ou plusieurs formations (1,n). (Université)

11

21

MCD

22

Quel logiciel pour créer des MCD?DB-Main disponible gratuitement à cette adresse: http://www.db-main.be/Avantages :Petit, gratuit , portable (pas d’installation), mode texte, performant.Inconvénients : Mode texte, ergonomie, notations.

12

23

Quel logiciel pour créer des MCD?WinDesign: Logiciel CommercialAvantages : Performant, complet, formats d’export, qualité des schémas..Inconvénients : Cher, assez lourd..

24

Quel logiciel pour créer des MCD?Analyse SI: Logiciel libre et gratuit disponible http://analysesi.free.frAvantages : Simple, relativement complet, qualité des schémas, respect de la démarche..Inconvénients : Plus développé

13

25

Quel logiciel pour créer des MCD?Open Modelsphere: Logiciel libre et gratuit Avantages : Performant, complet, qualité graphisme, exportsInconvénients : Complexe, prise en main délicate

26

ExerciceVous êtes responsable d’une bibliothèque universitaire, et décidez de structurer les services de consultation et d’emprunt des ouvrages . Ceux-ci sont placé s dans des rayons par centres d’intérêt (Economie, Histoire, Géographie, Finances,…). Ils sont écrits par un ou plusieurs auteurs , et édités dans une collection par un éditeur . Une collection appartient à un seul éditeur . Il arrive qu’un titre soit (ré)-édité par plusieurs éditeurs différents dans de s collections différentes(C’est souvent le cas pour les classiques de la littérature par exemple). Un même titre peut être possédé en plusieurs exemplaires par la bibliothèque si celui-ci est très utilisé par les adhérents.Un étudiant est inscrit dans une seule filière universitaire pour une année universitaire (DUT, DEUG, MSG,…).Il faut être inscrit avec son nom et son numéro de téléphone pour pouvoir fréquenter la bibliothèque. On est alors autorisé à consulter l’ensemble des ouvrages par auteur, titre de document et centre d’intérêt. L’emprunt, de un à cinq ouvrages, peut se faire pour une durée de 15 jours maximum.Sachant cela :1°) Quels sont les domaines de gestion concernés par cette application ?2°) Concevoir le Modèle Conceptuel des Données (DD => GDF =>MCD )

14

27

Du MCD au MLD (ou SR)

• MCD: étape conceptuelle

• MLD (SR): Plus concret: tables, clés, relations

MCD MLD ou SREntité Table

Propriété de l’entité Colonne ou attribut de la table

Identifiant de l’entité Clé primaire de la tableRelations (0,n ou 1,n)plusieurs à plusieurs (0,n ou 1,n)

Table

Relation binaire (0,1 ou 1,1) à plusieurs (0,n ou 1,n)

Exportation clé étrangère et attributs.

MCD MLD ou SREntité Table

Propriété de l’entité Colonne ou attribut de la table

Identifiant de l’entité Clé primaire de la tableRelations (0,n ou 1,n)plusieurs à plusieurs (0,n ou 1,n)

Table

Relation binaire (0,1 ou 1,1) à plusieurs (0,n ou 1,n)

Exportation clé étrangère et attributs.

28

Du MCD au MLD (ou SR)Traitement des entités :

� Chaque entité devient une table

� Chaque propriété devient une colonne (un champ) de cette table

� L’identifiant devient la clé primaire de cette table.

MCD

VILLE (code_postal , ville)

Schéma Relationnel Modèle physique (Access)

15

29

Du MCD au MLD (ou SR)Traitement des associations 1,1 – 1,n ou 0,n :

� Ajout d’une clé primaire dans la table du côté 1,1 de l’association

� Rien à faire dans l’autre table.

MCD

Schéma Relationnel

Modèle pysique (Access)

CLIENT (num_client, nom_raison_sociale, …,solde, #code_postal)

MCD

30

Du MCD au MLD (ou SR)Traitement des associations 1,n ou 0,n – 1,n ou 0,n :

� L’association se traduit par la création d’une table

� Les clés primaires des tables en relation sont (le plus souvent) ensemble clés primaires de cette nouvelle table.

� Certaines propriétés (porteuses de sens) de l’association peuvent aussi intégrer la clé primaire

MCD

MLD

16

31

Du MCD au MLD (ou SR)

LOUER ( #immatriculation, #num_client, date_emprunt , date_retour, date_reservation, arrhes)

Schéma Relationnel

Modèle pysique (Access)

Traitement des associations 1,n ou 0,n – 1,n ou 0,n :

Convention: TABLE (clé primaire , champ_1, …, champ_n, #clé étrangère)

32

MLD du cas

MLD

17

33

Schéma relationnel du cas• VILLE( code_postal , ville)

• CATEGORIE(id_categorie , categorie, commentaire)

• TYPE(id_type , type_auto)

• CONSTRUCTEUR(id_constructeur , constructeur, pays)

• MARQUE(id_marque , marque, #id_constructeur)

• VOITURE(immatriculation , modele,annee_mise_circulation, moteur, couleur, prix_location, prix_neuf, comment_auto, #id_marque, #id_type)

• CLIENT(num_client , nom_raison_sociale, prenom, adresse, telephone, fax, email, solde, #code_postal, #id_categorie)

• LOUER(#immatriculation, #num_client, date_emprunt , date_retour, date_reservation, arrhes)

34

Vocabulaire et concepts

MCD• Entité• Attributs• Propriétés• Identifiants• Associations• Cardinalités• Occurences

MLD (SR)• Tables• Champs• Colonnes• Clés primaires• Clés étrangères• Enregistrements

(tuples)• Relations

18

35

Associations particulièresUne association peut relier plus de deux entités. L’association peut donc avoir plus de deux pattes.

LOUER permet une association entre VOITURE, CLIENT et TARIF

36

Associations particulièresUne association peut relier plus de deux entités. L’association peut donc avoir plus de deux pattes.

D’où ce MLD

Et ce Schéma Relationnel:

LOUER (#immatriculation, #num_client, #code_tarif, date_emprunt, date_reservation, date_retour, arrhes)

19

37

Associations particulièresEntité en relation avec elle-même.

Association d’une entité avec elle-même: Associations réflexives

Cas 1,1 – 1,n. Exemple: un client est parrainé par un seul autre client plus ancien, qui peut en parrainer plusieurs.

MCD

Schéma Relationnel

CLIENT (num_client, nom_raison_sociale, prenom, adresse, telephone, fax, email, solde, #num_parrain)

Traitement particulier sous Access et en SQL de manière générale.

38

Associations particulièresEntité en relation avec elle-même.

Association d’une entité avec elle-même: Associations réflexives

Cas 1,n – 1,n. Exemple: un client est en affaires avec aucun, un ou plusieurs autres clients.

MCD

Schéma Relationnel

PARRAINER(#num_client_1, #num_client_2)

Traitement particulier sous Access et en SQL de manière générale.

Une date ou d’autres propriétés peuvent être ajoutée dans l’association, ce qui se traduira dans la table par une ou plusieurs autres clés primaires.

20

39

Associations particulièresPlusieurs associations peuvent réunir deux entités.

Aucun problème pour le Schéma Relationnel:

MCD

40

Associations particulièresPlusieurs associations peuvent réunir deux entités.

Aucun problème pour le MLD et le Schéma Relationnel: La table acheter s’obtient de la même manière que la table LOUER

MLD

21

41

Les Formes Normales (FN)

La normalisation permet :• D’éliminer les données redondantes• Vérifier que les données sont associées à

l’entité ou l’association qui convient• Eviter les valeurs nulles• Rendre la base de données cohérente et

robuste.=> Vérifier plusieurs Formes Normales

42

Les Formes Normales (FN)Relation(attribut_1 , attribut_2, attribut_3,…)

Relation: table, entité

Dépendance fonctionnelle entre deux attributs A et B d ’une relation R si à toute valeur de A on ne peut associer à tout instant qu ’une et une seule valeur de B. Principe utilisé dans le Graphe des Dépendances Fonctionnelles.

Dépendance fonctionnelle: L’immatriculation d’une voiture permet de connaître le modèle, la motorisation, …, sans ambiguité.

22

43

Première Forme Normale (1FN)

immatriculation Voiture moteur couleur prix1114SW33 C4, Citoën Diesel Jaune 1251235SX33 807, Peugeot Diesel Bleu 1901325SR33 Coupé, Hyundaï Essence Noir 2002341SR33 Clio, Renault Essence Rouge 1054231SZ33 Avensis, Toyota Diesel Gris 1504232SZ33 Avensis, Renault Essence Blanc 155

Une relation est 1FN si:- elle possède une clé- tous ses attributs sont non atomiques (attribut atomique, n ’ayant à un instant donné qu’une seule valeur, ou ne regroupe pas un ensemble de valeurs)

44

Deuxième Forme Normale (2FN)Une relation est en 2FN si:- elle est 1FN- toutes les DF sont élémentaires

=> Une table CLIENT et une table AUTOMOBILE…

23

45

Troisième Forme Normale (3FN)Une relation est 3FN si- elle est 2FN- toutes les DF sont directes

=> Une table CLIENT et une table AUTOMOBILE…en plus de la table LOUER!