Langages relationnels

Etant données les relations suivantes (exprimées en extension) r, s, t et w :

r
A	B	C
1	2	3
4	5	6
7	0	5

s
C	D
6	1
1	0
3	9
6	5

t
D	E
2	4
6	8

w
A	B	C
7	8	9
1	2	3
7	8	1

• Indiquez le schéma de chaque relation (exprimé en intension).
• Parmi les opérations suivantes, lesquelles sont licites ? Quelles sont les relations résultats?
  
  • r union t
  • s intersect t
  • t - w
  • r join s
  • r x t
  • r join t with B=D
  • r join T with B < D
  • r union w
  • r intersect w
  • r - w
  • r union r
  • r - r
  • s intersect s

Un centre de distribution gère, à l'aide d'un SGBDR, les informations concernant ses clients, leurs commandes, les produits, les fournisseurs, les usines où les produits sont fabriqués. Chaque commande client peut concerner plusieurs produits, chacun pour une certaine quantité. Plusieurs fournisseurs approvisionnent le centre, mais un produit est fourni par un seul fournisseur. Un produit peut être fabriqué dans plusieurs usines, et une usine peut fabriquer plusieurs produits. Le modèle relationnel mis en place utilise les relations suivantes :

• client (cl, nomc, villec)
• commande (cde, date, cl)
• produit (p, libellé, prix)
• lignecde (cde, p, qté)
• fournisseur (f, nomf, villef)
• fournir (p, f)
• fabriquer (u, p)

Avec :

• cl, nomc, villec : numéro, nom et ville du client
• cde, cl, date : numéro et date de la commande, numéro du client ayant passé la commande
• p, libellé, prix : numéro, libellé et prix de vente d'un produit
• cde, p, qté : éléments d'une ligne de commande, indiquant que la commande numéro cde fait référence au produit numéro p, pour la quantité qté
• f, nomf, villef : numéro, nom et ville d'un fournisseur
• u : numéro d'usine

• Grâce aux informations fournies sur ce contexte, indiquez les identifiants de ces relations
• Formulez les requêtes suivantes en algèbre relationnelle :
  • Quel est le numéro du client qui a effectué la commande numéro 34 ?
  • Donnez le(s) numéro(s) de commande(s) effectuée(s) par le client numéro 1023 le 01/03/93 ou le 02/03/93.
  • Quel est le numéro du fournisseur et le prix du camembert ?
  • Quels sont les noms et les villes des clients ayant commandé du camembert ?
  • Quelles sont les usines qui fabriquent tous les produits ?
  • Quel est le prix à payer pour la commande numéro 35 ?

Etant données deux relations R et S, avec :

R
n_emp	n_dep
1	2
3	4
5	6

S
n_emp	salaire
5	1000
3	1200
1	1600

Écrivez en langage algébrique et en SQL les requêtes suivantes :

• Dans quel département travaille l'employé n° 1 ?
• Pour les employés qui gagnent plus de 1200 €, indiquer le n° d'employé et le salaire.
• Quelles sont les valeurs possibles de n° département ?
• Quels sont les salaires des employés dont le n° est supérieur à 4 ?
• Que représente le produit cartésien R x S ? Donnez tous les tuples de ce produit cartésien.
• Y a-t-il des tuples dans R et S ayant la même valeur de n_emp ?

Déterminez, à l'aide des deux langages, les tuples de composantes n_emp, n_dep, salaire avec (n_emp, n_dep) dans R et (n_emp, salaire) dans S, et avec la composante n_emp dans R égale à la composante n_emp dans S.

Les relations :

• véhicule (nv, m, coul)
• réparation (nr, lib)
• véh-rév (nv, nr, d)

représentent respectivement :

• des véhicules, de numéro d'immatriculation nv, de marque m et de couleur coul.
• des réparations, de numéro nr et de libellé lib.
• des véhicules, de numéro nv, qui ont eu la réparation nr, à la date d.

Exprimez en langage algébrique et en SQL les requêtes suivantes :

• la marque et la couleur de tous les véhicules.
• N° d'immatriculation des voitures de couleur 'rouge'.
• Marques des voitures qui ont eu une réparation.
• Marques des voitures qui ont eu une réparation avant le 1er janvier 1994.
• Type de réparation fait à la voiture n° 102.
• Types de réparations faits aux voitures de marque Citroën.

Un exemple de tables relatives à ces relations est le suivant :

véhicule
nv	m	coul
100	Renault	rouge
101	Peugeot	bleu
102	Citroën	rouge

réparation
nr	lib
1	freins
2	carburateur

véh-rép
nv	nr	d
100	2	03-JAN-94
102	1	30-DEC-93

Ce schéma relationnel représente des vendeurs, des clients et l'attribution des clients aux vendeurs :

• vendeur (n°v, nomv, prénomv, agence)
• client (n°c, nomc, prénomc, villec)
• vendeur-client (n°v, n°c)

Un exemple de ces relations est le suivant :

vendeur
n°v	nomv	prénomv	agence
10	Durand	Jacques	Paris
20	Dupond	Pierre	Toulouse
30	Martin	Jean	Bordeaux

client
n°c	nomc	prénomc	villec
1	Alpha	Jacques	Paris
2	Beta	Jean	Marseille
3	Gamma	Alain	Paris
4	Delta	Yves	Bordeaux
5	Epsilon	Marc	Toulouse

vendeur-client
n°v	n°c
10	1
10	3
20	2
20	5
30	4

Écrivez en SQL les requêtes :

• Nom, prénom et ville du (des) client(s) du vendeur de nom Dupond.
• A quelle agence est rattaché le client Gamma ?
• Noms des clients rattachés à l'agence de Toulouse.

