25 Novembre 2002
Durée : 1h30. Documents autorisés.
Recommandations : choisir des solutions simples et lisibles et des réponses succinctes.
Un célèbre éditeur de jeux électroniques vous charge de la conception du schéma d’une base de données support à son dernier produit. Les principaux éléments gérés par la base de données sont des avatars, qui peuvent représenter soit des personnes soit des organisations regroupant plusieurs personnes. A chaque avatar est attribué un code qui l’identifie et une valeur dans une échelle de 0 et 10 qui caractérise son degré d’appartenance à l’univers du bien ou à l’univers du mal : 0 pour les avatars du mal et 10 pour ceux du bien. Chaque avatar de type personne possède un nom, un prénom, une date de naissance et un métier. Il peut occuper successivement des fonctions de responsabilité dans des organisations. Dans ce cas, on mémorise la fonction occupée, la date de début et la durée de cette fonction. Une organisation peut avoir une structure composite, c’est-à-dire, composée de sous-organisations. Dans ce jeux, les avatars sont localisés sur des bateaux et possèdent un certain nombre de munitions qui leur servent pour se battre. Un bateau a un capitaine, qui est une personne. Il a un nom, un degré (entre 0 et 10) d’importance dans le jeu et un degré de stabilité indiquant s’il peut couler facilement ou non. Les types de munitions sont identifiés par des codes et caractérisés par une puissance de destruction. Chaque type de munition a un constructeur qui ne peut être qu’une organisation d’avatars.
Travail demandé :
1. Proposer schéma Entité/Association pour cette base de données.
2. En déduire un schéma relationnel normalisé 3NF.
Partie II : SQL et PL/SQL
Soit le schéma de la base de données suivant d’une entreprise implantée sur plusieurs départements :
Employés(num, nom, fonction,
num_chef, salaire, date_embauche, num_département)
Départements
(num_département, nom, région)
Chaque employé possède un numéro, un nom, une fonction dans l’entreprise, un salaire et une date d’embauche. A chaque employé est associé un chef, sauf le directeur de l’entreprise qui n’est sous la responsabilité d’aucun autre employé. Les employés sont affectés à des départements. Chaque département possède un numéro, un nom et une région d’implantation.
A/ Ecrire en SQL les requêtes suivantes :
1 Liste des employés travaillant en région parisienne.
2 Nom et date d’embauche des employés embauchés avant leur chef.
3 Liste des numéros des employés qui n’ont pas de subordonné.
4 Liste des employés embauchés avant tous les employés du département 75.
5 Les employés ayant le salaire maximum de chaque département.
6 Nombre de subordonnés par chef.
B/ Soit la table Equipes(nom_chef, nom_subordonné) dont
le schéma est composé des attributs nom_chef et nom_subordonné.
Ecrie en PL/SQL une procédure Composition_Equipes qui va servir à
renseigner la table Equipes. Composition_Equipes prend en argument un
nombre Nb_min_membres et produit dans la table Equipes la composition
des équipes dont le nombre de membres (le chef + ses subordonnés) est supérieur
ou égal à la valeur de l’argument Nb_min_membres. Par exemple, l’appel
de la procédure suivant « execute Composition_Equipes(3) ; » insèrera
dans la table Equipes, la liste (nom chef, nom subordonné) des
équipes d’au moins trois personnes.