/* Jeu de données des cours de Stanford francisé / Dataset from Stanford classes translated in French
** Fabien Duchateau, 2022
** Version francisée en ligne : https://perso.liris.cnrs.fr/fabien.duchateau/BDW/
** Sources : https://lagunita.stanford.edu/courses/Home/Databases/Engineering/about
*/

DROP TABLE IF EXISTS Universite;
DROP TABLE IF EXISTS Eleve;
DROP TABLE IF EXISTS Candidature;

/* Creation des trois tables / Creating the three tables */
CREATE TABLE Universite(nomU VARCHAR(255) PRIMARY KEY, ville VARCHAR(255), effectif INT);
CREATE TABLE Eleve(idE INT PRIMARY KEY, nomE VARCHAR(255), moyenneLycee REAL, effectifLycee INT);
CREATE TABLE Candidature(idE INT REFERENCES Eleve(idE), nomU VARCHAR(255) REFERENCES Universite(nomU), departement VARCHAR(255), decision CHAR(1), PRIMARY KEY (idE,nomU,departement));

/* Insertions de tuples dans les tables / Inserting tuples
** Les notes ont été multipliées par 5 par rapport aux notes d'origine
** The original marks have been multiplied by 5
*/
INSERT INTO Eleve VALUES (123, 'Ana', 19.5, 1000);
INSERT INTO Eleve VALUES (234, 'Bob', 18.0, 1500);
INSERT INTO Eleve VALUES (345, 'Chloe', 17.5, 500);
INSERT INTO Eleve VALUES (456, 'Damien', 19.5, 1000);
INSERT INTO Eleve VALUES (567, 'Eleonore', 14.5, 2000);
INSERT INTO Eleve VALUES (678, 'Farid', 19.0, 200);
INSERT INTO Eleve VALUES (789, 'Gisele', 17.0, 800);
INSERT INTO Eleve VALUES (898, 'Hector', 18.5, 800);
INSERT INTO Eleve VALUES (876, 'Irene', 19.5, 400);
INSERT INTO Eleve VALUES (765, 'Joana', 14.5, 1500);
INSERT INTO Eleve VALUES (654, 'Ana', 19.5, 1000);
INSERT INTO Eleve VALUES (543, 'Chloe', 17.0, 2000);

INSERT INTO Universite VALUES ('UCB', 'Lyon', 15000);
INSERT INTO Universite VALUES ('INSA', 'Lyon', 36000);
INSERT INTO Universite VALUES ('UJF', 'Grenoble', 10000);
INSERT INTO Universite VALUES ('UJM', 'Saint-Etienne', 21000);

INSERT INTO Candidature VALUES (123, 'UCB', 'informatique', 'O');
INSERT INTO Candidature VALUES (123, 'UCB', 'electronique', 'N');
INSERT INTO Candidature VALUES (123, 'INSA', 'informatique', 'O');
INSERT INTO Candidature VALUES (123, 'UJM', 'electronique', 'O');
INSERT INTO Candidature VALUES (234, 'INSA', 'biologie', 'N');
INSERT INTO Candidature VALUES (345, 'UJF', 'bioinformatique', 'O');
INSERT INTO Candidature VALUES (345, 'UJM', 'bioinformatique', 'N');
INSERT INTO Candidature VALUES (345, 'UJM', 'informatique', 'O');
INSERT INTO Candidature VALUES (345, 'UJM', 'electronique', 'N');
INSERT INTO Candidature VALUES (678, 'UCB', 'histoire', 'O');
INSERT INTO Candidature VALUES (898, 'UCB', 'informatique', 'O');
INSERT INTO Candidature VALUES (898, 'INSA', 'informatique', 'O');
INSERT INTO Candidature VALUES (876, 'UCB', 'informatique', 'N');
INSERT INTO Candidature VALUES (876, 'UJF', 'biologie', 'O');
INSERT INTO Candidature VALUES (876, 'UJF', 'biologie marine', 'N');
INSERT INTO Candidature VALUES (765, 'UCB', 'histoire', 'O');
INSERT INTO Candidature VALUES (765, 'UJM', 'histoire', 'N');
INSERT INTO Candidature VALUES (765, 'UJM', 'psychologie', 'O');
INSERT INTO Candidature VALUES (543, 'UJF', 'informatique', 'N');

