TD I.

MODÈLE ENTITÉ
ASSOCIATION
BCG S2
SÉRIE N°1
 EXERCICE 1
Établir le diagramme du modèle Entité-Association, en ajoutant les cardinalités, sachant que :
▪ Entités : ACTEURS, FILMS, REALISATEURS, CINEMA

▪ Attributs :
▪ Nom et prénom pour l’entité ACTEURS
▪ Titre et Metteur en scène pour l’entité FILMS
▪ Nom et prénom pour l’entité REALISATEURS
▪ Nom et Adresse pour l’entité CINEMA

▪ Associations :
▪ Joue entre FILMS et ACTEURS
▪ Affiche entre CINEMA et FILMS
▪ Réalise entre REALISATEURS et FILMS
 EXERCICE 1
EXPLICATIONS SUR LES CARDINALITÉS

▪ Acteurs vers Films (1,n) : ▪ Réalisateurs vers Films (1,n) :

▪ (1) un acteur a joué dans au moins un film. ▪ (1) Un réalisateur a réalisé au moins un film.
▪ (n) un acteur peut avoir joué dans plusieurs films. ▪ (n) Un réalisateur peut avoir réalisé plusieurs
films.
▪ Films vers Acteurs (0,n) :
▪ (0) : un film n’ayant pas d’acteurs, possible si c’est ▪ Films vers CINEMA (1,n) :
un film documentaire. ▪ (1) Un film était au moins à l'affiche d’un
▪ (n) : un film peut avoir plusieurs acteurs. cinéma.
▪ (n) Plusieurs films peuvent être à l'affiche d’un
▪ Films vers Réalisateurs (1,1) : cinéma.
▪ (1) Un film a au moins un réalisateur.
▪ CINEMA vers Films (1,n) :
▪ (1) Il y a au plus un réalisateur (max=1) ==>Un
film a un seul réalisateur (le cas général). ▪ Un cinéma a au moins un film à projeter.
▪ Un cinéma peut avoir plusieurs films à projeter.
 EXERCICE 2
▪ Une équipe est composée de plusieurs joueurs et chaque joueur appartient à une seule équipe.

▪ Une équipe est caractérisé par Identifiant, Libellé.

▪ Un joueur est caractérisé par un Identifiant, Nom, Prénom, Age et Nationalité.

▪ Proposez un modèle E/A de données (avec entités, identifiants, attributs, associations, cardinalités ).
 EXERCICE 3
▪ Les salariés d'une société sont caractérisés par un code, un nom, prénom, âge, fonction et salaire.

▪ chaque salarié habite dans une ville et travaille dans un établissement. Chaque établissement se trouve dans une ville.

▪ Les villes sont caractérisées par un code et un nom.

▪ Un établissement est caractérisé par un code, nom, adresse et téléphone.

▪ Proposez un modèle E/A de données (avec entités, identifiants, attributs, associations, cardinalités).
 EXERCICE 4
▪ Un employé est identifié par son numéro de sécurité sociale (SSN) et a un nom et un prénom.

▪ Chaque employé peut être supervisé par au plus un autre employé.

▪ Donner un modèle entité-association de ce problème.

 EXERCICE 5
▪ Un organisme culturel souhaite mettre en place une base de données pour le suivi des films projetés dans les salles de
cinéma d'une ville.
▪ Pour simplifier, on considère qu'une salle de cinéma ne projette qu'un seul film à une heure donnée.
▪ Toutefois, un même film peut être projeté simultanément dans plusieurs salles. Pour des raisons d'organisation et
d'espace, une salle de cinéma ne projette chaque film qu'une seule fois par jour et toujours à la même heure. On
représentera les films actuellement à l'affiche. On ne souhaite pas archiver l'historique des projections des films par salle.
▪ L'organisme culturel effectue régulièrement des sondages sur un groupe de spectateurs fidèles pour recueillir leur
appréciation sur tous les films qu'ils ont vus.
▪ Pour simplifier, on considère que chaque spectateur émet une appréciation qui peut être résumée par bien, quelconque,
nul. On ne s'intéresse pas à l'information sur la salle dans laquelle il a regardé ce film.
▪ On dispose pour chaque salle des données suivantes : nom, adresse et liste des films projetés avec l'heure de leur
projection dans la salle. Les informations stockées sont celles de la semaine en cours.
▪ Chaque spectateur est identifié par un numéro. On connaît d'autre part son nom, son prénom, son adresse, sa date de
naissance et sa catégorie professionnelle. Pour chaque film, on souhaite stocker son numéro de visa d'exploitation, son
titre, le nom du réalisateur et son année de sortie. Enfin, on enregistre, pour chaque spectateur interrogé, la liste des
films visionnés et son appréciation sur chacun des films.
▪ On considère que 2 salles ne peuvent porter le même nom et que 2 films ne peuvent pas avoir le même numéro de visa
d’exploitation
EXERCICE 5
 EXERCICE 6
