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 « Salles de concerts ».

5/5 SELECT, FROM, WHERE

Vérification du contenu de la table 'salles'

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

math: Q_1 : « Rechercher toutes les informations sur les salles de la base de données ».

math:	Q_1 : « Rechercher toutes les informations sur les salles de la base de données ».

Votre réponse :

Arbre de requêtes : graph74.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	drop table if exists salles; drop table if exists spectacles; drop table if exists concerts; drop table if exists billets; drop table if exists ventes; CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), capacite INTEGER DEFAULT 100 ); CREATE TABLE spectacles (spec_id INTEGER PRIMARY KEY AUTOINCREMENT, titre VARCHAR(20), duree TIME, chanteur VARCHAR(20), salle_id INTEGER, FOREIGN KEY (salle_id) REFERENCES salles ); CREATE TABLE concerts (concert_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, heure TIME, spec_id INTEGER, FOREIGN KEY (spec_id) REFERENCES spectacles ); CREATE TABLE billets (billet_id INTEGER PRIMARY KEY AUTOINCREMENT, concert_id INTEGER NOT NULL, prix SMALLINT, FOREIGN KEY (concert_id) REFERENCES concerts ); CREATE TABLE ventes(vente_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, paiement VARCHAR(10), billet_id INTEGER NOT NULL UNIQUE, FOREIGN KEY (billet_id) REFERENCES billet ); INSERT INTO salles(nom,capacite) VALUES ('vauban',100); INSERT INTO salles(nom,capacite) VALUES ('quartz',500); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','04:00:00','laStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','03:00:00','laStar',2); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go out','02:30:00','TheStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go in','03:45:00','TheBigStar',2); DELETE FROM spectacles WHERE spec_id=5; INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5); DELETE FROM concerts WHERE spec_id=5; INSERT INTO billets(concert_id,prix) VALUES (1,35); INSERT INTO billets(concert_id,prix) VALUES (1,50); INSERT INTO billets(concert_id,prix) VALUES (2,100); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2); ----

Vue d'ensemble

drop table if exists salles;
drop table if exists spectacles;
drop table if exists concerts;
drop table if exists billets;
drop table if exists ventes;

CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    nom      VARCHAR(20),
                    capacite INTEGER DEFAULT 100
);

CREATE TABLE spectacles (spec_id   INTEGER PRIMARY KEY AUTOINCREMENT,
                        titre    VARCHAR(20),
                        duree    TIME,
                        chanteur VARCHAR(20),
                        salle_id  INTEGER,
                        FOREIGN KEY (salle_id) REFERENCES salles
);

CREATE TABLE concerts (concert_id  INTEGER PRIMARY KEY AUTOINCREMENT,
                      jour       DATE,
                      heure      TIME,
                      spec_id  INTEGER,
                      FOREIGN KEY (spec_id) REFERENCES spectacles
);

CREATE TABLE billets (billet_id   INTEGER PRIMARY KEY AUTOINCREMENT,
                     concert_id INTEGER NOT NULL,
                     prix       SMALLINT,
                     FOREIGN KEY (concert_id) REFERENCES concerts

);

CREATE TABLE ventes(vente_id  INTEGER PRIMARY KEY AUTOINCREMENT,
                   jour     DATE,
                   paiement VARCHAR(10),
                   billet_id INTEGER NOT NULL UNIQUE,
                   FOREIGN KEY (billet_id) REFERENCES billet

);

INSERT INTO salles(nom,capacite) VALUES ('vauban',100);
INSERT INTO salles(nom,capacite) VALUES ('quartz',500);

INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','04:00:00','laStar',1);
INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','03:00:00','laStar',2);
INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go out','02:30:00','TheStar',1);
INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go in','03:45:00','TheBigStar',2);

DELETE FROM spectacles WHERE spec_id=5;

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1);
INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1);

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5);
DELETE FROM concerts WHERE spec_id=5;

INSERT INTO billets(concert_id,prix) VALUES (1,35);
INSERT INTO billets(concert_id,prix) VALUES (1,50);
INSERT INTO billets(concert_id,prix) VALUES (2,100);

INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1);
INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2);

----

salle_id	nom	capacite
1	vauban	100
2	quartz	500

Une solution possible :

Récupérer toutes les informations : \(Q_1 = salles\)

Arbre de requêtes : concerts-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":"salles", "info":"TABLE", "key":-1, "loc":"125 275"}, {"category":"query", "name":"Q", "info":"QUERY", "key":-2, "loc":"125 75"} ], "linkDataArray": [ {"from":-1, "to":-2, "fromPort":"", "toPort":"R", "points":[125,258.65,125,258.65,125,182.5,125,182.5,125,106.35,125,91.35]} ]} drop table if exists salles; drop table if exists spectacles; drop table if exists concerts; drop table if exists billets; drop table if exists ventes; CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), capacite INTEGER DEFAULT 100 ); CREATE TABLE spectacles (spec_id INTEGER PRIMARY KEY AUTOINCREMENT, titre VARCHAR(20), duree TIME, chanteur VARCHAR(20), salle_id INTEGER, FOREIGN KEY (salle_id) REFERENCES salles ); CREATE TABLE concerts (concert_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, heure TIME, spec_id INTEGER, FOREIGN KEY (spec_id) REFERENCES spectacles ); CREATE TABLE billets (billet_id INTEGER PRIMARY KEY AUTOINCREMENT, concert_id INTEGER NOT NULL, prix SMALLINT, FOREIGN KEY (concert_id) REFERENCES concerts ); CREATE TABLE ventes(vente_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, paiement VARCHAR(10), billet_id INTEGER NOT NULL UNIQUE, FOREIGN KEY (billet_id) REFERENCES billet ); INSERT INTO salles(nom,capacite) VALUES ('vauban',100); INSERT INTO salles(nom,capacite) VALUES ('quartz',500); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','04:00:00','laStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','03:00:00','laStar',2); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go out','02:30:00','TheStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go in','03:45:00','TheBigStar',2); DELETE FROM spectacles WHERE spec_id=5; INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5); DELETE FROM concerts WHERE spec_id=5; INSERT INTO billets(concert_id,prix) VALUES (1,35); INSERT INTO billets(concert_id,prix) VALUES (1,50); INSERT INTO billets(concert_id,prix) VALUES (2,100); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2); ----

