Chapter 11 Exercise 2

2. Assuming your DBMS cannot generate distributed safe keys automatically write a procedure to generate key values based on a location.

Details depend on the specific DBMS.
Basic structure is to create a table that assigns a prefix number based on location. This step could be hard-coded, but it is better to make it flexible. Then, you need a value generator. In Oracle, you can use the Sequences and simply add the generated value to the prefix/base value. It is harder with other systems unless you use a big random number generator. To use sequential, you need to store the current value in a table and increment it, but you need to use concurrency testing to avoid issuing the same number to two processes. On the other hand, Access and SQL Server support distributed generated numbers directly.

Basic structure (needs to be customized to specific DBMS):
Function GetLocalCustomerID(strLocation as string) as number

Begin
  SELECT Prefix INTO BaseNo
  WHERE Location=strLocation;
  R = rnd()*100000000;
  Return R+BaseNo;
End