. .Spring 2008 CSC/CPE 365: Database Systems Alexander Dekhtyar. .Lab 8: PL/SQLDue date: Friday, June 6 11:59pm (official). There is also a grace period forthis lab, which extends until 10:00am, June 9.Assignment PreparationThe main part of th is assignment is to be done in teams. The remainingpart is individual. You can organize yourselves in teams of 2-3 people each.Once you build your team, one member of the team needs to email me [email protected]. The email message should have the header ‘‘CSC365: Lab 8". In the body of the message, please put the names and emailsof all team members .Lab AssignmentIn a NutshellThe lab consists of parts:• Part 1. (individual) Fix all queries from L abs 4 and 6.• Part 2. (team) Write PL/SQL code for specified functions and pro-cedures.Part 1Using the gradesheets from Labs 4 and 6, revisit your submissions and fixyour queries to provide correct answers. Feel free to talk to me if you haveany outstanding question about specific queries.There is no deliverable for th is part, but queries like th ose in the labs willbe asked on the final exam, so, this part of the lab goes towards your finalexam preparation.1This is an individual task, and should be done outside of the lab time, orafter the team part of the lab is completed.Part 2You are asked to form groups of 2-3 people. The goal of each group is tobuild simple PL/SQL packages for two course databases. The functions willbe used from SQL queries to the database. The procedures will be calledfrom anonymous PL/SQL blocks.The list of tasks is outlined below.BAKERY databaseCreate a package bakery which contains the following fun ctions and proce-dures.Please Note: I use anchored typ es in the specifications below. Theseanchor types reference attribute n ames from my implementation of thedatabases. If in your implementation, attribute names are different youwill use your attribute names in such situations. E.g., if the Food column ofthe goods.csv file is named FType in your database, then you w ill referencegoods.FType%type rather than goods.Food%type. (Same thing applies tothe table names).• function itemrevenue() takes as input the food type, and the foodflavor, as well as a pair of dates, and outpu ts the total r evenue gen-erated by this specified item in the given time interval (the d ates areinclusive). The f ull spec is:function item_revenue(myFood in goods.food%type,myFlavor in goods.flavor%type,stDate in receipts.SaleDate%type,endDate in receipts.SaleDate%type)return goods.price\%type;• function customerrevenue() takes as input the last name of the cus -tomer and a pair of dates and outputs the total revenue generatedby th e specified customer in the given time interval (the dates areinclusive). The f ull spec is:function customer_revenue(myLname in customer.LastName%type,stDate in receipts.SaleDate%type,endDate in receipts.SaleDate%type)return goods.price\%type;• procedure purchase() takes as input the last name of th e customer, adate, th e description of a bakery item (food and flavor information).2The procedure creates a database record of the specified customer’spurchase of the given bakery item. The receipt number is generatedas follows: find the largest receipt number in the database, and add10 to it. The full spec is:procedure purchase(myLname in customer.LastName%type,myDate in receipts.SaleDate%type,myFood in goods.food%type,myFlavor in goods.flavor%type);Note: this procedure results in creation of a new pur chase of a singleitem in the BAKERY database. This is NOT a generic ”create a newreceipt for a multi-item p urchase” procedure.AIRLINES databaseCreate a package airlines which contains the following functions and pro-cedures.• procedure directconnect() takes as input a pair of airport codes(source and destination). If at least one direct flight between th etwo airports exists, for each such flight the procedure outputs theflight number and the name of the airline servicing the flight. If nodirect flights are found, the procedure reports "No direct flightsfound." The full spec is:procedure direct_connect( mySource in flights.source%type,myDest in flights.destination%type);• procedure onestop connect() takes as input a pair codes (sourceand destination). If at least one direct flight between the two airportsexists, for each such fl ight the procedure outputs the flight numberand the name of the airline s ervicing the flight. If there are no directflights, the procedure checks if it is possible to get from the sourceairport to the destination airport via a single transfer point (i.e., viatwo flights). For each such transition, the procedure shall report thesource airport, the flight number of the first flight and the airlineservicing it, the intermediate airport, the flight number of the secondflight and the airline servicing it and, finally, the destination airport.The full spec is:procedure one_stop_connect( mySource in flights.source%type,myDest in flights.destination%type);• function numflights() takes as inpu t an airport code and returnsback the number of flights to/from the airport (note that the databaseis organized so that the number of incoming flights is the same as thenumber of the ou tgoing flights). The f ull spec is:3function num_flights( myAirport in flights.source%type)return binary_integer;Extra CreditExtra credit will be given for implementation of the following procedure inthe package airlines:• procedure reachable() takes as input an airport code and an integernumber and outputs the list of all airports reachable from the g i venairport i n the num of flights that is less than or equal to the i nputnumbe r. If the input integer is 0, then the procedure outputs allairports reachable from the given airport in any number of steps.Notes: Each airport name shall be reported exactly once. T he inputairport shall not appear in the output.The full spec is:procedure reachable(myAirport in flights.source%type,numSteps in binary_integer);Submission InstructionsYou have to prepare two .sql files: BAKERY-package.sql and AIRLINES-package.sql.For each of the two datasets, also submit the -setup.sql, -insert.sql and-cleanup.sql files. Each dataset is to be submitted in a separate direc-tory (BAKERY and AIRLINES respectively). Email instructor a .zip or.tar.gz file containing all required files.Each file must contain a comment block at the top listing a llmembers of the group.If you have implemented the extra credit assignment (which is