SCHEMA LOGICO DEL DB Utilissimo

(1)

SCHEMA LOGICO DEL DB

Utilissimo https://www.w3schools.com/sql/default.asp

PASSEGGERI (IDCARD(PK), nome, cognome, email)

PRENOTAZIONI (ID_prenotazioni(PK), fbA, fbP, cod_idcard(FK),id_viaggio(FK)) VIAGGI (ID_viaggio(PK),partenza, arrivo, gPartenza, cod_patente(FK))

AUTISTI (patente(PK), nome, cognome, email) Tipi di dato

PASSEGGERI

IDCARD char(20) not null PK nome char(20)

cognome char(20) email char(50) PRENOTAZIONI

ID_prenotazione int autoincrementale not null PK fbA int(1)

fbP int(1)

id_viaggio int FK

cod_idcard char(20) FK VIAGGI

ID_viaggio int autoincrementale not null PK partenza char(20)

arrivo char(20) gPartenza data

contributo decimale con 3 interi e 2 decimali cod_patente int FK

AUTISTI

patente int not null PK nome char(20)

cognome char(20) email char(50)

CREAZIONE TABELLE CREATE TABLE Autisti

(

patente INT NOT NULL PRIMARY KEY, nome CHAR(20),

cognome CHAR(20), email CHAR(50)

)

CREATE TABLE Passeggeri

(2)

(

IDCARD char(20) NOT NULL PRIMARY KEY, nome CHAR(20),

cognome CHAR(20), email CHAR(50)

)

CREATE TABLE Viaggi (

ID_viaggio INT AUTO_INCREMENT NOT NULL PRIMARY KEY, partenza CHAR(20),

arrivo CHAR(20), gPartenza DATE,

contributo DECIMAL(3,2), cod_patente INT NOT NULL,

FOREIGN KEY (cod_patente) REFERENCES Autisti (patente) )

CREATE TABLE Prenotazioni (

ID_prenotazione INT AUTO_INCREMENT NOT NULL PRIMARY KEY, fA INT(1),

fP INT(1),

id_viaggio INT NOT NULL,

FOREIGN KEY (id_viaggio) REFERENCES Viaggi (ID_viaggio), cod_idcard INT NOT NULL,

FOREIGN KEY (cod_idcard) REFERENCES Passeggeri (IDCARD) )

INSERIMENTO SET DI DATI PER I VARI TEST

INSERT INTO `Autisti`(`patente`, `nome`, `cognome`, `email`) VALUES (123,'Sara','Dottorini','fr@ghfj.it')

INSERT INTO `Autisti`(`patente`, `nome`, `cognome`, `email`) VALUES (567,'Luigi','Terni','ijg@ghfj.it')

INSERT INTO `Passeggeri`(`IDCARD`, `nome`, `cognome`, `email`) VALUES ('AC456','Marco','Verdi','sdh@ki.it')

INSERT INTO `Viaggi`(`partenza`, `arrivo`, `gPartenza`, `contributo`, `cod_patente`) VALUES ('Brescia','Salò','2021-12-30','5.5','123')

INSERT INTO `Prenotazioni`(`id_viaggio`, `cod_idcard`) VALUES ('123','AC456') SELEZIONE

//prende un insieme di tuple secondo le richieste del programmatore SELECT * FROM `Viaggi`

(3)

SELECT * FROM `Viaggi` WHERE `partenza`="Brescia"

SELECT * FROM `Viaggi` WHERE `partenza`="Brescia" AND `cod_patente`=122 SELECT `ID_prenotazione`,`Cognome` FROM `Prenotazioni`,`Passeggeri` WHERE

`cod_idcard`=`idcard`

SELECT `ID_prenotazione`,`Cognome` FROM `Prenotazioni`,`Passeggeri` WHERE

`cognome`="bianchi"

`nome`="Marco"

SELECT * FROM `Prenotazioni`,`Passeggeri` WHERE `nome`="Marco" OR

`cognome`="Rossi"

PROIEZIONE

//prende solo alcune colonne di una o più tabelle

`nome`="Marco" OR `cognome`="Rossi"

PROVARE CONDIZIONI CON OPERATORI VARI //operatore di similarità

SELECT * FROM `Autisti` WHERE `nome` LIKE "S%"

SELECT * FROM `Autisti` WHERE `nome` LIKE "S%" AND `email` LIKE "%@"

SELECT * FROM `Autisti` WHERE `nome` LIKE "S%" AND `email` LIKE "%@%"

SELECT * FROM `Autisti` WHERE `nome` LIKE "S%" OR `email` LIKE "%@"

//operatore di intervallo BETWEEN

SELECT * FROM `Viaggi` WHERE `contributo` BETWEEN 1 AND 10 //rinominare i risultati

SELECT `ID_viaggio` AS "codice" FROM `Viaggi` WHERE `contributo` BETWEEN 1 AND 10

PROVARE OPERATORI

SELECT DISTINCT `partenza` FROM `Viaggi`

Rispetto a

SELECT `partenza` FROM `Viaggi`

SELECT MIN(`contributo`) FROM Viaggi SELECT MAX(`contributo`) FROM Viaggi

(4)

SELECT COUNT(DISTINCT `partenza`) FROM Viaggi Rispetto a

SELECT COUNT(`partenza`) FROM Viaggi

SELECT `contributo`,(`contributo`*2) AS doppio FROM Viaggi Come questi SUM, AVG

Inoltre possiamo usare * + - tra i valori degli attributi SELECT `nome`

FROM Passeggeri

WHERE `cognome` IN ("Bianchi", "Rossi");

SELECT `cognome`

FROM Passeggeri

WHERE `nome` IS NULL;

JOIN

//unisce due tabella sulla base della corrispondenza tra un attributo comune //Estrarre gli abbinamenti tra autisti e passeggeri di uno stesso viaggio

//1. Prendo i cognomi degli autisti già coinvolti nel viaggio e i viaggi dove sono coinvolti

//fornendo un nome al risultato per poi poterlo usare

(SELECT Autisti.cognome AS ac,Viaggio.id_viaggio AS v1 FROM Viaggi INNER JOIN Autisti ON Viaggi.cod_patente = Autisti.patente)

//2. Prendo i cognomi dei passeggeri che hanno già prenotazione e i viaggi dove sono coinvolti

//fornendo un nome al risultato per poi poterlo usare

(SELECT Passeggeri.cognome AS pc, Prenotazioni,id_viaggio AS v FROM Passeggeri INNER JOIN Prenotazioni ON Passeggeri.idcard =

Prenotazioni.cod_idcard)

//voglio abbinare i cognomi degli autisti con i cognomi dei loro passeggeri nel relativo codice viaggio

SELECT v1,ac,pc FROM

//unisco le selezione degli autisti associati al viaggio

(SELECT Autisti.cognome AS ac,Autisti.patente AS ap, Viaggi.id_viaggio AS v1 FROM Viaggi INNER JOIN Autisti ON Viaggi.cod_patente = Autisti.patente) AS AsV INNER JOIN

//con i passeggeri associati ai viaggi per i quali si sono prenotati

(SELECT Passeggeri.cognome AS pc, Prenotazioni.id_viaggio AS v FROM Passeggeri INNER JOIN Prenotazioni ON Passeggeri.idcard =

Prenotazioni.cod_idcard) AS PvP ON

//sulla base dei viaggi inseriti e anche prenotati PvP.v = AsV.v1

(5)