What is the name of the rule that states that either each foreign key value must match a primary key value in another relation or the foreign key value must be null?

Foreign Keys

As you know, a foreign key is a column (or combination of columns) that is exactly the same as the primary of some table. When a foreign key value matches a primary key value, we know that there is a logical relationship between the database objects represented by the matching rows.

One of the major constraints on a relation is referential integrity, which states that every non-null foreign key must reference an existing primary key value. To maintain the integrity of the database, it is vital that foreign key constraints be stored within the database's data dictionary so that the DBMS can be responsible for enforcing those constraints.

To specify a foreign key for a table, you add a FOREIGN KEY clause:

FOREIGN KEY foreign_key_name (foreign_key_columns) REFERENCES primary_key_table (primary_key_columns) ON UPDATE update_option ON DELETE delete_option

Each foreign key–primary key reference is given a name. This makes it possible to identify the reference at a later time—in particular, so you can remove the reference if necessary.

Note: Some DBMSs, such as Oracle, do not support the naming of foreign keys, in which case you would use preceding syntax without the name.

The names of the foreign key columns follow the name of the foreign key. The REFERENCES clause contains the name of the primary key table being referenced. If the primary key columns are named in the PRIMARY KEY clause of their table, then you don't need to list the primary key columns. However, if the columns are not part of a PRIMARY KEY clause, you must list the primary key columns in the REFERENCES clause.

The final part of the FOREIGN KEY specification indicates what should happen with a primary key value being referenced by a foreign key value that is updated or deleted. Three options apply to both updates and deletions and there is one additional option for each:

SET NULL: Replace the foreign key value with null. This isn't possible when the foreign key is part of the table's primary key.

SET DEFAULT: Replace the foreign key value with the column's default value.

CASCADE: Delete or update all foreign key rows.

NO ACTION: On update, make no modifications of foreign key values.

RESTRICT: Do not allow deletions of primary key rows.

The complete declarations for the Antique Opticals database tables, which include foreign key constraints, can be found in Figure 9-4. Notice that although there are no restrictions on how to name foreign keys, the foreign keys in this database have been named to indicate the tables involved. This makes them easier to identify if you need to delete or modify a foreign key at a later date.

Foreign Keys

As you know, a foreign key is a column (or combination of columns) that is exactly the same as the primary of some table. When a foreign key value matches a primary key value, we know that there is a logical relationship between the database objects represented by the matching rows.

One of the major constraints on a relation is referential integrity, which states that every nonnull foreign key must reference an existing primary key value. To maintain the integrity of the database, it is vital that foreign key constraints be stored within the database’s data dictionary so that the DBMS can be responsible for enforcing those constraints.

To specify a foreign key for a table, you add a FOREIGN KEY clause:

Each foreign key–primary key reference is given a name. This makes it possible to identify the reference at a later time, in particular, so you can remove the reference if necessary.

Note: Some DBMSs, such as Oracle, do not support the naming of foreign keys, in which case you would use preceding syntax without the name.

The names of the foreign key columns follow the name of the foreign key. The REFERENCES clause contains the name of the primary key table being referenced. If the primary key columns are named in the PRIMARY KEY clause of their table, then you don’t need to list the primary key columns. However, if the columns aren’t part of a PRIMARY KEY clause, you must list the primary key columns in the REFERENCES clause.

The final part of the FOREIGN KEY specification indicates what should happen when a primary key value being referenced by a foreign key value is updated or deleted. There are three options that apply to both updates and deletions and one additional option for each:

SET NULL: Replace the foreign key value with null. This isn’t possible when the foreign key is part of the primary key of its table.

SET DEFAULT: Replace the foreign key value with the column’s default value.

CASCADE: Delete or update all foreign key rows.

NO ACTION: On update, make no modifications of foreign key values.

RESTICT: Do not allow deletions of primary key rows.

The complete declarations for the Antique Opticals database tables, which include foreign key constraints, can be found in Figure 11.4. Notice that, although there are no restrictions on how to name foreign keys, the foreign keys in this database have been named to indicate the tables involved. This makes them easier to identify, if you need to delete or modify a foreign key at a later date.

Clustering

Clustering and partitioning often go together, depending on the reason for partitioning and the way in which specific DBMSs support it. Whether or not partitioning is used, choosing the best clustering sequence can dramatically reduce I/O and improve performance.

