Database Reference
In-Depth Information
in database applications we are dealing with a set of tables with multi-columns and multi-
rows. To defi ne the data fl ow relations created from the database usage we should decide
on a level of granularity of the database variables in which we can trace their defi nition,
and subsequently, their use.
Suggested Technique
One choice of the level of granularity is to consider each table in the database as a
variable and handle all types of table usages as either a defi nition of the table or a retrieval of
values. However, most of the time only parts of a table are handled in a given SQL statement.
Another level of granularity is to consider each row in the table as a separate variable and
trace the data fl ow relations that exist from the usage of each row separately. This situation
is similar to the problem of defi ning the control fl ow relations created by linked lists, where
each node in the linked list is dynamically created, modifi ed, and deleted. In the case of
database tables each row is dynamically created, deleted, modifi ed, or retrieved. This implies
that we cannot statically identify the possible data fl ow relations that could exist between
rows. This is because the row usage is determined by evaluating the restricting conditions
of the SQL statement performing the data manipulation.
A more moderate solution between the table and row level of granularity is the column
level. Since the number of columns is fi xed and columns are used in SQL statements us-
ing their unique names, we can determine the column usage statically. A drawback of this
choice is the fact that it does not discriminate between the usage of one particular column
value belonging to some row and the usage of the same column but of a different row.
Discriminating between such usages leads us back to the problem of row-level data fl ow
dependencies.
SQL statements use columns directly and indirectly or, in other words, explicitly and
implicitly. These usages are either defi nition or retrieval. A table participating in master detail
relations has a group of its columns referencing the primary key columns of the master table.
Whenever these columns are defi ned the database implicitly checks that the master table
contains a record that has its primary key column values matching the foreign key column
values of the newly added record. So, whenever a new record is created the primary key
columns of the master table are used. Conversely, whenever a master record is deleted the
detail tables are checked to see whether there exist records with foreign key column values
matching with the primary key column values of the master record being deleted.
We differentiate between fi ve main usages of database columns. They are: delete, insert,
reference, select, and update. Reference and select usages are computational usages and
are denoted as c-use. Update, delete, and insert usages are defi ne usages and are denoted as
d-use. However, notice that in all of the previous defi ne categories the result of the defi ni-
tion is dependent on the initial values of the columns defi ned, because the columns contain
multi-values and zero or more of its values retain their initial values. Therefore, whenever
there is a defi ne usage of a column there is also a computation usage.
The list of various cases of column usages includes:
1.
Explicit usage
a. Explicit retrieval
i. In the selection list of SELECT SQL statements and SELECT
sub-queries.
