Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Apr 9, 2004

Data Modeling, Breaking & Fixing First Normal Form (1NF) - Page 2

By James Koopmann

Mistake 2, internal array element

This second form of breaking 1NF takes the form of creating a column that contains many values of an attribute. Taking from our CITY table example, this mistake makes our CITY table look like the one in Listing 5. This is a much worse form of breaking 1NF than our first mistake. It has the added difficulty of no boundaries for columns in the city except for the comma separator. It is prone to errors in data entry around the comma-separated fields. Sometimes we will encounter spaces before or after the comma that can play havoc on any parsing that we may want to do.

Listing 5
City table with internal array structure

STATE

CITY

COLORADO

DENVER,BOULDER,ASPEN,MONTROSE,PUEBLO

MICHIGAN

DETROIT,LANSING

TEXAS

DALLAS,HOUSTON,PARIS

Since there are no columns and we are not quite sure of the number of cities that any one column may or may not have, our only solution to this problem is to create some logic (application) to extract the information from this column. In Listing 6, there is code to make this mistake behave just like a normal table. In order to accomplish this task we need to do the following.

  1. Rename the CITY table. This is done since we are going to want to keep access to this data through the same object name of CITY.
  2. Create a few abstract data types for manipulating our output.
  3. Create a function that will be called when requesting information from the CITY table
  4. Create a view called CITY.
  5. Now you can just select directly from the CITY view.

Listing 6
Making an internal array listing behave like normal rows

RENAME city TO city_tb
/
CREATE TYPE CITY_TY AS OBJECT
       (STATE  CHAR(10),
        CITY   VARCHAR2(100))
/
CREATE TYPE CITY_TY_TB AS TABLE OF CITY_TY
/
CREATE OR REPLACE FUNCTION CITY_FC
         RETURN CITY_TY_TB PIPELINED IS
         PRAGMA AUTONOMOUS_TRANSACTION; 
TYPE         ref0 IS REF CURSOR;
cur0         ref0;
out_rec      city_ty 
          := city_ty(NULL,NULL);

vcity     VARCHAR2(100);
vstartpos NUMBER;
vendpos   NUMBER;
vlastpos  NUMBER;
BEGIN
OPEN cur0 FOR 'select state,city,instr(city,'','',1),instr(city,'','',-1) from city_tb';
LOOP
  vstartpos := 1;
  FETCH cur0 INTO out_rec.state, vcity, vendpos, vlastpos;
  EXIT WHEN cur0%NOTFOUND;
  IF vlastpos = 0 THEN
    out_rec.city := vcity;
    PIPE ROW(out_rec);
  END IF;
  LOOP
    EXIT WHEN vlastpos = 0;
    select instr(vcity,',',vstartpos) into vendpos from dual;
    IF vendpos = vlastpos THEN
      out_rec.city := substr(vcity,vstartpos,vendpos-vstartpos);
      PIPE ROW(out_rec);
      out_rec.city := substr(vcity,vlastpos+1);
      PIPE ROW(out_rec);
      EXIT;
    END IF;
    out_rec.city := substr(vcity,vstartpos,vendpos-vstartpos);
    PIPE ROW(out_rec);
    vstartpos := vendpos+1;
  END LOOP;
END LOOP;
CLOSE cur0;
RETURN;
END CITY_FC;
/
CREATE OR REPLACE VIEW CITY AS 
  SELECT a.state,a.city
  FROM TABLE(CITY_FC) a
/

The breaking of 1NF can and does cause havoc in all of our lives. Until you have the time and resources to fix the problem within the physical model, you must provide solutions that are easy for everyone to use. My suggestion to you is to come up with a solution that will work and allow you to fix the real problem with the least amount of impact on your user community. If you take an approach to put a view in place of the real table, let your user community select from that view, you will be able to hide the logic behind the scenes and hopefully one day fix the real problem and allow yourself to get rid of the view and underlying logic.

» See All Articles by Columnist James Koopmann



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date