From: lampson@tellabs.com (Dave Lampson)
Subject: Re: records-database
Organization: Tellabs, Inc., IL
Date: Tue, 1 Sep 1992 22:52:13 GMT

In article <CB.92Aug31122022@tamarack13.timbuk> cb@tamarack13.timbuk (Chris Brewster) writes:
>Bill Pearce writes:
>
>   ... One could use a simpler flat field database manager for this
>   purpose too.  As with most such [relational] programs, Alpha Four 
>   allows you to set up whatver kind of format you want ...
>
>Bill, I think your second statement above (and its implications) is the
>best argument against the first statement.  Data about recordings is
>actually pretty complex and difficult to manage effectively.

I agree, Chris.  But it depends on just how large your collection is, or
will be, and what you're going to do with your data.  (See next message
for more discussion of the advantages of relational vs. flat file for
this type of database - I can't resist a chance to rebut Margaret (:-))


>Maybe we should hear from Dave Lampson on how *he* structures his data
>(or anyone else with such a system already operating).

Here's the current relational database design I use.  I've been refining it 
since 1985, and in general, I think it's pretty good for my purposes.  Any 
suggestions, comments, etc. are welcome.


Key To Definitions:

The database is made up of 8 files:

        CD_CLASSICAL_MUSIC    (master file)
        [ALL_MANUFACTURERS]
        [CD_RECORDING_DATA]
        [CLASSICAL_PERFORMERS]
        [COMPOSER_DATA]
        [DIRECTOR_DATA]
        [ENSEMBLE_DATA]
        [INSTRUMENT_DATA]

Each file contains records (collections of data) which are made up of 
attributes (fields for data).  The attribute definitions are given in detail 
below.  Attributes can be of several types.  The types used in this database 
are:

        STR - (String)       This is an ASCII text string of a maximum of 255 
                               characters.
        INT - (Integer)      This is whole number value (i.e., 1, 2, 3, 4, etc.)
        DER - (Derived)      This attribute is not directly entered in the 
                               database, but rather it is derived from the data 
                               entered for other attributes.
        ENUM - (Enumerated)  This is an entry that can only take on values from 
                               a predefined valid list of values.
        SBRC - (Subrecord)   A subrecord is essentially a record within a 
                               record and has attributes of its own.
	DATE - (Date)        Date format (i.e., 11/2/90 or March 23, 1917)
	TIME - (Time)        Time format (i.e., 17:39 or 5:39 PM)

Relationships are also defined to exist between the files so that information 
can be shared between the files.  In the definitions below, the equal sign (=)
is used to denote that a relation ship exists.  For instance, in the file
CD_CLASSICAL_MUSIC, the attribute COMPOSER_SHORT_FORM is related to the
attribute of the same name (i.e., COMPOSER_SHORT_FORM) in the [COMPOSER_DATA]
file.  Only one relationship can exist between any two files, but one file 
may be related to many other files.

Derivations are simply formulas that use other attributes in the same file or 
related files to derive transient information.  For instance, in order to save 
space, the compositions listed in the CD_CLASSICAL_MUSIC file are identified 
according to short form of the composer's name (COMPOSER_SHORT_FORM) such as
SOLER.  In the [COMPOSER_DATA] file, which is related to the CD_CLASSICAL_MUSIC
file, both the short form and the long form, PADRE ANTONIO FRANCISCO JAVIER 
JOSE SOLER.  back in the CD_CLASSICAL_MUSIC file, there is a derived attribute
COMPOSER_LONG_FORM that, when it's displayed is actively retrieved from the 
[COMPOSER_DATA] file.  Thus, the information from one file is available in 
another without having to take up extra space to list it twice.  Also, since
it is listed only once, there's no chance that it will be spelled differently
between different records, and the database remains consistent.  Derivations 
may also be actual arithmetic formulas when the values are numbers, or may be 
concatenations of strings.

Restricting values to data that appears in another file also provides a way 
to ensure consistency across the database.  For example, the values of 
COMPOSER_SHORT_FORM in the master CD_CLASSICAL_MUSIC file may only take on 
values listed in the COMPOSER_SHORT_FORM field of the [COMPOSER_DATA] file.
This ensures that one record won't have SOLER misspelled as "SOLLER" or "SOLAR".

