GestioneMusei

create database Musei;
use Musei;

create table Direttore(
    CodiceDirettore integer primary key auto_increment,
    nomeDirettore varchar(250),
    cognomeDirettore varchar(250),
    dataNascitaDirettore date
);

create table Museo(
    CodiceMuseo integer primary key auto_increment,
    nomeMuseo varchar(250),
    indirizzoMuseo varchar(250),
    CodiceDirettoreRif integer not null,
    foreign key (CodiceDirettoreRif) references Direttore(CodiceDirettore)
);

create table Categoria(
    codiceCategoria integer primary key auto_increment,
    nomeCategoria varchar(250),
    descrizioneCategoria varchar(250)
);

create table AlaMuseo(
    codiceAlaMuseo integer primary key auto_increment,
    nomeAlaMuse varchar(250),
    tipologiaAlaMuseo varchar(250),
    CodiceMuseoRif integer not null,
    foreign key (CodiceMuseoRif) references Museo(CodiceMuseo)
);

create table Soggetto(
    codiceSoggetto integer primary key auto_increment,
    nomeSoggetto varchar(250),
    sessoSoggetto varchar(1)
);

create table OperaArte(
    codiceOperaArte integer primary key auto_increment,
    nomeOperaArte varchar(250),
    tipologiaOperaArte varchar(250),
    autoreOperaArte varchar(250),
    annoCreazioneOperaArte date,
    codiceAlaMuseoRif integer,
    foreign key (codiceAlaMuseoRif) references AlaMuseo(codiceAlaMuseo)
);

create table OperaArte_Ritrae_Soggetto(
    codiceOperaArteRif integer not null,
    codiceSoggettoRif integer not null,
    foreign key (codiceOperaArteRif) references OperaArte(codiceOperaArte),
    foreign key (codiceSoggettoRif) references Soggetto(codiceSoggetto)
);

create table Categoria_Caratterizza_OperaArte(
    codiceOperaArteRif integer not null,
    codiceCategoriaRif integer not null,
    foreign key (codiceOperaArteRif) references OperaArte(codiceOperaArte),
    foreign key (codiceCategoriaRif) references Categoria(codiceCategoria)
);

insert into Direttore (nomeDirettore,cognomeDirettore,dataNascitaDirettore) values
("aaa","aaa","2020-01-25"),
("bbb","bbb","2021-01-15"),
("ccc","ccc","2022-02-16"),
("ddd","ddd","2023-03-5");

insert into Museo (nomeMuseo,indirizzoMuseo,CodiceDirettoreRif) values
("aaa","Via aaa 1",1),
("bbb","Via bbb 1",1),
("ccc","Via ccc 1",2),
("ddd","Via ddd 1",3);

insert into Categoria(nomeCategoria,descrizioneCategoria) values
("aaa","aaa"),
("bbb","bbb"),
("ccc","ccc"),
("ddd","ddd"),
("eee","eee");

insert into AlaMuseo(nomeAlaMuse,tipologiaAlaMuseo,CodiceMuseoRif) values
("aaa","aaa",1),
("bbb","bbb",1),
("ccc","ccc",2),
("ddd","ddd",3),
("eee","eee",4);

insert into Soggetto(nomeSoggetto,sessoSoggetto) values
("aaa","M"),
("bbb","M"),
("ccc","F"),
("ddd","F"),
("eee","M");

insert into OperaArte(nomeOperaArte,tipologiaOperaArte,autoreOperaArte,annoCreazioneOperaArte,codiceAlaMuseoRif) values
("aaa","Scultura","aaa","2020-11-24",1),
("bbb","Scultura","bbb","2021-12-02",1),
("ccc","Dipinto","ccc","2022-06-12",2),
("ddd","Dipinto","ddd","2023-07-13",2),
("eee","Scultura","eee","2024-02-13",null);

insert into OperaArte_Ritrae_Soggetto(codiceOperaArteRif,codiceSoggettoRif) values
(1,1),
(1,2),
(1,3),
(2,1),
(2,3),
(3,4),
(3,1);

insert into Categoria_Caratterizza_OperaArte(codiceOperaArteRif,codiceCategoriaRif) values
(1,2),
(1,3),
(1,1),
(2,2),
(2,3),
(3,1),
(3,3);

select * from OperaArte
    join AlaMuseo on OperaArte.codiceAlaMuseoRif = AlaMuseo.codiceAlaMuseo
    join Museo on AlaMuseo.CodiceMuseoRif = Museo.CodiceMuseo
    where Museo.CodiceMuseo = 1;

select Museo.codiceMuseo,Categoria.nomeCategoria,Categoria.descrizioneCategoria from Categoria
    join Categoria_Caratterizza_OperaArte on Categoria.codiceCategoria = Categoria_Caratterizza_OperaArte.codiceCategoriaRif
    join OperaArte on Categoria_Caratterizza_OperaArte.codiceOperaArteRif = OperaArte.codiceOperaArte
    join AlaMuseo on OperaArte.codiceAlaMuseoRif = AlaMuseo.codiceAlaMuseo
    join Museo on AlaMuseo.CodiceMuseoRif = Museo.CodiceMuseo
    where Museo.CodiceMuseo = 1;

select OperaArte.nomeOperaArte,OperaArte.tipologiaOperaArte from OperaArte
    join OperaArte_Ritrae_Soggetto on OperaArte.codiceOperaArte = OperaArte_Ritrae_Soggetto.codiceOperaArteRif
    join Soggetto on OperaArte_Ritrae_Soggetto.codiceSoggettoRif = Soggetto.codiceSoggetto
    where Soggetto.nomeSoggetto = "aaa";