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