Vue d'ensemble

drop table if exists salles;
drop table if exists spectacles;
drop table if exists concerts;
drop table if exists billets;
drop table if exists ventes;

CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    nom      VARCHAR(20),
                    capacite INTEGER DEFAULT 100
);

);

INSERT INTO salles(nom,capacite) VALUES ('vauban',100);
INSERT INTO salles(nom,capacite) VALUES ('quartz',500);

DELETE FROM spectacles WHERE spec_id=5;

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1);
INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1);

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5);
DELETE FROM concerts WHERE spec_id=5;

INSERT INTO billets(concert_id,prix) VALUES (1,35);
INSERT INTO billets(concert_id,prix) VALUES (1,50);
INSERT INTO billets(concert_id,prix) VALUES (2,100);

INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1);
INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2);

----

salle_id	nom	capacite
1	vauban	100
2	quartz	500

Spectacles de 4 heures

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

math: Q_1 : « Rechercher les spectacles de 4 heures ».

math:	Q_1 : « Rechercher les spectacles de 4 heures ».

Votre réponse :

Arbre de requêtes : graph81.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	drop table if exists salles; drop table if exists spectacles; drop table if exists concerts; drop table if exists billets; drop table if exists ventes; CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), capacite INTEGER DEFAULT 100 ); CREATE TABLE spectacles (spec_id INTEGER PRIMARY KEY AUTOINCREMENT, titre VARCHAR(20), duree TIME, chanteur VARCHAR(20), salle_id INTEGER, FOREIGN KEY (salle_id) REFERENCES salles ); CREATE TABLE concerts (concert_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, heure TIME, spec_id INTEGER, FOREIGN KEY (spec_id) REFERENCES spectacles ); CREATE TABLE billets (billet_id INTEGER PRIMARY KEY AUTOINCREMENT, concert_id INTEGER NOT NULL, prix SMALLINT, FOREIGN KEY (concert_id) REFERENCES concerts ); CREATE TABLE ventes(vente_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, paiement VARCHAR(10), billet_id INTEGER NOT NULL UNIQUE, FOREIGN KEY (billet_id) REFERENCES billet ); INSERT INTO salles(nom,capacite) VALUES ('vauban',100); INSERT INTO salles(nom,capacite) VALUES ('quartz',500); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','04:00:00','laStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','03:00:00','laStar',2); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go out','02:30:00','TheStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go in','03:45:00','TheBigStar',2); DELETE FROM spectacles WHERE spec_id=5; INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5); DELETE FROM concerts WHERE spec_id=5; INSERT INTO billets(concert_id,prix) VALUES (1,35); INSERT INTO billets(concert_id,prix) VALUES (1,50); INSERT INTO billets(concert_id,prix) VALUES (2,100); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2); ----

Vue d'ensemble

drop table if exists salles;
drop table if exists spectacles;
drop table if exists concerts;
drop table if exists billets;
drop table if exists ventes;

CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    nom      VARCHAR(20),
                    capacite INTEGER DEFAULT 100
);

);

INSERT INTO salles(nom,capacite) VALUES ('vauban',100);
INSERT INTO salles(nom,capacite) VALUES ('quartz',500);

DELETE FROM spectacles WHERE spec_id=5;

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1);
INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1);

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5);
DELETE FROM concerts WHERE spec_id=5;

INSERT INTO billets(concert_id,prix) VALUES (1,35);
INSERT INTO billets(concert_id,prix) VALUES (1,50);
INSERT INTO billets(concert_id,prix) VALUES (2,100);

INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1);
INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2);

----

spec_id	titre	duree	chanteur	salles_id
1	show must go on	04:00:00	laStar	1

Une solution possible :

Récupérer toutes les informations : \(Q_1 = \sigma_{duree='04:00:00'}(spectacles)\)