The general concept behind clustering is that as the database is modified, the DBMS will attempt to keep the data on physical pages in the same order as that specified in the clustering index. But each DBMS does this a little differently. One DBMS will cluster each time an insert or update is processed. Another will make a valiant attempt to do that. A third will only cluster when the table is reorganized. But regardless of the approach, the result is to reduce physical I/O by locating data that is frequently accessed together as physically close together as possible.

Early DBMSs only allowed one clustering index, but newer releases often support multiple clustering sequences, sometimes called indexed views or multi-dimensional clustering.

It is important to determine the most frequently used access paths to the data. Often the most frequently used access paths are ones based on one or more foreign keys. For asserted version tables, currently asserted current versions are usually the most frequently queried data.

Sometimes, the right combination of foreign keys can provide good clustering for more than one access path. For example, suppose that a policy table has two low cardinality TFKs, product type and market segment, and that each TFK value has thousands of related policies.3 We might then create this clustering index:

{circa_asr_flag, product_type_oid, market_segment_oid, eff_end_dt, policy_oid}

The circa flag would cluster most of the currently asserted rows together, keeping them physically co-located under the lower cardinality columns. Clustering would continue based on effective date, and then by the higher cardinality object identifier of the table. This will provide access via the product type oid, and will tend to cluster the data for current access for three other potential search indexes:

{product_type_oid, circa_asr_flag, eff_end_dt};

{market_segment_oid, circa_asr_flag, eff_end_dt}; or

{policy_oid, circa_asr_flag, eff_end_dt}.

Temporal Referential Integrity

Another consequence of breaking the one-to-one correspondence between objects and rows is that the relational rule of referential integrity breaks down when the parent table in a referential integrity relationship is an asserted version table. In that case, the parent in any instance of that relationship is not a row; rather, it is an episode, and it may consist of any number of rows.

Referential integrity (RI) reflects an existence dependency. If a child row is RI-dependent on a parent row, this is based on the fact that the object represented by the child row is existence-dependent on the object represented by the parent row. Therefore, a child row cannot be inserted into the database unless its referenced parent row is already present, and that parent row cannot be deleted from the database as long as any child row referencing it is present.

The same logic is at work in the case of temporal referential integrity. If there is an existence dependency between a parent object and a child object—between a client and a policy, for example—then we cannot assert that the policy is ever in effect when the client is not. It follows that no change which reduces the total number of clock ticks in which a child policy is in effect, and no change which increases the total number of clock ticks in which a parent client is in effect, can create a TRI violation. And so the AVF never has to enforce TRI on a child-side temporal delete or a parent side-temporal insert. As for temporal updates, they never alter the number of clock ticks in which an object is represented. And so, unless they change the parent object for a version, there is no TRI enforcement needed.

By the same token, any change which increases the total number of clock ticks in which a child policy is in effect, and any change which decreases the total number of clock ticks in which a parent client is in effect, can create a TRI violation. And so the AVF must enforce temporal referential integrity on parent-side temporal delete transactions, and also on child-side temporal inserts.

Storing Transaction Data in a Lightly Dimensionalized Format

The hyper generalized form transforms business entities and their relationships into triples and their attributes into shredded storage. All that remains to manage from the source data is the transaction information that depicts events in the life of the business. The hyper generalized form leaves this information in a relational structure that closely resembles how it is received from the source systems. When the HGF warehouse captures each transaction record, it simply replaces any natural foreign key values with new surrogate keys that relate it to the proper entities in the associative data store, arriving at a “lightly dimensionalized” format for facts.

In processing source information, HGF applications typically read through the input tables that represent the qualifiers first, identifying the business objects and storing them away with object identifiers or OIDs. When it later processes the transaction data (which may or may not be found in the same extracts just scanned for qualifier information), the HGF transform needs only to (1) identify the dimensional entities associated with each transaction and (2) save away the measures representing the transaction. The remaining qualifier information can be ignored during transaction capture because it was processed earlier and stored as things, links, and attributes. By identifying the qualifiers ahead of time, the HGF transform can translate them into OIDs to be stored on the transaction record so that they can be rejoined later to the measures and provide context for the events. Some transaction source data contain complex grains, so the developers will have to process those data sets multiple times, with each pass capturing the set of transactions for a particular level of granularity.

