Exercices et Examens Merise : introduction aux systèmes de gestion de base de données relationnelle

Exercice introduction aux systèmes de gestion de base de données

  1. En utilisant vos propres mots, essayez d’expliquer ce qu’est une base de données. 
  2. Quelle est la différence principale entre une base de données à deux dimensions et une base de données relationnelle ?
  3. Citez un avantage d’une base de données relationnelle.
  4. Citez un inconvénient d’une base de données relationnelle.
  5. Quelle est la différence entre un champ et un enregistrement ?
  6. Pourquoi est-il recommandé d’utiliser une clé primaire dans votre table ?
  7. Parmi les objets suivants : tables, requêtes, formulaires et états, le(s)quel(s) est(sont) utilisé(s) pour stocker des données ?
  8. Proposez deux exemples où l’utilisation d’une base de données pourrait être plus  efficace que celle de fiches cartonnées.

Exercice analyse des requêtes d'une BD relationnelle

 La société Losoft vend, entre autres, des photocopieurs. Elle propose à ses clients de souscrire des abonnements qui inclus la fourniture du papier, du toner et l’entretien du photocopieur. Le coût de l’abonnement est fonction de la quantité de photocopies choisie. L’entretien est toujours assuré par le même technicien.

id 1798 1
Travail à faire :
Compléter le tableau d’analyse des requêtes ci-dessous :
id 1798 2

 Solution :

id 1798 3

 

Exercice Merise: Opération sur un Article

Quelle amélioration liée à la lisibilité du modèle ci-dessous apportez-vous ?

ExerciceMerise-id1806

Solution :

Le modèle présente des lacunes de forme :

  • la 1ère opération comporte un titre correspondant à un résultat, et non pas une action
  • un résultat créé par cette opération, n’est pas nommé ;
  • des résultats du modèle sont nommés par des intitulés d’opération

le modèle rectifié;

Exercice1Merise-id1806

Exercice cardinalités et analyse des requêtes SQL

Objectifs :
  • Justifier les cardinalités.
  • Les requêtes SQL sous SGBD SQL.


L’analyse des données

Madame SETAG, secrétaire comptable, vous transmet, en annexe 1, un extrait du modèle entité association qu'elle vient de concevoir pour la gestion des clients.


Travail à faire

1.  Expliquez les cardinalités.
2.  Complétez le tableau d'analyse des requêtes (annexe 2).

Annexes

Annexe 1

id 1801 1
Annexe 2
id 1801 2

Solution :

Question 1 :

Cardinalité Client - 0,N - Correspondre : A un client correspond 0 ou n factures.
Cardinalité Facture - 1,1 - Client : A une facture correspond un client et un seul.
Cardinalité Facture - 1,N - Comprendre : Une facture comprend 1 ou plusieurs produits.
Cardinalité Produit - 0,N - Comprendre : A un produit peut correspondre 0 ou n factures.

Question 2:

id 1801 3

Exercice merise et requêtes SQL sur une BD gestion des factures

Objectifs :
  • Passer du MCD au MLD.
  • Justifier des cardinalités.
  • Utiliser le langage SQL.
 
Enoncé

La société Zamur souhaite que vous développiez une application lui permettant d’améliorer le suivi de ses clients. Vous utiliserez pour ce faire le logiciel de gestion de bases de données acheté il y a 2 mois.

Travail à faire
  1. Complétez le modèle conceptuel des données fourni en annexe 1, en y implantant les cardinalités.
  2. Quels sont les critères pour qu’un attribut puisse être retenu comme identifiant ?
  3. Écrivez le modèle relationnel correspondant au modèle conceptuel des données que vous venez de compléter.
  4. Complétez le tableau d’analyse des requêtes fourni en annexe 2.

Annexe 1 :

id 17991

Règles de gestion :
  • Un client est toujours géré par le même commercial
  • Un commercial gère plusieurs clients
Annexe 2 :
id 17992

Solution :

Question 1
id 17993


Question 2

Pour être retenu en qualité d’identifiant, un attribut doit stocker des données uniques et stables.


Question 3


id 1799 4


Question 2

id 1799 5


Exercices normalisation et dépendances fonctionnelles des BD

Exercice 1


Soit R1 (A, B, C, D, E, F) une relation avec l'ensemble de dépendances suivant :

id 1794 1
  1. Donner le graphe(ensemble) minimum de dépendances. Quelles est la clé de R1 ?
  2. Quelle est la forme normale de R1 ?
  3. On décompose la relation R1 en R11 et R12 : R11(A, B, D, E, F) et R12(B,C). Quelles sont les formes normales des relations R11 et R12 ?
  4. Proposer une décomposition sans perte d'information de R11.
