Homework 2
Due Date: 3/21/2018
Even though this homework is due after Midterm 1 I strongly
recommend that you are trying to do it before the Midterm. Some of the questions will be
excellent exercises for preparing the programming part of the exam. You still have to study for
the theory part of course.
Points: 40
1) Draw a UML diagram expressing the following knowledge.
SUMMER-SPORT
IS-A SPORT
WINTER-SPORT
IS-A SPORT
TEAM-SPORT
IS-A SPORT
INDIVIDUAL-SPORT
IS-A SPORT
FOOTBALL
IS-A TEAM-SPORT
ICE-HOCKEY
IS-A TEAM-SPORT
ICE-HOCKEY
IS-A WINTER-SPORT
SKIING IS-A
WINTER-SPORT
SKIING IS-A
INDIVIDUAL-SPORT
DOWNHILL-SKIING
IS-A SKIING
GIANT-SLALOM
IS-A SKIING
PAIR-SPORT
IS-A SPORT
ICE-DANCING
IS-A PAIR-SPORT
SPORT has a
BOOLEAN attribute: IN-OLYMPICS
SKIING has
an operation: GET-TIME()
Do NOT add to this. Do NOT complicate this.
You will
not get more points, and more likely your grader will not like it.
You can
draw the UML diagram with any tool you like. NetBeans, Rational,
ERWIN (if you have access to it), Visio, or even just MS Word.
Just, you
CANNOT draw it by hand. Not even with a ruler.
Remember
that there is a difference between “correct” and “correct
& beautiful.” Try your
best to have a diagram that is correct & beautiful.
“Correct”
incudes getting the arrow heads right, etc. See the lecture.
I mentioned
rules for “beautiful” in class. For example, as much as possible
all children of one concept should be drawn at the same level (next to each
other). You may draw sideways on the page.
[5 points]
2) Create an ORACLE SQL class TPOPULATION. That class should have the data fields STATE and POPULATION.
[1]
Create a
table OOPOPULATION that has one single column of type TPOPULATION.
[1]
3) Write a PL/SQL main program using an implicit cursor that loads all the data from POPULATION (from Homework 1) into OOPOPULATION.
In other
words, instead of having two values in two columns (in every row) the two
values should be in one single column inside of an object. So OOPOPULATION will
have exactly as many rows as POPULATION when you are done. OOPOPULATION will
have the same data as POPULATION. But OOPOPULATION will have only one column.
Show your
main program.
Show the
table OOPOPULATION.
[5]
4) Drop the
table POPULATION. Show the drop command. Show the resulting error when you do
(select * from POPULATION)
[1]
5) Write a PL/SQL main program with a cursor (no WHERE clause in the cursor’s
SELECT is permitted). This program should display the complete content of
OOPOPULATION for all states with more than 1 Million people in a two column
format (Hint: use rpad). Show the states in
alphabetical order.
Show the
program.
Show the
output of the program.
[5]
6) Now CREATE an empty table POPULATION2 with two columns POPULATION and STATE (in this order, i.e., a different order from the original table POPULATION). Do this inside of a PL/SQL Program.
[1]
7) Write a PL/SQL main program using an implicit cursor that loads all the data from OOPOPULATION (from Question 3) into POPULATION2.
[5]
8) Write a PL/SQL main program with a cursor (no WHERE clause in the cursor’s
SELECT is permitted). This program should display the complete content of POPULATION2
for all states with more than 2
Million people in a two column format. It should show the STATE column first,
even though it is stored as second column. For every state with more than 10
Million people it should add the words “large state” at the end of
the line. Thus the output for
California would look like this:
California 39,250,017 large state
Show the
program.
Show the
output of the program.
[5]
9) Create a table NEWCARS with a column STATE, a column CARS and a column NEWCARS. Do this inside of a PL/SQL Program.
[1]
10) Write an Update Trigger for the table CARS from Homework 1. Whenever the table CARS is updated, the Trigger should place into the table NEWCARS the name of the state, the number of cars before the update, and the number of cars after the update.
Test your program by adding 100 cars to California, 200 Cars to New York and 300 Cars to Texas.
Show the Program (Trigger and Update statements).
Show the table NEWCARS once after all 3 updates.
Show the table CARS once after all 3 updates.
[5]
11) Using vi in AFS write a file with the name thankyou that contains the following text.
https://www.google.com/search?q=handwritten+notes&tbm=isch&tbo=u&source=univ&sa=X&ved=0ahUKEwip352kwK7ZAhUyhuAKHQS5CwIQ7AkIcQ&biw=1300&bih=674#imgrc=UsAnO81aEEKp4M:
Add a header with your first name, last name, UCID, home country and home town (where you were born).
Show the file.
[3]
Show a screen dump of the AFS directory after doing an ls –l command that shows the file name.
[2]
================================================================
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).