Transaction records do not need effectivity metadata. They will retain instead their event date. The HGF repository already maintains effectivity dates for everything that a transaction will be tied to, as well as its associated links and attributes. The effectivity dates on these other elements allow the context for an event to be re-created for any arbitrary point in time, so there is no need to treat a transaction extract as anything more than a stream of timestamped observations of business events.

The Temporal Foreign Key

A temporal foreign key (TFK) is analogous to a normal foreign key, and serves much the same purpose; but it is one which the DBMS does not know how to use. A foreign key, at the schema level, points from one table to a table it is dependent on. At the row level, a foreign key value points from one row in the former table to one row in the latter table.

At the schema level, a TFK also points from one table to a table it is dependent on. But at the row level, it points from one row, which is a version, to a group of one or more rows which make up an episode of the object whose oid matches the oid in the temporal foreign key.

So a TFK does not point to a specific row in its referenced table. But it also does not point to the episode which the version containing it is dependent on. It points only to an object. In our example, it says only that this version of policy P861 belongs to client C882. But since no row in particular represents a client, i.e. since clients are themselves versioned, and since their versions are also asserted, the TFK points to no row in particular. Since there may be multiple episodes for any object, the TFK points to no episode of that object in particular.

The very existence of a TFK instance does make the claim, however, that there is an episode of the designated object in the referenced table, and that the effective time period of that episode in the referenced table includes, i.e. is filled by ([fills−1]), the effective time period of the version which contains the referring TFK. And it is one of the responsibilities of the Asserted Versioning Framework to insure that for every TFK instance in a database, there is exactly one such episode. Although the TFK, by itself, does not designate this parent episode, the TFK together with the assertion and effective time periods on the child row does designate it.

Representing Data Relationships

In the preceding section we alluded to the use of identifiers in more than one relation. This is the one way in which relational databases represent relationships between entities. To make this concept clearer, take a look at the three tables in Figure 5.3.

Each table in the illustration is directly analogous to the entity by the same name in the Antique Opticals ER diagram. The orders table (the Order entity) is identified by an order number, an arbitrary unique primary key assigned by Antique Opticals. The items table (the Item entity) is identified by an item number, which could be another arbitrary unique identifier assigned by the company (often called an SKU, for stock keeping unit) or a UPC.

The third table—order item (the Order Item entity)—tells the company which items are part of which order. As you saw earlier in this chapter, this table requires a concatenated primary key because multiple items can appear on multiple orders. The columns in this primary key, however, have more significance than simply uniquely identifying each row. They also represent a relationship between the order items, the orders on which they appear, and the items being ordered.

The item number column in the order item relation is the same as the primary key of the item table. This indicates a one-to-many relationship between the two tables. By the same token, there is a one-to-many relationship between the order and order item tables because the order number column in the order item table is the same as the primary key of the orders table.

When a table contains a column (or concatenation of columns) that is the same as the primary key of some table in the database, the column is called a foreign key. The matching of foreign key values to primary key values represents data relationships in a relational database. As far as the user of a relational database is concerned, there are no structures that show relationships other than the matching column’s values.

Note: This is why the idea that relational databases have “relationships between files” is so absurd. The relationships in a relational database are between logical constructs—tables—and nothing else. Such structures make absolutely no assumptions about physical storage.

Foreign keys may be part of a concatenated primary key, or they may not be part of their table’s primary key at all. Consider, for example, a pair of simple Antique Opticals customer and order relations:

The customer number column in the order table is a foreign key that matches the primary key of the customer table. It represents the one-to-many relationship between customers and the orders they place. However, the customer number is not part of the primary key of its table; it is a nonkey attribute that is nonetheless a foreign key.

Technically, foreign keys need not have values unless they are part of a concatenated primary key; they can be null. For example, there is no theoretical reason that the orders relation above must have a value for the customer number. It is not part of the primary key of the table. However, in this particular database, Antique Opticals would be in serious trouble if customer numbers were null: There would be no way to know which customer placed an order!

A relational DBMS uses the relationships indicated by matching data between primary and foreign keys. For example, assume that an Antique Opticals employee wanted to see what titles had been ordered on order number 11102. First, the DBMS identifies the rows in the order item table that contain an order number of 11102. Then, it takes the item numbers from those rows and matches them to the item numbers in the item table. In the rows where there are matches, the DBMS retrieves the associated data.

Concatenated Foreign Keys

A foreign key does not necessarily need to be made up of a single column. In some cases, you may have a concatenated foreign key that references a concatenated primary key. As an example, let us consider a very small accounting firm that uses the following relations:

