CREATE DATABASE Trains
USE Trains

CREATE TABLE train (
	idTrain INTEGER,
	lundi BOOLEAN,
	mardi BOOLEAN,
	mercredi BOOLEAN,
	jeudi BOOLEAN,
	vendredi BOOLEAN,
	samedi BOOLEAN,
	dimanche BOOLEAN,
	PRIMARY KEY (idTrain) );

CREATE TABLE Arret (
	idTrain INTEGER,
	horaire TIME,
	ville VARCHAR(20) CHECK ( nomVille IN ('thonon', 'annemasse', 'bellegarde', 'annecy', 'chambery', 'la roche', 'grenoble') ),
	PRIMARY KEY (idTrain, ville), 
	FOREIGN KEY (idTrain) REFERENCES train(idTrain) );


INSERT INTO Train (1, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE);
INSERT INTO Arret (1, '05:01', 'thonon');
INSERT INTO Arret (1, '08:17', 'grenoble');


/* trains directs thonon-grenoble */
SELECT dep.horaire, dep.ville, arr.horaire, arr.ville
FROM Arret dep, Arret arr
WHERE dep.ville='thonon'
AND arr.ville='grenoble'
AND dep.idTrain=arr.idTrain
AND dep.horaire<arr.horaire;