B210 dev2

Anne 2015

Module B210 Bases de donnes relationnelles

Devoir n2

NB : En rendant son travail, l'tudiant garantit sur son honneur que celui-ci est 100%

personnel.

Exercice 1: Requtes CRVT et SQL (16pts)

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).

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.

Ecrire les requtes en CRVT et en SQL retournant les rsultats demands.

1) Donner le nom des socits lyonnaises.

2) Donner le nom et le prnom des personnes ayant dj t une fois responsable d'un

dpartement informatique.

3) Donner le nom et prnom des personnes ayant travaill dans une des socits ayant t

dirige par Monsieur Jean Grange.

4) Donner le nom des socits nayant aucun dpartement rpertori dans la base.

5) Donner le nom et prnom des personnes ayant travaill la fois dans les socits

VEALIO et VEVINDI.

6) Donner le nom et le prnom des ingnieurs n'ayant jamais t responsable d'un

dpartement.

7) Donner les dpartements ayant les bureaux avec la plus grande capacit.

8) Donner le nom, le prnom et lanne de naissance des personnes ayant travaill dans

toutes les socits parisiennes rpertories dans la base.

(i.e. il nexiste pas de socits parisiennes pour lesquelles la personne na pas

daffectation dans un des dpartement de ces socits)

Exercice 2: Formes normales (4pts)

Soit la relation UE (codeUE, #idEnseignant, NomUE, Matire,nbECTS) suppose 1 FN

par hypothse et dfinie pour stocke les informations sur les units denseignements

dune universit.

CodeUE reprsente un code associ une UE, idEnseignant rfrence lidentifiant de

lenseignant responsable de lUE, NomUE reprsente le libell de lUE, Matire

reprsente le sujet de lUE (Informatique, Mathmatiques) et nbECTS reprsente le

nombre dECTS associes lUE.

1) Quelles hypothses supplmentaires doit-on faire sur les enseignants et/ou les UE

pour que la relation UE soit au moins une deuxime forme normale (2FN).

2) Quelles hypothses supplmentaires doit-on faire sur les enseignants et/ou les UE pour

que la relation UE soit une troisime forme normale (3FN).