L'apparition de l'outil informatique a permis la mémorisation des informations sur un support informatique et à faciliter leur exploitation notamment grâce à de nouveaux outils logiciels, à la fois simple d'utilisation et efficace, les Systèmes de Gestion de Bases de Données Relationnelles (SGBDR). Exemple : Access, …

Un SGBDR est un logiciel qui permet de créer et d’exploiter (manipuler une base de données) est un ensemble structuré et cohérent d'informations stockées sur un support informatique et pouvant être utilisé par toutes les personnes autorisées.

Plus précisément les bases de données offrent les avantages suivants :

Toutes les informations de l'entreprise sont regroupées dans une seule base et organisés de manière à :

-- Minimiser la saisie.

-- Eviter les incohérences.

L’utilisation (= manipulation) d'une base de données est assez simple, elle n’exige pas de connaissance pointue en Informatique (langage d'interrogation appelée langage de requête).
On peut définir des autorisations d'accès aux utilisateurs.

b) La mise en place une politique de sécurité

De par sa nature stratégique, l'information stockée sur un support informatique doit être protégée contre le vol, la destruction ou l'endommagement.

Une politique de sécurité doit être mise en place :

-- Protection des locaux (porte à accès sécurisé, …).

-- Protection contre les pannes d'électricité (onduleurs, groupes électrogènes, …).

-- Protection d'accès aux informations (accès au système par mot de passe, …).

-- Protection contre le virus (antivirus, …).

-- Protection contre les pertes (sauvegarde régulière des données sur support de stockage).

c) Les contraintes légales liées aux stocks d'informations sur support informatique

La création d'une base de données dans une entreprise est soumise à des contraintes liées à la nature des informations qu'elle renferme et à l'utilisation qu’en est faite. En particulier les conditions de stockage d'informations nominatives (informations désignant directement ou indirectement une personne physique : nom, numéro matricule,...) pour des supports conformes à la loi « informatique et liberté » votée le 6 janvier 1973.

Cette loi protège les personnes contre l'usage d'informations d'ordre privé.

Exemple : religion, habitudes sexuelles, ...

En France, afin d'éviter les échanges d'informations nominatives entre eux organisation, il est interdit d'utiliser le numéro d'INSEE d’une personne pour l’identifier.