Initial values are used to automatically fill in a field within a newly created
record.  Initial values are shown below enclosed in greater than and less than 
signs ("<" and ">").  Initial values have three uses.  First, if it is known 
that a certain attribute will most often have a given value, then it saves 
effort to have that attribute initialize to that value whenever new records 
are made (ex., NUMBER_OF_PIECES is initialized to "1" when new records are 
created).  Second, an initial value may be used as a reminder for formats that 
may be different between records.  For example, you are entering a new piece 
by Schumann, and can't remember the format for the opus number (that is, you 
are entering Op. 39 and can't remember if there were more than 100 opus numbers 
in the database for Schumann so the entry should be "Op.  39" or "Op. 39").  
This type of formatting problem is important when a field is likely to be sorted
on.  And lastly, an initial value can be very useful for values that are totally
automatic and not generally edited, such as entry dates and times.  As soon as 
a new record is created, the initial values of ENTRY_DATE and ENTRY_TIME are 
set by the database to the values of <TODAY> and <NOW> respectively.

Attributes may also be multi-valued which indicates that more than one value 
of that field may be entered per record.  This is useful for attributes which 
may contain lists of values (such as the performers for Beethoven's Triple 
Concerto).

Any attribute may be indexed which simply means that the database tracks the 
attribute for all database records through the use of a separate index list.
though this list takes up a little extra disk space, it greatly speeds 
searches and sorts that involve the indexed attribute.  The database program 
automatically updates and maintains the index list as values are added are 
changed, so having indexed attributes may also slow execution slightly, 
however, no additional user interaction is required.

It is often desirable to make a given attribute mandatory.  For instance, 
all compositions in the database must have a name (ex., sonata, symphony, 
Clair de Lune, etc.).  Therefore, the COMPOSITION attribute in the master 
CD_CLASSICAL_MUSIC file is designated as mandatory so that when a new record 
is created, or an old record is altered, the program checks to make sure that 
that field has something in it.

