Re: What is Pick anyway?

David Cressey wrote:
> "DonR" <donr_work@xxxxxxxxx> wrote in message
> news:1135744411.676896.96910@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> > David Cressey wrote:
>
<snip>
>
> The MV database model basically is, you can store anything anyway you
> > want. The dictionary and program logic defines the meaning of the data.
> > As I stated in a previous post, the dictionary is optional if you want
> > to program EVERYTHING. However, I consider a good dictionary a must.
> > The dictionary is required for access from outside the MV environment
> > such as via ODBC.
> >
>
> Say some more about the dictionary. You consider a good dictionary a must.
> I consider an explicit data model a must. You and I may not be as far apart
> as it seems at first.
>
A dictionary is a file normally associated with one data file but
sometimes multiple data files. The records in the dictionary are of two
basic types; the first type defines a column (field, attribute) in the
data file and the second type calculates a value based on the contents
of one or more columns in one or more files.

As an example, I'll use PEOPLE and POSTAL files. In the dictionary for
the PEOPLE file, I'll create records called ID, FIRSTNAME, LASTNAME,
POSTALCODE, CITY and STATE. In these records, I'll define ID as field
0, the key, FIRSTNAME as field 1, LASTNAME as field 2, POSTALCODE as
field 3, CITY and STATE as a lookups (join) into the POSTAL file.

I'll define the POSTAL file with POSTALID as field 0, the key, CITY as
field 1 and STATE as field 2.

Once data is added to the files, I can use commands like these to list
data from the files,
LIST PEOPLE ID FIRSTNAME LASTNAME POSTALCODE

ID FIRSTNAME LASTNAME POSTALCODE
------- ---------------- ---------------- ---------------------
1 Don Robinson 45678


LIST POSTAL POSTALID CITY STATE

POSTALID CITY STATE
----------------- ---------------- ----------
45678 Cincinnati Ohio


Then I can list the combined contents of the files,
LIST PEOPLE ID FIRSTNAME LASTNAME POSTALCODE CITY STATE

ID FIRSTNAME LASTNAME POSTALCODE CITY STATE
------- ---------------- --------------- --------------------
-------------- ---------
1 Don Robinson 45678
Cincinnati Ohio

Here's how it works.
The LIST command gets the id, first name, last name and postal code
from the data in the PEOPLE file. Then using the postal code, it reads
the POSTAL file using 45678 as the key to get the city and state. The
CITY and STATE records define what file to read, what value to use as
the key and what field to retrieve.

In addition to defining the field or calculating a value, the
dictionary records contain a justification and length. The LIST command
uses the justification, L or R, for display of the data and for sorting
as alpha or numeric. The length is used to control the display width of
a column but has no effect on the amount of data stored in the record.

One reason the dictionary is optional is that MV system have a master
dictionary file that you can use to store generic field definitions for
use by the LIST command. Another reason is that the dictionary is used
to display data but is not used to control what goes into the file when
using a program, editor, etc. This means that data can be loaded into a
file with out creating a dictionary.

I consider the dictionary necessary for several reasons, one of which
is documentation. I've seen programs use fields that weren't defined
and then someone tries to use the same field and wonders why there data
is getting messed up!

I know that some relational databases require field definitions before
you can load data, do all of them?


> > > I may not have described the scenario I've outlined very well, but I
> regard
> > > the scenario as typical of database work.
> >
> > As a side note, one of the most popular ETL (Extract, Translate, Load)
> > programs, Datastage, uses Universe as its engine. Such a program could
> > be used to validate and load data from and to multiple sources
> > including relational databases.
> >
>
> Ahhh, ETL. Good. This is where "garbage in, garbage out" meets its match.
>
>
> Say some more about Universe. How does it relate to Pick? Can the "L" part
> of ETL be loading the data into a relational database? Is this common
> practice?

Dawn answered this but I'll add a little. Universe is just one of about
a dozen MV (Pick-like) systems available today. Over the years, Pick
Systems ported/licensed Pick to about 30 hardware vendors. In addition,
various software vendors created their own Pick-like systems. Prime
Information was one of the biggest vendors in the early 90's. Some
features of Prime are incorporated in Universe.

Today, the MV basic programming language and other features are being
added to Cache, an object database system.

As illustrated by Datastage, MV systems are very good at string
manipulation, pattern matching, etc.

Cheers,
Don

.

Relevant Pages

  • Re: What is Pick anyway?
    ... CITY and STATE as a lookups into the POSTAL file. ... > LIST PEOPLE ID FIRSTNAME LASTNAME POSTALCODE ... > an attribute and multiple sub-values in a value and in some versions, ... In a simple SQL table design, you would need to reserve a certain number of ...
    (comp.databases.theory)
  • Re: NULLs
    ... of the phone number is in turn functionally dependant on the city. ... in the vast majority of applications, this is not useful at all. ... Persons, Street, HouseNo, PostalCode, City, PhoneNo) ... to be adequately normalized - even when purists would prefer a design ...
    (comp.databases.theory)
  • Internal Passport Database - Additional records added
    ... 4,000 records from the city of Kaunas, and 550 records from the city of ... have been added to the Lithuania Internal Passport Database. ... though the internal passport was applied for in Kaunas or in Panevezys, ...
    (soc.genealogy.jewish)
  • Re: mail merge with a word database
    ... City, ST Zip¶ ... some business have 20-25 whiole others ... To answer your question, the database form is consistant, it starts with ... should be possible to create a macro that will manipulate that data into ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Database Design...
    ... nine as the address and used the PO database to look up the actual address. ... we had to modify the app to store city and state ... I would disagree that State is dependent on the City. ... Jacksonville can be found in Texas, ...
    (microsoft.public.access.tablesdbdesign)