Anne 2015
Module B210 Bases de donnes relationnelles
Devoir n3
NB : En rendant son travail, l'tudiant garantit sur son honneur
que celui-ci est 100%
personnel.
On considre la base de donnes Socit dfinie selon le schma
suivant :
Personne(NumP, Nom, Prenom, AnneeNaiss, Fonction) Stocke les
informations sur les personnes travaillant dans des socits. NumP
est un numro
identifiant de manire unique une personne. AnneeNaiss reprsente
lanne de naissance de la
personne. Fonction correspond la fonction de la personne :
fonction {Ingnieur, Commercial, Comptable, Agent entretien,
Administrateur}
Societe(NumS, Nom, Ville, #NumDirecteur) Stocke les informations
sur les socits. NumDirecteur rfrence le directeur de la socit.
Departement(NumD, Nom, NomBat, #NumResp, #NumSociete) Stocke les
informations sur les diffrents dpartements des socits. NumD est un
numro
identifiant de manire unique un dpartement de nom Nom. NomBati
spcifie le nom du btiment o se trouve le dpartement. NumSociete
rfrence la socit associe au
dpartement. NumResp rfrence la personne responsable du
dpartement.
Affectation( #NumP, #NumD, DateDebut, DateFin) Stocke les
informations sur les affectations des personnes (rfrences par NumP)
dans des
dpartements (rfrencs par NumD). DateDebut et DateFin
correspondent respectivement
la date dembauche et la date de dpart de la personne NumP dans
le dpartement NumD.
Bureau(Numero, #NumD, etage, capacite, climatise)
Stocke les informations sur les bureaux des dpartements. Un
bureau a un numro qui
lidentifie relativement un dpartement. Un bureau est donc
identifi de manire unique par le couple de valeur (Numero,NumD) o
NumD rfrence un dpartement.
Etage spcifie ltage o se trouve le bureau. Capacite spcifie le
nombre de places assises se trouvant dans le bureau et climatise
indique si le bureau est climatis (O) ou non (N).
CoinCafe(#NumD,stockCafe, stockThe)
Stocke les informations relatives la gestion des coins caf de
chaque dpartement.
Un dpartement dispose dun unique coin caf. Lattribut stockCafe
reprsente le nombre de paquets de caf disponibles et stockThe
reprsente le nombre de paquets de
th disponibles.
On suppose que :
Il ny a pas dhomonymes parmi les personnes.
Dans affectation, labsence de valeur pour DateFin dans une
affectation signifie que la personne est toujours dans le
dpartement identifi par NumD.
Prliminaire : installation dun SGBD (2pts) Il vous est demand
dinstaller le SGBD de votre choix :
- MySQL (possibilit via XAMP :
https://www.apachefriends.org/fr/index.html)
- ORACLE (possibilit via version Oracle Express :
http://www.oracle.com/technetwork/database/database-technologies/express-
edition/overview/index.html)
- POSTGRES (possibilit via http://www.postgresql.org/)
1) Crer la base de donnes B210.
Exercice 1: Commandes SQL (10pts)
A partir de la spcification ci-dessus, donner les commandes SQL
permettant de :
1) Donner les commandes SQL permettant de crer les tables
PERSONNE, SOCIETE, DEPARTEMENT, AFFECTATION, BUREAU, COINCAFE selon
les schmas
donns. Faire une copie dcran montrant que vous avez bien install
un SGBD et cr vos tables.
2) Ajouter pour chacune des tables les contraintes dintgrit a.
de cl primaire (attributs souligns) b. de cl trangre (attributs
prfixs par un #)
Remarque : Vos contraintes devront tre nommes selon les
conventions :
a. les noms de cls primaires commencent par pk_ b. les noms de
cls secondaires commencent par fk_
3) On souhaite ajouter les contraintes supplmentaires rpondant
aux besoins suivants : a. Une personne ne peut pas tre le directeur
de plusieurs socits b. Une personne a moins de 65 ans c. Tout
dpartement a un nom
4) Peupler votre base avec au moins 3 tuples dans chaque table.
5) Modifier lanne de naissance de la premire personne que vous avez
insre dans la
table PERSONNE pour le rajeunir de 5ans.
6) Supprimer le troisime tuple de la relation SOCIETE. 7)
Ajouter lattribut DateCreation de type Date la table SOCIETE. 8)
Supprimer lattribut DateCreation que vous venez de crer dans la
table SOCIETE. 9) Supprimer la table COINCAFE. 10) Que se
passe-t-il si vous essay de supprimer la table PERSONNE ? Pourquoi
?
Exercice 2: Requtes SQL (8pts)
1) Donner le nombre de fonctions possible dans la socit de nom
FDE
2) Pour chaque nom de socit, donner le nombre de personnes y
travaillant
actuellement.
3) Pour chaque dpartement de la socit TATOL, donner le nombre
total de places
assises.