Autorité = la CNIL (Commission Nationale de l'Information et Liberté).

B. L'analyser la représentation des informations

La création d'une base de données repose sur une démarche d'analyse qui va permettre d'organiser les données de manière à obtenir une représentation de la réalité le plus fidèle possible.

Cette démarche consiste à faire :

-- L’inventaire des informations pertinentes du système étudié.

-- Structurer les données (= organiser).

C. La méthode MERISE

La méthode la plus utilisée de représentation des données est la méthode Merise ou méthode Entité-Association. Cette méthode met en jeu différent niveaux d'abstraction. A chaque niveau correspond un modèle de représentation des données et des contraintes (règles) auxquels elles sont soumises.

1. Au niveau conceptuel

Constitution d'un dictionnaire de données : liste toutes les données utiles ainsi que les caractéristiques (codage, type, ...) ainsi que les règles de gestion.

Réalisation du modèle conceptuel de données (MCD) : schéma graphique représentant de manière ordonnée les données du dictionnaire de données.

2. Niveau logique (ou organisationnel) :

Réalisation du modèle logique de données (MLD) ou modèle relationnel (MR).

C’est la traduction du MCD en un schéma en ligne en vue de créer la base de données sous un SGBDR.

Exemple : CLIENT (NumClient, NomClient, AdresseClient)

COMMANDE (NumCom, DateCom, MontantCom)

PRODUIT (RefProd, DésignationProd, PrixUnitProd)

CONTENIR (NumCom#, RefProd#, QtitéProd)

3. Niveau physique (ou opérationnel)

Réalisation physique des données, c’est l’implantation concrète de la base de données sur ordinateur.

II Le dictionnaire des données

A. L’inventaire exhaustif des données

Pour informatiser la gestion d'une activité, l'entreprise a besoin de mémoriser certaines informations dans son système d'informations : celles qui sont utiles aux traitements

Pour repérer les données qu'il va falloir mémoriser, l'entreprise fait l'inventaire des informations

présentes sur les documents qui circulent dans l'entreprise (internes, entrants ou sortants)

relevées lors d'entretiens avec les décideurs, les utilisateurs et autres acteurs internes de l'entreprise

Exercice 1 :

La société VIDEOPLUS est spécialisée dans le prêt de cassettes vidéo. Elle gère environ 600 cassettes et possède environ 2500 clients.

Elle désire informatiser la gestion des prêts de cassettes. Pour cela, Monsieur RENARD, le gérant de la société vous fait un exposé sur sa manière de gérer à travers quelques exemples.

Le prêt identifié par le numéro 1132 a été enregistré le 25/04/98 et a été souscrit pour un montant dé 60,OOF par un client identifié chez VIDEOPLUS par le numéro 48. Ce même client a d'ailleurs un autre prêt en cours identifié parle numéro 1127.

Ce client se nomme Isabelle GARNACHE et habité rué dé la Madonne 59000 LILLE. Pour réaliser cet emprunt il a présenté une carte d'identité (CI) portant lé numéro FR56783

Lé prêt n°1132 concerné 2 cassettes

- la cassette identifiée chez VIDEOPLUS par le numéro 934 sur laquelle est enregistré le film identifié chez VIDEOPLUS par le numéro 9 dont lé titré est « Mourir d'aimer » et classé dans lé genre « Drame psychologique »

- la cassette identifiée chez VIDEOPLUS par le numéro 1123 sur laquelle est enregistré le film identifié chez VIDEOPLUS par le numéro 10 dont le titre est « Le train sifflera trois fois » et classé dans le genre « Western »

Le film numéro 9 a proposé au tarif identifié par la couleur jaune, qui est de 25,OOF. Le film numéro 10 est proposé au tarif identifié par la couleur rouge qui est de 35,o0F. Le film numéro 4, du genre Western comme le film numéro 10, est proposé au tarif jaune.

Pour dés raisons commerciales, le tarif d'un film ne peut dépasser 50,OOF

Le film numéro 5 est enregistré sur les cassettes numéro 500 et 501. Ces cassettes sont actuellement disponibles. On ne sait pas quels prêts ont pu les concerner auparavant : chez VIDEOPLUS, on ne mémorise que les prêts en cours.

Compétez le tableau ci-dessous afin d’extraire du texte les éléments d’informations variables, c’est à dire qui peuvent être remplacés sans modifier la vision globale de l’organisation.

Désignation de l’information	Exemples donnés dans le texte
Numéro de prêt	1132, 1127
Date du prêt	25/04/98
Montant du prêt	60,00 F
Numéro du client	48
Nom	GARNACHE
Prénom	Isabelle
Adresse	Rue de la Madonne 59 000
Type de pièce d’identité présentée	Carte d’identité
N° pièce d’identité présentée	FR56783
Nombre de cassette d’un prêt	2
N° de cassette	934, 1123, 500, 501
N° de film	9, 10, 5, 4
Titre du film	‘Mourir d’aimer’, ‘Le train… trois fois’
Genre du film	Drame, Western
Couleur du tarif	Jaune, rouge
Montant du tarif	25 F, 35 F

B. La classification des données :
données élémentaires / Calculées

Les données recensées lors de l’inventaire peuvent être classées selon 2 catégories :

Données élémentaires (saisie, d’entrée) : elles ne sont pas déductibles d’autres données : elles devront être mémorisées dans le système d’information.
Données calculées (résultats, de sortie) : elles peuvent être déduites d’autres données au moyen d’une règle de calcul.

Généralement, elles ne sont pas enregistrées dans le système d’information sauf quand le calcul repose sur des données élémentaires non stables (c’est à dire, dont la valeur peut varier souvent durant la durée de vie de la base).

Exercice 2 :

Parmi les données recensées dans l’exercice précédent, relevez celles qui sont le résultat d’un calcul (en précisant leur règle de calcul).

Désignation de l’information	Règle de calcul
Montant du prêt	Somme des montants de location d’un film
Nombre de cassette d’un prêt	Somme du nombre des cassettes

C. Le dictionnaire des données et les règles de gestion

1. Liste des données à mémoriser : le dictionnaire de données

Les données élémentaires (et certaines données calculées) sont reportées dans un tableau appelé le dictionnaire de données dans lequel on va préciser les caractéristiques de chaque donnée :

CODE

LIBELLE

TYPE

A / N /AN/ Log / Date

EXEMPLES / REMARQUES

(exemple de valeur, règle de calcul)

…

Pour être exploitable par l’ordinateur, les données doivent être concises et claires. A cet effet, on les codifie. La codification doit être sans ambiguïté et il faut éviter :

Les synonymes : une donnée du système d’information qui va être codifiées de 2 manières différentes.
Exemple : NumPrêt et NumLocation : il va falloir choisir l’un ou l’autre.
Les polysèmes : 2données différentes du système d’information qui vont être codifiées de la même manière.
Exemple : Montant (sous-entendu montant du prêt) va devenir MontantPrêt

Montant (sous-entendu montant du tarif des films) va devenir MontantTarif

Exercice 3 :

Réalisez le dictionnaire de donnée de l’exercice précédent.

CODE	LIBELLE	TYPE	EXEMPLES / REMARQUES
NumPrêt	Numéro de prêt	N (entier)	1132, 1127
DatePrêt	Date du prêt	Date	25/04/98
MtPrêt	Montant du prêt	N (réel)	60,00 F
NumCli	Numéro du client	N (entier)	48
NomCli	Nom du client		GARNACHE
PrenomCli	Prénom du client	AN (50)	Isabelle
AdresseCli	Adresse du client	AN (50)	Rue de la Madonne 59 000
TypePI	Type de pièce d’identité présentée	A (20)	Carte d’identité
NumPI	N° pièce d’identité présentée	AN (10)	FR56783
NumCassette	N° de cassette	N (entier)	934, 1123, 500, 501
NumFilm	N° de film	N (entier)	9, 10, 5, 4
TitreFilm	Titre du film	AN (30)	‘Mourir d’aimer’, ‘Le train… trois fois’
GenreFilm	Genre du film	A (30)	Drame, Western
CouleurTarif	Couleur du tarif	A (10)	Jaune, rouge
MtTarif	Montant du tarif	N (entier)	25 F, 35 F

2. Liste des règles de gestion ou contraintes à respecter

Le dictionnaire de données recense les données à mémoriser. Il faut maintenant préciser les relations de dépendance qu’il y a entre les différentes données et les contraintes qui pèsent sur ces données en vue de construire le MCD.

Pour cela, il faut analyser les documents fournis et, à partir des données du dictionnaire de données, déduire les règles de gestion pour exprimer la façon dont le système étudié fonctionne.

On peut présenter les règles de gestion sous la forme d’un tableau du type suivant :

REGLE N°	ENONCE DE LA REGLE
RG1	…
…	…

Exercice 4 :

Soit l'exposé précédent, repris de manière à remplacer les exemples par des informations génériques :

Un prêt identifié par un certain numéro est enregistré à une certaine date et est souscrit pour un certain montant par un client identifié chez VIDEOPLUS par un certain numéro. Un client peut avoir plusieurs prêts en cours.

Un client est caractérisé par son nom, son prénom, son adresse et doit présenter une pièce d'identité quelconque caractérisée par son numéro.

Un prêt peut concerner plusieurs cassettes.

Une cassette est identifiée par un certain numéro et sur chacune est enregistré un film identifié par un certain numéro et caractérisé par un titre et un genre.

Un film est proposé à un certain tarif identifié par une certaine couleur correspondant à un certain montant de location qui ne peut pas dépasser 50, 00F. Le tarif ne dépend pas du genre.

Un film peut être enregistré sur plusieurs cassettes différentes.

On ne mémorise que les prêts en cours.

Précisez les règles de gestion appliquées chez VIDEOPLUS dans le tableau ci-dessous Vous écrirez ces règles sous la forme :

REGLE N°	ENONCE DE LA REGLE
RG 1	Un prêt correspond à un numéro
RG 2	Un prêt est enregistré à une date
RG 3	Le montant du prêt est la somme des tarifs des cassettes empruntés
RG 4	Un client est désigné par un numéro (NumCli)
RG 5	Le nom du client correspond à NomCli
RG 6	Le prénom du client correspond à PrénomCli
RG 7	L’adresse du client correspond à AdresseCli
RG 8	Le type de Pièce d’Identité correspond à TypePI
RG 9	Le n° de la P.I. correspond à NumPI
RG 10	Une cassette correspond à un numéro (NumCassette)
RG …	…

III Le Model Conceptuel des Données (MCD)

A. Formalisme et vocabulaire MERISE

Voici un exemple du MCD se présentant sous la forme suivante :

1. Entité

a) Définition

A partir du dictionnaire de données, on regroupe les données élémentaires par ‘objets reconnaissables’ appelés entités (opération de composition).
Le nom d’une entité est souvent un nom représentant un ‘objet de gestion’
Exemple : Client, Commande, Produit, …

b) Les propriétés d’une entité

