bdd

drop database eSLIDES;
Create database eSLIDES;

use eSLIDES;


create table codeBaps ( 
id int auto_increment, 
libelleCodeBaps varchar(256) not null unique, 
primary key (id)
) 
ENGINE=InnoDB;

create table codeRayhane (
id int auto_increment,
codeRayhane varchar(256) not null unique,
primary key (id)
)
ENGINE = INNODB;

Create table stockerPageDeGarde (
id int auto_increment, 
dateInjection timestamp not null default current_timestamp,
derniereModification timestamp not null default current_timestamp on update current_timestamp, 
emplacement varchar(256) not null, 
libelle varchar(256) not null unique, 
primary key(id)
) 
ENGINE = InnoDb;


Create table supportCoursGen ( 
id int auto_increment, 
dateGeneration timestamp not null default current_timestamp,
derniereModification timestamp not null default current_timestamp on update current_timestamp,  
emplacement varchar(256) not null unique,  
supportCoursSource varchar(256), 
codeBaps varchar(256),
codeRayhane varchar(256),
primary key (id)
)
ENGINE=InnoDB;

create table supportCoursSource (
id int auto_increment, 
dateInjection timestamp not null default current_timestamp,
derniereModification timestamp not null default current_timestamp on update current_timestamp,  
emplacement varchar(256) not null unique,
codeBaps varchar(256),
codeRayhane varchar(256),  
supportCoursGen varchar(256), 
primary key (id) 
) 
ENGINE=InnoDB;

create table slide ( 
id int auto_increment, 
dateInjection timestamp not null default current_timestamp,
dateDerniereModification timestamp not null default current_timestamp on update current_timestamp,
emplacementFichier varchar(256) not null unique, 
codeBaps varchar(256),
codeRayhane varchar(256),
emplacementCsv varchar(256), 
emplacementThumbnails varchar(256) unique,
primary key (id)
)
ENGINE=InnoDB;

create table csv (
id int auto_increment, 
dateInjection timestamp not null default current_timestamp,
emplacement varchar(256) not null unique,
primary key (id)
)
ENGINE = InnoDB;

create table thumbnails (
id int auto_increment,
dateInjection timestamp not null default current_timestamp,
emplacement varchar(256) not null unique,
primary key (id)
)
ENGINE = InnoDB; 



ALTER TABLE supportCoursGen 
ADD CONSTRAINT fk_supportCoursGen_supCoursSource FOREIGN KEY (supportCoursSource)
REFERENCES supportCoursSource(emplacement);

ALTER TABLE supportCoursGen
ADD CONSTRAINT fk_supportCoursGen_codeBaps FOREIGN KEY (codeBaps) REFERENCES codeBaps (libelleCodeBaps); 

ALTER TABLE supportCoursGen
ADD CONSTRAINT fk_supportCoursGen_codeRayhane FOREIGN KEY (codeRayhane) REFERENCES codeRayhane (codeRayhane); 



ALTER TABLE supportCoursSource 
ADD CONSTRAINT fk_supportCoursSource_supCoursGen FOREIGN KEY (supportCoursGen)
REFERENCES supportCoursGen(emplacement);

ALTER TABLE supportCoursSource
ADD CONSTRAINT fk_supportCoursSource_codeBaps FOREIGN KEY (codeBaps) REFERENCES codeBaps (libelleCodeBaps); 

ALTER TABLE supportCoursSource
ADD CONSTRAINT fk_supportCoursSource_codeRayhane FOREIGN KEY (codeRayhane) REFERENCES codeRayhane (codeRayhane); 



alter table slide
add constraint fk_slide_codeBaps foreign key (codeBaps) references codeBaps(libelleCodeBaps);

alter table slide
add constraint fk_slide_codeRayhane foreign key (codeRayhane) references codeRayhane(codeRayhane);

alter table slide 
add constraint fk_slide_emplacementCsv foreign key (emplacementCsv) references csv(emplacement);

alter table slide 
add constraint fk_slide_emplacementThumbnails foreign key (emplacementThumbnails) references thumbnails(emplacement);