Fichier de rejeu Close

Indication Close

A propos de... Close

Commentaire Close

Systèmes d'Information

Présentation

Films

Repas

Repas

Index

Téléchargements

Crédits

Aide

Menu	Contenu
	Sommaire, Téléchargements
	Aide sur les outils

Chevron	Action
	Aller en haut de la page courante
	Aller en bas de la page courante
	Passer à la page précédente
	Passer à la page suivante

Operations ensemblistes

L’objectif de ces exercices est de savoir représenter sous forme d’arbre de requêtes des opérations ensemblistes (\(\cup,\cap,\setminus\)) « Aux pays des bières ».

3/3 Union, Intersection, Différence

Union

Construire un arbre de requêtes répondant à la question :

\(Q_1\) : « récupérer les noms des pays ou se trouvent des bars et ceux des origines de fabrication des bières ».

Votre réponse :

Arbre de requêtes : graph180.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY, bar VARCHAR(20), pays VARCHAR(20) ); CREATE TABLE bieres ( id_biere INTEGER NOT NULL PRIMARY KEY, biere VARCHAR(20), couleur VARCHAR(10), origine VARCHAR(20) ); CREATE TABLE services ( id_bar INTEGER NOT NULL, id_biere INTEGER NOT NULL, stock SMALLINT, PRIMARY KEY (id_bar, id_biere), FOREIGN KEY (id_bar) REFERENCES bars, FOREIGN KEY (id_biere) REFERENCES bieres); INSERT INTO bars VALUES (1, 'Bar du Coin', 'France'); INSERT INTO bars VALUES (2, 'Corners Pub', 'USA'); INSERT INTO bars VALUES (3, 'Cafe der Ecke','Allemagne'); INSERT INTO bars VALUES (4, 'Cafe des Amis', 'France'); INSERT INTO bieres VALUES (1, 'Kronenbourg', 'Blonde', 'France'); INSERT INTO bieres VALUES (2, 'Guinness', 'Brune', 'Irlande'); INSERT INTO bieres VALUES (3, 'Heineken', 'Blonde', 'Hollande'); INSERT INTO bieres VALUES (4, 'Newcastle', 'Rousse', 'UK'); INSERT INTO bieres VALUES (5, 'Spaten', 'Blonde', 'Allemagne'); INSERT INTO bieres VALUES (6, 'Bush', 'Blonde', 'USA'); INSERT INTO bieres VALUES (7, 'Kanterbrau', 'Blonde', 'France'); INSERT INTO bieres VALUES (8, 'Kronenbourg', 'Brune', 'France'); INSERT INTO services VALUES (1,1,1000); INSERT INTO services VALUES (1,2, 250); INSERT INTO services VALUES (1,3, 50); INSERT INTO services VALUES (1,4, 10); INSERT INTO services VALUES (1,5, 10); INSERT INTO services VALUES (1,6, 10); INSERT INTO services VALUES (1,7, 20); INSERT INTO services VALUES (1,8, 20); INSERT INTO services VALUES (2,1, 100); INSERT INTO services VALUES (2,6, 1500); INSERT INTO services VALUES (3,5, 5000);

Vue d'ensemble

CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY,
                    bar    VARCHAR(20),
                    pays   VARCHAR(20) );

CREATE TABLE bieres ( id_biere  INTEGER NOT NULL PRIMARY KEY,
                      biere     VARCHAR(20),
                      couleur   VARCHAR(10),
                      origine   VARCHAR(20) );

CREATE TABLE services  ( id_bar   INTEGER NOT NULL,
                         id_biere INTEGER NOT NULL,
                         stock    SMALLINT,
                         PRIMARY KEY (id_bar, id_biere),
                         FOREIGN KEY (id_bar)   REFERENCES bars,
                         FOREIGN KEY (id_biere) REFERENCES bieres);

INSERT INTO bars VALUES (1, 'Bar du Coin', 'France');
INSERT INTO bars VALUES (2, 'Corners Pub',  'USA');
INSERT INTO bars VALUES (3, 'Cafe der Ecke','Allemagne');  
INSERT INTO bars VALUES (4, 'Cafe des Amis', 'France');

