We have to make a java program.

airport.txt:

ABR,Aberdeen Regional Airport,Aberdeen,SD ABI,Abilene Regional Airport,Abilene,TX ADK,Adak Airport,Adak Island,AK GUM,Guam International Airport,Agana,GU BQN,Aguadilla Airport,Aguadilla,PR AUK,Alakanuk Airport,Alakanuk,AK ALM,Alamogordo-White Sands Regional Airport,Alamogordo,NM ALS,San Luis Valley Regional Airport,Alamosa,CO ABY,Southwest Georgia Regional Airport,Albany,GA ALB,Albany International Airport,Albany,NY ABQ,Albuquerque International Sunport,Albuquerque,NM AEX,Alexandria International Airport,Alexandria,LA AXN,Chandler Field Airport,Alexandria,MN ESF,Esler Regional Airport,Alexandria,LA ABE,Lehigh Valley International Airport,Allentown,PA AIA,Alliance Municipal Airport,Alliance,NE APN,Alpena County Regional Airport,Alpena,MI ALN,St Louis Regional Airport,Alton-St Louis,IL AOO,Altoona-Blair County Airport,Altoona,PA AMA,Amarillo International Airport,Amarillo,TX AFM,Ambler Airport,Ambler,AK 74S,Anacortes Airport,Anacortes,WA AKP,Anaktuvuk Pass Airport,Anaktuvuk Pass,AK ANC,Anchorage International Airport,Anchorage,AK LHD,Lake Hood Airport,Anchorage,AK COD,Yellowstone Regional Airport,Cody,WY COE,Coeur Dalene Airport,Coeur Dalene,ID CDB,Cold Bay Airport,Cold Bay,AK CLL,Easterwood Airport,College Station,TX COS,Colorado Springs Airport,Colorado Springs,CO CAE,Columbia Metropolitan Airport,Columbia,SC COU,Columbia Regional Airport,Columbia,MO BAK,Columbus Municipal Airport,Columbus,IN CMH,Port Columbus International Airport,Columbus,OH CSG,Columbus Metropolitan Airport,Columbus,GA GTR,Golden Triangle Regional Airport,Columbus,MS CVO,Corvallis Municipal Airport,Corvallis,OR CGA,Craig Airport,Craig,AK CEC,Jack Mc Namara Field Airport,Crescent City,CA DAL,Dallas Love Field,Dallas,TX DFW,DallasFort Worth International Airport,Dallas,TX DXR,Danbury Municipal Airport,Danbury,CT DAN,Danville Regional Airport,Danville,VA DNV,Vermilion County Airport,Danville,IL DAY,Dayton International Airport,Dayton,OH DAB,Daytona Beach International Airport,Daytona Beach,FL SCC,Deadhorse Airport,Deadhorse,AK DEC,Decatur Airport,Decatur,IL DRT,Del Rio International Airport,Del Rio,TX BIG,Allen Aaf Airport,Delta Junction Ft Greely,AK DEN,Denver International Airport,Denver,CO DSM,Des Moines International Airport,Des Moines,IA DET,Detroit City Airport,Detroit,MI DTW,Detroit Metro Airport,Detroit,MI DVL,Devils Lake Municipal Airport,Devils Lake,ND DIK,Dickinson - Theodore Roosevelt Regional Airport,Dickinson,ND DLG,Dillingham Airport,Dillingham,AK DDC,Dodge City Regional Airport,Dodge City,KS DHN,Dothan Regional Airport,Dothan,AL DUJ,Du Bois-Jefferson County Airport,Du Bois,PA DBQ,Dubuque Regional Airport,Dubuque,IA DLH,Duluth International Airport,Duluth,MN DRO,Durango-La Plata County Airport,Durango,CO EGE,Eagle County Regional Airport,Eagle,CO ORS,Orcas Island Airport,Eastsound,WA EAU,Chippewa Valley Regional Airport,Eau Claire,WI EEK,Eek Airport,Eek,AK ACY,Atlantic City International Airport,Egg Harbor Township,NJ ELP,El Paso International Airport,El Paso,TX FRM,Fairmont Municipal Airport,Fairmont,MN YNG,Youngstown Regional Airport,Youngstown,OH YUM,Yuma International Airport,Yuma,AZ FCA,Glacier Park International Airport,Kalispell,MT CLD,McClellanPalomar Airport,Carlsbad,CA