Because the firm is so small, the database designer decides that employee numbers are not necessary and, instead, uses the accountants’ first and last names as the primary key of the accountant table. The job table, used to gather data about one accountant preparing one year’s tax returns for one customer, uses the tax year and the customer number as its primary key. The form table that stores data about the forms that are part of a specific tax return uses the concatenation of the form’s ID and the primary key of the project table for its primary key.

A foreign key is the same as the complete primary key of another table. Therefore, the acct_first_name attribute by itself in the job table is not a foreign key; neither is the acct_last_name attribute. If you concatenate them, however, then they are the same as the primary key of the accountant table and, in fact, this is the unit with which referential integrity should be enforced.

Assume that “Jane Johnson” is working on customer 10100’s 2017 tax return. It is not enough to ensure that “Jane” appears somewhere in the first name column in the accountant table and “Johnson” appears anywhere in the last name column in the accountant table. There could be many people named “Jane” and many with the last name of “Johnson.” What we need to ensure is that there is one person named “Jane Johnson” in the accountant table, the concatenation of the two attributes that make up the primary key.

The same holds true for the concatenated foreign key in the form table: the tax year and the customer number. A row with a matching pair must exist in the job table before referential integrity is satisfied.

Foreign Keys That Reference the Primary Key of Their Own Table

Foreign keys do not necessarily need to reference a primary key in a different table; they need only reference a primary key. As an example, consider the following employee relation:

A manager is also an employee. Therefore, the manager ID, although named differently from the employee ID, is actually a foreign key that references the primary key of its own table. The DBMS will, therefore, always ensure that whenever a user enters a manager ID, that manager already exists in the table as an employee.

Foreign Keys and Referential Integrity

If you look back at Figure 1-8, you'll notice that some attributes appear in more than one entity. For example, you can see that the engine entity contains the VIN of the car into which it is inserted. This is how a relational database shows data relationships. When VIN in the engine entity has a value, it represents the relationship with a specific occurrence of car. Any attribute in an entity that is the same as the entire primary key of another entity is known as a foreign key. Table 1-1 lists all the foreign keys in the car database we have been developing.

Table 1-1. Foreign keys in the database design in Figure 1-8

A foreign key can be null. For example, if an engine isn't installed in a car, then the VIN attribute in the Engine entity will be null. However, foreign keys that are part of a primary key, such as the URL and VIN attributes in the listing entity, must have values to satisfy entity integrity.

When foreign keys are non-null, a matching primary key value must exist in the table referenced by the foreign key. When a car has an owner, for example, a row with the matching owner_numb must exist in the Owner table. Otherwise, it will be impossible to find information about that owner. This property is known as referential integrity: Every non-null foreign key value must reference an existing primary key value. As you will see throughout this book, much of what you do with SQL involves retrieving matching data using primary key-foreign key relationships.

Foreign keys are not limited to single columns; they can be concatenated, just like primary keys. As an example, consider a part of the database design for a very small accounting firm in Figure 1-9. Because the firm is so small, the database designer decides that employee numbers aren't necessary and instead uses the accountants' first and last names as the primary key of the accountant table. The project table, used to gather data about one accountant preparing one year's tax returns for one customer, uses the tax year and the customer number as its primary key. However, it has three foreign keys. (We'll get to those in a moment.) The form table that stores data about the forms that are part of a specific tax return uses the concatenation of the form's ID and the primary key of the project table for its primary key.

A foreign key is the same as the complete primary key of another table. Therefore, the acct_first_name attribute by itself in the project table is not a foreign key; neither is the acc_ last_name attribute. If you concatenate them, however, then they are the same as the primary key of the accountant table and, in fact, this is the unit with which referential integrity should be enforced.

Assume that “Jane Johnson” is working on customer 10100's 2014 tax return. It's not enough to ensure that “Jane” appears somewhere in the first name column in the accountant table and “Johnson” appears anywhere in the last name column in the accountant table. There could be many people named “Jane” and many with the last name of “Johnson.” What we need to ensure is that there is one person named “Jane Johnson” in the accountant table, the concatenation of the two attributes that make up the primary key.

The same holds true for the concatenated foreign key in the form table: the tax year and the customer number. A row with a matching pair must exist in the project table before referential integrity is satisfied.

7.1.2 SELECT Queries for Currawong Accounting

