CREATE TABLE Ecole(
  id NUMBER,
  nom VARCHAR2(50),
  adresse VARCHAR2(255),
  PRIMARY KEY(id)
);

CREATE TABLE Soiree (
  id NUMBER,
  nom VARCHAR2(50),
  date1 TIMESTAMP,
  adresse VARCHAR2(255),
  prix FLOAT,
  PRIMARY KEY(id)
);

CREATE TABLE Sponsor (
  id NUMBER,
  nom VARCHAR2(50),
  contact VARCHAR2(50),
  PRIMARY KEY(id)
);

CREATE TABLE Etudiant(
  id NUMBER ,
  Ecole_id NUMBER,
  nom VARCHAR2(50),
  prenom VARCHAR2(50),
  sexe CHAR,
  annee_naissance INT,
  PRIMARY KEY(id),
  CONSTRAINT fk_etudiant_ecole FOREIGN KEY (Ecole_id)REFERENCES Ecole
);

CREATE TABLE Etudiant_par_soiree (
  Soiree_id NUMBER,
  Etudiant_id NUMBER,
  PRIMARY KEY(Soiree_id, Etudiant_id),
  CONSTRAINT fk_etudiant_ecole_1 FOREIGN KEY (Soiree_id)REFERENCES Soiree,
  CONSTRAINT fk_etudiant_ecole_2 FOREIGN KEY (Etudiant_id)REFERENCES Etudiant
);



CREATE TABLE Sponsor_par_soiree (
  Soiree_id NUMBER,
  Sponsor_id NUMBER,
  participation FLOAT,
  PRIMARY KEY(Soiree_id, Sponsor_id),
  CONSTRAINT fk_soiree_sponsor_1 FOREIGN KEY (Soiree_id)REFERENCES Soiree,
  CONSTRAINT fk_soiree_sponsor_2 FOREIGN KEY (Sponsor_id)REFERENCES Sponsor
);


