Lab 9

1
Lab 9

• Moving on to implementation and testing the
  package

2
Tasks for today in the lab

• Look at the access required to
• Add data to another schema
• Update data in another schema
• Run procedures / functions in another schema.
• Try to run the procedures in custorders.
• Have your team ERD checked for accuracy.
• Start to build your team schema.

3
Testing the package

• To test the package, each student needs
• Insert access to the customer table
• Insert access to the corder table
• Insert access to the corderline table
• Update access to the stock_level column of the
  stock table
• Also
• Insert access to all three error tables.
• Execute access to the contents of the
  builder.custorders package.

4
Granting access

• If Oliver OConnor takes the role of salesperson
• Grant insert on customer to ooconnor
• Grant insert on corder to ooconnor
• Grant insert on corderline to ooconnor
• Grant update on stock(stock_level) to ooconnor
• Grant insert on corder_error to ooconnor
• Grant insert on restock to ooconnor
• Grant insert on corderline_error to ooconnor
• Grant execute on custorders to ooconnor
• And Art Sloan takes up the role of foreman
• Grant update on corder(staffissued) to asloan
• Grant insert on corder_error to asloan
• Grant insert on corderline_error to asloan

5
Your access

• Every student has been given access to both the
  foreman role and the salesperson role.
• You can run the procedures yourselves.
• Try to add the following order
• Brad Pitt from Hollywood Boulevard, Hollywood,
  Los Angeles, wants to buy 4 Brass Hinges, a
  power saw and 4 cans of beer.
• The source of custorders is in the P drive.

6
To add the order

• Set up an anonymous block.
• Add declarations as required.
• Count the customers whose name is Brad Pitt and
  put the result into an integer rowcnt.
• If there is 0, select the customer_id into a
  variable
• Otherwise, add the customer using add_customer.
• Add an order, with todays date and the customer
  number (the order number will be generated. Let
  the staffpaid be 51).
• For each stock item being ordered
• Check to see if it exists (as you did for the
  customer)
• If so, add a corderline for it, otherwise display
  an error.

7
Team ERD

• If you think youre nearly there
• Check with Patricia OByrne or Oliver OConnor to
  see if you have a reasonable set of tables,
  attributes and relationships.
• Either
• Model it in ERWin and generate the CREATE
  statements.
• Or
• Write the CREATE statements, ensuring referential
  integrity.
• If you dont think youre nearly there
• Flag the problem with Patricia OByrne
• Consult Patricia OByrne or Mark Foley in the
  tutorial.

8
Lab test results

• Lab test results have been posted.
• If you have any questions about your result
• Art Sloan can show you
• the full result sheet
• The sample solutions
• Please note, there are other correct solutions.
• In cases where hyphens were used instead of
  underscores, marks were not deducted.
• In cases where students could not find the driver
  name, any field specific to the driver table
  sufficed.