INSERT INTO bieres  VALUES (1, 'Kronenbourg', 'Blonde', 'France');  
INSERT INTO bieres  VALUES (2, 'Guinness', 'Brune', 'Irlande');  
INSERT INTO bieres  VALUES (3, 'Heineken', 'Blonde', 'Hollande');  
INSERT INTO bieres  VALUES (4, 'Newcastle', 'Rousse', 'UK');  
INSERT INTO bieres  VALUES (5, 'Spaten', 'Blonde', 'Allemagne');  
INSERT INTO bieres  VALUES (6, 'Bush', 'Blonde', 'USA');  
INSERT INTO bieres  VALUES (7, 'Kanterbrau', 'Blonde', 'France');  
INSERT INTO bieres  VALUES (8, 'Kronenbourg', 'Brune', 'France');

INSERT INTO services VALUES (1,1,1000);
INSERT INTO services VALUES (1,2, 250);
INSERT INTO services VALUES (1,3, 50);
INSERT INTO services VALUES (1,4, 10);
INSERT INTO services VALUES (1,5, 10);
INSERT INTO services VALUES (1,6, 10);
INSERT INTO services VALUES (1,7, 20);
INSERT INTO services VALUES (1,8, 20);
INSERT INTO services VALUES (2,1, 100);
INSERT INTO services VALUES (2,6, 1500);
INSERT INTO services VALUES (3,5, 5000);

pays
Allemagne
France
Hollande
Irlande
UK
USA

Une solution possible :

On applique d’abord une projection (\(\Pi\)) sur les colonnes pays de table bars et origine de la table bieres pour pouvoir appliquer l’opération ensembliste d’union (\(\cup\)).

Les opérations ensemblistes sont des opérateurs binaires qui doivent avoir pour chaque entrée (opérande) le même nombre d’attributs, chacun étant défini sur le même domaine.

Ecriture en algèbre relationnelle :

\(Q_1 = \cup (\Pi_{(pays)}(bars), \Pi_{(origine)}(bieres))\).

Arbre de requêtes : bars-8.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	{ "class": "go.GraphLinksModel", "copiesArrays": true, "copiesArrayObjects": true, "linkFromPortIdProperty": "fromPort", "linkToPortIdProperty": "toPort", "nodeDataArray": [ {"category":"table", "name":"bars", "info":"TABLE", "key":-1, "loc":"25 325"}, {"category":"table", "name":"bieres", "info":"TABLE", "key":-2, "loc":"225 325"}, {"category":"project", "name":"P_1", "info":"PROJECTION", "key":-3, "loc":"25 200", "attributes":"(pays)"}, {"category":"project", "name":"P_2", "info":"PROJECTION", "key":-4, "loc":"225 200", "attributes":"(origine)"}, {"category":"union", "name":"U_1", "info":"UNION", "key":-5, "loc":"125 125"}, {"category":"query", "name":"Q_1", "info":"QUERY", "key":-6, "loc":"125 25"} ], "linkDataArray": [ {"from":-1, "to":-3, "fromPort":"", "toPort":"B", "points":[25,308.65,25,308.65,25,285,25,285,25,261.35,25,246.35000000000002]}, {"from":-2, "to":-4, "fromPort":"", "toPort":"B", "points":[225,308.65,225,308.65,225,285,225,285,225,261.35,225,246.35000000000002]}, {"from":-3, "to":-5, "fromPort":"T", "toPort":"L", "points":[25,195.35000000000002,25,195.35000000000002,25,188,25,188,25,104.15,84,104.15,99,104.15]}, {"from":-4, "to":-5, "fromPort":"T", "toPort":"R", "points":[225,195.35000000000002,225,195.35000000000002,225,188,225,188,225,104.15,166,104.15,151,104.15]}, {"from":-5, "to":-6, "fromPort":"T", "toPort":"R", "points":[125,78.15,125,78.15,125,67.25,125,67.25,125,56.35,125,41.35]} ]} CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY, bar VARCHAR(20), pays VARCHAR(20) ); CREATE TABLE bieres ( id_biere INTEGER NOT NULL PRIMARY KEY, biere VARCHAR(20), couleur VARCHAR(10), origine VARCHAR(20) ); CREATE TABLE services ( id_bar INTEGER NOT NULL, id_biere INTEGER NOT NULL, stock SMALLINT, PRIMARY KEY (id_bar, id_biere), FOREIGN KEY (id_bar) REFERENCES bars, FOREIGN KEY (id_biere) REFERENCES bieres); INSERT INTO bars VALUES (1, 'Bar du Coin', 'France'); INSERT INTO bars VALUES (2, 'Corners Pub', 'USA'); INSERT INTO bars VALUES (3, 'Cafe der Ecke','Allemagne'); INSERT INTO bars VALUES (4, 'Cafe des Amis', 'France'); INSERT INTO bieres VALUES (1, 'Kronenbourg', 'Blonde', 'France'); INSERT INTO bieres VALUES (2, 'Guinness', 'Brune', 'Irlande'); INSERT INTO bieres VALUES (3, 'Heineken', 'Blonde', 'Hollande'); INSERT INTO bieres VALUES (4, 'Newcastle', 'Rousse', 'UK'); INSERT INTO bieres VALUES (5, 'Spaten', 'Blonde', 'Allemagne'); INSERT INTO bieres VALUES (6, 'Bush', 'Blonde', 'USA'); INSERT INTO bieres VALUES (7, 'Kanterbrau', 'Blonde', 'France'); INSERT INTO bieres VALUES (8, 'Kronenbourg', 'Brune', 'France'); INSERT INTO services VALUES (1,1,1000); INSERT INTO services VALUES (1,2, 250); INSERT INTO services VALUES (1,3, 50); INSERT INTO services VALUES (1,4, 10); INSERT INTO services VALUES (1,5, 10); INSERT INTO services VALUES (1,6, 10); INSERT INTO services VALUES (1,7, 20); INSERT INTO services VALUES (1,8, 20); INSERT INTO services VALUES (2,1, 100); INSERT INTO services VALUES (2,6, 1500); INSERT INTO services VALUES (3,5, 5000);