Arbre de requêtes : concerts-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":"spectacles", "info":"TABLE", "key":-1, "loc":"150 325"}, {"category":"query", "name":"Q", "info":"QUERY", "key":-2, "loc":"150 100"}, {"category":"restrict", "name":"R", "condition":"[duree='04:00:00']", "info":"RESTRICTION", "key":-3, "loc":"150 225"} ], "linkDataArray": [ {"from":-1, "to":-3, "fromPort":"", "toPort":"B", "points":[150,308.65,150,308.65,150,276.65,150,276.65,150,244.65,150,229.65]}, {"from":-3, "to":-2, "fromPort":"T", "toPort":"R", "points":[150,178.65,150,178.65,150,155,149.99999999999997,155,149.99999999999997,131.35000000000002,149.99999999999997,116.35000000000001]} ]} drop table if exists salles; drop table if exists spectacles; drop table if exists concerts; drop table if exists billets; drop table if exists ventes; CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), capacite INTEGER DEFAULT 100 ); CREATE TABLE spectacles (spec_id INTEGER PRIMARY KEY AUTOINCREMENT, titre VARCHAR(20), duree TIME, chanteur VARCHAR(20), salle_id INTEGER, FOREIGN KEY (salle_id) REFERENCES salles ); CREATE TABLE concerts (concert_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, heure TIME, spec_id INTEGER, FOREIGN KEY (spec_id) REFERENCES spectacles ); CREATE TABLE billets (billet_id INTEGER PRIMARY KEY AUTOINCREMENT, concert_id INTEGER NOT NULL, prix SMALLINT, FOREIGN KEY (concert_id) REFERENCES concerts ); CREATE TABLE ventes(vente_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, paiement VARCHAR(10), billet_id INTEGER NOT NULL UNIQUE, FOREIGN KEY (billet_id) REFERENCES billet ); INSERT INTO salles(nom,capacite) VALUES ('vauban',100); INSERT INTO salles(nom,capacite) VALUES ('quartz',500); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','04:00:00','laStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','03:00:00','laStar',2); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go out','02:30:00','TheStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go in','03:45:00','TheBigStar',2); DELETE FROM spectacles WHERE spec_id=5; INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5); DELETE FROM concerts WHERE spec_id=5; INSERT INTO billets(concert_id,prix) VALUES (1,35); INSERT INTO billets(concert_id,prix) VALUES (1,50); INSERT INTO billets(concert_id,prix) VALUES (2,100); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2); ----

Vue d'ensemble

drop table if exists salles;
drop table if exists spectacles;
drop table if exists concerts;
drop table if exists billets;
drop table if exists ventes;

CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    nom      VARCHAR(20),
                    capacite INTEGER DEFAULT 100
);

);

INSERT INTO salles(nom,capacite) VALUES ('vauban',100);
INSERT INTO salles(nom,capacite) VALUES ('quartz',500);

DELETE FROM spectacles WHERE spec_id=5;

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1);
INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1);

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5);
DELETE FROM concerts WHERE spec_id=5;

INSERT INTO billets(concert_id,prix) VALUES (1,35);
INSERT INTO billets(concert_id,prix) VALUES (1,50);
INSERT INTO billets(concert_id,prix) VALUES (2,100);

INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1);
INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2);

----

spec_id	titre	duree	chanteur	salles_id
1	show must go on	04:00:00	laStar	1

jour et heure des concerts qui durent 4 heures

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

math: Q_1 : « jour et heure des concerts de spectacles de 4 heures ».

math:	Q_1 : « jour et heure des concerts de spectacles de 4 heures ».

Votre réponse :

Arbre de requêtes : graph88.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	drop table if exists salles; drop table if exists spectacles; drop table if exists concerts; drop table if exists billets; drop table if exists ventes; CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), capacite INTEGER DEFAULT 100 ); CREATE TABLE spectacles (spec_id INTEGER PRIMARY KEY AUTOINCREMENT, titre VARCHAR(20), duree TIME, chanteur VARCHAR(20), salle_id INTEGER, FOREIGN KEY (salle_id) REFERENCES salles ); CREATE TABLE concerts (concert_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, heure TIME, spec_id INTEGER, FOREIGN KEY (spec_id) REFERENCES spectacles ); CREATE TABLE billets (billet_id INTEGER PRIMARY KEY AUTOINCREMENT, concert_id INTEGER NOT NULL, prix SMALLINT, FOREIGN KEY (concert_id) REFERENCES concerts ); CREATE TABLE ventes(vente_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, paiement VARCHAR(10), billet_id INTEGER NOT NULL UNIQUE, FOREIGN KEY (billet_id) REFERENCES billet ); INSERT INTO salles(nom,capacite) VALUES ('vauban',100); INSERT INTO salles(nom,capacite) VALUES ('quartz',500); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','04:00:00','laStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','03:00:00','laStar',2); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go out','02:30:00','TheStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go in','03:45:00','TheBigStar',2); DELETE FROM spectacles WHERE spec_id=5; INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5); DELETE FROM concerts WHERE spec_id=5; INSERT INTO billets(concert_id,prix) VALUES (1,35); INSERT INTO billets(concert_id,prix) VALUES (1,50); INSERT INTO billets(concert_id,prix) VALUES (2,100); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2); ----

Vue d'ensemble

drop table if exists salles;
drop table if exists spectacles;
drop table if exists concerts;
drop table if exists billets;
drop table if exists ventes;

CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    nom      VARCHAR(20),
                    capacite INTEGER DEFAULT 100
);

);

INSERT INTO salles(nom,capacite) VALUES ('vauban',100);
INSERT INTO salles(nom,capacite) VALUES ('quartz',500);

DELETE FROM spectacles WHERE spec_id=5;

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1);
INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1);

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5);
DELETE FROM concerts WHERE spec_id=5;

INSERT INTO billets(concert_id,prix) VALUES (1,35);
INSERT INTO billets(concert_id,prix) VALUES (1,50);
INSERT INTO billets(concert_id,prix) VALUES (2,100);

INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1);
INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2);

----

jour	heure
2015-12-24	21:00:00
2015-12-31	21:00:00

Une solution possible :

\(Q = \Pi_{(jour,heure)}(\Join_{[]}(\sigma_{[duree='04:00:00']}(spectacles),concerts))\)

