Comment se comporte l’opérateur « Difference » de l’algèbre relationnel

Hervé Chastel

Description

Examen de Base de Données A4–2594422 février 2003Le polycopié et les notes de cours sont autorisés.Le corrigé.1 (1 pt)Quelle est la différence entre le produit cartésien et la jointure entre deux tables.Solution : La jointure est un produit cartésien qui s’effectue sur deux tables qui ontau moins un attribut commun, on dit que la jointure se fait sur ces attributs. Lestuples du résultat sont ceux pour lesquels les attributs de jointure sont égaux.2 (6 pts)Relation des Employés (EMP)EMP(N°E, ENOM, PROF, DATEEMB, SAL, COMM, N°D)N°E :numéro d'employé, clé ENOM :nom de l'employé PROF :profession (directeur n'est pas une profession) DATEEMB :date d'embauche SAL :salaire COMM :commission (un employé peut ne pas avoir de commission) N°D :numéro de département auquel appartient l'employé Relation des Départements (DEPT)DEPT(N°D, DNOM, DIR, VILLE)N°D :numéro de département, clé DNOM :nom du département DIR :directeur du département VILLE :lieu du département (ville) 1Opérations AlgébriquesSoit l'exemple suivant :EMPN°E ENOM PROF DATEEMB SAL COMM N°D10 Joe Ingénieur 1.10.93 4000 3000 320 Jack Technicien 1.5.88 3000 2000 230 Jim Vendeur 1.3.80 5000 5000 140 Lucy Ingénieur 5000 5000 3DEPTN°D DNOM DIR VILLE1 Commercial 30 New York2 Production 20 Houston3 Développement 40 BostonExercice 2-A : (1,5 pts)Calculer, σ (EMP) :(donnez la table résultat)SAL<5000 Solution:N°E ENOM PROF DATEEMB SAL COMM N°D10 Joe ...

Informations

• Publié par: Cipot
• Nombre de lectures: 84
• Langue: Français

Extrait

Examen de Base de Données A4–25944 22 février 2003 Le polycopié et les notes de cours sont autorisés.

Le corrigé. 1 (1 pt) Quelle est la différence entre le produit cartésien et la jointure entre deux tables. Solution : La jointure est un produit cartésien qui s’effectue sur deux tables qui ont au moins un attribut commun, on dit que la jointure se fait sur ces attributs. Les tuples du résultat sont ceux pour lesquels les attributs de jointure sont égaux.

2 (6 pts) Relation des Employés (EMP)

EMP(N°E, ENOM, PROF, DATEEMB, S

AL, COMM, N°D)

N°E :numéro d'employé, clé ENOM :nom de l'employé PROF :profession (directeur n'est pas une profession) DATEEMB :date d'embauche SAL :salaire COMM :commission (un employé peut ne pas avoir de commission) N°D :numérode département auquel appartient l'employé

Relation des Départements (DEPT)

DEPT(N°D, DNOM, DIR, VILLE)

N°D :numéro de département, clé DNOM :nom du département DIR :directeur du département VILLE :lieu du département (ville)

1

Opérations Algébriques Soit l'exemple suivant :

EMP N°E 10 20 30 40

ENOM Joe Jack Jim Lucy

PROF DATEEMB Ingénieur 1.10.93 Technicien 1.5.88 Vendeur 1.3.80 Ingénieur 1.3.80

DEPT N°D DNOM 1 Commercial30 2 Production20 3 Développement

SAL 4000 3000 5000 5000

DIR VILLE New York Houston 40 Boston

COMM N°D 3000 3 2000 2 5000 1 5000 3

Exercice 2-A :(1,5 pts) σ SAL<5000 (EMP) Calculer, :(donnezla table résultat) Solution: N°E ENOMPROF DATEEMBSAL COMMN°D 10 JoeIngénieur 1.10.934000 3000 3 20 JackTechnicien 1.5.883000 2000 2

Exercice 2-B :(1,5 pts) Calculer, EMPbis = ρ N°E→N°E1 (π N°E ,COMM (EMP)) Solution: EMPbis N°E1 COMM 10 3000 20 2000 30 5000 405000

Exercice 2-C:(le signe ∞ est celui de la jointure) (2 pts) Calculer, π N°E,SAL ( EMP )∞ EMPbis  SAL=COMM

Solution: N°E SAL 20 3000 30 5000 40 5000 30 5000 40 5000

N°E 1 10 30 40 40 30

COMM 3000 5000 5000 5000 5000

2

Exercice 2-D :(1, pt)

Quelle est l'expression de l'algèbre relationnelle qui permettrait d'obtenir le nom des employés qui travaillent à New York. Solution: π ( EMP ∞  ( σ VILLE='New York' )  (DEPT)) ENOM EMP.N°D=DEPT.N°D

3 (6 pts) Dans la ville de Taloche pour rendre la circulation fluide et libérer les trottoirs des piquets, aucune voiture ne peut stationner sur les axes de circulations automobiles ou piétonnes. Des places de parking existent en sous-sol ou à l’air libre dans des zones réservées, elles sont la propriété de certains habitants. Les propriétaires de véhicules louent une place de parking pour un certain nombre de mois, ils y conservent leurs véhicules. Lors des déplacements en ville, les habitants peuvent stationner sur des emplacements ad hoc (toujours propriétés d’habitants) pour un certain tarif horaire. L’association des propriétaires de parkings de la ville de Taloche dispose d’une base de données relationnelle pour la gestion des revenus de ces parkings, les relations suivantes sont établies.

Habitant ( idHab, nom, adr, dateArrivée )

Véhicule ( N°v, idHab, dateAchat, prix, km )

Parking ( idPark, localisation, n°, surface )

Propriétaire (idPark, idHab, dateAchat, prix )

Louer ( N°v, idHab, idPark, nbmois )

Stationner (N°v, idHab, idPark, nbheures)

Les clés sont soulignées, les clés étrangères ne sont pas précisées.

3

Question 3-ADonnez les requêtes CREATE des relations : Habitant (1 pt), Véhicule (1 pt), Louer (2 pts). Sans oublier de préciser les contraintes. Ni les clauses ON DELETE et ON UPDATE .

Solution:

CREATE TABLE Habitant ( idHab INTEGER(6), nom VARCHAR(25) NOT NULL , adr VARCHAR(35) NOT NULL , dateArrivée INTEGER(4) NOT NULL , PRIMARY KEY idHab)

CREATE TABLE Véhicule (N°v VARCHAR(9), idHab INTEGER(6), dateAchat INTEGER(4) NOT NULL, prix INTEGER(5), km INTEGER(6), PRIMARY KEY( N°v, idHab) , FOREIGN KEY (idHab) REFERENCES Habitant ON DELETE CASCADE)

CREATE TABLE Louer (N°v VARCHAR(9), idHab INTEGER(6), idPark INTEGER(4), nbmois INTEGER(5) NOT NULL, PRIMARY KEY( N°v, idHab, idPark), FOREIGN KEY (N°v, idHab) REFERENCES Véhicule, FOREIGN KEY (idPark) REFERENCES Véhicule ON UPDATE CASCADE)

4

(2 pts) 1,N Véhicule N°v dateAchat prix km

1,1

0,N

0,N

Posséde

Habitant idHab nom adr dateArrivée

4 (7 pts) Requête SQL :

0,N

Louer nbmois

Stationner datedebut

0,N

0,N

Propriétaire

DateAchat prix

Parking idPark, localisation n° surface

0,N

4-1(0,5 pt)Donnez le nom et l’adresse de tous les habitants de Taloche arrivés aprés 1985.

SELECT nom, adr FROM Habitant WHERE dateArrivée > 1985;

5

4-2(0,5 pt) Donnez le nom et l’adresse de tous les habitants de Taloche classés par ordre alphabétique.

SELECT nom, adr FROM Habitant ORDER BY nom;

4-3(0,5 pt) Combien de véhicules existent dans la ville de TALOCHE ;

SELECT COUNT(*) FROM Véhicule;

4-4(1 pt) Donner l’année et le nombre de véhicule par année d’achat.

SELECT dateAchat, COUNT(*) FROM Véhicule GROUP BY dateAchat ;

4-5(1 pt) Quel est le nom, la date d’achat, la localisation et le n° de parking des habitants qui en ont achetés après 1998.

SELECT nom, dateAchat, localisation, n° FROM Habitant H, Propriétaire PR, Parking P WHERE H.idHab=PR.idHab AND PR.idPark=P.idpark AND dateAchat>1998;

4-6(2 pts) Donner le nom, l’adresse des habitants qui disposent d’un véhicule mais qui ne sont pas propriétaire de parking.

SELECT nom, adr FROM Habitant H, véhicule V WHERE H.idHab=V.idHab And H.IdHab NOT IN (SELECT idHab FROM Propriètaire) ;

4-7(1,5 pt) Quels habitants ont stationné.

SELECT DISTINCT(idHab ), nom FROM Habitant H, Véhicule V, Stationner S WHERE H.idHab=V.idHab AND V.N°v=S.N°v;

6