Corrigé SGBD

Bases
de
données
Corrigés
des
exercices
OC
informatique
© 2011, Manuel Fragnière, Lycée Denis-de-Rougemont.
OC
informatique
1.
SGBD : Modélisation.
Pour organiser un karaoké, une association veut créer une base de données qui
lui permettra d’enregistrer le contenu de tous les DVD dont elle dispose. Les
organisateurs veulent ensuite pouvoir éditer la liste de tous les chanteurs, de tous
les titres et le sommaire de chaque DVD.
a) Dessinez le modèle entité-association correspondant.
b) A partir du diagramme entité-association que vous aurez construit, écrivez
les requêtes SQL permettant de créer cette base de données.
Réponses :
a) Modèle E-A de la base de données :
DVD
m
comporte
m
CHANSONS
1
chante
m
CHANTEURS
b) Requêtes SQL permettant de créer la base de données :
create table DVD (
idDVD int auto_increment primary key,
titre varchar(80)
);
create table Chansons (
idChanson int auto_increment primary key,
titre varchar(80),
idChanteur int
);
create table Chanteurs (
idChanteur int auto_increment primary key,
nom varchar(80)
);
Page 2
OC
informatique
create table Comporte (
idDVD int,
idChanson int
);
Pour tester la base de données créées, voici en bonus quelques données :
insert into DVD values(null, "A la poursuite d'Octobre Rouge");
insert into DVD values(null, "Thriller");
insert into DVD values(null, "Casino Royale");
insert into Chanteurs values(null,'Basil Poledouris');
insert into Chanteurs values(null,'Michael Jackson');
insert into Chanteurs values(null,'David Arnold');
insert into Chansons values(null,'Hymn To Red October',1);
insert into Chansons values(null,'Nuclear Scam',1);
insert into Chansons values(null,'Thriller',2);
insert into Chansons values(null,"The Name's Bond... James Bond",3);
insert into Chansons values(null,'African Rundown',3);
insert into Chansons values(null,'The End Of An Aston Martin',3);
insert into Comporte values(1,1);
insert into Comporte values(1,2);
insert into Comporte values(2,3);
insert into Comporte values(3,4);
insert into Comporte values(3,5);
insert into Comporte values(3,6);
2.
SGBD : Modélisation.
Une entreprise aimerait mettre en place un système informatique pour gérer
efficacement l’un de ses secteurs d’activité : une chaîne d’hôtels situés dans
diverses grandes villes. Elle compte sur vous, analyste-concepteur de bases de
données pour traduire les règles de gestion suivantes dans un modèle entitéassociation que vous transformerez ensuite en un schéma de base de données
relationnelle.
-
Chaque HOTEL a plusieurs employés, dont un-e directeur-trice unique.
-
Chaque EMPLOYE est affecté à un seul hôtel.
-
Chaque HOTEL possède plusieurs CHAMBRES et une chambre appartient
toujours à un hôtel.
-
Les hôtes n’offrent que deux catégories de chambres mutuellement
exclusives, à savoir :
o SIMPLE : Attributs spécifique : le type-de-salle-de-bain (valeurs
possibles : « douche » ou « bain »).
Page 3
OC
informatique
o DOUBLE : Attributs spécifique : le type-de-lit (valeurs possibles : «
double » ou « jumeau »).
Les attributs communs aux deux catégories sont : le numéro et la
description de la chambre.
-
Chaque employé peut (ou non) être appelé à effectuer des services
(nettoyage, inspection du mini bar, réparation, ...) dans les chambres et
chaque chambre est « servie » de nombreuses fois durant l’année. Les
attributs spécifiques pour chaque service sont le type, la date et un
commentaire.
-
Sur une année, chaque chambre est réservée par au moins un CLIENT
enregistré dans la base de données. Par contre, un client peut soit ne rien
réserver, soit réserver une ou plusieurs chambres. De plus, chaque
réservation doit offrir les informations supplémentaires suivantes : la datede-réservation, la date-d-arrivée, la durée et le type-de-séjour (valeurs
possibles : « basic », « demi-pension », « pension complète »).
Le modèle entité-association est représenté de manière incomplète par un
diagramme dans la figure de la page suivante.
a) Complétez ce diagramme entité-association. N’oubliez pas d’y indiquer,
conformément au formalisme étudié durant le cours :
o Le type (1, c, m ou mc) de chaque association,
o Les propriétés de chaque structure ‘EST UN’ (‘IS A’) ou ‘MEMBRE DE’
(‘PART OF’) éventuellement présente dans le diagramme.
b) A partir du diagramme entité-association que vous venez de construire,
complétez le schéma de base de données relationnelle de la page suivante
en y ajoutant un nombre minimum de tables selon les règles de passage du
diagramme entité-association au schéma relationnel.
-
Chaque attribut défini comme clé primaire doit être souligné et en gras.
-
Pour chaque clé étrangère, il faut déclarer la contrainte d’intégrité
référentielle sous la forme :
FOREIGN KEY ( nom_clé_étrangère ) REFERENCES nom_table ( nom_clé_primaire )
Page 4
OC
informatique
Réponses :
a) Diagramme entité-association
Direction
c
1
EMPLOYE
mc
1
HOTEL
CLIENT
m
mc
m
Affectation
Appartenance
Réservation
1
m
Service
CHAMBRE
m
1
disjoint complet
1
1
SIMPLE
DOUBLE
Page 5
OC
informatique
b) Schéma de base de données relationnelle (à compléter)
En surligné jaune les réponses
1. HOTEL ( hotelID, nomHotel, adresseHotel, descriptionHotel, directeurID )
FOREIGN KEY ( directeurID ) REFERENCES EMPLOYE ( employeID )
2. EMPLOYE ( employeID, nomEmploye, fonction, hotelID )
FOREIGN KEY ( hotelID ) REFERENCES HOTEL (hotelID)
3. CLIENT ( clientID, nomClient, adresseClient, telephoneClient, emailClient )
Ajoutez à la table CHAMBRE les attributs manquants et déclarez la contrainte
d’intégrité référentielle pour chaque clé étrangère éventuellement présente :
4. CHAMBRE ( chambreID, numChambre, descriptionChambre,
categorieChambre, hotelID )
FOREIGN KEY (hotelID)
REFERENCES HOTEL(hotelID)
Ajoutez ci-dessous les tables manquantes en respectant les conventions de
notation définies au début de cette page pour mettre en évidence les clés
primaires et les contraintes d’intégrité référentielle :
5. SERVICE ( employeID, chambreID, typeService, dateService, commentaire )
FOREIGN KEY (employeID) REFERENCES EMPLOYE(employeID)
FOREIGN KEY (chambreID) REFERENCES CHAMBRE(chambreID)
6. RESERVATION ( chambreID, clientID, dateReservation, dateArrivee, duree,
typeSejour )
FOREIGN KEY (chambreID) REFERENCES CHAMBRE(chambreID)
FOREIGN KEY (clientID)
REFERENCES CLIENT(clientID)
7. SIMPLE ( csimpleID, typeBain )
FOREIGN KEY (csimpleID) REFERENCES CHAMBRE(chambreID)
8. DOUBLE ( csoubleID, typeLit )
FOREIGN KEY (cdoubleID) REFERENCES CHAMBRE(chambreID)
Page 6