Arbre de requêtes : concerts-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":"spectacles", "info":"TABLE", "key":-1, "loc":"175 400"}, {"category":"query", "name":"Q", "info":"QUERY", "key":-2, "loc":"250 -25"}, {"category":"restrict", "name":"R", "condition":"[duree='04:00:00']", "info":"RESTRICTION", "key":-3, "loc":"175 300"}, {"category":"innerjoin", "name":"IJ", "condition":"[]", "info":"INNER JOIN", "key":-6, "loc":"275 200"}, {"category":"table", "name":"concerts", "info":"TABLE", "key":-5, "loc":"375 400"}, {"category":"project", "name":"P", "attributes":"(jour,heure)", "info":"PROJECTION", "key":-7, "loc":"250 50"} ], "linkDataArray": [ {"from":-1, "to":-3, "fromPort":"", "toPort":"B", "points":[174.99999999999994,383.65,174.99999999999994,383.65,174.99999999999994,351.65000000000003,175,351.65000000000003,175,319.6500000000001,175,304.6500000000001]}, {"from":-3, "to":-6, "fromPort":"T", "toPort":"L", "points":[175,253.65000000000006,175,253.65000000000006,175,236.65000000000003,236.81,236.65000000000003,236.81,219.65,236.81,204.65]}, {"from":-5, "to":-6, "fromPort":"", "toPort":"R", "points":[375,383.65,375,383.65,375,301.65,287.19,301.65,287.19,219.65,287.19,204.65]}, {"from":-6, "to":-7, "fromPort":"T", "toPort":"B", "points":[262,179.099,262,179.099,262,180,262,180,262,140,250,140,250,111.35,250,96.35]}, {"from":-7, "to":-2, "fromPort":"T", "toPort":"R", "points":[250,45.35,250,45.35,250,25.849999999999984,250,25.849999999999984,250,6.349999999999966,250,-8.650000000000034]} ]} drop table if exists salles; drop table if exists spectacles; drop table if exists concerts; drop table if exists billets; drop table if exists ventes; CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), capacite INTEGER DEFAULT 100 ); CREATE TABLE spectacles (spec_id INTEGER PRIMARY KEY AUTOINCREMENT, titre VARCHAR(20), duree TIME, chanteur VARCHAR(20), salle_id INTEGER, FOREIGN KEY (salle_id) REFERENCES salles ); CREATE TABLE concerts (concert_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, heure TIME, spec_id INTEGER, FOREIGN KEY (spec_id) REFERENCES spectacles ); CREATE TABLE billets (billet_id INTEGER PRIMARY KEY AUTOINCREMENT, concert_id INTEGER NOT NULL, prix SMALLINT, FOREIGN KEY (concert_id) REFERENCES concerts ); CREATE TABLE ventes(vente_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, paiement VARCHAR(10), billet_id INTEGER NOT NULL UNIQUE, FOREIGN KEY (billet_id) REFERENCES billet ); INSERT INTO salles(nom,capacite) VALUES ('vauban',100); INSERT INTO salles(nom,capacite) VALUES ('quartz',500); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','04:00:00','laStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','03:00:00','laStar',2); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go out','02:30:00','TheStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go in','03:45:00','TheBigStar',2); DELETE FROM spectacles WHERE spec_id=5; INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5); DELETE FROM concerts WHERE spec_id=5; INSERT INTO billets(concert_id,prix) VALUES (1,35); INSERT INTO billets(concert_id,prix) VALUES (1,50); INSERT INTO billets(concert_id,prix) VALUES (2,100); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2); ----

Vue d'ensemble

drop table if exists salles;
drop table if exists spectacles;
drop table if exists concerts;
drop table if exists billets;
drop table if exists ventes;

CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    nom      VARCHAR(20),
                    capacite INTEGER DEFAULT 100
);

);

INSERT INTO salles(nom,capacite) VALUES ('vauban',100);
INSERT INTO salles(nom,capacite) VALUES ('quartz',500);

DELETE FROM spectacles WHERE spec_id=5;

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1);
INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1);

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5);
DELETE FROM concerts WHERE spec_id=5;

INSERT INTO billets(concert_id,prix) VALUES (1,35);
INSERT INTO billets(concert_id,prix) VALUES (1,50);
INSERT INTO billets(concert_id,prix) VALUES (2,100);

INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1);
INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2);

----

jour	heure
2015-12-24	21:00:00
2015-12-31	21:00:00

jour et heure des concerts de 'laStar' au 'vauban'

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

math: Q_1 : « jour et heure des concerts de “laStar” au “vauban” ».

math:	Q_1 : « jour et heure des concerts de “laStar” au “vauban” ».

Votre réponse :

Arbre de requêtes : graph95.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	drop table if exists salles; drop table if exists spectacles; drop table if exists concerts; drop table if exists billets; drop table if exists ventes; CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), capacite INTEGER DEFAULT 100 ); CREATE TABLE spectacles (spec_id INTEGER PRIMARY KEY AUTOINCREMENT, titre VARCHAR(20), duree TIME, chanteur VARCHAR(20), salle_id INTEGER, FOREIGN KEY (salle_id) REFERENCES salles ); CREATE TABLE concerts (concert_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, heure TIME, spec_id INTEGER, FOREIGN KEY (spec_id) REFERENCES spectacles ); CREATE TABLE billets (billet_id INTEGER PRIMARY KEY AUTOINCREMENT, concert_id INTEGER NOT NULL, prix SMALLINT, FOREIGN KEY (concert_id) REFERENCES concerts ); CREATE TABLE ventes(vente_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, paiement VARCHAR(10), billet_id INTEGER NOT NULL UNIQUE, FOREIGN KEY (billet_id) REFERENCES billet ); INSERT INTO salles(nom,capacite) VALUES ('vauban',100); INSERT INTO salles(nom,capacite) VALUES ('quartz',500); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','04:00:00','laStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','03:00:00','laStar',2); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go out','02:30:00','TheStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go in','03:45:00','TheBigStar',2); DELETE FROM spectacles WHERE spec_id=5; INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5); DELETE FROM concerts WHERE spec_id=5; INSERT INTO billets(concert_id,prix) VALUES (1,35); INSERT INTO billets(concert_id,prix) VALUES (1,50); INSERT INTO billets(concert_id,prix) VALUES (2,100); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2); ----