▪ On souhaite gérer des réservations dans une compagnie d’hôtels. A cette fin, on considère le diagramme Entité

Association suivant (les attributs soulignés sont les identifiants des entités.

▪ À partir de ce diagramme, répondre aux questions suivantes :

 EXERCICE 6
▪ Est-il possible d’avoir des clients homonymes ?
▪ oui, car le nom n’identifie pas les clients. Il peut donc y avoir des homonymes, ils auront des numéros
différents.
▪ Un client peut-il réserver plusieurs chambres à une date donnée ?
▪ oui, mais il faut faire plusieurs réservations.
▪ Une réservation peut-elle correspondre à plusieurs chambres ?
▪ non, chaque réservation correspond à une et une seule chambre.
▪ Est-il possible de réserver une chambre sur plusieurs jours ?
▪ oui, mais il faut faire plusieurs réservations.
SÉRIE N°2
 EXERCICE 1
▪ On souhaite gérer des réservations dans une compagnie d’hôtels. A cette fin, on considère le modèle entité-association
suivant (les attributs soulignés sont les identifiants des entités) .
▪ Proposer un schéma relationnel correspondant au diagramme EA en indiquant les clés primaires et étrangères..

Client(numéroCl, nom, prénom)

Réservation(numéroRés, date, #numéroCl, #numéroCh)
Hotel(nomHot, adresse)
Chambre(numéroCh, prix, #nomHot)
 EXERCICE 2
▪ Soit le schéma entité-association suivant, proposer un schéma relationnel en indiquant les clés primaires et étrangères.

Patient(NuméroSS, nomPatient)
Médicament(Code, Libelle)
Medecin(Matricule, NomMéd)
Consultation( NuméroCons,Datecons, #NuméroSS,#Matricule)
Prescrire(#NuméroCons, #Code)
 EXERCICE 3
▪ Soit la relation R(A,B,C) avec l’extension suivante :
A B C
▪ Quelle est la clé primaire de la relation R.
a1 b1 c1
a2 b1 c2
a2 b2 c3
a1 b2 c2
a2 b3 c3
a1 b3 c3

▪ A ne peut pas être clé de R car la valeur a1 et a2 de A se répètent dans la relation R.

▪ De même pour B (b1, b2 et b3) et C (c2 et c3).
▪ (A, B) est une clé composée de R car les valeurs de (A,B) ne se répètent pas dans R.
▪ Les valeurs de (B,C) et (A,C) se répètent exemple
▪ (b,c) : le couple B3,C3 se répètent
▪ (A,C) : le couple a2, c3 se répètes
 EXERCICE 4
▪ On trouve dans un SGBD relationnel les relations ci-dessous.

▪ Reconstruire le schéma E/A correspondant.

▪ GARDIEN(num_employé, nom_gardien, prénom_gardien, adresse, salaire, date_embauche, couloir, code_prison)

▪ PRISONNIER(numéro_prisonnier, nom_prisonnier, prénom_prisonnier, sexe, date_arrivée, durée_peine, motif,

num_cellule, couloir, code_prison)
▪ PRISON(code_prison, nom_prison, ville, capacité)

▪ COULOIR(couloir, code_prison)

▪ CELLULE(num_cellule, couloir, code_prison, nb_lits, surface)

EXERCICE 4
 SÉRIE N°3

SQL : standard query language

 RAPPEL
▪ Création d'une table

CREATE TABLE nom_table(

Attribut1 TYPE_Att1, • Type de données
Attribut2 TYPE_Att2, • INT : Entier à n (<=38) chiffres,
Primary key (Attribut),
Foreing key(Attribut) • FLOAT: Réel à n chiffres au total, m
);
après la virgule
▪ Ajout des données (une ligne) dans une tables
• Char(n) : Chaîne de taille fixe
INSERT INTO nom_table(attribut1, attribut2, …)
VALUES (valeur_attribut1, valeur_attribut2, …) • Varchar(n) : Chaîne de taille variable

▪ Intérogation de la base ( recherche des données) • Date : Date au format

SELECT attribut(s) • Time : date pour heure et minute

FROM table(s)
[WHERE condition(s)]
[ORDER BY attribut(s)[ASC|DESC]];
[GROUP BY attribut(s)[HAVING condition]]
 RAPPEL
Modifier la structure d'une table :

▪ Ajouter un attribut dans la table

ALTER TABLE nom_table ADD (attribut1 TYPE, …)

▪ Suppression d’attribut d’une table :

ALTER TABLE nom_table DROP COLUMN nom_attribut;

▪ Modification d’attributs

ALTER TABLE nom_table MODIFY(attribut TYPE);

 RAPPEL
Modifier les données d'une table
▪ Ajout d’un tuple

INSERT INTO nom_table(att1, att2, …)

VALUES (val_att1, val_att2, …)

▪ Mise à jour d’un attribut

UPDATE nom_table SET attribut= Valeut

[WHERE condition];

▪ Suppression de tuples

DELETE FROM nom_table [WHERE condition];

 RAPPEL
▪ Fonctions d’agrégation

▪ COUNT( ) : nombre de valeurs

▪ AVG( ) : moyennedes valeurs

▪ SUM( ): somme des valeurs

▪ MIN( ) : valeur minimum

▪ MAX( ) : valeur maximum

 EXERCICE 1
▪ Soit la table Livre suivante.

Id_livre titre Isbn_10 auteur prix

1 Fortresse digitale 2709626306 Dan Brown 20,5
2 La jeunne fille et l nuit 2253237620 Guillaume Musso 21,9

3 T'choupi se brosse les dents 2092589547 Thierry Courtin 5,7

4 La dernière chasse 2226439412 Jean-Christophe Grangé 22,9

5 Le signal 2226319484 Guillaume Musso 23,9

▪ Ecrire des requêtes SQL pour :

 EXERCICE 1
▪ Créer la table Livre. CREATE TABLE Livres (
id_livre INT NOT NULL auto_increment,
titre VARCHAR(100), // chaine de longueur variable
isbn_10 CHAR(10), // chaine de longueur fixe
auteur VARCHAR(50),
prix FLOAT,
PRIMARY KEY (id_livre)
);
▪ Insérer les valeurs dans la table Livre (selon les enregistrements indiquées dans la table Livre).

INSERT INTO Livres VALUES (1,'Forteresse digitale’, ‘2709626306’, ‘Dan Brown’, 20.5);
INSERT INTO Livres VALUES ('La jeune fille et la nuit’, ‘2253237620’, ‘Guillaume Musso’, 21.9);
….
INSERT INTO Livres VALUES ('Le Signal’, ‘2226319484’, ‘Guillaume Musso’, 23.9);
▪ Afficher l'ensemble des enregistrements de la table.

SELECT *
FROM Livres;
 EXERCICE 1
▪ Afficher les titres des livres écrits par Guillaume Musso.

SELECT titre
FROM Livres
WHERE auteur= ‘Guillaume Musso’;
▪ Sélectionner uniquement les livres qui ont un prix strictement supérieur à 20.

SELECT *
FROM Livres
WHERE prix > 20;
▪ Trier les enregistrements de la table Livre du prix le plus élevé aux prix le plus bas.

SELECT *
FROM Livres
ORDER BY prix DESC;
 EXERCICE 1
▪ Afficher les livres de la table Livre qui ont un prix compris entre 20 et 22.

SELECT *
FROM Livres
WHERE prix >= 20 AND prix <=22;

▪ Afficher tous les livres de la table Livre à l'exception de celui portant la valeur pour la colonne isbn_10 : 2092589547 ?

SELECT *
FROM Livres
WHERE isbn_10 != 2092589547;
 EXERCICE 2
▪ Soit la base de données relationnelle des vols quotidiens d’une compagnie aérienne
contenant les tables Avion, Pilote et Vol :
▪ Avion (NA : numéro avion de type entier, Nom : nom avion de type texte (12), Capacite : capacité avion de
type entier, Localite : ville de localité de l’avion de type texte (10) )
▪ Pilote (NP : numéro pilote de type entier, Nom : nom du pilote de type texte (25), Adresse : adresse du pilote
de type texte (40) )
▪ Vol (NV : numéro de vol de type texte (6), NP : numéro de pilote de type entier, NA : numéro avion de type
entier,VD : ville de départ de type texte (10), VA : ville d’arrivée de type texte (10), HD : heure de départ , HA :
heure d’arrivée )

▪ Ecrire des requêtes SQL pour:

 EXERCICE 2
▪ Créer la table Vol CREATE TABLE Vol(
NV VARCHAR(10) NOT NULL,
NP INT,
NA INT,
VD VARCHAR(10),
VA VARCHAR(10),
HD TIME,
HA TIME,
PRIMARY KEY(NV),
FOREIGN KEY(NP),
FOREIGN KEY(NA)
)
▪ Insérer les avions suivants dans la table Avion :

▪ (100, AIRBUS, 300, RABAT), (101,B737,250,CASA), (102, B737,220,RABAT)

INSERT INTO Avion VALUES (100,’AIRBUS', 300, ‘RABAT’)

INSERT INTO Avion VALUES (101,’B737’,250,’CASA’)
INSERT INTO Avion VALUES(101, ‘B737’,220,’RABAT')
 EXERCICE 2
▪ Afficher tous les avions SELECT*
FROM Avion;
▪ Afficher tous les avions par ordre alphabétique croissant sur le champ nom
SELECT *
FROM Avion order by Nom asc;
▪ Afficher les noms et les capacités des avions
SELECT nom, capacite
FROM Avion;
▪ Afficher les localités distinctes des avions

SELECT distinct Localite

FROM Avion;
▪ Afficher les avions dont la localité et Rabat ou Casa
SELECT *
FROM Avion
WHERE Localite='Rabat' or Localite='Casa' ;
 EXERCICE 2
▪ Afficher le nom et l’adresse des pilotes assurant les vols IT100 et IT104
SELECT Nom, Adresse
FROM Pilote, Vol
WHERE Pilote.NP= Vol.NP and NV='IT100' or NV='IT104';
▪ Afficher les numéros des pilotes qui sont en service
SELECT NP
FROM Vol;
▪ Afficher les numéros des pilotes qui ne sont pas en service
SELECT NP
FROM Pilote
WHERE NP not in (select NP from Vol);
▪ Afficher les noms des pilotes qui conduisent un AIRBUS
SELECT Pilote.NOM
FROM Pilote, Vol, Avion
WHERE Pilote.NP= Vol.NP and Avion.NA=Vol.NA and
Avion.NOM='AIRBUS';
 EXERCICE 2
▪ Modifier la capacité de l’avion numéro 101, la nouvelle capacité et 220
UPDATE Avion
SET Capacite=220 WHERE NA=101;
▪ Supprimer les avions dont la capacité et inférieure à 200

DELETE FROM Avion WHERE Capacite <200;

▪ Afficher la capacité maximale, minimale, moyenne des avions

SELECT Max(Capacite), Min(Capacite), Avg(Capacite) FROM Avion;

▪ Afficher les données des avions dont la capacité et la plus basse
SELECT *
FROM Avion
WHERE capacite=min(capacite);
▪ Afficher les données des avions dont la capacité et supérieure à la capacité moyenne

SELECT* FROM Avion

WHERE capacite>=avg(capacite);
SÉRIE N°4
 EXERCICE 1
▪ Soit le schéma relationnel suivant :

▪ Départements(ID_Dep, Nom_Dep, VILLE)

▪ Employes(ID_Emp, Nom_Emp, JOB, SALAIRE, COMMISSION, #ID_Dep)

▪ Exprimez en SQL les requêtes suivantes :

▪ Ajouter à la table Départements l’attribut ADRESSE qui est de type chaîne de caractères de taille 200
ALTER TABLE Départements ADD ADRESSE VARCHAR(200);
▪ Supprimer de la table Départements l’attribut VILLE
ALTER TABLE Départements DROP VILLE;
▪ Donner la liste des employés ayant une commission
SELECT *
FROM Employes
WHERE COMMISSION NOT NULL;
 EXERCICE 1
Départements(ID_Dep, Nom_Dep, VILLE)
▪ Donner le salaire moyen des employés
Employes(ID_Emp, Nom_Emp, JOB, SALAIRE, COMMISSION, #ID_Dep)
SELECT AVG(SALAIRE) FROM Employes ;
▪ Donner le salaire moyen du département appelé Production
SELECT AVG(Employe.SALAIRE)
FROM Employes , Departement
Where Employe.ID_Dep=Departements.ID_Dep
and Departements.Nom_Dep="Production" ;
▪ Donner les Id de départements et leur salaire maximum
SELECT ID_Dep, MAX(SALAIRE)
FROM Employes
GROUP BY ID_Dep ;
▪ Donnez les différentes professions et leur salaire moyen
SELECT job, AVG(SALAIRE)
FROM Employes
GROUP BY job ;
 EXERCICE 1
Départements(ID_Dep, Nom_Dep, VILLE)
Employes(ID_Emp, Nom_Emp, JOB, SALAIRE, COMMISSION, #ID_Dep)

▪ Donnez le salaire moyen par profession le plus bas

SELECT AVG (SALAIRE)
FROM Employes
GROUP BY JOB
ORDER BY AVG (SALAIRE) ASC LIMIT 1
 EXERCICE 2
▪ soient les 2 tables suivantes:

▪ Table- Employes Table - Departement

ID Nom Age Salaire Profession Dep

ID_dep Nom_dep
1 Ismail 25 6000.00 Assistant 2
1 Informatique
2 Mohamed 30 8000.00 Directeur 1
2 Ressources Humaine
3 Sarrah 29 6000.00 Directeur 3
3 Vente
4 Dounia 30 7000.00 Assistant 4
4 Strategies
5 Omar 30 9000.00 Ingenieur 1
6 Amine 29 7500.00 Ingénieur NULL
▪ Exprimez en SQL les requêtes suivantes:
 EXERCICE 2
▪ Donner les groupes d’âge des employés.
SELECT Age FROM Employes GROUP BY Age;
▪ Compter le nombre d'employés de chaque groupe d’âge.
SELECT Age, count(*)
FROM Employes
GROUP BY Age;
▪ Compter le salaire moyen pour chaque groupe d’âge.
SELECT Age, AVG(Salaire)
FROM Employes
GROUP BY Age;
▪ Récupérer l'identifiant du département et le nombre d'employés dans chaque département.
SELECT Dep, COUNT(*)
FROM Employes
GROUP BY Dep;
 EXERCICE 2
▪ Récupérer les noms de département et le salaire moyen de chaque département
SELECT Departement.Nom_dep, AVG(Employes.Salaire)
FROM Employes, Departement
where Employes.Dep=Departement.Id_dep
GROUP BY Employes.dep;
▪ Lister les départements dont le salaire moyen est supérieur à 6000 ?
SELECT Departement.Nom_dep, AVG(Employes.Salaire)
FROM Employes, Departement
Where Employes.Dep=Departement.Id_dep
GROUP BY Employes.dep
HAVING AVG(Employes.Salaire) > 6000;