Vue d'ensemble

{ "class": "go.GraphLinksModel",
  "copiesArrays": true,
  "copiesArrayObjects": true,
  "linkFromPortIdProperty": "fromPort",
  "linkToPortIdProperty": "toPort",
  "nodeDataArray": [ 
{"category":"table", "name":"bars", "info":"TABLE", "key":-1, "loc":"25 325"},
{"category":"table", "name":"bieres", "info":"TABLE", "key":-2, "loc":"225 325"},
{"category":"project", "name":"P_1", "info":"PROJECTION", "key":-3, "loc":"25 200", "attributes":"(pays)"},
{"category":"project", "name":"P_2", "info":"PROJECTION", "key":-4, "loc":"225 200", "attributes":"(origine)"},
{"category":"union", "name":"U_1", "info":"UNION", "key":-5, "loc":"125 125"},
{"category":"query", "name":"Q_1", "info":"QUERY", "key":-6, "loc":"125 25"}
 ],
  "linkDataArray": [ 
{"from":-1, "to":-3, "fromPort":"", "toPort":"B", "points":[25,308.65,25,308.65,25,285,25,285,25,261.35,25,246.35000000000002]},
{"from":-2, "to":-4, "fromPort":"", "toPort":"B", "points":[225,308.65,225,308.65,225,285,225,285,225,261.35,225,246.35000000000002]},
{"from":-3, "to":-5, "fromPort":"T", "toPort":"L", "points":[25,195.35000000000002,25,195.35000000000002,25,188,25,188,25,104.15,84,104.15,99,104.15]},
{"from":-4, "to":-5, "fromPort":"T", "toPort":"R", "points":[225,195.35000000000002,225,195.35000000000002,225,188,225,188,225,104.15,166,104.15,151,104.15]},
{"from":-5, "to":-6, "fromPort":"T", "toPort":"R", "points":[125,78.15,125,78.15,125,67.25,125,67.25,125,56.35,125,41.35]}
 ]}

CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY,
                    bar    VARCHAR(20),
                    pays   VARCHAR(20) );

CREATE TABLE bieres ( id_biere  INTEGER NOT NULL PRIMARY KEY,
                      biere     VARCHAR(20),
                      couleur   VARCHAR(10),
                      origine   VARCHAR(20) );