delayedFlights.txt:

0,1,3,4,2003,1955,2211,2225,WN,335,128,150,116,-14,8,TLH,TPA,810 1,1,3,4,754,735,1002,1000,WN,3231,128,145,113,2,19,TIX,TPA,810 2,1,3,4,628,620,804,750,WN,448,96,90,76,14,8,IND,BWI,515 4,1,3,4,1829,1755,1959,1925,WN,3920,90,90,77,34,34,IND,BWI,515 5,1,3,4,1940,1915,2121,2110,WN,378,101,115,87,11,25,IND,JAX,688 6,1,3,4,1937,1830,2037,1940,WN,509,240,250,230,57,67,IND,LAS,1591 10,1,3,4,706,700,916,915,WN,100,130,135,106,1,6,IND,MCO,828 11,1,3,4,1644,1510,1845,1725,WN,1333,121,135,107,80,94,IND,MCO,828 15,1,3,4,1029,1020,1021,1010,WN,2272,52,50,37,11,9,IND,MDW,162 16,1,3,4,1452,1425,1640,1625,WN,675,228,240,213,15,27,AST,PHX,1489 17,1,3,4,754,745,940,955,WN,1144,226,250,205,-15,9,SOW,PHX,1489 18,1,3,4,1323,1255,1526,1510,WN,4,123,135,110,16,28,IND,BWI,838 19,1,3,4,1416,1325,1512,1435,WN,54,56,70,49,37,51,ISP,BWI,220 21,1,3,4,1657,1625,1754,1735,WN,623,57,70,47,19,32,ISP,BWI,220 22,1,3,4,1900,1840,1956,1950,WN,717,56,70,49,6,20,ISP,ITH,220 23,1,3,4,1039,1030,1133,1140,WN,1244,54,70,47,-7,9,ISP,ITH,220 25,1,3,4,1520,1455,1619,1605,WN,2553,59,70,50,14,25,ISP,BWI,220 26,1,3,4,1422,1255,1657,1610,WN,188,155,195,143,47,87,ISP,MSS,1093 27,1,3,4,1954,1925,2239,2235,WN,1754,165,190,155,4,29,ISP,PLB,1093 30,1,3,4,2107,1945,2334,2230,WN,362,147,165,134,64,82,ISP,MCO,972 33,1,3,4,1312,1300,1546,1550,WN,1397,154,170,140,-4,12,ISP,MCO,972 34,1,3,4,1449,1430,1715,1720,WN,3398,146,170,134,-5,19,ISP,MCO,972 35,1,3,4,1634,1555,1859,1845,WN,3480,145,170,134,14,39,ISP,MCO,972 37,1,3,4,1812,1650,1927,1815,WN,422,135,145,118,72,82,ISP,MDW,765 38,1,3,4,1127,1105,1235,1230,WN,1837,128,145,114,5,22,ISP,MDW,765 39,1,3,4,1424,1355,1531,1520,WN,2871,127,145,113,11,29,ISP,MDW,765 40,1,3,4,1326,1230,1559,1530,WN,1056,153,180,143,29,56,ISP,PBI,1052 41,1,3,4,1749,1725,2019,2030,WN,2175,150,185,138,-11,24,ISP,PBI,1052 42,1,3,4,726,720,958,1020,WN,3319,152,180,140,-22,6,ISP,PBI,1052 43,1,3,4,646,640,929,955,WN,3667,163,195,151,-26,6,ISP,RSW,1101 44,1,3,4,1153,1140,1428,1440,WN,2006,155,180,143,-12,13,ISP,TPA,1034 45,1,3,4,1528,1510,1802,1810,WN,3858,154,180,144,-8,18,ISP,TPA,1034 48,1,3,4,1450,1435,1806,1745,WN,3244,136,130,121,21,15,JAN,BWI,888 49,1,3,4,2245,1730,2354,1850,WN,186,69,80,59,304,315,JAN,HOU,359 52,1,3,4,2025,1940,2135,2100,WN,3154,70,80,60,35,45,JAN,HOU,359 53,1,3,4,1038,945,1314,1225,WN,1035,96,100,81,49,53,JAN,MCO,587 54,1,3,4,1900,1850,2123,2045,WN,205,143,115,97,38,10,JAN,MDW,666 56,1,3,4,948,925,959,940,WN,3430,71,75,59,19,23,JAX,BHM,365 57,1,3,4,646,620,725,655,WN,1580,99,95,77,30,26,JAX,BNA,484 58,1,3,4,1110,1040,1136,1110,WN,2195,86,90,72,26,30,JAX,BNA,484 64,1,3,4,2139,2130,2244,2240,WN,378,65,70,54,4,9,JAX,FLL,318 69,1,3,4,1738,1730,1841,1840,WN,3948,63,70,49,1,8,JAX,FLL,318 70,1,3,4,1813,1735,1936,1905,WN,54,143,150,125,31,38,JAX,HOU,816 71,1,3,4,802,750,1001,955,WN,2272,119,125,104,6,12,JAX,IND,688 74,1,3,4,1734,1650,1941,1905,WN,23,127,135,113,36,44,JAX,PHL,742 75,1,3,4,712,700,926,915,WN,1232,134,135,120,11,12,JAX,PHL,742 76,1,3,4,1318,1310,1410,1400,WN,977,52,50,39,10,8,JAX,TPA,180 77,1,3,4,958,900,1052,950,WN,1574,54,50,36,62,58,JAX,TPA,180 78,1,3,4,1859,1850,1950,1945,WN,2019,51,55,38,5,9,JAX,TPA,180 79,1,3,4,1538,1445,1753,1710,WN,500,75,85,60,43,53,LAS,HND,487 81,1,3,4,2248,2125,102,2345,WN,890,74,80,60,77,83,LAS,HND,487 82,1,3,4,1327,1230,1550,1500,WN,1171,83,90,65,50,57,LAS,ELY,487 84,1,3,4,1614,1600,1833,1825,WN,1925,79,85,60,8,14,LAS,RNO,487 86,1,3,4,1832,1655,148,30,WN,302,256,275,243,78,97,LAS,RNO,2237 87,1,3,4,1229,1155,1633,1555,WN,1079,124,120,91,38,34,LAS,AMA,758 88,1,3,4,1256,1240,1724,1720,WN,155,148,160,131,4,16,LAS,RNO,1090 89,1,3,4,2118,2015,144,50,WN,2021,146,155,127,54,63,LAS,RNO,1090 90,1,3,4,905,850,1334,1330,WN,3222,149,160,135,4,15,LAS,EKO,1090 91,1,3,4,1739,1640,114,25,WN,1018,275,285,253,49,59,LAS,EKO,2298 94,1,3,4,1325,1240,1841,1810,WN,419,196,210,178,31,45,LAS,BNA,1588 95,1,3,4,1506,1440,2030,2010,WN,2032,204,210,183,20,26,LAS,BNA,1588 96,1,3,4,2039,1930,155,55,WN,3940,196,205,177,60,69,LAS,BNA,1588 98,1,3,4,1611,1535,1849,1825,WN,538,98,110,73,24,36,LAS,BOI,520

