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

Recherches sur une table

L’objectif de ces exercices est de savoir représenter sous forme d’arbre de requêtes les requêtes de base sur une seule table de la base de données « Aux pays des bières ».

8/8 Aux pays des bières : SELECT, FROM, WHERE

Vérification du contenu de la table 'bars'

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

\(Q_1\) : « récupérer les bars de la base de données ».

Votre réponse :

Arbre de requêtes : graph123.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);

id_bar	bar	pays
1	Bar du Coin	France
2	Corners Pub	USA
3	Cafe der Ecke	Allemagne
4	Cafe des Amis	France

Une solution possible :

Récupérer les bars : \(\displaystyle Q_1 = bars\)

Arbre de requêtes : bars-0-1.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 350"}, {"category":"query", "name":"Q_1", "info":"QUERY", "key":-2, "loc":"-25 150"} ], "linkDataArray": [ {"from":-1, "to":-2, "fromPort":"", "toPort":"R", "points":[-24.999999999999986,333.65,-24.999999999999986,333.65,-24.999999999999986,333.65,-24.999999999999986,333.65,-24.999999999999986,181.35000000000008,-24.999999999999986,166.35000000000008]} ]} 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) );

id_bar	bar	pays
1	Bar du Coin	France
2	Corners Pub	USA
3	Cafe der Ecke	Allemagne
4	Cafe des Amis	France

Vérification du contenu de la table 'services'

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

\(Q_1\) : « récupérer les services de la base de données ».

Votre réponse :

Arbre de requêtes : graph130.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) );

id_bar	id_biere	stock
1	1	1000
1	2	250
1	3	50
1	4	10
1	5	10
1	6	10
1	7	20
1	8	20
2	1	100
2	6	1500
3	5	5000

Une solution possible :

Récupérer les bars : \(\displaystyle Q_1 = services\)

Arbre de requêtes : bars-0-2.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":"services", "info":"TABLE", "key":-1, "loc":"0 275"}, {"category":"query", "name":"Q_1", "info":"QUERY", "key":-2, "loc":"0 75"} ], "linkDataArray": [ {"from":-1, "to":-2, "fromPort":"", "toPort":"R", "points":[0,258.65,0,258.65,0,182.5,2.842170943040401e-14,182.5,2.842170943040401e-14,106.35000000000004,2.842170943040401e-14,91.35000000000004]} ]} 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) );

id_bar	id_biere	stock
1	1	1000
1	2	250
1	3	50
1	4	10
1	5	10
1	6	10
1	7	20
1	8	20
2	1	100
2	6	1500
3	5	5000

Vérification du contenu de la table 'bieres'

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

\(Q_1\) : « récupérer les bieres de la base de données ».

Votre réponse :

Arbre de requêtes : graph137.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) );

id_biere	biere	couleur	origine
1	Kronenbourg	Blonde	France
2	Guinness	Brune	Irlande
3	Heineken	Blonde	Hollande
4	Newcastle	Rousse	UK
5	Spaten	Blonde	Allemagne
6	Bush	Blonde	USA
7	Kanterbrau	Blonde	France
8	Kronenbourg	Brune	France

Une solution possible :

Récupérer les bars : \(\displaystyle Q_1 = bieres\)

Arbre de requêtes : bars-0-3.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":"0 275"}, {"category":"query", "name":"Q_1", "info":"QUERY", "key":-2, "loc":"0 75"} ], "linkDataArray": [ {"from":-1, "to":-2, "fromPort":"", "toPort":"R", "points":[0,258.65,0,258.65,0,182.5,2.842170943040401e-14,182.5,2.842170943040401e-14,106.35000000000004,2.842170943040401e-14,91.35000000000004]} ]} 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) );

id_biere	biere	couleur	origine
1	Kronenbourg	Blonde	France
2	Guinness	Brune	Irlande
3	Heineken	Blonde	Hollande
4	Newcastle	Rousse	UK
5	Spaten	Blonde	Allemagne
6	Bush	Blonde	USA
7	Kanterbrau	Blonde	France
8	Kronenbourg	Brune	France

