Query - Airline Fligts

#Introducció

El camp temàtic d’aquesta base de dades són els vols de companyies aèries a Rússia.

En desenvolupar aquesta base de dades de demostració, vam perseguir diversos objectius:

• L’esquema de la base de dades ha de ser prou senzill com per ser entès sense explicacions addicionals.

• Al mateix temps, l’esquema de la base de dades ha de ser prou complex per permetre escriure consultes significatives.

• La base de dades ha de contenir dades reals amb les quals sigui interessant treballar.

#Instal.lació

Entra dins el contenidor postgres_db_1 i descarrega la base de dades Airline Flights:

$ docker exec -it postgres_db_1 sh
... $ curl https://gitlab.com/xtec/postgres-data/-/raw/main/airline_flights.dump | su postgres -c " pg_restore -C -d postgres"

Conecta’t a la base de dades:

... $ psql -U postgres airline_flights
    ... $ \d
                           List of relations
          Schema  |         Name          |   Type   |  Owner   
        ----------+-----------------------+----------+----------
         bookings | aircrafts             | view     | postgres
         bookings | aircrafts_data        | table    | postgres
        ...

#Esquema

classDiagram
direction LR

class Bookings { 
  # book_ref
  book_date
  total_amount
}
class Tickets { 
  # ticket_no
  passenger_id
  passenger_name
  contact_data
}
Tickets ..> Bookings : book_ref

class Airports {
  # airport_code
  airport_name
  ...
}

class Flights {
  # flight_id
  flight_no
  schedule_departure
  ...
}

Flights ..> Airports: departure_airport
Flights ..> Airports: arrival_airport
Flights ..> Aircrafts: aircraft_code

class Tickets_flights {
  fare_conditions
  amount
}

Tickets_flights --> Tickets : # ticket_no
Tickets_flights --> Flights : # flight_id

class Aircrafts {
  # aircraft_code
  model
  range
}

class Seats {
  # seat_no
  fare_condtions
}

Seats --> Aircrafts:  #aircraft_code

#Schema bookings

L’esquema bookings conté tots els objectes de la base de dades de demostració. Quan us connecteu a la base de dades, el paràmetre de configuració search_path s’estableix automàticament en bookings, public, de manera que no cal que especifiqueu explícitament el nom de l’esquema.

Tanmateix, per a la funció bookings.now, sempre heu d’especificar l’esquema per distingir aquesta funció de la funció estàndard now.

#Translations

Per defecte, els valors de diversos camps traduïbles es mostren en rus. Aquests són airport_name i city de la vista de airports, així com el model de la vista de aircrafts.

Podeu optar per mostrar aquests camps en un altre idioma (tot i que a la base de dades de demostració només es proporciona la traducció a l’anglès).

Per canviar a l’anglès, configureu el paràmetre bookings.lang a en. Pot ser convenient triar l’idioma a nivell de base de dades:

ALTER DATABASE demo SET bookings.lang = en;

Heu de tornar a connectar-vos a la base de dades perquè aquesta ordre tingui efecte.

#Consultes

1.- Tots els vols operats pels diferentes tipus d’avions:

{% sol %}

SELECT * FROM aircrafts;

{% endsol %}

(continua)

Google Docs

#Avaluació

Treballes al MI6 com a tècnic informàtic i ets responsable de gestionar els sistemes d’informació de la zona rusa.

Entre aquest sistemes, tens una base de dades de tots els vols en l’espai aeri rus, i en un dia normal de feina tens que donar resposta a aquesta informació solicitada.

1.- Fes un llistat de tots els seients i tarifes del Cessna.

{% sol %}

SELECT a.aircraft_code, a.model, s.seat_no, s.fare_conditions
FROM aircrafts a NATURAL JOIN seats s
WHERE a.model = 'Cessna 208 Caravan'
ORDER BY s.seat_no;

{% endsol %}

2.- Consulta quin és l’avió (codi i model) amb més capacitat de transport de viatgers que vola en l’espai aeri rus, i que per tant, causaria més baixes en un atac terrorista.

{% sol %}

select a.aircraft_code, a.model, count(*) seats
FROM aircrafts a NATURAL JOIN seats s
group by 1,2
order by seats desc
limit 1

{% endsol %}

3.- Consulta quins són els vols (aeroport de sortida i l’aeroport d’arribada) que fa en l’espai rus l’avió amb menys capacitat de passatgers.

{% sol %}

select a.aircraft_code, f.departure_airport, f.arrival_airport
from aircrafts a natural join flights f
where aircraft_code in (
  select ax.aircraft_code from (
	select a.aircraft_code, count(*) seats
	from aircrafts a NATURAL JOIN seats s
	group by 1
	order by seats
	limit 1
  ) ax
)
group by 1,2,3

{% endsol %}

4.- A Rússia hi ha ciutats que tenen més d’un aeroport. Volen saber quines són aquestes ciutats i quins aeroports hi ha.

{% sol %}

SELECT a.airport_code as code, a.airport_name, a.city, a.coordinates
FROM airports a
WHERE a.city IN (
  SELECT   aa.city
  FROM 	airports aa
  GROUP BY aa.city
  HAVING   COUNT(*) > 1
)
ORDER BY a.city, a.airport_code;

{% endsol %}

5.- Ha arribat una informació de que Antonina Kuznecova és un cas X23. Has de proporcionar tot la informació de desplaçaments (tickets_flights) dels tiquets comprats per Antonina Kuznecova que consten en la base de dades de vols de Rusia, ordenats per la data de sortida programada.

El llistat ha de tenir la data de sortida programada, la ciutat (aeroport) de sortida, la ciutat (aeroport) d’arribada i l’estatus de tots els segments del vol.

{% sol %}

SELECT to_char(f.scheduled_departure, 'DD.MM.YYYY') AS when,
  f.departure_city || ' (' || f.departure_airport || ')' AS departure,
  f.arrival_city || ' (' || f.arrival_airport || ')' AS arrival,
  f.status
FROM ticket_flights tf NATURAL JOIN flights_v f    
WHERE tf.ticket_no IN (
  select ticket_no from tickets where passenger_name = 'ANTONINA KUZNECOVA')
ORDER BY f.scheduled_departure;

{% endsol %}

6.- La informació anterior està molt bé, però ara també volen saber el seient de l’avió que consta en la tarjeta d’embarcament de cada segment de vol.

{% sol %}

SELECT to_char(f.scheduled_departure, 'DD.MM.YYYY') AS when,
  f.departure_city || ' (' || f.departure_airport || ')' AS departure,
  f.arrival_city || ' (' || f.arrival_airport || ')' AS arrival,
  f.status, bp.seat_no
FROM ticket_flights tf NATURAL JOIN flights_v f LEFT JOIN boarding_passes bp ON tf.flight_id = bp.flight_id AND tf.ticket_no = bp.ticket_no
WHERE tf.ticket_no IN (
  select ticket_no from tickets where passenger_name = 'ANTONINA KUZNECOVA')
ORDER BY f.scheduled_departure;

{% endsol %}