Vue d'ensemble

drop table if exists salles;
drop table if exists spectacles;
drop table if exists concerts;
drop table if exists billets;
drop table if exists ventes;

CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    nom      VARCHAR(20),
                    capacite INTEGER DEFAULT 100
);

);

INSERT INTO salles(nom,capacite) VALUES ('vauban',100);
INSERT INTO salles(nom,capacite) VALUES ('quartz',500);

DELETE FROM spectacles WHERE spec_id=5;

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1);
INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1);

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5);
DELETE FROM concerts WHERE spec_id=5;

INSERT INTO billets(concert_id,prix) VALUES (1,35);
INSERT INTO billets(concert_id,prix) VALUES (1,50);
INSERT INTO billets(concert_id,prix) VALUES (2,100);

INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1);
INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2);

----

jour	heure
2015-12-24	21:00:00
2015-12-31	21:00:00

Une solution possible :

\(Q = \Pi_{(jour,heure)}(\Join_{[]}(\sigma_{[nom='vauban']}(salles),\Join_{[]}(\sigma_{[chanteur='laStar']}(spectacles),concerts)))\)

Arbre de requêtes : concerts-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":"spectacles", "info":"TABLE", "key":-1, "loc":"175 400"}, {"category":"query", "name":"Q", "info":"QUERY", "key":-2, "loc":"450 -50"}, {"category":"restrict", "name":"R_1", "condition":"[chanteur='laStar']", "info":"RESTRICTION", "key":-3, "loc":"175 325"}, {"category":"innerjoin", "name":"NJ_1", "condition":"[]", "info":"INNER JOIN", "key":-6, "loc":"275 225"}, {"category":"table", "name":"concerts", "info":"TABLE", "key":-5, "loc":"375 400"}, {"category":"project", "name":"P", "attributes":"(jour,heure)", "info":"PROJECTION", "key":-7, "loc":"450 25"}, {"category":"table", "name":"salles", "info":"TABLE", "key":-8, "loc":"525 400"}, {"category":"innerjoin", "name":"NJ_2", "condition":"[]", "info":"INNER JOIN", "key":-9, "loc":"450 150"}, {"category":"restrict", "name":"R_2", "condition":"[nom='vauban']", "info":"RESTRICTION", "key":-10, "loc":"525 300"} ], "linkDataArray": [ {"from":-1, "to":-3, "fromPort":"", "toPort":"B", "points":[175,383.65,175,383.65,175,364.15,174.99999999999997,364.15,174.99999999999997,344.6499999999999,174.99999999999997,329.6499999999999]}, {"from":-3, "to":-6, "fromPort":"T", "toPort":"L", "points":[174.99999999999997,278.6499999999999,174.99999999999997,278.6499999999999,174.99999999999997,268,227.81,268,227.81,244.65,227.81,229.65]}, {"from":-5, "to":-6, "fromPort":"", "toPort":"R", "points":[375,383.65,375,383.65,375,314.15,278.19,314.15,278.19,244.65,278.19,229.65]}, {"from":-7, "to":-2, "fromPort":"T", "toPort":"R", "points":[450.0000000000001,20.35,450.0000000000001,20.35,450.0000000000001,0.850000000000005,450,0.850000000000005,450,-18.64999999999999,450,-33.64999999999999]}, {"from":-6, "to":-9, "fromPort":"T", "toPort":"L", "points":[253,204.099,253,204.099,253,196,253,196,253,172,402.81000000000006,172,402.81000000000006,169.65,402.81000000000006,154.65]}, {"from":-9, "to":-7, "fromPort":"T", "toPort":"B", "points":[428.00000000000006,129.099,428.00000000000006,129.099,428.00000000000006,124,428.00000000000006,124,428.00000000000006,92,450,92,450,86.35,450,71.35]}, {"from":-8, "to":-10, "fromPort":"", "toPort":"B", "points":[525,383.65,525,383.65,525,383.65,525,383.65,525,319.65,525,304.65]}, {"from":-10, "to":-9, "fromPort":"T", "toPort":"R", "points":[525,253.65,525,253.65,525,244,453.19000000000005,244,453.19000000000005,169.65,453.19000000000005,154.65]} ]} drop table if exists salles; drop table if exists spectacles; drop table if exists concerts; drop table if exists billets; drop table if exists ventes; CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), capacite INTEGER DEFAULT 100 ); CREATE TABLE spectacles (spec_id INTEGER PRIMARY KEY AUTOINCREMENT, titre VARCHAR(20), duree TIME, chanteur VARCHAR(20), salle_id INTEGER, FOREIGN KEY (salle_id) REFERENCES salles ); CREATE TABLE concerts (concert_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, heure TIME, spec_id INTEGER, FOREIGN KEY (spec_id) REFERENCES spectacles ); CREATE TABLE billets (billet_id INTEGER PRIMARY KEY AUTOINCREMENT, concert_id INTEGER NOT NULL, prix SMALLINT, FOREIGN KEY (concert_id) REFERENCES concerts ); CREATE TABLE ventes(vente_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, paiement VARCHAR(10), billet_id INTEGER NOT NULL UNIQUE, FOREIGN KEY (billet_id) REFERENCES billet ); INSERT INTO salles(nom,capacite) VALUES ('vauban',100); INSERT INTO salles(nom,capacite) VALUES ('quartz',500); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','04:00:00','laStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','03:00:00','laStar',2); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go out','02:30:00','TheStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go in','03:45:00','TheBigStar',2); DELETE FROM spectacles WHERE spec_id=5; INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5); DELETE FROM concerts WHERE spec_id=5; INSERT INTO billets(concert_id,prix) VALUES (1,35); INSERT INTO billets(concert_id,prix) VALUES (1,50); INSERT INTO billets(concert_id,prix) VALUES (2,100); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2); ----