Une entité est un regroupement de données appelées propriétés qui la définissent.

c) Les occurrences d’une entité

Un exemple (un jeu de valeurs) d’entité est appelé occurrence.

12
SIMPSON
34 avenue Faidherbe
59 000 Lille

56

DUPONT
5 rue de la bougière

59 000 Lille

Une entité est un ensemble d’occurrences.

Exemple :

d) L’identifiant d’une entité

Parmi les propriétés d’une entité il y en a qui permet d’identifier de manière unique une occurrence. Cela signifie qu’il suffit de connaître la valeur de l’identifiant pour déterminer la valeur des autres propriétés.
La valeur de l’Identifiant (Id) est obligatoire, unique et stable.
Il n’y a qu’un seul identifiant par entité et pour le repérer, on le souligne.

Exemple : Parmi les occurrences suivantes de l’entité PRODUIT, certaines sont incorrectes.

Bonne

Fausse

Bonne

Fausse

Occ 1, pas d’Id. Conflit d’Id entre Occ 1 et Occ 2.

Deux occurrences d’une même entité ne peuvent avoir le même Id.

2. Association

a) Définition

Les entités ne sont pas isolés chacune dans leur coin : elles sont reliées entre elles par des association.
Une association permet de mettre en relation les occurrences de deux ou plusieurs entités (pas forcément distinctes) pour traduire les interactions qui existent entre elles.
Le nom d’une association est souvent un verbe représentant une ‘action de gestion’

