How do I Justify a one-to-one relationship:

A database developer asked me a couple weeks ago to justify the use of one-to-one relationships within his design document. He stated that the only thing he found on the web concerning one-to-one relationships was ‘Use them when appropriate for your situation’. Now if that is not helpful, I do not know what is. But the web does provide a lot of that advise.

One-to-one relationship: Defined as the existence of exactly one record in Table A that corresponds to one record in Table B.

I recently read a white paper put out by Sybase that tries to answer the age-old question (or at least help define the question) of how to ensure that the people you trust the most with your corporate data are NOT the ones who are ripping you off. The target audience is the commercial IT manager or CIO. The paper talks a lot about Sarbanes-Oxley, as you might imagine.

On a recent project I had to persist image features into a database to be used on a content-based image retrieval (CBIR) system. These features included the following:

• Color - represented as histograms for each color band; each histogram consisted of 256 float numbers.
• Shape - represented as 127 Fourier coefficients (complex numbers).
• Texture - represented as an array of the top 60 peaks locations (and sign) on a 128x128 wavelet decomposition grid; the wavelet decomposition is performed in each of the three color bands.

I normally do my modeling in terms of object orientation first. I created an ImageFeatures class which had references to other classes such as Color, Shape and Texture. Each of these classes ended up holding a primitive array of float numbers for the corresponding feature data. That's all well and good. Pretty simple stuff.

A recent issue of Infoworld magazine had a cover article on Viper, IBM's new release of DB2 (version 9.1). The major enhancement is that it stores XML data in native format, not as a CLOB or BLOB. Here's a link to the article:

http://www.infoworld.com/article/06/08/14/33FEdb2viper_1.html?s=feature

Quoting one paragraph:
For starters, it gives you the ability to access
XML data using SQL queries, just like ordinary relational tables. You
can also use XQuery to access relational tables, in addition to XML.
You can even use relational SQL to limit the range of data pulled back
from XQuery expressions. DB2 allows almost continuous intermixing of
the two languages.

Just a quick post to make sure all know that a version of Oracle's 10g database is now free...

Free
to develop, deploy, and distribute

Oracle Database 10g Express Edition (Oracle Database XE) is an entry-level, small-footprint database based on the Oracle Database 10g
Release 2 code base that's free
to develop, deploy, and distribute; fast to download; and simple to
administer.

 Oracle 10g Express Edition has the expected limitations on size, but Oracle has greatly improved the install process.  (If you have tried to install 9i before... you'll see what I mean.)  They appear to be going after the MySQL market share with this free offering...  should be interesting to see how it plays out for them.

For your next project, it will be worth your time to investigate this new option.

Are you new to a database schema and want to get to know it quickly?  It is very common for a system in development to interact with an existing system and all too often, the requirements for this interaction are vague and difficult to understand, and gaining knowledge about this (potentially very old) system can be even harder.

Of course, there are tons of ways to learn about a schema.  Old school DBA’s may prefer to do manual queries on the views of the database metadata (all_tables, user_tables, all_objects, etc) to get an idea of how it works.  People not as familiar with such ‘DBA-esque’ things may have a hard time extracting an understanding of the data model using nothing but queries.  Often times, it might be easier to get an understanding using such tools as Toad and SQuirreL.  I just recently found a tool called SchemaSpy that can answer a LOT of questions that a ‘newbie’ may have about a schema.

When most people think of Microsoft Excel, they think of graphs, formulas, financials, etc.  But by scratching slightly below the surface, one will notice that Excel is extremely powerful at more than just the everyday crunching and presentation of data.

In working with databases on a regular basis, there are tedious tasks that can take valuable time away from expanding, solidifying and tuning the database itself.  Of course there are 3rd party tools that can be used for database administration.  However, for data related issues that require large numbers of SQL statements to insert or update records, or to perform in-depth analysis of large amounts of data, I find that there is no better tool than Microsoft Excel.

An Iterative Approach to Data Migration

Data Migration is the process of moving required (and most often very large) volumes of data from existing systems to new systems. I found that such projects can benefit from the use of a tailored development process. This process I came up with can be broken down into three phases.

      Requirements and Design Phase (Mostly requirements gathering activities)

For many companies and government agencies, accurate Audit and History information is important and in some cases, mandated.

For the purposes of this article, the following definitions are presented:
• Audit – which record was inserted, updated, or deleted, when, and by who
• History – storage of earlier values from records that have changed

Considerations for Audit and History System Designs
In designing a comprehensive Audit and History (A/H) system, a number of considerations must be made.

Among them:
• Is this A/H functionality being built into the system early in the design process or is an existing system being retrofitted with the A/H functionality?
• For what data should A/H  be maintained?  Partial?  Complete?
• Will the history information tie into systems external to the databases/file systems under direct control of the immediate system?
• Where should the functionality be located – client, midtier, or database?
• Is A/H important enough that transactions should fail if the A/H fails?
• If the A/H functionality is based in the database, will the client log in to the database with the user’s own id or one or more generic accounts?
• How long should A/H information be stored?

Oracle's Application Development Framework business Components (ADF BC ) allows for the implementation of business rules for adding default values to entity attributes, but what type of rules can we implement for the case of a sequence generated primary key?

It is standard practice within current industry to define a primary key column on all object relational database tables, which in most cases is a unique number (as opposed to a unique string of characters). This unique number can be generated by a database sequence number generator or by calculating the next available value based on what is currently realized on that column. The latter presents obvious transactional problems whereas the previous is the standard way of handling unique number-based primary keys. This presents an interesting challenge when implementing business logic, which involves inserting new record/records into a database. If we create a new Row, populate the various attributes (except for the primary key) and attempt to save the transaction we will most assuredly receive a database error, as a primary key is always unique and non- null. To prevent this type of error we can implement this logic in one of two ways: