Soit RU une relation universelle de schéma (A, B, C, D, E) avec les dépendances fonctionnelles suivantes : {B-> D, AC -> E, BD -> A, ABD -> C}.

Proposer une clé (minimale, bien sûr!) pour cette relation, en prouvant à l'aide des axiomes d'Armstrong qu'elle détermine bien tous les attributs.
B -> D (H1) ; B -> B (reflexivité) => B -> BD (union) ; BD -> A (H3) => B -> A (transitivité) ;
BD -> ABD (réflexivité + union) ; ABD -> C (H4) => BD -> C (transitivité) => B -> C (transitivité) ;
AC -> E (H2) ; B -> AC (union) => B -> E (transitivité) => B -> ACDE (union) ; B est clé (CQFD)

Proposer une décomposition en 3^ème Forme Normale de la relation RU.
Couverture minimale du graphe des DFs
B -> D; B-> A; B-> C; AC -> E
Algo de décomposition
étape 1: R1(A, C, E)
étape 2: R2(B, D, A, C)

Partie 2 : Langage relationnel SQL

On considère une base de données de tournois de tennis dont le schéma est le suivant :

JOUEURS (NomJoueur, Prénom, Age, Nationalité)
RENCONTRES (NomGagnant, NomPerdant, LieuTournoi, Année, Score)
GAINS (NomJoueur, LieuTournoi, Année, Prime, NomSponsor)
SPONSORS (NomSponsor, Adresse, ChiffreAffaires)

où les attributs soulignés représentent des clés primaires pour chaque relation.

Exprimer en SQL les requêtes suivantes :
/* variante */ exprime une variante possible

1. Quels sont les nom et prime des joueurs sponsorisés par 'FT' entre 1995 et 2000 ?

select NomJoueur, Prime /*, Année */ from GAINS
where NomSponsor = 'FT' and /* Année > 1994 and Année < 2001 */
Année between 1995 and 2000
 
2. Quelle est, pour chaque sponsor et pour chaque année, la moyenne des primes offertes ?

select NomSponsor, Année, avg(Prime) from GAINS
group by NomSponsor, Année
 
3. Quels sont les nom et nationalité des joueurs sponsorisés par 'FT' et ayant gagné au moins une fois à 'Roland Garros' ?

Select distinct J.NomJoueur, J.Nationalité from JOUEURS J, GAINS G, RENCONTRES R
where J.NomJoueur = G.NomJoueur and G.NomJoueur = R.NomGagnant
and G.NomSponsor = 'FT' and R.LieuTournoi = 'Roland Garros'

/* and S.LieuTournoi = 'Roland Garros' */
 

4. Diminuer de 80% toutes les primes données en 2000 par 'FT' aux joueurs français ayant perdus toutes leurs rencontres à 'Roland Garros' cette année-là.

update GAINS set Prime = 0.2 * Prime
where Année = 2000 and NomSponsor = 'FT'
and NomJoueur in

/* (select NomJoueur from JOUEURS where Nationalité = 'FR'
and NomJoueur in
(select NomPerdant from RENCONTRES where Année = 2000 and LieuTournoi='Roland Garros'
/* ) and NomJoueur not in ( */
minus
select NomGagnant from RENCONTRES where Année = 2000 and LieuTournoi='Roland Garros')
) */

(select NomJoueur from JOUEURS J
where Nationalité = 'FR'
and not exists (select * from RENCONTRES
where Année = 2000 and LieuTournoi='Roland Garros'
and J.NomJoueur not in (select NomPerdant from RENCONTRES
where Année = 2000 and LieuTournoi='Roland Garros')
)
)
 

5. Quels sont les nom, âge et nationalité des joueurs ayant participé en 2000 à la fois aux tournois de 'Roland Garros' et de 'Wimbledon' ?

En considérant d'abord les contraintes d'intégrité référentielles à ajouter au schéma précédent, ensuite un schéma selon le modèle "Entité-Relations" qui pourrait mieux représenter les mêmes données, proposez un nouveau schéma relationnel (sans perte par rapport au précédent) augmentés des contraintes d'intégrités qui vous semblent nécessaires pour préserver au mieux la cohérence de la BD de tournois de tennis.

1. Appelons J, R, G et S, les relations JOUEURS, RENCONTRES, GAINS et SPONSORS


Les jointures sémantiquement possibles entre attributs sont:

