INFO4030 - cours du professeur éric hervet

INFO4030
Bases de données relationnelles et réparties
Examen final (40%)
INFO4030 - Examen final (40%)
Nom :
NI :
I.
Questions de cours (10 points)
i. Donner le résultat de la jointure naturelle des trois tables suivantes :
(2 points)
Personne
Nom
Anas Elhilali
Evance Kafando
Siriki Koné
Marc Eddy Lafontant
Bobby LeBlanc
Fadaho Mahamat
Pierrette Mugisha
Sadath Oke Dare
Yann Ouedraogo
Éric Hervet
Endroit
Login
eae6198
eek5559
esk2603
eml5534
ebl4562
eam2403
epm9135
eao9863
eyo3005
Ville
Wonderland
Linuxworld
Disneyworld
Wonderland
Babaorum
Lutèce
Linuxworld
Wonderland
Babaorum
Login
eae6198
eek5559
esk2603
eml5534
ebl4562
eam2403
epm9135
eao9863
eyo3005
q 1 / 14 p
Produit
Ville
Babaorum
Babaorum
Disneyworld
Disneyworld
Disneyworld
Linuxworld
Linuxworld
Linuxworld
Produit
Clé USB 8To
Clavier sans fil
Super Smart Phone
Clé USB Star Wars
Clavier Jungle Book
Fastest Supercomputer
Kung Fu Penguin
From Scratch Book
24 avril 2016
INFO4030
Bases de données relationnelles et réparties
Examen final (40%)
ii. Fragmenter horizontalement et verticalement la table suivante, sachant que Login est la clé primaire, et avec les directives suivantes :
+ On dispose de quatre serveurs S1 , S2 , S3 et S4 .
+ S1 et S2 contiennent les tuples où le nom de famille commence par A, B, . . . , ou L.
Plus spécifiquement, S1 contient juste les noms et S2 contient juste les villes.
+ S3 et S4 contiennent les tuples où le nom de famille commence par M, N, . . . , ou Z.
Plus spécifiquement, S3 contient juste les noms et S4 contient juste les villes.
Dessiner quatre grands cercles S1 , S2 , S3 et S4 en y écrivant à l’intérieur les données qu’ils
contiennent.
(2 points)
Personne
Login
eae6198
eeh1357
eek5559
esk2603
eml5534
ebl4562
eam2403
epm9135
eao9863
eyo3005
Éric Hervet
Nom
Anas Elhilali
Eric Hervet
Evance Kafando
Siriki Koné
Marc Eddy Lafontant
Bobby LeBlanc
Fadaho Mahamat
Pierrette Mugisha
Sadath Oke Dare
Yann Ouedraogo
Ville
Wonderland
Linuxworld
Linuxworld
Disneyworld
Wonderland
Babaorum
Lutèce
Linuxworld
Wonderland
Babaorum
q 2 / 14 p
24 avril 2016
INFO4030
Bases de données relationnelles et réparties
Examen final (40%)
iii. Expliquer la différence entre un SGBD réparti homogène et un SGBD réparti hétérogène.
(2 points)
iv. Donner au moins deux avantages et deux inconvénients d’un SGBD réparti.
(2 points)
v. Quel est l’avantage d’une procédure PL/SQL stockée ?
(2 points)
Éric Hervet
q 3 / 14 p
24 avril 2016
INFO4030
II.
Bases de données relationnelles et réparties
Examen final (40%)
Normalisation (10 points)
Soit la table relationnelle suivante servant à enregistrer des présentations de projets étudiants :
Présentation(NI, NomEtud, Sigle, Titre, NomProf, Dépt, Session, Crédits,
HeurePrésentation, SallePrésentation, NoteProjet)
+ NI : NI étudiant ;
+ NomEtud : prénom & nom de l’étudiant.e ;
+ Sigle : sigle du cours ;
+ Titre : titre du cours ;
+ NomProf : prénom & nom du prof qui donne le cours ;
+ Dépt : nom du département ;
+ Session : session (’A15’, ’H16’, etc.) ;
+ Crédits : nombre de crédits du cours ;
+ HeurePrésentation : date et heure de présentation du projet ;
+ SallePrésentation : numéro de la salle pour présenter le projet ;
+ NoteProjet : note obtenue par l’étudiant.e à l’issue de sa présentation.
Pour les projets en binôme ou en groupe, il peut y avoir plusieurs étudiant.e.s qui présentent leur projet
ensemble (même cours, même heure, même salle). Ça ne signifie pas qu’ils auront nécessairement la
même note pour leur projet.
+ Écrire les dépendances fonctionnelles avec pour chacune quelques mots de justification.
(3 points)
+ En déduire la clé primaire de la relation Présentation.
(1 point)
Éric Hervet
q 4 / 14 p
24 avril 2016
INFO4030
Bases de données relationnelles et réparties
Examen final (40%)
+ Mettre la relation en deuxième forme normale.
(2 points)
+ Mettre la relation en troisième forme normale.
(2 points)
+ Mettre la relation en forme normale de Boyce-Codd.
(2 points)
Éric Hervet
q 5 / 14 p
24 avril 2016
INFO4030
III.
Requêtes (10 points)
III.1.
Base de données
Bases de données relationnelles et réparties
Examen final (40%)
On considère une base de données immobilière dont les tables sont données aux deux pages suivantes.
C’est une base de données qui permet de gérer le fonctionnement d’agences immobilières, réparties en
plusieurs filiales et pour lesquelles du personnel travaille, font visiter des maisons ou des appartements
à des clients qui recherchent une propriété à louer (et qui ont des préférences de location).
Les maisons/appartements appartiennent à des propriétaires privés.
Les clients qui recherchent une maison ou un appartement à louer ont la possibilité de s’inscrire à une
filiale qui planifie les dates de visites.
Éric Hervet
q 6 / 14 p
24 avril 2016
Éric Hervet
Personnel
q 7 / 14 p
nom
Oscar Meier
Marie Labonté
Jean Martin
Michel Richard
ville
Genève
Paris
Montréal
Montréal
Montréal
Montréal
type
Maison
Appartement
Appartement
Appartement
Maison
Appartement
Client
codePostal
1201
75000
H2L 5Y7
H2L 5Y7
H2L 5Y7
H2L 5Y7
pièces
6
4
3
3
5
4
adresse
5 Quai du Mont-Blanc, 1201 Genève, Suisse
186 Boulevard Laviolette, Trois-Rivières J7K 3L8, QC, Canada
56 Boulevard St-Germain, 75000 Paris, France
125 Rue de Genève, 1226 Thônex, Suisse
rue
15 Place du Bourg de Four
6 Champs-Élysées
430 Rue des Peupliers #5
1780 Boulevard Central #210
45 Chemin du Lac
911 Route de l’Aéroport #1809
EM37
EM37
EM14
numFiliale
F007
F005
F003
F003
F003
F003
locMax
350
600
425
750
numPersonnel
EG9
EP41
typePréf
Appartement
Appartement
Appartement
Maison
numPropriétaire
CP46
CP87
CP40
CP93
CP87
CP93
numTél
+41 022 732 61 73
+1 819 524 1875
+33 01 67 75 92 12
+41 022 349 34 79
loyer
650
400
350
375
600
450
Bases de données relationnelles et réparties
numClient
CL56
CL62
CL76
CL74
numPropriété
LG14
LP94
LM4
LM36
LM21
LM16
PropriétéÀLouer
dateNaissance
numFiliale
19 février 1990
F007
24 mars 1978
F003
1 octobre 1960
F005
10 novembre 1980
F003
3 juin 1960
F003
13 juin 1990
F005
rue
ville
pays
codePostal
59 Boulevard St-Germain
Paris
France
75018
243 Rue des Roses
Montréal Canada
H2L 5Y7
243 Boulevard Anspach
Bruxelles Belgique
33000
67 Champs-Élysées
Paris
France
75000
33 Place du Bourg de Four Genève
Suisse
1201
numPersonnel
nom
fonction
genre
EG9
Marie Thale
Assistant
F
EM14
David Renault Superviseur
M
EP21
Jean Blanc
Gérant
M
EM37
Anne Bertrand
Assistant
F
EM5
Suzanne Brière
Gérant
F
EP41
Julie Hette
Assistant
F
numFiliale
F002
F003
F004
F005
F007
Filiale
INFO4030
Examen final (40%)
24 avril 2016
Éric Hervet
numClient numFiliale
CL56
F003
CL62
F007
CL74
F003
CL76
F005
numPersonnel dateInscription
EM37
11 avril 2013
EG9
7 mars 2013
EM37
16 novembre 2012
EP41
2 janvier 2014
Inscription
numClient numPropriété dateVisite
commentaire
CL56
LG14
24 mai 2014
trop petit
CL56
LM36
28 avril 2014
CL56
LM4
26 mai 2014
CL62
LG14
14 mai 2014 pas de salle à manger
CL76
LM4
20 avril 2014
trop éloigné
Visite
numPropriétaire
nom
adresse
numTél
CP40
Brigitte Gauthier
18 Rue Edison, Montréal H2L 5Y7, QC, Canada
+ 1 418 871 9635
CP46
Bruno Long
15 Quai des Berges, 1201 Genève, Suisse
+41 022 906 30 02
CP87
Claude Vézina
2115 Rue de la Gauchetière, Montréal H2L 5Y7, QC, Canada
+ 1 514 656 7891
CP93
Guy Lacasse
888 Boulevard Laviolette, Montréal H2L 5Y7, QC, Canada
+ 1 819 524 1075
PropriétairePrivé
INFO4030
Bases de données relationnelles et réparties
q 8 / 14 p
Examen final (40%)
24 avril 2016
INFO4030
III.2.
Bases de données relationnelles et réparties
Examen final (40%)
Requêtes d’algèbre relationnelle (5 points)
Requêtes à formuler en algèbre relationnelle.
i. Les noms des membres du personnel âgés de moins de 40 ans.
ii. Le ratio hommes / femmes parmi le personnel d’agence (nombre d’hommes ÷ nombre de femmes).
iii. Le ratio nombre de maisons à louer ÷ nombre d’appartements à louer.
iv. Les noms des propriétaires qui ont une propriété à louer pour moins de 500$ par mois.
v. Les noms des membres du personnel qui ne sont pas inscrits avec un client.
Éric Hervet
q 9 / 14 p
24 avril 2016
INFO4030
III.3.
Bases de données relationnelles et réparties
Examen final (40%)
Requêtes SQL (5 points)
Requêtes à formuler en langage SQL.
i. Les noms des clients ayant visité une propriété.
ii. Le loyer mensuel moyen d’une propriété à louer.
iii. Les noms des clients inscrits dans une filiale de France.
iv. Les numéros des filiales avec pour chaque numéro, le nombre de membres du personnel qui y
travaillent.
v. Les numéros des filiales avec pour chaque numéro, le nombre de visites organisé par chaque filiale.
Éric Hervet
q 10 / 14 p
24 avril 2016
INFO4030
IV.
Bases de données relationnelles et réparties
Examen final (40%)
Transactions concurrentes (5 points)
On considère trois transactions concurrentes Ta , Tb , Tc opérant sur des comptes bancaires X, Y , Z
de la manière suivante :
+ Ta transfère 200$ de X vers Y .
+ Tb transfère 400$ de Y vers Z.
+ Tc transfère 300$ de Z vers X.
Dire si l’exécution parallèle suivante est sérialisable ou non.
(2 points)
Si elle ne l’est pas, proposer une exécution parallèle sérialisable.
Si elle l’est, proposer une autre exécution parallèle sérialisable.
(3 points)
Temps
t0
t1
t2
t3
t4
t5
t6
t7
t8
t9
t10
t11
t12
t13
t14
t15
t16
t17
Éric Hervet
Ta
Tb
Tc
a ← Lecture(X)
b ← Lecture(Y )
c ← Lecture(Z)
b ← b − 400
c ← c − 300
a ← a − 200
Ecriture(X, a)
Ecriture(Z, c)
Ecriture(Y, b)
y ← Lecture(Z)
z ← Lecture(X)
x ← Lecture(Y )
x ← x + 200
z ← z + 300
Ecriture(X, z)
Ecriture(Y, x)
y ← y + 400
Ecriture(Z, y)
q 11 / 14 p
24 avril 2016
INFO4030
Bases de données relationnelles et réparties
Éric Hervet
q 12 / 14 p
Examen final (40%)
24 avril 2016
INFO4030
V.
Bases de données relationnelles et réparties
Examen final (40%)
Contraintes d’intégrité (5 points)
On considère ici les tables t_departements et t_employes dont les champs et les données suivent :
Table t_departements :
DEPT
10
20
30
40
50
60
NOMD
Finance
Recherche
Vente
Gestion
Marketing
Vente
LIEU
Moncton
Moncton
Bathurst
Dieppe
Shediac
Dieppe
Table t_employes :
MATRICULE
A00111222
A00222333
A00333444
A00444555
A00555666
A00666777
A00777888
NOME
Andre
Bernard
Claude
Denise
Elyse
Fernand
Gabrielle
PASSWD
andre1
bernard2
claude3
denise4
elyse5
fernand6
gabrielle7
SALAIRE
30000
40000
35000
45000
42000
39000
40000
PRIME
1000
2000
1500
DEPT
10
10
20
30
40
50
60
POSTE
Commercial
Directeur
Professeur
Professeure
Secretaire
Commercial
Directrice
MATSUP
A00222333
A00777888
A00777888
A00333444
A00222333
DATEMB
96-01-01
06-06-30
86-09-01
00-01-01
12-04-01
02-10-15
01-12-09
Rappel : dept est la clé primaire de la table t_departements et matricule la clé primaire de la table
t_employes. Dans t_employes, la colonne dept est une clé étrangère vers la table t_departements.
Pour chacune des opérations suivantes qui ne s’exécutent pas en séquence, dire si elle est possible
directement (en une seule requête), indirectement (en plusieurs requêtes) ou simplement impossible.
+ Changement du mot de passe de Gabrielle.
+ Changement de tous les numéros de département sur trois chiffres au lieu de deux.
Éric Hervet
q 13 / 14 p
24 avril 2016
INFO4030
Bases de données relationnelles et réparties
Examen final (40%)
+ Suppression du département 30.
+ Enregistrement d’un nouvel employé travaillant dans le département de numéro 80.
+ Changement de superviseur pour Claude : son nouveau superviseur est l’employé de matricule
A00111999.
Éric Hervet
q 14 / 14 p
24 avril 2016