pays
Allemagne
France
Hollande
Irlande
UK
USA

Intersection

Construire un arbre de requêtes répondant à la question :

\(Q_1\) : « récupérer les noms des pays ayant des bars et fabriquant de la bière ».

Votre réponse :

Arbre de requêtes : graph187.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY, bar VARCHAR(20), pays VARCHAR(20) ); CREATE TABLE bieres ( id_biere INTEGER NOT NULL PRIMARY KEY, biere VARCHAR(20), couleur VARCHAR(10), origine VARCHAR(20) ); CREATE TABLE services ( id_bar INTEGER NOT NULL, id_biere INTEGER NOT NULL, stock SMALLINT, PRIMARY KEY (id_bar, id_biere), FOREIGN KEY (id_bar) REFERENCES bars, FOREIGN KEY (id_biere) REFERENCES bieres); INSERT INTO bars VALUES (1, 'Bar du Coin', 'France'); INSERT INTO bars VALUES (2, 'Corners Pub', 'USA'); INSERT INTO bars VALUES (3, 'Cafe der Ecke','Allemagne'); INSERT INTO bars VALUES (4, 'Cafe des Amis', 'France'); INSERT INTO bieres VALUES (1, 'Kronenbourg', 'Blonde', 'France'); INSERT INTO bieres VALUES (2, 'Guinness', 'Brune', 'Irlande'); INSERT INTO bieres VALUES (3, 'Heineken', 'Blonde', 'Hollande'); INSERT INTO bieres VALUES (4, 'Newcastle', 'Rousse', 'UK'); INSERT INTO bieres VALUES (5, 'Spaten', 'Blonde', 'Allemagne'); INSERT INTO bieres VALUES (6, 'Bush', 'Blonde', 'USA'); INSERT INTO bieres VALUES (7, 'Kanterbrau', 'Blonde', 'France'); INSERT INTO bieres VALUES (8, 'Kronenbourg', 'Brune', 'France'); INSERT INTO services VALUES (1,1,1000); INSERT INTO services VALUES (1,2, 250); INSERT INTO services VALUES (1,3, 50); INSERT INTO services VALUES (1,4, 10); INSERT INTO services VALUES (1,5, 10); INSERT INTO services VALUES (1,6, 10); INSERT INTO services VALUES (1,7, 20); INSERT INTO services VALUES (1,8, 20); INSERT INTO services VALUES (2,1, 100); INSERT INTO services VALUES (2,6, 1500); INSERT INTO services VALUES (3,5, 5000);

Vue d'ensemble

CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY,
                    bar    VARCHAR(20),
                    pays   VARCHAR(20) );

CREATE TABLE bieres ( id_biere  INTEGER NOT NULL PRIMARY KEY,
                      biere     VARCHAR(20),
                      couleur   VARCHAR(10),
                      origine   VARCHAR(20) );

pays
Allemagne
France
USA

Une solution possible :

On applique d’abord une projection (\(\Pi\)) sur les colonnes pays de table bars et origine de la table bieres pour pouvoir appliquer l’opération ensembliste d’intersection (\(\cap\)).

Les opérations ensemblistes sont des opérateurs binaires qui doivent avoir pour chaque entrée (opérande) le même nombre d’attributs, chacun étant défini sur le même domaine.

Ecriture en algèbre relationnelle :

\(Q_1 = \cap (\Pi_{(pays)}(bars), \Pi_{(origine)}(bieres))\).

