Correction examen SGBD 2014-2015

 Administration

1- Est-ce que l'administrateur de la base de données peut voir les données en train d'être

modifiées dans une transaction par les utilisateurs de la base ? NON

2- Peut-on annuler partiellement une transaction ? Indiquer quelle est la commande qui

permet de le faire. OUI

3- Vous avez ouvert deux sessions avec le même utilisateur. Dans la première session vous modifiez un enregistrement d'une table. Est-ce que dans la deuxième session, connectée avec le même utilisateur, vous pouvez voir la modification effectuée dans l'autre session ? NON

4- Parmi les commandes SQL suivantes :  INSERT  ALTER  CREATE  DROP  TRUNCATE  DELETE  UPDATE a. Quelles sont les commandes qui peuvent être annulées dans une transaction ? INSERT, DELETE, UPDATE

b. Quelles sont les commandes qui valident automatiquement une transaction ? ALTER, CREATE, DROP, TRUNCATE

5- Quand le processus DataBaseWriter « DBWn » écrit-il les données dans les fichiers de

données ?

A. Après chaque validation de la transaction B. Avant valider la transaction C. Après le processus « LGWR » D. Avant ou après la validation de la transaction

6- Quels sont les fichiers mis à jour par le processus DataBaseWriter «DBWn» pour écrire les blocs modifiés ?

A. Les fichiers de données B. Les fichiers de données et les fichiers de contrôles C. Les fichiers de données et les fichiers journaux D. Les fichiers journaux et les fichiers de contrôles 7- Qu'est-ce qui nous permet de récupérer les données qui n'ont pas été mises à jour dans les fichiers de données suite à l'arrêt brutal du serveur ?

A. Les fichiers journaux B. Les segments UNDO C. Le tablespace « SYSTEM »

8- Quelle est la vue du dictionnaire de données qui vous permet d'afficher la liste de tous les utilisateurs de la base de données et leurs caractéristiques ?

_ DBA_USERS _ USER_USER _ ALL_USER _ V$SESSION

9- Quelle est la vue qui vous permet d'afficher le nom de toutes les vues du dictionnaire de données?

 DBA_NAMES  DBA_TABLES  DBA_DICTIONARY  DICTIONARY

10- Vous avez besoin d'arrêter la base de données, vous avez demandé à l'ensemble des utilisateurs de la base de données de fermer leur session. Il reste un seul utilisateur qui effectue des manipulations critiques de la base de données.

Quel est le mode d'arrêt de la base de données que vous devez choisir ?

 SHUTDOWN  SHUTDOWN ABORT  SHUTDOWN NORMAL  SHUTDOWN IMMEDIATE  SHUTDOWN TRANSACTIONAL

10- L’utilisateur est verrouillé après cinq échecs de connexion. SQL> ALTER PROFILE DEFAULT 2 LIMIT 3 FAILED_LOGIN_ATTEMPTS 5 4 PASSWORD_LIFE_TIME 60 5 PASSWORD_REUSE_TIME 1800 6 PASSWORD_REUSE_MAX UNLIMITED 7 PASSWORD_LOCK_TIME 1/1440 8 PASSWORD_GRACE_TIME 10 9 PASSWORD_VERIFY_FUNCTION DEFAULT; Combien de temps doit-on attendre avant de pouvoir se reconnecter de nouveau ? Justifiez votre réponse en précisant quel est le paramètre qui indique le temps pendant lequel l’utilisateur ne peut pas se connecter

 1 MINUTE  5 MINUTES  10 MINUTES  14 MINUTES  18 MINUTES  60 MINUTES

 SQL et PL/SQL

Partie I

Écrivez les commandes SQL permettant de créer :

1- La vue LogicielsUnix qui contient tous les logiciels de type 'Unix' (toutes les colonnes

sont conservées).

Create view logicielsUnix as Select * from logiciel Where upper (typeLog)='UNIX';

2- La vue Poste_0 de structure (nPos0, nomPoste0, nSalle0, TypePoste0, indIP, ad0) qui contient

tous les postes du rez-de-chaussée (etage=0 au niveau de la table Segment).

Create view Poste_0 (nPos0, nomPoste0, nSalle0, TypePoste0, indIP, ad0) As select p.nPoste, p.nomPoste, p.nSalle, p.typePoste, p.indIP, p.ad from poste p, segment s where p.indIP=s.indIP and s.etage=0;

3- Créez la vue SallePrix de structure (nSalle, nomSalle, nbPoste, prixLocation) qui contient les salles et leur prix de location pour une journée (fonction du nombre de postes). Le montant de la location d’une salle à la journée sera calculé sur la base de 100 € par poste.

Create view SallePrix (nSalle, nomSalle, nbPoste, prixLocation) As Select s.nSalle, s.nomSalle, s.nbPoste, s.nbPoste*100 From salle s;

4- Ecrire une procédure pl/sql qui permet d’afficher les salles dont le prix de location

dépasse 150 €.