Vue d'ensemble

{ "class": "go.GraphLinksModel",
  "copiesArrays": true,
  "copiesArrayObjects": true,
  "linkFromPortIdProperty": "fromPort",
  "linkToPortIdProperty": "toPort",
  "nodeDataArray": [ 
{"category":"table", "name":"spectacles", "info":"TABLE", "key":-1, "loc":"175 400"},
{"category":"query", "name":"Q", "info":"QUERY", "key":-2, "loc":"450 -50"},
{"category":"restrict", "name":"R_1", "condition":"[chanteur='laStar']", "info":"RESTRICTION", "key":-3, "loc":"175 325"},
{"category":"innerjoin", "name":"NJ_1", "condition":"[]", "info":"INNER JOIN", "key":-6, "loc":"275 225"},
{"category":"table", "name":"concerts", "info":"TABLE", "key":-5, "loc":"375 400"},
{"category":"project", "name":"P", "attributes":"(jour,heure)", "info":"PROJECTION", "key":-7, "loc":"450 25"},
{"category":"table", "name":"salles", "info":"TABLE", "key":-8, "loc":"525 400"},
{"category":"innerjoin", "name":"NJ_2", "condition":"[]", "info":"INNER JOIN", "key":-9, "loc":"450 150"},
{"category":"restrict", "name":"R_2", "condition":"[nom='vauban']", "info":"RESTRICTION", "key":-10, "loc":"525 300"}
 ],
  "linkDataArray": [ 
{"from":-1, "to":-3, "fromPort":"", "toPort":"B", "points":[175,383.65,175,383.65,175,364.15,174.99999999999997,364.15,174.99999999999997,344.6499999999999,174.99999999999997,329.6499999999999]},
{"from":-3, "to":-6, "fromPort":"T", "toPort":"L", "points":[174.99999999999997,278.6499999999999,174.99999999999997,278.6499999999999,174.99999999999997,268,227.81,268,227.81,244.65,227.81,229.65]},
{"from":-5, "to":-6, "fromPort":"", "toPort":"R", "points":[375,383.65,375,383.65,375,314.15,278.19,314.15,278.19,244.65,278.19,229.65]},
{"from":-7, "to":-2, "fromPort":"T", "toPort":"R", "points":[450.0000000000001,20.35,450.0000000000001,20.35,450.0000000000001,0.850000000000005,450,0.850000000000005,450,-18.64999999999999,450,-33.64999999999999]},
{"from":-6, "to":-9, "fromPort":"T", "toPort":"L", "points":[253,204.099,253,204.099,253,196,253,196,253,172,402.81000000000006,172,402.81000000000006,169.65,402.81000000000006,154.65]},
{"from":-9, "to":-7, "fromPort":"T", "toPort":"B", "points":[428.00000000000006,129.099,428.00000000000006,129.099,428.00000000000006,124,428.00000000000006,124,428.00000000000006,92,450,92,450,86.35,450,71.35]},
{"from":-8, "to":-10, "fromPort":"", "toPort":"B", "points":[525,383.65,525,383.65,525,383.65,525,383.65,525,319.65,525,304.65]},
{"from":-10, "to":-9, "fromPort":"T", "toPort":"R", "points":[525,253.65,525,253.65,525,244,453.19000000000005,244,453.19000000000005,169.65,453.19000000000005,154.65]}
 ]}

drop table if exists salles;
drop table if exists spectacles;
drop table if exists concerts;
drop table if exists billets;
drop table if exists ventes;

CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    nom      VARCHAR(20),
                    capacite INTEGER DEFAULT 100
);

);

INSERT INTO salles(nom,capacite) VALUES ('vauban',100);
INSERT INTO salles(nom,capacite) VALUES ('quartz',500);

DELETE FROM spectacles WHERE spec_id=5;

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1);
INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1);

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5);
DELETE FROM concerts WHERE spec_id=5;

INSERT INTO billets(concert_id,prix) VALUES (1,35);
INSERT INTO billets(concert_id,prix) VALUES (1,50);
INSERT INTO billets(concert_id,prix) VALUES (2,100);

INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1);
INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2);

----

jour	heure
2015-12-24	21:00:00
2015-12-31	21:00:00

chanteurs qui n ont jamais fait de spectacle au 'vauban'

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

math: Q_1 : « les chanteurs qui n ont jamais fait de spectacle au “vauban” ».

math:	Q_1 : « les chanteurs qui n ont jamais fait de spectacle au “vauban” ».

Votre réponse :

Arbre de requêtes : graph102.json


Tables Opérateurs unaires Opérateurs binaires

