Advanced Database Management

90-758

Spring 1998

Take Home Midterm Exam

(Due: Monday, March 9, 1998)

 

 

Group Members:

 

1.____________________________________

2.____________________________________

3.____________________________________

4.____________________________________

 

 

Question 1 (5 Points):

 

Write a procedure called Populate_List_Items that uses the Init_Codes_Multi function (located on the class web page) to populate all list items in a form. In other words, instead of populating list items from within a when-new-form-instance trigger, populate them from within a procedure, and call the procedure from the when-new-form-instance trigger.

 

Assumptions:

 

The code table containing the multiple codes is named TBL_CODES and has the following three columns: (1), CODE_ID (number) which is the primary key of the code table, (2) CODE_TYPE (varchar2(10)) which contains the type of code for the current row (for example, 'STATE'), and DESCRIPTION (varchar2(15)) which contains the description of the code.

 

You need to populate the following five list items:

 

  1. STATE_LIST (mirrored to STATE_ID, code type 'STATE'), which is an item in the B_CUSTOMER block.
  2. STATE_LIST (mirrored to STATE_ID, code type 'STATE'), which is an item in the B_VENDOR block.
  3. ITEM_TYPE_LIST (mirrored to ITEM_TYPE_ID, code type 'ITEM'), which is an item in the B_ORDERS block.
  4. PAYMENT_TYPE_LIST (mirrored to PAYMENT_TYPE_ID, code type 'PAYMENT'), which is an item in the B_ORDERS block.
  5. CREDIT_STATUS_LIST (mirrored to CREDIT_STATUS_ID, code type 'CREDIT'), which is an item in the B_CUSTOMER block.

 

Hint: all items ending in _LIST are the list items (base table = false), and all items ending in _ID are the actual fields that get inserted into the database (base table = true).

 

Question 2 (5 Points):

 

Of the four choices for each question, which type of trigger is most appropriate for the situation described?

 

1. Initialize global variables

 

  1. when-window-activated
  2. when-new-form-instance
  3. when-new-block-instance
  4. post-form

2. Obtain a sequence number for a primary key field in a block

 

  1. post-insert
  2. pre-update
  3. when-create-record
  4. pre-text-item

 

3. Using the execute_query procedure, automatically fetch all records into a block each time a block is navigated to

 

  1. post-block
  2. when-new-block-instance
  3. pre-record
  4. when-new-item-instance

 

4. Use an alert to ask the user if he/she is sure a record is to be deleted after the user has pushed a delete button that calls the delete_record procedure

 

  1. pre-delete
  2. post-delete
  3. post-commit
  4. on-fetch

 

5. Use a NULL statement to disable the 'up arrow' key

 

  1. key-edit
  2. key-scrup
  3. key-scrdown
  4. key-up

 

 

 

Question 3 (5 points):

 

The following PL/SQL source code is a somewhat feeble attempt at writing a function that updates the name of a boat (boat_name - varchar2(15)) based on the boat's primary key (Boat_Id - number) in a table called TBL_BOATS. The function returns 1 if the operation was successful, -1 if not. There are five syntax errors in the function that would result in compilation errors. Identify the error (half point) and explain what the nature of the error is (half point).

 

Function Get_Boat_Id(new_boat_name varchar2, this_boat_id number) is

 

curr_name varchar2(15)

 

begin

select boat_name into temp_name from TBL_BOATS

where boat_id = this_boat_id;

if (curr_name is not null then

update TBL_BOATS set boat_name = new_boat_name

where boat_id = this_boat_id;

commit;

return 1;

else

return -1;

end;

 

 

Question 5 (10 Points):

 

Assume that your boat table is called TBL_BOATS and contains the following items:

 

Boat_id number (note: this is the primary key)

Boat_name varchar2(15)

Owner_first_name varchar2(15)

Owner_last_name varchar2(15)

 

Write a function called get_boat_details which has one parameter (this_boat_id - number) passed to it which is the primary key of the boat table. The function returns a string (of type varchar2) containing information on that boat where boat_id = this_boat_id. The returned string should be formatted.

 

For example, assume that a row in the boat table looks like this:

 

Boat_id: 45

Boat_name: Island Dream

Owner_first_name: Sally

Owner_last_name: Jones

 

Given the above row, if we call the function with 45 as the this_boat_id parameter (i.e., get_boat_details(45)), the function should return the following string:

 

'Island Dream is owned by Sally Jones'

 

Your function should also check to see whether the boat exists. If a boat where boat_id = this_boat_id cannot be found, your function should return the string 'Sorry, but this boat is not in the database.'

 

Hint: You can concatenate two strings by using the "||" symbol (see the init_codes_multi function for an example).

 

Back to Main Page