Exemple :

PASSER : permet de mettre en relation les occurrences de CLIENT avec les occurrences de COMMANDE.

Le client n°56 a passé commande n°C/006.

b) Association porteuse

Une association peut avoir (ou porter) des propriétés : On dit que c’est une association porteuse.

Dans le cas où une donnée du dictionnaire de données dépendrait de plusieurs entités : On ne peut la mettre ni dans l’une ni dans l’autre, on l’a met donc dans une association qui relie les deux entités.

c) Degré d’une association

Une association qui relie entre elles :

2 entités = association binaire.
3 entités = association ternaire.

d) Les cardinalités d’une association

Pour préciser le nombre de fois qu’une entité est concerné par une association, on précise les cardinalités.

Plus précisément :

La cardinalité minimum permettra d’indiquer si la participation d’une occurrence d’une entité dans une association est :

Facultatif : cardinalité min = 0.
Obligatoire : cardinalité min = 1.

La cardinalité maximum permettra d’indiquer si la participation d’une occurrence d’une entité dans une association est :

o Exclusive (au plus 1) :cardinalité Max = 1.

o Multiple : cardinalité Max = N (N > 1).

· Tableau récapitulatif des types de cardinalité :

Cardinalité	Signification
0, 1	Au plus un : Chaque occurrence de l’entité n’est pas obligatoirement concernée par l’association et si elle l’est, c’est au plus une seule fois.
1, 1	Un et un seul : Chaque occurrence de l’entité est concernée par l’association au plus une fois.
0, N	Zéro, un ou plusieurs : Chaque occurrence de l’entité n’est pas obligatoirement concernée par l’association et si elle l’est, elle peut l’être plusieurs fois.
1, N	Au moins un : Chaque occurrence est concerné par l’association et peut l’être plusieurs fois.

Exemple :

e) Les différents types d’associations

Ces ont les cardinalités d’une association qui définissent son type.

1) Association de type 1, N

Un vers plusieurs = association hiérarchique.

Card. Max =1 d’un côté et =N de l’autre quelque soit la cardinalité.
C’est un lien hiérarchique qui exprime une dépendance fonctionnelle.

