create table membres ( 
mbr_id int not null auto_increment,
mbr_pseudo varchar(20) NOT NULL, 
mbr_motdepasse varchar(40) NOT NULL,
mbr_mail varchar(30),
mbr_phpsessid varchar(40),
mbr_societe_id int NOT NULL,
primary key (mbr_id),
constraint membre_societe foreign key (mbr_societe_id) references societe (societe_id),
constraint pseudo_unique unique (mbr_pseudo)
);



create table news(
news_id int not null auto_increment,
news_id_membre int not null,
news_date date not null,
news_categorie_id int not null,
news_titre varchar(40),
news_contenu text,
primary key(news_id),
constraint news_membre foreign key (news_id_membre) references membres (mbr_id),
constraint news_categorie foreign key (news_categorie_id) references categorie(categorie_id)
);



create table categorie (
categorie_id int not null auto_increment,
categorie_name varchar(40) not null,
primary key (categorie_id)
);


create table staff(
staff_id int not null auto_increment,
staff_pseudo varchar(20) not null,
staff_motdepasse varchar(40) not null,
staff_age int,
staff_mail varchar(40),
staff_role varchar(40),
staff_competence varchar(40),
staff_ville varchar(40),
staff_pays varchar(40),
primary key (staff_id),
constraint pseudo_unique unique(staff_pseudo)
);


create table societe(
societe_id int not null auto_increment,
societe_nom varchar(40) not null,
societe_adresse varchar(100),
societe_description text,
primary key (societe_id)
);


create table arealiser(
areal_staff_id int not null,
areal_real_id int not null,
areal_commentaire text,
primary key (areal_staff_id,areal_real_id),
constraint arealiser_staff foreign key (areal_staff_id) references staff (staff_id),
constraint arealiser_realisation foreign key (areal_real_id) references realisation (real_id) 
);



create table realisation(
real_id int not null auto_increment,
real_societe_id int not null,
real_libelle_real_id int not null,
real_nom varchar(40) not null,
real_url varchar(100),
real_date date not null,
real_importance char default 'N',
real_type_code varchar(100),
real_description text,
primary key (real_id),
constraint realisation_societe foreign key (real_societe_id) references societe (societe_id),
constraint realisation_libelle foreign key (real_libelle_real_id) references libelle_real(libelle_real_id)
);


create table libelle_real(
libelle_real_id int not null auto_increment,
libelle varchar(40) not null,
libelle_commentaire text,
primary key (libelle_real_id)
);