Exercice 01 :
Dans ce type d'énoncé, on donne les attributs et les DFs qui les lient. On vous demande ensuite de normaliser. Dans certains énoncés, cf. série 2 et 3, il faut chercher
sois-même ces attributs et/ou DFs. Ensuite on suppose que les domaines des attributs sont sous-entendus, i.e. simple à concevoir... Ici, Matricule est un attribut défini sur le numéro matricule d'élève (e.g. 9867,4 chiffres décimaux). On fera dorénavant cette hypothèse, sauf indication contraire.
1) Une dépendance fonctionnelle DF établit d'abord une relation entre donnée, en plus d'être fonctionnelle.
Matricule → Nom, Age, signifie qu'il y a d'abord la relation "
le matricule le nom et l'âge d'un certain élève" entre Matricule, Nom et Age. Ensuite, le nom et l'âge sontunique pour un élève identifié par un matricule.
Matricule → Club, signifie un élève est " inscrit" ou " participe" à un club donné. En plus, ce club est unique (un élève ne participe pas à plus d'un club).
Club → Salle, signifie qu'un club " a un local qui est une salle". Cette salle estunique, (aucun club ne dispose de plus d'un local).
2) Si maintenant on considère la relation ELEVE (Matricule, Nom , Age, Club, Salle) on peut dire que l'attribut Matricule est clé, car il détermine tous les autres attributs, y compris Salle (la DF Matricule → Salle est transitive). Cette relation est en 2FN, car aucun attribut non clé ne dépend d'une partie de la clé (la clé n'est pas composée d'ailleurs). Cette relation n'est pas en 3FN, car les attributs non clés ne sont pas mutuellement indépendants, à cause de la dépendance fonctionnelle Club → Salle. (Une autre façon de le dire, la DF par rapport à la clé Matricule → Salle, n'est pas directe mais transitive par le fait que, par hypothèses, Matricule → Club et que Club → Salle). Donc on projette le relation ELEVE pour que cette DF (Club → Salle) soit due à une clé (Club). ELEVE (Matricule, Nom, Age, Club) ACTIVITE (Club, Salle) qui sont deux relations en 3FN (car dans ELEVE, il n'y a plus de DFs entre attributs non clé). On retrouve la relation initiale par jointure des ces deux dernières relations, car Club est clé dans la deuxième relation.
Probleme :
1)
DECLARE
heure1 CHAR(5) := ‘21-40’; heure2 CHAR(5) := ‘23-10’; ville1 VARCHAR2(20) := 'Dschang'; ville2 VARCHAR2(20) := 'Paris';
BEGIN INSERT INTO Vol VALUES ('AF110', heure1, heure2, ville1, ville2);
END;
2)
SQL> ACCEPT s_nde PROMPT ‘Nombre d’escales : ’
DECLARE
numEscaleCourante Escales.Numescale%TYPE;
derniereEscale Escales.Numescale%TYPE;
escaleCourante Escales.Ville_escale%TYPE;
dureeEscaleCourante Escales.Duree_escale%TYPE;
prochaineDestination Vol.Ville_arrivee%TYPE;
numeroVol Vol.Numvol%TYPE;
heureDepart Vol.Heure_depart%TYPE;
destinationFinale Vol.Ville_arrivee%TYPE := ‘Paris’;
BEGIN
numEscaleCourante := 1;
derniereEscale := &s_nde;
IF (derniereEscale > 8) THEN
DBMS_OUTPUT.PUT_LINE(‘Au maximum 8 escales !’);
ELSIF (derniereEscale < 1) THEN
DBMS_OUTPUT.PUT_LINE(‘Au minimum 1 escale !’);
ELSE
LOOP
SELECT Ville_escale, Duree_escale
INTO escaleCourante, dureeEscaleCourante
FROM Escales WHERE (Numescale = numEscaleCourante);
IF (numEscaleCourante = derniereEscale) THEN
prochaineDestination := destinationFinale;
ELSE
SELECT Ville_escale INTO prochaineDestination
FROM Escales
WHERE (Numescale = numEscaleCourante + 1);
END IF;
SELECT Numvol, Heure_depart INTO numeroVol, heureDepart
FROM Vol WHERE (Ville_depart = escaleCourante AND
Ville_arrivee = prochaineDestination);
DBMS_OUTPUT.PUT_LINE(‘A partir de ’ || escaleCourante ||
‘ (durée’ || dureeEscaleCourante ||
‘ jours) prendre vol ’ || numeroVol ||
‘ à ’ || heureDepart);
numEscaleCourante := numEscaleCourante + 1;
EXIT WHEN (numEscaleCourante > derniereEscale);
END LOOP;
END IF;
END;
3)
DECLARE
CURSOR curseur1 IS SELECT Salaire FROM Pilote
WHERE (Age >= 30 AND Age <=40);
salairePilote Pilote.Salaire%TYPE;
sommeSalaires NUMBER(11,2) := 0;
moyenneSalaires NUMBER(11,2);
BEGIN
OPEN curseur1;
LOOP
FETCH curseur1 INTO salairePilote;
EXIT WHEN (curseur1%NOTFOUND OR curseur1%NOTFOUND IS NULL);
sommeSalaires := sommeSalaires + salairePilote;
END LOOP;
moyenneSalaires := sommeSalaires / curseur1%ROWCOUNT;
CLOSE curseur1;
DBMS_OUTPUT.PUT_LINE(‘Moyenne salaires (pilotes de 30 à 40 ans) : ’ ||
moyenneSalaires);
END;
4) SQL> ACCEPT s_nde PROMPT ‘Nombre d’escales : ’
DECLARE
nbMaxEscales NUMBER := 0;
escaleCourante Escales.Ville_escale%TYPE;
prochaineDestination Vol.Ville_arrivee%TYPE;
dureeEscaleCourante Escales.Duree_escale%TYPE;
numEscaleCourante Escales.Numescale%TYPE;
derniereEscale Escales.Numescale%TYPE;
volAPrendre Vol%ROWTYPE;
CURSOR curseur1 IS SELECT * FROM Vol
WHERE (Ville_depart = escaleCourante AND
Ville_arrivee = prochaineDestination);
BEGIN
numEscaleCourante := 1;
derniereEscale := &s_nde;
SELECT COUNT(*) INTO nbMaxEscales FROM Pilotes;
IF (derniereEscale > nbMaxEscales) THEN
DBMS_OUTPUT.PUT_LINE(‘Au maximum ’ || nbMaxEscales || ‘ escales !’);
ELSIF (derniereEscale < 1) THEN
DBMS_OUTPUT.PUT_LINE(‘Au minimum 1 escale !’);
ELSE
<<boucleEscales>>
LOOP
SELECT Ville_escale, Duree_escale
INTO escaleCourante, dureeEscaleCourante
FROM Escales WHERE (Numescale = numEscaleCourante);
IF (numEscaleCourante = derniereEscale) THEN
prochaineDestination := destinationFinale;
ELSE
SELECT Ville_escale INTO prochaineDestination
FROM Escales
WHERE (Numescale = numEscaleCourante + 1);
END IF;
OPEN curseur1;
FETCH curseur1 INTO volAPrendre;
IF (curseur1%NOTFOUND OR curseur1%NOTFOUND IS NULL) THEN
DBMS_OUTPUT.PUT_LINE(‘Aucun vol disponible de ’ ||
escaleCourante);
EXIT boucleEscales;
ELSE -- retourner au maximum 10 propositions de vol
WHILE (curseur1%FOUND AND curseur1%ROWCOUNT <= 10) LOOP
DBMS_OUTPUT.PUT_LINE(‘A partir de ’ ||
escaleCourante || ‘ (durée’ ||
dureeEscaleCourante ||
‘ jours) prendre vol ’ ||
volAPrendre.Numvol ||
‘ à ’ || volAPrendre.Heure_depart);
FETCH curseur1 INTO volAPrendre;
END LOOP;
END IF;
CLOSE curseur1;
numEscaleCourante := numEscaleCourante + 1;
EXIT WHEN (numEscaleCourante > derniereEscale);
END LOOP;
END IF;
EXCEPTION
WHEN TOO_MANY_ROWS THEN OR NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘La table des escales est mal définie !’);
END;
5)
PROCEDURE trouverEscales(villeBase IN Vol.Ville_depart%TYPE,
nbMinEscales IN INTEGER, nbMaxEscales IN INTEGER)
IS
FUNCTION tourValide(destCourante IN Vol.Ville_depart%TYPE,
villeDepart IN Vol.Ville_depart%TYPE, minEscales IN INTEGER, maxEscales IN INTEGER)
RETURN BOOLEAN
IS valeurRetour BOOLEAN := FALSE; quelVol Vol%ROWTYPE; CURSOR curseurVol IS SELECT * FROM Vol WHERE (Ville_arrivee = destCourante);
BEGIN FOR quelVol IN curseurVol LOOP IF (((quelVol.Ville_depart = villeDepart) AND (minEscales>0))
OR ((quelVol.Ville_depart != villeDepart) AND (maxEscales<1)))
THEN
NULL;
ELSIF (quelVol.Ville_depart = villeDepart) THEN DBMS_OUTPUT.PUT_LINE(‘De ’ || quelVol.Ville_depart || ‘ à ’ || destCourante || ‘ : ’ || quelVol.Numvol); valeurRetour := TRUE;
ELSIF tourValide(quelVol.Ville_depart, villeDepart, minEscales-1, maxEscales-1) THEN DBMS_OUTPUT.PUT_LINE(‘De ’ || quelVol.Ville_depart || ‘ à ’ || destCourante || ‘ : ’ || quelVol.Numvol); valeurRetour := TRUE;
END IF;
END LOOP; RETURN valeurRetour;
EXCEPTION WHEN OTHERS THEN RETURN FALSE;
END tourValide;
BEGIN
IF ((nbMinEscales > nbMaxEscales) OR (nbMaxEscales < 1)) THEN
DBMS_OUTPUT.PUT_LINE(‘Vérifier contraintes pour nombre d’escales’);
ELSIF tourValide(villeBase, villeBase, nbMinEscales, nbMaxEscales) THEN DBMS_OUTPUT.PUT_LINE(‘Faites votre choix !’);
ELSE DBMS_OUTPUT.PUT_LINE(‘Aucun tour valide n’a été trouvé !’);
END FOR;
END trouverEscales;
6) CREATE OR REPLACE PACKAGE paquetageEscales
AS
PROCEDURE proposerVols(nbEscales IN Escales.Numescale%TYPE);
PROCEDURE trouverEscales(villeBase IN Vol.Ville_depart%TYPE,
nbMinEscales IN INTEGER,
nbMaxEscales IN INTEGER);
END paquetageEscales;
CREATE OR REPLACE PACKAGE BODY paquetageEscales
AS
PROCEDURE proposerVols(nbEscales IN Escales.Numescale%TYPE)
IS
nbMaxEscales NUMBER := 0;
escaleCourante Escales.Ville_escale%TYPE;
prochaineDestination Vol.Ville_arrivee%TYPE;
dureeEscaleCourante Escales.Duree_escale%TYPE;
numEscaleCourante Escales.Numescale%TYPE;
volAPrendre Vol%ROWTYPE;
CURSOR curseur1 IS SELECT * FROM Vol
WHERE (Ville_depart = escaleCourante AND
Ville_arrivee = prochaineDestination);
BEGIN
numEscaleCourante := 1;
nbEscales:= &s_nde;
SELECT COUNT(*) INTO nbMaxEscales FROM Pilotes;
IF (nbEscales > nbMaxEscales) THEN
DBMS_OUTPUT.PUT_LINE(‘Au maximum ’ || nbMaxEscales || ‘ escales !’);
ELSIF (nbEscales < 1) THEN
DBMS_OUTPUT.PUT_LINE(‘Au minimum 1 escale !’);
ELSE
<<boucleEscales>>
LOOP
SELECT Ville_escale, Duree_escale
INTO escaleCourante, dureeEscaleCourante
FROM Escales WHERE (Numescale = numEscaleCourante);
IF (numEscaleCourante = nbEscales) THEN
prochaineDestination := destinationFinale;
ELSE
SELECT Ville_escale INTO prochaineDestination
FROM Escales
WHERE (Numescale = numEscaleCourante + 1);
END IF;
OPEN curseur1;
FETCH curseur1 INTO volAPrendre;
IF (curseur1%NOTFOUND OR curseur1%NOTFOUND IS NULL) THEN
DBMS_OUTPUT.PUT_LINE(‘Aucun vol disponible de ’ ||
escaleCourante);
EXIT boucleEscales;
ELSE -- retourner au maximum 10 propositions de vol
WHILE (curseur1%FOUND AND curseur1%ROWCOUNT <= 10) LOOP
DBMS_OUTPUT.PUT_LINE(‘A partir de ’ ||
escaleCourante || ‘ (durée’ ||
dureeEscaleCourante ||
‘ jours) prendre vol ’ ||
volAPrendre.Numvol ||
‘ à ’ || volAPrendre.Heure_depart);
FETCH curseur1 INTO volAPrendre;
END LOOP;
END IF;
CLOSE curseur1;
numEscaleCourante := numEscaleCourante + 1;
EXIT WHEN (numEscaleCourante > nbEscales);
END LOOP;
END IF;
EXCEPTION
WHEN TOO_MANY_ROWS THEN OR NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘La table des escales est mal définie !’);
END proposerVols;
PROCEDURE trouverEscales(villeBase IN Vol.Ville_depart%TYPE,
nbMinEscales IN INTEGER, nbMaxEscales IN INTEGER)
IS
…
END trouverEscales;
END paquetageEscales;
