CONSERVATOIRE NATIONAL DES ARTS ET METIERS
Samedi 11 Février 2006
2 heures
10 H 45 – 12 H 45
SYSTEMES DE GESTION DE BASES DE DONNEES
Code UE NFP107
EXAMEN
Première session
Documents autorisés : aucun
Le sujet doit impérativement être remis dans la copie
1/6
Centre d’Enseignement à Distance
Laurent MOREAU
CONSERVATOIRE NATIONAL DES ARTS ET METIERS
PARTIE 1 : Création de schéma (5 points)
Le Modèle Conceptuel de Données suivant permet de modéliser le domaine de gestion d’un parc informatique
Travail à réaliser :
1. Donnez les scripts de création de tables correspondant à ce schéma.
Il ne fallait pas oublier d’insérer les clés étrangères dans les bonnes tables
CREATE TABLE reparation (
id_reparation uniqueidentifier NOT NULL,
date_envoi datetime,
date_ret datetime,
prix decimal,
id_panne uniqueidentifier,
id_tiers uniqueidentifier );
ALTER TABLE reparation ADD CONSTRAINT PK_reparation PRIMARY KEY (id_reparation);
ALTER TABLE reparation ADD CONSTRAINT FK_reparation_id_panne FOREIGN KEY (id_panne)
REFERENCES panne (id_panne);
2/6
Centre d’Enseignement à Distance
Laurent MOREAU
CONSERVATOIRE NATIONAL DES ARTS ET METIERS
ALTER TABLE reparation ADD CONSTRAINT FK_reparation_id_tiers FOREIGN KEY (id_tiers) REFERENCES
tiers (id_tiers);
2. Expliquez les choix de modélisation effectués par l’analyste. Vous concentrerez vos explications sur les
choix de cardinalités.
Explication des cardinalités particulières 1,1 ou 0,1
PARTIE 2 : SQL (5 POINTS)
Vous disposez d’un script de création de tables
-- CREATION DE TABLES
CREATE TABLE menu_type (
id_menu uniqueidentifier NOT NULL,
libelle_aliment_compose varchar(25),
qte_commandee decimal ,
code_espece uniqueidentifier,
CONSTRAINT PK_menu_type PRIMARY KEY (id_menu),
CONSTRAINT FK_menu_type_code_espece FOREIGN KEY (code_espece) REFERENCES espece
(code_espece)
);
CREATE TABLE element_de_base (
code_element uniqueidentifier NOT NULL,
libelle_element varchar(50),
CONSTRAINT PK_element_de_base PRIMARY KEY (code_element)
);
CREATE TABLE espece (
code_espece uniqueidentifier NOT NULL,
libelle_espece varchar(50),
CONSTRAINT PK_espece PRIMARY KEY (code_espece )
);
CREATE TABLE animal (
nom_bapteme uniqueidentifier NOT NULL,
sexe varchar(5),
date_naissance datetime,
date_deces datetime,
code_espece uniqueidentifier,
CONSTRAINT PK_animal PRIMARY KEY (nom_bapteme),
CONSTRAINT FK_animal_code_espece FOREIGN KEY (code_espece) REFERENCES espece
(code_espece)
);
CREATE TABLE enclos (
num_enclos uniqueidentifier NOT NULL,
nom_enclos varchar(50),
CONSTRAINT PK_enclos PRIMARY KEY (num_enclos )
);
CREATE TABLE periode (
id_periode uniqueidentifier NOT NULL,
date_debut datetime,
date_fin datetime,
CONSTRAINT PK_periode PRIMARY KEY (id_periode)
);
CREATE TABLE se_composer (
code_element uniqueidentifier NOT NULL,
id_menu uniqueidentifier NOT NULL,
pourcentage decimal,
CONSTRAINT PK_se_composer PRIMARY KEY (code_element, id_menu),
3/6
Centre d’Enseignement à Distance
Laurent MOREAU
CONSERVATOIRE NATIONAL DES ARTS ET METIERS
CONSTRAINT FK_se_composer_code_element FOREIGN KEY (code_element) REFERENCES
element_de_base (code_element),
CONSTRAINT FK_se_composer_id_menu FOREIGN KEY (id_menu) REFERENCES menu_type
(id_menu)
);
CREATE TABLE recevoir (
id_menu uniqueidentifier NOT NULL,
nom_bapteme uniqueidentifier NOT NULL,
qte_absorbee decimal,
date_repas datetime,
heure_repas datetime,
CONSTRAINT PK_recevoir PRIMARY KEY (id_menu, nom_bapteme),
CONSTRAINT FK_recevoir_id_menu FOREIGN KEY (id_menu) REFERENCES menu_type (id_menu),
CONSTRAINT FK_recevoir_nom_bapteme FOREIGN KEY (nom_bapteme) REFERENCES animal
(nom_bapteme)
);
CREATE TABLE pouvoir_vivre (
code_espece uniqueidentifier NOT NULL,
num_enclos uniqueidentifier NOT NULL,
CONSTRAINT PK_pouvoir_vivre PRIMARY KEY (code_espece, num_enclos),
CONSTRAINT FK_pouvoir_vivre_code_espece FOREIGN KEY (code_espece) REFERENCES espece
(code_espece),
CONSTRAINT FK_pouvoir_vivre_num_enclos FOREIGN KEY (num_enclos ) REFERENCES enclos
(num_enclos)
);
CREATE TABLE occuper (
nom_bapteme uniqueidentifier NOT NULL,
id_periode uniqueidentifier NOT NULL,
num_enclos uniqueidentifier NOT NULL,
CONSTRAINT PK_occuper PRIMARY KEY (nom_bapteme, id_periode, num_enclos),
CONSTRAINT FK_occuper_nom_bapteme FOREIGN KEY (nom_bapteme) REFERENCES animal
(nom_bapteme),
CONSTRAINT FK_occuper_id_periode FOREIGN KEY (id_periode) REFERENCES periode
(id_periode),
CONSTRAINT FK_occuper_num_enclos FOREIGN KEY (num_enclos) REFERENCES enclos
(num_enclos)
);
Travail à réaliser : Ecrivez les requêtes correspondantes
1. Requête 1 : Quels sont les tigres mâles qui reçoivent un menu composé de 40% de protides ?
SELECT nom baptême
FROM Animal, Recevoir, Menu Type, Se composer, Eléments de base, Espece
WHERE [Link] = « Male »
AND Se [Link] = 40
AND Element de [Link]é aliment = « Protides »
AND [Link]é espèce = « Tigre »
2. Requête 2 : Pendant quelle période les rhinocéros vivent-ils dans un enclos ?
SELECT date début, date fin
FROM Période, Enclos, Espèce, Pouvoir vivre, Occuper
WHERE Espè[Link]é espèce = « Rhinocéros »
3. Requête 3 : Quels sont les menus qui sont composés de moins de 10% de glucides ?
SELECT id menu
FROM Menu type, Se composer, Eléments de base
WHERE Eléments de [Link]é éléments = « Glucides »
AND Se [Link] >= 10
4. Requête 4 : Lister les animaux dont le nom ressemble à « PUMBA »
SELECT Nom baptême
FROM Animal
4/6
Centre d’Enseignement à Distance
Laurent MOREAU
CONSERVATOIRE NATIONAL DES ARTS ET METIERS
WHERE Nom baptême LIKE « %PUMBA% »
5. Requête 5 : Combien d’espèces peuvent vivre en enclos ?
SELECT COUNT(Code espèce)
FROM Espèce, Pouvoir vivre, Enclos
PARTIE 3 : TRIGGERS (6 POINTS)
On dispose de :
CREATE TABLE dept(
nodept NUMERIC(2) NOT NULL CONSTRAINT dept_nodept_pk PRIMARY KEY,
nom VARCHAR(25),
noregion NUMERIC(1) NOT NULL
);
CREATE TABLE emp(
noemp NUMERIC(7) NOT NULL CONSTRAINT emp_noemp_pk PRIMARY KEY,
nom VARCHAR(25),
prenom VARCHAR(25) ,
embauche DATE,
nosupr NUMERIC(7) ,
titre VARCHAR(25) ,
nodept NUMERIC(2) NOT NULL ,
salaire NUMERIC(11, 2) ,
tx_commission NUMERIC(4, 2) CONSTRAINT emp_tx_commission_ck CHECK (tx_commission
BETWEEN 10 AND 20) ,
CONSTRAINT emp_nodept_fk FOREIGN KEY (nodept) REFERENCES dept(nodept)
);
1. Écrire une fonction moySalaire sans paramètre qui renvoie le salaire moyen des employés.
CREATE OR REPLACE FUNCTION moySalaire() RETURNS NUMERIC AS ’
DECLARE
Moyennne NUMERIC
BEGIN
SELECT INTO moyenne AVG(salaire) FROM emp;
RETURN moyenne;
END;
‘ LANGUAGE plpgsql;
2. Utiliser ensuite cette fonction pour afficher les noms et le salaire des employés qui gagnent plus que le
salaire moyen,
SELECT nom, salaire FROM emp
WHERE salaire > moySalaire() ;
3. puis ceux dont le salaire est égal au salaire moyen à 10% près (c'est-à-dire ceux dont le salaire est
compris entre 90% et 110% du salaire moyen).
SELECT nom, salaire FROM emp
WHERE ABS(salaire-moySalaire()) < 0.10*moySalaire() ;
PARTIE 4 : DIVERS (2 POINTS)
Une table ayant été créée, son propriétaire peut accorder ou retirer à un autre utilisateur (ou à tous) les droits
suivants :
¾ SELECT : Accès en lecture à toutes les colonnes d'une table ou d'une vue.
¾ INSERT : Insérer des données dans toutes les colonnes d'une table.
¾ UPDATE : Mettre à jour dans toutes les colonnes d'une table.
¾ DELETE : Supprimer des enregistrements d'une table.
5/6
Centre d’Enseignement à Distance
Laurent MOREAU
CONSERVATOIRE NATIONAL DES ARTS ET METIERS
¾ RULE : Définir une règle sur une table ou une vue.
¾ ALL : Donner tous les privilèges.
1. Créer une vue relative au numéro, nom, titre, numéro de département des employés.
Puis accorder les droits de SELECT sur la vue à un autre user
CREATE VIEW EMPVUE AS
SELECT NOEMP, NOM, TITRE, NODEPT
FROM EMP;
GRANT SELECT
ON EMPVUE
TO <nom de l’utilisateur>
2. Accorder tous les droits sur la table DEPT à un autre user
GRANT ALL ON DEPT TO <nom de l’utilisateur>
PARTIE 5 : REPRISE DUR PANNE (2 POINTS)
Expliquez les différentes techniques de reprises sur panne
Cf cours
6/6
Centre d’Enseignement à Distance
Laurent MOREAU
