Exercices MCD MLD schéma relationnel algèbre relationnel Requête SQL Normalisation et dépendances fonctionnelles

Exercice 1 : Etude de cas 

Le jardin d’enfants «  Le paradis des petits » prend en charge des enfants allant de cinq mois à cinq ans. 

Le désir de l’établissement est, qu'au travers des activités et des programmes proposés, chaque enfant s'épanouisse pleinement. Les enfants sont pris en charge de 8h30 à 16h30. Un déjeuner et un goûter leur sont servis quotidiennement par les monitrices et une visite médicale est assurée au moins une fois par mois.

Le jardin d’enfants regroupe les enfants en trois catégories : des groupes de garderies pour les enfants ayant entre cinq mois et un an, des classes enfantines pour ceux ayant entre un an et trois ans, et des classes primaires pour les enfants entre trois ans et cinq ans. A chaque enfant, quelque soit son âge et son groupe, est associé un code, un nom, un ou plusieurs prénoms, sa date de naissance, le montant de son inscription et un badge de couleur rose pour les filles et de couleur bleue pour les garçons. On conserve aussi la date d’entrée de l’enfant à l’établissement. 

 
En plus du personnel administratif, le jardin d’enfants dispose des monitrices qualifiées pour animer les journées des enfants suivant leur âge. L’établissement garde les données personnelles relatives à ses employés regroupant le nom, prénom(s), date de naissance, date d’embauche, salaire, le téléphone privé et le dernier diplôme obtenu. Les monitrices peuvent être de deux types : les animatrices et les  éducatrices. Les animatrices se chargent de l’organisation de différents jeux (gymnastique, activités manuelles individuelles ou par groupes) et des activités de motricité globale (connaissance du corps, coordination, performance, aisance…).  Quant à leur éducation, des séances de motricité fine (préparation à l'écriture), de graphisme, de math… sont organisées par les éducatrices pour les enfants dépassant les trois ans.

 
Suivant la demande des parents, certains enfants sont gardés au-delà de 16h30 et prennent leur dîner dans l’établissement. Dans ce cas, un supplément est payé par rapport aux autres enfants. Il est calculé en fonction de l’âge de l’enfant et l’état social de sa famille (famille aisée, famille moyenne, famille pauvre).  La monitrice qui nourrit l’enfant est choisie par ses parents. 

  

L’établissement dispose de jouets qui sont distribués aux enfants. Ces jouets sont classés en deux types : ceux qui peuvent être données aux enfants de moins de deux ans et ceux données aux enfants de plus de deux ans. Le jardin  d’enfants garde trace du libellé du jouet, la catégorie (poupées, voitures, …), le nombre d’exemplaires ainsi que le nom et le numéro de téléphone du ou des fournisseurs.

 
Le jardin d’enfants organise des activités à des dates et heures précises. A chaque activité est associée une monitrice responsable qui elle-même est aidée par d’autres monitrices. Chaque activité se déroule dans un local de l’établissement et est exercée par un groupe d’enfants.  

On demande :

- Définir le modèle entité-association le plus complet possible relatif à ce problème. 

N’hésitez pas à émettre des hypothèses si vous estimez que des détails vous manquent.
Précisez également les contraintes d’intégrité non-représentées.

- Effectuer la conversion du diagramme entité-association obtenu vers le modèle relationnel. Indiquer la (les) clé(s) de chaque relation, les contraintes d’intégrité référentielles, les contraintes de domaine, ainsi que toute autre contrainte. 

Exercice 2

Voici  un modèle relationnel reprenant la gestion des jardins d’enfants.

Jardin(codeJardin, nomJardin, adresse)
Fournisseur(codeFournisseur, nomFournisseur, adresse)
Jouets(codeJouet, nomJouet, catégorie) 
Commande(CJE, CF, CJ,mois,année, quantité )

Où :

•  L’attribut CJE correspond au codeJardin du jardin d’enfants,
•  L’attribut CF correspond au codeFournisseur du fournisseur,
•  L’attribut CJ correspond au code jouet du jouet,

Soient les requêtes suivantes :

 
1. Donner les noms des jardins d’enfants qui ont commandé aux moins tous les jouets que ceux que le jardin d’enfants de l’« ULB » a commandé le mois de décembre 2000. 

 
2. Donner pour chaque mois de l’année 2000 et par catégorie la quantité totale de jouets que le fournisseur « BelgiumToys » fournit. 

 
3. Donner les noms des jardins d’enfants qui ont acheté la plus grande quantité de poupées de code « JOU001 » pendant le mois de décembre 2000. 

 
4. Donner le nom des fournisseurs qui ne fournissent pas le jardin d’enfants « ULB » mais fournissent le jardin d’enfants « Le paradis des petits ».

Exprimer toutes les requêtes en SQL et les requêtes 1 et 4 en algèbre.

Exercice 3

Voici un modèle relationnel reprenant la gestion des équipes gagnantes  les trophées lors des matchs de football

Pays(codePays, nomPays)
Equipe(codeEquipe, nomEquipe, codePays)
Gagne(codeEquipe, codeTrophée ,année, codePays)
Trophée(codeTrophée, nomTrophée)

Où : 

• Nom du trophée a une valeur appartenant à l’ensemble {coupe du monde, coupe du monde junior, jeux olympiques, ...}
• CodePays dans la relation Gagne correspond a l’endroit où se déroule le trophée.

Soient les requêtes suivantes :

1. Donner les noms des équipes qui n’ont jamais gagné un trophée dans leur pays.
2. Donner les noms des équipes qui ont gagné 2 fois le trophée « Coupe du Monde».
3. Donner les noms des pays qui ont gagné au moins une fois tous les trophées.
4. Donner les équipes qui n’ont jamais gagné un trophée.  

Exprimer en calcul relationnel tuple les requêtes 1 et 2, et en calcul relationnel domaine les requêtes 3 et 4.

Exercice 4 

On considère le schéma relationnel R défini sur les attributs suivants :

C : cours, P : professeur, H : heure,  S : salle,  E : étudiant, N : note 

 

où un n-uplet (c, p, h, s, e, n) a pour signification que le cours c est fait par le professeur p à l'heure h dans la salle s par l'étudiant e qui a reçu la note n. 

L'ensemble E des dépendances fonctionnelles initiales est le suivant :  

C → P, H, S → C , H, P → S, C, E → N,  H, E → S 

1 ) Donner l'ensemble des dépendances fonctionnelles élémentaires engendrées par E. 
2 ) Quelle est la clé de la relation R ? Montrer qu'elle est unique. 
3 ) Quelle est la forme normale de la relation R ? Si elle n'est pas en 3FN proposer une décomposition en 3FN.