Vue d'ensemble
	drop table if exists salles; drop table if exists spectacles; drop table if exists concerts; drop table if exists billets; drop table if exists ventes; CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), capacite INTEGER DEFAULT 100 ); CREATE TABLE spectacles (spec_id INTEGER PRIMARY KEY AUTOINCREMENT, titre VARCHAR(20), duree TIME, chanteur VARCHAR(20), salle_id INTEGER, FOREIGN KEY (salle_id) REFERENCES salles ); CREATE TABLE concerts (concert_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, heure TIME, spec_id INTEGER, FOREIGN KEY (spec_id) REFERENCES spectacles ); CREATE TABLE billets (billet_id INTEGER PRIMARY KEY AUTOINCREMENT, concert_id INTEGER NOT NULL, prix SMALLINT, FOREIGN KEY (concert_id) REFERENCES concerts ); CREATE TABLE ventes(vente_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, paiement VARCHAR(10), billet_id INTEGER NOT NULL UNIQUE, FOREIGN KEY (billet_id) REFERENCES billet ); INSERT INTO salles(nom,capacite) VALUES ('vauban',100); INSERT INTO salles(nom,capacite) VALUES ('quartz',500); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','04:00:00','laStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','03:00:00','laStar',2); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go out','02:30:00','TheStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go in','03:45:00','TheBigStar',2); DELETE FROM spectacles WHERE spec_id=5; INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5); DELETE FROM concerts WHERE spec_id=5; INSERT INTO billets(concert_id,prix) VALUES (1,35); INSERT INTO billets(concert_id,prix) VALUES (1,50); INSERT INTO billets(concert_id,prix) VALUES (2,100); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2); ----

Vue d'ensemble

drop table if exists salles;
drop table if exists spectacles;
drop table if exists concerts;
drop table if exists billets;
drop table if exists ventes;

CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    nom      VARCHAR(20),
                    capacite INTEGER DEFAULT 100
);

);

INSERT INTO salles(nom,capacite) VALUES ('vauban',100);
INSERT INTO salles(nom,capacite) VALUES ('quartz',500);

DELETE FROM spectacles WHERE spec_id=5;

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1);
INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1);

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5);
DELETE FROM concerts WHERE spec_id=5;

INSERT INTO billets(concert_id,prix) VALUES (1,35);
INSERT INTO billets(concert_id,prix) VALUES (1,50);
INSERT INTO billets(concert_id,prix) VALUES (2,100);

INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1);
INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2);

----

chanteur
TheBigStar

Une solution possible :

\(Q = \setminus(\Pi_{(chanteur)}(spectacles),\Pi_{(chanteur)}(\Join_{[]}(spectacles,\sigma_{[nom='vauban']}(salles))))\)

Arbre de requêtes : concerts-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":"table", "name":"spectacles", "info":"TABLE", "key":-1, "loc":"250 400"}, {"category":"query", "name":"Q", "info":"QUERY", "key":-2, "loc":"325 -50"}, {"category":"restrict", "name":"R", "condition":"[nom='vauban']", "info":"RESTRICTION", "key":-3, "loc":"525 325"}, {"category":"project", "name":"P_1", "attributes":"(chanteur)", "info":"PROJECTION", "key":-7, "loc":"250 75"}, {"category":"table", "name":"salles", "info":"TABLE", "key":-8, "loc":"525 400"}, {"category":"innerjoin", "name":"NJ_1", "condition":"[]", "info":"INNER JOIN", "key":-9, "loc":"425 200"}, {"category":"project", "name":"P_2", "attributes":"(chanteur)", "info":"PROJECTION", "key":-12, "loc":"400 75"}, {"category":"minus", "name":"E", "info":"EXCEPT", "key":-11, "loc":"325 50"} ], "linkDataArray": [ {"from":-8, "to":-3, "fromPort":"", "toPort":"B", "points":[525,383.65,525,383.65,525,364.15,525,364.15,525,344.65,525,329.65]}, {"from":-1, "to":-9, "fromPort":"", "toPort":"L", "points":[250,383.65000000000003,250,383.65000000000003,250,301.65000000000003,377.81,301.65000000000003,377.81,219.65,377.81,204.65]}, {"from":-3, "to":-9, "fromPort":"T", "toPort":"R", "points":[525,278.65,525,278.65,525,276,525,276,525,268,428.19,268,428.19,219.65,428.19,204.65]}, {"from":-1, "to":-7, "fromPort":"", "toPort":"B", "points":[250,383.65000000000003,250,383.65000000000003,250,383.65000000000003,250,383.65000000000003,250,136.35,250,121.35]}, {"from":-9, "to":-12, "fromPort":"T", "toPort":"B", "points":[403,179.099,403,179.099,403,157.72449999999998,400,157.72449999999998,400,136.35,400,121.35]}, {"from":-7, "to":-11, "fromPort":"T", "toPort":"L", "points":[250,70.35,250,70.35,250,29.15,267,29.15,284,29.15,299,29.15]}, {"from":-12, "to":-11, "fromPort":"T", "toPort":"R", "points":[400,70.35,400,70.35,400,29.15,383,29.15,366,29.15,351,29.15]}, {"from":-11, "to":-2, "fromPort":"T", "toPort":"R", "points":[325,3.1499999999999986,325,3.1499999999999986,325,-7.749999999999989,325,-7.749999999999989,325,-18.649999999999977,325,-33.64999999999998]} ]} drop table if exists salles; drop table if exists spectacles; drop table if exists concerts; drop table if exists billets; drop table if exists ventes; CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), capacite INTEGER DEFAULT 100 ); CREATE TABLE spectacles (spec_id INTEGER PRIMARY KEY AUTOINCREMENT, titre VARCHAR(20), duree TIME, chanteur VARCHAR(20), salle_id INTEGER, FOREIGN KEY (salle_id) REFERENCES salles ); CREATE TABLE concerts (concert_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, heure TIME, spec_id INTEGER, FOREIGN KEY (spec_id) REFERENCES spectacles ); CREATE TABLE billets (billet_id INTEGER PRIMARY KEY AUTOINCREMENT, concert_id INTEGER NOT NULL, prix SMALLINT, FOREIGN KEY (concert_id) REFERENCES concerts ); CREATE TABLE ventes(vente_id INTEGER PRIMARY KEY AUTOINCREMENT, jour DATE, paiement VARCHAR(10), billet_id INTEGER NOT NULL UNIQUE, FOREIGN KEY (billet_id) REFERENCES billet ); INSERT INTO salles(nom,capacite) VALUES ('vauban',100); INSERT INTO salles(nom,capacite) VALUES ('quartz',500); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','04:00:00','laStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go on','03:00:00','laStar',2); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go out','02:30:00','TheStar',1); INSERT INTO spectacles(titre,duree,chanteur,salle_id) VALUES ('show must go in','03:45:00','TheBigStar',2); DELETE FROM spectacles WHERE spec_id=5; INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1); INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5); DELETE FROM concerts WHERE spec_id=5; INSERT INTO billets(concert_id,prix) VALUES (1,35); INSERT INTO billets(concert_id,prix) VALUES (1,50); INSERT INTO billets(concert_id,prix) VALUES (2,100); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1); INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2); ----

