-- V1.0.0

DROP TABLE Organigramme;
DROP TABLE Convention;
DROP TABLE Etudiant;
DROP TABLE Personnel;
DROP TABLE Societe;

CREATE TABLE Etudiant (
    ide NUMBER(38) NOT NULL PRIMARY KEY, 
    nom VARCHAR2(25) NOT NULL,
    prenom VARCHAR2(20),
    email VARCHAR2(25) UNIQUE,
    sexe CHAR(1) 	
        CONSTRAINT ck_etu_sex
        CHECK (sexe IN ('M', 'F') OR sexe IS NULL),
    daten DATE,
    adresse VARCHAR2(60),
    annee NUMBER(1) DEFAULT 3 CHECK (annee < 4),
    tel CHAR(14)
        CONSTRAINT ck_etu_tel 
        CHECK (REGEXP_LIKE(tel, '[0-9][0-9]-[0-9][0-9]-[0-9][0-9]-[0-9][0-9]-[0-9][0-9]')),
    CONSTRAINT uk_etu_01 UNIQUE (nom, prenom)
);

CREATE TABLE Societe (
    ids NUMBER(38) NOT NULL PRIMARY KEY,
    nom VARCHAR2(25) NOT NULL UNIQUE,
    raisons VARCHAR2(20),
    activite NUMBER(2),
    adresse VARCHAR2(60)
);

CREATE TABLE Personnel (
    idp NUMBER(38) NOT NULL PRIMARY KEY,
    nom VARCHAR2(25) NOT NULL,
    prenom VARCHAR2(20),
    ids NUMBER(38) NOT NULL,
    salaire NUMBER(8, 2),
    CONSTRAINT fk_per_soc FOREIGN KEY (ids) REFERENCES Societe(ids)
);

CREATE TABLE Convention (
    ide NUMBER(38) NOT NULL,
    ids NUMBER(38) NOT NULL,
    idp NUMBER(38) NOT NULL,
    datec DATE,
    date_deb DATE,
    duree NUMBER(38),
    intitule VARCHAR2(50),
    note NUMBER(2),
    CONSTRAINT pk_con PRIMARY KEY (ide, ids, idp),
    CONSTRAINT fk_con_etu FOREIGN KEY (ide) REFERENCES Etudiant(ide),
    CONSTRAINT fk_con_soc FOREIGN KEY (ids) REFERENCES Societe(ids),
    CONSTRAINT fk_con_per FOREIGN KEY (idp) REFERENCES Personnel(idp),
    CONSTRAINT ch_con_dat CHECK (datec < date_deb)
);

CREATE TABLE Organigramme (
    employe NUMBER(38) NOT NULL PRIMARY KEY,
	responsable NUMBER(38),
	CONSTRAINT fk_org_org FOREIGN KEY (responsable) REFERENCES Organigramme(employe),
	CONSTRAINT fk_org_per_01 FOREIGN KEY (employe) REFERENCES Personnel(idp),
	CONSTRAINT fk_org_per_02 FOREIGN KEY (responsable) REFERENCES Personnel(idp)
);

INSERT INTO Etudiant (ide, nom, prenom, email, sexe, daten, adresse, annee, tel) VALUES (8, 'Dupond', 'Lulu', 'dupond.lulu@jmail.com', 'M', TO_DATE('2001-03-16', 'YYYY-MM-DD'), 'Aix_en_Pce', 3, '01-23-45-67-89');
INSERT INTO Etudiant (ide, nom, prenom, email, sexe, daten, adresse, annee, tel) VALUES (12, 'Duvent', 'Yves', 'duvent.yves@jmail.com', 'M', TO_DATE('2001-04-20', 'YYYY-MM-DD'), 'Marseille', 2, '01-23-45-67-89');
INSERT INTO Etudiant (ide, nom, prenom, email, sexe, daten, adresse, annee, tel) VALUES (14, 'Dupond', 'Maurice', 'dupond.maurice@jmail.com', 'M', TO_DATE('2002-04-17', 'YYYY-MM-DD'), 'Marseille', 3, '01-23-45-67-89');
INSERT INTO Etudiant (ide, nom, prenom, email, sexe, daten, adresse, annee, tel) VALUES (15, 'Durand', 'Helene', 'durand.helene@jmail.com', 'F', TO_DATE('2002-04-18', 'YYYY-MM-DD'), 'Gardanne', 1, '01-23-45-67-89');
INSERT INTO Etudiant (ide, nom, prenom, email, sexe, daten, adresse, annee, tel) VALUES (17, 'Tapioca', 'Lulu', 'tapioca.lulu@jmail.com', 'M', TO_DATE('2000-05-11', 'YYYY-MM-DD'), NULL, 3, '01-23-45-67-89');
INSERT INTO Etudiant (ide, nom, prenom, email, sexe, daten, adresse, annee, tel) VALUES (45, 'Deschamps', 'Jean', 'deschamps.jean@jmail.com', 'M', TO_DATE('2002-04-20', 'YYYY-MM-DD'), 'Aix_en_Pce', 2, '01-23-45-67-89');

