Modellazione ER e SQL:
Gestione di cinema multisale

Il seguente esercizio è dato come soluzione alla traccia: TASK – Modelazione ER e MySQL 08/07/2022 – Percorso di Data Engineer

La soluzione è stata sviluppata e gentilmente resa disponibile per tutti gli studenti da Marco Rabino.

 

TRACCIA:

Si rappresenta una base dati contente le informazioni relative alla gestione di un’azienda di multisale cinematografiche che vuole gestire le prenotazioni attraverso internet. Le multisale hanno un nome, un indirizzo, una città un telefono.

Presso ogni multisala sono disponibili più sale, ogni sala ha un certo numero di posti (contrassegnati dalla fila e il numero) e un Film in proiezione. Gli utenti registrati una volta scelto il  film possono acquistare uno o più biglietti. Per i film si memorizza il titolo, il regista, gli attori, il personaggio interpretato da ogni attore. Per ogni film è disponibile anche una trama. Gli utenti a fine proiezione sono abilitati a rilasciare un feedback sul film visto, tramite un giudizio  sintetico e un voto numerico da 1 a 10.

 

IL DIAGRAMMA ER:

Il seguente diagramma è consultabile o modificabile direttamente dal suo file sorgente con il programma draw.io scaricabile dall’indirizzo https://www.diagrams.net/ o utilizzabile direttamente sul sito.

SCARICA IL DIAGRAMMA: CLICCA QUI

 

 

DDL – Data Definition Language

 

drop database if exists MultisaleDB;
create database MultisaleDB;
use MultisaleDB;

create table Multisala (
	CodiceMultisala char (5) primary key,
	Nome char(50),
	Citta char(30)NOT NULL,
	Indirizzo varchar(50)NOT NULL,
	Telefono varchar(10) unique
);

create table Film (
	idFilm varchar(10) primary key,
	Titolo varchar(50) NOT NULL,
	Regista varchar(50),
	Trama text
);

create table Sala ( 
	CodiceSala char(3), 
	NumeroPosti int NOT NULL,
	CodiceMultisala char(5), 
	Film varchar(10),
	primary key (CodiceSala),
	foreign key (CodiceMultisala) references Multisala(CodiceMultisala) ON DELETE CASCADE, 
	foreign key (Film) references Film(idFilm) ON DELETE CASCADE
);

create table Posto ( 
	idPosto integer auto_increment primary key,
	nFila char(1),
	nNumero char(3),
	CodiceMultisala char(5), 
	CodiceSala char(3), 
	foreign key (CodiceMultisala) references Sala(CodiceMultisala) ON DELETE CASCADE,
	foreign key (CodiceSala) references Sala(CodiceSala) ON DELETE CASCADE
);

create table Attore (
	CF varchar(16) primary key, 
	Nome varchar(25) NOT NULL,
	Cognome varchar(25) NOT NULL
);

create table Recita ( 
	Film varchar(10), 
	Attore varchar(16),
	Personaggio varchar(30) NOT NULL, 
	primary key(Film, Attore),
	foreign key (Film) references Film(idFilm) ON DELETE CASCADE, 
	foreign key (Attore) references Attore(CF) ON DELETE CASCADE
);

create table Feedback (
	idFeed integer auto_increment primary key, 
	Giudizio varchar(100) NULL, 
	Voto smallint NOT NULL,
	Film varchar(10) references Film(idFilm) ON DELETE CASCADE
);

create table Utente(
	CF varchar(16) PRIMARY KEY,
	Username varchar(12) UNIQUE NOT NULL , 
	Nome varchar(25) NOT NULL,
	Cognome varchar(25) NOT NULL, 
	Passw varchar(64) NOT NULL, 
	Email varchar(50) UNIQUE NOT NULL
);

create table Biglietto ( 
	idPosto integer not null, 
	Utente varchar(16), 
	Feed integer, 
	Film varchar(10),
	idBig integer auto_increment primary key,
	Prezzo float NOT NULL, 
	foreign key (idPosto) references Posto(idPosto) ON DELETE CASCADE,
	foreign key (Utente) references Utente(CF) ON DELETE CASCADE, 
	foreign key (Feed) references Feedback(idFeed) ON DELETE CASCADE, 
	foreign key (Film) references Film(idFilm) ON DELETE CASCADE
);

 

DML – Data Manipilation Language

 

insert into Multisala (CodiceMultisala, Nome, Citta, Indirizzo, Telefono) values
("A001","TheSpace1","Torino","Via Rossi 1","3334445555"),
("A002","TheSpace2","Roma","Via Neri 1","7774445555"),
("A003","TheSpace3","Milano","Via Verdi 1","9994445555");

insert into Film (idFilm,Titolo,Regista,Trama) VALUES
("AAA","Miao","Scorzese","C'era una volta una trama"),
("ABB","Miao2","Tarantino","C'era una volta una trama"),
("AAB","Miao3","De niro","C'era una volta una trama");

insert into Sala(CodiceSala,NumeroPosti,CodiceMultisala,Film) VALUES
("A11","89","A001","AAA"),
("B34","110","A001","ABB"),
("C78","343","A003","AAB");

insert into Posto (nFila, nNumero, CodiceMultisala,CodiceSala) values 
(4,6,"A001","A11"),
(1,5,"A001","B34"),
(4,7,"A003","B34");

insert into Attore (CF,Nome,Cognome) Values 
("MMMMMM00M00M000M","Giovanni","Pace"),
("NNMMMM00M00M000M","Mario","Rossi"),
("RRMMMM00M00M000M","Luigi","Neri");

insert into Feedback (Giudizio, Voto,Film) values 
("Molto bello",8,"AAA"),
("Molto bello",9,"AAB"),
("Molto brutto",5,"ABB");

Insert into Utente (CF, Username ,Nome,Cognome,Passw,Email) values 
("AMMMMM00M00M000M","user1","Giovanni","Pace","password","email@email.it"),
("BMMMMM00M00M000M","user2","Mario","Rossi","password","1@email.it"),
("CMMMMM00M00M000M","user3","Luigi","Pollice","password","2@email.it");

insert into Biglietto (idPosto,Utente,Feed,Film,Prezzo) VALUES 
(2,"AMMMMM00M00M000M",2,"AAA",15);

 

QL – Query Language

 

select Posto.CodiceMultisala,Posto.CodiceSala,Sala.Film, Sala.NumeroPosti, nFila,nNumero from Posto
	join Sala on Posto.Codicesala = Sala.Codicesala;

select Utente.Nome, Utente.Cognome, Utente.Email ,Posto.CodiceMultisala, Posto.CodiceSala, Posto.nFila, Posto.nNumero, Film.Titolo, Prezzo 
	from Biglietto
	join Utente on Biglietto.Utente = Utente.CF
    join Posto on Biglietto.idPosto = Posto.idPosto
    join Film on Biglietto.Film = Film.idFilm;

 

COMMENTI SULLA SOLUZIONE:

 

1. A livello di diagramma ci sono alcune relazioni che andrebbero dichiarate come opzionali per evitare la dipendenza ciclica all’interno del DataBase, inoltre bisogna prestare molta importanza in questa situazione anche alle Foreign Key poichè nei casi di interdipendenza si rischia di non poter agire in operazioni di eliminazione.