Exercice 2
Soit le schéma de la relation R(A, B, C, D, E, G) et un ensemble donné de dépendances fonctionnelles pour cette relation:
id 1794 2
1. Donner le graphe minimal des dépendances fonctionnelles de R
2. Donner une décomposition de R en relations 3NF sans perte d'informations et sans perte de dépendances.
3.Précisez l'identifiant de chaque relation obtenue.

Solution :


Corrigé Exercice 1 :

1. L'ensemble minimum de dépendances fonctionnelles de R1 est le suivant :
id 1794 3
2. La clé de cette relation est (A,B). Cette relation est en première forme normale mais pas en deuxième forme normale car il y a un attribut qui ne fait pas partie de la clé(C) qui dépend d'une partie de la clé(B).

3. La relation R11 est en deuxième forme normale et pas en troisième normale car il subsiste une dépendance transitive. La relation R12 est en BCNF.

4. On peut décomposer R11 en R112(D, E, F) et R112(A,B,D) sans perte d'information.
Corrigé Exercice 2

Avec A --> C et A, C --> E,on constate que A, C --> E n'est pas élémentaire et que l'on peut la remplacer par A --> E sans perte de dépendance.

Avec A --> E et A, D, E --> B, G, on constate que A, D, E --> B, G n'est pas élémentaire et que l'on  peut la remplacer par A, D --> B, G sans perte de dépendance.

Avec A --> B et A, D --> B,on constate que A, D --> B est redondante et que l'on peut la supprimer.

Avec A --> B, C --> B et A --> C, on constate que A --> B peut être déduite et que l'on peut donc la supprimer.

L'ensemble minimum des dépendances fonctionnelles de R est donc le suivant :

id 1794 4

Voici une décomposition sans perte de dépendance ni d'information :

id 1794 5

Exercice sur les SGBD relationnelles

1.  Qu'est  ce  qu'un  SGBDR  Donnez  la  signification  des lettres ainsi  qu'une  définition détaillée ? 
2.  Citez 6 exemples de SGBDR (3 commerciaux et 3 libres) 
3.  Donnez les différents types de données
4.  Qu'est ce que l'intégrité référentielle (donnez une réponse détaillée) ? 
5.  Qu'est ce que l'intégrité de domaine (donnez un exemple) ? 
6.  Que représente le modèle relationnel ? 
7.  Qu'est ce qu'une clé étrangère ? 
8.  Que signifient les lettres SQL ? 
9.  En SQL, que signifient les lettres LID 

Solution :

1.  Qu'est  ce  qu'un  SGBDR  Donnez  la  signification  des lettres ainsi  qu'une  définition détaillée ? (2 pts) 

Système de Gestion de Base de Données Relationnel

Un  SGBD  représente  donc  l'ensemble  des  programmes  assurant  structuration,  stockage, maintenance,  mise à jour et  recherche des données d‘une base +  interfaces nécessaires aux différentes formes d‘utilisation de la base. Relationnel exprime les relations (clés primaires - clés étrangères) entre les tables


2.  Citez 6 exemples de SGBDR (3 commerciaux et 3 libres) (2 pts) 

Les SGBD commerciaux :

·     Oracle  (Unix, Linux, W indows) 
·     M icrosoft : SQL serveur et Access 
·     IBM  : DB2, Informix 
·     Sybase : Sql Anywhere (W indows, Unix, Linux …) 
·     Interbase (W indows) 
·     … 

Les SGBDR libres
:

  • My-SQL
  • Postgre Sql (Linux)
  • Firebird (Linux, W indows)
  • Adabas, SAP-DB,    Max DB (Linux, Windows)


3.  Donnez les différents types de données (3 pts) 

Numériques  (octet, entier, entiers longs, réels …) 
Texte 
Date/heure 
Monétaire, booléen, lien hypertexte, mémo … 

4.  Qu'est ce que l'i égrité référenti le (donnez une réponse détailée) ? (2 pts) 

La notion d‘intégrité référentielle est au cœur même de la base de données. Elle lui  confère  son  caractère  relationnel.Référentielle  signifie  que  la  clé  étrangère  doit  faire référence à une clé primaire.


Appliquer l'intégrité référentielle implique :

  • Dans le champ  clé étrangère vous ne pouvez pas entrer une valeur qui  n'existe pas dans la clé primaire (Un client ne peut être saisit dans une commande s'il n'existe pas).
  • Vous  ne  pouvez  pas  effacer  un  enregistrement  de  la  table  primaire  si  des enregistrements correspondants existent dans une table liée
  • Vous ne  pouvez pas modifier une  valeur clé  primaire  dans la table  primaire  si  cet enregistrement a des enregistrements liés
