Critérios de Correção para Consultas SQL e Estruturas de Dados

Critérios gerais para correção de cada uma das 5 consultas 1 A consulta deve estar correta 33 2 As estruturas criadas devem ser necessárias e estarem corretas 33 3 O plano de execução otimizado deve possuir custo e cardinalidades menores do que o plano original 33 1 Listar o nome a idade o país e a cidade de todos os passageiros do sexo feminino sexw com mais de 40 anos residentes no país Brazil SELECT pfirstname plastname AS name TRUNCMONTHSBETWEENSYSDATEpdbirthdate120 AS age pdcity FROM airpassengers p inner join airpassengersdetails pd ON pdpassengerid ppassengerid WHERE pdsex w AND pdbirthdate ADDMONTHSSYSDATE4012 AND country BRAZIL resposta sugerida 143 linhas Custo final 202 Cardinalidade final 143 1 Criar hash cluster incluindo airairports e airairportsgeo 2 Criar PKs e FKs 3 Criar índices nas FKs 4 Criar índice em airpassengersdetailscountry ou Criar índice em airpassengersdetailsbirthdate ou Criar índice em airpassengerdetailsh1country sex birthdate 2 Listar o nome da companhia aérea o identificador da aeronave o nome do tipo de aeronave e o número de todos os voos que saem de aeroportos localizados no país Brazil SELECT alairlinename aplairplaneid aptname fflightno FROM airairlines al inner join airflights f ON fairlineid alairlineid inner join airairplanes apl ON aplairplaneid fairplaneid inner join airairplanetypes apt ON aptairplanetypeid aplairplanetypeid inner join airairportsgeo apgo ON apgoairportid ffromairportid inner join airairportsgeo apgd ON apgdairportid ftoairportid WHERE apgocountry BRAZIL AND apgdcountry BRAZIL resposta sugerida 8 linhas Custo final 65 Cardinalidade final 1 1 Criar hash cluster incluindo airairports e airairportsgeo 2 Criar PKs e FKs 3 Criar índices nas FKs 4 Criar índice em airairportsgeocountry 3 Listar o número do vôo o nome do aeroporto de saída e o nome do aeroporto de destino o nome completo primeiro e último nome e o assento de cada passageiro para todos os vôos que partem em uma determinada data SELECT fflightno fapname AS fromairportname tapname AS toairportname pfirstname plastname AS passengername bseat FROM airflights f inner join airairports fap ON fapairportid ffromairportid inner join airairports tap ON tapairportid ftoairportid inner join airbookings b ON bflightid fflightid inner join airpassengers p ON ppassengerid bpassengerid WHERE fdeparture BETWEEN TRUNCTODATE25032023ddmmyyyy AND TRUNCTODATE26032023ddmmyyyy1246060 resposta sugerida 106 linhas para o dia 25033023 Custo final 233 Cardinalidade final 357 1 Criar hash cluster incluindo airflights e airbookings 2 Criar PKs e FKs 3 Criar índices nas FKs 4 Criar índice em airflightsdeparture 4 Listar o nome da companhia aérea bem como a data e a hora de saída de todos os voos que chegam na cidade de New York às terças quartas ou quintasfeiras daqui a um mês SELECT DISTINCT alairlinename fflightno fdeparture tapgcity FROM airairlines al inner join airflights f ON fairlineid alairlineid inner join airflightsschedules fs ON fsflightno fflightno inner join airairportsgeo tapg ON tapgairportid ftoairportid inner join airbookings b ON bflightid fflightid inner join airpassengers p ON ppassengerid bpassengerid WHERE tapgcity NEW YORK AND fstuesday 1 OR fswednesday 1 OR fsthursday 1 and fdeparture between sysdate and addmonthssysdate1 AND fdeparture BETWEEN TODATE01032023ddmmyyyy AND ADDMONTHSTODATE01042023ddmmyyyy11 resposta sugerida 1 linha para departures no mês de março de 2023 Custo final 237 Cardinalidade final 136 Não há necessidade de criação de hash cluster ou index cluster 1 Criar PKs e FKs 2 Criar índices nas FKs 3 Criar índice em airflightsdeparture 4 Criar índice em airairportsgeocity 5 Criar índice bitmap em airflightsschedulesdias da semana 5 Crie uma consulta que seja resolvida adequadamente com um acesso hash em um cluster com pelo menos duas tabelas A consulta deve utilizar todas as tabelas do cluster e pelo menos outra tabela fora dele Avaliar o cluster e a consulta PRIMARY KEYS ALTER TABLE airairlines ADD CONSTRAINT pkairlineid PRIMARY KEYairlineid ALTER TABLE airairplanetypes ADD CONSTRAINT pkairplanetypeid PRIMARY KEYairplanetypeid ALTER TABLE airairplanes ADD CONSTRAINT pkairplaneid PRIMARY KEYairplaneid ALTER TABLE airairports ADD CONSTRAINT pkairportid PRIMARY KEYairportid ALTER TABLE airairportsgeo ADD CONSTRAINT pkairportsgeo PRIMARY KEYairportid ALTER TABLE airflights ADD CONSTRAINT pkflightid PRIMARY KEYflightid ALTER TABLE airflightsschedules ADD CONSTRAINT pkflightsschedules PRIMARY KEYflightno ALTER TABLE airpassengers ADD CONSTRAINT pkpassengerid PRIMARY KEYpassengerid ALTER TABLE airpassengersdetails ADD CONSTRAINT pkpassengersdetails PRIMARY KEYpassengerid ALTER TABLE airbookings ADD CONSTRAINT pkbookingid PRIMARY KEYbookingid FOREIGN KEYS ALTER TABLE airairplanes ADD CONSTRAINT fkairplanesairplanetypes foreign KEYairplanetypeid references airairplanetypesairplanetypeid ALTER TABLE airairportsgeo ADD CONSTRAINT fkairportsgeo foreign KEYairportid references airairportsairportid ALTER TABLE airflights ADD CONSTRAINT fkflightsschedules foreign KEYflightno references airflightsschedulesflightno ALTER TABLE airflights ADD CONSTRAINT fkflightsairlines foreign KEYairlineid references airairlinesairlineid ALTER TABLE airflights ADD CONSTRAINT fkflightsfromairports foreign KEYfromairportid references airairportsairportid ALTER TABLE airflights ADD CONSTRAINT fkflightstoairports foreign KEYtoairportid references airairportsairportid ALTER TABLE airflights ADD CONSTRAINT fkflightsairplanes foreign KEYairplaneid references airairplanesairplaneid ALTER TABLE airpassengersdetails ADD CONSTRAINT fkpassengersdetails foreign KEYpassengerid references airpassengerspassengerid ALTER TABLE airbookings ADD CONSTRAINT fkflightsbookings foreign KEYflightid references airflightsflightid ALTER TABLE airbookings ADD CONSTRAINT fkbookingspassengers Foreign KEYpassengerid references airpassengerspassengerid ALTERNATE KEYS ALTER TABLE airairlines ADD CONSTRAINT akairairlinesiata UNIQUE iata ALTER TABLE airairports ADD CONSTRAINT akairairportsicao UNIQUE icao ALTER TABLE airpassengers ADD CONSTRAINT akairpassengerspassportno UNIQUE passportno CREATE UNIQUE INDEX akairbookingsflightidseat ON airbookings CASE WHEN seat IS NOT NULL THEN flightid ELSE NULL END CASE WHEN seat IS NOT NULL THEN seat ELSE NULL END BTREE INDEXES NECESSÁRIOS CREATE INDEX idxairportsgeocountry ON airairportsgeocountry CREATE INDEX idxairportsgeocity ON airairportsgeocity CREATE INDEX idxflightsdeparture ON airflightsdeparture CREATE INDEX idxflightsarrival ON airflightsarrival CREATE INDEX idxflightsnumber ON airflightsflightno CREATE INDEX idxpassengersdetailscountry ON airpassengersdetailscountry CREATE INDEX idxpassengersdetailsbirth ON airpassengersdetailsbirthdate CREATE INDEX idxpassengersdetailssex ON airpassengersdetailssex INCORRETOS CREATE INDEX idxflightsschedulestuesday ON airflightsschedulestuesday CREATE INDEX idxflightsscheduleswednes ON airflightsscheduleswednesday CREATE INDEX idxflightsschedulesthursday ON airflightsschedulesthursday OPCIONAIS CREATE INDEX idxairairlinesbaseairportid ON airairlines baseairportid CREATE INDEX idxairairplanesairlineid ON airairplanes airlineid CREATE INDEX idxairairplanesairplanetype ON airairplanes airplanetypeid CREATE INDEX idxairairflightsairlineid ON airflights airlineid CREATE INDEX idxairairflightsairplaneid ON airflights airplaneid CREATE INDEX idxairairflightsfromid ON airflights fromairportid CREATE INDEX idxairairflightstoid ON airflights toairportid CREATE INDEX idxairflightsschedairline ON airflightsschedules airlineid CREATE INDEX idxairflightsschedfrom ON airflightsschedules fromairportid CREATE INDEX idxairflightsschedto ON airflightsschedules toairportid CREATE INDEX idxairflightsschdeparture ON airflightsschedules departure CREATE INDEX idxairflightsscharrival ON airflightsschedules arrival CREATE INDEX idxairbookingspassid ON airbookings passengerid CREATE INDEX idxairbookingsflightid ON airbookings flightid CLUSTERS RECOMENDADOS FLIGHTS CREATE CLUSTER airclflightsflightid numeric10 INDEX storage INITIAL 8192K next 512K CREATE INDEX idxclairflights ON CLUSTER airclflights RENAME airflights TO airflightstemp RENAME airbookings TO airbookingstemp CREATE TABLE airflights flightid numeric10 NOT NULL flightno CHAR8 NOT NULL airlineid numeric5 NOT NULL fromairportid NUMBER5 NOT NULL toairportid NUMBER5 NOT NULL airplaneid numeric6 NOT NULL departure TIMESTAMP NOT NULL arrival TIMESTAMP NOT NULL CLUSTER airclflightsbookingsflightid CREATE TABLE airbookings bookingid numeric 20 NOT NULL passengerid numeric 12 NOT NULL flightid numeric 10 NOT NULL seat CHAR 4 price DECIMAL 102 NOT NULL CLUSTER airclflightsbookingsflightid INSERT INTO airflights SELECT FROM airflightstemp INSERT INTO airbookings SELECT FROM airbookingstemp DROP TABLE airflightstemp CASCADE CONSTRAINTS DROP TABLE airbookingstemp CASCADE CONSTRAINTS PASSENGERS CREATE CLUSTER airclpassengerspassengerid numeric12 INDEX storage INITIAL 8192K avgrowlen135B numrows36095 4758K next 512K CREATE INDEX airidxclpassengers ON CLUSTER airclpassengers RENAME airpassengers TO airpassengerstemp RENAME airpassengersdetails TO airpassengersdetailstemp CREATE TABLE airpassengers passengerid numeric12 NOT NULL passengerno CHAR9 NOT NULL firstname VARCHAR100 NOT NULL lastname VARCHAR100 NOT NULL CLUSTER airclpassengersdetailspassengerid CREATE TABLE airpassengersdetails passengerid numeric12 NOT NULL birthdate DATE NOT NULL sex CHAR1 NOT NULL street VARCHAR100 city VARCHAR100 zip NUMBER5 NOT NULL country VARCHAR50 emailaddress VARCHAR120 telephoneno VARCHAR30 CLUSTER airclpassengersdetailspassengerid INSERT INTO airpassengers SELECT FROM airpassengerstemp INSERT INTO airpassengersdetails SELECT FROM airpassengersdetailstemp DROP TABLE airpassengerstemp CASCADE CONSTRAINTS DROP TABLE airpassengersdetailstemp CASCADE CONSTRAINTS AIRPORTS CREATE CLUSTER airclairportsairportid numeric5 hashkeys 128 initial750K datablock8K 93 storage INITIAL 1024K avgrowlen78B numrows9854 750K next 128K RENAME airairports TO airairportstemp RENAME airairportsgeo TO airairportsgeotemp CREATE TABLE airairports airportid numeric5 NOT NULL iata CHAR3 icao CHAR4 NOT NULL airportname VARCHAR50 NOT NULL CLUSTER airclairportsairportid CREATE TABLE airairportsgeo airportid numeric5 NOT NULL airportname VARCHAR50 NOT NULL city VARCHAR50 country VARCHAR50 NOT NULL latitude DECIMAL118 NOT NULL longitude DECIMAL118 NOT NULL CLUSTER airclairportsgeoairportid INSERT INTO airairports SELECT FROM airairportstemp INSERT INTO airairportsgeo SELECT FROM airairportsgeotemp DROP TABLE airairportstemp CASCADE CONSTRAINTS DROP TABLE airairportsgeotemp CASCADE CONSTRAINTS OPCIONAIS AIRPLANETYPES CREATE CLUSTER airclairplanetypesairplanetypeid numeric3 hashkeys 32 storage INITIAL 128K avgrowlen21 numrows342 avgrowlen17 numrows5583 99k next 32K RENAME airairplanetypes TO airairplanetypestemp RENAME airairplanes TO airairplanestemp CREATE TABLE airairplanetypes airplanetypeid numeric3 NOT NULL airplanename VARCHAR50 NOT NULL CLUSTER airclairplanetypesairplanetypeid CREATE TABLE airairplanes airplaneid numeric6 NOT NULL airlineid numeric5 NOT NULL airplanetypeid numeric3 NOT NULL airplanecapacity numeric3 NOT NULL conferir CLUSTER airclairplanetypesairplanetypeid INSERT INTO airairplanes SELECT FROM airairplanestemp INSERT INTO airairplanetypes SELECT FROM airairplanetypestemp DROP TABLE airairplanestemp CASCADE CONSTRAINTS