Examen de Bases de Données 2006

Examen de Bases de Données
mardi 25 avril 2006
9h-10h30
sujet: 1 page
documents non autorisés

Exercice A

Conception de schéma relationnel: schémas entité-association et schéma relationnel

On considère les entités suivantes:

– ENFANT: Prénom, Nom.

– ADULTE: Prénom, Nom, Numéro de sécuritéso­ciale.

On souhaite représenter les relations binaires suivantes entre un enfant et une autre personne: père, mère, et unique personne (qui est obligatoirement son père ou sa mère) sur le compte de laquelle l’enfant est inscrit: en effet, un enfant n’a pas de numéro de sécuritésociale. La mère est une femme et le père un homme. Atten­tion, il peut y avoir des personnes différentes, aussi bien des adultes que des enfants, de mêmes prénoms et de même noms! Par contre, deux personnes inscrites sous le même numéro de sécuritésociale ont des noms ou des prénoms différents, et il n’y a qu’un seul adulte inscrit sous un numéro de sécuritésociale. On ne rajoutera pas d’attributs aux entités ENFANT et ADULTE.

(A.i) Proposer un schéma entitéassociation avec au
moins les entités mentionnées en utilisant si besoin d’autre
entités, spécialisations ou généralisations. On pourra écrire

explicitement des contraintes.

(A.ii) Proposer un ou plusieurs schémas relation­nels correspondants.

Exercice B

Conception de schéma relationnel: formes normales

(B.i) Donner un exemple de relation et de dépen­dances fonctionnelles qui soit 3NF et pas BCNF. Expli­quer pourquoi cet exemple est 3NF mais pas BCNF.

(B.ii) Peut-on mettre cet exemple sous forme BCNF? Si oui faites-le.

(B.iii) Quels avantages et inconvénients ont les dif­férentes formes normales 3NF et BCNF?

(B.iv) Quel minimum peut-on garantir lors de la con­ception d’un schéma relationnel?

NUMAV et NOMAV pour chaque avion trouvé):

(D.i) Quels sont les avions de plus de 300 places?

On considère la base ”avions”:

PILOTE (NUMPIL, NOMPIL, ADR, SAL) AVION (NUMAV, NOMAV, CAPACITE, LOC) VOL (NUMVOL, NUMPIL, NUMAV,

VILLE DEP, VILLE ARR, H DEP, H ARR)

NUMPIL: cléde PILOTE, nombre entier NOMPIL: nom du pilote, chalne de caractères ADR: ville de la résidence du pilote, chalne

de caractères

SAL: salaire du pilote, nombre entier

NUMAV: cléde AVION, nombre entier CAPACITE: nombre de places d’un avion, nombre entier

LOC: ville de l’aéroport d’attache de l’avion, chalne de caractères

NUMVOL: cléde VOL, nombre entier VILLE DEP: ville de départ du vol, chalne de caractères

VILLE ARR: ville d’arrivée du vol, chalne de caractères

H DEP: heure de départ du vol, nombre entier entre 0 et 23

H ARR: heure d’arrivée du vol, nombre en-tier entre 0 et 23

Exercice C

SQL: création de table

(C.i) Donner la commande SQL pour créer la table VOL.

Exercice D

SQL: requêtes

Exprimer les requêtes suivantes en SQL (on donnera)

(D.ii) Quels sont les avions effectuant au moins un vol au départ de ’Bordeaux’?

(D.iii) Quels sont les avions effectuant un vol au départ de leur localitéavec un pilote demeurant dans cette même ville?

(D.iv) Quels sont les avions de capacitémaximale?

(D.v) Quelle est la durée moyenne des vols, pour chaque avion?

(D.vi) Quelle est la durée moyenne des vols, pour chaque avion de capacitésupérieure `a 300?

(D.vii) (difÞcile) Quels sont les avions n’effectuant que des vols conduits par un pilote nommé’Durand’?

(D.viii) (difÞcile) Quels sont les avions conduits par tous les pilotes?