Arbre de requêtes : bars-9.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	{ "class": "go.GraphLinksModel", "copiesArrays": true, "copiesArrayObjects": true, "linkFromPortIdProperty": "fromPort", "linkToPortIdProperty": "toPort", "nodeDataArray": [ {"category":"table", "name":"bars", "info":"TABLE", "key":-1, "loc":"25 325"}, {"category":"table", "name":"bieres", "info":"TABLE", "key":-2, "loc":"225 325"}, {"category":"project", "name":"P_1", "info":"PROJECTION", "key":-3, "loc":"25 200", "attributes":"(pays)"}, {"category":"project", "name":"P_2", "info":"PROJECTION", "key":-4, "loc":"225 200", "attributes":"(origine)"}, {"category":"query", "name":"Q_1", "info":"QUERY", "key":-6, "loc":"125 25"}, {"category":"inter", "name":"I_1", "info":"INTERSECTION", "key":-13, "loc":"125 125"} ], "linkDataArray": [ {"from":-1, "to":-3, "fromPort":"", "toPort":"B", "points":[25,308.65,25,308.65,25,285,25,285,25,261.35,25,246.35000000000002]}, {"from":-2, "to":-4, "fromPort":"", "toPort":"B", "points":[225,308.65,225,308.65,225,285,225,285,225,261.35,225,246.35000000000002]}, {"from":-3, "to":-13, "fromPort":"T", "toPort":"L", "points":[25,195.35000000000002,25,195.35000000000002,25,104.15,54.5,104.15,84,104.15,99,104.15]}, {"from":-4, "to":-13, "fromPort":"T", "toPort":"R", "points":[225,195.35000000000002,225,195.35000000000002,225,104.15,195.5,104.15,166,104.15,151,104.15]}, {"from":-13, "to":-6, "fromPort":"T", "toPort":"R", "points":[125,78.15,125,78.15,125,67.25,125,67.25,125,56.35,125,41.35]} ]} CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY, bar VARCHAR(20), pays VARCHAR(20) ); CREATE TABLE bieres ( id_biere INTEGER NOT NULL PRIMARY KEY, biere VARCHAR(20), couleur VARCHAR(10), origine VARCHAR(20) ); CREATE TABLE services ( id_bar INTEGER NOT NULL, id_biere INTEGER NOT NULL, stock SMALLINT, PRIMARY KEY (id_bar, id_biere), FOREIGN KEY (id_bar) REFERENCES bars, FOREIGN KEY (id_biere) REFERENCES bieres); INSERT INTO bars VALUES (1, 'Bar du Coin', 'France'); INSERT INTO bars VALUES (2, 'Corners Pub', 'USA'); INSERT INTO bars VALUES (3, 'Cafe der Ecke','Allemagne'); INSERT INTO bars VALUES (4, 'Cafe des Amis', 'France'); INSERT INTO bieres VALUES (1, 'Kronenbourg', 'Blonde', 'France'); INSERT INTO bieres VALUES (2, 'Guinness', 'Brune', 'Irlande'); INSERT INTO bieres VALUES (3, 'Heineken', 'Blonde', 'Hollande'); INSERT INTO bieres VALUES (4, 'Newcastle', 'Rousse', 'UK'); INSERT INTO bieres VALUES (5, 'Spaten', 'Blonde', 'Allemagne'); INSERT INTO bieres VALUES (6, 'Bush', 'Blonde', 'USA'); INSERT INTO bieres VALUES (7, 'Kanterbrau', 'Blonde', 'France'); INSERT INTO bieres VALUES (8, 'Kronenbourg', 'Brune', 'France'); INSERT INTO services VALUES (1,1,1000); INSERT INTO services VALUES (1,2, 250); INSERT INTO services VALUES (1,3, 50); INSERT INTO services VALUES (1,4, 10); INSERT INTO services VALUES (1,5, 10); INSERT INTO services VALUES (1,6, 10); INSERT INTO services VALUES (1,7, 20); INSERT INTO services VALUES (1,8, 20); INSERT INTO services VALUES (2,1, 100); INSERT INTO services VALUES (2,6, 1500); INSERT INTO services VALUES (3,5, 5000);

Vue d'ensemble