In our multicurrency accounting application, we need a number of accessor-layer programs based on SELECT queries. Specifically, we need classes that will do the following tasks:

Return all payees,

Return all bank accounts,

Return all currencies,

Return all institutions,

Return all accounts,

Return all account types,

Return all transactions,

Return an account for which an id value is specified,

Return an account type for which an id value is specified,

Return a bank account for which an id value is specified,

Return a currency for which an id value is specified,

Return an institution for which an id value is specified,

Return a payee for which an id value is specified,

Return a transaction for which an id value is specified,

Return a transaction type for which an id value is specified,

Return the balance of a bank account on a specific date, given the account's id value and the date,

Return the average balance of an account during a specified number of days prior to a specified date, given the account's id value, the date, and a number of days (this will be used for moving-average calculations), and

Return a range of transactions from a specified account, given the account's id value, a start date, and an end date.

Most of these require only a simple SELECT statement, but others require that joins be used. The last eight items in this list are functionally very similar. They're all dealt with in the section titled, “Return a Specified Row from a Specified Table.” Let's examine solutions to each of the requirements in turn.

Return All Payees

To return all payees, we obviously need a SELECT statement that draws all columns out of the ACCT_payee table. Because ACCT_payee has no foreign keys, extracting its data is a simple matter of sending a straightforward SELECT statement to the database server via a PEAR DB connection. Let's examine getPayees.php line by line to see how this is done.

The first order of business is to import the PEAR DB classes, the NuSOAP classes, and dbDetails.php, which contain information about the database server and security credentials for it. Then, we register the getPayees function—to be defined momentarily—as a SOAP service. Refer to Chapters 4 and 5 for further information on NuSOAP and Web services.

In the function definition, these five global variables must be declared in order for them to be accessible. Their values are assigned in dbDetails.php, which was imported at the beginning of the program.

Using the standard PEAR DB procedure (see Chapter 5 for more details), we connect to the database server. The program checks for an error condition.

That's the SQL query that is to be sent to the database server. Note that we specify the columns, even though it's all of them. That way we know what order the columns will be in when results come back.

The program sends the query to the database and checks to see if an error message comes back.

The remainder of the program involves setting up an array—$returnArray—and filling it with a series of subarrays (in other words, $returnArray is a two-dimensional array). The subarrays are associative arrays in which the keys correspond to database column names and values come from each row of results that comes out of $result by way of fetchRow().

Return All Bank Accounts

In order to return a list of all bank accounts, the database server will need to perform several joins. This is because the ACCT_bank_account table, which contains information on individual accounts maintained by the customer, has a number of foreign keys. Other than the complex SQL query, though, the accessor-layer program that retrieves account information is similar to other accessor-layer programs.

Here's a listing of getBankAccounts.php, which handles account-list retrieval.

Again, this program bears a strong resemblance to other programs in the accessor layer, with the exception of this complicated SQL statement:

The statement specifies that seven columns are to be retrieved from four different tables. Not surprisingly, ACCT_bank_account is the main table, and the other three tables are left joined to it based on foreign key values in ACCT_bank_account. Performing multiple joins in a single query is not a problem. Note also that a number of retrieved columns are renamed with AS statements.

Return All Currencies

The requirement to return a list of all currencies stored in the database is handled by the code contained in getCurrencies.php. It's a straightforward query module with no joins or other unusual tricks. Here's a full listing:

Simple enough. Again, the returned array—$returnArray—is a two-dimensional array in which the subarrays are associative.

Return All Institutions

Currawong Accounting must be able to return a list of all financial institutions with which the customer does business. Because the table that stores information about financial institutions, ACCT_institutions, has no foreign keys, there are no joins to be set up. The accessor program simply connects via PEAR DB, runs a simple query, and returns the results in an array. Here is a full listing of getInstitutions.php:

This is an accessor program like many others for Currawong Accounting. The SQL statement is simple, and the results are made available via a Web service in the form of an array of associative arrays.

Return All Accounts

Currawong Accounting must be able to return the names of all accounts—that is to say, all “accounting accounts,” not all bank accounts in this case. This is a very simple operation, requiring only an uncomplicated SELECT query and the usual NuSOAP and array-returning infrastructure. Here is a full listing of getAccounts.php:

Again, quite simple. The program runs an unadorned SELECT statement:

and returns the resultant rows in an indexed array of associative arrays.

