Examen Base de Donnees SGBD - MCD - MLD - Dépendances fonctionnelles - Requêtes SQL en Algèbre

Partie 1 : Dépendances Fonctionnelles

Soit R(A, B, C, D, E, F) une relation avec l’ensemble de dépendances suivantes :

{AB→ C , AB→D, AB→E, AB→F, A→D, B→F, C→D, C→A}



1. Donner l’ensemble minimal de dépendances et motiver votre choix. Quelle est la clé de R ?
2. Quelle est la forme normale de R ?
3. On décompose la relation R en R1(A, B, C, D, E) et R2(B, F). Quelles sont les formes normales des relations R1 et R2 ?

Partie 2 : Etude de cas 

La société SOFMATEC s’occupe de l’entretien routier. Elle dispose d’un ensemble d'employés (caractérisés par leur numéro d'employé, nom, adresse, date de recrutement, et catégorie) et d’un ensemble d’équipements  (camions, pelleteuses, porte-outils, pose-bordures, balayeurs...). Les équipements sont caractérisés par un code, le type et la date d’achat. 

La société reçoit de ses clients des demandes d'intervention qui peuvent être d’entretien ou de réparation. Les clients sont identifiés par un numéro du client, leur nom, adresse et type (privé ou public). Les demandes d'intervention contiennent le numéro, la date de demande, le type et une description des travaux à réaliser. 

A la réception d'une demande, un ingénieur de la société est chargé de l'établissement d'un devis. Il visite les lieux, estime les quantités des différentes ressources (humaines et équipements), les matériaux (ciment, goudron, ... ), les différentes tâches, ainsi que le temps estimé du chantier. Les matériaux sont caractérisées par leur code, désignation et description. Une tâche est spécifiée par un code, la désignation (chargement, nivelage, balayage, terrassement, déneigement...), la localisation, une description et le temps estimé.

Lors de l'acceptation du devis par le client, il est tenu de payer un acompte du 30% du montant total avant que les travaux commencent. Le système doit garder trace des dates d'acceptation du devis ainsi que la date du paiement de l'acompte. L'ingénieur qui a réalisé le devis est alors responsable du chantier. A la fin de chaque mois, le client reçoit un compte rendu détaillé de l'avancement des travaux, spécifiant les ressources et matériaux utilisés, ainsi que l'état d'avancement des différentes tâches. La société ne peut utiliser des moyens différents des ceux prévus dans le devis qu’après accord du client. Un accord contient les différentes ressources, matériaux, et tâches à ajouter, ainsi que la date de l'accord par le client.

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 par le diagramme.
                            

• 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.

Partie 3 : Requête SQL en Algèbre

La base de données d'une entreprise est constituée des relations suivantes.

Client(noClient, nomClient, adresseClient)

Produit(noProd, nomProd, prixUnitaire, quantitéStock) 

Ventes(noVente, noClient, noProd, date, quantité, montantTotal ) 
 noClient référence Client.noClient
 noProd référence Produit.noProd

Soient les requêtes suivantes : 

1. Donner les noms des clients qui ont acheté au moins un produit dont le prix est supérieur à 1000.

2. Donner pour chaque produit et pour les ventes du mois de février 2000, le nombre de ventes effectuées et la plus grande quantité achetée par un client.

3. Donner le nom et l'adresse des clients dont chacun de ses achats est d'un montant total supérieur ou égale à 25.000

4. Supprimer de la base de données toutes les ventes du client de nom Dounia qui ont été faites après le 1/4/2000 et qui concernent des produits dont le prix unitaire est supérieur à 10.000.

On vous demande d’exprimer toutes les requêtes en SQL et les requêtes 1 et 3 en algèbre.

Partie 4 : Calcul Relationnel Tuple et Domaine

Soit la base de données relationnelle suivante, décrivant le nombre d’heures passées par chaque employé sur son projet.

Employé(noMatricule, nomEmp, prénomEmp, adresseEmp, dateRecrutement, nomDépart)
 nomDépart référence Département.nomDépart

Département(nomDépart, responsableDépart, nombreEmployés)
 responsableDépart référence Employé.noMatricule

Projet(nomProjet, nomDépart, budgetProjet, dateDébut)
 nomDépart référence Département.nomDépart

Travaille(noMatricule, nomProjet, mois, année, nombreHeures)
 noMatricule référence Employé.noMatricule
 nomProjet référence Projet nomProjet

Soient les requêtes suivantes :

1. Donner le nom et l'adresse des employés qui ont travaillé le mois précédent plus de 20 heures dans le projet 'Virtual Java'.

2. Donner les noms des projets dont toutes les personnes qui y travaillent ont été engagées l'année 1998.

3. Pour chaque projet donner son nom ainsi que le nom de l'employé qui a travaillé le plus  grand nombre d'heures en juillet 2000.

4. Donner le nom des départements qui gèrent des projets de plus de 5M FB de budget et des projets de moins de 5M FB de budget.

5. Donner le nom des responsables de département dont tous les employés du département ont travaillé en février 2000 pour plus d'un projet.

On vous demande d’exprimer en calcul relationnel tuple les requêtes 1, 2 et 3, et en calcul relationnel domaine les requêtes 4 et 5.