Gsi devoir 1

Dossier 1 – Compréhension du modèle relationnel (14 points)

1. Écrivez le modèle relationnel en ligne de la base de données de la médiathèque. (3 points)

AUTEUR (Num_Auteur, Nom_Auteur, Nationalité_Auteur)

OUVRAGE (Num_Ouvrage, Titre_Ouvrage, #Num_Auteur)

EMPRUNT (#Num_Lect, #Num_Ouvrage, DateEmprunt)

LECTEUR (Num_Lect, Nom_Lect, Pnom_Lect, Adr_Lect, DateNce_Lect, #Num_Categ)

CATSOCIOPROF (Num_Categ, Lib_Categ)

2. Les ouvrages sont ils écrits par plusieurs auteurs ? Justifiez. (2 points)

Il existe une dépendance fonctionnelle entre Ouvrage et Auteur ; un ouvrage est écrit par un et un seul auteur (relation

de type 1.1) par contre un auteur peut écrire plusieurs ouvrages. Cette dépendance fonctionnelle sera mise en

oeuvre sous Access par l’application de la contrainte d’intégrité référentielle.

3. Quelle est la signification de la présence de deux clés primaires dans la relation « emprunt ». (3 points)

Il y a concaténation des clés primaires dans la relation « Emprunt », ce qui signifie qu'un ouvrage peut être emprunté

par un ou plusieurs Lecteurs, et qu'un lecteur peut lui aussi emprunter un ou plusieurs ouvrages (on a une CIM,

contrainte d’intégrité multiple, relation de type un vers plusieurs).

4. La propriété « DateEmprunt » pourrait-elle figurer dans la relation « ouvrage » ? Pourquoi ? (4 points)

La relation Emprunt est une relation « porteuse de données avec l’attribut « DateEmprunt ». Celui-ci dépend à la

fois de la relation Ouvrage et de la relation Lecteur. Pour un ouvrage donné et un lecteur donné on aura une date

d’emprunt. On ne peut pas trouver l’attribut « DateEmprunt » dans l’une des 2 autres relations, cela ne signifierait pas

la même chose, car si on avait cet attribut dans la relation ouvrage, on voudrait signifier que l’ouvrage donné dépend

forcément de la date d’emprunt (dépendance fonctionnelle des attributs vis-à-vis de la clé primaire ou identifiant).

83714CTPA01 2/4

5. On souhaite connaître la durée des emprunts des ouvrages : Quelle modification doit-on apporter à la

base de données ? (2 points)

Si on souhaite avoir une durée des emprunts il faudrait ajouter dans la relation emprunt un attribut supplémentaire qui

serait dateEmpruntRetour, ce qui pourrait permettre par l’intermédiaire de requêtes de calculer la durée des emprunts.

Dossier 2 – gestion de la base de données (16 points)

La responsable de la médiathèque, Madame Lerouvillois, souhaite connaître un peu mieux les habitudes des adhérents

de sa médiathèque. À ce titre, elle vous demande de réaliser quelques requêtes en SQL.

1. Liste des auteurs (numéros et noms) de nationalité Américaine (2 points)

SELECT Num_Auteur, Nom_Auteur

FROM Auteur

WHERE Nationalite_Auteur = "Américaine" ;

Remarque : on accepte guillemets ou doubles apostrophe (cote, ‘’)

2. Liste des ouvrages (numéro, titre) écrits par Émile Zola (3 points)

SELECT OUVRAGE.Num_Ouvrage, OUVRAGE.Titre_Ouvrage

FROM OUVRAGE,AUTEUR

WHERE AUTEUR.Num_Auteur=OUVRAGE.Num_Auteur

AND AUTEUR. Nom_Auteur = "Emile Zola" ;

Remarque : lorsque l’on a une jointure ( au moins 2 tables dans la clause From il est conseillé de mettre le

nom de la table plus le nom de l’ attribut pour éviter des confusions et des messages d’erreurs sous Access ;

Par ailleurs ici, on a juste un attribut qui reçoit le nom et le prénom de l’auteur, il n’ a pas été prévu de noter

le prénom dans un champ à part, mais attention cela peut exister.

3. Liste des lecteurs (noms et prénoms, date de naissance) qui ont emprunté l’ouvrage « Les misérables »

au cours de l’année 2012 (4 points)

SELECT LECTEUR.Num_Lect, LECTEUR.Nom_Lecteur, LECTEUR.DateNce_Lect

FROM LECTEUR, OUVRAGE, EMPRUNT

WHERE LECTEUR.Num_Lect=EMPRUNT.Num_Lect

AND EMPRUNT.Num_Ouvrage=OUVRAGE.Num_Ouvrage

AND OUVRAGE.Titre_Ouvrage = "Les Misérables"

AND EMPRUNT.DateEmrunt between #01/01/2012# AND #12/31/2012#;

...