Esercizio no.9:soluzione

ad ogni trasporto corrisponde un cliente. un cliente può ordinare più trasporti

ad ogni trasporto corrisponde un solo automezzo un automezzo può effettuare più trasporti

Sembra sicura una prima bozza del diagramma; rimane da includere l'entità autisti nello schema precedente.

Come si nota dalla figura la relazione che intercorre fra le entità mezzi e l'entità autisti è una relazione molti-molti, la relazione guida può essere interpretata e sostituita dall'entità trasporto.

questa scelta ci consente di definire in maniera univoca quale autista guidando un dato mezzo ha eseguito un certo trasporto

Per comodità, poniamo:
T=Trasporti
M=Mezzi
C=Clienti
A=Autisti
ecco come si dovrebbe presentare il diagramma concettuale finale

si può impostare il seguente tracciato dei campi:

C
	Nome campo	Tipo di dati	parametri
∇	IdC	contatore	Chiave primaria
	rs	testo

M
	Nome campo	Tipo di dati	parametri
∇	idM	contatore	Chiave primaria
	tipo	testo
	maxpeso	numerico	Intero

A
	Nome campo	Tipo di dati	Parametri
∇	idA	contatore	Chiave primaria
	Cognome	testo

T
	Nome campo	Tipo di dati	Parametri
∇	idT	contatore	Chiave primaria
	xida	numerico	Intero lungo
	xidm	numerico	Intero lungo
	xidc	numerico	Intero lungo
	partenza	testo
	arrivo	testo
	peso	numerico	Intero
	data	data/ora	data in cifre

ottenendo il seguente diagramma logico

Queste sono le query di creazione e il contenuto delle tabelle

CREATE TABLE C(
idC COUNTER PRIMARY KEY,
rs char(20)
);

idC	rs
1	BMW
2	AUDI
3	FIAT
4	SEAT
5	DAF
6	NISSAN
7	VOLVO

CREATE TABLE M(
idM COUNTER PRIMARY KEY,
tipo CHAR(20),
maxpeso INTEGER );

idM	tipo	maxpeso
1	furgone	2000
2	auto	300
3	camion	5000
4	autotreno	10000
5	monovolume	1000

CREATE TABLE A(
idA COUNTER PRIMARY KEY,
cognome CHAR(20)
);

idA	cognome
1	Bianchi
2	Rossi
3	Verdi
4	Viola
5	Nero

Infine

CREATE TABLE T(
idT COUNTER PRIMARY KEY,
partenza CHAR(20),
arrivo CHAR(20),
peso INTEGER,
data DATE,
xidC INTEGER REFERENCES C(idC),
xidM INTEGER REFERENCES M(idM),
xidA INTEGER REFERENCES A(idA)
);

idT	partenza	arrivo	peso	data	xidC	xidM	xidA
1	milano	varese	1500	09/03/2015	2	1	4
2	milano	como	9000	05/09/2015	7	4	1
3	novara	pavia	200	07/07/2015	6	2	2
4	pavia	milano	3500	05/08/2015	5	3	5
5	como	bergamo	900	07/04/2015	2	5	4
6	varese	milano	8000	07/01/2015	1	4	3
7	como	milano	4500	12/12/2015	4	3	5
8	bergamo	milano	7000	15/11/2015	7	4	2
9	milano	bergamo	200	27/08/2015	3	2	1
10	pavia	como	100	01/09/2015	6	2	4
11	varese	novara	3500	02/03/2015	2	3	3
12	milano	novara	6000	04/06/2015	1	5	5

q1:Elencare tutti i trasporti eseguiti partendo da una specifica località (nel nostro caso, Milano)

SELECT T.idT, T.partenza, T.arrivo,
T.data
FROM T
WHERE T.partenza='milano';

idT	partenza	arrivo	data
1	milano	varese	09/03/2015
2	milano	como	05/09/2015
9	milano	bergamo	27/08/2015
12	milano	novara	04/06/2015

q2:Elencare i trasporti, i clienti e gli autisti effettuati fra due specifiche date (nel nostro caso, fra il 1/3/15 e il 1/6/15 )

SELECT T.idT, C.rs, A.cognome
FROM T, C, A
WHERE A.idA=T.xidA AND
C.idC=T.xidC AND
T.data<#6/1/15# AND T.data>=#3/1/15#;

idT	rs	cognome
1	AUDI	Viola
5	AUDI	Viola
11	AUDI	Verdi

q3:Elencare tutti gli autisti che abbiano effettuato più di 2 trasporti

SELECT A.cognome, COUNT(*) AS Viaggi
FROM T, A
WHERE A.idA=T.xidA
GROUP BY A.cognome
HAVING COUNT(*)>2;

cognome	Viaggi
Nero	3
Viola	3

q4:Elencare tutti i clienti con la relativa somma in peso della merce consegnata

SELECT C.rs, SUM (T.peso) AS peso
FROM C, T
WHERE C.idC=T.xidC
GROUP BY C.rs;

rs	peso
AUDI	5900
BMW	14000
DAF	3500
FIAT	200
NISSAN	300
SEAT	4500
VOLVO	16000

q5:Calcolare la somma in peso dei trasporti effettuati per BMW

SELECT SUM (T.peso) AS peso
FROM C, T
WHERE C.idC=T.xidC AND
C.rs='BMW';

peso

14000

q6:Ricavare il tipo di mezzo usato più di frequente

SELECT TOP 1 M.tipo, COUNT (*)
FROM M, T
WHERE M.idM=T.xidM
GROUP BY M.tipo
ORDER BY COUNT(*) DESC;

tipo	Expr1001
camion	3
autotreno	3
auto	3