Projection

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

\(Q_1\) : « récupérer le nom et la couleur des bières ».

Votre réponse :

Arbre de requêtes : graph144.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) );

biere	couleur
Kronenbourg	Blonde
Guinness	Brune
Heineken	Blonde
Newcastle	Rousse
Spaten	Blonde
Bush	Blonde
Kanterbrau	Blonde
Kronenbourg	Brune

Une solution possible :

On applique une projection (\(\Pi\)) sur les colonnes (biere,couleur) de la table bieres.

Ecriture en algèbre relationnelle :

\(Q_1 = \Pi_{(biere,couleur)}(bieres)\)

Arbre de requêtes : bars-1.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	{ "class": "go.GraphLinksModel", "copiesArrays": true, "copiesArrayObjects": true, "linkFromPortIdProperty": "fromPort", "linkToPortIdProperty": "toPort", "nodeDataArray": [ {"category":"query", "name":"Q_1", "info":"QUERY", "key":-2, "loc":"275 75"}, {"category":"table", "name":"bieres", "info":"TABLE", "key":-1, "loc":"275 300"}, {"category":"project", "name":"P_1", "info":"PROJECTION", "key":-3, "loc":"275 175", "attributes":"(biere,couleur)"} ], "linkDataArray": [ {"from":-1, "to":-3, "fromPort":"", "toPort":"B", "points":[275,283.65,275,283.65,275,260,275,260,275,236.35000000000002,275,221.35000000000002]}, {"from":-3, "to":-2, "fromPort":"T", "toPort":"R", "points":[275,170.35000000000002,275,170.35000000000002,275,138.35000000000002,275,138.35000000000002,275,106.35,275,91.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

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) );

biere	couleur
Kronenbourg	Blonde
Guinness	Brune
Heineken	Blonde
Newcastle	Rousse
Spaten	Blonde
Bush	Blonde
Kanterbrau	Blonde
Kronenbourg	Brune

Restriction

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

\(Q_1\) : « récupérer les bières d’origine française ».

Votre réponse :

Arbre de requêtes : graph151.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) );

id_biere	biere	couleur	origine
1	Kronenbourg	Blonde	France
7	Kanterbrau	Blonde	France
8	Kronenbourg	Brune	France

Une solution possible :

On applique une restriction (\(\sigma\)) avec la condition [origine = 'France'] sur la table bieres.

Ecriture en algèbre relationnelle :

\(Q_1 = \sigma_{[origine = 'France']}(bieres)\)

Arbre de requêtes : bars-2.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	{ "class": "go.GraphLinksModel", "copiesArrays": true, "copiesArrayObjects": true, "linkFromPortIdProperty": "fromPort", "linkToPortIdProperty": "toPort", "nodeDataArray": [ {"category":"query", "name":"Q_1", "info":"QUERY", "key":-2, "loc":"275 25"}, {"category":"table", "name":"bieres", "info":"TABLE", "key":-1, "loc":"275 300"}, {"category":"restrict", "name":"R_1", "info":"RESTRICTION", "key":-3, "loc":"275 200", "condition":"[origine='France']"} ], "linkDataArray": [ {"from":-1, "to":-3, "fromPort":"", "toPort":"B", "points":[275,283.65,275,283.65,275,283.65,275,283.65,275,219.65000000000006,275,204.65000000000006]}, {"from":-3, "to":-2, "fromPort":"T", "toPort":"R", "points":[275,153.65000000000006,275,153.65000000000006,275,153.65000000000006,275,153.65000000000006,275,56.35,275,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

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) );

id_biere	biere	couleur	origine
1	Kronenbourg	Blonde	France
7	Kanterbrau	Blonde	France
8	Kronenbourg	Brune	France

Projection et restriction

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

\(Q_1\) : « récupérer le nom bières françaises ».

Votre réponse :

Arbre de requêtes : graph158.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) );

biere
Kronenbourg
Kanterbrau
Kronenbourg