Modèle :

2) Association de type N, N

Plusieurs vers plusieurs : association hiérarchique.
Card. Max =N d’un côté et =N de l’autre quelque soit la cardinalité minimale.
Seules les associations de type N, N peuvent être porteuse.

Modèle :

f) Cas particulier d’association

1) Entité reliées à elles même par une association : Association Réflexives

Une association sert à reliée 2 ou plusieurs entités distinctes en général, cependant, il existe des association relie une entité à elle-même, les association réflexives.

Remarque : On précise sur chacune des branches de l’association le rôle qu’elle joue.

Modèle :

2) Entités reliées par deux associations différentes

Deux entités distinctes peuvent avoir plusieurs lien de dépendance. Dans ce cas elles vont être reliées par plusieurs associations.

Modèle :

B. Démarche de conception d’un MCD

Concevoir un MCD exige de procéder par étape :

1) Faire l’inventaire des données et présenter le dictionnaire des données (ne garder que les informations élémentaires et utiles).

2) Créer les entités en regroupant les propriétés qui portent sur un même objet de gestion et choisir parmi celles-ci, celle qui sera l’identifiant :Si aucune ne convient, en crée une.

3) Relier les entités par les associations en respectant les contraintes de gestion qui apparaissent dans le sujet.

4) Traiter le cas particulier des données du dictionnaire pas encore casés.
Il peut s’agir :

D’une donnée paramètre. Exemple : Taux TVA, frais de port, …
=> On crée une entité qui contiendra un seul attribut identifiant : la donnée paramètre.
D’une donnée dépendant de plusieurs entités en même temps.
=> Elle va appartenir à une association porteuse que l’on va créer (association qui sera forcément de type N, N).

5) Fixer les cardinalités

6) Valider le MCD

IV Le Model Relationnel et les bases de données relationnelles (MR)

A. DU MCD au MR

1. Etude du problème à partir d’un exemple

Le MCD ci dessous représente une partie du système de gestion des commandes des clients dans l’entreprise Ordin’Air.

L’entreprise a enregistré dans son Système Informatique les informations suivantes (sous forme de table Access).

CLIENT	NumCli	NomCli	AdresseCli
	1	DUPONT	4 rue Victor Hugo 59 800 Lille
	2	NEMAR	78, bis av Faidherbe 59 800 Lille
	3	TERIEUR	8 rue Jaurès 59 540 Caudry
	4	VERSAIRE	1 rue Colbert 59 800 Lille

COMMANDE	NumCde	DateCde	MontantCde	NumCli#
	BC 01	13/07/98	12 000	1
	BC 02	5/09/98	15 000	1
	BC 03	8/09/98	980	3
	BC 04	04/10/98	75 300	5
	BC 05	05/10/98	45 120	4
	BC 07	10/10/98	67 000	4
	BC 08	15/10/98	34 000	1
	BC 09	17/10/98	50 900	1
	BC 10	19/10/98	8 000	1
	BC 11	20/10/98	32 000	3

1) Quel est le nombre d’occurrence de l’entité client et l’entité commande ? 4 et 10

2) Lors de la saisie des informations dans la base de données, 2 erreurs on été faites : trouver ces erreurs en analysant les valeurs des occurrences et les règles imposées par le MCD ?
-- BC 04 incorrect car passée par le client 5 qui n’existe pas.
-- Client 2 n’a pas passé de commande or le MCD impose que tout client enregistré a passé au moins une commande.

LE MR est la traduction du MCD sous forme de relation qui représente la structure des tables à implanter sous un SGBDR quelconque.

Le MR ne se construit pas au hasard, il y a des règles biens précises à respecter.

2. Analyse d’un exemple

Afin d’implanter la BD relationnelle à ce MCD, le responsable informatique de la compagnie a réalisé le MR déduit de ce MCD suivant :

TYPE APPAREIL (CodeType, NomType)

OPERATION (CodeOpe, LibelléOpe)

REVISION NumRévision, DateRévision, Observation, CodeOpe#)

APPAREIL (Matricule, Date mise en service)

