Scenario: Random Customer
After the end of each month company runs a bonanza gift for its registered customers.
Each month their application will randomly pick the name of one registered customer to be rewarded with a bonanza gift.
To support the above, the following database objects are required:
- The company keeps the ids, names, type and phone number information of all its customers in the CUSTOMER table.
- There is a view called REGISTERED_CUSTOMERS that contains the records of all registered customers only.
- There is another view called MONTH_CUSTOMER defined on top of REGISTERED_CUSTOMERS view. This view selects
the name of random customer based on output from REGISTERED_CUSTOMERS.
- There is a function called FULL_NAME that returns the full name of customer based on an customer's id.
Solution
Please note the order of CREATE statements:
- CREATE view MONTH_CUSTOMER, even though the underlying REGISTERED_CUSTOMERS view does not exist yet.
- CREATE view REGISTERED_CUSTOMERS before the underlying function FULL_NAME and underlying column CUSTOMER_TYPE of base table CUSTOMER is created
- CREATE Function FULL_NAME to get the full name of customers.
- ALTER the base table CUSTOMER to add a new column CUSTOMER_TYPE to store the type of customer.
- REORG base table CUSTOMER.
- UPDATE column CUSTOMER_TYPE.
Demonstrated Features
This demonstrates the ability of DB2 9.7 to create objects tolerant of certain errors, like a missing object.
After running the SQL statements, you will see several WARNING in the Console - lower right panel.
Despite the warning messages, the commands did work. Warnings were returned to inform
you that dependent objects have not yet been created.