Homework 3

Due Date: 11-20-2018 TUESDAY !!!
 
Points: 40
 
1) The purpose of this assignment is to learn to transform from relational to object-relational format and use that format.
 
a) For this purpose create an ORACLE class airport_t that has data fields for 
Rank,
Airport,
Location,
Country,
Code_IATA,
Code_ICAO,
Total_passenger,
Rank_change,
Percent_Change
 
[1]
 
b) Next create a table AIRPORTS2017OO that has one single column AIRPORT which can store an objectof type airport_t.
 
[1]
 
c) Write a PL/SQL main program that inserts 50 rows into AIRPORTS2017OO. The data should be taken from AIRPORTS2017 using a cursor.
 
In other words, both AIRPORTS2017 and AIRPORTS2017OO contain exactly the same data, but AIRPORTS2017OO has all the data in a single column.
 
[3]
 
d) Using ONLY the table AIRPORTS2017OO solve these problems from HWK 1 again:
[5 * 1]
 
A) Show AIRPORTS2017 in alphabetical order by Country.
 
B) Using Group By, show each country once and how often it appears in the list.
 
C) Show the total number of countries in the table. With a single SELECT statement.
 
D) Show all information about all airports that had more than 7 million flights in 2017. 
 
E) Show all information about all the airports that increased their passengers, yet decreased their ranking.
 
 
2) The purpose of this assignment is to learn to transform from relational to XML format and use that format.
 
a) For this purpose create a table AIRPORTS2017XML that has one single column AIRPORT which can store data of type XML.
 
b) Write a PL/SQL main program that inserts 50 rows into AIRPORTS2017XML. The data should be taken from AIRPORTS2017 using a cursor.
 
A single row should look (for example) like this:
<OneAirport>
 <Rank>3</Rank>
 <Airport>Dubai International</Airport>
 <Location>Garhoud</Location>
 <Country>United Arab Emirates</Country>
 <Code_IATA>DXB</Code_IATA>
 <Code_ICAO>OMDB</Code_ICAO>
 <Total_passenger>88242099</Total_passenger>
 <Rank_change>0</Rank_change>
 <Percent_Change>5.5</Percent_Change>
</OneAirport>
 
Typically a lot of students have a problem with this.
The solution is to use MANY || operations that combine the XML tags with the data elements returned by the cursor. In this way, you create one long string (varchar2). Then you pass that string to the XML constructor. It is not hard, just a lot of correct typing.
 
c) Using ONLY the table AIRPORTS2017XML solve these problems from HWK 1 again:
 
A) Show AIRPORTS2017 in alphabetical order by Country.
 
B) Using Group By, show each country once and how often it appears in the list.
 
C) Show the total number of countries in the table. With a single SELECT statement.
 
D) Show all information about all airports that had more than 7 million flights in 2017. 
 
E) Show all information about all the airports that increased their passengers, yet decreased their ranking.
 
 
 
================================================================
 
THE FOLLOWING APPLIES TO ALL ASSIGNMENTS:
 
- Create a nice looking cover page with 
 your name, assignment number, class number and a descriptive
 title of the assignment. You may be creative to make it nice looking. 
 
 All parts of your assignment need to be stapled 
 together, or held together by a folder. Points will be taken off
 for not complying with these rules.
 
 If you use a folder, write your name ALSO on the folder. Or use
 a folder with a transparent cover page.
 
 No handwriting anywhere.
 No handwriting anywhere.
 No handwriting anywhere.
 
 Make things look nice and professional. 
 
- Documentation:
 Do a reasonable amount of internal documentation.
 Internal documentation typically means that you explain
 your code or SQL statements.
 No external documentation unless I specifically ask for it.
 
- Test runs:
 Show your table after you build it. Show testruns 
(that means all SQL statements and their results).
 Show as much as you can. The burden of proof is up to you.
 NO TESTRUNS, NO POINTS. 
 (Or very, very few.) You may record the results in a file.
 Thus, you don't need to do screen dumps, you print the file.
 You will not be punished for too many test runs.
 
- Hardcopy:
 All code, documentation, and testruns will be accepted as Hardcopy
 (Printout) ONLY. No email, No Google documents, etc. 
 
- Grading:
 Grading takes into account correctness of the results, but also
 how well they are documented, and what their visual appearance is.
 Things should look nice and PROFESSIONAL. Not sloppy. Use a spelling
 corrector for your comments or (later on) write-ups. Some people do this
 very nicely. Don't cut corners here, or you will look bad in comparison.
 PROGRAMS MUST BE INDENTED.
 
- Cheating:
 The usual policies apply. 0 points for "suspiciously similar" results.
 Report to the Dean in case of repetition.
 
- Files:
 You must keep all your files of every assignment up to the end of
 the semester. If there is any doubt about your assignment I will
 ask you to demonstrate it online.
 
- Late policy:
 I will take off 8 points for being late up to 1 week. Programs
 that are handed in more than a week late will receive no credit
 at all, unless you have a good reason with WRITTEN documentation
 (usually medical).