Travaux dirigés de base de données

Base de données – Module I4
2006-2007
Base de données
Travaux dirigés de base de données
Module I4
Chronologie des séances de travaux dirigés et des travaux préparatoires.
Les travaux préparatoires sont à établir sur des feuilles séparées. Ils peuvent être demandés en
séance et faire l'objet d'une évaluation.
Séance 1
• Pas de travail préparatoire
• Sujet "compagnie aérienne"
• Sujet basket (graphe)
Travaux préparatoires séance 2
• Graphe du sujet parc informatique
• MCD sujet basket (si vu en cours)
• MLD-tables du sujet basket (si vu en cours)
Séance 2
• Sujet
• Sujet
o
o
o
o
Basket : MCD, MLD (correction rapide si vu en cours)
parc informatique
Correction graphe
MCD
MLD - tables
Gestion de l'intégrité référentielle et autres contraintes
Travaux préparatoires séance 3
• Graphe du sujet camping
Séance 3
• Sujet camping
o Correction graphe
o MCD
o MLD – tables
o
Travaux préparatoires séance 4
• Requêtes préparatoires au tp 3-4 (en fin de document)
Séance 4
• Sujet requêtes camping
Travaux préparatoires séance 5
• Sujet grossiste : Exercice 1
Séance 5
• Sujet grossiste
S. P.
Base de données – Module I4
2006-2007
Base de données
Dépendances fonctionnelles (DF) et graphe : 1er exercice
Notions abordées : dépendance fonctionnelle / élémentaire / directe
Exercice : compagnie aérienne
Les dépendances ci dessous sont relatives au cas d'une compagnie aérienne. Les rubriques sont :
N°vol
date
N°ligne
TempsEstimé
N°commandant
Nom
Numéro de vol (régulier et
journalier)
date du vol
Numéro de ligne aérienne
de circulation
Durée habituelle suivant la
ligne empruntée
Numéro du commandant de
bord
Nom du commandant de
bord
Adresse du commandant
de bord
Ancienneté du
commandant de bord
Numéro de l'avion
type de l'avion
capacité de l'avion
Heure de départ
habituelle
Heure d'arrivée
habituelle
Adresse
Ancienneté
N°avion
Type
Capacité
HeureDep
HeureArr
Un avion est piloté par un commandant de bord. Le commandant peut le même jour effectuer plusieurs
vols. Une liste de dépendance est données ci-dessous. Elles ne sont pas toutes fonctionnelles,
élémentaires ou directes.
(a) N°commandant
(b) N°commandant
(c) (date, N°vol)
(d) N°avion
(e) Date, n°vol
(f) (N°avion, N°vol)
(g) N°avion
(h) N°vol
(i) N°commandant
(j) (date, N°avion)
(k) (N° avion, type)
(l) N°ligne
(m) (N°vol, date)
(n) (date,N°vol)
(o) N°vol
(p) N°vol
(q) date,N°vol,N°ligne
(r) N°ligne
1.
Adresse
Nom
N°commandant
Type
TempsEstimé
N°ligne
Capacité
N°ligne
Ancienneté
N°vol
capacité
N°commandant
N°ligne
N°avion
HeureDep
HeureArr
N°avion
TempsEstimé
Déterminer les dépendances non-fontionnelles dans la liste ci-dessus. Justifier brièvement.
2. A partir des dépendances fonctionnelles (restantes), établir le graphe.
3. Déterminer les dépendances fonctionnelles (DF) non élémentaires. Justifier brièvement.
4. Déterminer les dépendances fonctionnelles élémentaires non directes. Justifier brièvement.
S. P.
Base de données – Module I4
2006-2007
Base de données
Equipe de basket
Notions abordées : conception, relation 1,2
On souhaite gérer les informations relatives à des équipes de basket.
CONCEPTION du graphe
1.
On s'intéressera uniquement au fonctionnement suivant : chaque équipe est composée de
joueurs. Les noms et prénoms des joueurs sont mémorisés. Pour une équipe on mémorisera la
ville et le nom, prénom de l'entraîneur. Les résultats des rencontres ne sont pas à prendre en
compte.
Proposer le graphe des DF.
2. On s'intéressera en plus aux résultats des rencontres. Pour chaque rencontre, on mémorise les
équipes ayant joué en distinguant celle qui reçoit, de celle qui est 'visiteur'. Pour chaque
rencontre, on retiendra le résultat, la date, le nombre de spectateurs.
On souhaite pouvoir connaître le résultat individuel de chaque joueur pour chaque match
(nombre de points marqués et le nombre de fautes). Chaque joueur ne joue pas forcément
toutes les rencontres.
Proposer le nouveau graphe des DF.
Modèles conceptuel et logique des données
1.
Etablir le modèle conceptuel des données. (MCD) correspondant à cette gestion des équipes de
baskets.
2. Proposer le modèle logique des données (tables) correspondant. Préciser les clés, clés
composées et clés étrangères.
3. On propose de simplifier les informations relatives à l'entraîneur : il n'y a pas d'homonyme, et
l'utilisation du seul nom de famille peut suffire. Déterminer les modifications à effectuer.
S. P.
Base de données – Module I4
2006-2007
Base de données
Parc informatique
Notions abordées : conception base de données
Le responsable d’un réseau de micro-ordinateurs souhaite mettre en place une base de données lui
permettant de gérer :
•
•
•
•
•
•
•
1.
Les postes de travail dont il dispose dans les différentes salles ; un poste de travail est décrit
par :
o un identifiant attribué par le système de gestion de base de données (SGBD) et qui ne
sera jamais visible,
o un nom de poste de travail,
o la salle dans laquelle le poste est installé.
Les ressources partagées (imprimantes, imprimantes couleur, scanner, etc.) installées
également dans différentes salles. Une ressource partagée est décrite par :
o un identifiant attribué par le système et qui ne sera jamais visible,
o un nom de ressource
o la salle dans laquelle le poste est installé.
Les groupes d’utilisateurs du réseau (superviseur, professeur, étudiant, invité) ; un groupe est
décrit par
o son nom
o un identifiant géré par le système.
Des autorisations d’accès aux postes de travail et aux ressources partagées sont données à
chaque groupe d’utilisateur.
Les utilisateurs du réseau. Un utilisateur est décrit par :
o un identifiant attribué par le système et qui ne sera jamais visible,
o un nom
o un prénom
o un nom sur le réseau, unique pour le réseau et constitué de 25 caractères alphabétiques
au maximum
o le groupe auquel il appartient.
Les autorisations d’accès à un poste de travail ou à une ressource partagée sont habituellement
données par le groupe d’appartenance. Toutefois, certains utilisateurs ont des autorisations
particulières d’accès, données par l’administrateur système pour accéder à certains postes ou
certaines ressources.
Les salles peuvent contenir des postes et/ou des ressources ou aucun de ces composants.
Donner le graphe des dépendances fonctionnelles correspondant à cette base et son
fonctionnement.
2. Donner le MCD correspondant.
3. Donner les tables correspondantes. Préciser les clés, clés composées et clés étrangères.
4. Indiquer les champs qui doivent être obligatoirement remplis.
5. A quel niveau l'intégrité référentielle intervient-elle ? Indiquer chaque fois qu'elle peut être
évoquée, comment l'intégrité référentielle doit être gérée.
6. Est-il possible de supprimer la table correspondant aux salles ?
Si oui, justifier. Si non, à quelle(s) condition(s) pouvait le faire ?
S. P.
Base de données – Module I4
2006-2007
Base de données
Camping
Notions abordées : conception base de données, fausse redondance
On s'intéresse à la gestion d'un camping.
L'application devra permettre de gérer les clients, leur séjour, les emplacements, la location
des chalets, bungalows, caravanes.
Chaque emplacement peut contenir un seul type de logement (chalet, bungalow, etc…).
Les clients peuvent demander une réservation d'un séjour qui leur sera confirmée plus tard par
le gérant. Les clients pourront consulter les logements qui les intéressent pour une période
donnée. Seuls les logements non réservés durant la période choisie seront affichés. Le client ne
pourra pas réserver un emplacement donné, mais uniquement le type, la date de début et la date
de fin de séjour (par exemple : chalet 4 places du 15 juin aaaa au 2 juillet aaaa, où aaaa
correspond à l'année considérée).
Le gérant pourra consulter les demandes de séjour. Il pourra alors valider ou non les séjours et
renseigner l'emplacement correspondant à chaque demande. Afin de facilité la gestion d'un
séjour, de la demande, jusqu'à son paiement, un séjour peut passer par ces 3 états :
-
Demande en cours
-
Accepté
-
Payé.
Les logements sont fournis avec le nécessaire au séjour. Chaque logement possède donc par
exemple une table, des chaises, des couteaux, etc…
Le gestionnaire doit avoir la possibilité de connaître la liste des objets normalement présents
dans un logement. Il peut également la mettre à jour (ajout ou suppression). Un logement peut
ainsi contenir au départ 12 verres. Si un client en casse 2, le logement n'en contient plus que 10.
Le gérant doit donc connaître le nombre de verres présent en début de séjour afin de pouvoir
facturer au client les verres cassés. Le prix de remplacement dépend de l'objet (ici un verre).
Si les verres ne sont pas remplacés, le gestionnaire ne doit pas réclamer les verres manquant au
client suivant.
Afin de faciliter les publipostages, le gérant devra avoir la possibilité d'établir la liste des
clients ayant séjournés sur une période choisie.
Différentes interrogations devront être possibles. Elles sont présentes dans le travail
préparatoire au TP5 et dans le sujet du TD6. Il est nécessaire de les prendre en compte dès la
conception.
1.
Proposer le graphe, MCD et MLD correspondant.
2. Donner les champs obligatoirement remplis et les champs où une valeur est autorisée qu'une
seule fois.
3. Donner la gestion de l'intégrité référentielle chaque fois que cela est nécessaire.
S. P.
Base de données – Module I4
2006-2007
Base de données
Requêtes (camping)
Notions abordées : requêtes
Rappel des tables :
TYPES (IdType, NomType)
EMPLACEMENTS (IdEmplacement,RefType, NomEmplacement)
OBJETS (IdObjet, Designation, PrixRemplacement)
INVENTORIER (IdObj, IdEmpl, Qte)
VILLES (IdVille, NomVille, Cp)
CLIENTS (IdClient, NomClient, PrenomClient, Rue, RefVille)
ETATS (IdEtat, Etat)
SEJOURS (IdResa, RefClient, DateDebut, DateFin, RefTypeSouhaite, RefEtat,
RefEmplacement, MontantAccompteDemande, MontantAccompteVerse, MontantTotal)
Exercice 1 :
Etablir le code SQL correspondant aux requêtes ci-dessous :
1.
Donner, pour un identifiant d'emplacement donné (par exemple 1) la quantité d'objets
inventoriés (autrement dit, le nombre total d'objet).
2. Donner pour un identifiant de type de logement donné (par exemple 3), le nombre total
d'objets inventoriés. Autrement dit, le quantité totale d'objets des emplacements
correspondant à ce type.
3. Donner pour un identifiant de type de logement donné (par exemple 3), l'identifiant et le nom
des emplacements possédant ce type.
4. Donner pour un identifiant de type de logement donné (par exemple 3), le nombre total
d'objets inventoriés par emplacement. Autrement dit, la quantité d'objets par emplacement
possédant ce type. Le résultat donnera l'identifiant des emplacements. (S'inspirer des 2
requêtes précédentes).
5. Donner le nombre total d'objets inventoriés par emplacement. Autrement dit, la quantité
d'objets par emplacement. Le résultat donnera l'identifiant des emplacements.
6. Donner le nombre total d'objets inventoriés par emplacement qui font l'objet de séjours
programmé (passés ou à venir). Autrement dit, la quantité d'objets par emplacement évoqués
par un séjour programmé. Le résultat donnera l'identifiant des emplacements.
Note : on considérera qu'un séjour est programmé à partir du moment où on lui a affecté un
emplacement.
Exercice 2 :
Etablir le code SQL correspondant à la requête ci-dessous :
Donner les noms des emplacements disponibles pour un type donné (par exemple 3) et pour une
période donnée (par exemple du 20 mai aaaa ('May 20, aaaa') au 3 juin aaaa ('June 3,aaaa') où aaaa
représente l'année considérée.
S. P.
Base de données – Module I4
2006-2007
Base de données
Grossiste
Notions abordées : conception, cas particulier CP et ville
Exercice 1
On souhaite construire la base de données d’une entreprise de type "grossiste" qui achète des
produits à différents fournisseurs et les revend à ses clients.
Les fournisseurs pourront recevoir des courriers ou des appels téléphoniques. De même pour les
clients.
Un client peut passer commande de différents produits. S'il ne connaît pas les références du
produit, il indique la désignation. S’ils sont en stocks, le grossiste les lui livre. S’ils ne sont pas
en stock, le grossiste les lui livrera plus tard ; cependant, le grossiste doit avoir la possibilité
d'interroger la base de données pour connaître les produits commandés mais non encore livrés,
par client.
La stratégie d’achat auprès des fournisseurs n’est pas automatisée : c’est le grossiste qui, en
fonction de ses choix personnels, va choisir tel ou tel fournisseur pour un produit (un même
produit pouvant être fournit par plusieurs fournisseurs). Afin de l’aider à choisir les
fournisseurs, le grossiste doit connaître les délais chez un fournisseur donné. Ce fournisseur
peut avoir des délais différents suivant les produits. De plus, chaque fournisseur pratique des
prix libres. Le grossiste doit connaître ces prix.
1.
Etablir le graphe des dépendances fonctionnelles. Vérifier que les dépendances fonctionnelles
sont élémentaires et directes.
2. Etablir le modèle conceptuel des données - MCD - correspondant.
3. Proposer le modèle logique des données – MLD - (tables) correspondant. Préciser les clés, clés
composées et clés étrangères.
4. Indiquer les champs qui doivent être obligatoirement remplis.
5. A quel niveau l'intégrité référentielle intervient-elle ? Indiquer chaque fois qu'elle peut être
évoquée, comment l'intégrité référentielle doit être gérée.
Exercice 2
Il existe un lien entre le code postal et la ville. Quels sont les intérêts à intégrer ce lien ?
Modifier le graphe correspondant à l'étude du grossiste en conséquence.
Modifier le MCD et le MLD correspondants.
S. P.
Base de données – Module I4
2006-2007
Exercice 3
Etablir les opérations relationnelles des différentes requêtes d'interrogation permettant
d'obtenir les souhaits exposés ci-dessous. Puis donner le code SQL correspondant.
1.
Pour un produit P, connaître l'ensemble des fournisseurs et leur délai de livraison.
a.
P est l'identifiant du produit
b. P est la désignation du produit.
2. Pour une commande C, connaître l'ensemble des informations permettant d'établir le bon de
commande.
3. Connaître l'ensemble des produits disponibles dans un délai de N jours.
4. Afin d'établir un courrier, on souhaite connaître l'ensemble des clients d'un département D qui
dont la dernière commande date de plus de N jours.
5. Une requête SQL permet de calculer le montant de la commande C.
Si les prix des produits augmentent une fois la commande réalisée, que retourne la requête
SQL ? Facultatif : donnez cette requête.
6. Modifier le graphe pour que le montant d'une commande puisse être obtenu.
a.
Donner les modifications à apporter au graphe.
b. Donner les modifications à apporter au MCD.
c.
Donner les modifications à apporter au MLD.
Quels sont les avantages de créer une rubrique que l'on remplirait avec le résultat d'un calcul ;
par exemple le montant d'une commande ? Autrement dit, pourquoi basculer une donnée
calculée dans le dictionnaire des données élémentaires ? Quels sont les inconvénients ?
Rappel : le dictionnaire des données élémentaires correspond aux rubriques du graphe.
S. P.
Base de données – Module I4
2006-2007
Base de données
TRAVAIL PREPARATOIRE AUX TP 3-4
A partir des tables suivantes correspondant à un camping (voir explication sujet "camping") :
TYPES (IdType, NomType)
EMPLACEMENTS (IdEmplacement,RefType, NomEmplacement)
OBJETS (IdObjet, Designation, PrixRemplacement)
INVENTORIER (IdObj, IdEmpl, Qte)
VILLES (IdVille, NomVille, Cp)
CLIENTS (IdClient, NomClient, PrenomClient, Rue, RefVille)
ETATS (IdEtat, Etat)
SEJOURS (IdResa, RefClient, DateDebut, DateFin, RefTypeSouhaite, RefEtat,
RefEmplacement, MontantAccompteDemande, MontantAccompteVerse, MontantTotal)
1.
Déterminer le nombre d'objet différents référencés (la quantité de référence d'objet et non
pas la quantité des objets que le camping possède).
2. Sélectionner tous les articles ayant un prix de remplacement supérieur à 15 euros. Afficher
leur nom (designation) et leur prix, trié par ordre alphabétique.
3. Sélectionner tous les articles dont le prix est supérieur à celui dont l'identifiant est donné (par
exemple 4). Afficher leur nom et leur prix, trié par ordre décroissant de prix.
4. Donner les prénoms et noms des clients ayant obtenu l'emplacement dont l'identifiant est
donné (par exemple 1).
5. Donner, pour chaque client (idclient), la somme des montants des séjours (payés par les clients).
6. Donner la moyenne des montants des séjours payés par les clients (le prix payé est une donnée
élémentaire non-calculée automatiquement, mais entrée manuellement par le gestionnaire du
camping ; il peut ainsi faire des remises…).
7. Donner la liste des identifiants des clients dont la somme des montants payés par ceux-ci sur
l'ensemble de leur séjours est supérieure à la moyenne des montants des séjours.
8. Donner le prénom et le nom des clients correspondant à la requête précédente.
S. P.