99,1,3,4,1824,1715,117,25,WN,2383,233,250,221,52,69,LAS,BUF,1987 101,1,3,4,2118,2015,2224,2115,WN,219,66,60,46,69,63,LAS,BUR,223 102,1,3,4,1818,1740,1916,1840,WN,391,58,60,46,36,38,LAS,BUR,223 104,1,3,4,2146,2055,2250,2155,WN,815,64,60,48,55,51,LAS,BUR,223 105,1,3,4,2241,1910,2340,2010,WN,1072,59,60,43,210,211,LAS,BUR,223 106,1,3,4,1409,1355,1513,1500,WN,1328,64,65,50,13,14,LAS,BUR,223 107,1,3,4,1100,1050,1157,1155,WN,1586,57,65,46,2,10,LAS,BUR,223 108,1,3,4,1306,1250,1406,1355,WN,1838,60,65,47,11,16,LAS,BUR,223 109,1,3,4,1726,1630,1832,1740,WN,2284,66,70,46,52,56,LAS,BUR,223 111,1,3,4,1229,1220,1342,1325,WN,2874,73,65,47,17,9,LAS,BUR,223 113,1,3,4,908,845,1628,1610,WN,1774,260,265,244,18,23,LAS,BWI,2106 114,1,3,4,1817,1730,122,50,WN,2632,245,260,230,32,47,LAS,BWI,2106 116,1,3,4,956,945,1658,1645,WN,818,242,240,213,13,11,LAS,CLE,1825 117,1,3,4,1849,1740,121,30,WN,564,212,230,195,51,69,LAS,CMH,1772 118,1,3,4,1210,1200,1905,1850,WN,991,235,230,201,15,10,LAS,CMH,1772 120,1,3,4,1047,1030,1328,1320,WN,157,101,110,79,8,17,LAS,DEN,629 121,1,3,4,2232,2115,108,5,WN,632,96,110,77,63,77,LAS,DEN,629 122,1,3,4,1512,1315,1802,1610,WN,706,110,115,88,112,117,LAS,DEN,629 123,1,3,4,2025,1955,2301,2245,WN,908,96,110,78,16,30,LAS,DEN,629 124,1,3,4,1439,1425,1720,1720,WN,1582,101,115,85,0,14,LAS,DEN,629 125,1,3,4,1627,1600,1916,1900,WN,2907,109,120,80,16,27,LAS,DEN,629 126,1,3,4,1745,1710,2017,1945,WN,2192,92,95,78,32,35,LAS,ELP,584 127,1,3,4,1049,1040,1320,1320,WN,2280,91,100,73,0,9,LAS,ELP,584 128,1,3,4,1117,1050,1325,1325,WN,1636,128,155,116,0,27,LAS,GEG,806 129,1,3,4,1725,1620,1940,1850,WN,2803,135,150,116,50,65,LAS,GEG,806 130,1,3,4,1824,1735,2303,2230,WN,719,159,175,143,33,49,LAS,HOU,1235 131,1,3,4,1150,1140,1644,1640,WN,1776,174,180,147,4,10,LAS,HOU,1235 132,1,3,4,749,740,1227,1235,WN,3244,158,175,146,-8,9,LAS,HOU,1235 133,1,3,4,2051,2010,134,55,WN,3746,163,165,150,39,41,LAS,HOU,1235 134,1,3,4,1555,1525,2307,2245,WN,1027,252,260,232,22,30,LAS,IAD,2066 135,1,3,4,2255,1820,509,55,WN,1924,194,215,176,254,275,LAS,IND,1591 136,1,3,4,1129,1050,1757,1725,WN,3920,208,215,179,32,39,LAS,IND,1591 137,1,3,4,1602,1510,2357,2255,WN,3144,295,285,269,62,52,LAS,ISP,2283 138,1,3,4,1738,1715,1838,1820,WN,82,60,65,42,18,23,LAS,LAX,236 139,1,3,4,2207,2150,2306,2255,WN,135,59,65,40,11,17,LAS,LAX,236 140,1,3,4,1851,1825,2004,1930,WN,317,73,65,41,34,26,LAS,LAX,236 141,1,3,4,701,645,802,745,WN,337,61,60,42,17,16,LAS,LAX,236 142,1,3,4,1556,1455,1704,1600,WN,354,68,65,41,64,61,LAS,LAX,236 145,1,3,4,1540,1525,1648,1630,WN,1061,68,65,47,18,15,LAS,LAX,236 227,1,3,4,1657,1630,1905,1845,WN,3517,68,75,49,20,27,LAS,PHX,256 228,1,3,4,926,915,1129,1120,WN,3598,63,65,44,9,11,LAS,PHX,256 230,1,3,4,1825,1745,114,50,WN,512,229,245,210,24,40,LAS,PIT,1910 231,1,3,4,927,915,1631,1620,WN,594,244,245,218,11,12,LAS,PIT,1910 232,1,3,4,1630,1600,23,2350,WN,416,293,290,266,33,30,LAS,PVD,2363 233,1,3,4,1136,1120,1852,1840,WN,170,256,260,231,12,16,LAS,RDU,2027 234,1,3,4,1824,1725,121,45,WN,1509,237,260,222,36,59,LAS,RDU,2027 235,1,3,4,1716,1630,1830,1755,WN,239,74,85,60,35,46,LAS,RNO,345 236,1,3,4,1928,1855,2034,2015,WN,298,66,80,54,19,33,LAS,RNO,345 238,1,3,4,1741,1730,1905,1850,WN,675,84,80,57,15,11,LAS,RNO,345 239,1,3,4,1609,1540,1732,1700,WN,709,83,80,55,32,29,LAS,RNO,345 240,1,3,4,913,840,1029,955,WN,891,76,75,61,34,33,LAS,RNO,345 241,1,3,4,2215,2200,2334,2315,WN,1341,79,75,57,19,15,LAS,RNO,345 242,1,3,4,2137,2040,2251,2155,WN,2273,74,75,56,56,57,LAS,RNO,345 244,1,3,4,1100,1020,1210,1145,WN,2719,70,85,54,25,40,LAS,RNO,345 245,1,3,4,1313,1255,1437,1415,WN,3430,84,80,57,22,18,LAS,RNO,345 246,1,3,4,1415,1350,1527,1510,WN,3835,72,80,56,17,25,LAS,RNO,345 248,1,3,4,1348,1330,1455,1435,WN,341,67,65,51,20,18,LAS,SAN,258 250,1,3,4,2140,2050,2240,2150,WN,708,60,60,45,50,50,LAS,SAN,258 252,1,3,4,1643,1625,1751,1735,WN,715,68,70,46,16,18,LAS,SAN,258 253,1,3,4,946,940,1045,1045,WN,1037,59,65,46,0,6,LAS,SAN,258 254,1,3,4,1915,1905,2016,2005,WN,1254,61,60,44,11,10,LAS,SAN,258 255,1,3,4,1839,1740,1945,1840,WN,2186,66,60,44,65,59,LAS,SAN,258 256,1,3,4,2207,2125,2307,2225,WN,2211,60,60,49,42,42,LAS,SAN,258 257,1,3,4,1503,1425,1604,1530,WN,2357,61,65,48,34,38,LAS,SAN,258 258,1,3,4,1613,1515,1727,1615,WN,2363,74,60,47,72,58,LAS,SAN,258 259,1,3,4,2248,2155,2346,2255,WN,2449,58,60,45,51,53,LAS,SAN,258 282,1,3,4,2114,2040,2241,2200,WN,774,87,80,69,41,34,LAS,SJC,386 283,1,3,4,1605,1520,1739,1645,WN,783,94,85,68,54,45,LAS,SJC,386 286,1,3,4,1734,1655,1857,1820,WN,3844,83,85,66,37,39,LAS,SJC,386 287,1,3,4,1139,1100,1408,1320,WN,161,89,80,54,48,39,LAS,SLC,368 288,1,3,4,2104,1905,2314,2120,WN,415,70,75,58,114,119,LAS,SLC,368 289,1,3,4,919,830,1132,1050,WN,643,73,80,55,42,49,LAS,SLC,368 290,1,3,4,2113,2050,2328,2305,WN,1166,75,75,58,23,23,LAS,SLC,368 291,1,3,4,1712,1555,1935,1820,WN,1585,83,85,62,75,77,LAS,SLC,368 292,1,3,4,1419,1355,1627,1615,WN,1660,68,80,55,12,24,LAS,SLC,368 293,1,3,4,2242,2140,54,2355,WN,2533,72,75,55,59,62,LAS,SLC,368 295,1,3,4,1949,1910,2110,2035,WN,94,81,85,66,35,39,LAS,SMF,397 296,1,3,4,1409,1350,1539,1520,WN,584,90,90,74,19,19,LAS,SMF,397 298,1,3,4,1821,1730,1946,1855,WN,641,85,85,67,51,51,LAS,SMF,397 299,1,3,4,916,905,1046,1035,WN,2121,90,90,68,11,11,LAS,SMF,397

