Reflexivity axiom for inferring functional dependencies

This is obviously a homework question but it seems to me a bit like a trick question from a db perspective. I don't think there is a simple answer and here the answer may be quite a number of additional questions. My recommendation in trying to answer the question is to sketch out how pages would work, and the like. In essence so much depends on implementation (compare disk I/O in MySQL's InnoDB vs PostgreSQL and you will see massive differences due partly to supported methods of access).

The key problem comes down to indexing. The heap table could in fact be implicitly indexed the way MySQL's InnoDB does, or it could be unindexed (the way PostgreSQL does it). If it is indexed, then you have additional issues. Do you support a physical scan of the first block (to determine where to start)? If so, how do you determine which starting point to use (if you collect statistics on the data in the tables that would help but that's additional I/O there too)?

I could imagine cases where the number of disk block reads could range from n/3 (where n is the number of records) to (n^1/10)/3, and writes to range from 1 to n/3.

In the end questions like this, particularly where you have software assigned for use in class probably should be directed to your instructor. It isn't clear what the point of the exercise is and so much depends on implementation.

The approach I take is broadly as follows (and this is by no means complete):

1. Requirements You need to know the goals of the to be situation.

   • specifics of platforms (hardware, software), versions of these. There may be dependencies in terms of feature availability
   • features used in "from" situation and needed in "to" situation. Think of
     • queueing
     • clustering
     • high availability
     • backup and recovery

2. Analyse current situation for

   • requirements missing from above
   • DBMS or platform specific features being used

3. Design for an entirely automated and repeatable approach. This makes it

   • testable
   • able to run overnight/weekend in case of a long process
   • allows for an iterative solution where the database becomes more and more complete during the life of the project

4. Determine dependencies between systems both on the "from" side and on the "to" side. This will affect the "flow" of the migration.

5. Decide how the data will be migrated. Amongst others, choices are:

   • dump and reload, probably via some database agnostic format
     • if reloading, it may be easier to go via "temporary" tables and then populate the real target tables from the temporary ones. I've had good experience with this approach.
     • this also makes it easier to allow for "fallout" which can be manually corrected after cutover (but you need to keep this under control too).
   • "suck" the data from the "from" database into the "to" database using some database connection technology (eg Oracle Gateway, PostgreSQL foreign data wrappers). This approach is usually also best when wanting to perform a migration where the target DBMS is the same as the source.
   • decide whether to use scripting (SQL scripts mainly, but possibly also Perl/awk etc) or a tool (eg Oracle used to have a product called Migration Workbench or an ETL tool can be used for this purpose) or even "programmed" solutions in Java/C# etc. This decision will largely be driven by the skillset at hand. My preference is to use SQL wherever possible.

6. Build it.

   • If the target data model is different, determine mappings. An ETL tool can be handy in these cases, but well written SQL can be just as clear. Like all code, try to keep the documentation on the mapping with the code rather than in a separate document.