5.  Qu'est ce que l'intégrité de domaine (donnez un exemple) ? (2 pts)
Les contraintes de domaines sont  des contraintes liées aux colonnes des tables (attribut  non nul,  ou  dans une fourchette de valeurs …)  Ex  :  mode de paiement  (Espèce,  chèque,  Carte bleue)

6.  Que représente le modèle relationnel ? (2 pts) 

Le modèle relationnel représente la structure de la base de donnée, à savoir
  • La structure des tables (nom, nom des champs)
  • Les liens entre elles.
7.  Qu'est ce qu'une clé étrangère ? (1 pt)
  • La clé étrangère matérialise la liaison de la table vers une autre table (clé primaire).
8.  Que signifient les lettres SQL ? (1 pt)
  • Structured Query Language (langage d'interrogation de base de donnée)
9.  En SQL, que signifient les lettres LID ? (1 pt) 

Langage d'Interrogation des Données 

UE Bases de Données

Examen du vendredi 22 avril 9h-10h30, documents non autorisés.

On considère la base ”avions” maintes fois vue en TD:

PILOTE (NUMPIL, NOMPIL, ADR, SAL)

AVION (NUMAV, NOMAV, CAPACITE, LOC)

VOL (NUMVOL, NUMPIL, NUMAV, VILLE_DEP, VILLE_ARR, H_DEP, H_ARR)

NUMPIL: cléde PILOTE, nombre entier

NOMPIL: nom du pilote, cha^?ne de caractères

ADR: ville de la résidence du pilote, cha^?ne de caractères SAL: salaire du pilote, nombre entier

NUMAV: cléde AVION, nombre entier

CAPACITE: nombre de places d’un avion, nombre entier

LOC: ville de l’aéroport d’attache de l’avion, cha^?ne de caractères NUMVOL: cléde VOL, nombre entier

VILLE_DEP: ville de départ du vol, cha^?ne de caractères VILLE_ARR: ville d’arrivée du vol, cha^?ne de caractères H_DEP: heure de départ du vol, nombre entier entre 0 et 23 H_ARR: heure d’arrivée du vol, nombre entier entre 0 et 23

Exercice A Algèbre relationnelle, contraintes, cl´es

(A.i) Quelle propriétéexprime la formule suivante:

?NUMPIL(VOL) ? ?NUMPIL(PILOTE) = 0/

[ Si on préfère, on peut aussi écrire cette formule ainsi:

R1 = Projection(VOL/NUMPIL)

R2 = Projection(PILOTE/NUMPIL) Différence(R1,R2) = 0/ ~

(A.ii) Donner la définition de:

Les attributs {NOMPIL, ADR} constituent une cléde la relation PILOTE.

(A.iii) (difficile) Exprimer la propriétéprécédente sous la forme R = 0/ ou R est une expression de l’algèbre relationnelle.

Exercice B Requêtes en algèbre relationnelle

Exprimer les requêtes suivantes dans l’algèbre relationnelle (on donnera NUMPIl et NOMPIL pour chaque pilote trouvé):

(B.i) Quels sont les pilotes gagnant moins de 20000?

(B.ii) Quels sont les pilotes conduisant au moins un vol au départ de ’Bordeaux’? (B.iii) Quels sont les pilotes conduisant des avions de plus de 300 places?

(B.iv) Quels sont les pilotes ne conduisant que des avions de plus de 300 places?

(B.v) Quels sont les pilotes conduisant un vol au départ de chez eux avec un avion localisédans leur ville?

Exercice C Requêtes en SQL

Exprimer les requêtes suivantes en SQL:

(C.i) Quels sont les pilotes bordelais conduisant au moins un vol arrivant a` ’Bordeaux’? (C.ii) Quelle est le NUMAV et la LOC des avions qui font un trajet mais pas le retour? (C.iii) Quels est le salaire maximum d’un pilote niçois?

(C.iv) Quel est le salaire maximum d’un pilote et quel est le pilote le perçoit?

Exercice D Schémas

On considère une base de donn´ees bibliographique avec les entit´es suivantes:

(1). BIBLIOGRAPHIE, dont les attributs sont nom, organisme, URL

(2). R´EF´ERENCE, dont les attributs sont clé, éditeur, année, titre, auteur, (on suppose qu’il n’y a qu’un auteur par r´ef´erence) et a deux entit´es sp´ecialis´ees:

(2.1) REVUE, qui a pour attributs sp´ecifiques suppl´ementaires nom, volume, ISSN

(2.2) LIVRE qui a pour attributs sp´ecifiques suppl´ementaires ISBN et qui se sp´ecialise lui même en une entit´e:

(2.2.1). ACTES DE COLLOQUE qui a pour attribut sp´ecifique coordonnateur (on suppose qu’un volume d’actes n’a qu’un coordonnateur).

Il n’y a qu’une seule association: possède qui indique qu’une bibliographie contient une r´ef´erence. (D.i) Dessiner un diagramme entit´es associations qui d´ecrive au mieux cette situation.

(D.ii) Convertir ce diagramme en sch´ema relationnel (on pr´ecisera les avantages et inconv´enients des choix effectu´es).

(D.iii) (difficile) Proposer une description de la même base lorsqu’il peut y a voir plusieurs auteurs pour une même r´ef´erence et plusieurs coordonnateurs pour un même volume d’actes.

Examen de Bases de Données 2006

Mardi 25 avril 2006 9h-10h30
Sujet: 1 page documents non autorisés
 

Exercice A

Conception de schéma relationnel: schémas entité-association et schéma relationnel

On considère les entités suivantes:

– ENFANT: Prénom, Nom.

– ADULTE: Prénom, Nom, Numéro de sécuritéso­ciale.

On souhaite représenter les relations binaires suivantes entre un enfant et une autre personne: père, mère, et unique personne (qui est obligatoirement son père ou sa mère) sur le compte de laquelle l’enfant est inscrit: en effet, un enfant n’a pas de numéro de sécuritésociale. La mère est une femme et le père un homme. Atten­tion, il peut y avoir des personnes différentes, aussi bien des adultes que des enfants, de mêmes prénoms et de même noms! Par contre, deux personnes inscrites sous le même numéro de sécuritésociale ont des noms ou des prénoms différents, et il n’y a qu’un seul adulte inscrit sous un numéro de sécuritésociale. On ne rajoutera pas d’attributs aux entités ENFANT et ADULTE.

(A.i) Proposer un schéma entitéassociation avec au moins les entités mentionnées en utilisant si besoin d’autre entités, spécialisations ou généralisations. On pourra écrire explicitement des contraintes.

(A.ii) Proposer un ou plusieurs schémas relation­nels correspondants.

Exercice B

Conception de schéma relationnel: formes normales

(B.i) Donner un exemple de relation et de dépen­dances fonctionnelles qui soit 3NF et pas BCNF. Expli­quer pourquoi cet exemple est 3NF mais pas BCNF.

(B.ii) Peut-on mettre cet exemple sous forme BCNF? Si oui faites-le.

(B.iii) Quels avantages et inconvénients ont les dif­férentes formes normales 3NF et BCNF?

(B.iv) Quel minimum peut-on garantir lors de la con­ception d’un schéma relationnel?

NUMAV et NOMAV pour chaque avion trouvé):

(D.i) Quels sont les avions de plus de 300 places?

On considère la base ”avions”:

PILOTE (NUMPIL, NOMPIL, ADR, SAL) AVION (NUMAV, NOMAV, CAPACITE, LOC) VOL (NUMVOL, NUMPIL, NUMAV, VILLE DEP, VILLE ARR, H DEP, H ARR)

NUMPIL: cléde PILOTE, nombre entier NOMPIL: nom du pilote, chalne de caractères ADR: ville de la résidence du pilote, chalne de caractères

SAL: salaire du pilote, nombre entier

NUMAV: cléde AVION, nombre entier CAPACITE: nombre de places d’un avion, nombre entier

LOC: ville de l’aéroport d’attache de l’avion, chalne de caractères

NUMVOL: cléde VOL, nombre entier VILLE DEP: ville de départ du vol, chalne de caractères

VILLE ARR: ville d’arrivée du vol, chalne de caractères

H DEP: heure de départ du vol, nombre entier entre 0 et 23

H ARR: heure d’arrivée du vol, nombre en-tier entre 0 et 23

Exercice C

SQL: création de table

(C.i) Donner la commande SQL pour créer la table VOL.

Exercice D

SQL: requêtes

Exprimer les requêtes suivantes en SQL (on donnera)

(D.ii) Quels sont les avions effectuant au moins un vol au départ de ’Bordeaux’?

(D.iii) Quels sont les avions effectuant un vol au départ de leur localitéavec un pilote demeurant dans cette même ville?

(D.iv) Quels sont les avions de capacitémaximale?

(D.v) Quelle est la durée moyenne des vols, pour chaque avion?

(D.vi) Quelle est la durée moyenne des vols, pour chaque avion de capacitésupérieure `a 300?

(D.vii) (difÞcile) Quels sont les avions n’effectuant que des vols conduits par un pilote nommé’Durand’?

(D.viii) (difÞcile) Quels sont les avions conduits par tous les pilotes?

ABONNEZ-VOUS À LA NEWSLETTER !

Télécharger votre Calendrier 2022

X