PREVOIR (CodeType#, CodeOpe#, Nbre heure de vol)

COMPORTER (CodeOpe#, NumRévision#)

a) Déduction des règles de passages

Une entité devient une relation :
=> Dont les attributs sont les propriétés de l’entité ;
=> Dont la clé primaire est l’identifiant de l’entité.

Les associations hiérarchiques s’implantent en ajoutant une clé étrangère correspondant à la clé primaire de la relation mère. Cette clé traduit la dépendance fonctionnelle entre 2 entités, pour la repérer, on met un dièse (#). # = clé étrangère.

Exemple : REVISION (………, ………, Matricule#)
APPAREIL (………, ………, CodeType#)

Les associations non hiérarchique deviennent des relations :
=> Dont la clé primaire est une clé composée résultant de la caractérisation des identifiants des entités reliées ;
=> Dont les attributs sont les propriétés portées par l’association.

Exemple : PREVOIR (CodeType#, CodeOpe#, nbHvol)
COMPORTER (CodeOpe#, NumRevision#)

B. Du MR à la Base de données

Dans cette phase, il s'agit de créer la base de données sous un SGBDR.

Toutes les relations deviennent des tables qui seront reliées entre elles via les clés étrangères et les clés primaires.

1. Créer la structure de la BD

Créer pour chaque relation du MR la table correspondante :

On crée les attributs en définissant leur nom, leur type et leur domaine de validité ;
Définir la clé primaire : contraintes de relation ;
Finir la ou les clés étrangères : doivent être de type compatible (souvent du même type que la clé primaire de la table mère) : contraintes de domaine de définition.

V L’exploitation des bases de données : Les requêtes

A. Définition des requêtes

Une fois la base de données créée sous ACCESS par exemple (création des tables, mise en relation puis saisie des enregistrements dans chaque table), on va pouvoir l’interroger pour répondre aux questions que l’on se pose par l’intermédiaire de requêtes.

Une requête est constituée d'opérations de base (projection (restriction en colonne), sélection (restriction en ligne), jointure) pouvant être complétées par :

Des opérations de calcul

=> en ligne (horizontale) : fonctions arithmétiques comme : + , - , x , /

=> en colonne (verticale) (fonctions somme, moyenne, minimum, maximum, comptage,...).

Des opérations de regroupement de lignes
Des opérations de présentation (tri)

B. Opérations réalisables dans les requêtes

Pour illustrer les différentes opérations réalisables, on partira de l'exercice suivant :

Exemple 1 :

L'entreprise LASSAUD a informatisé sa gestion de production. Elle a créé une base de données sous ACCESS nommée GESTPROD.MDB dont voici le dictionnaire de données simplifié :

Code	Libellé	Type
NumFour	numéro d'un fournisseur	AN(2)
NomFour	nom d'un fournisseur	A(15)
RueFour	n ° et rue d'un fournisseur	AN(40)
CpFour	code postal de la commune d'un fournisseur	N(entier)
VilleFour	ville d'un fournisseur	A(20)
NumComp	numéro d'un composant	AN(3)
DesignComp	désignation d'un composant	A(30)
PrixAchat	prix d'achat unitaire d'un composant	N(réel)
Qté	quantité de composant entrant dans la composition d'un produit	N(entier)
NumProd	numéro de produit	AN(2)
DesignProd	désignation d'un produit	A(15)
TpsAssemb	temps d'assemblage total pour un produit	N(entier)
ChargesDir	charges directes imputées au produit	N(réel)

Et le MCD représentant le système de gestion de production de l’entreprise :

1) Ecrire le modèle relationnel relatif à ce MCD :

FOURNISSEUR (NumFour, NomFour, RueFour, CpFour, VilleFour)

COMPOSANT (NumComp, DesignComp, PrixAchat, NumFour#)

PRODUIT (NumProd, DesignProd, TpsAssemb, ChargesDir)

COMPOSER (NumComp#, NumProd#, Qté)

1. Opérations de base

Exemple 1 :

Question 1 : liste des noms et des adresses de tous les fournisseurs.

Table résultat R

NomFour	RueFour	CpFour	VilleFour
DEVAIS	110 rue maine	75200	PARIS
DUVAL	7 rue du chêne	85000	LA ROCHE
GATIEN	20 rue du parc	44300	NANTES
PANOU	5 rue du lac	53000	LAVAL

R= Projection FOURNISSEUR (NomFour, RueFour, CpFour, VilleFour)

Question 2 : liste des composants proposés par le fournisseur n°18.

Table résultat R

NumComp	DesignComp	PrixAchat	NumFour#
511	double pied table	12,00 F	18
512	ailettes acier mâles	0,50F	18
513	pieds de règlage	5,0017	18
534	ailettes acier femelles	0,40F	18

R= Sélection COMPOSANT (NumFour# = 18)

Question 3 : liste de tous les composants avec les informations relatives aux fournisseurs qui les proposent.

Table résultat R

NumComp	DesignComp	PrixAchat	NumFour#	NumFour	NomFour	RueFour	CpFour	VilleFour
511	double pied table	12,00 F	18	18	GATIEN	20 rue du parc	44300	NANTES
512	ailettes acier mâles	0,50F	18	18	GATIEN	20 rue du parc	44300	NANTES
513	pieds de règlage	5,00 F	18	18	GATIEN	20 rue du parc	44300	NANTES
514	armature plateau	6,00 F	24	24	PANOU	5 rue du lac	53000	LAVAL
515	plateau bois "mer"	8,00 F	35	35	DUVAL	7 rue du chêne	85000	LA ROCHE
516	plateau bois "azur"	20,00 F	14	14	DEVAIS	110 rue maine	75200	PARIS
534	ailettes acier femelles	0,40 F	18	18	GATIEN	20 rue du parc	44300	NANTES

R= Jointure COMPOSANT, FOURNISSEUR (COMPOSANT.NumFOUR = FOURNITURE.NumFour)

Question 4 : N° et désignation des composants entrant dans la composition du produit n°28.

Table résultat R

NumComp	DesignComp
511	double pied table
512	ailettes acier mâles
515	plateau bois "mer"
534	ailettes acier femelles

R1= Jointure COMPOSANT, COMPOSER (COMPOSANT.NumComp= COMPOSER.NumComp#)

R2= Sélection R1 (NumProd# = 28)

R= Projection R2 (NumComp, DesignComp)

2. Opération de calcul

Les calculs horizontaux sont des calculs réalisés en ligne. Ils permettent d'obtenir les valeurs de champs calculés.

Question 5 : Numéro de composant, quantité, prix d'achat unitaire et montant (Qté x prix d'achat) pour chaque composant entrant dans la composition du produit n°29.

Table résultat R

NumComp	Qté	PrixAchat	Montant
511	2	12,00 F	24,00 F
512	4	0,50F	2,00 F
516	1	20,00F	20,00F

R1= Jointure COMPOSANT, COMPOSE (COMPOSANT.NumComp= COMPOSER.NumComp)

R2= Sélection (NumProd# =29)

R= Projection R2

Les calculs verticaux sont des calculs réalisés en colonne : ils permettent d’obtenir des totaux, des résultats statistique sur un ensemble de lignes.

Question 6 : Nombre de fournisseurs enregistrés dans la base de donnée.

Table résultat R

Nombre de fournisseurs

R= Projection Fournisseur (Nombre (NumFour))

Question 7 : Montant total des charges directes pour tous les produits.

Table résultat R

Montant total des charges

65,00 F

R = Projection Produit (somme (ChargesDir))

Question 8 : Prix d'achat le plus élevé parmi les composants.

Table résultat R

Prix d’achat le plus élevé

20,00 F

R = Projection Composant (maximum (PrixAchat))

Question 9 : Prix d’achat totaldu produit n°29.

Table résultat R

Prix d’achat total

46,00 F

R1 = Jointure Composant, composer (Composant.numcomp = Composer.numComp)

R2 = Sélection R1 (NumProd# = 29)

R = Projection R2 (somme (Qté x prix d’achat)

3. Opérations de regroupement

Question 10 : Numéro des produits et nombres de composant entrant dans la composition de chacun d’eux.

Table résultat R

NumProd#	Nb de NumComp
28	4
29	3

R = Projection Groupée Composer (NumProd#, Nombre (NumComp#)

Remarque : Le calcul (NumComp#) se fait pour chaque groupe NumProd# crée.

Question 11 : Numéro des produits et quantité de composants entrant dans la composition de chacun d’eux.

Table résultat R

NumProd#	Somme de Qté
28	11
29	7

R = Projection Groupée Composer (NumProd#, Somme (Qté)

4. Opérations de tri

Question 12 : Liste des noms et adresses de tous les fournisseurs triés par ordre alphabétique.

Table résultat R

NonFour	RueFour	CpFour	VilleFour
DEVAIS	110 rue maine	75 200	PARIS
DUVAL	7 rue du chêne	85 000	LA ROCHE
GATIEN	20 rue du parc	44 300	NANTES
PANOU	5 rue du lac	53 000	LAVAL

R1 = Projection Fournisseur (NomFour, RueFour, CpFour, VilleFour)

R = Tri croissant 21 (NomFour)

C. Le langage SQL

Une requête est une question. Pour être comprise par l'ordinateur, elle doit être écrite dans un langage de requêtes tel que SQL (langage standard d’interrogation des BD relationnelles) ou QBE (mode graphique sous ACCESS).

La syntaxe générale d’une requête SQL est la suivante :

SELECT	colonnes de projection (élémentaires ou calculées)
FROM	tables d’où l’on extrait les informations
WHERE	conditions de sélection et/ou de jointure
GROUP BY	colonnes sur lesquelles on effectue un regroupement
ORDER BY	colonnes sur lesquelles on trie (ASC / DESC)
DISTINCT	affiche une seule fois

Une requête SQL n’utilise pas forcément toutes ces opérations mais lors de l’écriture d’une requêtes, on doit respecter l’ordre indiqué.

Algébrique	SQL
Nombre	Count
Somme	Sum
Maximum	Max
Minimum	Min
Moyenne	AVG

Exemple 2 : même données que l’exemple 1.

Question 1 : liste des noms et des adresses de tous les fournisseurs.

SELECT NomFour, RueFour, CpFour, VilleFour

FROM Fournisseur ;

Question 2 : liste des composants proposés par le fournisseur n°18.

SELECT NumComp, DesignComp, PrixAchat

FROM Composant, Fournisseur
WHERE Composant.NumFour =Fournisseur.NumFour ;

* = tout

Question 3 : liste de tous les composants avec les informations relatives aux fournisseurs qui les proposent.

SELECT *
FROM Fournisseur, Composant
WHERE Composant.NumFour = Fournisseur.NumFour ;

Question 4 : N° et désignation des composants entrant dans la composition du produit n°28.

SELECT NumComp, DesignComp

FROM Composant, Produit, Composer

WHERE Composant.NumComp= Composer.Numcomp

AND Produit.NumProd= Composer.numProd ;

Question 5 : Numéro de composant, quantité, prix d'achat unitaire et montant (Qté x prix d'achat) pour chaque composant entrant dans la composition du produit n°29.

SELECT NumComp, Qté, prix d’achat, Qté*prixd’achat

FROM Composant, Produit, Composer

WHERE Composant.NumComp= Composer.Numcomp

AND Produit.NumProd= Composer.numProd

AND Produit.NumProd= 29 ;

Question 6 : Nombre de fournisseurs enregistrés dans la base de donnée.

SELECT Count (NumFour)

FROM Fournisseur ;

Question 7 : Montant total des charges directes pour tous les produits.

SELECT Sum (ChargesDir)

FROM Fournisseur ;

Question 8 : Prix d'achat le plus élevé parmi les composants.

SELECT Max (PrixAchat)

FROM Composant ;

Question 9 : Prix d’achat totaldu produit n°29.

SELECT Sum (PrixAchat*Qté)

FROM Composant, Composer, Produit ;

Question 10 : Numéro des produits et nombres de composant entrant dans la composition de chacun d’eux.

SELECT NumProd, Count (NumComp)

FROM Composant, Composer, Produit

WHERE Produit.NumProd=Composer.NumProd
GROUP BY Produit.NumProd

Question 11 : Numéro des produits et quantité de composants entrant dans la composition de chacun d’eux.

SELECT NumProd, Sum (Qté)

FROM Produit, Composer

WHERE Produit.NumProd=Composer.NumProd
GROUP BY Produit.NumProd ;

Question 12 : Liste des noms et adresses de tous les fournisseurs triés par ordre alphabétique.

SELECT NomFour, RueFour, CpFour, VilleFour

FROM Fournisseur

ORDER BY ASC (NomFour)