CREATE TABLE Apprenti
   (Nom VARCHAR(25) NOT NULL,
    Prenom VARCHAR(25),
    NSS INTEGER NOT NULL UNIQUE);

CREATE TABLE Ingenieur
   (Nom VARCHAR(25) NOT NULL,
    Prenom VARCHAR(25),
    CodeIngenieur INTEGER NOT NULL UNIQUE,
    NomEntreprise VARCHAR(25) NOT NULL,
    CodeEntreprise INTEGER NOT NULL);

CREATE TABLE Enseignant
   (Nom VARCHAR(25) NOT NULL,
    Prenom VARCHAR(25),
    Statut VARCHAR(25),
    CodeEnseignant INTEGER NOT NULL UNIQUE);

CREATE TABLE Cours
   (Intitule VARCHAR(25) NOT NULL,
    CodeCours VARCHAR(25) NOT NULL UNIQUE);

CREATE TABLE Affectation
   (CodeEnseignant INTEGER NOT NULL,
    CodeCours VARCHAR(25) NOT NULL UNIQUE);

CREATE TABLE Inscription
   (NSS INTEGER NOT NULL,
    CodeCours VARCHAR(25) NOT NULL);

CREATE TABLE Tutorat
   (NSS INTEGER NOT NULL UNIQUE,
    CodeEnseignant INTEGER NOT NULL,
    CodeIngenieur INTEGER NOT NULL);

INSERT INTO Apprenti
VALUES('Dupont','Jacques',1450);

INSERT INTO Apprenti
VALUES('Dupond','Francois',1451);

INSERT INTO Apprenti
VALUES('Tintin','David',1452);

INSERT INTO Apprenti
VALUES('Milou','Jerome',1453);

INSERT INTO Enseignant
VALUES('Beal','Marie-Pierre','titulaire',8001);

INSERT INTO Enseignant
VALUES('Roussel','Gilles','titulaire',8002);

INSERT INTO Enseignant
VALUES('Berstel','Jean','titulaire',8003);

INSERT INTO Enseignant
VALUES('Revuz','Dominique','titulaire',8004);

INSERT INTO Ingenieur
VALUES('Atome','Michel',9202,'CEA',100);

INSERT INTO Ingenieur
VALUES('Dechet','Ludovic',9203,'ANDRA',200);

INSERT INTO Ingenieur
VALUES('Monge','Gaspard',9204,'CEA',100);

INSERT INTO Ingenieur
VALUES('Banquetout','Felix',9205,'Credit-Avantageux',300);

INSERT INTO Cours
VALUES('Java','IR00');

INSERT INTO Cours
VALUES('Interfaces graphiques','IR01');

INSERT INTO Cours
VALUES('Systemes','IR04');

INSERT INTO Cours
VALUES('Reseaux','IR12');

INSERT INTO Affectation
VALUES(8001,'IR00');

INSERT INTO Affectation
VALUES(8003,'IR01');

INSERT INTO Affectation
VALUES(8004,'IR04');

INSERT INTO Affectation
VALUES(8004,'IR12');

INSERT INTO Inscription
SELECT NSS, CodeCours
FROM Apprenti, Cours;

INSERT INTO Tutorat
VALUES(1450,8001,9202);

INSERT INTO Tutorat
VALUES(1451,8001,9204);

INSERT INTO Tutorat
VALUES(1452,8002,9205);

INSERT INTO Tutorat
VALUES(1453,8003,9202);