Le schéma relationnel suivant représente le fait que des rois ont résidé dans des châteaux :

• ROIS (n°r, nom_roi, date_naissance)
• CHATEAUX (n°c, nom_c, siècle)
• RESIDER (n°r, n°c, année_début, année_fin)

Écrivez en SQL les requêtes suivantes :

• Quelle est la date de naissance du roi Louis XVI ?
• A quel siècle le château de Chambord a-t-il été construit ?
• Quels sont les n° de rois qui ont résidé dans le château n° 5 ?
• Quels sont les n° de rois qui ont résidé dans le château de Chambord ?
• Quels sont les noms des rois qui ont résidé dans le château de Chambord ?
• Liste des rois (n°, nom et date de naissance) classés par date de naissance croissante.
• Pour chacun des rois (n° du roi), nombre de châteaux dans lesquels il a résidé.
• Pour chacun des rois (nom du roi), nombre de châteaux dans lesquels il a résidé.
• Quels sont les rois qui n'ont jamais habité au château de Blois ?

Le schéma relationnel suivant :

• employés (e-num, e-nom, e-ville)
• projets (p-num, p-titre)
• être-assigné (e-num, p-num, heures)

représentent respectivement :

• des employés, avec le numéro d'employé, nom et ville
• des projets, avec numéro et titre
• l'assignation des employés aux projets, pour un quota d'heures.

Ecrivez en SQL les requêtes suivantes :

• Noms des employés qui habitent à Lyon.
• Pour chaque employé affecté au projet n° 5, quel est son nom et le nombre d'heures passées sur ce projet.
• Combien y a-t-il de projets enregistrés ?
• A combien de projets l'employé n° 3 a-t-il participé ?
• Combien d'heures l'employé n° 3 a-t-il passé sur l'ensemble de ses projets ?
• Combien d'heures chaque employé a-t-il passé sur l'ensemble de ses projets ?

Les deux schémas relationnels

• société (n°s, nom, activité, nb_employés)
• contrat (n°contrat, montant, n°s)

représentent respectivement :

• des sociétés, avec le numéro, le nom, le domaine d'activité, le nombre d'employés
• et des contrats d'assurance, avec le numéro, le montant et le n° de la société l'ayant souscrit.

Écrivez en algèbre relationnelle et en SQL les requêtes suivantes :

• Noms des sociétés du domaine du bâtiment ou de la distribution, de plus de 1000 employés.
• Quels sont les montants des contrats souscrits par la société "O.K." ?

Ce schéma relationnel représente des personnes appartenant à certains organismes et participant à des congrès. On connaît le n° de congrès, la date de début et la date de fin, le titre et la ville où il s'est tenu :

• personne (n°p, nomp, prénomp, nom_organisme)
• participer (n°p, n°c)
• congrès (n°c, date_d, date_f, titrec, ville)

Écrivez en algèbre relationnelle et en SQL les requêtes suivantes :

• Donnez le nom et le prénom des personnes de l'Université de Marne la Vallée, ayant participé au congrès "Les bases de données avancées".
• Donnez le titre des congrès qui se sont tenus à Paris et qui se sont finis avant le 1er février 1995 ou après le 30 avril 1995.

Ces trois relations d'une base de données touristique, représentent des gares, situées dans des villes, elles-mêmes situées dans des pays :

• Gares (nom_gare, n°ville)
• Villes (n°ville, nom_ville, nb_habitants, n°pays)
• Pays (n°pays, nom_pays)

Ecrivez en algèbre relationnelle et en SQL les requêtes suivantes :

• Pour les villes avec plus de 1.000.000 d'habitants, indiquer les noms des gares.
• Donnez le nom du pays et de la ville où se trouve la "Stazione Termini".

Les trois relations représentent des circuits touristiques se déroulant dans des localités.

• Circuits (n°c, libellé, durée, prix)
• Localités (n°l, nom, nb_habitants)
• Comprend (n°c, n°l)

Ecrivez en SQL la requête suivante :

• Libellé des circuits de moins de 4 jours, incluant les localités de Cannes ou Chamonix
• Combien de circuits comprennent la visite de Versailles ?

Les schémas relationnels suivants représentent respectivement des élèves, des matières et le fait que des élèves suivent des matières :

• élève (n°el, nom_el, prénom_el)
• matière (n°mat, nom_mat)
• suivre (n°el, n°mat)

Ecrivez en algèbre relationnelle et en SQL les requêtes suivantes :

• N° de la matière suivie par un étudiant qui s'appelle Jean Dupont.
• Nom et prénom des élèves qui suivent le cours de Bases de Données.

Ecrivez en SQL la requête suivante :

• Pour chaque matière (le n° de matière suffit), nombre d'élèves qui la suivent.

Dans un grand magasin, les rayons sont colorés pour repérer facilement les différents types de produits. L'emplacement des produits dans les rayons satisfait le modèle conceptuel suivant :

On utilise le schéma relationnel suivant :

• Produit (n°p, prix)
• Rayon (n°rayon, couleur)
• Employé (n°emp, nom-emp)
• Est_dans (n°p, n°rayon)
• Responsable (n°rayon, n°emp)

Répondre aux questions suivantes :

• Trouvez les clés des relations Est_dans et Responsable.
• Donnez les schémas et les clés des relations :
  • Produit join Est_dans join Rayon
  • Produit join Est_dans join Rayon join Responsable join Employé
• Ecrivez en SQL la requête qui donne les numéros des rayons, de couleur vert ou jaune, où l'on peut trouver des produits à plus de 5 €.
• Est-il possible de supprimer, dans le schéma, les relations Est_dans et/ou Responsable ? Si oui, quel est le nouveau schéma ?