Homework 2

Due Date: 10/10/2008
Points: 50

10 points for each question.

1) Given is a set of store locations as follows:

Store 1 Bridgewater
          o 279 Route 22 West, Bridgewater, NJ 08807
    2 New Brunswick
          o 280 US Hwy 1 North, New Brunswick, NJ 08903
    3 Paramus
          o 485 Route 4 East, Paramus, NJ 07652
    4 Parsippany
          o 219 Route 46 East, Parsippany, NJ 07054
    5 Succasunna
          o 1275 Rt 10, Succasunna, NJ 07876
    6 Turnersville
          o 91 Black Horse Pike, Turnersville, NJ 08012
    7 Voorhees
          o 189 White Horse Road, Voorhees, NJ 08043
    8 Wayne
          o 613 Route 23 S., Wayne, NJ 07470
    9 Weehawken
          o 485 Avenue at Port Imperial, Weehawken, NJ 07086


Create a table called STOREADDRESSES that contains all this information.
Note that street name, number, town, state and zip code should each be
stored in a separate column.

2) a) Alter the table STOREADDRESSES so that it has a new column.
(Do NOT create this column from the beginning).  The new column
should be called AREACODE.

b) Write a PL/SQL program with a cursor (implicit or explicit)
that will find the correct area code for each location (based 
on the table from Homework 1) and will put the area code
into the table STOREADDRESSES.  This will require an update cursor
for the second table.

c) Write a PL/SQL program with an update cursor.  This program
should change every town name in STOREADDRESSES to all upper case,
e.g. WEEHAWKEN.


3) a) Create a table CHANGEDZIPCODES with one column for zip codes.

b) Write a PL/SQL trigger for the table NJCONGRESS.  Any time 
somebody deletes a district, the trigger should send all the
zip codes of this district to the screen.  It should also save
all these zipcodes to the new table CHANGEDZIPCODES.
It should NOT abort the deletion.

Demonstrate that your trigger works with ONE district.

c) Recreate the table NJCONGRESS as it was before.
You can do this in any way you like, by hand, by program,
by reloading the table, etc.  We just need the table
back to the correct state.


4) a) Add a column TOTALREVENUE to the table STOREADDRESSES.
There are 9 stores above that should be in your table.  Enter the following
values into the TOTALREVENUE column, using the order of the
stores given above:

$140,000
$180,000
$205,000
 $99,000
 $89,000
$212,000
$111,000
$201,000
 $85,000

b) Create a trigger for the table STOREADDRESSES.  Any time
somebody adds a new store address, this trigger should 
send to the screen the NUMBER of how many store addresses there
are and the total revenue (sum) of all stores, including the new one.

Demonstrate that your trigger works with THREE new addresses.
Use the following values as revenues for the new stores:

 $11,000
 $22,000
 $33,000

Invent the addresses of the new stores.
Make sure they don't look like the address of your class mates.


5) Create a trigger for the column TOTALREVENUE in the table
STOREADDRESSES.  Any time somebody updates the total revenue
for a store, your trigger should print the "percent change"
to the screen.

The percent change is computed as 

newtotalrevenue -- oldtotalrevenue
----------------------------------  * 100
       oldtotalrevenue

Test your trigger by changing the total revenue of 

Store 1: 150,000
Store 2: 190,000
Store 3: 210,000

Percentages should be integer numbers.



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