Une solution possible :

On applique une projection (\(\Pi\)) sur la colonne biere et une restriction (\(\sigma\)) sur la condition [origine='France'] de la table bieres.

Ecriture en algèbre relationnelle :

\(Q_1 = \Pi_{(biere)}(\sigma_{[origine='France']}(bieres))\).

Arbre de requêtes : bars-3.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	{ "class": "go.GraphLinksModel", "copiesArrays": true, "copiesArrayObjects": true, "linkFromPortIdProperty": "fromPort", "linkToPortIdProperty": "toPort", "nodeDataArray": [ {"category":"query", "name":"Q_1", "info":"QUERY", "key":-2, "loc":"275 75"}, {"category":"table", "name":"bieres", "info":"TABLE", "key":-1, "loc":"275 425"}, {"category":"project", "name":"P_1", "info":"PROJECTION", "key":-3, "loc":"275 175", "attributes":"(biere)"}, {"category":"restrict", "name":"R", "info":"RESTRICTION", "key":-4, "loc":"275 325", "condition":"[origine='France']\n"} ], "linkDataArray": [ {"from":-3, "to":-2, "fromPort":"T", "toPort":"R", "points":[275,170.35000000000002,275,170.35000000000002,275,138.35000000000002,275,138.35000000000002,275,106.35,275,91.35]}, {"from":-1, "to":-4, "fromPort":"", "toPort":"B", "points":[275,408.65,275,408.65,275,408.65,275,408.65,275,338.80000000000007,275,323.80000000000007]}, {"from":-4, "to":-3, "fromPort":"T", "toPort":"B", "points":[275,272.80000000000007,275,272.80000000000007,275,272.80000000000007,275,272.80000000000007,275,236.35000000000002,275,221.35000000000002]} ]} 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":"query", "name":"Q_1", "info":"QUERY", "key":-2, "loc":"275 75"},
{"category":"table", "name":"bieres", "info":"TABLE", "key":-1, "loc":"275 425"},
{"category":"project", "name":"P_1", "info":"PROJECTION", "key":-3, "loc":"275 175", "attributes":"(biere)"},
{"category":"restrict", "name":"R", "info":"RESTRICTION", "key":-4, "loc":"275 325", "condition":"[origine='France']\n"}
 ],
  "linkDataArray": [ 
{"from":-3, "to":-2, "fromPort":"T", "toPort":"R", "points":[275,170.35000000000002,275,170.35000000000002,275,138.35000000000002,275,138.35000000000002,275,106.35,275,91.35]},
{"from":-1, "to":-4, "fromPort":"", "toPort":"B", "points":[275,408.65,275,408.65,275,408.65,275,408.65,275,338.80000000000007,275,323.80000000000007]},
{"from":-4, "to":-3, "fromPort":"T", "toPort":"B", "points":[275,272.80000000000007,275,272.80000000000007,275,272.80000000000007,275,272.80000000000007,275,236.35000000000002,275,221.35000000000002]}
 ]}

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) );

biere
Kronenbourg
Kanterbrau
Kronenbourg

Connecteur logique

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

\(Q_1\) : « récupérer les bières blondes françaises ».

Votre réponse :

Arbre de requêtes : graph165.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) );

id_biere	biere	couleur	origine
1	Kronenbourg	Blonde	France
7	Kanterbrau	Blonde	France

Une solution possible :

On vérifie (\(\sigma\)) que les bières sont d’origine française (origine='France') et (\(\land\)) qu’elles sont de couleur blonde (couleur='Blonde')

Ecriture en algèbre relationnelle :

\(Q_1 = \sigma_{[origine='France' \land couleur='Blonde']}(bieres)\).

