Institut National des Télécommunications
Contrôle des connaissances
|
Bases de données |
Code : BD21 |
|
Date : 12/11/2007 |
Durée : 1h30 |
|
Coordonnateur : S. Tata et C. Lecocq |
Documents autorisés: ceux distribués en cours |
____________________________________________________
Avertissements
1/ Lisez attentivement le sujet.
2/ Les questions sont indépendantes les unes des autres.
3/ Essayez d'être clair et précis dans vos réponses.
4/ Soignez la présentation, dans la mesure du possible.
5/ Barème indicatif
Question 1 : 4 points (Q1 = 4 pts)
Question 2 : 4 points (Q2.1 = 2 pts, Q2.2 = 2 pts)
Question 3 : 5 points (Q3.1 = 2 pts, Q3.2 = 1 pts, Q3.3 = 2 pts)
Question 4 : 2 points
Question 5 : 2 points (Q5.1 = 1 pt, Q5.2 = 1 pt)
Question 6 : 3 points (Q6.1 = 1 pt, Q6.2 = 1 pt, Q6.3 = 1 pt)
Un garage effectuant la réparation et l’entretien de véhicules souhaite disposer d’un système d’information pour gérer les actes effectués sur les véhicules et les clients correspondants. Pour cela, on a défini le schéma relationnel de base de données suivant :
Proprietaire (nom, prenom, rue, codePostal, ville)
Vehicule (plaqueImmatriculation, type, couleur, nomP, prenomP)
Composant (nomComposant, com_nomComposant, description)
Acte (nomComposant, nomActe, prixNominal, typeActe)
EffectuerActeSur (plaqueImmatriculation, nomActe, nomComposant, date, prix)
Les clés sont soulignées et les clés étrangères sont en gras.
La sémantique des diverses relations est la suivante :
Proprietaire (nom, prenom, rue, codePostal, ville) : un propriétaire de véhicule est identifié par un nom et un prénom. Son adresse se décompose en rue, codePostal et ville.
Vehicule (plaqueImmatriculation, type, couleur, nomP, prenomP): un véhicule est identifié par le numéro de la plaque d’immatriculation. Il possède un type (‘Voiture’, ‘Moto’, ‘Camion’), une couleur. Le nom et le prénom du propriétaire du véhicule sont mentionnés. Nom et prenom sont des clés étrangères sur Proprietaire.
Composant (nomComposant, com_nomComposant, description) : un composant de véhicule est identifié par nomComposant (‘filtre à air’ par exemple). Lorsqu’il est une partie de composant (‘Moteur’ par exemple), le composant supérieur est référencé par com_nomComposant. Com_nomComposant est une clé étrangère sur Composant. Le composant possède une description.
Acte (nomComposant, nomActe, prixNominal, typeActe) : un acte effectué par un garagiste est identifié par le nom de l’acte et le composant concerné par l’acte. nomComposant est une clé étrangère sur composant. Un acte à un prix nominal (prix indicatif) et un type (‘Réparation’ ou ‘Entretien’).
EffectuerActeSur (plaqueImmatriculation, nomActe, nomComposant, date, prix) : un acte est effectué sur un véhicule. Un acte est identifié par la plaque d’immatriculation du véhicule, le nom de l’acte et le composant concerné par l’acte. plaqueImmatriculation est une clé étrangère sur Vehicule, nomActe et nomComposant est une clé étrangère sur Composant. Un acte est effectué à une date donnée et à un prix. L’historique d’un acte sur un véhicule n’est pas conservé : si plusieurs vidanges moteurs sont faite (à des dates différentes) sur le même véhicule, seules les informations sur la dernière vidange sont conservées.
Question 1
En vous aidant des règles de passage d'une modélisation Entité/association à une modélisation relationnelle "à l'envers", proposer un schéma Entité/Association équivalent à ce schéma relationnel. Ce schéma devra comporter la description des entités (avec leurs propriétés), des entités faibles (s'il y a lieu), des associations (avec leurs propriétés) ainsi que les cardinalités minimum et maximum des associations binaires.
Question 2
Exprimer chacune des 2 questions suivantes, d'une part en algèbre relationnelle sous la forme d'un arbre algébrique, et d'autre part en SQL.
(Q2.1) Quels sont les actes (nom d’acte et nom de composant) à plus de 100 euros (prix nominal) ?
(Q2.2) Quels sont les actes effectués (nom d’acte et nom de composant) sur des bielles (nom de composant) de moteurs (nom du composant supérieur) ?
Question 3
Exprimer les 3 questions suivantes en SQL :
(Q3.1) Pour chaque composant, donner son nom, sa description et le chiffre d’affaire total des actes effectués en novembre 2007. Le composant n’apparaît que si actes effectués au moins ont concerné ce composant. Il est possible d’écrire : date > ‘01/11/2007’ par exemple
(Q3.2) Quels sont les véhicules (plaque immatriculation et type de véhicule) qui ne sont qu’entretenus (jamais réparés) ?
(Q3.3) Quels sont les propriétaires (nom et prénom) qui ont effectués le plus grand nombre d’acte sur un de leur véhicule ?
Question 4
Exprimer la question suivante en algèbre relationnelle ou en SQL :
(Q4) Quels sont les actes (nom d’acte et nom de composant) effectués sur tous les véhicules ?
Question 5
(Q5.1) Ecrire l’ordre SQL de création de la vue syntheseActe qui donne pour chaque acte son nom, le nom du composant concerné, le nombre d’acte effectués et le chiffre d’affaire correspondant.
(Q5.2) En interrogeant la vue que vous venez de créer, répondre à la requête suivante : Quel est l’acte (nom d’acte et nom de composant) le plus courant et sa part dans le chiffre d’affaire global (CA de l’acte/CA total) ?
Question 6
Considérez la table proprietaire suivante dans la base de données
|
prénom |
nom |
rue |
codePostal |
ville |
|
Guy |
Tarembois |
Quincampoix |
75000 |
Paris |
|
Sarah |
Longe |
Paradis |
69000 |
Lyon |
|
Harry |
Covert |
Fourier |
91000 |
Evry |
Considérez le début de script PHP suivant qui opère sur la table personnes et dans lequel se sont glissées des erreurs.
include('./init-ado.php); # chargement du code de ADOdb
$connexion = &ADONewConnexion('mysql');
$c=$connexion->Connect (‘inf-6820.int-edu.eu’,’A00’,’A00’,’A00’,);
$rs=$connexion->Execute("SELECT * proprietaire");
Considérez enfin la deuxième partie de ce script.
$i=0;
while ($i<$rs->FieldCount()) {$ff= $rs->FetchField($i); echo $ff->name; echo "-"; $i=($i+1);}
$arr== $rs->FetchRow();
$i=0;
while ($i<$rs->FieldCount()) {echo $arr[$i];echo " - ";$i=($i+1);}
Questions
(Q6.1) Donnez une version corrigée de la première partie du script.
(Q6.2) Qu'affiche la ligne 6 ?
(Q6.3) Qu'affiche la ligne 9 ?