CONSERVATOIRE  NATIONAL  DES  ARTS  ET METIERS

Samedi 07/02/20092 heures

10h30 – 12h30

Système de gestion de base de données

NFP107

EXAMENPremière session

Documents autorisés : Oui

Centre d’Enseignement à DistanceMoreau Laurent

Le sujet doit impérativement être remis dans la copie

En cas de problème, vous pouvez contacter :Secrétariat EAD 03.26.36.80.20Assistance technique : 03.26.36.80.07

Exercice 1   : (6 pts) Soit la base de données suivante de la maternité e-choux :

ADMISSIONS (N°mère, NomMère, PrénomMère, Adresse)NAISSANCES (N°Bébé, Sexe, Prénom, Nom, Poids, Taille, HeureNaissance, JourNaissance, MoisNaissance,

AnnéeNaissance, #N°Mère)STATISTIQUES (Mois, Année, NombreFilles, NombreGarçons, PoidsMoyenFilles, PoidsMoyenGarçons)

Ecrire les requêtes SQL suivantes :A. Quels sont les mois où il naît plus de filles que de garçons ?SELECT Mois, Année FROM StatistiqueWHERE NombreFilles > NombreGarconsB. Quelles mères (numéro) ont eu des naissances multiples ?Select N°Mere, Count(Nom) As NB From Naissances Where Nb > 1 Group By N°MereC. Quel est le garçon le plus grand né en 2008 ?SELECT max(Taille), Prénom FROM Naissances WHERE Sexe = M and AnnéeNaissance = 2008D. Quels sont les bébés qui ont le même prénom que leur mère ?SELECT N°Bébé, Prénom, PrénomMère FROM Naissances, Admissions WHERE N°Bébé.Prénom = Admissions.PrénomMère AND…E. Combien de bébés a eu Madame Dupont Mélanie à la maternité e-choux ?SELECT Count(*) FROM Naissances, Admissions WHERE Naissances.NomMère = « Dupont » AND Admissions.PrenomMère = « Mélanie » AND …F. Quels sont les prénoms de filles plutôt rares (c'est-à-dire donnés moins souvent que la

moyenne) ?SELECT Distinct Prénom, count (Distinct Prenom) As Nb From Naissances Where Sexe = F and Nb < (Select AVG(Distinct Prenom) From Statistiques)

Exercice 2   : (4 pts) On considère la relation suivante décrivant des voitures :

R(N°Immat, Puissance, Marque, Pays, Agence, ChiffreAffaire)

Soient les hypothèses suivantes : Chaque véhicule est caractérisé par une puissance et une marque Une marque est spécifique d’un pays Le chiffre d’affaire fait référence à une agence pour une marque donnée Une agence peut représenter plusieurs marques

Questions :A. Quel est la clé de la relation ? N°Immat, ChiffreAffaireB. En quelle forme normale est cette relation ? Pourquoi ? 2NFC. Donner des exemples de dépendances réflexives et transitives. Cf coursD. Tracer le graphe des dépendances fonctionnelles.

Exercice 3   : (5 pts) On dispose de :

CREATE TABLE Dept(N°Dept Numeric NOT NULL PRIMARY KEY,Nom Varchar(25),N°Région Numeric NOT NULL);

CREATE TABLE Emp(N°Emp Numeric NOT NULL PRIMARY KEY,Nom Varchar(25),Prénom Varchar(25),

Embauche Date,N°Sup Numeric,Titre Varchar(25),N°Dept Numeric NOT NULL,Salaire Numeric,TxComission Numeric,CONSTRAINT emp_nodept_fk FOREIGN KEY (N°Dept) dept (N°Dept));

A. Ecrire une fonction département qui admette un numéro d’employé en paramètre et qui renvoie comme résultat le nom du département de l’employé

CREATE OR REPLACE FUNCTION departement (integer) RETURNS VARCHAR AS ‘DECLARE

dpt varchar;BEGIN

SELECT INTO dpt d.nom FROM emp e, dpt dWHERE e.nodept = d.nodept and noemp = $1;

RETURN dpt;END;‘LANGUAGE plpgsql;

Une table ayant été créée, son propriétaire peut accorder ou retirer à un autre utilisateur les droits suivants : Select, Insert, Update, Delete, Rule, All.

B. Accorder les droits de Insert et Update sur DEPT à un autre user.GRANT INSERT, UPDATE ON DEPT TO <User>C. Créer une vue relative à toutes les informations des employés du service 50. Puis accorder

tous les droits sur la vue d’un autre userCREATE VIEW EMP50 AS SELECT * FROM EMP WHERE NODEPT = 50 ;GRANT ALL ON EMP50 To <User>D. Retirer le droit d’Insert à l’autre user sur la table Dept.REVOKE INSERT ON DEPT FROM <User>

Exercice 4   : (5 pts) On souhaite archiver toutes les commandes d’un mois donné, qui n’est pas le mois en cours, dans

une table d’archive.

CREATE TABLE ArchivesCommandes(NumCommande Integer Primary Key,NumClient Integer,Montant Numeric) ;

Pour cela, on doit : Vérifier que le mois en cours n’est pas le mois pour lequel l’archive est demandée Calculer les montants de ces commandes Ajouter des éléments pour chacune de ces commandes dans la table prévue à cet effet  :

ArchivesCommandes Supprimer toutes les lignes des tables Procom et Commandes reliés à cette table via le

champ NumCommande (le nom des champs sources sont identiques)

A. Donner la fonction PL/pgSQL correspondante

DECLARE Moisarchive ALIAS FOR $1 ;Anneearchive ALIAS FOR $2 ;Com commandes%ROWTYPE ;

BEGINIF Anneearchive = EXTRACT(YEAR FROM now()) AND Moisarchive = EXTRACT(MONTH FROM

now()) THENELSE

FOR com IN SELECT *FROM commandesWHERE Anneearchive = EXTRACT (YEAR FROM date)AND moisarchive = EXTRACT (MONTH FROM date)

LOOP

INSERT INTO archivescommandes VALUES (com.numcommande, com.refclient, com.date, montantcommande (com.numcommande));

DELETE FROM procom WHERE refcommande = com.numcommande;DELETE FROM commandes WHERE numcommande=com.numcommande;

END LOOP;END IF;RETURN;

END;