In question 3) e) you can use two queries.
Due Date: 4/11/08
Points: 50
IMPORTANT: MY GRADER HAS REQUESTED THAT YOU SUBMIT ALL YOUR CODE
(SELECT STATEMENTS, ETC.) ELECTRONICALLY BY EMAIL. (BUT NOT THE SCREEN DUMPS!!)
THIS IS **IN ADDITION** TO THE HARDCOPY AND DUE ON THE SAME DAY.
THE HARDCOPY NEEDS TO HAVE CODE AND SCREEN DUMPS, AS BEFORE.
SEND EMAIL TO: sv88@njit.edu
SUBJECT: Homework 3.
We are going to reuse the table from Homework 1.
1) a) Create an Oracle class person_t that contains fields for
FirstName, MiddleInitial, LastName, BirthYear, and PersonID.
This will be similar to the person_t in your handout.
b) Create a table PERSON_OBJECTS which has one single column
named PERSON_INFO and which contains objects of type person_t.
c) Write a PL/SQL program that will load your 20 people into this one column table.
Your program should get the data from the Table of Homework 1.
In other words: You are NOT inserting the data into the new table by hand.
You are doing it by program. As usual, with a cursor.
d) Display all data in your table PERSON_OBJECTS. NOTE: This cannot be done with Aqua,
you need to do it with the Oracle iSQL Web client, which is accessible through
my home page.
[10 points]
2) a) Write a SELECT statement that will return all unique first names from PERSON_OBJECTS.
b) Write a SELECT statement that will return complete information on all
people born after 1950 from PERSON_OBJECTS. But make it look nice. Separate
the different parts of the data out into separate columns.
c) A person can be the parent of another person if [let's say]
the first person is at least 18 years older than the second person.
Find all pairs of people where one could possibly be the parent of another.
Use PERSON_OBJECTS for this. Display all information on child and parent
in one row. Do this with a PL/SQL program with a cursor.
[10 points]
3) a) Create an Oracle class woman_t. Make it a subclass of person_t,
with an additional field: number_of_children.
b) Create an Oracle class man_t. Make it a subclass of person_t,
with an additional field: years_of_military_service.
c) Create tables WOMEN and MEN for data of type woman_t and men_t respectively.
d) Insert information on the following people:
WOMEN
Greta M. Garbo, 1931, 1021, 3 [children]
Marlene S. Dietrich, 1920, 1022, 0
Hildegard F. Knef, 1969, 1023, 2
Amanda B. Lear, 1973, 1027, 1
MEN
Herman B. Sherman, 1945, 1024, 1 [years of service]
John T. Lieber, 1934, 1025, 0
Arnold R. Orkun, 1961, 1026, 2
Bertrand P. Plastic, 1970, 1028, 0
d) Repeat 2) b) for both tables WOMEN and MEN.
e) Display complete information on all people in the order of birth year for both WOMEN
and MEN. Use a SELECT statement.
[10 points]
4) Next you will create another table XMLPERSONS that contains the same information as Question
1 in XML format. There should be one column called XMLINFO. Insert all people from
Question 1.
Here is an example how one person should be encoded:
<person>
<name><first>Greta</first>
<mid>M</mid>
<last>Garbo</last>
</name>
<birthyear>1951</birthyear>
<id>1021</id>
</person>
[10 points]
5) a) Repeat queries 2)a), 2)b) and 2)c) using the table XMLPERSONS.
Note again that a) b) are just SELECT statements, but c) is a PL/SQL program.
b) Write one paragraph on the following question: Which representation do
you think is better, Objects in Oracle or XML in Oracle? Why?
[10 points]
THE FOLLOWING APPLIES TO ALL ASSIGNMENTS:
- Create a nice cover page with
your name, assignment number, class number and a descriptive
title of the assignment.
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.
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.
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 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 fax 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.
- 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 10 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).