Exercice introduction aux systèmes de gestion de base de données
- En utilisant vos propres mots, essayez d’expliquer ce qu’est une base de données.
- Quelle est la différence principale entre une base de données à deux dimensions et une base de données relationnelle ?
- Citez un avantage d’une base de données relationnelle.
- Citez un inconvénient d’une base de données relationnelle.
- Quelle est la différence entre un champ et un enregistrement ?
- Pourquoi est-il recommandé d’utiliser une clé primaire dans votre table ?
- Parmi les objets suivants : tables, requêtes, formulaires et états, le(s)quel(s) est(sont) utilisé(s) pour stocker des données ?
- Proposez deux exemples où l’utilisation d’une base de données pourrait être plus efficace que celle de fiches cartonnées.
Exercice analyse des requêtes d'une BD relationnelle
La société Losoft vend, entre autres, des photocopieurs. Elle propose à ses clients de souscrire des abonnements qui inclus la fourniture du papier, du toner et l’entretien du photocopieur. Le coût de l’abonnement est fonction de la quantité de photocopies choisie. L’entretien est toujours assuré par le même technicien.
Travail à faire :
Compléter le tableau d’analyse des requêtes ci-dessous :
Solution :
Exercice Merise: Opération sur un Article
Quelle amélioration liée à la lisibilité du modèle ci-dessous apportez-vous ?
Solution :
Le modèle présente des lacunes de forme :
- la 1ère opération comporte un titre correspondant à un résultat, et non pas une action
- un résultat créé par cette opération, n’est pas nommé ;
- des résultats du modèle sont nommés par des intitulés d’opération
le modèle rectifié;
Exercice cardinalités et analyse des requêtes SQL
Objectifs :
- Justifier les cardinalités.
- Les requêtes SQL sous SGBD SQL.
L’analyse des données
Madame SETAG, secrétaire comptable, vous transmet, en annexe 1, un extrait du modèle entité association qu'elle vient de concevoir pour la gestion des clients.
Travail à faire
1. Expliquez les cardinalités.
2. Complétez le tableau d'analyse des requêtes (annexe 2).
Annexes
Annexe 1
Solution :
Question 1 :
Cardinalité Client - 0,N - Correspondre : A un client correspond 0 ou n factures.
Cardinalité Facture - 1,1 - Client : A une facture correspond un client et un seul.
Cardinalité Facture - 1,N - Comprendre : Une facture comprend 1 ou plusieurs produits.
Cardinalité Produit - 0,N - Comprendre : A un produit peut correspondre 0 ou n factures.
Question 2:
Exercice merise et requêtes SQL sur une BD gestion des factures
Objectifs :
- Passer du MCD au MLD.
- Justifier des cardinalités.
- Utiliser le langage SQL.
Enoncé
La société Zamur souhaite que vous développiez une application lui permettant d’améliorer le suivi de ses clients. Vous utiliserez pour ce faire le logiciel de gestion de bases de données acheté il y a 2 mois.
Travail à faire
- Complétez le modèle conceptuel des données fourni en annexe 1, en y implantant les cardinalités.
- Quels sont les critères pour qu’un attribut puisse être retenu comme identifiant ?
- Écrivez le modèle relationnel correspondant au modèle conceptuel des données que vous venez de compléter.
- Complétez le tableau d’analyse des requêtes fourni en annexe 2.
Annexe 1 :
Règles de gestion :
- Un client est toujours géré par le même commercial
- Un commercial gère plusieurs clients
Annexe 2 :
Solution :
Question 1
Question 2
Pour être retenu en qualité d’identifiant, un attribut doit stocker des données uniques et stables.
Question 3
Question 2
Exercices normalisation et dépendances fonctionnelles des BD
Exercice 1
Soit R1 (A, B, C, D, E, F) une relation avec l'ensemble de dépendances suivant :
- Donner le graphe(ensemble) minimum de dépendances. Quelles est la clé de R1 ?
- Quelle est la forme normale de R1 ?
- On décompose la relation R1 en R11 et R12 : R11(A, B, D, E, F) et R12(B,C). Quelles sont les formes normales des relations R11 et R12 ?
- Proposer une décomposition sans perte d'information de R11.
Exercice 2
Soit le schéma de la relation R(A, B, C, D, E, G) et un ensemble donné de dépendances fonctionnelles pour cette relation:
1. Donner le graphe minimal des dépendances fonctionnelles de R
2. Donner une décomposition de R en relations 3NF sans perte d'informations et sans perte de dépendances.
3.Précisez l'identifiant de chaque relation obtenue.
Solution :
Corrigé Exercice 1 :
1. L'ensemble minimum de dépendances fonctionnelles de R1 est le suivant :
2. La clé de cette relation est (A,B). Cette relation est en première forme normale mais pas en deuxième forme normale car il y a un attribut qui ne fait pas partie de la clé(C) qui dépend d'une partie de la clé(B).
3. La relation R11 est en deuxième forme normale et pas en troisième normale car il subsiste une dépendance transitive. La relation R12 est en BCNF.
4. On peut décomposer R11 en R112(D, E, F) et R112(A,B,D) sans perte d'information.
Corrigé Exercice 2
Avec A --> C et A, C --> E,on constate que A, C --> E n'est pas élémentaire et que l'on peut la remplacer par A --> E sans perte de dépendance.
Avec A --> E et A, D, E --> B, G, on constate que A, D, E --> B, G n'est pas élémentaire et que l'on peut la remplacer par A, D --> B, G sans perte de dépendance.
Avec A --> B et A, D --> B,on constate que A, D --> B est redondante et que l'on peut la supprimer.
Avec A --> B, C --> B et A --> C, on constate que A --> B peut être déduite et que l'on peut donc la supprimer.
L'ensemble minimum des dépendances fonctionnelles de R est donc le suivant :
Voici une décomposition sans perte de dépendance ni d'information :
Exercice sur les SGBD relationnelles
1. Qu'est ce qu'un SGBDR Donnez la signification des lettres ainsi qu'une définition détaillée ?
2. Citez 6 exemples de SGBDR (3 commerciaux et 3 libres)
3. Donnez les différents types de données
4. Qu'est ce que l'intégrité référentielle (donnez une réponse détaillée) ?
5. Qu'est ce que l'intégrité de domaine (donnez un exemple) ?
6. Que représente le modèle relationnel ?
7. Qu'est ce qu'une clé étrangère ?
8. Que signifient les lettres SQL ?
9. En SQL, que signifient les lettres LID
Solution :
1. Qu'est ce qu'un SGBDR Donnez la signification des lettres ainsi qu'une définition détaillée ? (2 pts)
Système de Gestion de Base de Données Relationnel
Un SGBD représente donc l'ensemble des programmes assurant structuration, stockage, maintenance, mise à jour et recherche des données d‘une base + interfaces nécessaires aux différentes formes d‘utilisation de la base. Relationnel exprime les relations (clés primaires - clés étrangères) entre les tables
2. Citez 6 exemples de SGBDR (3 commerciaux et 3 libres) (2 pts)
Les SGBD commerciaux :
· Oracle (Unix, Linux, W indows)
· M icrosoft : SQL serveur et Access
· IBM : DB2, Informix
· Sybase : Sql Anywhere (W indows, Unix, Linux …)
· Interbase (W indows)
· …
Les SGBDR libres :
- My-SQL
- Postgre Sql (Linux)
- Firebird (Linux, W indows)
- Adabas, SAP-DB, Max DB (Linux, Windows)
3. Donnez les différents types de données (3 pts)
Numériques (octet, entier, entiers longs, réels …)
Texte
Date/heure
Monétaire, booléen, lien hypertexte, mémo …
4. Qu'est ce que l'i égrité référenti le (donnez une réponse détailée) ? (2 pts)
La notion d‘intégrité référentielle est au cœur même de la base de données. Elle lui confère son caractère relationnel.Référentielle signifie que la clé étrangère doit faire référence à une clé primaire.
Appliquer l'intégrité référentielle implique :
- Dans le champ clé étrangère vous ne pouvez pas entrer une valeur qui n'existe pas dans la clé primaire (Un client ne peut être saisit dans une commande s'il n'existe pas).
- Vous ne pouvez pas effacer un enregistrement de la table primaire si des enregistrements correspondants existent dans une table liée
- Vous ne pouvez pas modifier une valeur clé primaire dans la table primaire si cet enregistrement a des enregistrements liés
5. Qu'est ce que l'intégrité de domaine (donnez un exemple) ? (2 pts)
Les contraintes de domaines sont des contraintes liées aux colonnes des tables (attribut non nul, ou dans une fourchette de valeurs …) Ex : mode de paiement (Espèce, chèque, Carte bleue)
6. Que représente le modèle relationnel ? (2 pts)
Le modèle relationnel représente la structure de la base de donnée, à savoir
- La structure des tables (nom, nom des champs)
- Les liens entre elles.
7. Qu'est ce qu'une clé étrangère ? (1 pt)
- La clé étrangère matérialise la liaison de la table vers une autre table (clé primaire).
8. Que signifient les lettres SQL ? (1 pt)
- Structured Query Language (langage d'interrogation de base de donnée)
9. En SQL, que signifient les lettres LID ? (1 pt)
Langage d'Interrogation des Données
UE Bases de Données
Examen du vendredi 22 avril 9h-10h30, documents non autorisés.
On considère la base ”avions” maintes fois vue en TD:
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, cha^?ne de caractères
ADR: ville de la résidence du pilote, cha^?ne 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, cha^?ne de caractères NUMVOL: cléde VOL, nombre entier
VILLE_DEP: ville de départ du vol, cha^?ne de caractères VILLE_ARR: ville d’arrivée du vol, cha^?ne 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 entier entre 0 et 23
Exercice A Algèbre relationnelle, contraintes, cl´es
(A.i) Quelle propriétéexprime la formule suivante:
?NUMPIL(VOL) ? ?NUMPIL(PILOTE) = 0/
[ Si on préfère, on peut aussi écrire cette formule ainsi:
R1 = Projection(VOL/NUMPIL)
R2 = Projection(PILOTE/NUMPIL) Différence(R1,R2) = 0/ ~
(A.ii) Donner la définition de:
”Les attributs {NOMPIL, ADR} constituent une cléde la relation PILOTE. ”
(A.iii) (difficile) Exprimer la propriétéprécédente sous la forme R = 0/ ou R est une expression de l’algèbre relationnelle.
Exercice B Requêtes en algèbre relationnelle
Exprimer les requêtes suivantes dans l’algèbre relationnelle (on donnera NUMPIl et NOMPIL pour chaque pilote trouvé):
(B.i) Quels sont les pilotes gagnant moins de 20000?
(B.ii) Quels sont les pilotes conduisant au moins un vol au départ de ’Bordeaux’? (B.iii) Quels sont les pilotes conduisant des avions de plus de 300 places?
(B.iv) Quels sont les pilotes ne conduisant que des avions de plus de 300 places?
(B.v) Quels sont les pilotes conduisant un vol au départ de chez eux avec un avion localisédans leur ville?
Exercice C Requêtes en SQL
Exprimer les requêtes suivantes en SQL:
(C.i) Quels sont les pilotes bordelais conduisant au moins un vol arrivant a` ’Bordeaux’? (C.ii) Quelle est le NUMAV et la LOC des avions qui font un trajet mais pas le retour? (C.iii) Quels est le salaire maximum d’un pilote niçois?
(C.iv) Quel est le salaire maximum d’un pilote et quel est le pilote le perçoit?
Exercice D Schémas
On considère une base de donn´ees bibliographique avec les entit´es suivantes:
(1). BIBLIOGRAPHIE, dont les attributs sont nom, organisme, URL
(2). R´EF´ERENCE, dont les attributs sont clé, éditeur, année, titre, auteur, (on suppose qu’il n’y a qu’un auteur par r´ef´erence) et a deux entit´es sp´ecialis´ees:
(2.1) REVUE, qui a pour attributs sp´ecifiques suppl´ementaires nom, volume, ISSN
(2.2) LIVRE qui a pour attributs sp´ecifiques suppl´ementaires ISBN et qui se sp´ecialise lui même en une entit´e:
(2.2.1). ACTES DE COLLOQUE qui a pour attribut sp´ecifique coordonnateur (on suppose qu’un volume d’actes n’a qu’un coordonnateur).
Il n’y a qu’une seule association: possède qui indique qu’une bibliographie contient une r´ef´erence. (D.i) Dessiner un diagramme entit´es associations qui d´ecrive au mieux cette situation.
(D.ii) Convertir ce diagramme en sch´ema relationnel (on pr´ecisera les avantages et inconv´enients des choix effectu´es).
(D.iii) (difficile) Proposer une description de la même base lorsqu’il peut y a voir plusieurs auteurs pour une même r´ef´erence et plusieurs coordonnateurs pour un même volume d’actes.
Examen de Bases de Données 2006
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ésociale.
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. Attention, 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 relationnels correspondants.
Exercice B
Conception de schéma relationnel: formes normales
(B.i) Donner un exemple de relation et de dépendances fonctionnelles qui soit 3NF et pas BCNF. Expliquer 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 différentes formes normales 3NF et BCNF?
(B.iv) Quel minimum peut-on garantir lors de la conception 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?
Exercice analyse Merise et algèbre relationnelle
1. Concevoir un modèle adéquat en donnant la représentation graphique selon les conventions du modèle entité-attribut-association.On rappelle qu'un typed'entité est représente par un rectangle, un attribut par un cercle et un typed'association par un losange. (2)
2. Décrire le domaine de chaque attribut (date, chaînes de caractères, nombre entier, ...) (1)
3. Matérialiser les clés des types d'entité en soulignant dans le graphe le nom du ou des attributs qui composent ces clés ; (1)
4. Donner la cardinalité de chaque type d'association sous la forme habituelle d'un couple (minimum, maximum). Justifier en quelques lignes les cardinalités de chaque type d'association. (1)
Exercice 2. Schéma relationnel (5 points)
Le but de l'exercice est de passer ce modèle conceptuel à un schéma relationnel
1. Réaliser le passage à un schéma relationnel en détaillant les étapes ;
2. Indiquer et justifier les clés.
Exercice 3. Requêtes en algèbre relationnelle (5 points)
1. Donner le nom des hôtels et leur catégorie dans lesquels le client Dupont a effectué une réservation. (1)
2. Donner les types d'activités proposées pour chaque hôtel 4 étoiles par la station où il se trouve. (1)
3. Donner les types d'activités proposées par toutes les stations. (1)
4. Donner le nom des stations proposant les activités tennis et piscine. (1)
5. Donner le nom des hôtels n'ayant aucun client pour la date du 1er janvier. (1)
Exercice 4. Requêtes en SQL (5 points)
Les requêtes suivantes font référence à la même base de données que celle décrite dans l'exercice 3.
Le but de l'exercice est d'écrire les requêtes suivantes dans le language SQL.
1. Donner le nom des stations ne disposant pas d'hôtel 5 étoiles. (1)
2. Donner pour chaque client (donné par son nom) le nom de l'hôtel, le nom de la station et les dates de ses séjours. (1)
3. Donner l'altitude moyenne des stations accessibles depuis la gare d'Alberville. (1)
4. Donner pour chaque station le nombre de chambres disponibles. Préciser le nom de la station. (1)
5. Donner le nombre d'activités que peut effectuer le client Dupont pour chacune des stations où il effectue un séjour. Préciser le nom de la station. (1)ù
Exercice 1. Modèle conceptuel (5 points)2. Décrire le domaine de chaque attribut (date, chaînes de caractères, nombre entier, ...) (1)
Sauf précision les propriétés des attributs sont valeurs nulles non admises, monovalué et atomique.
3. Matérialiser les clés des types d'entité en soulignant dans le graphe le nom du ou des attributs qui composent ces clés ; (1)
4. Donner la cardinalité de chaque type d'association sous la forme habituelle d'uncouple (minimum, maximum). Justifier en quelques lignes les cardinalités de chaque type d'association. (1)
Exercice 2. Schéma relationnel (5 points)
1. Réaliser le passage à un schéma relationnel en détaillant les étapes ;
Étape 1 :
SALLES (NumS, NomSalle, NbPlaces, Surface)
MANIFESTATION (NumM, NomM, Type, Durée)
CLIENT (NumC, NomC, NomS, Tél, Adresse, PréC)
Étape 2 : Aucune
Étape 3 : Aucune
Étape 4 :
RESERVEPOUR (NumS, NumM)
BILLET (NumC, NumM, DateB, Type)
Étape 5 :
EQUIPEMENT (NumS, Equipement)
DATE (NumM, Date)
2 Indiquer et justifier les clés. (2)
• SALLES : NumS est clé car
NumS -> NbPlaces, Surfaces, NomSalles, Equipement est un DEF par définition de l'attribut NumS
• MANIFESTATION : NumM est clé car
NumM -> NomM, Date, Type, Durée est une DFE par définition de l'attribut NumM
• CLIENT : NumC est clé car
NumC -> NomC, PréC, Adresse, Tél, NomS est une DFE par définition de l'attribut NumC
• RESERVEPOUR : NumS, NumM est clé car
NumS, NumM -> ? est une DFE car on n'a pas
1. NumS -> NumM, plusieurs manifestations peuvent avoir lieu dans la même salle (à des
dates différentes)
2. NumM -> NumS, une manifestation peut avoir lieu dans plusieurs salles
• BILLET : NumC, NumM est clé car
NumC, NumM ->DateB, Type est une DFE car on n'a pas
1. NumC -> NumM, DateB, Type, un client peut avoir des billets pour plusieurs
manifestations
2. NumM -> NumC, DateB, Type, des billets pour une manifestation peuvent être vendus à
plusieurs clients
• EQUIPEMENT : NumS, Equipement est clé car
NumS, Equipement -> ? est une DFE car on n'a pas
1. NumS -> Equipement, une salle peut être équipée de plusieurs équipements,
2. Equipement -> NumS, un même type d'équipement peut se trouver dans plusieurs salles
Exercice 3. Requêtes en algèbre relationnelle (5 points)
1. Donner le nom des hôtels et leur catégorie dans lesquels le client Dupont a effectué une réservation. (1)
(CLIENT:NomC= « Dupont ») * RESERVATION * HOTEL [NomHotel]
2. Donner les types d'activités proposées pour chaque hôtel 4 étoiles par la station où il se trouve. (1)
(HOTEL:Cat=4) * STATION * ACTIVITE [TypeActivité]
3. Donner les types d'activités proposées par toutes les stations. (1)
(ACTIVITE / STATION[NomStation])[TypeActivité]
4. Donner le nom des stations proposant les activités tennis et piscine. (1)
((ACTIVITE1:TypeActivité= « Piscine ») x
(ACTIVITE2:TypeActivité= « Tennis ») : ACTIVITE1.NumStation =
ACTIVITE2.NumStation) * STATION[NomStation]
5. Donner le nom des hôtels n'ayant aucun client pour la date du 1er janvier. (1)
HOTEL [NomHotel] -
(RESERVATION : Date < 1/1 et Nb + Date > 1/1)[NomHotel]
Exercice 4. Requêtes en SQL (5 points)
Les requêtes suivantes font référence à la même base de données que celle décrite dans l'exercice 3. Le but de l'exercice est d'écrire les requêtes suivantes dans le language SQL.
1. Donner le nom des stations ne disposant pas d'hôtel 5 étoiles. (1)
(select NomStation
from STATION)
minus
(select NomStation
from STATION, HOTEL
where STATION.NumStation = HOTEL.NumStation
and Cat = 5)
ou bien
select NomStation
from STATION, HOTEL
where STATION.NumStation = HOTEL.NumStation
and Cat <> 1
and Cat <> 2
and Cat <> 3
and Cat <> 4
2. Donner pour chaque client (donné par son nom) le nom de l'hôtel, le nom de la station et les dates de ses
séjours. (1)
select NomClient, NomHotel, NomStation, Date, Date+NbJours
from STATION S, HOTEL H, RESERVATION R, CLIENT C
where S.NumStation = H.NumStation
and H.NumHotel = R.NumHotel
and R.NumClient = C.NumClient
3. Donner l'altitude moyenne des stations accessibles depuis la gare d'Alberville. (1)
select avg (Altitude)
from STATION
where G are = « Albertville »
4. Donner pour chaque station le nombre de chambres disponibles. Préciser le nom de la station. (1)
select NomStation, sum (NbChamb)
from HOTEL, STATION
where HOTEL.NumStation = STATION.NumStation
group by NumStation
5. Donner le nombre d'activités que peut effectuer le client Dupont pour chacune des stations où il effectue un
séjour. Préciser le nom de la station. (1)
select NomStation, count (TypeActivité)
from ACTIVITE A, CLIENT C, RESERVATION R, HOTEL H, STATIN S
where A.NumStation = H.NumStation
and R.NumHotel = H.NumHotel
and R.NumClient = C.NumClient
and NomClient = « Dupont »
group by NumHotel, Date
Exercice algèbre relationnelle calcul à variable nuplet
On suppose qu'une bibliothèque gère une base de données dont le schéma est le suivant (les clés primaires des relations sont soulignées) :
Emprunt (Personne, Livre, DateEmprunt, DateRetourPrevue, DateRetourEective)
Retard (Personne, Livre, DateEmprunt, PenalitéRetard)
Exprimer, lorsque cela est possible, les requêtes suivantes en algèbre relationnelle, en calcul à variable nuplet et en SQL.
1. Quelles sont les personnes ayant emprunté le livre "Recueil Examens BD" ?
2. Quelles sont les personnes n'ayant jamais rendu de livre en retard ?
3. Quelles sont les personnes ayant emprunté tous les livres (empruntés au moins une fois) ?
4. Quels sont les livres ayant été empruntés par tout le monde (i.e. tous les emprunteurs) ?
5. Quelles sont les personnes ayant toujours rendu en retard les livres qu'elles ont empruntés ?
Dans cet exercice, le schéma relationnel est particulièrement simple, an que l'expression des requêtes soit facile à exprimer. Il s'agit néanmoins de requêtes complexes. Vous pouvez vous entraîner à exprimer ces requêtes en améliorant le schéma, c'est-à-dire en ajoutant deux relations Personne et Livre et précisant les clés étrangères dans les relations Emprunt et Retard faisant référence à une personne et à un livre.
1. Quelles sont les personnes ayant emprunté le livre "Recueil Examens BD" ?
En algèbre relationnelle :
?Personne(? Livre='Recueil...' (Emprunt))L'algèbre relationnelle est un langage composé d'opérations ensemblistes. Il permet d'indiquer comment le résultat de la requête est calculé en termes d'opérations ensemblistes sur des ensembles de nuplets (les relations). Dans cette requête par exemple, le résultat est calculé en parcourant tous les nuplets de la relation Emprunt, en y sélectionnant les nuplets dont l'attribut Livre a pour valeur 'Recueil...' et en prenant uniquement les valeurs de l'attribut
Personne (i.e. en projetant sur l'attribut Personne).En calcul relationnel :
{t.Personne | Emprunt(t) ? (u.Livre =' Recueil...') }Le calcul relationnel décrit, sous forme logique, le résultat de la requête (sans préciser comment on le calcule). Le résultat de la requête contient les valeurs de l'attribut Personne des nuplets
t de la relation Emprunt tels que l'attribut Livre corresponde à 'Recueil Examens BD'.
En SQL:
SELECT Personne
FROM Emprunt WHERE Livre = 'Recueil...'Il aurait également été possible de remplacer la clause WHERE par WHERE Livre LIKE 'Recueil%' indiquant que l'on recherche les emprunteurs des ouvrages dont le titre commence par 'Recueil'.
2. Quelles sont les personnes n'ayant jamais rendu de livre en retard ?
En algèbre relationnelle :
?Personne(Emprunt) ? ?Personne(Retard)La résultat de la requête est calculé en prenant toutes les valeurs de l'attribut Personne dans la relation Emprunt et en éliminant les valeurs de ce même attribut apparaissant également dans la relation Retard. Il s'agit d'une différence entre deux ensembles.
En calcul relationnel :
{t.Personne | Emprunt(t) ? ¬[? u Retard(u) ? (u.Personne = t.Personne) )]}
Le résultat de la requête contient les valeurs de l'attribut Personne des nuplets t de la relation Emprunt (donc des personnes empruntant) tels qu'il n'existe pas de nuplets u dans la relation Retard avec la même valeur pour l'attribut Personne (donc telles qu'il n'existe pas de retards associés à ces personnes).
En SQL, deux manières possibles, par simple traduction en SQL de la requête en calcul relationnel (le calcul relationnel étant à l'origine de la syntaxe de SQL) :
SELECT t.Personne FROM Emprunt t WHERE NOT EXISTS (SELECT * FROM Retard u WHERE u.Personne=t.Personne
)
SELECT Personne FROM Emprunt WHERE Personne NOT IN (SELECT Personne FROM Retard)
Les variables nuplet (ex. t et u) ne sont nécessaire que lorsqu'il y a ambiguïté au niveau des noms d'attributs (cf. requête de gauche).
3. Quelles sont les personnes ayant emprunté tous les livres (empruntés au moins une fois) ?
En algèbre relationnelle :
?Personne,Livre(Emprunt) ÷ ?Livre(Emprunt)Le résultat de cette requête est calculé en utilisant l'opérateur de division. Pour une bonne compréhension de la division.
La sous-requête ?Livre(Emprunt) correspond à la liste des livres empruntés. Le résultat de la sous-requête ? Personne,Livre(Emprunt) contient tous les couples (Personne, Livre emprunté au moins une fois par cette personne). Le résultat de la division sera donc la liste des personnes associées, dans le résultat de ?Personne,Livre(Emprunt), à chacun des livres apparaissant dans le résultat de la requête ?Livre(Emprunt).
En calcul relationnel :
{t.Personne | Emprunt(t) ? [? u (Emprunt(u)) =? (? v Emprunt(v) ? (v.Personne = t.Personne) ? (u.Livre = v.Livre) )]}
Le résultat de la requête contient les valeurs de l'attribut Personne des nuplets t de la relation Emprunt tels que quel que soit un nuplet s'il s'agit d'un livre emprunté (donc d'un nuplet u dans Emprunt) alors on trouve un nuplet v dans Emprunt associant cette personne à ce livre (c'est-à-dire
v.Personne = t.Personne et u.Livre = v.Livre).
On peut également l'écrire de la manière suivante :
{t.Personne | Emprunt(t) ? [? u ¬(Emprunt(u)) ? (? v Emprunt(v) ? (v.Personne =
t.Personne) ? (u.Livre = v.Livre) )]}
Ce qui signie que le résultat de la requête contient les valeurs de l'attribut Personne des nuplets t de la relation Emprunt tels que quel que soit un nuplet u soit c'est n'est pas un nuplet de Emprunt soit (implicitement c'est un nuplet de Emprunt et) on trouve un nuplet v dans Emprunt associant cette personne à ce livre (c'est-à-dire
v.Personne = t.Personne et u.Livre = v.Livre).
D'où dit de manière négative :
{t.Personne | Emprunt(t)? ¬[? u Emprunt(u) ¬(? v Emprunt(v)?(v.Personne = t.Personne) ? (u.Livre = v.Livre) )]}
En SQL, simple traduction de la requête en calcul relationnel :
SELECT t.Personne
FROM Emprunt t
WHERE NOT EXISTS ( SELECT *
FROM Emprunt u WHERE NOT EXISTS ( SELECT *
FROM Emprunt v
WHERE v.Personne=t.Personne
AND v.Livre=u.Livre
)
)
4. Quels sont les livres ayant été empruntés par tout le monde (i.e. tous les emprunteurs) ?
En algèbre relationnelle :
?Personne,Livre(Emprunt) ÷ ?Personne(Emprunt)Le résultat de cette requête est calculé en utilisant également l'opérateur de division.
La sous-requête ?Personne(Emprunt) correspond à la liste des emprunteurs. Le résultat de la sous-requête ? Personne,Livre(Emprunt) contient tous les couples (Personne ayant emprunté au moins une fois, Livre emprunté au moins une fois par cette personne). Le résultat de la division sera donc la liste des livres associés, dans le résultat de ?Personne,Livre(Emprunt), à chacun des emprunteurs apparaissant dans le résultat de la requête ? Personne(Emprunt).
En calcul relationnel :
{t.Livre | Emprunt(t) ? [? u (Emprunt(u)) =? (? v Emprunt(v) ? (u.Livre = t.Livre) ? (v.Personne = u.Personne) )]}
Le résultat de la requête contient les valeurs de l'attribut Livre des nuplets t de la relation Emprunt tels que quel que soit un nuplet s'il s'agit d'un emprunteur (donc d'un nuplet u dans Emprunt) alors on trouve un nuplet v dans Emprunt associant ce livre à cet emprunteur (c'est-à-dire u
.Livre = t.Livre et v.Personne = u.Personne ).
On peut également l'écrire de la manière suivante :
{t.Livre | Emprunt(t) ? [? u ¬(Emprunt(u)) ? (? v Emprunt(v) ? (u.Livre = t.Livre) ? (v.Personne = u.Personne) )]}
Ce qui signifie que le résultat de la requête contient les valeurs de l'attribut Livre des nuplets t de la relation Emprunt tels que quel que soit un nuplet soit il ne s'agit pas d'un nuplet u dans Emprunt soit (il s'agit d'un d'un nuplet u dans Emprunt et) il existe un nuplet
v dans Emprunt associant ce livre à cet emprunteur (c'est-à-dire
u.Livre = t.Livre et v.Personne = u.Personne ).D'où dit de manière négative :
{t.Livre | Emprunt(t)? ¬[? u Emprunt(u) ¬(? v Emprunt(v) ? (u.Livre = t.Livre) ? (v.Personne = u.Personne) )]}
En SQL, simple traduction de la requête en calcul relationnel :
SELECT t.Livre FROM Emprunt t
WHERE NOT EXISTS ( SELECT * FROM Emprunt u
WHERE NOT EXISTS ( SELECT * FROM Emprunt v
WHERE u.Livre=t.Livre AND v.Personne=u.Personne
)
)
5. Quelles sont les personnes ayant toujours rendu en retard les livres qu'elles ont empruntés ?
En algèbre relationnelle : Il n'est pas possible d'exprimer cette requête par une division.
La requête est donc décomposée en deux sous-requêtes. La requête, R1, ci-dessous, retourne la liste des personnes ayant emprunté au moins un livre sans le rendre en retard.
R1= ?Personne [?Personne,Livre,DateEmprunt(Emprunt) ? ?Personne,Livre,DateEmprunt(Retard)]
La requête ci-dessous enlève de la liste des personnes qui empruntent des livres (sous-requête de gauche) la liste des personnes ayant rendu au moins un livre sans retard (requête R1). Cela correspond à comment calculer le résultat de la requête que l'on recherche.
?Personne(Emprunt) ? R1
En calcul relationnel :
{t.Personne | Emprunt(t) ? [? u [Emprunt(u) ? (u.Personne = t.Personne)] =? (? v Retard(v) ? (v.Personne = u.Personne) ? (u.Livre = v.Livre) )]}
Le résultat de la requête contient les valeurs de l'attribut Personne des nuplets t de la relation Emprunt tels que quel que soit un nuplet s'il s'agit d'un livre emprunté par cette personne (donc d'un nuplet u dans Emprunt tel que u.Personne = t.Personne) alors on trouve un nuplet v dans Retard associant cette personne à ce livre (c'est-à-dire
v.Personne = u.Personne et u.Livre = v.Livre).On peut également écrire :
{t.Personne | Emprunt(t) ? [? u ¬[Emprunt(u) ? (u.Personne = t.Personne)] ? (? v Retard(v) ? (v.Personne = u.Personne) ? (u.Livre = v.Livre) )]}
Le résultat de la requête contient les valeurs de l'attribut Personne des nuplets
tde la relation Emprunt tels que quel que soit un nuplet soit il ne s'agit pas d'un livre emprunté par cette personne (donc d'un nuplet u dans Emprunt tel que
u.Personne = t.Personne)soit on trouve un nuplet
v dans Retard associant cette personne à ce livre (c'est-à-dire
v.Personne = u.P ersonne et u.Livre = v.Livre).
D'où dit de manière négative :
{t.Personne | Emprunt(t)? ¬[? u Emprunt(u)?(u.Personne = t.personne) ¬(? v Retard(v)? (v.Personne = u.Personne) ? (u.Livre = v.Livre) )]}
En SQL, là encore , simple traduction de la requête en calcul relationnel:
SELECT t.Personne
FROM Emprunt t
WHERE NOT EXISTS (SELECT * FROM Emprunt u WHERE u.Personne=t.Personne
AND NOT EXISTS (SELECT * FROM Retard v WHERE v.Personne=u.Personne
AND v.Livre=u.Livre )
)
TD 8 : Introduction aux bases de données Le langage SQL
Travaux Dirigés 8
Introduction aux bases de données
Le langage SQL
Exercice 1 – Clinique de médecine Soit la base de données suivante :
PATIENT (NoPatient, NoAssSociale, Nom, Prenom)
MEDECIN (NoMedecin, Nom, Prenom)
DIAGNOSTIC (NoDiagnostic, description)
TRAITEMENT (NoTraitement, description)
ENTREE_DOSSIER (NoDossier, DateVisite, #NoPatient, #NoMedecin, #NoTraitement, #NoDiagnostic)
- Donnez le code SQL pour créer la base de données
- Vous réalisez que la taille de l'attribut "description" de la table DIAGNOSTIC n'est pas adéquate. Donnez le code SQL pour la modifier pour une chaîne de longueur variable de 255 caractères maximum.
- Donnez le code SQL pour ajouter les attributs "NoTelephone" et "DateNaissance" dans la table PATIENT.
- Donnez le code SQL pour entrer les données suivantes dans la base de données
Table PATIENT
|
NoPatient
|
NoAssSociale
|
Nom
|
Prenom
|
NoTelephone
|
DateNaissance
|
|
111111
|
12345678
|
Delisle
|
Pierre
|
22 33 44 33 22
|
1977-04-11
|
|
111112
|
87654321
|
Delisle
|
Sylvain
|
33 22 44 33 55
|
1975-08-21
|
|
111113
|
23234433
|
Tremblay
|
Sylvain
|
418-275-1232
|
1980-01-30
|
Table MEDECIN
|
NoMedecin
|
Nom
|
Prenom
|
|
12345
|
Lajoie
|
René
|
|
67899
|
Dion
|
Céline
|
Table DIAGNOSTIC
|
NoDiagnostic
|
Description
|
|
1
|
Migraine
|
|
2
|
Fracture au bras
|
Table ENTREE DOSSIER
Table TRAITEMENT
|
NoTraitement
|
Description
|
|
1
|
2 Cachets à toutes les 4 heures
|
|
2
|
Placer le bras dans un plâtre
|
|
NoDossier
|
DateVisite
|
NoPatient
|
NoMedecin
|
NoTraitement
|
NoDiagnostic
|
|
1
|
2008-04-25
|
111111
|
12345
|
1
|
1
|
|
2
|
2008-04-26
|
111111
|
67899
|
2
|
2
|
|
3
|
2008-04-26
|
111111
|
12345
|
1
|
2
|
|
4
|
2008-04-26
|
111112
|
67899
|
1
|
1
|
- Vous avez entré le mauvais traitement dans l'entrée de dossier no. 3. Modifiez l'enregistrement pour donner le traitement no. 2 au lieu du no. 1.
- Effectuez les requêtes SQL simples suivantes :
- Afficher toutes les informations de tous les patients;
- Afficher le nom et le prénom de tous les patients;
- Afficher le nom et le prénom des patients dont le nom de famille est 'Delisle';
- Afficher le nom et le prénom des patients nés après 1976;
- Afficher les noms de famille différents des patients;
- Afficher les patients en ordre croissant de date de naissance;
- Afficher les entrées de dossier où la patient traité est de no. 111111 et le médecin traitant est de no. 67899.
7) Effectuez les jointures suivantes :
- Afficher toutes les entrées de dossier et les informations de leurs patients respectifs;
- Afficher les entrées de dossier de Pierre Delisle;
- Afficher la description des traitements dont a bénéficié Pierre Delisle;
- Afficher, du plus jeune au plus vieux, le nom et le prénom des patients traités par René Lajoie le 26 avril 2008.
Exercice 2 – Bibliothèque
Soit la base de données suivante :
|
SPECIALITE (NoSpecialite, Description)
SECTION (NoSection, Emplacement, Description)
LIVRE (CodeISBN, Titre, #NoSpecialité, #NoSection) FOURNISSEUR (NoFournisseur, Nom)
EXEMPLAIRE (NoExemplaire, #CodeISBN, #NoFournisseur) ABONNE (NoAbonne, Nom, Prenom)
CARTE (NoCarte, DateDebut, DateFin, #NoAbonne)
EMPRUNT (NoExemplaire, NoCarte, DateLocation, DateRetour)
|
1) Effectuez les requêtes SQL suivantes :
- Afficher la liste des livres classés dans les sections 1 et 4;
- Ajouter un attribut adresse à la table abonné;
- Ajouter le fournisseur 'Livres du Québec inc.' à la base de données';
- Afficher le nom et le prénom des abonnés qui se sont abonnés ou ont renouvelé leur carte en 2008;
- Afficher le code et le titre des livres qui ont été empruntés le 28 avril 2008, triés par ordre alphabétique de titre;
- Afficher le nom et le prénom des abonnés qui ont déjà emprunté le livre intitulé 'Nos amis les français';
- Prolonger tous les abonnements échus le 25 avril 2010 au 25 mai 2010;
- Afficher le titre des livres de science-fiction empruntés durant le mois d'avril 2008.