create or replace procedure affiche is Cursor cur is Select * from SallePrix Where prixLocation>150; Begin DBMS_OUTPUT.PUT_LINE('Les salles dont le prix de location est supérieur à 150€'); For c in cur loop DBMS_OUTPUT.PUT_LINE(c.nSalle||' '||c.nomSalle||' '||c.nbPoste||' '||c.prixLocation); End loop; Exception when others then DBMS_OUTPUT.PUT_LINE(SQLERRM); end;

5- Ecrire un bloc pl/sql qui affiche les cinq salles les plus économiques à la location

(si elles existent : si le nombre de salle est inférieur à cinq alors afficher toutes les salles).

Set serveroutput on; Declare Cursor cur is Select * from SallePrix Order by prixLocation; i number:=1; C cur%RowType; Begin DBMS_OUTPUT.PUT_LINE('Les cinq salles les plus économiques à la location'); Open cur; Loop Fetch cur into c; Exit when cur%NotFound or i>5; DBMS_OUTPUT.PUT_LINE(c.nSalle||' '|| c.nomSalle||' '||c.prixLocation); i:=i+1;

end loop;

Exception when others then DBMS_OUTPUT.PUT_LINE(SQLERRM); end;

Partie II

1- Écrivez le bloc PL/SQL qui saisit un numéro de salle et un type de poste, et qui

retourne un message indiquant les nombres de postes et d’installations de logiciels correspondantes sous la forme suivante :

Set serveroutput on ; Declare ns salle.nSalle%Type:='&NumSalle'; tp Poste.typePoste%Type:='&TypePoste' ; np number ; ni number ; begin select count(p.nPoste) into np from poste p where p.nSalle=ns and upper(p.typePoste)=upper(tp); select count(i.numIns) into ni from installer i, poste p where i.nPoste=p.nPoste and upper(p.typePoste)=upper(tp) and p.nSalle=ns; DBMS_OUTPUT.PUT_LINE('Numéro de Salle : '||ns); DBMS_OUTPUT.PUT_LINE('Type de poste : '||tp) ; DBMS_OUTPUT.PUT_LINE('G_NBPOSTE'); DBMS_OUTPUT.PUT_LINE('----------'); DBMS_OUTPUT.PUT_LINE(np); DBMS_OUTPUT.PUT_LINE('G_NBINSTALL'); DBMS_OUTPUT.PUT_LINE('-----------') ; DBMS_OUTPUT.PUT_LINE(ni);

Exception when others then DBMS_OUTPUT.PUT_LINE(SQLERRM); end;

2- On désire connaître, pour chaque logiciel installé, le temps (nombre de jours) passé entre l’achat et l’installation. Ce calcul devra renseigner la colonne delai de la table Installer pour l’instant nulle.

Les résultats devront être affichés ainsi que les incohérences (date d’installation antérieure à la date d’achat, date d’installation ou date d’achat inconnue).

Écrivez la procédure calculTemps pour programmer ce processus. Un exemple d’état de sortie est présenté ci-après :

create or replace procedure calculTemp is Cursor cur is Select l.nLog, dateAch, nomLog, p.nPoste, dateIns,nomPoste From logiciel l, poste p, installer i Where l.nLog=i.nLog And p.nPoste=i.nPoste; begin for c in cur loop if (c.dateAch is null) then DBMS_OUTPUT.PUT_LINE ('Date d'achat inconnue pour le logiciel '||c.nomLog||' sur le '|| c.nomPoste) ; Elsif (c.dateIns is null) Then DBMS_OUTPUT.PUT_LINE('Pas de date d''installation pour le logiciel '||c.nomLog||' sur le '|| c.nomPoste) ; Elsif (c.dateAch>c.dateIns) Then DBMS_OUTPUT.PUT_LINE('Logiciel '||c.nomLog||' installé sur Poste '|| c.nomPoste|| ' '|| (c.dateAch-c.dateIns) ||'jour(s) avant d''être acheté!') ; Else DBMS_OUTPUT.PUT_LINE('Logiciel '||c.nomLog||'sur Poste '||c.nPoste|| ', attente '|| (c.dateIns- c.dateAch) || ' jour(s).'); Update installer set delai=c.dateIns-c.dateAch where nLog=c.nLog and nPoste=c.nPoste; commit; end if; end loop; exception when others then DBMS_OUTPUT.PUT_LINE(SQLERRM);

end;

3- Écrivez le déclencheur Trig_Après_DI_Installer sur la table Installer permettant de faire la mise à jour automatique des colonnes nbLog de la table Poste, et nbInstall de la table Logiciel.

Prévoir les cas de désinstallation d’un logiciel sur un poste, et d’installation d’un logiciel sur un autre.

create or replace trigger Trig_Après_DI_Installer After insert or delete on installer For each row Begin If inserting Then Update exam_Mai2015_poste set nbLog=nbLog+1

Where nPoste=:new.nPoste; Update logiciel Set nbPoste=nbPoste+1 Where nLog =:new.nLog; Commit; Elsif deleting then Update poste Set nbLog=nbLog-1 Where nPoste=:new.nposte; Update logiciel Set nbPoste=nbPoste-1 Where nLog=:new.nLog; Commit; End if; exception when others then DBMS_OUTPUT.PUT_LINE(SQLERRM); End;