Si vuole realizzare un’applicazione di basi di dati relativa ad una compagnia aerea. La compagnia ha un certo numero di dipendenti, distinti in assistenti di volo e piloti. Ogni dipendente ha un numero di codice, un nome, un indirizzo, uno stipendio e un numero di ore di volo. La compagnia ha a disposizione vari modelli di aereo, ognuno caratterizzato da ditta produttrice, velocità, numero di posti. Ogni pilota è abilitato a condurre solo alcuni modelli di aereo. La compagnia fornisce vari voli, ognuno identificato da una sigla, aereoporto di partenza, aereoporto di arrivo, orario di partenza e orario di arrivo. Un passeggero (caratterizzato da nome e indirizzo) prenota un posto di una certa classe e prezzo su un volo che si effettua in una certa data, con un certo aereo (caratterizzato da numero di serie, modello, data di fabbricazione e numero di ore di volo) e con un determinato pilota e alcuni assistenti di volo. Motivando adeguatamente le scelte:

a) Effettuare la progettazione concettuale dello scenario descritto producendo il relativo schema ER.

b) Precisare eventuali vincoli di integrità non rappresentabili nello schema ER proposto e la tipologia delle eventuali generalizzazioni.

c) Generare lo schema ristrutturato corrispondente allo schema ER definito al punto a).

d) Tradurre lo schema concettuale in uno schema logico del modello relazionale evidenziando esplicitamente ogni tipo di vincolo utile a rappresentare lo scenario in esame.

Soluzione punto a

Soluzione punto b

• la ristrutturazione considerata consiste nell’eliminare le entità figlie della gerarchia di generalizzazione totale ed esclusiva dipendente – pilota, assistente:

o l’entità padre conterrà un unico altro attributo, qualifica, mai nullo avente come dominio le stringhe di caratteri lunghe al massimo 10; tale attributo sarà vincolato ad assumere solo uno dei valori dell’insieme {pilota, assistente} (e se ci fossero stati degli attributi nelle entità figlie?)

o le associazioni SU_P e SU_A legano ora le entità DIPENDENTE ed EFFETT_VOLO mantenendo inalterate le cardinalità

o l’associazione ABIL lega ora le entità DIPENDENTE e MODELLO; la cardinalità minima di DIPENDENTE rispetto all’associazione ABIL deve essere modificata da 1 a 0

o devono essere considerati i vincoli seguenti da verificare per ogni istanza di dipendente:

se qualifica=assistente allora dipendente può partecipare all’associazione SU_A e non deve partecipare alle associazioni SU_P e ABIL

se qualifica=pilota allora dipendente può partecipare all’associazione SU_P, deve partecipare all’associazione ABIL mentre non deve partecipare all’associazione SU_A

Soluzione punto c

• per ogni effettuazione di volo, il pilota deve essere abilitato a condurre il modello dell’aereo utilizzato

• per ogni effettuazione di volo, il numero di prenotazioni deve essere minore o uguale al numero di posti del modello dell’aereo utilizzato

• ogni pilota (ed ogni assistente) non può effettuare più voli contemporaneamente (nella stessa data e/o negli stessi orari)

• supposto essere nominativo il biglietto aereo, ogni passeggero non può acquistare biglietti relativi a voli effettuati nella stessa data e/o negli stessi orari

Soluzione punto d DIPENDENTE(NumD,Nome, Indirizzo, Stipendio,OreVolo,Qualifica) MODELLO(NomeM, Prod,Velocita,NumPosti) ABIL(NumD*,NomeM*) NumD fk su DIPENDENTE, NomeM fk su MODELLO VOLO(Sigla,APart,AArr,OPart,OArr) AEREO(NumA,NomeM*,DataFabbr,OreVolo) NomeM fk su MODELLO EFFETT_VOLO(Sigla*,Data,NumA*,NumDP*) Sigla fk su VOLO, NumA fk su AEREO, NumDP fk su DIPENDENTE SU_A(Sigla*,Data*,NumDA*) Sigla,Data fk su EFFETT_VOLO, NumDA fk su DIPENDENTE PASSEGGERO(Nome, Indirizzo) PREN(Sigla*,Data*,Nome*, Indirizzo*, Classe, Prezzo) Sigla,Data fk su EFFETT_VOLO, Nome, Indirizzo fk su PASSEGGERO Altri vincoli:

• un volo deve avere almeno un assistente • un modello deve avere almeno un pilota abilotato a pilotarlo • un pilota deve essere abilitato a pilotare almeno un modello

Create table Dipendente ( NumD Integer NOT NULL, Nome Varchar(20) NOT NULL, Indirizzo Varchar(20) NOT NULL, Stipendio Numeric(7,2) NOT NULL Default 1200, OreVolo Integer NOT NULL,

Qualifica Varchar(10) NOT NULL Constraint qualifica_check Check (Qualifica in {'pilota','assistente'}),

primary key (NumD) ); Create table Modello ( NomeM Varchar(20) NOT NULL, Prod Varchar(20) NOT NULL, Velocità Numeric(15,2) NOT NULL, NumPosti Integer NOT NULL, primary key (NomeM) ); Create table Abil ( NumD Integer NOT NULL, NomeM Varchar(20) NOT NULL, foreign key (NumD) references Dipendente (NumD) on update no action on delete no action, foreign key (NomeM) references Modello (NomeM) on update no action on delete no action, primary key (NumD, NomeM) );