Homework 2: Revised on Friday 10/9/2010

I received several questions on problem 3).  See extended
descriptions below.

Due Date: 10/12/2009
Points: 50

1)  Now that you have experience with creating tables, create a table MEGA that
contains all the following data:

http://www.state.nj.us/lottery/games/1-1-2_mega_history.shtml

You may eliminate the date column.  Whatever is easier for you.  We won't need it.
Call the other columns NUMBER1, NUMBER2, NUMBER3, NUMBER4, NUMBER5, MEGANUMBER, PAYOUT.
(Hint: Use the printer-friendly version.)

[10 points]

2) A lottery typically pays out only half (or less) of what it takes in.
Let's assume that the NJ lottery pays out 40% of all income. 
One lottery ticket costs $1.
Note that there are two drawings per week.

So... using all the paid out prices for all the available data, how many
tickets did the lottery sell ON AVERAGE every week?  

Solve this problem with SELECT statements.  No program, no calculator, no 
hand calculation.
[5 points]


3) Using the table MEGA write two PL/SQL programs with explicit or implicit cursor(s)
that answers the following questions: (One program per question.)


a) What was the most common Mega Ball number and how often did it occur?
Hint: Megal Ball numbers go from 1 to 48.

Student Question: What if there are several numbers that occur equally "most often."
Answer: You need to report all of them.
[5 points]

b) What was the most common number in the five other columns?  Note that many
numbers appear in more than one column.  Hint: All other numbers are between
1 and 56.

Clarification:
Imagine that the five other columns are combined into one single column, and
then you are doing the same thing as in question a).

HOWEVER: You should solve this problem by programming.
You should **NOT** combine the values of five columns into a single column of a new table.
I **ONLY** used this to explain the idea.

[10 points]


4) a) Create a table UPDATE_COUNT that contains one single row and one single
column. Set the value in this table to 0. 

b) Write an update trigger (row trigger) for the table MEGA.  Every time a row
in Mega is changed you should update the value in UPDATE_COUNT itself, adding 1 to it.

c) Test this trigger by updating MEGA as follows:

update MEGA
set NUMBER1 = 0
where NUMBER1 = 1

update MEGA
set NUMBER1 = 0
where NUMBER1 = 2

update MEGA
set NUMBER1 = 0
where NUMBER1 = 3

Show UPDATE_COUNT before and after each update.

[10 points for a) & b) & c)]

5) a) Write a delete trigger for the table MEGA.  Every time a row
is deleted it should print a message to the screen: Warning, a
row was deleted. If many rows are delete with one statement
it should still print only one message.

b) Test this trigger by 4 delete statements.

c) Test this trigger with a truncate statement.
What happens?  Write down what happens.

[10 points for a) & b) & c)]

===============================================================

VOLUNTARY QUESTIONS:


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).