Arbre de requêtes : bars-4.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	{ "class": "go.GraphLinksModel", "copiesArrays": true, "copiesArrayObjects": true, "linkFromPortIdProperty": "fromPort", "linkToPortIdProperty": "toPort", "nodeDataArray": [ {"category":"query", "name":"Q_1", "info":"QUERY", "key":-2, "loc":"275 25"}, {"category":"table", "name":"bieres", "info":"TABLE", "key":-1, "loc":"275 275"}, {"category":"restrict", "name":"R", "info":"RESTRICTION", "key":-4, "loc":"275 175", "condition":"[origine='France' and couleur='Blonde']\n"} ], "linkDataArray": [ {"from":-1, "to":-4, "fromPort":"", "toPort":"B", "points":[274.9999999999999,258.64999999999986,274.9999999999999,258.64999999999986,275,258.64999999999986,275,258.64999999999986,275,188.8,275,173.8]}, {"from":-4, "to":-2, "fromPort":"T", "toPort":"R", "points":[275,122.80000000000001,275,122.80000000000001,274.9999999999999,122.80000000000001,274.9999999999999,122.80000000000001,274.9999999999999,56.34999999999998,274.9999999999999,41.34999999999998]} ]} 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) );

id_biere	biere	couleur	origine
1	Kronenbourg	Blonde	France
7	Kanterbrau	Blonde	France

Connecteurs logiques

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

\(Q_1\) : « récupérer le nom et la couleur des bières françaises blondes et les bières brunes ».

Votre réponse :

Arbre de requêtes : graph172.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) );

id_biere	biere	couleur	origine
1	Kronenbourg	Blonde	France
2	Guinness	Brune	Irlande
7	Kanterbrau	Blonde	France
8	Kronenbourg	Brune	France

Une solution possible :

On vérifie (\(\sigma\)) sur la table bieres qu’elles sont d’origine française (origine='France') et (\(\land\)) de couleur blonde (couleur='Blonde') ou (\(\lor\)) que la couleur des bières est brune (couleur='Brune') .

Ecriture en algèbre relationnelle :

\(Q_1 = \sigma_{[origine='France' \; \land \; couleur='Blonde' \; \lor \; couleur='Brune']}(bieres)\)

Attention à la priorité du connecteur logique \(\land\) sur le \(\lor\) (de même que la multiplication l’est sur l’addition).

La requête :

\(Q_1 = \sigma_{[origine='France' \; \lor \; couleur='Blonde' \; \land \; couleur='Brune']}(bieres)\)

donnerait comme résultat … les bières françaises (il ne peut exister des bières blonde et brune à la fois).

On peut modifier la priorité en parenthésant l’expression logique.

Qu’obtiendrait-on avec l’expression algébrique :

\(Q_2 = \sigma_{[origine='France' \; \land \; (couleur='Blonde' \; \lor \; couleur='Brune')]}(bieres)\)

Arbre de requêtes : bars-5.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	{ "class": "go.GraphLinksModel", "copiesArrays": true, "copiesArrayObjects": true, "linkFromPortIdProperty": "fromPort", "linkToPortIdProperty": "toPort", "nodeDataArray": [ {"category":"query", "name":"Q_1", "info":"QUERY", "key":-2, "loc":"275 175"}, {"category":"table", "name":"bieres", "info":"TABLE", "key":-1, "loc":"275 425"}, {"category":"restrict", "name":"R", "info":"RESTRICTION", "key":-4, "loc":"275 325", "condition":"[origine='France' and couleur='Blonde' or couleur='Brune']\n"} ], "linkDataArray": [ {"from":-1, "to":-4, "fromPort":"", "toPort":"B", "points":[275,408.65,275,408.65,275,408.65,275,408.65,275,338.8000000000001,275,323.8000000000001]}, {"from":-4, "to":-2, "fromPort":"T", "toPort":"R", "points":[275,272.8000000000001,275,272.8000000000001,275,272.8000000000001,275,272.8000000000001,275,206.35000000000002,275,191.35000000000002]} ]} 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) );

id_biere	biere	couleur	origine
1	Kronenbourg	Blonde	France
2	Guinness	Brune	Irlande
7	Kanterbrau	Blonde	France
8	Kronenbourg	Brune	France

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

Recherches sur une table

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

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

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