Vue d'ensemble

{ "class": "go.GraphLinksModel",
  "copiesArrays": true,
  "copiesArrayObjects": true,
  "linkFromPortIdProperty": "fromPort",
  "linkToPortIdProperty": "toPort",
  "nodeDataArray": [ 
{"category":"table", "name":"spectacles", "info":"TABLE", "key":-1, "loc":"250 400"},
{"category":"query", "name":"Q", "info":"QUERY", "key":-2, "loc":"325 -50"},
{"category":"restrict", "name":"R", "condition":"[nom='vauban']", "info":"RESTRICTION", "key":-3, "loc":"525 325"},
{"category":"project", "name":"P_1", "attributes":"(chanteur)", "info":"PROJECTION", "key":-7, "loc":"250 75"},
{"category":"table", "name":"salles", "info":"TABLE", "key":-8, "loc":"525 400"},
{"category":"innerjoin", "name":"NJ_1", "condition":"[]", "info":"INNER JOIN", "key":-9, "loc":"425 200"},
{"category":"project", "name":"P_2", "attributes":"(chanteur)", "info":"PROJECTION", "key":-12, "loc":"400 75"},
{"category":"minus", "name":"E", "info":"EXCEPT", "key":-11, "loc":"325 50"}
 ],
  "linkDataArray": [ 
{"from":-8, "to":-3, "fromPort":"", "toPort":"B", "points":[525,383.65,525,383.65,525,364.15,525,364.15,525,344.65,525,329.65]},
{"from":-1, "to":-9, "fromPort":"", "toPort":"L", "points":[250,383.65000000000003,250,383.65000000000003,250,301.65000000000003,377.81,301.65000000000003,377.81,219.65,377.81,204.65]},
{"from":-3, "to":-9, "fromPort":"T", "toPort":"R", "points":[525,278.65,525,278.65,525,276,525,276,525,268,428.19,268,428.19,219.65,428.19,204.65]},
{"from":-1, "to":-7, "fromPort":"", "toPort":"B", "points":[250,383.65000000000003,250,383.65000000000003,250,383.65000000000003,250,383.65000000000003,250,136.35,250,121.35]},
{"from":-9, "to":-12, "fromPort":"T", "toPort":"B", "points":[403,179.099,403,179.099,403,157.72449999999998,400,157.72449999999998,400,136.35,400,121.35]},
{"from":-7, "to":-11, "fromPort":"T", "toPort":"L", "points":[250,70.35,250,70.35,250,29.15,267,29.15,284,29.15,299,29.15]},
{"from":-12, "to":-11, "fromPort":"T", "toPort":"R", "points":[400,70.35,400,70.35,400,29.15,383,29.15,366,29.15,351,29.15]},
{"from":-11, "to":-2, "fromPort":"T", "toPort":"R", "points":[325,3.1499999999999986,325,3.1499999999999986,325,-7.749999999999989,325,-7.749999999999989,325,-18.649999999999977,325,-33.64999999999998]}
 ]}

drop table if exists salles;
drop table if exists spectacles;
drop table if exists concerts;
drop table if exists billets;
drop table if exists ventes;

CREATE TABLE salles (salle_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    nom      VARCHAR(20),
                    capacite INTEGER DEFAULT 100
);

);

INSERT INTO salles(nom,capacite) VALUES ('vauban',100);
INSERT INTO salles(nom,capacite) VALUES ('quartz',500);

DELETE FROM spectacles WHERE spec_id=5;

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-24', '21:00:00',1);
INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-31', '21:00:00',1);

INSERT INTO concerts(jour,heure,spec_id) VALUES ('2015-12-25', '21:00:00',5);
DELETE FROM concerts WHERE spec_id=5;

INSERT INTO billets(concert_id,prix) VALUES (1,35);
INSERT INTO billets(concert_id,prix) VALUES (1,50);
INSERT INTO billets(concert_id,prix) VALUES (2,100);

INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-24', 'cheque',1);
INSERT INTO ventes(jour,paiement,billet_id) VALUES ('2015-11-30', 'carte',2);

----

chanteur
TheBigStar

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