Integer fields may also have minimums and/or maximums  associated with them.  
An example would be the CD_MINUTES or CD_SECONDS attributes which may only 
take on values from zero through 59, inclusive.



  FILE: CD_CLASSICAL_MUSIC
 Classical Music Database for CDs
 ATTRIBUTE                    TYPE   RELATIONSHIP/INITIAL VALUE/DERIVATION/ETC
 ---------                    ----  -------------------------------------------
 COMPOSER_SHORT_FORM          STR   = [COMPOSER_DATA].COMPOSER_SHORT_FORM
        Indexed & Mandatory          
                & Restrict values to: [COMPOSER_DATA].COMPOSER_SHORT_FORM
 COMPOSER_LONG_FORM           DER   [COMPOSER_DATA].COMPOSER_LONG_FORM
 YEAR_OF_BIRTH                DER   STRING([COMPOSER_DATA].YEAR_OF_BIRTH)
 YEAR_OF_DEATH                DER   STRING([COMPOSER_DATA].YEAR_OF_DEATH)
 NUMBER_OF_PIECES             INT   <1>
        Range: 1-99 & Mandatory
 COMPOSITION                  STR   
        Indexed & Mandatory
 OPUS_NUMBER                  STR   <[COMPOSER_DATA].OPUS_OR_CATALOG_#_FORMAT>
        Indexed
 DATE_OF_COMPOSITION          INT   <[COMPOSER_DATA].YEAR_OF_DEATH>
        Mandatory
 SOLO_INSTRUMENT              STR   = [INSTRUMENT_DATA].INSTRUMENT
        Indexed & Restrict values to: [INSTRUMENT_DATA].INSTRUMENT
 SOLO_PERFORMER               STR   = [CLASSICAL_PERFORMERS].PERFORMER
        Indexed & Restrict values to: [CLASSICAL_PERFORMERS].PERFORMER
 \CLASSICAL_PERFORMERS        SBRC  Multi-Valued
     SUBFILE: \CLASSICAL_PERFORMERS
     COMPOSER                 DER   CD_CLASSICAL_MUSIC.COMPOSER_SHORT_FORM
     INSTRUMENT               STR   = [INSTRUMENT_DATA].INSTRUMENT
                                      <CD_CLASSICAL_MUSIC.SOLO_INSTRUMENT>
        Indexed & Restrict values to: [INSTRUMENT_DATA].INSTRUMENT
     PERFORMER                STR   = [CLASSICAL_PERFORMERS].PERFORMER
                                      <CD_CLASSICAL_MUSIC.SOLO_PERFORMER>
        Indexed & Restrict values to: [CLASSICAL_PERFORMERS].PERFORMER
     PIECE                    DER   STRING(CD_CLASSICAL_MUSIC.NUMBER_OF_PIECES,
                                           " ",CD_CLASSICAL_MUSIC.COMPOSITION)
     OPUS_OR_CATALOG          DER   CD_CLASSICAL_MUSIC.OPUS_NUMBER
 ENSEMBLE                     STR   = [ENSEMBLE_DATA].ENSEMBLE
        Indexed & Restrict values to: [ENSEMBLE_DATA].ENSEMBLE
 ENSEMBLES                    STR   Multi-Valued
                  Restrict values to: [ENSEMBLE_DATA].ENSEMBLE
 DIRECTOR                     STR   = [DIRECTOR_DATA].DIRECTOR
        Indexed & Restrict values to: [DIRECTOR_DATA].DIRECTOR
 MANUFACTURER                 DER   = [ALL_MANUFACTURERS].MANUFACTURER
                                      [CD_RECORDING_DATA].MANUFACTURER
 SUBLABEL                     DER   [CD_RECORDING_DATA].SUBLABEL
 CATALOG_NUMBER               STR   = [CD_RECORDING_DATA].CATALOG_NUMBER
        Indexed & Mandatory
              & Restrict values to: [CD_RECORDING_DATA].CATALOG_NUMBER
 SPARS                        ENUM  <"DDD">
        Mandatory & Enumerated values: AAD,ADD,DDD,DAD,MAD,MDD
 RECORDING_DATE               INT 
        Range: 1900-1992 & Mandatory
 TOTAL_CDS_IN_SET             DER   [CD_RECORDING_DATA].NUMBER_OF_COMPACT_DISCS
 CD_HOURS                     INT   <0>
        Range: 0-9 & Mandatory
 CD_MINUTES                   INT   <0>
        Range: 0-59 & Mandatory
 CD_SECONDS                   INT   <0>
        Range: 0-59 & Mandatory
 TOTAL_CD_MINUTES             DER   (CD_HOURS * 60) + CD_MINUTES + 
                                    (CD_SECONDS / 60)
 DISC_SET_NUMBER_FRACTION     DER   (TOTAL_CD_MINUTES / 
                                     [CD_RECORDING_DATA].TOTAL_TIME) * 
                                     [CD_RECORDING_DATA].NUMBER_OF_COMPACT_DISCS
 CD_PERFORMANCE_RATING        INT   <0>
        Range: 0-10 & Mandatory
 CD_SOUND_RATING              INT   <0>
        Range: 0-10 & Mandatory
 CD_MUSIC_QUALITY_RATING      INT   <0>
        Range: 0-10 & Mandatory
 CD_TOTAL_RATING              DER   (CD_PERFORMANCE_RATING + CD_SOUND_RATING 
                                     + (CD_MUSIC_QUALITY_RATING * 2)) / 4



  FILE: [CD_RECORDING_DATA]
 CD Recordings appearing in the Classical Database
 ATTRIBUTE                    TYPE   RELATIONSHIP/INITIAL VALUE/DERIVATION/ETC
 ---------                    ----  -------------------------------------------
 MANUFACTURER                 STR   = [ALL_MANUFACTURERS].MANUFACTURER
        Indexed & Mandatory
                Restrict values to: [ALL_MANUFACTURERS].MANUFACTURER
 SUBLABEL                     STR 
        Indexed & Restrict values to: \SUBLABELS.SUBLABEL
 CATALOG_NUMBER               STR   = CD_CLASSICAL_MUSIC.CATALOG_NUMBER
                                    <[ALL_MANUFACTURERS].DEFAULT_CATALOG_NUMBER>
        Unique & Indexed & Mandatory
 LENGTH_OF_CATALOG_NUMBER     INT 
        Mandatory
 HOURS                        INT   <0>
        Minimum: 0 & Mandatory
 MINUTES                      INT 
        Range: 0-59 & Mandatory
 SECONDS                      INT 
        Range: 0-59 & Mandatory
 TOTAL_TIME                   DER   (HOURS * 60) + MINUTES + (SECONDS / 60)
 NUMBER_OF_COMPACT_DISCS      INT   <0>
        Minimum: 1 & Mandatory
 AVERAGE_MINUTES_PER_CD       DER   TOTAL_TIME / NUMBER_OF_COMPACT_DISCS
 PRICE                        INT   <12>
        Mandatory
 PRICE_PERFORMANCE_RATIO      DER   PRICE / TOTAL_TIME
 REPLACEMENT_COST             INT   <[ALL_MANUFACTURERS].DEFAULT_REPLACEMENT_
                                     COST * NUMBER_OF_COMPACT_DISCS>
        Mandatory
 FULL_PRICE_COST_RATIO        DER   REPLACEMENT_COST / TOTAL_TIME
 RELEASE_DATE                 INT 
        Range: 1900-1992 & Mandatory
 CLASSIFICATION               ENUM
        Mandatory & Enumerated values: CLASSICAL, JAZZ, ROCK, MISC
 REMARKS                      STR 
        Mandatory
 ENTRY_DATE                   DATE  <TODAY>
        Indexed & Mandatory
 ENTRY_TIME                   TIME  <NOW>
        Mandatory



  FILE: [ALL_MANUFACTURERS]
 Manufacturers appearing in the Recording Database
 ATTRIBUTE                    TYPE   RELATIONSHIP/INITIAL VALUE/DERIVATION/ETC
 ---------                    ----  -------------------------------------------
 MANUFACTURER                 STR   = [CD_RECORDING_DATA].MANUFACTURER
                                    = CD_CLASSICAL_MUSIC.MANUFACTURER
        Unique & Indexed & Mandatory
 LENGTH                       INT 
        Mandatory
 DEFAULT_CATALOG_NUMBER       STR 
 DEFAULT_REPLACEMENT_COST     INT   <15>
        Mandatory
 \SUBLABELS                   SBRC  Multi-Valued
     SUBFILE: \SUBLABELS
     SUBLABEL                 STR 
        Indexed 
     LENGTH                   INT 
        Mandatory
 MAXIMUM_TOTAL_LENGTH         DER   LENGTH + MAX (\SUBLABELS.LENGTH) +  1



  FILE: [CLASSICAL_PERFORMERS]
 Performers appearing in the Classical Music Database
 ATTRIBUTE                    TYPE   RELATIONSHIP/INITIAL VALUE/DERIVATION/ETC
 ---------                    ----  -------------------------------------------
 PERFORMER                    STR   = CD_CLASSICAL_MUSIC.SOLO_PERFORMER
        Indexed & Mandatory         = \CLASSICAL_PERFORMERS.PERFORMER
 LENGTH                       INT 
        Mandatory



  FILE: [INSTRUMENT_DATA]
 Instruments appearing in the Classical Music Database
 ATTRIBUTE                    TYPE   RELATIONSHIP/INITIAL VALUE/DERIVATION/ETC
 ---------                    ----  -------------------------------------------
 INSTRUMENT                   STR   = CD_CLASSICAL_MUSIC.SOLO_INSTRUMENT
        Indexed & Mandatory         = \CLASSICAL_PERFORMERS.INSTRUMENT
 LENGTH                       INT 
        Mandatory



  FILE: [COMPOSER_DATA]
 Composers appearing in the Classical Music Database
 ATTRIBUTE                    TYPE  RELATIONSHIP/INITIAL VALUE/DERIVATION/ETC
 ---------                    ----  -------------------------------------------
 COMPOSER_SHORT_FORM          STR   = CD_CLASSICAL_MUSIC.COMPOSER_SHORT_FORM
        Indexed & Mandatory         <COMPOSER_LONG_FORM>
 LENGTH                       INT 
 COMPOSER_LONG_FORM           STR 
 LENGTH_LONG_FORM             INT 
 OPUS_OR_CATALOG_#_FORMAT     STR 
 YEAR_OF_BIRTH                INT 
 YEAR_OF_DEATH                INT 
 AGE                          DER   YEAR_OF_DEATH - YEAR_OF_BIRTH
 EXACT_BIRTH_DATE             DATE      
 EXACT_DEATH_DATE             DATE      
 LIFESPAN                     DER   (INTERVAL_DATE(EXACT_DEATH_DATE,
                                     EXACT_BIRTH_DATE)) / 365.24



  FILE: [DIRECTOR_DATA]
 Directors/Conductors appearing in the Classical Music Database
 ATTRIBUTE                    TYPE   RELATIONSHIP/INITIAL VALUE/DERIVATION/ETC
 ---------                    ----  -------------------------------------------
 DIRECTOR                     STR   = CD_CLASSICAL_MUSIC.DIRECTOR
        Indexed & Mandatory
 LENGTH                       INT 
        Mandatory



  FILE: [ENSEMBLE_DATA]
 Ensembles/Orchestras appearing in the Classical Music Database
 ATTRIBUTE                    TYPE   RELATIONSHIP/INITIAL VALUE/DERIVATION/ETC
 ---------                    ----  -------------------------------------------
 ENSEMBLE                     STR   = CD_CLASSICAL_MUSIC.ENSEMBLE
        Indexed & Mandatory
 LENGTH                       INT 
        Mandatory



-- 

Dave
lampson@tellabs.com

For detailed lists of Recommended Classical CDs, Basic Repertoire and Buying
Guide access cs.uwp.edu (131.210.1.4) by anonymous FTP, or email me for info.

Contribute to the rec.music.classical biography project!  Email me your bio!

Newsgroups: rec.music.classical