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 :
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.
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
responsableDépart référence Employé.noMatricule
Projet(nomProjet, nomDépart, budgetProjet, dateDébut)
nomDépart référence Département.nomDépart
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'.
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.
Article plus récent Article plus ancien