{ "class": "go.GraphLinksModel",
  "copiesArrays": true,
  "copiesArrayObjects": true,
  "linkFromPortIdProperty": "fromPort",
  "linkToPortIdProperty": "toPort",
  "nodeDataArray": [ 
{"category":"table", "name":"bars", "info":"TABLE", "key":-1, "loc":"25 325"},
{"category":"table", "name":"bieres", "info":"TABLE", "key":-2, "loc":"225 325"},
{"category":"project", "name":"P_1", "info":"PROJECTION", "key":-3, "loc":"25 200", "attributes":"(pays)"},
{"category":"project", "name":"P_2", "info":"PROJECTION", "key":-4, "loc":"225 200", "attributes":"(origine)"},
{"category":"query", "name":"Q_1", "info":"QUERY", "key":-6, "loc":"125 25"},
{"category":"inter", "name":"I_1", "info":"INTERSECTION", "key":-13, "loc":"125 125"}
 ],
  "linkDataArray": [ 
{"from":-1, "to":-3, "fromPort":"", "toPort":"B", "points":[25,308.65,25,308.65,25,285,25,285,25,261.35,25,246.35000000000002]},
{"from":-2, "to":-4, "fromPort":"", "toPort":"B", "points":[225,308.65,225,308.65,225,285,225,285,225,261.35,225,246.35000000000002]},
{"from":-3, "to":-13, "fromPort":"T", "toPort":"L", "points":[25,195.35000000000002,25,195.35000000000002,25,104.15,54.5,104.15,84,104.15,99,104.15]},
{"from":-4, "to":-13, "fromPort":"T", "toPort":"R", "points":[225,195.35000000000002,225,195.35000000000002,225,104.15,195.5,104.15,166,104.15,151,104.15]},
{"from":-13, "to":-6, "fromPort":"T", "toPort":"R", "points":[125,78.15,125,78.15,125,67.25,125,67.25,125,56.35,125,41.35]}
 ]}

CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY,
                    bar    VARCHAR(20),
                    pays   VARCHAR(20) );

CREATE TABLE bieres ( id_biere  INTEGER NOT NULL PRIMARY KEY,
                      biere     VARCHAR(20),
                      couleur   VARCHAR(10),
                      origine   VARCHAR(20) );

pays
Allemagne
France
USA

Différence

Construire un arbre de requêtes répondant à la question :

\(Q_1\) : « récupérer les noms des pays fabriquant de la bière mais n’ayant pas de bars ».

Votre réponse :

Arbre de requêtes : graph194.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY, bar VARCHAR(20), pays VARCHAR(20) ); CREATE TABLE bieres ( id_biere INTEGER NOT NULL PRIMARY KEY, biere VARCHAR(20), couleur VARCHAR(10), origine VARCHAR(20) ); CREATE TABLE services ( id_bar INTEGER NOT NULL, id_biere INTEGER NOT NULL, stock SMALLINT, PRIMARY KEY (id_bar, id_biere), FOREIGN KEY (id_bar) REFERENCES bars, FOREIGN KEY (id_biere) REFERENCES bieres); INSERT INTO bars VALUES (1, 'Bar du Coin', 'France'); INSERT INTO bars VALUES (2, 'Corners Pub', 'USA'); INSERT INTO bars VALUES (3, 'Cafe der Ecke','Allemagne'); INSERT INTO bars VALUES (4, 'Cafe des Amis', 'France'); INSERT INTO bieres VALUES (1, 'Kronenbourg', 'Blonde', 'France'); INSERT INTO bieres VALUES (2, 'Guinness', 'Brune', 'Irlande'); INSERT INTO bieres VALUES (3, 'Heineken', 'Blonde', 'Hollande'); INSERT INTO bieres VALUES (4, 'Newcastle', 'Rousse', 'UK'); INSERT INTO bieres VALUES (5, 'Spaten', 'Blonde', 'Allemagne'); INSERT INTO bieres VALUES (6, 'Bush', 'Blonde', 'USA'); INSERT INTO bieres VALUES (7, 'Kanterbrau', 'Blonde', 'France'); INSERT INTO bieres VALUES (8, 'Kronenbourg', 'Brune', 'France'); INSERT INTO services VALUES (1,1,1000); INSERT INTO services VALUES (1,2, 250); INSERT INTO services VALUES (1,3, 50); INSERT INTO services VALUES (1,4, 10); INSERT INTO services VALUES (1,5, 10); INSERT INTO services VALUES (1,6, 10); INSERT INTO services VALUES (1,7, 20); INSERT INTO services VALUES (1,8, 20); INSERT INTO services VALUES (2,1, 100); INSERT INTO services VALUES (2,6, 1500); INSERT INTO services VALUES (3,5, 5000);

Vue d'ensemble

CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY,
                    bar    VARCHAR(20),
                    pays   VARCHAR(20) );

CREATE TABLE bieres ( id_biere  INTEGER NOT NULL PRIMARY KEY,
                      biere     VARCHAR(20),
                      couleur   VARCHAR(10),
                      origine   VARCHAR(20) );

origine
Hollande
Irlande
UK

Une solution possible :

On applique d’abord une projection (\(\Pi\)) sur les colonnes pays de table bars et origine de la table bieres pour pouvoir appliquer l’opération ensembliste de différence (\(\displaystyle {\setminus}\)).

Les opérations ensemblistes sont des opérateurs binaires qui doivent avoir pour chaque entrée (opérande) le même nombre d’attributs, chacun étant défini sur le même domaine.

Ecriture en algèbre relationnelle :

\(Q_1 = \setminus(\Pi_{(pays)}(bars), \Pi_{(origine)}(bieres))\)

Arbre de requêtes : bars-10.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	{ "class": "go.GraphLinksModel", "copiesArrays": true, "copiesArrayObjects": true, "linkFromPortIdProperty": "fromPort", "linkToPortIdProperty": "toPort", "nodeDataArray": [ {"category":"table", "name":"bieres", "info":"TABLE", "key":-1, "loc":"25 325"}, {"category":"table", "name":"bars", "info":"TABLE", "key":-2, "loc":"225 325"}, {"category":"project", "name":"P_1", "info":"PROJECTION", "key":-3, "loc":"25 200", "attributes":"(origine)"}, {"category":"project", "name":"P_2", "info":"PROJECTION", "key":-4, "loc":"225 200", "attributes":"(pays)"}, {"category":"query", "name":"Q_1", "info":"QUERY", "key":-6, "loc":"125 25"}, {"category":"minus", "name":"E_1", "info":"EXCEPT", "key":-13, "loc":"125 150"} ], "linkDataArray": [ {"from":-1, "to":-3, "fromPort":"", "toPort":"B", "points":[24.999999999999996,308.65,24.999999999999996,308.65,24.999999999999996,285,25,285,25,261.34999999999997,25,246.34999999999997]}, {"from":-2, "to":-4, "fromPort":"", "toPort":"B", "points":[225.00000000000003,308.65,225.00000000000003,308.65,225.00000000000003,285,225,285,225,261.35,225,246.35000000000002]}, {"from":-3, "to":-13, "fromPort":"T", "toPort":"L", "points":[25,195.34999999999997,25,195.34999999999997,25,188,25,188,25,129.15,84,129.15,99,129.15]}, {"from":-4, "to":-13, "fromPort":"T", "toPort":"R", "points":[225,195.35000000000002,225,195.35000000000002,225,188,225,188,225,129.15,166,129.15,151,129.15]}, {"from":-13, "to":-6, "fromPort":"T", "toPort":"R", "points":[125,103.15,125,103.15,125,79.75,125,79.75,125,56.35,125,41.35]} ]} CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY, bar VARCHAR(20), pays VARCHAR(20) ); CREATE TABLE bieres ( id_biere INTEGER NOT NULL PRIMARY KEY, biere VARCHAR(20), couleur VARCHAR(10), origine VARCHAR(20) ); CREATE TABLE services ( id_bar INTEGER NOT NULL, id_biere INTEGER NOT NULL, stock SMALLINT, PRIMARY KEY (id_bar, id_biere), FOREIGN KEY (id_bar) REFERENCES bars, FOREIGN KEY (id_biere) REFERENCES bieres); INSERT INTO bars VALUES (1, 'Bar du Coin', 'France'); INSERT INTO bars VALUES (2, 'Corners Pub', 'USA'); INSERT INTO bars VALUES (3, 'Cafe der Ecke','Allemagne'); INSERT INTO bars VALUES (4, 'Cafe des Amis', 'France'); INSERT INTO bieres VALUES (1, 'Kronenbourg', 'Blonde', 'France'); INSERT INTO bieres VALUES (2, 'Guinness', 'Brune', 'Irlande'); INSERT INTO bieres VALUES (3, 'Heineken', 'Blonde', 'Hollande'); INSERT INTO bieres VALUES (4, 'Newcastle', 'Rousse', 'UK'); INSERT INTO bieres VALUES (5, 'Spaten', 'Blonde', 'Allemagne'); INSERT INTO bieres VALUES (6, 'Bush', 'Blonde', 'USA'); INSERT INTO bieres VALUES (7, 'Kanterbrau', 'Blonde', 'France'); INSERT INTO bieres VALUES (8, 'Kronenbourg', 'Brune', 'France'); INSERT INTO services VALUES (1,1,1000); INSERT INTO services VALUES (1,2, 250); INSERT INTO services VALUES (1,3, 50); INSERT INTO services VALUES (1,4, 10); INSERT INTO services VALUES (1,5, 10); INSERT INTO services VALUES (1,6, 10); INSERT INTO services VALUES (1,7, 20); INSERT INTO services VALUES (1,8, 20); INSERT INTO services VALUES (2,1, 100); INSERT INTO services VALUES (2,6, 1500); INSERT INTO services VALUES (3,5, 5000);

