D E C U S - I N T E R O F F I C E M E M O R A N D U M Date: 20-Dec-1990 08:27pm EST From: Winston Tellis Dept: SIG STEERING COMM.: Tel No: 203-254-4000 TO: Bart Z. Lederman ( LEDERMAN ) Subject: accent prob sol Chris Hoskins, Computer Scientist Naval Weapons Center, China Lake, California Experience: 3 years Pascal (college) 7-1/2 years FORTRAN 4 years DATATRIEVE (sparse) 1 year ACCENT R (sparse) last 4 months ACCENT R (intense) {I would like to emphasize that I am not a "database person", nor am I a 4GL expert. I consider my experience with ACCENT R very limited until I took on this project and one immediately preceding it. I made NO decisions concerning which database to use, or how to optimize the database for fast response time. Most of my time was spent reading the manuals and experimenting with several different solutions. For much of the stuff I tried, I had to bring myself up from "ground zero", to go from knowing nothing to being able to use something well in a relatively short period of time.} Time spent spread out over 6 weeks (as time permitted). Final solution took about a week, includes learning curve. Screen management implemented in about 8 hours total. Optional report implemented in about 15 minutes. All parts of the VEGASproblem were solved. All parts of the solution are in ACCENT R. < 2 seconds to find and display single MASTER/36 DETAIL records (microVAX II with RA 81). 8 seconds to generate top 100 donations report. Software Required: ACCENT R Basic System | RMSgateway |These items are all 4th Generation Tools |contained in the basic Host Language Interface |Accent R package. DB-MACH2 | User is assumed to know the purpose of the program. User must know what is meant by 'SOUNDEX'. {very little user training is required. About 1 minute of instruction was needed by a person completely unfamiliar with the problem} Count of detail records and total donations are real fields in MASTER. Date of dontation is stored as YYMMDD in DETAIL (fund code is used). Fields are keyed thru data indexes. There are several ways of solving the problem using ACCENT. I spent quite a bit of time trying to solve it using the nice 4th GL part of ACCENT, but I was unable to figure out a satisfactory method of implementing the browse backward phase. Fortunately, ACCENT is a very flexible 4GL language that can allow itself to be used almost as a 3GL, if the problem requires that level of implementation. I decided to solve the problem using what I consider a very low-tech approach, I let the problem dictate the method of solution and simply used the wide range of tools available in ACCENT to implement that solution. Upon reading the VEGAS problem, it is obvious that we are being asked to do the following: REPEAT A. Identify a particular record B. Process that record UNTIL DONE Filling in with a little more detail: REPEAT A. Identify a particular record 1. Find sub-set of MASTER records 2. Browse thru sub-set 3. Select a record B. Process that record 1. Add 2. Modify 3. Delete 4. Delete MASTER/DETAILS UNTIL DONE Let's focus a little more on this "find a sub-set" part: A. 1. Find sub-set of MASTER records Three separate types of comparisons requested. GET records by: a. ID-number b. Last_name (or part) c. SOUNDEX At this point, we know enough of how we want to approach solving the problem to define our data and how we will manipulate that data. ! schema definition for NAMES (MASTER dataset description) FORM IS ASCII ID, I, 7, ALIAS ID_NUMBER LAST, C, 15, ALIAS LAST_NAME FIRST, C, 10, ALIAS FIRST_NAME MI, C, 1, ALIAS MIDDLE_INITIAL TITLED, C, 4, ALIAS TITLE_OF_ADDRESS SENIOR, C, 4, ALIAS SENIORITY ADDR, C, 15, ALIAS ADDRESS CITY, C, 12 STATE, C, 2 ZIP, I, 5, ALIAS ZIP_CODE SOUNDEX, C, 4, ALIAS SOUNDS_LIKE TOTDON, I, 3, ALIAS TOTAL_DONATIONS TOTAMT, N, 10, 2, ALIAS TOTAL_AMOUNT ! schema def for DETAILS (DETAILS dataset description) FORM IS ASCII ID, I, 7, ALIAS ID_NUMBER DATED, D $1 YEAR, I, 2 $3 MONTH, I, 2 $5 DAY, I, 2 FCODE, I, 1, ALIAS FUND_CODE AMOUNT, V, 7, 2, ALIAS AMOUNT_OF_DONATION A Data Index (DI) is a set of pointers external to the data that allows the data to appear to be sorted in any desired way, without physically sorting the data. ! DI NAMES (data index for MASTER dataset) INDEX TYPE IS RAM INDEX TO NAMES ! four domains, or views of the data DOMAIN ID ON ID !by id number DOMAIN NAME ON LAST,FIRST,MI !by alphabetic name DOMAIN SOUNDEX ON SOUNDEX !by last name soundex value DOMAIN TOTAMT ON -TOTAMT !by descending total amount ! DI DETAILS (data index for DETAIL dataset) INDEX TYPE IS RAM INDEX TO DETAILS DOMAIN ID ON ID,-DATED !by id number, and descending date Now we know enough to set up the problem as it needs to be solved. Match by id_number involves no browse because by definition we will only match one master record. Match by soundex and match by last_name involve separate browse phases because I use different domains to look at the master data. REPEAT ENTER COMPARISON: IF (matched by ID_number) THEN select the record ELSEIF (matched by SOUNDEX) THEN browse the records select a record ELSEIF (matched by [part of] Last_name) THEN browse the records select a record ENDIF IF (a MASTER record was selected) THEN process the records ENDIF UNTIL DONE Final Solution is performed entirely in one Process Module (PM). A PM consists of procedural statements grouped into sections. Low-level record manipulation routines are used (GET NEXT, GET PRIOR). Order of the data is controlled by which Data Index is used.