ug_ch4b.htm

db.* (pronounced dee-be star) is an advanced, high performance, small footprint embedded database fo

<p>Sets, however, only implement one-to-many relationships. On the
surface, it might appear that the following incorrect
implementation using two sets would work.</p>
<pre>
<font color="#0000FF">record class {
   unique key char class_id[6];
   ... other fields
}
record student {
   key char name[36];
   ... other fields
}
set my_students {
   order last;
   owner class;
   member student;
}
set my_classes {
   order last;
   owner student;
   member class;
}
</font>
</pre>
<p><font size="2">The schema diagram in Figure 4-1 illustrates the
relationships.</font></p>
<p align="center">&nbsp;</p>
<p align="center"><img alt="dbstar_4-1.gif - 2336 Bytes" border="0"
height="173" src="dbstar_4-1.gif" width="233"></p>
<p align="center">Fig. 4-1. Incorrect Many-to-Many
Implementation</p>
<p>Consider, however, the following instance of the
<b>my_students</b> set.</p>
<pre>
<font color="#0000FF">Class:   Computer Science 101 (CS101)

Students:   Smith
      Jones
      Kelly
      Carlson
</font>
</pre>
<p><font size="2">A problem occurs when the <b>my_classes</b> set
instances for these students is examined. Each student in CS101
must have that same class as a member of his <b>my_classes</b> set
instance. This is not possible, however, since CS101 can only be
connected to a single owner in the <b>my_classes</b>
set.</font></p>
<p>The correct technique for implementing many-to-many
relationships does indeed utilize two sets but through the use of
an intersection record type, as follows:</p>
<pre>
<font color="#0000FF">record class {
   unique key char class_id[6];
   ... other fields
}
record student {
   key char name[36];
   ... other fields
}
record intersect {
} 
set my_students {
   order last;
   owner class;
   member intersect;
}
set my_classes {
   order last;
   owner student;
   member intersect;
}
</font>
</pre>
<p><font size="2">Figure 4-2 shows the schema diagram that
corresponds to the above DDL.</font></p>
<p align="center">&nbsp;</p>
<p align="center"><img alt="dbstar_4-2.gif - 2237 Bytes" border="0"
height="125" src="dbstar_4-2.gif" width="250"></p>
<p align="center">Fig. 4-2. Correct Many-to-Many Implementation</p>
<p>Each <b>student</b> record occurrence will have its own set of
<b>intersect</b> record occurrences through the <b>my_classes</b>
set. Each of these intersect records is also owned by a specific
class record through the <b>my_students</b> set. Similarly, each
<b>class</b> record will have its own set of <b>intersect</b>
record occurrences through the <b>my_students</b> set, each of
which is owned by an individual student record through the
<b>my_classes</b> set.</p>
<p>In this case the intersection record has no user-defined data in
it. However, it could be an ideal place to store the student's
grade for the class if that was needed. In that case you would
probably want to change the name of the record type from
<b>intersect</b> to <b>grade</b> or some other more meaningful
name.</p>
<p>A description of the actual database manipulation involved in
maintaining many-to-many relationships, based on the database
design example presented in section 4.5, is provided in Chapter
5.</p>
<h5>Variable-length Records</h5>
<p><font size="2">Only fixed-length records are allowed in
<b><i>db.*</i></b>. In systems where variable-length records are
supported, they are often implemented by storing the
variable-length information in a linked list of fixed-length
segments. This technique can be implemented by the
<b><i>db.*</i></b> application developer through sets.</font></p>
<p>In the simplest example, a note of arbitrary length is
associated with a record such as a customer record. If the note is
stored as a field in the customer record, then an arbitrary limit
on the length of the note text would need to be imposed. Much of
the space taken up by the field would never be used. If, however, a
separate record type were defined to store a single line of text, a
set relationship between the customer and note text line would
allow any number of text lines to be connected to a particular
customer. The schema diagram in Figure 4-3 shows this
relationship.</p>
<p align="center">&nbsp;</p>
<p align="center"><img alt="dbstar_4-3.gif - 1699 Bytes" border="0"
height="139" src="dbstar_4-3.gif" width="135"></p>
<p align="center">Fig. 4-3. Example Variable-Length Text
Implementation</p>
<p>The corresponding DDL statements are shown below.</p>
<pre>
<font color="#0000FF">record customer {
   unique key char cust_id[7];
   char company[21];
   ... other fields
}
record text {
   char line[80]; 
}
set cust_notes {
   order last;
   owner customer;
   member text;
}
</font>
</pre>
<p><font size="2">Use of multiple member sets can save even more
space. Consider the alternative schema diagram in Figure
4-4.</font></p>
<p align="center"><b><img alt=
"Better Variable-Length Text Implementation" border="0" height=
"145" src="dbstar_4-4.gif" width="298"></b></p>
<p align="center">Fig. 4-4. Better Variable-Length Text
Implementation</p>
<p>The DDL corresponding to this implementation follows:</p>
<pre>
<font color="#0000FF">record customer {
   unique key char cust_id[7];
   char company[21];
   ... other fields
}
record text30 {
   char t30_line[30]; 
}
record text55 {
   char t55_line[55];
}
record text80 {
   char t80_line[80];
}
set cust_notes {
   order last;
   owner customer;
   member text30;
   member text55;
   member text80;
}
</font>
</pre>
<p><font size="2">Multiple member sets allow any occurrences of the
record types that are defined as valid members to exist in the same
set instance. Thus, in this example, a customer record may have
<b>text30</b>, <b>text55</b>, and <b>text80</b> record occurrences
connected through the <b>cust_notes</b> set. This allows the
program to choose the record type that best fits the amount of text
actually entered for a given line of text. Note, however, that each
of the text record types should be contained in a separate data
file, or no real space savings will occur (see section 4.4.2,
"Physical Design Considerations").</font></p>
<p>The C code that manages the <b>cust_notes</b> set connections is
described in Chapter 5, "Database Manipulation."</p>
<h4><a name="Multiple" id="Multiple"></a>Use of Multiple
Databases</h4>
<p><font size="2">Application programs can open and access more
than one database at a time. The use of multiple databases in an
application can yield certain advantages and therefore should be a
consideration in the design of the database. In this sense, an
application's database may actually consist of several
<b><i>db.*</i></b> databases.</font></p>
<p>There are at least two situations in which multiple database
design is useful:</p>
<ul>
<li>A temporary control or working database is desired</li>
<li>An application is to consist of optional, modular
components</li>
</ul>
<p>Sometimes temporary application control information needs to be
manipulated in sufficient quantities to benefit from the use of
database operations on that data. This information can be stored in
a temporary working database, which is initialized when the program
begins and deleted when the program terminates.</p>
<p>An accounting package is a good example of a modular database
application. Most such packages provide separate modules for
various accounting functions. Companies only purchase the modules
they need. A typical package would include these modules :</p>
<div style="margin-left: 4em">
<p>General Ledger</p>
<p>Accounts Receivable</p>
<p>Accounts Payable</p>
<p>Payroll</p>
<p>Inventory</p>
<p>Sales Orders</p>
</div>
<p>Almost all the modules require the general ledger module. The
amount of shared information between the other modules depends on
the application.</p>
<p>When designing an application that will use multiple databases,
keep in mind that inter-database relationships can exist, but can
only be implemented relationally through keys or through the use of
<b>DB_ADDR</b> fields containing the database addresses of records
in another database. It is not possible to have a set with an owner
defined in one database and a member defined in another. However,
an account number stored in the accounts receivable database can be
used to find the associated account record in the general
ledger.</p>
<h3><a name="Physical" id="Physical"></a>4.4.2 Physical Design
Considerations</h3>
<p><font size="2">This section describes those aspects of the
physical characteristics of a <b><i>db.*</i></b> database that can
impact the disk space usage and access performance of an
application program. An understanding of these physical
implementation issues will help you design the most efficient
databases possible under <b><i>db.*</i></b>.</font></p>
<h4><a name="FileStructure" id="FileStructure"></a>The <i>db.*</i>
File Structure</h4>
<p><font size="2">Data and key files consist of pages, which
contain a specific number of fixed-length record and key slots.
Control information, as well as the normal record and key contents,
are stored in these fixed-length slots. The size of the record and
key slots in a file is based on the size of the largest record or
key contained in that file. Smaller records or keys will of
necessity contain unused slot space.</font></p>
<p>The amount of control information in a key is always 10 bytes.
However, the amount of space required for the control information
in a record varies, and can therefore have database design
implications.</p>
<p>The control information maintained by <b><i>db.*</i></b> in a
record is as follows:</p>
<ul>
<li>Record number (two bytes), required</li>
<li>Database address (four bytes), required</li>
<li>Optional key bit maps (one byte for every eight optional keys
defined in the record)</li>
<li>If record type is timestamped, creation timestamp (four
bytes)</li>
<li>If record type is timestamped, update timestamp (four
bytes)</li>
<li>One set pointer (12 or 16 bytes) for each set for which record
type is defined as an owner. A set pointer for a timestamped set
uses 16 bytes, otherwise only 12 bytes are used</li>
<li>One member pointer (12 bytes) for each set for which record
type is defined as a member</li>
</ul>
<p>The information to be considered in database design is the space
required for set and member pointers, as this is determined from
the DDL.</p>
<p>Chapter 14, "File Formats and Dictionary Tables," describes in
detail the structure of <b><i>db.*</i></b> files, records, and
keys.</p>
<h4><a name="Placement" id="Placement"></a>Record and Key
Placement</h4>
<p><font size="2">The factors that dictate the optimal placement of
records and keys into files are not always easy to determine, and
are sometimes even mutually exclusive. Here are some "rules of
thumb" to assist you. Some of these involve conflicting
requirements. The best determination for your particular
environment will be based on your own intuition supported by some
testing, but ultimately through experience gained from actual
use.</font></p>
<ul>
<li>Place dissimilar sized records and keys in separate files.</li>
<li>Minimizing the slot space that would be unused in the smaller
records is the motivation for this guideline.</li>
<li>Place records and keys in separate files for better multi-user
concurrency.</li>
</ul>
<div style="margin-left: 4em">
<p>The chances of multiple users locking the same file is increased
when several record or key types are contained in the same file.
Placing each key and record type in its own file will minimize
access conflicts.</p>
</div>
<ul>
<li>Place owner and member record types in the same file for
improved set access performance.</li>
</ul>
<div style="margin-left: 4em">
<p>If new owner and member record occurrences are stored and
connected in the same transaction and if they are located on the
same file the likelihood of their being placed on the same database
page is increased. This could yield better set access
performance.</p>
</div>
<ul>
<li>Minimize the number of separate key files to improve cache
performance.</li>
</ul>
<div style="margin-left: 4em">
<p>Caching is a technique in which frequently accessed database
pages are kept in memory, thus reducing the amount of actual disk
input and output required. Using fewer key files increases the
probability that needed index pages will be in the cache.</p>
</div>
<ul>
<li>Experiment with the size of the pages to change the number of
slots per page.</li>
</ul>
<div style="margin-left: 4em">
<p>Once the application is built, there will be only a fixed amount
of memory left over that can be used for the <b><i>db.*</i></b>
cache. Some applications get better performance from a smaller
number of larger-sized pages that contain more slots per page.
Other applications get better performance from a larger number of
smaller-sized pages that contain fewer slots per page.</p>
</div>
<h4><a name="Report" id="Report"></a>ddlp File Structure
Report</h4>
<p><font size="2">The File Structure Report is produced by using
the <b>-r</b> option on the <b>ddlp</b> command line. This report
summarizes the physical characteristics for each file and record
type defined in the DDL. The report produced for the checking
account database is given below.</font></p>
<pre>
<font color="#0000FF">db.* 1.0.0 Summary of Database: ckngacct
Fri Dec 17 15:05:41 1999

FILE: chkg.dat
   Id  : 0