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?
  
  1. r union t
  2. s intersect t
  3. t - w
  4. r join s
  5. r x t
  6. r join t with B=D
  7. r join T with B < D
  8. r union w
  9. r intersect w
  10. r - w
  11. r union r
  12. r - r
  13. s intersect 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

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 algèbre relationnelle et 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 ?

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.
• Pour chaque matière (le n° de matière suffit), nombre d'élèves qui la suivent.