Return All Account Types

The requirement to return a list of all account types stored in the database is handled by the code contained in getAcctTypes.php. It's a straightforward query module. Here's a full listing:

The core of this module is a simple SELECT statement that returns two columns, id and name, from every row in the table.

Return All Transactions

The program is required to return all transactions stored in the ACCT_register table. Although the requirement is easily satisfied with a SELECT query, the query must include a couple of JOIN operations because ACCT_register includes several foreign keys. Here is the complete listing of getTransactions.php, the file that handles the work:

The unusual aspect of this program is the elaborate SELECT statement, which involves two left joins:

The two joins correspond to the two foreign keys in ACCT_register, which have to do with the payee and the accounting account involved in the transaction.

Return a Specified Row from a Specified Table

The Currawong specification requires a number of accessor-layer programs for similar purposes. These all take the primary key value (id) of some row in some table and return all or most column values from that row. Here's a list of all accessor-layer programs that do that kind of work:

▪

Return a specified account (getSpecifiedAccount.php)

▪

Return a specified account type (getSpecifiedAcctType.php)

▪

Return a specified bank account (getSpecifiedBankAccount.php)

▪

Return a specified currency (getSpecifiedCurrency.php)

▪

Return a specified institution (getSpecifiedInstitution.php)

▪

Return a specified payee (getSpecifiedPayee.php)

▪

Return a specified transaction (getSpecifiedTransaction.php)

▪

Return a specified transaction type (getSpecifiedTransType.php)

All of these programs are essentially the same, differing only in the tables against which their queries run. Let's have a look at getSpecifiedBankAccount.php, which can exemplify the bunch:

Essentially, that program makes the same query against ACCT_bank_account as getBankAccounts.php (which returns all bank accounts), except that this program contains a WHERE clause:

Because $id arrived as a parameter, it can be used to grab the details of only the account required by the remote tier.

Return the Balance of an Account

The accessor layer has to be able to return the balance of a bank account on a specific date, given the account's id value and the date. This job is handled by getSpecifiedBankAccount-Balance.php, listed here:

That program accepts the id (primary key value) of the account to be examined as a parameter, as well as the date for which the balance is to be calculated. The date value has to be in MySQL format (YYYY-MM-DD) when it arrives as a parameter; no verification of its format is done or conversion made.

The id and date values are used in a query:

There, sum() is a MySQL function that totals all values in a specified column (amount, in this case). The query is further limited by the WHERE clause, which imposes limits based on both id and date.

Return the Average Balance of an Account over Time

Currawong Accounting needs a module on the accessor layer that will return the average balance of an account during a specified number of days prior to a specified date, given the account's id value, the date, and a number of days. The idea is that the user can specify an account, a date, and a number of days (typically 60 or 90) and see the average balance (based on weekly samples) for that account during the preceding number of days specified. For example, if the user specified account 2 (that's an id value), today's date, and the value 60, this module should calculate the mean balance of account 2 during the past 60 days.

This work is carried out by getAverageBalance.php.

Note that getAverageBalance.php makes use of an unusual feature of PHP syntax in enclosing its SQL statement. The following sequence of characters is the functional equivalent of a double quotation mark (″) in the opening position (that is, at the beginning of a quoted string).

Its complement is this: EOQ;

That is the equivalent of a double quotation mark (″) at the end of a quoted string. Between the two sequences you can have as many line breaks as you like; the PHP interpreter ignores them. The EOQ sequences allow you to break up long strings and thus make your source code more readable.

This program works by taking the provided date value, converting it to a Unix-standard timestamp, and subtracting from it a value equal to the number of seconds in the period for which an average value is required:

Then, a loop gets the balance at the end of every seven-day period between that calculated starting point and the original date value. On each pass through the loop, the balance is added to a running total, and $n is incremented so the total number of samples is known:

After the loop terminates, the average of all the samples is computed:

and returned:

Return a Range of Transactions

In order for the presentation layer to be able to depict an account register, it has to be able to retrieve a series of transactions from an account of interest. The module contained in getSpecifiedTransactions.php will return a range of transactions from a specified account, given the account's id value, a start date, and an end date.

This is not a complicated program. Its SQL query simply retrieves all fields in the ACCT_register table that have the right account value and fit within the date parameters:

As is typical of the software in this layer, the retrieved values are then fitted into a two-dimensional associative array that's returned to the sender.