• J.NomJoueur = R.NomGagnant ; J.NomJoueur = R.NomPerdant ;
• ajouter dans R les CI de clé étrangère identifiante ( = appartenant à la clé locale)
• J.NomJoueur = G.NomJoueur ;
• ajouter dans G la CI de clé étrangère identifiante
• G.NomSponsor = S.NomSponsor ;
• ajouter dans G la CI de clé étrangère non identifiante
• R.LieuTournoi = G.LieuTournoi ; R.Année = G. Année ;
• créer une relation Tournois T (LieuTournoi, Année) que réfèrent les attributs de mêmes noms des relations R et G
• savoir s'ils se réfèrent entre eux est un choix sémantique: (a) pas de gain reçu si pas de rencontre ou (b) pas de rencontre si pas de gain promis ou (c) aucune relation
2. Les schémas Entité-Relation possibles
1. ou bien s'en déduisent dans les trois versions (a) (b) (c)
2. 
3. CREATE TABLE JOUEURS (
          NomJoueur            VARCHAR2(20) NOT NULL,
          Prenom               VARCHAR2(20) NULL,
          Age                  NUMBER NULL,
          Nation               VARCHAR2(20) NULL,
          PRIMARY KEY (NomJoueur)
   );
   
   CREATE TABLE TOURNOIS (
          LieuTournoi          VARCHAR2(20) NOT NULL,
          Annee                NUMBER NOT NULL,
          PRIMARY KEY (LieuTournoi, Annee)
   );
   
   CREATE TABLE RENCONTRES (
          NomGagnant           VARCHAR2(20) NOT NULL,
          NomPerdant           VARCHAR2(20) NOT NULL,
          LieuTournoi          VARCHAR2(20) NOT NULL,
          Annee                NUMBER NOT NULL,
          score                VARCHAR2(20) NULL,
          PRIMARY KEY (NomGagnant, NomPerdant, LieuTournoi, Annee), 
          FOREIGN KEY (LieuTournoi, Annee)
                                REFERENCES TOURNOIS, 
          FOREIGN KEY (NomPerdant)
                                REFERENCES JOUEURS, 
          FOREIGN KEY (NomGagnant)
                                REFERENCES JOUEURS
   );
   
   CREATE TABLE SPONSORS (
          NomSponsor           VARCHAR2(20) NOT NULL,
          Adresse              VARCHAR2(20) NULL,
          ChiffreAffaire       NUMBER NULL,
          PRIMARY KEY (NomSponsor)
   );
   
   CREATE TABLE GAINS (
          NomJoueur            VARCHAR2(20) NOT NULL,
          LieuTournoi          VARCHAR2(20) NOT NULL,
          Annee                NUMBER NOT NULL,
          NomSponsor           VARCHAR2(20) NULL,
          PRIMARY KEY (NomJoueur, LieuTournoi, Annee), 
          FOREIGN KEY (LieuTournoi, Annee)
                                REFERENCES TOURNOIS, 
          FOREIGN KEY (NomSponsor)
                                REFERENCES SPONSORS, 
          FOREIGN KEY (NomJoueur)
                                REFERENCES JOUEURS
   );
   
   
4. ou bien peuvent être sémantiquement augmentés en introduisant une Inscription avant toute Rencontre ou toute promesse/attribution de Gain.prime par un Sponsor (mais ceci est au delà de ce qui était demandé ;-)

1. Q1/ dans quels cas un SGBD prend-il l'initiative d'annuler une transaction ?

R1/ (a) si la transaction possède des droits insuffisants, fait une erreur syntaxique ou sémantique, viole des CI ou (b) si l'ordonnanceur doit résoudre une situation de concurrence (dead lock, âge de la transaction) ou (c) en cas de panne système
 
2. Q2/ quels sont les avantages et les inconvénients respectifs de la programmation en "Embedded-SQL" et de la programmation classique avec une bibliothèque "SQL-Call Level Interface" (ODBC ou JDBC) ?

R2/ Les deux sont des normes ISO (=> portabilité du code source) mais l'E-SQL nécessite une précompilation en présence de la bibliothèque du SGBD-cible, la compilation conséquente ne produisant alors qu'un exécutable pour ce SGBD-cible, alors que le SQL-CLI utilise une bibliothèque standard utilisable "sans-couture" dans le code source et produit, après compilation, un exécutable pouvant accèder à différents SGBD ( => interopérabilité) grâce au mécanisme des bibliothèques objets dynamiquement "linkables". Le SQL-CLI s'écrit avec un plus grand nombre de lignes de code (écriture plus fastidieuse) mais un précompilateur standard pourrait générer du SQL-CLI à partir de l'E-SQL.