create view CargoDetails (FlightId, FlightNumber, Origin, Destination, CargoID, CargoType, WeightKg, Height, Width, Length,
    Description) as
    select flight.id, flight.flightnumber, da.code, aa.code,
           baggage.id, 'LUGGAGE',
            baggage.weight, baggage.height, baggage.width, baggage.length,
            'Passenger Luggage'
    from baggage
    join ticket on baggage.ticketid = ticket.id
    join flight on ticket.flightid = flight.id
    join gate dg on dg.id = flight.actualgatedepartureid
    join terminal dt on dt.id = dg.terminalid
    join airport da on da.id = dt.airportid
    join gate ag  on ag.id = flight.actualgatearrivalid
    join terminal at_ on at_.id = ag.terminalid
    join airport aa on aa.id = at_.airportid

    union all

    select flight.id, flight.flightnumber, da.code, aa.code,
           additionalcargo.id, 'ADDITIONAL CARGO',
           additionalcargo.weight, additionalcargo.height, additionalcargo.width,
           additionalcargo.length, additionalcargo.description
    from additionalcargo
    join flight on additionalcargo.flightid = flight.id
    join gate dg on dg.id = flight.actualgatedepartureid
    join terminal dt on dt.id = dg.terminalid
    join airport da on da.id = dt.airportid
    join gate ag  on ag.id = flight.actualgatearrivalid
    join terminal at_ on at_.id = ag.terminalid
    join airport aa on aa.id = at_.airportid;

