edutecnica



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

 

 







edutecnica