All submissions must be in the format specified. In our case it should be a single java file. You must build your java project with a single java file (e.g., foo.java). There is no need to submit any other file, data files, rar files etc. Please read very carefully the requirements. They are not extremely difficult, but following the details is very important. The program will be tested against test files. The test files will have the same format as discussed below in the Specifications section. So make sure your program is oblivious to the content of the file (but of course, not to the format of the file), and will result in a correct answer to any possible test file conforming to the specified format. For example, the fact that your program outputs the correct result on the input files You are supposed to build a program that uses a database for storing, manipulating and querying information about delayed flights within the USA. You should use the Java language, This mini-project covers some of the notions covered in class and lab tutorials. The assessed learning outcomes are a correct usage and understanding of SQL via the JDBC library. You should not use any other auxiliary file in your program. Only the files delayedFlights and airport should be used. 2. The program should check if the tables (and possibly views) it creates already exist in the database, and only if they do, drop them before their creation. For tables you can simply do: DROP TABLE IF EXISTS Reading the delayedFlights and airport files The delayedFlights file available on moodle is a file with the following format: - Each line in delayedFlights consists of a record about a delayed flight. - The attributes are as follows: ID_of_Delayed_Flight, Month, DayofMonth, DayofWeek, DepTime, ScheduledDeptime, ArrTime, ScheduledArrTime, UniqueCarrier, FlightNum, ActualFlightTime, scheduledFlightTime, AirTime, ArrDelay, DepDelay, Orig, Dest, Dis - The attributes' meaning are self explanatory. The airport file available on Moodle lists US Airports, sorted by Airport Code, with the format being: airportCode, airportName, City, State. 3. The program now should read the two files, delayedFlights and airport above, and insert the information it reads to the database, in an appropriate way that would suite the requirements below (namely, it should insert it to tables in the way suitable to answer the queries below). 4. Make sure that the basic primary keys and foreign keys are specified so that users cannot delete a record containing information referred to (implicitly) by another table. - The program should output some statistical information about the data it reads from the file, as described in what follows (in the same order they are listed). - When you are required to list a collection of records/attributes, you are meant to output them to the standard output, where each record is written in a new line. - After the program lists each of the clauses above, it should output section separators lines like "\#\#\#\#\#\#\#\#\#\# ith Query \#\#\#\#\#\#\#" to make the output easy to read. - If you are requested to list 5 elements/records and there are less than 5 such elements/records, the program should simply print as many as there are. Query 1 (10 marks) List the 5 distinct Unique Carriers (UniqueCarrier) that suffer the largest number of flight delays (either on arrival or on departure) and the number of delays they had in descending order (with respect to the number of delays). Example of output: \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# 1st Query \#\#\#\#\#\#\#\#\#\#\#\#\#\#\# WN 5665 AA 809 B6 396 EV 330 UA 252 Query 2 (10 marks) List the top 5 distinct cities in which departure delays occur the most (irrespective of the length of the delay) in descending order (with respect to number of delays). Example of output: \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# 2nd Query \#\#\#\#\#\#\#\#\#\#\#\#\#\# Chicago 752 Las Vegas 695 Dallas 425 Los Angeles 375 Phoenix 358 Query 3 (15 marks) List the 2nd to 6th distinct destinations (Dest) that has the highest total amount of minutes in arrival delays (ArrDelay) in descending order (with respect to total arrival delay minutes), together with their total arrival delay minutes. Namely, if we denote the six distinct destinations with the highest total arrival delay minutes by a1, a2, a3, a4, a5, a6, then the program should output a2, a3 ,a4 ,a5 ,a6 (together with the total arrival delay minutes for each destination, as shown in the example below). Example of output: Query 4 (15 marks) For every state that has at least 10 airports, list the state and the number of the airports. Query 5 (20 marks) List the top 5 distinct states in which a flight between different airports within the same state has been delayed (either in arrival or departure) by descending order with respect to number of such delays (irrespective of the length of the delay), together with the number of such delays (you can assume that no flight has both origin and destination the same airport, and that every occurrence of a line in the delayedFlight file records a delay in either departure or arrival)