INSERT INTO Societe (ids, nom, raisons, activite, adresse) VALUES (8, 'Microware', 'SARL', 8 ,'Aix_en_Pce');
INSERT INTO Societe (ids, nom, raisons, activite, adresse) VALUES (13, 'EdF', 'SEM', 15, 'Marseille');
INSERT INTO Societe (ids, nom, raisons, activite, adresse) VALUES (21, 'TOTAL', 'SA', 9, 'Fos_sur_Mer');
INSERT INTO Societe (ids, nom, raisons, activite, adresse) VALUES (34, 'Almacom', 'SARL', 7, 'Aubagne');

INSERT INTO Personnel (idp, nom, prenom, ids, salaire) VALUES (2, 'Durand', 'Jules', 8, 3100);
INSERT INTO Personnel (idp, nom, prenom, ids, salaire) VALUES (4, 'Dupond', 'Emile', 8, 2420);
INSERT INTO Personnel (idp, nom, prenom, ids, salaire) VALUES (7, 'Toulemonde', 'Rene', 8, 1960);
INSERT INTO Personnel (idp, nom, prenom, ids, salaire) VALUES (12, 'Mirabeau', 'Regine', 13, 1810.5);
INSERT INTO Personnel (idp, nom, prenom, ids, salaire) VALUES (19, 'Petitjean', 'Helene', 21, 1750);
INSERT INTO Personnel (idp, nom, prenom, ids, salaire) VALUES (53, 'Duvent', 'Yves', 34, 3200);
INSERT INTO Personnel (idp, nom, prenom, ids, salaire) VALUES (61, 'Martin', 'Louis', 21, 2670);
INSERT INTO Personnel (idp, nom, prenom, ids, salaire) VALUES (69, 'Dubois', 'Thomas', 8, 2200);

INSERT INTO Convention (ide, ids, idp, datec, date_deb, duree, intitule, note) VALUES (8, 8, 2, TO_DATE('2020-06-10', 'YYYY-MM-DD'), NULL, 3, 'conception d''un logiciel …', 16);
INSERT INTO Convention (ide, ids, idp, datec, date_deb, duree, intitule, note) VALUES (17, 8, 7, TO_DATE('2020-05-8', 'YYYY-MM-DD'), NULL, 4, '… realisation d''un serveur WEB', 14);
INSERT INTO Convention (ide, ids, idp, datec, date_deb, duree, intitule, note) VALUES (15, 13, 12, TO_DATE('2020-04-12', 'YYYY-MM-DD'), TO_DATE('2020-7-01', 'YYYY-MM-DD'), 5, '… logiciel de controle / commande', 11);
INSERT INTO Convention (ide, ids, idp, datec, date_deb, duree, intitule, note) VALUES (17, 34, 53, TO_DATE('2021-03-2', 'YYYY-MM-DD'), NULL, 6, 'logiciel de transfert de fichiers', 14);
INSERT INTO Convention (ide, ids, idp, datec, date_deb, duree, intitule, note) VALUES (8, 34, 53, TO_DATE('2021-03-2', 'YYYY-MM-DD'), TO_DATE('2021-5-15', 'YYYY-MM-DD'), 6, 'prototype de modem externe', 18);
INSERT INTO Convention (ide, ids, idp, datec, date_deb, duree, intitule, note) VALUES (12, 13, 12, TO_DATE('2020-06-2', 'YYYY-MM-DD'), NULL, 6, '… carte d''acquisition', 13);
INSERT INTO Convention (ide, ids, idp, datec, date_deb, duree, intitule, note) VALUES (8, 21, 19, TO_DATE('2020-05-21', 'YYYY-MM-DD'), NULL, 5, 'logiciel de traitement d''images', 17);
INSERT INTO Convention (ide, ids, idp, datec, date_deb, duree, intitule, note) VALUES (15, 8, 4, TO_DATE('2021-06-6', 'YYYY-MM-DD'), NULL, 6, '… driver de carte video', 10);

INSERT INTO Organigramme (employe, responsable) VALUES (2, NULL);
INSERT INTO Organigramme (employe, responsable) VALUES (12, NULL);
INSERT INTO Organigramme (employe, responsable) VALUES (53, NULL);
INSERT INTO Organigramme (employe, responsable) VALUES (61, NULL);
INSERT INTO Organigramme (employe, responsable) VALUES (19, 61);
INSERT INTO Organigramme (employe, responsable) VALUES (4, 2);
INSERT INTO Organigramme (employe, responsable) VALUES (7, 4);
INSERT INTO Organigramme (employe, responsable) VALUES (69, 2);