Vue d'ensemble

{ "class": "go.GraphLinksModel",
  "copiesArrays": true,
  "copiesArrayObjects": true,
  "linkFromPortIdProperty": "fromPort",
  "linkToPortIdProperty": "toPort",
  "nodeDataArray": [ 
{"category":"table", "name":"bieres", "info":"TABLE", "key":-1, "loc":"25 325"},
{"category":"table", "name":"bars", "info":"TABLE", "key":-2, "loc":"225 325"},
{"category":"project", "name":"P_1", "info":"PROJECTION", "key":-3, "loc":"25 200", "attributes":"(origine)"},
{"category":"project", "name":"P_2", "info":"PROJECTION", "key":-4, "loc":"225 200", "attributes":"(pays)"},
{"category":"query", "name":"Q_1", "info":"QUERY", "key":-6, "loc":"125 25"},
{"category":"minus", "name":"E_1", "info":"EXCEPT", "key":-13, "loc":"125 150"}
 ],
  "linkDataArray": [ 
{"from":-1, "to":-3, "fromPort":"", "toPort":"B", "points":[24.999999999999996,308.65,24.999999999999996,308.65,24.999999999999996,285,25,285,25,261.34999999999997,25,246.34999999999997]},
{"from":-2, "to":-4, "fromPort":"", "toPort":"B", "points":[225.00000000000003,308.65,225.00000000000003,308.65,225.00000000000003,285,225,285,225,261.35,225,246.35000000000002]},
{"from":-3, "to":-13, "fromPort":"T", "toPort":"L", "points":[25,195.34999999999997,25,195.34999999999997,25,188,25,188,25,129.15,84,129.15,99,129.15]},
{"from":-4, "to":-13, "fromPort":"T", "toPort":"R", "points":[225,195.35000000000002,225,195.35000000000002,225,188,225,188,225,129.15,166,129.15,151,129.15]},
{"from":-13, "to":-6, "fromPort":"T", "toPort":"R", "points":[125,103.15,125,103.15,125,79.75,125,79.75,125,56.35,125,41.35]}
 ]}

CREATE TABLE bars ( id_bar INTEGER NOT NULL PRIMARY KEY,
                    bar    VARCHAR(20),
                    pays   VARCHAR(20) );

CREATE TABLE bieres ( id_biere  INTEGER NOT NULL PRIMARY KEY,
                      biere     VARCHAR(20),
                      couleur   VARCHAR(10),
                      origine   VARCHAR(20) );

origine
Hollande
Irlande
UK

Fichier de rejeu Close

Indication Close

A propos de... Close

Commentaire Close

Systèmes d'Information

Téléchargements

Aide

En-tête

Pied de page

Operations ensemblistes

Votre réponse :

Tables

Opérateurs unaires

Opérateurs binaires

Une solution possible :

Tables

Opérateurs unaires

Opérateurs binaires

Votre réponse :

Tables

Opérateurs unaires

Opérateurs binaires

Une solution possible :

Tables

Opérateurs unaires

Opérateurs binaires

Votre réponse :

Tables

Opérateurs unaires

Opérateurs binaires

Une solution possible :

Tables

Opérateurs unaires

Opérateurs binaires